[Home] [Help]
PACKAGE BODY: APPS.INV_PARENT_MMTT_PVT
Source
1 PACKAGE BODY inv_parent_mmtt_pvt AS
2 /* $Header: INVVPMTB.pls 115.1 2004/05/19 01:18:56 stdavid noship $ */
3
4 g_pkg_body_ver CONSTANT VARCHAR2(100) := '$Header: INVVPMTB.pls 115.1 2004/05/19 01:18:56 stdavid noship $';
5 g_newline CONSTANT VARCHAR2(10) := fnd_global.newline;
6
7
8 PROCEDURE print_debug
9 ( p_msg IN VARCHAR2
10 , p_api_name IN VARCHAR2
11 ) IS
12 BEGIN
13 inv_log_util.trace
14 ( p_message => p_msg
15 , p_module => g_pkg_name || '.' || p_api_name
16 , p_level => 4
17 );
18 END print_debug;
19
20
21
22 PROCEDURE print_version_info
23 IS
24 BEGIN
25 print_debug ('Spec:: ' || g_pkg_spec_ver, 'print_version_info');
26 print_debug ('Body:: ' || g_pkg_body_ver, 'print_version_info');
27 END print_version_info;
28
29
30
31 PROCEDURE process_parent
32 ( x_return_status OUT NOCOPY VARCHAR2
33 , p_parent_temp_id IN NUMBER
34 ) IS
35
36 l_api_name VARCHAR2(30) := 'process_parent';
37 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
38
39 l_api_return_status VARCHAR2(1);
40 l_msg_count NUMBER;
41 l_msg_data VARCHAR2(2000);
42
43 l_dummy VARCHAR2(1);
44 l_child_wdth_exists BOOLEAN;
45 l_txn_header_id NUMBER;
46 l_parent_txn_id NUMBER;
47
48
49 CURSOR c_get_parent_details
50 ( p_temp_id IN NUMBER
51 ) IS
52 SELECT mmtt.organization_id
53 , mmtt.transfer_lpn_id
54 , wdt.task_id
55 FROM mtl_material_transactions_temp mmtt
56 , wms_dispatched_tasks wdt
57 WHERE mmtt.transaction_temp_id = p_temp_id
58 AND wdt.transaction_temp_id = mmtt.transaction_temp_id;
59
60 parent_rec c_get_parent_details%ROWTYPE;
61
62 BEGIN
63 x_return_status := fnd_api.g_ret_sts_success;
64
65 SAVEPOINT process_parent_sp;
66
67 print_version_info;
68
69 IF l_debug = 1 THEN
70 print_debug
71 ( 'Entered with parameters: ' || g_newline ||
72 'p_parent_temp_id => ' || to_char(p_parent_temp_id)
73 , l_api_name
74 );
75 END IF;
76
77 l_child_wdth_exists := FALSE;
78 BEGIN
79 SELECT 'x'
80 INTO l_dummy
81 FROM dual
82 WHERE EXISTS
83 ( SELECT 'x'
84 FROM wms_dispatched_tasks_history
85 WHERE parent_transaction_id = p_parent_temp_id
86 AND is_parent = 'N'
87 );
88
89 IF l_debug = 1 THEN
90 print_debug ('Child record exists', l_api_name);
91 END IF;
92
93 l_child_wdth_exists := TRUE;
94 EXCEPTION
95 WHEN NO_DATA_FOUND THEN
96 l_child_wdth_exists := FALSE;
97 WHEN OTHERS THEN
98 IF l_debug = 1 THEN
99 print_debug
100 ( 'Exception checking if child WDTH rec exists: ' || sqlerrm
101 , l_api_name
102 );
103 END IF;
104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
105 END;
106
107 OPEN c_get_parent_details (p_parent_temp_id);
108 FETCH c_get_parent_details INTO parent_rec;
109 CLOSE c_get_parent_details;
110
111 IF l_debug = 1 THEN
112 print_debug
113 ( 'Org ID: ' || to_char(parent_rec.organization_id) ||
114 ', xfer LPN ID: ' || to_char(parent_rec.transfer_lpn_id) ||
115 ', task ID: ' || to_char(parent_rec.task_id)
116 , l_api_name
117 );
118 END IF;
119
120 IF l_child_wdth_exists
121 AND
122 parent_rec.transfer_lpn_id IS NOT NULL
123 THEN
124 SELECT mtl_material_transactions_s.NEXTVAL
125 INTO l_txn_header_id
126 FROM dual;
127
128 IF l_debug = 1 THEN
129 print_debug
130 ( 'Generated header ID: ' || to_char(l_txn_header_id)
131 , l_api_name
132 );
133 END IF;
134
135 l_api_return_status := fnd_api.g_ret_sts_success;
136 wms_task_dispatch_put_away.archive_task
137 ( p_temp_id => p_parent_temp_id
138 , p_org_id => parent_rec.organization_id
139 , x_return_status => l_api_return_status
140 , x_msg_count => l_msg_count
141 , x_msg_data => l_msg_data
142 , p_delete_mmtt_flag => 'Y'
143 , p_txn_header_id => l_txn_header_id
144 , p_transfer_lpn_id => parent_rec.transfer_lpn_id
145 );
146
147 IF l_api_return_status <> fnd_api.g_ret_sts_success
148 THEN
149 IF l_debug = 1 THEN
150 print_debug
151 ( 'Error from wms_task_dispatch_put_away.archive_task: '
152 || l_msg_data
153 , l_api_name
154 );
155 END IF;
156 RAISE FND_API.G_EXC_ERROR;
157 END IF;
158
159 --
160 -- Update parent_transaction_id in WDTH
161 --
162 BEGIN
163 UPDATE wms_dispatched_tasks_history wdth
164 SET wdth.parent_transaction_id = wdth.transaction_id
165 , wdth.is_parent = 'Y'
166 WHERE wdth.task_id = parent_rec.task_id
167 RETURNING wdth.transaction_id INTO l_parent_txn_id;
168
169 IF l_debug = 1 AND SQL%FOUND
170 THEN
171 print_debug
172 ( 'Updated WDTH. Parent transaction ID is: '
173 || to_char(l_parent_txn_id)
174 , l_api_name
175 );
176 END IF;
177
178 EXCEPTION
179 WHEN OTHERS THEN
180 IF l_debug = 1 THEN
181 print_debug
182 ( 'Exception updating WDTH: ' || sqlerrm
183 , l_api_name
184 );
185 END IF;
186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
187 END;
188
189 --
190 -- Update child lines
191 --
192 BEGIN
193 UPDATE wms_dispatched_tasks_history wdth
194 SET wdth.parent_transaction_id = l_parent_txn_id
195 WHERE wdth.parent_transaction_id = p_parent_temp_id;
196
197 IF l_debug = 1 AND SQL%FOUND
198 THEN
199 print_debug
200 ( 'Updated WDTH for child records.'
201 , l_api_name
202 );
203 END IF;
204
205 EXCEPTION
206 WHEN OTHERS THEN
207 IF l_debug = 1 THEN
208 print_debug
209 ( 'Exception updating WDTH: ' || sqlerrm
210 , l_api_name
211 );
212 END IF;
213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
214 END;
215 ELSE
216 DELETE wms_dispatched_tasks
217 WHERE transaction_temp_id = p_parent_temp_id;
218
219 DELETE mtl_material_transactions_temp
220 WHERE transaction_temp_id = p_parent_temp_id;
221 END IF;
222
223 EXCEPTION
224 WHEN fnd_api.g_exc_error THEN
225 ROLLBACK TO process_parent_sp;
226
227 x_return_status := fnd_api.g_ret_sts_error;
228
229 fnd_msg_pub.count_and_get
230 ( p_count => l_msg_count
231 , p_data => l_msg_data
232 , p_encoded => fnd_api.g_false
233 );
234
235 IF l_debug = 1 THEN
236 print_debug (l_msg_data, l_api_name);
237 END IF;
238
239 WHEN OTHERS THEN
240 ROLLBACK TO process_parent_sp;
241
242 x_return_status := fnd_api.g_ret_sts_unexp_error;
243
244 IF l_debug = 1 THEN
245 print_debug ('Other error: ' || sqlerrm, l_api_name);
246 END IF;
247
248 END process_parent;
249
250
251 END inv_parent_mmtt_pvt;