1 package BODY rcv_receipt_confirmation AS
2 /* $Header: RCVRCCNB.pls 120.0.12010000.12 2010/04/13 11:32:34 smididud noship $ */
3
4 g_asn_debug VARCHAR2(1) := asn_debug.is_debug_on; -- Bug 9152790
5
6 PROCEDURE send_confirmation (x_errbuf OUT NOCOPY VARCHAR2,
7 x_retcode OUT NOCOPY NUMBER,
8 p_deploy_mode IN VARCHAR2,
9 p_client_code IN VARCHAR2,
10 p_org_id IN NUMBER,
11 p_dummy_client IN VARCHAR2,
12 p_trx_date_from IN VARCHAR2,
13 p_trx_date_to IN VARCHAR2,
14 p_rcpt_from IN NUMBER,
15 p_rcpt_to IN NUMBER,
16 p_xml_doc_id IN NUMBER) IS
17
18 l_event_name VARCHAR2(100);
19 l_return_status VARCHAR2(1);
20 temp_shid NUMBER := NULL;
21 p_shid_from NUMBER := NULL;
22 p_shid_to NUMBER := NULL;
23 xml_doc_id NUMBER := NULL;
24 trx_date_from VARCHAR2(100);
25 trx_date_to VARCHAR2(100);
26 l_trx_date_from DATE;
27 l_trx_date_to DATE;
28 l_wms_deployment_mode VARCHAR2(2);
29
30
31
32 CURSOR get_rcv_headers_1(l_trx_date_from DATE, l_trx_date_to DATE) IS
33 SELECT distinct rsh.shipment_header_id
34 FROM rcv_shipment_headers rsh,
35 rcv_shipment_lines rsl,
36 rcv_transactions rt
37 WHERE rsh.ship_to_org_id = p_org_id
38 AND wms_deploy.get_client_code(rsl.item_id) = p_client_code
39 AND rt.transaction_date BETWEEN nvl(l_trx_date_from,rt.transaction_date) AND nvl(l_trx_date_to, rt.transaction_date)
40 AND rsh.shipment_header_id BETWEEN nvl(p_shid_from,rsh.shipment_header_id) AND nvl(p_shid_to, rsh.shipment_header_id)
41 AND rsh.shipment_header_id = rt.shipment_header_id
42 AND rt.shipment_line_id = rsl.shipment_line_id
43 AND Nvl(rt.xml_document_id,-99) = nvl(p_xml_doc_id,-99)
44 AND Nvl(rt.receipt_confirmation_extracted,'N') not in ('Y','P')
45 AND (rt.transaction_type IN ('DELIVER') OR
46 (rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
47 AND EXISTS (SELECT '1' FROM rcv_transactions rt2
48 WHERE rt.parent_transaction_id = rt2.transaction_id
49 AND rt2.transaction_type = 'DELIVER')
50 ) OR
51 (rt.TRANSACTION_TYPE IN ('CORRECT')
52 AND EXISTS (SELECT '1' FROM rcv_transactions rt3
53 WHERE rt.parent_transaction_id = rt3.transaction_id
54 AND rt3.transaction_type = 'RETURN TO RECEIVING')
55 )
56 ) order by shipment_header_id asc;
57
58 CURSOR get_rcv_headers_2(l_trx_date_from DATE, l_trx_date_to DATE) IS
59 SELECT distinct rsh.shipment_header_id
60 FROM rcv_shipment_headers rsh,
61 rcv_shipment_lines rsl,
62 rcv_transactions rt
63 WHERE rsh.ship_to_org_id = p_org_id
64 AND wms_deploy.get_client_code(rsl.item_id) = p_client_code
65 AND rt.transaction_date BETWEEN nvl(l_trx_date_from,rt.transaction_date) AND nvl(l_trx_date_to, rt.transaction_date)
66 AND rsh.shipment_header_id BETWEEN nvl(p_shid_from,rsh.shipment_header_id) AND nvl(p_shid_to, rsh.shipment_header_id)
67 AND rt.xml_document_id = p_xml_doc_id
68 AND rsh.shipment_header_id = rt.shipment_header_id
69 AND rt.shipment_line_id = rsl.shipment_line_id
70 AND Nvl(rt.receipt_confirmation_extracted,'N') in ('Y')
71 AND (rt.transaction_type IN ('DELIVER') OR
72 (rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
73 AND EXISTS (SELECT '1' FROM rcv_transactions rt2
74 WHERE rt.parent_transaction_id = rt2.transaction_id
75 AND rt2.transaction_type = 'DELIVER')
76 ) OR
77 (rt.TRANSACTION_TYPE IN ('CORRECT')
78 AND EXISTS (SELECT '1' FROM rcv_transactions rt3
79 WHERE rt.parent_transaction_id = rt3.transaction_id
80 AND rt3.transaction_type = 'RETURN TO RECEIVING')
81 )
82 ) order by shipment_header_id asc;
83
84 CURSOR get_rcv_headers_3(l_trx_date_from DATE, l_trx_date_to DATE) IS
85 SELECT distinct rsh.shipment_header_id
86 FROM rcv_shipment_headers rsh,
87 rcv_transactions rt
88 WHERE rsh.ship_to_org_id = p_org_id
89 AND rt.transaction_date BETWEEN nvl(l_trx_date_from,rt.transaction_date) AND nvl(l_trx_date_to, rt.transaction_date)
90 AND rsh.shipment_header_id BETWEEN nvl(p_shid_from,rsh.shipment_header_id) AND nvl(p_shid_to, rsh.shipment_header_id)
91 AND rsh.shipment_header_id = rt.shipment_header_id
92 AND Nvl(rt.xml_document_id,-99) = nvl(p_xml_doc_id,-99)
93 AND Nvl(rt.receipt_confirmation_extracted,'N') not in ('Y','P')
94 AND (rt.transaction_type IN ('DELIVER') OR
95 (rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
96 AND EXISTS (SELECT '1' FROM rcv_transactions rt2
97 WHERE rt.parent_transaction_id = rt2.transaction_id
98 AND rt2.transaction_type = 'DELIVER')
99 ) OR
100 (rt.TRANSACTION_TYPE IN ('CORRECT')
101 AND EXISTS (SELECT '1' FROM rcv_transactions rt3
102 WHERE rt.parent_transaction_id = rt3.transaction_id
103 AND rt3.transaction_type = 'RETURN TO RECEIVING')
104 )
105 ) order by shipment_header_id asc;
106
107 CURSOR get_rcv_headers_4(l_trx_date_from DATE, l_trx_date_to DATE) IS
108 SELECT distinct rsh.shipment_header_id
109 FROM rcv_shipment_headers rsh,
110 rcv_transactions rt
111 WHERE rsh.ship_to_org_id = p_org_id
112 AND rt.transaction_date BETWEEN nvl(l_trx_date_from,rt.transaction_date) AND nvl(l_trx_date_to, rt.transaction_date)
113 AND rsh.shipment_header_id BETWEEN nvl(p_shid_from,rsh.shipment_header_id) AND nvl(p_shid_to, rsh.shipment_header_id)
114 AND rsh.shipment_header_id = rt.shipment_header_id
115 AND rt.xml_document_id = p_xml_doc_id
116 AND Nvl(rt.receipt_confirmation_extracted,'N') in ('Y')
117 AND (rt.transaction_type IN ('DELIVER') OR
118 (rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
119 AND EXISTS (SELECT '1' FROM rcv_transactions rt2
120 WHERE rt.parent_transaction_id = rt2.transaction_id
121 AND rt2.transaction_type = 'DELIVER')
122 ) OR
123 (rt.TRANSACTION_TYPE IN ('CORRECT')
124 AND EXISTS (SELECT '1' FROM rcv_transactions rt3
125 WHERE rt.parent_transaction_id = rt3.transaction_id
126 AND rt3.transaction_type = 'RETURN TO RECEIVING')
127 )
128 ) order by shipment_header_id asc;
129
130 grh get_rcv_headers_1%ROWTYPE;
131
132 BEGIN
133
134 l_wms_deployment_mode := wms_deploy.wms_deployment_mode;
135
136 IF (g_asn_debug = 'Y') THEN
137 asn_debug.put_line('Deployment Mode is '|| l_wms_deployment_mode);
138 END IF;
139
140 IF (l_wms_deployment_mode not in ('L','D')) THEN
141 RETURN;
142 END IF;
143
144 IF (g_asn_debug = 'Y') THEN
145 asn_debug.put_line('Entering send_confirmation call');
146 asn_debug.put_line('p_org_id is '||p_org_id);
147 asn_debug.put_line('p_client_code is '||p_client_code);
148 asn_debug.put_line('p_xml_doc_id is ' ||p_xml_doc_id);
149 END IF;
150
151
152 x_errbuf := 'Success';
153 x_retcode := 0;
154
155
156 IF p_rcpt_from IS NOT NULL AND p_rcpt_to IS NULL THEN
157 p_shid_from := p_rcpt_from;
158 p_shid_to := p_rcpt_from;
159 END IF;
160
161 IF p_rcpt_from IS NULL AND p_rcpt_to IS NOT NULL THEN
162 p_shid_from := p_rcpt_to;
163 p_shid_to := p_rcpt_to;
164 END IF;
165
166 IF p_rcpt_from IS NOT NULL AND p_rcpt_to IS NOT NULL THEN
167 p_shid_from := p_rcpt_from;
168 p_shid_to := p_rcpt_to;
169 END IF;
170
171
172 IF p_shid_from IS NOT NULL AND p_shid_to IS NOT NULL THEN
173
174 IF p_shid_from > p_shid_to THEN
175 temp_shid := p_shid_from;
176 p_shid_from := p_shid_to;
177 p_shid_to := temp_shid;
178 END IF;
179
180 END IF;
181
182 IF p_xml_doc_id IS NOT NULL THEN
183 xml_doc_id := p_xml_doc_id;
184 end if;
185
186
187 IF (g_asn_debug = 'Y') THEN
188 asn_debug.put_line('p_shid_from is '||p_shid_from);
189 asn_debug.put_line('p_shid_to is '||p_shid_to);
190 END IF;
191
192
193 IF p_trx_date_from IS NOT NULL AND p_trx_date_to IS NULL THEN
194 trx_date_from := FND_DATE.date_to_canonical(to_date(p_trx_date_from, 'YYYY/MM/DD HH24:MI:SS'));
195 l_trx_date_from := to_Date(trx_date_from, 'YYYY/MM/DD HH24:MI:SS');
196 l_trx_date_to := SYSDATE;
197 END IF;
198
199 IF p_trx_date_from IS NULL AND p_trx_date_to IS NOT NULL THEN
200 trx_date_to := FND_DATE.date_to_canonical(to_date(p_trx_date_to, 'YYYY/MM/DD HH24:MI:SS'));
201 l_trx_date_from := null;
202 l_trx_date_to := to_Date(trx_date_to, 'YYYY/MM/DD HH24:MI:SS');
203 END IF;
204
205 IF p_trx_date_from IS NOT NULL AND p_trx_date_to IS NOT NULL THEN
206 trx_date_from := FND_DATE.date_to_canonical(to_date(p_trx_date_from, 'YYYY/MM/DD HH24:MI:SS'));
207 trx_date_to := FND_DATE.date_to_canonical(to_date(p_trx_date_to, 'YYYY/MM/DD HH24:MI:SS'));
208 l_trx_date_from := to_Date(trx_date_from, 'YYYY/MM/DD HH24:MI:SS');
209 l_trx_date_to := to_Date(trx_date_to, 'YYYY/MM/DD HH24:MI:SS');
210 END IF;
211
212 IF (g_asn_debug = 'Y') THEN
213 asn_debug.put_line('From Trxn Date = ' ||to_char(l_trx_date_from,'DD-MON-YYYY HH24:MI:SS'));
214 asn_debug.put_line('To Trxn Date = ' || to_char(l_trx_date_to,'DD-MON-YYYY HH24:MI:SS'));
215 asn_debug.put_line('xml_doc_id = '||xml_doc_id);
216 END IF;
217
218 l_event_name := 'oracle.apps.po.standalone.rcpto';
219
220 IF ( p_xml_doc_id is null and p_client_code is not null ) THEN
221
222 IF (g_asn_debug = 'Y') THEN
223 asn_debug.put_line('Opening cursor get_rcv_headers_1');
224 END IF;
225
226 OPEN get_rcv_headers_1(l_trx_date_from,l_trx_date_to);
227
228 ELSIF (p_xml_doc_id is not null and p_client_code is not null) THEN
229
230 IF (g_asn_debug = 'Y') THEN
231 asn_debug.put_line('Opening cursor get_rcv_headers_2');
232 END IF;
233
234 OPEN get_rcv_headers_2(l_trx_date_from,l_trx_date_to);
235
236 ELSIF (p_xml_doc_id is null and p_client_code is null) THEN
237
238 IF (g_asn_debug = 'Y') THEN
239 asn_debug.put_line('Opening cursor get_rcv_headers_3');
240 END IF;
241
242 OPEN get_rcv_headers_3(l_trx_date_from,l_trx_date_to);
243
244 ELSIF (p_xml_doc_id is not null and p_client_code is null) THEN
245
246 IF (g_asn_debug = 'Y') THEN
247 asn_debug.put_line('Opening cursor get_rcv_headers_4');
248 END IF;
249
250 OPEN get_rcv_headers_4(l_trx_date_from,l_trx_date_to);
251
252 END IF;
253
254 LOOP
255
256 IF get_rcv_headers_1%ISOPEN THEN
257 IF (g_asn_debug = 'Y') THEN
258 asn_debug.put_line('Fetching cursor get_rcv_headers_1');
259 END IF;
260 FETCH get_rcv_headers_1 INTO grh;
261
262 IF (get_rcv_headers_1%NOTFOUND) THEN
263 IF (g_asn_debug = 'Y') THEN
264 asn_debug.put_line('Closing cursor get_rcv_headers_1');
265 END IF;
266 CLOSE get_rcv_headers_1;
267 EXIT;
268 END IF;
269
270 ELSIF get_rcv_headers_2%ISOPEN THEN
271 IF (g_asn_debug = 'Y') THEN
272 asn_debug.put_line('Fetching cursor get_rcv_headers_2');
273 END IF;
274 FETCH get_rcv_headers_2 INTO grh;
275
276 IF (get_rcv_headers_2%NOTFOUND) THEN
277 IF (g_asn_debug = 'Y') THEN
278 asn_debug.put_line('Closing cursor get_rcv_headers_2');
279 END IF;
280 CLOSE get_rcv_headers_2;
281 EXIT;
282 END IF;
283
284 ELSIF get_rcv_headers_3%ISOPEN THEN
285 IF (g_asn_debug = 'Y') THEN
286 asn_debug.put_line('Fetching cursor get_rcv_headers_3');
287 END IF;
288 FETCH get_rcv_headers_3 INTO grh;
289
290 IF (get_rcv_headers_3%NOTFOUND) THEN
291 IF (g_asn_debug = 'Y') THEN
292 asn_debug.put_line('Closing cursor get_rcv_headers_3');
293 END IF;
294 CLOSE get_rcv_headers_3;
295 EXIT;
296 END IF;
297
298 ELSIF get_rcv_headers_4%ISOPEN THEN
299 IF (g_asn_debug = 'Y') THEN
300 asn_debug.put_line('Fetching cursor get_rcv_headers_4');
301 END IF;
302 FETCH get_rcv_headers_4 INTO grh;
303
304 IF (get_rcv_headers_4%NOTFOUND) THEN
305 IF (g_asn_debug = 'Y') THEN
306 asn_debug.put_line('Closing cursor get_rcv_headers_4');
307 END IF;
308 CLOSE get_rcv_headers_4; EXIT;
309 END IF;
310 END IF;
311
312
313 IF (xml_doc_id is NULL) THEN
314
315 IF (l_wms_deployment_mode = 'L') then
316
317 IF (g_asn_debug = 'Y') THEN
318 asn_debug.put_line('WMS Deploy Mode is LSP');
319 asn_debug.put_line('Updating rt.receipt_confirmation_extracted flag to P');
320 END IF;
321
322 UPDATE rcv_transactions rt
323 SET rt.receipt_confirmation_extracted = 'P'
324 WHERE rt.shipment_header_id = grh.shipment_header_id
325 AND nvl(rt.receipt_confirmation_extracted, 'N') = 'N'
326 AND (rt.transaction_type IN ('DELIVER') OR
327 (rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
328 AND EXISTS (SELECT '1' FROM rcv_transactions rt2
329 WHERE rt.parent_transaction_id = rt2.transaction_id
330 AND rt2.transaction_type = 'DELIVER')
331 ) OR
332 (rt.TRANSACTION_TYPE IN ('CORRECT')
333 AND EXISTS (SELECT '1' FROM rcv_transactions rt3
334 WHERE rt.parent_transaction_id = rt3.transaction_id
335 AND rt3.transaction_type = 'RETURN TO RECEIVING')
336 )
337 )
338 AND EXISTS (SELECT '1' FROM rcv_shipment_lines rsl
339 WHERE rsl.shipment_line_id = rt.shipment_line_id
340 AND wms_deploy.get_client_code(rsl.item_id) = p_client_code);
341
342 ELSE
343
344 IF (g_asn_debug = 'Y') THEN
345 asn_debug.put_line('WMS Deploy Mode is Distributed');
346 asn_debug.put_line('Updating rt.receipt_confirmation_extracted flag to P');
347 END IF;
348
349
350 UPDATE rcv_transactions rt
351 SET rt.receipt_confirmation_extracted = 'P'
352 WHERE rt.shipment_header_id = grh.shipment_header_id
353 AND nvl(rt.receipt_confirmation_extracted, 'N') = 'N'
354 AND (rt.transaction_type IN ('DELIVER') OR
355 (rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
356 AND EXISTS (SELECT '1' FROM rcv_transactions rt2
357 WHERE rt.parent_transaction_id = rt2.transaction_id
358 AND rt2.transaction_type = 'DELIVER')
359 ) OR
360 (rt.TRANSACTION_TYPE IN ('CORRECT')
361 AND EXISTS (SELECT '1' FROM rcv_transactions rt3
362 WHERE rt.parent_transaction_id = rt3.transaction_id
363 AND rt3.transaction_type = 'RETURN TO RECEIVING')
364 )
365 );
366
367 END IF;
368
369 COMMIT;
370
371 END IF;
372
373
374 RCV_TRANSACTIONS_UTIL2.Send_Document(
375 p_entity_id => grh.shipment_header_id,
376 p_entity_type => 'RCPT',
377 p_action_type => 'A',
378 p_document_type => 'RC',
379 p_organization_id => p_org_id,
380 p_client_code => p_client_code,
381 p_xml_document_id => p_xml_doc_id,
382 x_return_status => l_return_status);
383
384 IF (g_asn_debug = 'Y') THEN
385 asn_debug.put_line('Send_Document.l_return_status is ' || l_return_status);
386 asn_debug.put_line('Exiting Send_Document call');
387 END IF;
388
389 IF (l_return_status <> rcv_error_pkg.g_ret_sts_success) THEN
390 IF (xml_doc_id is null) THEN
391
392 IF (l_wms_deployment_mode = 'L') then
393
394 IF (g_asn_debug = 'Y') THEN
395 asn_debug.put_line('WMS Deploy Mode is LSP');
396 asn_debug.put_line('Resetting rt.receipt_confirmation_extracted flag to null');
397 END IF;
398
399 UPDATE rcv_transactions rt
400 SET rt.receipt_confirmation_extracted = null,
401 rt.xml_document_id = null
402 WHERE rt.shipment_header_id = grh.shipment_header_id
403 AND nvl(rt.receipt_confirmation_extracted, 'N') = 'P'
404 AND (rt.transaction_type IN ('DELIVER') OR
405 (rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
406 AND EXISTS (SELECT '1' FROM rcv_transactions rt2
407 WHERE rt.parent_transaction_id = rt2.transaction_id
408 AND rt2.transaction_type = 'DELIVER')
409 ) OR
410 (rt.TRANSACTION_TYPE IN ('CORRECT')
411 AND EXISTS (SELECT '1' FROM rcv_transactions rt3
412 WHERE rt.parent_transaction_id = rt3.transaction_id
413 AND rt3.transaction_type = 'RETURN TO RECEIVING')
414 )
415 )
416 AND EXISTS (SELECT '1' FROM rcv_shipment_lines rsl
417 WHERE rsl.shipment_line_id = rt.shipment_line_id
418 AND wms_deploy.get_client_code(rsl.item_id) = p_client_code);
419
420 ELSE
421
422 IF (g_asn_debug = 'Y') THEN
423 asn_debug.put_line('WMS Deploy Mode is Distributed');
424 asn_debug.put_line('Resetting rt.receipt_confirmation_extracted flag to null');
425 END IF;
426
427 UPDATE rcv_transactions rt
428 SET rt.receipt_confirmation_extracted = null,
429 rt.xml_document_id = null
430 WHERE rt.shipment_header_id = grh.shipment_header_id
431 AND nvl(rt.receipt_confirmation_extracted, 'N') = 'P'
432 AND (rt.transaction_type IN ('DELIVER') OR
433 (rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
434 AND EXISTS (SELECT '1' FROM rcv_transactions rt2
435 WHERE rt.parent_transaction_id = rt2.transaction_id
436 AND rt2.transaction_type = 'DELIVER')
437 ) OR
438 (rt.TRANSACTION_TYPE IN ('CORRECT')
439 AND EXISTS (SELECT '1' FROM rcv_transactions rt3
440 WHERE rt.parent_transaction_id = rt3.transaction_id
441 AND rt3.transaction_type = 'RETURN TO RECEIVING')
442 )
443 );
444
445 END IF;
446
447 END IF;
448 END IF;
449
450 END LOOP;
451
452 IF (g_asn_debug = 'Y') THEN
453 asn_debug.put_line('Exit Loop');
454 asn_debug.put_line('Exiting send_confirmation call');
455 END IF;
456
457 COMMIT;
458
459 EXCEPTION
460 WHEN OTHERS THEN
461
462 IF (g_asn_debug = 'Y') THEN
463 asn_debug.put_line('Exception : '||sqlerrm||' occurred in Send_Confirmation');
464 END IF;
465 ROLLBACK;
466
467 IF ( get_rcv_headers_1%ISOPEN) THEN
468 IF (g_asn_debug = 'Y') THEN
469 asn_debug.put_line('Closing cursor get_rcv_headers_1 in exception block');
470 END IF;
471 close get_rcv_headers_1;
472 END IF;
473
474 IF ( get_rcv_headers_2%ISOPEN) THEN
475 IF (g_asn_debug = 'Y') THEN
476 asn_debug.put_line('Closing cursor get_rcv_headers_2 in exception block');
477 END IF;
478 close get_rcv_headers_2;
479 END IF;
480
481 IF ( get_rcv_headers_3%ISOPEN) THEN
482 IF (g_asn_debug = 'Y') THEN
483 asn_debug.put_line('Closing cursor get_rcv_headers_3 in exception block');
484 END IF;
485 close get_rcv_headers_3;
486 END IF;
487
488 IF ( get_rcv_headers_4%ISOPEN) THEN
489 IF (g_asn_debug = 'Y') THEN
490 asn_debug.put_line('Closing cursor get_rcv_headers_4 in exception block');
491 END IF;
492 close get_rcv_headers_4;
493 END IF;
494
495 x_errbuf := 'Error';
496 x_retcode := 2;
497
498 END send_confirmation;
499
500
501 PROCEDURE get_ou_name(p_org_id NUMBER, p_ou_name OUT NOCOPY varchar2) IS
502 l_ou_name VARCHAR2(240);
503 BEGIN
504 SELECT organization_name
505 INTO l_ou_name
506 FROM org_organization_definitions
507 WHERE organization_id = p_org_id;
508
509 p_ou_name := l_ou_name;
510
511 EXCEPTION
512 WHEN OTHERS THEN
513 p_ou_name := NULL;
514 END;
515
516 END rcv_receipt_confirmation;
[Home] [Help]