[Home] [Help]
PACKAGE BODY: APPS.RCV_824_SV
Source
1 PACKAGE BODY RCV_824_SV AS
2 /* $Header: RCV824B.pls 120.0.12000000.2 2007/04/12 06:29:28 kagupta ship $ */
3
4 /*==================================================================*/
5 PROCEDURE RCV_824_INSERT (X_Interface_Header IN RCV_ROI_PREPROCESSOR.header_rec_type,
6 X_Type IN VARCHAR2) IS
7
8 /* This is for creating the lines row for 824 */
9
10 cursor get_header_error_rows is
11 select error_message, error_message_name
12 from po_interface_errors pie
13 where
14 pie.interface_header_id = X_interface_header.header_record.header_interface_id and
15 pie.interface_line_id is null;
16
17 /* This is for creating the lines row for 824 */
18 /* Bug 2533087 - The nvl on interface_header_id was causing a performance issue as
19 index was not being used which resulted in a full table scan on po_interface_errors.
20 The interface_header_id can be null only in the case of error messages inserted
21 for transactions that are processed thru forms.
22 But we generate Application advices for errors that occur while processing
23 the ROI. Not for errors generated thru forms. So we can remove the nvl condition.
24 Also added a condition pie.interface_header_id is not null. */
25
26
27 cursor get_line_error_rows is
28 select item_num, document_num, document_line_num,
29 barcode_label, error_message, error_message_name
30 from rcv_transactions_interface rti, po_interface_errors pie
31 where pie.interface_line_id = rti.interface_transaction_id and
32 pie.interface_header_id =
33 X_interface_header.header_record.header_interface_id and
34 pie.interface_line_id is not null and
35 pie.interface_header_id is not null;
36
37 /* NWANG 9/4/97 */
38 CURSOR get_receipt_line_rows IS
39 SELECT msi.concatenated_segments, poh.segment1, pol.line_num, rsl.bar_code_label,
40 error_message, error_message_name
41 FROM po_interface_errors pie, rcv_shipment_lines rsl ,
42 mtl_system_items_kfv msi, po_headers poh, po_lines pol
43 WHERE pol.po_line_id = rsl.po_line_id
44 AND poh.po_header_id = rsl.po_header_id
45 AND msi.inventory_item_id (+)= rsl.item_id
46 AND NVL(msi.organization_id, rsl.to_organization_id) = rsl.to_organization_id
47 AND pie.interface_line_id = rsl.shipment_line_id
48 AND NVL(pie.interface_header_id , X_interface_header.header_record.header_interface_id) =
49 X_interface_header.header_record.header_interface_id
50 AND pie.interface_line_id is not null;
51
52
53 X_progress VARCHAR2(3);
54 X_compl_code VARCHAR2(1);
55
56 x_api_version_number number := 1;
57 x_return_status varchar2(25);
58 x_msg_count number;
59 x_msg_data varchar2(80);
60 x_communication_method varchar2(10) := 'EDI';
61 x_related_document_id varchar2(10) := 'ASNI';
62 x_tp_header_id number;
63 x_tp_location_code varchar2(35);
64 x_document_type varchar2(25) := 'ADVO';
65 x_document_code varchar2(25) := null;
66 x_entity_code varchar2(30) := null;
67 x_entity_name varchar2(80) := null;
68 --<UTF8 FPI START>
69 x_entity_address1 po_vendor_sites.address_line1%type := null;
70 x_entity_address2 po_vendor_sites.address_line2%type := null;
71 x_entity_address3 po_vendor_sites.address_line3%type := null;
72 x_entity_address4 varchar2(60) := null;
73 x_entity_city varchar2(30) := null;
74 x_entity_postal_code varchar2(30) := null;
75 x_entity_country po_vendor_sites.country%type := null;
76 x_entity_state po_vendor_sites.state%type := null;
77 x_entity_province po_vendor_sites.province%type := null;
78 x_entity_county varchar2(30) := null;
79 --<UTF8 FPI END>
80 x_external_reference_1 varchar2(30) := null;
81 x_external_reference_2 varchar2(30) := null;
82 x_external_reference_3 varchar2(30) := null;
83 x_external_reference_4 varchar2(30) := null;
84 x_external_reference_5 varchar2(30) := null;
85 x_external_reference_6 varchar2(30) := null;
86 x_internal_reference_1 varchar2(30) := null;
87 x_internal_reference_2 varchar2(30) := null;
88 x_internal_reference_3 varchar2(30) := null;
89 x_internal_reference_4 varchar2(30) := null;
90 x_internal_reference_5 varchar2(30) := null;
91 x_internal_reference_6 varchar2(30) := null;
92 x_advice_status_code varchar2(30) := null;
93 x_advo_message_code varchar2(60) := null;
94 x_advo_message_desc varchar2(240) := null; -- bug 669568
95 x_advo_data_bad varchar2(60) := null;
96 x_advo_data_good varchar2(60) := null;
97
98 x_advice_header_id number;
99
100 BEGIN
101 x_progress := '001';
102
103 asn_debug.put_line('I am in 824 API');
104
105 -- bug 569723 external_reference_1 should be the shipment number.
106
107 x_external_reference_1 := X_interface_header.header_record.shipment_num;
108
109 /* 2700139 - The internal reference1 is populated with shipment num for
110 advice header creation. */
111
112 x_internal_reference_1 := X_interface_header.header_record.shipment_num;
113
114 -- bug 569723 the rest should be null
115 /* x_external_reference_2 := X_interface_header.header_record.transaction_type;
116 x_external_reference_3 := X_interface_header.header_record.vendor_name;
117 x_external_reference_4 := X_interface_header.header_record.vendor_site_code;
118 x_external_reference_5 := X_interface_header.header_record.invoice_num;
119 x_external_reference_6 := X_interface_header.header_record.processing_status_code; */
120 x_entity_code := X_interface_header.header_record.vendor_num;
121 x_entity_name := X_interface_header.header_record.vendor_name;
122
123 asn_debug.put_line('Vendor Site Id ' || to_char(X_interface_header.header_record.vendor_site_id));
124 asn_debug.put_line('Vendor Site Code ' || X_interface_header.header_record.vendor_site_code);
125
126 if X_interface_header.header_record.vendor_site_id is not null then
127
128 asn_debug.put_line('Getting vendor_site information from vendor_site_id');
129
130 begin
131 select tp_header_id, address_line1, address_line2, address_line3,
132 city, zip, country, state, province, ece_tp_location_code
133 into x_tp_header_id, x_entity_address1, x_entity_address2, x_entity_address3,
134 x_entity_city, x_entity_postal_code, x_entity_country, x_entity_state,
135 x_entity_province, x_tp_location_code
136 from po_vendor_sites where po_vendor_sites.vendor_site_id = X_interface_header.header_record.vendor_site_id;
137
138 asn_debug.put_line('TP header id ' || to_char(x_tp_header_id));
139
140 exception
141
142 when others then
143 asn_debug.put_line('Unable to locate vendor site record');
144
145 end;
146 else
147
148 asn_debug.put_line('Need to handle vendor_site_code is not null and vendor_site_id is null');
149
150 end if;
151
152
153 asn_debug.put_line('Calling the create advice header ');
154
155 asn_debug.put_line('p_api_version_number ' || x_api_version_number);
156 asn_debug.put_line('p_communication_method ' || x_communication_method);
157 asn_debug.put_line('p_related_document_id ' || x_related_document_id);
158 asn_debug.put_line('p_tp_header_id ' || x_tp_header_id);
159 asn_debug.put_line('p_tp_location_code ' || x_tp_location_code);
160 asn_debug.put_line('p_document_type ' || x_document_type);
161 asn_debug.put_line('p_document_code ' || x_document_code);
162 asn_debug.put_line('p_entity_code ' || x_entity_code);
163 asn_debug.put_line('p_entity_name ' || x_entity_name);
164 asn_debug.put_line('p_entity_address1 ' || x_entity_address1);
165 asn_debug.put_line('p_entity_address2 ' || x_entity_address2);
166 asn_debug.put_line('p_entity_address3 ' || x_entity_address3);
167 asn_debug.put_line('p_entity_address4 ' || x_entity_address4);
168 asn_debug.put_line('p_entity_city ' || x_entity_city);
169 asn_debug.put_line('p_entity_postal_code ' || x_entity_postal_code);
170 asn_debug.put_line('p_entity_country ' || x_entity_country);
171 asn_debug.put_line('p_entity_state ' || x_entity_state);
172 asn_debug.put_line('p_entity_province ' || x_entity_province);
173 asn_debug.put_line('p_entity_county ' || x_entity_county);
174 asn_debug.put_line('p_external_reference_1 ' || x_external_reference_1);
175 asn_debug.put_line('p_external_reference_2 ' || x_external_reference_2);
176 asn_debug.put_line('p_external_reference_3 ' || x_external_reference_3);
177 asn_debug.put_line('p_external_reference_4 ' || x_external_reference_4);
178 asn_debug.put_line('p_external_reference_5 ' || x_external_reference_5);
179 asn_debug.put_line('p_external_reference_6 ' || x_external_reference_6);
180 asn_debug.put_line('p_internal_reference_1 ' || x_internal_reference_1);
181 asn_debug.put_line('p_internal_reference_2 ' || x_internal_reference_2);
182 asn_debug.put_line('p_internal_reference_3 ' || x_internal_reference_3);
183 asn_debug.put_line('p_internal_reference_4 ' || x_internal_reference_4);
184 asn_debug.put_line('p_internal_reference_5 ' || x_internal_reference_5);
185 asn_debug.put_line('p_internal_reference_6 ' || x_internal_reference_6);
186
187
188
189 EC_APPLICATION_ADVICE_PUB.create_advice (p_api_version_number => x_api_version_number,
190 p_return_status => x_return_status,
191 p_msg_count => x_msg_count,
192 p_msg_data => x_msg_data,
193 p_communication_method => x_communication_method,
194 p_related_document_id => x_related_document_id,
195 p_tp_header_id => x_tp_header_id,
196 p_tp_location_code => x_tp_location_code,
197 p_document_type => x_document_type,
198 p_document_code => x_document_code,
199 p_entity_code => x_entity_code,
200 p_entity_name => x_entity_name,
201 p_entity_address1 => x_entity_address1,
202 p_entity_address2 => x_entity_address2,
203 p_entity_address3 => x_entity_address3,
204 p_entity_address4 => x_entity_address4,
205 p_entity_city => x_entity_city,
206 p_entity_postal_code => x_entity_postal_code,
207 p_entity_country => x_entity_country,
208 p_entity_state => x_entity_state,
209 p_entity_province => x_entity_province,
210 p_entity_county => x_entity_county,
211 p_external_reference_1 => x_external_reference_1,
212 p_external_reference_2 => x_external_reference_2,
213 p_external_reference_3 => x_external_reference_3,
214 p_external_reference_4 => x_external_reference_4,
215 p_external_reference_5 => x_external_reference_5,
216 p_external_reference_6 => x_external_reference_6,
217 --bug 569723 the rest of the internal reference should just be NULL
218 p_internal_reference_1 => x_internal_reference_1,
219 p_internal_reference_2 => x_internal_reference_2,
220 p_internal_reference_3 => x_internal_reference_3,
221 p_internal_reference_4 => x_internal_reference_4,
222 p_internal_reference_5 => x_internal_reference_5,
223 p_internal_reference_6 => x_internal_reference_6,
224 p_advice_header_id => x_advice_header_id);
225
226 asn_debug.put_line('Returned Advice header id ' || to_char(x_advice_header_id));
227 asn_debug.put_line('Return Status ' || x_return_status);
228 asn_debug.put_line('msg count ' || to_char(x_msg_count));
229 asn_debug.put_line('msg data ' || x_msg_data);
230
231 -- 824 header level errors
232
233 for gher in get_header_error_rows loop
234
235 asn_debug.put_line('Calling the advice lines api for header errors');
236
237 /* 2700139 - The internal reference1 is populated with to_char(0) as external reference1
238 for advice line creation. */
239 x_internal_reference_1 := to_char(0);
240
241
242 x_external_reference_1 := to_char(0); -- bug 569723, use 0 for header error;
243 /* x_external_reference_1 := X_interface_header.header_record.shipment_num;
244 x_external_reference_2 := X_interface_header.header_record.vendor_name;
245 x_external_reference_3 := X_interface_header.header_record.vendor_site_code;
246 x_external_reference_4 := X_interface_header.header_record.invoice_num;
247 x_external_reference_5 := X_interface_header.header_record.freight_carrier_code;
248 x_external_reference_6 := null; */
249
250 x_advice_status_code := 'FATAL';
251 x_advo_message_code := gher.error_message_name;
252 x_advo_message_desc := substr(gher.error_message,1,240); -- need this to be 2000
253
254
255 EC_APPLICATION_ADVICE_PUB.create_advice_line (p_api_version_number => x_api_version_number,
256 p_return_status => x_return_status,
260 p_advice_date_time => sysdate,
257 p_msg_count => x_msg_count,
258 p_msg_data => x_msg_data,
259 p_advice_header_id => x_advice_header_id,
261 p_advice_status_code => x_advice_status_code,
262 p_external_reference_1 => x_external_reference_1,
263 p_external_reference_2 => x_external_reference_2,
264 p_external_reference_3 => x_external_reference_3,
265 p_external_reference_4 => x_external_reference_4,
266 p_external_reference_5 => x_external_reference_5,
267 p_external_reference_6 => x_external_reference_6,
268 p_internal_reference_1 => x_internal_reference_1,
269 p_internal_reference_2 => x_internal_reference_2,
270 p_internal_reference_3 => x_internal_reference_3,
271 p_internal_reference_4 => x_internal_reference_4,
272 p_internal_reference_5 => x_internal_reference_5,
273 p_internal_reference_6 => x_internal_reference_6,
274 p_advo_message_code => x_advo_message_code,
275 p_advo_message_desc => x_advo_message_desc,
276 p_advo_data_bad => x_advo_data_bad,
277 p_advo_data_good => x_advo_data_good);
278
279 asn_debug.put_line('Return Status ' || x_return_status);
280 asn_debug.put_line('msg count ' || to_char(x_msg_count));
281 asn_debug.put_line('msg data ' || x_msg_data);
282
283 end loop;
284
285 -- 824 line level errors
286
287 /* NWANG 9-4-1997 */
288 if (X_Type = 'DISCREPANT_SHIPMENT') then
289
290 asn_debug.put_line('in discrepant_shipment');
291 for grlr in get_receipt_line_rows loop
292
293 x_external_reference_1 := grlr.concatenated_segments;
294 x_external_reference_2 := grlr.segment1;
295 x_external_reference_3 := grlr.line_num;
296 x_external_reference_4 := grlr.bar_code_label;
297 x_external_reference_5 := X_interface_header.header_record.shipment_num;
298 x_external_reference_6 := X_interface_header.header_record.invoice_num;
299 x_advice_status_code := 'WARNING';
300 x_advo_message_code := substr(grlr.error_message_name,1,60); -- this should be 2000
301 x_advo_message_desc := substr(grlr.error_message,1,240);
302
303 /* 2700139 - The internal reference1 is populated with document line num for
304 advice line creation. */
305
306 x_internal_reference_1 := grlr.line_num;
307
308
309
310 asn_debug.put_line(' before create line');
311
312 asn_debug.put_line('line_api_version_number ' || x_api_version_number);
313 asn_debug.put_line('line_advice_header_id ' || x_advice_header_id);
314 asn_debug.put_line('line_advice_status_code ' || x_advice_status_code);
315 asn_debug.put_line('line_external_reference_1 ' || x_external_reference_1);
316 asn_debug.put_line('line_external_reference_2 ' || x_external_reference_2);
317 asn_debug.put_line('line_external_reference_3 ' || x_external_reference_3);
318 asn_debug.put_line('line_external_reference_4 ' || x_external_reference_4);
319 asn_debug.put_line('line_external_reference_5 ' || x_external_reference_5);
320 asn_debug.put_line('line_external_reference_6 ' || x_external_reference_6);
321 asn_debug.put_line('line_internal_reference_1 ' || x_internal_reference_1);
322 asn_debug.put_line('line_internal_reference_2 ' || x_internal_reference_2);
323 asn_debug.put_line('line_internal_reference_3 ' || x_internal_reference_3);
324 asn_debug.put_line('line_internal_reference_4 ' || x_internal_reference_4);
325 asn_debug.put_line('line_internal_reference_5 ' || x_internal_reference_5);
326 asn_debug.put_line('line_internal_reference_6 ' || x_internal_reference_6);
327 asn_debug.put_line('line_advo_message_code ' || x_advo_message_code);
328 asn_debug.put_line('line_advo_message_desc ' || x_advo_message_desc);
329 asn_debug.put_line('line_advo_data_bad ' || x_advo_data_bad);
330 asn_debug.put_line('line_advo_data_goog ' || x_advo_data_good);
331
332 EC_APPLICATION_ADVICE_PUB.create_advice_line (p_api_version_number => x_api_version_number,
333 p_return_status => x_return_status,
334 p_msg_count => x_msg_count,
335 p_msg_data => x_msg_data,
336 p_advice_header_id => x_advice_header_id,
337 p_advice_date_time => sysdate,
338 p_advice_status_code => x_advice_status_code,
339 p_external_reference_1 => x_external_reference_1,
343 p_external_reference_5 => x_external_reference_5,
340 p_external_reference_2 => x_external_reference_2,
341 p_external_reference_3 => x_external_reference_3,
342 p_external_reference_4 => x_external_reference_4,
344 p_external_reference_6 => x_external_reference_6,
345 p_internal_reference_1 => x_internal_reference_1,
346 p_internal_reference_2 => x_internal_reference_2,
347 p_internal_reference_3 => x_internal_reference_3,
348 p_internal_reference_4 => x_internal_reference_4,
349 p_internal_reference_5 => x_internal_reference_5,
350 p_internal_reference_6 => x_internal_reference_6,
351 p_advo_message_code => x_advo_message_code,
352 p_advo_message_desc => x_advo_message_desc,
353 p_advo_data_bad => x_advo_data_bad,
354 p_advo_data_good => x_advo_data_good);
355
356 asn_debug.put_line('Return Status -- ' || x_return_status);
357 asn_debug.put_line('msg count -- ' || to_char(x_msg_count));
358 asn_debug.put_line('msg data -- ' || x_msg_data);
359 end loop;
360
361 else
362 for gler in get_line_error_rows loop
363
364 x_external_reference_1 := gler.document_line_num; -- bug 569723, use line number to indicate
365 -- line errors
366 /* Added for bug 5769886
367 Description : To show the Document number also in the ece_advo_details table */
368 x_external_reference_2 := gler.document_num;
369 /* x_external_reference_3 := gler.document_line_num;
370 x_external_reference_4 := gler.barcode_label;
371 x_external_reference_5 := X_interface_header.header_record.shipment_num;
372 x_external_reference_6 := X_interface_header.header_record.invoice_num; */
373 x_advice_status_code := 'FATAL';
374 x_advo_message_code := substr(gler.error_message_name,1,80); -- this should be 2000
375 x_advo_message_desc := substr(gler.error_message,1,240); -- bug 669568
376
377 /* 2700139 - The internal reference1 is populated with document line num for
378 advice line creation. */
379
380 x_internal_reference_1 := gler.document_line_num;
381
382
383 EC_APPLICATION_ADVICE_PUB.create_advice_line (p_api_version_number => x_api_version_number,
384 p_return_status => x_return_status,
385 p_msg_count => x_msg_count,
386 p_msg_data => x_msg_data,
387 p_advice_header_id => x_advice_header_id,
388 p_advice_date_time => sysdate,
389 p_advice_status_code => x_advice_status_code,
390 p_external_reference_1 => x_external_reference_1,
391 p_external_reference_2 => x_external_reference_2,
392 p_external_reference_3 => x_external_reference_3,
393 p_external_reference_4 => x_external_reference_4,
394 p_external_reference_5 => x_external_reference_5,
395 p_external_reference_6 => x_external_reference_6,
396 p_internal_reference_1 => x_internal_reference_1,
397 p_internal_reference_2 => x_internal_reference_2,
398 p_internal_reference_3 => x_internal_reference_3,
399 p_internal_reference_4 => x_internal_reference_4,
400 p_internal_reference_5 => x_internal_reference_5,
401 p_internal_reference_6 => x_internal_reference_6,
402 p_advo_message_code => x_advo_message_code,
403 p_advo_message_desc => x_advo_message_desc,
404 p_advo_data_bad => x_advo_data_bad,
405 p_advo_data_good => x_advo_data_good);
406
407 asn_debug.put_line('Return Status ' || x_return_status);
408 asn_debug.put_line('msg count ' || to_char(x_msg_count));
409 asn_debug.put_line('msg data ' || x_msg_data);
410 end loop;
411 end if;
412
413 EXCEPTION
414 WHEN others THEN
415 asn_debug.put_line('RCV_824_S.RCV_824_INSERT ' || sqlcode);
416 END RCV_824_INSERT;
417
418 PROCEDURE rcv_824_insert(
419 x_interface_header IN rcv_shipment_header_sv.headerrectype,
420 x_type IN VARCHAR2
421 ) IS
422 x_temp rcv_roi_preprocessor.header_rec_type;
423 BEGIN
424 x_temp.header_record := x_interface_header.header_record;
425 x_temp.error_record.error_status := x_interface_header.error_record.error_status;
426 x_temp.error_record.error_message := x_interface_header.error_record.error_message;
427 rcv_824_insert(x_temp,x_type);
428 END rcv_824_insert;
429
430 END RCV_824_SV;