1 PACKAGE BODY RCV_FTE_CALL_PVT AS
2 /* $Header: RCVFTECB.pls 120.2 2006/03/21 15:35:51 pparthas noship $ */
3
4 PROCEDURE call_fte(
5 p_action IN VARCHAR2,
6 p_shipment_header_id IN NUMBER,
7 p_shipment_line_id IN NUMBER DEFAULT NULL,
8 p_interface_id IN NUMBER DEFAULT NULL)
9 IS
10 l_is_asn VARCHAR2(25);
11 l_has_receipt NUMBER;
12
13 l_action VARCHAR2(255);
14 l_txn_id NUMBER;
15 l_txn_type VARCHAR2(255);
16 l_rcv_txn VARCHAR2(255);
17
18 l_valid_lines NUMBER;
19
20 l_return_status VARCHAR2(255);
21 l_msg_count NUMBER;
22 l_msg_data VARCHAR2(255);
23 BEGIN
24
25 asn_debug.put_line('action: ' || p_action || ' header_id: ' || p_shipment_header_id || ' line_id: ' || p_shipment_line_id || ' interface_id: ' || p_interface_id);
26
27 -- If this is a regular RECEIPT_ADD transaction,
28 -- we just need to note it
29
30 IF (WSH_UTIL_CORE.FTE_IS_INSTALLED <> 'Y') THEN
31 asn_debug.put_line('RCV_FTE_CALL_PVT.call_fte: fte not installed, skipping fte call');
32 RETURN;
33 END IF;
34
35 IF (p_action = 'RECEIPT_ADD')
36 THEN
37 asn_debug.put_line('action: '|| p_action);
38 rcv_fte_txn_lines_pvt.insert_row(
39 p_shipment_header_id,
40 p_shipment_line_id,
41 l_txn_id,
42 p_action,
43 'Y');
44 RETURN;
45 END IF;
46
47 l_action := p_action;
48
49 IF (l_action = 'ASN')
50 THEN l_rcv_txn := 'SHIP';
51 ELSIF (l_action = 'RTV')
52 THEN l_rcv_txn := 'RETURN TO VENDOR';
53 ELSIF (l_action = 'MATCH')
54 THEN l_rcv_txn := 'MATCH';
55 ELSIF (l_action = 'RECEIPT')
56 THEN l_rcv_txn := 'RECEIVE';
57 ELSIF (l_action = 'CORRECT')
58 THEN l_rcv_txn := 'CORRECT';
59 ELSIF (l_action = 'CANCEL_ASN')
60 THEN l_rcv_txn := 'CANCEL';
61 END IF;
62
63 IF (p_interface_id IS NOT NULL AND l_rcv_txn <> 'CANCEL')
64 THEN
65 SELECT transaction_id
66 INTO l_txn_id
67 FROM rcv_transactions
68 WHERE shipment_header_id = p_shipment_header_id
69 AND transaction_type = l_rcv_txn
70 AND interface_transaction_id = p_interface_id
71 AND rownum = 1;
72 END IF;
73
74 IF (p_action = 'CORRECT') THEN
75 SELECT parent.transaction_type
76 INTO l_txn_type
77 FROM rcv_transactions parent, rcv_transactions child
78 WHERE child.transaction_id = l_txn_id
79 AND parent.transaction_id = child.parent_transaction_id;
80
81 IF (l_txn_type = 'RECEIVE') THEN
82 l_action := 'RECEIPT_CORRECTION';
83 ELSIF (l_txn_type = 'RETURN TO VENDOR') THEN
84 l_action := 'RTV_CORRECTION';
85 ELSE RETURN;
86 END IF;
87 END IF;
88
89 IF(p_action IN ('RECEIPT', 'SHIP', 'MATCH')) THEN
90 SELECT count(rsl.shipment_line_id)
91 INTO l_valid_lines
92 FROM rcv_shipment_lines rsl,
93 rcv_shipment_headers rsh,
94 po_headers_all poh,
95 po_lines_all pol,
96 po_line_types_b plb
97 WHERE
98 rsh.shipment_header_id = p_shipment_header_id
99 AND (rsl.shipment_line_id = p_shipment_line_id OR p_shipment_line_id IS NULL)
100 AND rsl.shipment_header_id = rsh.shipment_header_id
101 AND rsl.po_header_id = poh.po_header_id
102 AND rsl.po_line_id = pol.po_line_id
103 AND poh.type_lookup_code IN ('STANDARD', 'BLANKET')
104 AND poh.shipping_control IS NOT NULL
105 AND pol.line_type_id = plb.line_type_id
106 AND plb.order_type_lookup_code = 'QUANTITY';
107
108 IF (l_valid_lines = 0)
109 THEN RETURN;
110 END IF;
111 END IF;
112
113 IF (p_action = 'RECEIPT') THEN
114 -- Check for RECEIPT_ADD
115 -- If this is a Receipt against an ASN
116 -- And there is an existing Receipt against an ASN
117 -- Then this should be a RECEIPT_ADD
118 -- Rather than a 'RECEIPT'
119 SELECT asn_type
120 INTO l_is_asn
121 FROM rcv_shipment_headers
122 WHERE shipment_header_id = p_shipment_header_id;
123
124 IF (l_is_asn IS NOT NULL)
125 THEN
126 SELECT count(*)
127 INTO l_has_receipt
128 FROM rcv_fte_transaction_lines
129 WHERE action = 'RECEIPT'
130 AND reported_flag = 'Y'
131 AND header_id = p_shipment_header_id;
132
133 IF (l_has_receipt > 0)
134 THEN
135 l_action := 'RECEIPT_ADD';
136 END IF; -- there is an existing receipt
137 END IF; -- this is a receipt against ASN
138
139 -- One more check for RECEIPT_ADD
140 -- If there is a RECEIPT_ADD transaction
141 -- with the same header_id as this receipt,
142 -- then this is a RECEIPT_ADD
143 SELECT count(*)
144 INTO l_has_receipt
145 FROM rcv_fte_transaction_lines
146 WHERE action = 'RECEIPT_ADD'
147 AND header_id = p_shipment_header_id;
148
149 IF (l_has_receipt > 0)
150 THEN
151 l_action := 'RECEIPT_ADD';
152 END IF;
153 END IF; -- this is a receipt
154
155 IF(p_action = 'MATCH') THEN
156 -- This will be represented as a Match
157 l_action := 'MATCH';
158 END IF;
159
160
161 rcv_fte_txn_lines_pvt.insert_row(
162 p_shipment_header_id,
163 p_shipment_line_id,
164 l_txn_id,
165 l_action);
166
167 IF(l_action IN ('RTV_CORRECTION', 'RECEIPT_CORRECTION', 'RTV'))
168 THEN
169 po_delrec_pvt.create_update_delrec(
170 1.0,
171 l_return_status,
172 l_msg_count,
173 l_msg_data,
174 l_action,
175 'RCV',
176 l_action,
177 p_shipment_header_id,
178 p_shipment_line_id,
179 null);
180 asn_debug.put_line('action: ' || p_action || 'l_action: ' || l_action || ' header_id: ' || p_shipment_header_id || ' line_id: ' || p_shipment_line_id || ' output: ' || l_return_status);
181 asn_debug.put_line('error msg: ' || l_msg_data);
182 IF (l_return_status = 'S')
183 THEN
184 rcv_fte_txn_lines_pvt.update_record_to_reported(
185 p_shipment_header_id,
186 p_shipment_line_id,
187 l_action);
188 ELSE
189 rcv_fte_txn_lines_pvt.update_record_to_failed(
190 p_shipment_header_id,
191 p_shipment_line_id,
192 l_action);
193 END IF;
194 END IF;
195
196 asn_debug.put_line('action: ' || p_action || 'header_id: ' || p_shipment_header_id || ' line_id: ' || p_shipment_line_id || ' interface_id: ' || p_interface_id);
197
198
199 END call_fte;
200
201 PROCEDURE aggregate_calls
202 IS
203 l_return_status VARCHAR2(255);
204 l_msg_count NUMBER;
205 l_msg_data VARCHAR2(2000);
206 CURSOR all_calls IS
207 SELECT a.header_id, a.action
208 FROM rcv_fte_transaction_lines a, rcv_fte_transaction_lines b
209 WHERE a.reported_flag IN ('N','U')
210 AND a.header_id = b.header_id
211 AND b.reported_flag = 'N'
212 GROUP BY a.header_id, a.action;
213 BEGIN
214 -- INSERT INTO ben_test VALUES ('IN AG CALLS ', SYSDATE);
215 FOR call IN all_calls
216 LOOP
217 po_delrec_pvt.create_update_delrec(
218 1.0,
219 l_return_status,
220 l_msg_count,
221 l_msg_data,
222 call.action,
223 'RCV',
224 call.action,
225 call.header_id,
226 null,
227 null);
228 asn_debug.put_line('action: ' || call.action || ' header_id: ' || call.header_id || ' output: ' || l_return_status);
229 asn_debug.put_line('error msg: ' || l_msg_data);
230
231 IF (l_return_status = 'S')
232 THEN
233 rcv_fte_txn_lines_pvt.update_record_to_reported(
234 call.header_id,
235 null,
236 call.action);
237 ELSE
238 rcv_fte_txn_lines_pvt.update_record_to_unreported(
239 call.header_id,
240 null,
241 call.action);
242 END IF;
243 END LOOP;
244 END aggregate_calls;
245
246 END RCV_FTE_CALL_PVT;
[Home] [Help]