1 PACKAGE BODY RCV_CALL_LCM_WS AS
2 /* $Header: RCVLCMIB.pls 120.0.12010000.3 2008/10/30 20:04:08 musinha noship $ */
3
4 PROCEDURE insertLCM( x_errbuf OUT NOCOPY VARCHAR2
5 ,x_retcode OUT NOCOPY NUMBER) IS
6
7 cursor c_rsh_rti is
8 select *
9 from rcv_transactions_interface rti
10 where transaction_status_code = 'PENDING'
11 and processing_status_code = 'LC_PENDING'
12 and (transaction_type = 'RECEIVE' or
13 (transaction_type ='SHIP' and auto_transact_code in ('RECEIVE','DELIVER'))
14 )
15 and source_document_code = 'PO'
16 and exists ( select 'lcm shipment'
17 from po_line_locations_all pll
18 where pll.line_location_id = rti.po_line_location_id
19 and pll.lcm_flag = 'Y'
20 )
21 and shipment_header_id IS NOT NULL
22 order by shipment_header_id;
23
24
25
26 cursor c_rhi_rti is
27 select *
28 from rcv_transactions_interface rti
29 where transaction_status_code = 'PENDING'
30 and processing_status_code = 'LC_PENDING'
31 and (transaction_type = 'RECEIVE' or
32 (transaction_type ='SHIP' and auto_transact_code in ('RECEIVE','DELIVER'))
33 )
34 and source_document_code = 'PO'
35 and exists ( select 'lcm shipment'
36 from po_line_locations_all pll
37 where pll.line_location_id = rti.po_line_location_id
38 and pll.lcm_flag = 'Y'
39 )
40 and shipment_header_id IS NULL
41 and header_interface_id IS NOT NULL
42 order by header_interface_id;
43
44
45 l_ret BOOLEAN;
46 p_rti_rec rti_rec;
47
48 BEGIN
49
50 asn_debug.put_line('Entering RCV_CALL_LCM_WS.insertLCM' || to_char(sysdate,'DD-MON-YYYY HH:MI:SS'));
51
52 open c_rsh_rti;
53 fetch c_rsh_rti BULK COLLECT INTO p_rti_rec;
54
55 asn_debug.put_line('No of rows from RSH to be inserted: ' || p_rti_rec.COUNT);
56
57
58 IF p_rti_rec.first IS NOT NULL THEN
59
60 asn_debug.put_line('calling LCM API for RSH');
61
62 INL_INTEGRATION_GRP.Import_FromRCV(p_rti_rec);
63
64 asn_debug.put_line('after calling LCM API for RSH: ' || p_rti_rec.COUNT);
65
66 END IF;
67
68 asn_debug.put_line('before closing the RSH cursor');
69
70 IF c_rsh_rti%ISOPEN THEN
71 CLOSE c_rsh_rti;
72 END IF;
73
74 p_rti_rec.delete;
75
76 open c_rhi_rti;
77
78 asn_debug.put_line('fetching the RHI cursor');
79
80 fetch c_rhi_rti BULK COLLECT INTO p_rti_rec;
81
82
83 asn_debug.put_line('No of rows from RHI to be inserted: ' || p_rti_rec.COUNT);
84
85 IF p_rti_rec.first IS NOT NULL THEN
86
87 asn_debug.put_line('calling LCM API for RHI' || p_rti_rec.COUNT);
88
89 INL_INTEGRATION_GRP.Import_FromRCV(p_rti_rec);
90
91 asn_debug.put_line(' after calling LCM API for RHI' || p_rti_rec.COUNT);
92
93 END IF;
94
95 asn_debug.put_line('before closing the RHI cursor');
96 IF c_rhi_rti%ISOPEN THEN
97 CLOSE c_rhi_rti;
98 END IF;
99
100 l_ret := fnd_concurrent.set_completion_status('NORMAL', 'Success');
101
102 x_retcode := 0;
103 x_errbuf := 'Success';
104
105
106 EXCEPTION
107 WHEN OTHERS THEN
108
109 IF c_rsh_rti%ISOPEN THEN
110 CLOSE c_rsh_rti;
111 END IF;
112
113 IF c_rhi_rti%ISOPEN THEN
114 CLOSE c_rhi_rti;
115 END IF;
116
117 asn_debug.put_line('the error is: ' || sqlcode ||' '||substr(SQLERRM, 1, 1000));
118
119 l_ret := fnd_concurrent.set_completion_status('ERROR', 'Error');
120
121 x_retcode := 2;
122 x_errbuf := 'Error';
123
124 END insertLCM;
125
126 END RCV_CALL_LCM_WS;
127
[Home] [Help]