1 PACKAGE BODY rcv_shipment_object_sv AS
2 /* $Header: RCVCHTIB.pls 120.1 2005/06/29 00:52:00 pjiang noship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_asn_debug VARCHAR2(1) := NVL(fnd_profile.VALUE('RCV_DEBUG_MODE'), 'N');
6
7 /*===========================================================================
8
9 PROCEDURE NAME: create_object()
10
11 ===========================================================================*/
12 /* ksareddy - parallel processing support for RVCTP */
13 PROCEDURE create_object(
14 x_request_id NUMBER,
15 x_group_id NUMBER
16 ) IS
17 x_progress VARCHAR2(3) := NULL;
18 x_all_lines_fatal BOOLEAN := TRUE;
19 x_error_record rcv_shipment_object_sv.errorrectype;
20 x_header_record rcv_shipment_header_sv.headerrectype;
21 x_transaction_record rcv_shipment_line_sv.transaction_record_type;
22 x_cascaded_table rcv_shipment_object_sv.cascaded_trans_tab_type;
23 document_num_record rcv_shipment_line_sv.document_num_record_type;
24 x_first_doc_num rcv_transactions_interface.document_num%TYPE;
25 x_first_record BOOLEAN := TRUE;
26 x_any_line_error_flag BOOLEAN := FALSE;
27 n BINARY_INTEGER := 0;
28 x_current_line_status VARCHAR2(1) := 'S';
29 x_fail_if_one_line_fails VARCHAR2(1) := 'N';
30 BEGIN
31 IF (g_asn_debug = 'Y') THEN
32 asn_debug.put_line('Enter shipment object create');
33 END IF;
34
35 x_progress := '000';
36
37 IF (g_asn_debug = 'Y') THEN
38 asn_debug.put_line('Enter garbage processor');
39 END IF;
40
41 -- Need to do garbage collection before anything else
42 -- as we may have some ASNs that are entirely invalid
43 -- as they have either invalid POs or missing PO numbers
44
45 -- Also need to update all transaction_interface rows for
46 -- a header_interface row that is marked as running.
47 -- The call to garbage collector is for Bug 2367174.
48
49 x_progress := '001';
50
51 IF (g_asn_debug = 'Y') THEN
52 asn_debug.put_line('Calling the Garbage collector');
53 END IF;
54
55 -- bug2626270 - pass x_group_id into collect_garbage procedure also
56 rcv_garbage_collector_sv.collect_garbage(x_request_id, x_group_id);
57
58 IF (g_asn_debug = 'Y') THEN
59 asn_debug.put_line('Enter shipment object create');
60 END IF;
61
62 fnd_profile.get('RCV_FAIL_IF_LINE_FAILS', x_fail_if_one_line_fails);
63
64 IF (g_asn_debug = 'Y') THEN
65 asn_debug.put_line('RCV_FAIL_IF_LINE_FAILS profile option =' || x_fail_if_one_line_fails);
66 END IF;
67
68 --ksareddy 2506961 - performance bug - cache the install status of EC
69 --X_edi_install := po_core_s.get_product_install_status('EC'); --2187209
70 IF (g_is_edi_installed IS NULL) THEN
71 g_is_edi_installed := po_core_s.get_product_install_status('EC');
72 END IF;
73
74 --ksareddy - 2506961 support for parallel processing - group_id based
75 OPEN rcv_shipment_object_sv.c1(x_request_id, x_group_id);
76 x_progress := '010';
77
78 LOOP
79 FETCH rcv_shipment_object_sv.c1 INTO x_header_record.header_record;
80
81 IF (g_asn_debug = 'Y') THEN
82 asn_debug.put_line(TO_CHAR(rcv_shipment_object_sv.c1%ROWCOUNT));
83 END IF;
84
85 EXIT WHEN rcv_shipment_object_sv.c1%NOTFOUND;
86 x_progress := '020';
87 x_error_record.error_status := 'S';
88 x_error_record.error_message := NULL;
89 x_header_record.error_record := x_error_record;
90
91 IF x_header_record.header_record.transaction_type = 'CANCEL' THEN
92 IF (g_asn_debug = 'Y') THEN
93 asn_debug.put_line('Enter Cancel Shipment ');
94 END IF;
95
96 rcv_shipment_header_sv.cancel_shipment(x_header_record);
97
98 IF x_header_record.error_record.error_status IN('S', 'W') THEN
99 UPDATE rcv_headers_interface
100 SET processing_status_code = 'SUCCESS',
101 validation_flag = 'N',
102 receipt_header_id = x_header_record.header_record.receipt_header_id
103 WHERE header_interface_id = x_header_record.header_record.header_interface_id;
104
105 IF (g_asn_debug = 'Y') THEN
106 asn_debug.put_line('RCV_ASN_ACCEPT_NO_ERR');
107 END IF;
108
109 -- bug 654099, should not insert into PO Interface Errors table if there is no error
110 -- otherwise, 824 will pick it up and send to customers
111 IF (g_asn_debug = 'Y') THEN
112 asn_debug.put_line('ASN cancelled without errors');
113 END IF;
114 ELSE
115 -- the header failed
116 -- error status for the header is either 'E' or 'U'
117
118 IF (g_asn_debug = 'Y') THEN
119 asn_debug.put_line('RCV_ASN_NOT_ACCEPT');
120 asn_debug.put_line('The header has failed ' || TO_CHAR(x_header_record.header_record.header_interface_id));
121 asn_debug.put_line('ASN could not be cancelled');
122 END IF;
123
124 UPDATE rcv_headers_interface
125 SET processing_status_code = 'ERROR'
126 WHERE header_interface_id = x_header_record.header_record.header_interface_id;
127
128 UPDATE rcv_transactions_interface
129 SET processing_status_code = 'ERROR'
130 WHERE header_interface_id = x_header_record.header_record.header_interface_id;
131
132 x_progress := '060';
133 /* WDK - we've already inserted an error into po_interface_errors. isn't this redundant? */
134 x_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
135 rcv_error_pkg.set_error_message('RCV_ASN_NOT_ACCEPT', x_header_record.error_record.error_message);
136 rcv_error_pkg.set_token('SHIPMENT', x_header_record.header_record.shipment_num);
137 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
138 'SHIPMENT_NUM',
139 FALSE
140 );
141 END IF;
142 ELSE
143 IF (g_asn_debug = 'Y') THEN
144 asn_debug.put_line('Enter create shipment header');
145 END IF;
146
147 rcv_shipment_header_sv.create_shipment_header(x_header_record);
148
149 IF (g_asn_debug = 'Y') THEN
150 asn_debug.put_line(x_header_record.error_record.error_status);
151 asn_debug.put_line(x_header_record.error_record.error_message);
152 END IF;
153
154 IF (x_header_record.error_record.error_status IN('S', 'W')) THEN
155 x_progress := '030';
156 x_all_lines_fatal := TRUE;
157 x_any_line_error_flag := FALSE;
158
159 -- x_header_record.header_interface_id is not null thru table....
160 IF (g_asn_debug = 'Y') THEN
161 asn_debug.put_line('In lines');
162 asn_debug.put_line(TO_CHAR(x_header_record.header_record.header_interface_id));
163 END IF;
164
165 OPEN rcv_shipment_object_sv.c2(x_header_record.header_record.header_interface_id);
166 n := 0;
167 x_first_record := TRUE;
168 x_cascaded_table.DELETE;
169 x_current_line_status := 'S'; -- Bug 610233, resetting to 'S' for a new ASN
170
171 LOOP
172 n := n + 1;
173
174 IF (g_asn_debug = 'Y') THEN
175 asn_debug.put_line('Current counter is ' || TO_CHAR(n));
176 asn_debug.put_line('No of records in cascaded table ' || TO_CHAR(x_cascaded_table.COUNT));
177 END IF;
178
179 FETCH rcv_shipment_object_sv.c2 INTO x_cascaded_table(n);
180 EXIT WHEN( rcv_shipment_object_sv.c2%NOTFOUND
181 OR x_cascaded_table(n).error_status NOT IN('S', 'W'));
182 x_progress := '040';
183 x_cascaded_table(n).error_status := x_current_line_status;
184 x_cascaded_table(n).error_message := NULL;
185
186 IF (g_asn_debug = 'Y') THEN
187 asn_debug.put_line('Shipment number ' || x_header_record.header_record.shipment_num);
188 END IF;
189
190 rcv_shipment_line_sv.create_shipment_line(x_cascaded_table,
191 n,
192 x_header_record.header_record.header_interface_id,
193 x_header_record.header_record.asn_type,
194 x_header_record
195 );
196
197 IF (g_asn_debug = 'Y') THEN
198 asn_debug.put_line('Back from create shipment line');
199 asn_debug.put_line('Current counter is ' || TO_CHAR(n));
200 END IF;
201
202 IF (g_asn_debug = 'Y') THEN
203 asn_debug.put_line('Error Status For Create Shipment Line=' || x_cascaded_table(n).error_status);
204 END IF;
205
206 /*
207 ** If one line has failed, check if the profile value that
208 ** controls whether you should fail all lines if one line
209 ** fails is set. If so set all line to be failed.
210 */
211 IF ( x_cascaded_table(n).error_status NOT IN('S', 'W')
212 AND x_fail_if_one_line_fails = 'Y') THEN
213 FOR i IN 1 .. n LOOP
214 x_cascaded_table(i).error_status := 'E';
215 END LOOP;
216
217 x_current_line_status := 'E';
218 x_all_lines_fatal := TRUE;
219 END IF;
220
221 /* <Consigned Inventory Pre-Processor FPI START> */
222
223 /*
224 ** If one rti line fails line-level validation, and the
225 ** failure reason is because the transaction contains either
226 ** one of the document type:
227 ** 1) ASBN for consigned PO,
228 ** 2) ASN/ASBN/STD for Consumption PO, or
229 ** 3) ASN/ASBN/STD for Consumption Release
230 ** pre-processsor will fail all the transactions
231 */
232 IF (x_cascaded_table(n).error_status NOT IN('S', 'W'))
233 AND ( (x_cascaded_table(n).error_message = 'RCV_REJECT_ASBN_CONSIGNED_PO')
234 OR (x_cascaded_table(n).error_message = 'RCV_REJECT_CONSUMPTION_PO')
235 OR (x_cascaded_table(n).error_message = 'RCV_REJECT_CONSUMPTION_RELEASE')) THEN
236 FOR i IN 1 .. n LOOP
237 x_cascaded_table(i).error_status := 'E';
238 END LOOP;
239
240 IF (g_asn_debug = 'Y') THEN
241 asn_debug.put_line('Since some of the transaction lines are related to Consigned Inventory');
242 asn_debug.put_line('Set all the transaction lines error_status E');
243 END IF;
244
245 x_current_line_status := 'E';
246 x_all_lines_fatal := TRUE;
247 END IF; --IF (X_cascaded_table(n).error_status not in ('S','W')
248 /* <Consigned Inventory Pre-Processor FPI END> */
249 END LOOP;
250
251 -- Loop thru the plsql table for any success/warning at line level
252 -- If any line is a success then we need to insert the line level data
253
254 FOR i IN 1 .. x_cascaded_table.COUNT LOOP
255 IF (g_asn_debug = 'Y') THEN
256 asn_debug.put_line(x_cascaded_table(i).error_status);
257 END IF;
258
259 IF (x_cascaded_table(i).error_status IN('S', 'W')) THEN
260 x_all_lines_fatal := FALSE;
261 x_cascaded_table(i).validation_flag := 'N'; -- Success so RVCTP can take this
262 ELSE
263 x_any_line_error_flag := TRUE; -- if any line is in error
264 END IF;
265 END LOOP;
266
267 IF (x_all_lines_fatal) THEN
268 IF (g_asn_debug = 'Y') THEN
269 asn_debug.put_line('All lines were in error for the shipment ' || x_header_record.header_record.shipment_num);
270 END IF;
271
272 -- Need to insert an error condition into the poi
273
274 IF (g_asn_debug = 'Y') THEN
275 asn_debug.put_line('RCV_ASN_NOT_ACCEPT');
276 END IF;
277
278 x_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
279 rcv_error_pkg.set_error_message('RCV_ASN_NOT_ACCEPT', x_header_record.error_record.error_message);
280 rcv_error_pkg.set_token('SHIPMENT', x_header_record.header_record.shipment_num);
281 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
282 'SHIPMENT_NUM',
283 FALSE
284 );
285
286 -- If this is a test, then we have not inserted the header record
287
288 IF NVL(x_header_record.header_record.test_flag, 'N') <> 'Y' THEN
289 DELETE FROM rcv_shipment_headers
290 WHERE shipment_header_id = x_header_record.header_record.receipt_header_id;
291 END IF;
292
293 UPDATE rcv_headers_interface
294 SET processing_status_code = 'ERROR'
295 WHERE header_interface_id = x_header_record.header_record.header_interface_id;
296
297 UPDATE rcv_transactions_interface
298 SET processing_status_code = 'ERROR'
299 WHERE header_interface_id = x_header_record.header_record.header_interface_id;
300
301 x_progress := '050';
302 ELSE
303 -- if this is not a test, then
304 -- delete the first original transactions_interface row and
305 -- insert the pl/sql table into the transactions_interface
306
307 IF NVL(x_header_record.header_record.test_flag, 'N') <> 'Y' THEN
308 rcv_asn_trx_insert.handle_rcv_asn_transactions(x_cascaded_table, x_header_record);
309 END IF;
310
311 IF NOT x_any_line_error_flag THEN -- all lines were fine
312 IF (g_asn_debug = 'Y') THEN
313 asn_debug.put_line('RCV_ASN_ACCEPT_NO_ERR');
314 END IF;
315
316 -- bug 654099, should not insert into PO Interface Errors table if
317 -- there is no error. Otherwise, 824 will pick it up and send to customers
318
319 IF (g_asn_debug = 'Y') THEN
320 asn_debug.put_line('ASN accepted without errors');
321 END IF;
322 ELSE
323 IF (g_asn_debug = 'Y') THEN
324 asn_debug.put_line('ASN accepted with errors RCV_ASN_ACCEPT_W_ERR');
325 END IF;
326
327 x_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_warning;
328 rcv_error_pkg.set_error_message('RCV_ASN_ACCEPT_W_ERR', x_header_record.error_record.error_message);
329 rcv_error_pkg.set_token('SHIPMENT', x_header_record.header_record.shipment_num);
330 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
331 'SHIPMENT_NUM',
332 FALSE
333 );
334 END IF;
335
336 IF x_header_record.header_record.asn_type = 'ASBN'
337 AND x_any_line_error_flag THEN -- if any line is in error and type = ASBN
338 -- use this flag to reset invoice_status_code
339
340 -- update the interface table
341 IF (g_asn_debug = 'Y') THEN
342 asn_debug.put_line('RCV_ASBN_NO_AUTO_INVOICE');
343 END IF;
344
345 x_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_warning;
346 rcv_error_pkg.set_error_message('RCV_ASBN_NO_AUTO_INVOICE', x_header_record.error_record.error_message);
347 rcv_error_pkg.set_token('SHIPMENT', x_header_record.header_record.shipment_num);
348 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
349 'SHIPMENT_NUM',
350 FALSE
351 );
352
353 UPDATE rcv_headers_interface
354 SET invoice_status_code = 'RCV_ASBN_NO_AUTO_INVOICE',
355 processing_status_code = 'SUCCESS',
356 validation_flag = 'N',
357 receipt_header_id = x_header_record.header_record.receipt_header_id
358 WHERE header_interface_id = x_header_record.header_record.header_interface_id;
359
360 -- update the rcv_shipment_headers table
361
362 UPDATE rcv_shipment_headers
363 SET invoice_status_code = 'RCV_ASBN_NO_AUTO_INVOICE'
364 WHERE shipment_header_id = x_header_record.header_record.receipt_header_id;
365 ELSE
366 UPDATE rcv_headers_interface
367 SET processing_status_code = 'SUCCESS',
368 validation_flag = 'N',
369 receipt_header_id = x_header_record.header_record.receipt_header_id
370 WHERE header_interface_id = x_header_record.header_record.header_interface_id;
371 END IF;
372 END IF;
373
374 CLOSE rcv_shipment_object_sv.c2;
375 ELSE
376 -- the header failed
377 -- error status for the header is either 'E' or 'U'
378
379 IF (g_asn_debug = 'Y') THEN
380 asn_debug.put_line('RCV_ASN_NOT_ACCEPT');
381 END IF;
382
383 x_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
384 rcv_error_pkg.set_error_message('RCV_ASN_NOT_ACCEPT', x_header_record.error_record.error_message);
385 rcv_error_pkg.set_token('SHIPMENT', x_header_record.header_record.shipment_num);
386 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
387 'SHIPMENT_NUM',
388 FALSE
389 );
390
391 IF (g_asn_debug = 'Y') THEN
392 asn_debug.put_line('The header has failed ' || TO_CHAR(x_header_record.header_record.header_interface_id));
393 END IF;
394
395 UPDATE rcv_headers_interface
396 SET processing_status_code = 'ERROR'
397 WHERE header_interface_id = x_header_record.header_record.header_interface_id;
398
399 UPDATE rcv_transactions_interface
400 SET processing_status_code = 'ERROR'
401 WHERE header_interface_id = x_header_record.header_record.header_interface_id;
402
403 x_progress := '060';
404 END IF;
405 END IF; -- CANCEL/CREATE
406
407 /* Bug#2187209
408 * Before calling the 824 Interface, we need to check whether EDI is
409 * installed or not. If EDI is not installed we need not call 824
410 * Interface and it in turn will not call any EC packages which
411 * will insert records into ECE_ADVO_HEADERS and ECE_ADVO_DETAILS.
412 */
413
414 /* ksareddy call this for ASN or ASBNS */
415 IF (x_header_record.header_record.asn_type IN('ASN', 'ASBN')) THEN
416 IF g_is_edi_installed = 'I' THEN
417 rcv_824_sv.rcv_824_insert(x_header_record, 'ASN'); /* NWANG: changed to two parameters for RCV824 */
418 END IF;
419 END IF;
420 END LOOP;
421
422 CLOSE rcv_shipment_object_sv.c1;
423
424 IF (g_asn_debug = 'Y') THEN
425 asn_debug.put_line('Exit shipment object create');
426 END IF;
427 END create_object;
428 END rcv_shipment_object_sv;
[Home] [Help]