1 PACKAGE BODY rcv_transactions_interface_sv AS
2 /* $Header: RCVTISVB.pls 120.3 2007/12/12 19:47:04 vthevark ship $*/
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 cascaded_table rcv_shipment_object_sv.cascaded_trans_tab_type;
7 transaction_record rcv_shipment_line_sv.transaction_record_type;
8 item_id_record rcv_shipment_line_sv.item_id_record_type;
9 document_num_record rcv_shipment_line_sv.document_num_record_type;
10 release_id_record rcv_shipment_line_sv.release_id_record_type;
11 po_line_id_record rcv_shipment_line_sv.po_line_id_record_type;
12 po_line_location_id_record rcv_shipment_line_sv.po_line_location_id_rtype;
13 ship_to_org_record rcv_shipment_object_sv.organization_id_record_type;
14 organization_id_record rcv_shipment_object_sv.organization_id_record_type;
15 intransit_owning_org_record rcv_shipment_line_sv.intransit_owning_org_rtype;
16 location_id_record rcv_shipment_object_sv.location_id_record_type;
17 sub_item_id_record rcv_shipment_line_sv.sub_item_id_record_type;
18 category_id_record rcv_shipment_line_sv.category_id_record_type;
19 employee_id_record rcv_shipment_object_sv.employee_id_record_type;
20 routing_header_id_record rcv_shipment_line_sv.routing_header_id_rec_type;
21 routing_step_id_record rcv_shipment_line_sv.routing_step_id_rec_type;
22 deliver_to_person_id_record rcv_shipment_line_sv.deliver_to_person_id_rtype;
23 locator_id_record rcv_shipment_line_sv.locator_id_record_type;
24 reason_id_record rcv_shipment_line_sv.reason_id_record_type;
25 quantity_shipped_record rcv_shipment_line_sv.quantity_shipped_record_type;
26 expected_receipt_record rcv_shipment_line_sv.expected_receipt_record_type;
27 quantity_invoiced_record rcv_shipment_line_sv.quantity_invoiced_record_type;
28 ref_integrity_record rcv_shipment_line_sv.ref_integrity_record_type;
29 asl_record rcv_shipment_line_sv.ref_integrity_record_type;
30 freight_carrier_record rcv_shipment_line_sv.freight_carrier_record_type;
31 tax_name_record rcv_shipment_line_sv.tax_name_record_type;
32 --FRKHAN 12/18/98 add record type for country of origin
33 country_of_origin_record rcv_shipment_line_sv.country_of_origin_record_type;
34 vendor_record rcv_shipment_header_sv.vendorrectype;
35 vendor_site_record rcv_shipment_header_sv.vendorsiterectype;
36 cum_quantity_record rcv_shipment_line_sv.cum_quantity_record_type;
37 uom_record rcv_shipment_line_sv.quantity_shipped_record_type;
38 employee_record rcv_shipment_line_sv.employee_record_type;
39 po_lookup_code_record rcv_shipment_line_sv.po_lookup_code_record_type;
40 location_record rcv_shipment_line_sv.location_record_type;
41 subinventory_record rcv_shipment_line_sv.subinventory_record_type;
42 locator_record rcv_shipment_line_sv.locator_record_type;
43 item_revision_record rcv_shipment_line_sv.item_id_record_type;
44 /* <Consigned Inventory Pre-Processor FPI START> */
45 l_consigned_po_rec rcv_shipment_line_sv.po_line_location_id_rtype;
46 l_consumption_po_rec rcv_shipment_line_sv.document_num_record_type;
47 l_consumption_release_rec rcv_shipment_line_sv.release_id_record_type;
48 e_validation_error EXCEPTION;
49
50 /* <Consigned Inventory Pre-Processor FPI END> */
51
52
53 /*===========================================================================
54
55 PROCEDURE NAME: derive_shipment_line()
56
57 ===========================================================================*/
58 PROCEDURE derive_shipment_line(
59 x_cascaded_table IN OUT NOCOPY rcv_shipment_object_sv.cascaded_trans_tab_type,
60 n IN OUT NOCOPY BINARY_INTEGER,
61 temp_cascaded_table IN OUT NOCOPY rcv_shipment_object_sv.cascaded_trans_tab_type,
62 x_header_record IN rcv_shipment_header_sv.headerrectype
63 ) IS
64 /*
65 ** Debug: Needed to add all the columns selected in the distributions cursor
66 ** so the definition of the shipments and distributions cursors were
67 ** identical.
68 */
69 /* 1887728 - IN ASN closed for receiving PO's were also being
70 received . In the Enter Receipts form the closed for
71 receiving PO's can be received only if Include Closed PO
72 profile option is set . Modified the cursors shipments,
73 count shipments, distributions,count distributions
74 to restrict the shipments and distributions based on the
75 profile option.
76 The fnd_profile.get_specfic(x,y,z,w) returns the value
77 of profile option starting from user. If there is no value
78 at the user value ,then the value at responsibility
79 level is returned and so on. */
80 x_include_closed_po VARCHAR2(1); -- Bug 1887728
81
82 CURSOR shipments(
83 header_id NUMBER,
84 v_item_id NUMBER,
85 v_po_line_num NUMBER,
86 v_po_release_id NUMBER,
87 v_shipment_num NUMBER,
88 v_ship_to_org_id NUMBER,
89 v_ship_to_location_id NUMBER,
90 v_vendor_product_num VARCHAR2
91 ) IS
92 SELECT pll.line_location_id,
93 pll.unit_meas_lookup_code,
94 pll.unit_of_measure_class,
95 NVL(pll.promised_date, pll.need_by_date) promised_date,
96 pll.ship_to_organization_id,
97 pll.quantity quantity_ordered,
98 pll.quantity_shipped,
99 pll.receipt_days_exception_code,
100 pll.qty_rcv_tolerance,
101 pll.qty_rcv_exception_code,
102 pll.days_early_receipt_allowed,
103 pll.days_late_receipt_allowed,
104 NVL(pll.price_override, pl.unit_price) unit_price,
105 pll.match_option, -- 1845702
106 pl.category_id,
107 pl.item_description,
108 pl.po_line_id,
109 ph.currency_code,
110 ph.rate_type, -- 1845702
111 0 po_distribution_id,
112 0 code_combination_id,
113 0 req_distribution_id,
114 0 deliver_to_location_id,
115 0 deliver_to_person_id,
116 ph.rate_date rate_date, --1845702
117 ph.rate rate, --1845702
118 '' destination_type_code,
119 0 destination_organization_id,
120 '' destination_subinventory,
121 0 wip_entity_id,
122 0 wip_operation_seq_num,
123 0 wip_resource_seq_num,
124 0 wip_repetitive_schedule_id,
125 0 wip_line_id,
126 0 bom_resource_id,
127 '' ussgl_transaction_code,
128 pll.ship_to_location_id,
129 NVL(pll.enforce_ship_to_location_code, 'NONE') enforce_ship_to_location_code,
130 pl.item_id
131 FROM po_line_locations pll,
132 po_lines pl,
133 po_headers ph
134 WHERE ph.po_header_id = header_id
135 AND pll.po_header_id = header_id
136 AND pl.line_num = NVL(v_po_line_num, pl.line_num)
137 AND NVL(pll.po_release_id, 0) = NVL(v_po_release_id, NVL(pll.po_release_id, 0))
138 AND pll.shipment_num = NVL(v_shipment_num, pll.shipment_num)
139 AND pll.po_line_id = pl.po_line_id
140 AND NVL(pl.item_id, 0) = NVL(v_item_id, NVL(pl.item_id, 0)) -- v_item_id could be null
141 AND NVL(pll.approved_flag, 'N') = 'Y'
142 AND NVL(pll.cancel_flag, 'N') = 'N'
143 AND ( ( NVL(x_include_closed_po, 'N') = 'Y'
144 AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED')
145 OR ( NVL(x_include_closed_po, 'N') = 'N'
146 AND (NVL(pll.closed_code, 'OPEN') NOT IN('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING'))))
147 AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED')
148 AND pll.ship_to_organization_id = NVL(v_ship_to_org_id, pll.ship_to_organization_id)
149 AND pll.ship_to_location_id = NVL(v_ship_to_location_id, pll.ship_to_location_id)
150 AND NVL(pl.vendor_product_num, '-999') = NVL(v_vendor_product_num, NVL(pl.vendor_product_num, '-999'))
151 ORDER BY NVL(pll.promised_date, pll.need_by_date);
152
153 CURSOR count_shipments(
154 header_id NUMBER,
155 v_item_id NUMBER,
156 v_po_line_num NUMBER,
157 v_po_release_id NUMBER,
158 v_shipment_num NUMBER,
159 v_ship_to_org_id NUMBER,
160 v_ship_to_location_id NUMBER,
161 v_vendor_product_num VARCHAR2
162 ) IS
163 SELECT COUNT(*)
164 FROM po_line_locations pll,
165 po_lines pl,
166 po_headers ph
167 WHERE ph.po_header_id = header_id
168 AND pll.po_header_id = header_id
169 AND pl.line_num = NVL(v_po_line_num, pl.line_num)
170 AND NVL(pll.po_release_id, 0) = NVL(v_po_release_id, NVL(pll.po_release_id, 0))
171 AND pll.shipment_num = NVL(v_shipment_num, pll.shipment_num)
172 AND pll.po_line_id = pl.po_line_id
173 AND NVL(pl.item_id, 0) = NVL(v_item_id, NVL(pl.item_id, 0)) -- v_item_id could be null
174 AND NVL(pll.approved_flag, 'N') = 'Y'
175 AND NVL(pll.cancel_flag, 'N') = 'N'
176 AND ( ( NVL(x_include_closed_po, 'N') = 'Y'
177 AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED')
178 OR ( NVL(x_include_closed_po, 'N') = 'N'
179 AND (NVL(pll.closed_code, 'OPEN') NOT IN('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING'))))
180 AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED')
181 AND pll.ship_to_organization_id = NVL(v_ship_to_org_id, pll.ship_to_organization_id)
182 AND pll.ship_to_location_id = NVL(v_ship_to_location_id, pll.ship_to_location_id)
183 AND NVL(pl.vendor_product_num, '-999') = NVL(v_vendor_product_num, NVL(pl.vendor_product_num, '-999'));
184
185 /***** Bug # 1553154
186 ***** There was a performance issue since the cursor COUNT_DISTRIBUTIONS
187 ***** was driving through PO_LINE_LOCATIONS_ALL. Modified the Select
188 ***** statement so that it will drive through PO_HEADERS_ALL
189 ***** followed by PO_LINES_ALL which is followed by PO_LINE_LOCATIONS_ALL
190 ***** so that there is an improvement in performance.
191 *****/
192 CURSOR distributions(
193 header_id NUMBER,
194 v_item_id NUMBER,
195 v_po_line_num NUMBER,
196 v_po_release_id NUMBER,
197 v_shipment_num NUMBER,
198 v_distribution_num NUMBER,
199 v_ship_to_org_id NUMBER,
200 v_ship_to_location_id NUMBER,
201 v_vendor_product_num VARCHAR2
202 ) IS
203 SELECT pll.line_location_id,
204 pll.unit_meas_lookup_code,
205 pll.unit_of_measure_class,
206 NVL(pll.promised_date, pll.need_by_date) promised_date,
207 pll.ship_to_organization_id,
208 pll.quantity quantity_ordered,
209 pll.quantity_shipped,
210 pll.receipt_days_exception_code,
211 pll.qty_rcv_tolerance,
212 pll.qty_rcv_exception_code,
213 pll.days_early_receipt_allowed,
214 pll.days_late_receipt_allowed,
215 NVL(pll.price_override, pl.unit_price) unit_price,
216 pll.match_option, -- 1845702
217 pl.category_id,
218 pl.item_description,
219 pl.po_line_id,
220 ph.currency_code,
221 ph.rate_type, -- 1845702
222 pod.po_distribution_id,
223 pod.code_combination_id,
224 pod.req_distribution_id,
225 pod.deliver_to_location_id,
226 pod.deliver_to_person_id,
227 pod.rate_date,
228 pod.rate,
229 pod.destination_type_code,
230 pod.destination_organization_id,
231 pod.destination_subinventory,
232 pod.wip_entity_id,
233 pod.wip_operation_seq_num,
234 pod.wip_resource_seq_num,
235 pod.wip_repetitive_schedule_id,
236 pod.wip_line_id,
237 pod.bom_resource_id,
238 pod.ussgl_transaction_code,
239 pll.ship_to_location_id,
240 NVL(pll.enforce_ship_to_location_code, 'NONE') enforce_ship_to_location_code,
241 pl.item_id
242 FROM po_distributions pod,
243 po_line_locations pll,
244 po_lines pl,
245 po_headers ph
246 WHERE ph.po_header_id = header_id
247 AND pl.po_header_id = ph.po_header_id
248 AND pll.po_line_id = pl.po_line_id
249 AND pod.line_location_id = pll.line_location_id
250 AND pl.line_num = NVL(v_po_line_num, pl.line_num)
251 AND NVL(pll.po_release_id, 0) = NVL(v_po_release_id, NVL(pll.po_release_id, 0))
252 AND pll.shipment_num = NVL(v_shipment_num, pll.shipment_num)
253 AND NVL(pl.item_id, 0) = NVL(v_item_id, NVL(pl.item_id, 0)) -- v_item_id could be null
254 AND NVL(pll.approved_flag, 'N') = 'Y'
255 AND NVL(pll.cancel_flag, 'N') = 'N'
256 AND ( ( NVL(x_include_closed_po, 'N') = 'Y'
257 AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED')
258 OR ( NVL(x_include_closed_po, 'N') = 'N'
259 AND (NVL(pll.closed_code, 'OPEN') NOT IN('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING'))))
260 AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED')
261 AND pod.distribution_num = NVL(v_distribution_num, pod.distribution_num)
262 AND pll.ship_to_organization_id = NVL(v_ship_to_org_id, pll.ship_to_organization_id)
263 AND pll.ship_to_location_id = NVL(v_ship_to_location_id, pll.ship_to_location_id)
264 AND NVL(pl.vendor_product_num, '-999') = NVL(v_vendor_product_num, NVL(pl.vendor_product_num, '-999'))
265 ORDER BY NVL(pll.promised_date, pll.need_by_date);
266
267 /***** Bug # 1553154
268 ***** There was a performance issue since the cursor DISTRIBUTIONS
269 ***** was driving through PO_HEADERS_ALL followed by PO_DISTRIBUTIONS_ALL
270 ***** Modified the Select statement so that it will drive through
271 ***** PO_HEADERS_ALL followed by PO_LINES_ALL which is followed by
272 ***** PO_LINE_LOCATIONS_ALL which in turn is followed by
273 ***** PO_DISTRIBUTIONS_ALL so that there is an improvement in
274 ***** Performance
275 *****/
276 CURSOR count_distributions(
277 header_id NUMBER,
278 v_item_id NUMBER,
279 v_po_line_num NUMBER,
280 v_po_release_id NUMBER,
281 v_shipment_num NUMBER,
282 v_distribution_num NUMBER,
283 v_ship_to_org_id NUMBER,
284 v_ship_to_location_id NUMBER,
285 v_vendor_product_num VARCHAR2
286 ) IS
287 SELECT COUNT(*)
288 FROM po_distributions pod,
289 po_line_locations pll,
290 po_lines pl,
291 po_headers ph
292 WHERE ph.po_header_id = header_id
293 AND pl.po_header_id = ph.po_header_id
294 AND pll.po_line_id = pl.po_line_id
295 AND pod.line_location_id = pll.line_location_id
296 AND pl.line_num = NVL(v_po_line_num, pl.line_num)
297 AND NVL(pll.po_release_id, 0) = NVL(v_po_release_id, NVL(pll.po_release_id, 0))
298 AND pll.shipment_num = NVL(v_shipment_num, pll.shipment_num)
299 AND pod.distribution_num = NVL(v_distribution_num, pod.distribution_num)
300 AND NVL(pl.item_id, 0) = NVL(v_item_id, NVL(pl.item_id, 0)) -- v_item_id could be null
301 AND NVL(pll.approved_flag, 'N') = 'Y'
302 AND NVL(pll.cancel_flag, 'N') = 'N'
303 AND ( ( NVL(x_include_closed_po, 'N') = 'Y'
304 AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED')
305 OR ( NVL(x_include_closed_po, 'N') = 'N'
306 AND (NVL(pll.closed_code, 'OPEN') NOT IN('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING'))))
307 AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED')
308 AND pll.ship_to_organization_id = NVL(v_ship_to_org_id, pll.ship_to_organization_id)
309 AND pll.ship_to_location_id = NVL(v_ship_to_location_id, pll.ship_to_location_id)
310 AND NVL(pl.vendor_product_num, '-999') = NVL(v_vendor_product_num, NVL(pl.vendor_product_num, '-999'));
311
312 /*
313 ** Debug: had to change this to the distribution record
314 ** Might be a compatibility issue between the two record definitions
315 */
316 x_shipmentdistributionrec distributions%ROWTYPE;
317 x_record_count NUMBER;
318 x_remaining_quantity NUMBER := 0;
319 x_remaining_qty_po_uom NUMBER := 0;
320 x_bkp_qty NUMBER := 0;
321 x_progress VARCHAR2(3);
322 x_to_organization_code VARCHAR2(5);
323 x_converted_trx_qty NUMBER := 0;
324 transaction_ok BOOLEAN := FALSE;
325 x_expected_date rcv_transactions_interface.expected_receipt_date%TYPE;
326 high_range_date DATE;
327 low_range_date DATE;
328 rows_fetched NUMBER := 0;
329 x_tolerable_qty NUMBER := 0;
330 x_first_trans BOOLEAN := TRUE;
331 x_sysdate DATE := SYSDATE;
332 current_n BINARY_INTEGER := 0;
333 insert_into_table BOOLEAN := FALSE;
334 x_qty_rcv_exception_code po_line_locations.qty_rcv_exception_code%TYPE;
335 tax_amount_factor NUMBER;
336 lastrecord BOOLEAN := FALSE;
337 po_asn_uom_qty NUMBER;
338 po_primary_uom_qty NUMBER;
339 already_allocated_qty NUMBER := 0;
340 x_item_id NUMBER;
341 x_approved_flag VARCHAR(1);
342 x_cancel_flag VARCHAR(1);
343 x_closed_code VARCHAR(25);
344 x_shipment_type VARCHAR(25);
345 x_ship_to_organization_id NUMBER;
346 x_ship_to_location_id NUMBER;
347 /* temp_ship_to_location_id number;
348 temp_mirror_ship_to_loc_id number ;
349 temp_enf_ship_to_loc_code varchar(25) ; */
350 /* The above 3 variables added for bug 1898283 */
351 x_vendor_product_num VARCHAR(25);
352 x_temp_count NUMBER;
353 x_full_name VARCHAR2(240) := NULL; -- Bug 2392074
354 /* 1887728 -Added the following variables */
355 profile_user_id NUMBER := -1;
356 profile_appl_id NUMBER := -1;
357 profile_resp_id NUMBER := -1;
358 defined BOOLEAN;
359 /* 1845702 */
360 x_sob_id NUMBER := NULL;
361 x_rate NUMBER;
362 x_allow_rate_override VARCHAR2(1);
363 /* Bug# 1548597 */
364 x_secondary_available_qty NUMBER := 0;
365 BEGIN
366 IF (g_asn_debug = 'Y') THEN
367 asn_debug.put_line('Enter derive_shipment_line');
368 asn_debug.put_line('Current pointer in actual table ' || TO_CHAR(n));
369 asn_debug.put_line('Current error status ' || x_cascaded_table(n).error_status);
370 asn_debug.put_line('To Organization Id ' || NVL(TO_CHAR(x_cascaded_table(n).to_organization_id), 'DUH'));
371 asn_debug.put_line('To Organization Code ' || NVL(x_cascaded_table(n).to_organization_code, 'XMA'));
372 END IF;
373
374 /* 1887728- Getting the profile option value based on the user_id,
375 resp_id,appl_id
376 */
377 profile_user_id := fnd_profile.VALUE('USER_ID');
378 profile_resp_id := fnd_profile.VALUE('RESPONSIBILITY_ID');
379 profile_appl_id := fnd_profile.VALUE('APPLICATION_ID');
380 fnd_profile.get_specific('RCV_CLOSED_PO_DEFAULT_OPTION',
381 profile_user_id,
382 profile_resp_id,
383 profile_appl_id,
384 x_include_closed_po,
385 defined
386 );
387
388 IF (g_asn_debug = 'Y') THEN
389 asn_debug.put_line('Include closed PO profile value ' || x_include_closed_po);
390 END IF;
391
392 x_progress := '000';
393
394 -- default org from header in case it is null at the line level
395
396 IF x_cascaded_table(n).to_organization_code IS NULL
397 AND x_cascaded_table(n).error_status IN('S', 'W') THEN
398 IF (g_asn_debug = 'Y') THEN
399 asn_debug.put_line('Attempting to default the org from the ship to location');
400 END IF;
401
402 IF (x_cascaded_table(n).ship_to_location_code IS NOT NULL) THEN
403 SELECT MAX(org.organization_code)
404 INTO x_to_organization_code
405 FROM hr_locations hl,
406 mtl_parameters org -- Bugfix 5217098
407 WHERE x_cascaded_table(n).ship_to_location_code = hl.location_code
408 AND hl.inventory_organization_id = org.organization_id;
409
410 x_cascaded_table(n).to_organization_code := x_to_organization_code;
411
412 IF (g_asn_debug = 'Y') THEN
413 asn_debug.put_line('Set Org Code using location code = ' || x_cascaded_table(n).to_organization_code);
414 END IF;
415 END IF;
416
417 IF (x_cascaded_table(n).to_organization_code IS NULL) THEN
418 IF (g_asn_debug = 'Y') THEN
419 asn_debug.put_line('Will default org change DUH to ' || x_header_record.header_record.ship_to_organization_code);
420 END IF;
421
422 x_cascaded_table(n).to_organization_code := x_header_record.header_record.ship_to_organization_code;
423 END IF;
424 END IF;
425
426 -- call derivation procedures if conditions are met
427
428 IF (x_cascaded_table(n).error_status IN('S', 'W'))
429 AND ( x_cascaded_table(n).to_organization_id IS NULL
430 AND x_cascaded_table(n).to_organization_code IS NOT NULL) THEN
431 IF (g_asn_debug = 'Y') THEN
432 asn_debug.put_line('X_Progress ' || x_progress);
433 END IF;
434
435 ship_to_org_record.organization_code := x_cascaded_table(n).to_organization_code;
436 ship_to_org_record.organization_id := x_cascaded_table(n).to_organization_id;
437 ship_to_org_record.error_record.error_status := 'S';
438 ship_to_org_record.error_record.error_message := NULL;
439
440 IF (g_asn_debug = 'Y') THEN
441 asn_debug.put_line('Into Derive Organization Record Procedure');
442 END IF;
443
444 po_orgs_sv.derive_org_info(ship_to_org_record);
445
446 IF (g_asn_debug = 'Y') THEN
447 asn_debug.put_line('Debug Output after organization procedure');
448 asn_debug.put_line(ship_to_org_record.organization_code);
449 asn_debug.put_line(TO_CHAR(ship_to_org_record.organization_id));
450 asn_debug.put_line(ship_to_org_record.error_record.error_status);
451 asn_debug.put_line('Debug organization output over');
452 END IF;
453
454 x_cascaded_table(n).to_organization_code := ship_to_org_record.organization_code;
455 x_cascaded_table(n).to_organization_id := ship_to_org_record.organization_id;
456 x_cascaded_table(n).error_status := ship_to_org_record.error_record.error_status;
457 rcv_error_pkg.set_error_message(ship_to_org_record.error_record.error_message, x_cascaded_table(n).error_message);
458 END IF;
459
460 /* Derive Vendor Information */
461 x_progress := '002';
462
463 IF (g_asn_debug = 'Y') THEN
464 asn_debug.put_line('X_progress ' || x_progress);
465 END IF;
466
467 IF (x_cascaded_table(n).error_status IN('S', 'W')) THEN
468 IF ( x_cascaded_table(n).vendor_name IS NOT NULL
469 OR x_cascaded_table(n).vendor_num IS NOT NULL
470 OR x_cascaded_table(n).vendor_id IS NOT NULL) THEN
471 vendor_record.vendor_name := x_cascaded_table(n).vendor_name;
472 vendor_record.vendor_num := x_cascaded_table(n).vendor_num;
473 vendor_record.vendor_id := x_cascaded_table(n).vendor_id;
474 vendor_record.error_record.error_message := x_cascaded_table(n).error_message;
475 vendor_record.error_record.error_status := x_cascaded_table(n).error_status;
476
477 IF (g_asn_debug = 'Y') THEN
478 asn_debug.put_line('In Vendor Procedure');
479 END IF;
480
481 po_vendors_sv.derive_vendor_info(vendor_record);
482
483 IF (g_asn_debug = 'Y') THEN
484 asn_debug.put_line(TO_CHAR(vendor_record.vendor_id));
485 asn_debug.put_line(vendor_record.vendor_name);
486 asn_debug.put_line(vendor_record.vendor_num);
487 asn_debug.put_line(vendor_record.error_record.error_status);
488 asn_debug.put_line(vendor_record.error_record.error_message);
489 END IF;
490
491 x_cascaded_table(n).vendor_name := vendor_record.vendor_name;
492 x_cascaded_table(n).vendor_num := vendor_record.vendor_num;
493 x_cascaded_table(n).vendor_id := vendor_record.vendor_id;
494 rcv_error_pkg.set_error_message(vendor_record.error_record.error_message, x_cascaded_table(n).error_message);
495 x_cascaded_table(n).error_status := vendor_record.error_record.error_status;
496 END IF;
497 END IF;
498
499 /* derive vendor site information */
500 /* Call derive vendor_site_procedure here */
501 /* UK1 -> vendor_site_id
502 UK2 -> vendor_site_code + vendor_id + org_id */
503 x_progress := '004';
504
505 IF (g_asn_debug = 'Y') THEN
506 asn_debug.put_line('X_progress ' || x_progress);
507 END IF;
508
509 IF x_cascaded_table(n).error_status IN('S', 'W')
510 AND ( x_cascaded_table(n).vendor_site_code IS NOT NULL
511 OR x_cascaded_table(n).vendor_site_id IS NOT NULL) THEN
512 vendor_site_record.vendor_site_code := x_cascaded_table(n).vendor_site_code;
513 vendor_site_record.vendor_id := x_cascaded_table(n).vendor_id;
514 vendor_site_record.vendor_site_id := x_cascaded_table(n).vendor_site_id;
515 vendor_site_record.organization_id := x_cascaded_table(n).to_organization_id;
516 vendor_site_record.error_record.error_message := x_cascaded_table(n).error_message;
517 vendor_site_record.error_record.error_status := x_cascaded_table(n).error_status;
518
519 IF (g_asn_debug = 'Y') THEN
520 asn_debug.put_line('In Vendor Site Procedure');
521 END IF;
522
523 po_vendor_sites_sv.derive_vendor_site_info(vendor_site_record);
524
525 IF (g_asn_debug = 'Y') THEN
526 asn_debug.put_line(vendor_site_record.vendor_site_code);
527 asn_debug.put_line(vendor_site_record.vendor_site_id);
528 END IF;
529
530 x_cascaded_table(n).vendor_site_code := vendor_site_record.vendor_site_code;
531 x_cascaded_table(n).vendor_id := vendor_site_record.vendor_id;
532 x_cascaded_table(n).vendor_site_id := vendor_site_record.vendor_site_id;
533 x_cascaded_table(n).to_organization_id := vendor_site_record.organization_id;
534 rcv_error_pkg.set_error_message(vendor_site_record.error_record.error_message, x_cascaded_table(n).error_message);
535 x_cascaded_table(n).error_status := vendor_site_record.error_record.error_status;
536 END IF;
537
538 x_progress := '005';
539
540 IF (g_asn_debug = 'Y') THEN
541 asn_debug.put_line('X_progress ' || x_progress);
542 END IF;
543
544 IF (x_cascaded_table(n).error_status IN('S', 'W'))
545 AND ( x_cascaded_table(n).po_header_id IS NULL
546 AND x_cascaded_table(n).document_num IS NOT NULL) THEN
547 IF (g_asn_debug = 'Y') THEN
548 asn_debug.put_line('X_progress ' || x_progress);
549 END IF;
550
551 document_num_record.document_num := x_cascaded_table(n).document_num;
552 document_num_record.error_record.error_status := 'S';
553 document_num_record.error_record.error_message := NULL;
554
555 IF (g_asn_debug = 'Y') THEN
556 asn_debug.put_line('Derive po_header_id');
557 END IF;
558
559 rcv_transactions_interface_sv.get_po_header_id(document_num_record);
560 x_cascaded_table(n).po_header_id := document_num_record.po_header_id;
561 x_cascaded_table(n).error_status := document_num_record.error_record.error_status;
562 rcv_error_pkg.set_error_message(document_num_record.error_record.error_message, x_cascaded_table(n).error_message);
563
564 IF (g_asn_debug = 'Y') THEN
565 asn_debug.put_line(TO_CHAR(x_cascaded_table(n).po_header_id));
566 END IF;
567
568 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
569 'DOCUMENT_NUM',
570 FALSE
571 );
572 END IF;
573
574 x_progress := '010';
575
576 IF (x_cascaded_table(n).error_status IN('S', 'W'))
577 AND ( x_cascaded_table(n).item_id IS NULL
578 AND (x_cascaded_table(n).item_num IS NOT NULL)) THEN
579 IF (g_asn_debug = 'Y') THEN
580 asn_debug.put_line('X_progress ' || x_progress);
581 END IF;
582
583 item_id_record.item_num := x_cascaded_table(n).item_num;
584 item_id_record.vendor_item_num := NULL; -- x_cascaded_table(n).vendor_item_num;
585 item_id_record.to_organization_id := x_cascaded_table(n).to_organization_id;
586 item_id_record.error_record.error_status := 'S';
587 item_id_record.error_record.error_message := NULL;
588
589 IF (g_asn_debug = 'Y') THEN
590 asn_debug.put_line('Derive item_id');
591 END IF;
592
593 rcv_transactions_interface_sv.get_item_id(item_id_record);
594 x_cascaded_table(n).item_id := item_id_record.item_id;
595 x_cascaded_table(n).primary_unit_of_measure := item_id_record.primary_unit_of_measure;
596 x_cascaded_table(n).use_mtl_lot := item_id_record.use_mtl_lot; -- bug 608353
597 x_cascaded_table(n).use_mtl_serial := item_id_record.use_mtl_serial; -- bug 608353
598 x_cascaded_table(n).error_status := item_id_record.error_record.error_status;
599 rcv_error_pkg.set_error_message(item_id_record.error_record.error_message, x_cascaded_table(n).error_message);
600 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
601 'ITEM_NUM',
602 FALSE
603 );
604 END IF;
605
606 /*
607 ** DEBUG: Primary UOM is not being set
608 */
609 /* x_cascaded_table(n).primary_unit_of_measure := 'Each'; */
610
611 /*
612 ** DEBUG: Need to set the employee_id from the header
613 */
614 x_progress := '015';
615
616 IF (x_cascaded_table(n).error_status IN('S', 'W'))
617 AND ( x_cascaded_table(n).substitute_item_id IS NULL
618 AND (x_cascaded_table(n).substitute_item_num IS NOT NULL)) THEN
619 IF (g_asn_debug = 'Y') THEN
620 asn_debug.put_line('X_progress ' || x_progress);
621 END IF;
622
623 sub_item_id_record.substitute_item_num := x_cascaded_table(n).substitute_item_num;
624 sub_item_id_record.vendor_item_num := NULL; -- x_cascaded_table(n).vendor_item_num;
625 sub_item_id_record.error_record.error_status := 'S';
626 sub_item_id_record.error_record.error_message := NULL;
627
628 IF (g_asn_debug = 'Y') THEN
629 asn_debug.put_line('Derive Substitute Item Id');
630 END IF;
631
632 rcv_transactions_interface_sv.get_sub_item_id(sub_item_id_record);
633 x_cascaded_table(n).substitute_item_id := sub_item_id_record.substitute_item_id;
634 x_cascaded_table(n).error_status := sub_item_id_record.error_record.error_status;
635 rcv_error_pkg.set_error_message(sub_item_id_record.error_record.error_message, x_cascaded_table(n).error_message);
636 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
637 'SUBSTITUTE_ITEM_NUM',
638 FALSE
639 );
640
641 IF (g_asn_debug = 'Y') THEN
642 asn_debug.put_line(TO_CHAR(x_cascaded_table(n).substitute_item_id));
643 END IF;
644 END IF;
645
646 x_progress := '020';
647
648 IF (x_cascaded_table(n).error_status IN('S', 'W'))
649 AND ( x_cascaded_table(n).po_line_id IS NULL
650 AND x_cascaded_table(n).po_header_id IS NOT NULL
651 AND x_cascaded_table(n).document_line_num IS NOT NULL) THEN
652 IF (g_asn_debug = 'Y') THEN
653 asn_debug.put_line('X_progress ' || x_progress);
654 END IF;
655
656 po_line_id_record.po_header_id := x_cascaded_table(n).po_header_id;
657 po_line_id_record.document_line_num := x_cascaded_table(n).document_line_num;
658 po_line_id_record.po_line_id := x_cascaded_table(n).po_line_id;
659 po_line_id_record.item_id := x_cascaded_table(n).item_id;
660 po_line_id_record.error_record.error_status := 'S';
661 po_line_id_record.error_record.error_message := NULL;
662
663 IF (g_asn_debug = 'Y') THEN
664 asn_debug.put_line('Derive po_line_id');
665 END IF;
666
667 rcv_transactions_interface_sv.get_po_line_id(po_line_id_record);
668
669 IF x_cascaded_table(n).item_id IS NULL THEN
670 x_cascaded_table(n).item_id := po_line_id_record.item_id;
671 END IF;
672
673 x_cascaded_table(n).po_line_id := po_line_id_record.po_line_id;
674 x_cascaded_table(n).error_status := po_line_id_record.error_record.error_status;
675 rcv_error_pkg.set_error_message(po_line_id_record.error_record.error_message, x_cascaded_table(n).error_message);
676 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
677 'DOCUMENT_LINE_NUM',
678 FALSE
679 );
680
681 IF (g_asn_debug = 'Y') THEN
682 asn_debug.put_line(TO_CHAR(x_cascaded_table(n).po_line_id));
683 END IF;
684 END IF;
685
686 -- Get the primary uom in case item_id was determined on the basis of the po_line_id
687
688 IF (x_cascaded_table(n).error_status IN('S', 'W'))
689 AND x_cascaded_table(n).item_id IS NOT NULL
690 AND x_cascaded_table(n).primary_unit_of_measure IS NULL THEN
691 BEGIN
692 /* BUG 608353 */
693 SELECT primary_unit_of_measure,
694 NVL(x_cascaded_table(n).use_mtl_lot, lot_control_code),
695 NVL(x_cascaded_table(n).use_mtl_serial, serial_number_control_code)
696 INTO x_cascaded_table(n).primary_unit_of_measure,
697 x_cascaded_table(n).use_mtl_lot,
698 x_cascaded_table(n).use_mtl_serial
699 FROM mtl_system_items
700 WHERE mtl_system_items.inventory_item_id = x_cascaded_table(n).item_id
701 AND mtl_system_items.organization_id = x_cascaded_table(n).to_organization_id;
702
703 IF (g_asn_debug = 'Y') THEN
704 asn_debug.put_line('Primary UOM: ' || x_cascaded_table(n).primary_unit_of_measure);
705 END IF;
706 EXCEPTION
707 WHEN NO_DATA_FOUND THEN
708 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_warning;
709 rcv_error_pkg.set_error_message('RCV_UOM_NO_CONV_PRIMARY', x_cascaded_table(n).error_message);
710 rcv_error_pkg.set_token('PRIMARY_UNIT', '');
711 rcv_error_pkg.set_token('SHIPMENT_UNIT', '');
712 rcv_error_pkg.log_interface_warning('ITEM_ID');
713
714 IF (g_asn_debug = 'Y') THEN
715 asn_debug.put_line('Primary UOM error');
716 END IF;
717 END;
718 END IF;
719
720 x_progress := '025';
721
722 /* Bug 1830177. If the po_line_id is null then we do not populate the correct
723 * po_release_id even if we specify the release_num since we do not enter
724 * this block. Removed the condition x_cascaded_table(n).po_line_id is not null
725 */
726
727 /* Bug 2020269 : uom_code needs to be derived from unit_of_measure
728 entered in rcv_transactions_interface.
729 */
730 IF (x_cascaded_table(n).unit_of_measure IS NOT NULL) THEN
731 IF (g_asn_debug = 'Y') THEN
732 asn_debug.put_line('X_progress ' || x_progress);
733 END IF;
734
735 SELECT muom.uom_code
736 INTO x_cascaded_table(n).uom_code
737 FROM mtl_units_of_measure muom
738 WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
739 ELSE
740 IF (g_asn_debug = 'Y') THEN
741 asn_debug.put_line('uom_code not dereived as unit_of_measure is null');
742 END IF;
743 END IF;
744
745 x_progress := '026';
746
747 IF (x_cascaded_table(n).error_status IN('S', 'W'))
748 AND x_cascaded_table(n).po_release_id IS NULL
749 AND -- Maybe we need an or with shipnum,relnum
750 x_cascaded_table(n).po_header_id IS NOT NULL THEN
751 IF (g_asn_debug = 'Y') THEN
752 asn_debug.put_line('X_progress ' || x_progress);
753 END IF;
754
755 release_id_record.po_header_id := x_cascaded_table(n).po_header_id;
756 release_id_record.release_num := x_cascaded_table(n).release_num;
757 release_id_record.po_line_id := x_cascaded_table(n).po_line_id;
758 release_id_record.shipment_num := x_cascaded_table(n).document_shipment_line_num;
759 release_id_record.error_record.error_status := 'S';
760 release_id_record.error_record.error_message := NULL;
761
762 IF (g_asn_debug = 'Y') THEN
763 asn_debug.put_line('Derive po_line_location_id, shipment_num, po_release_id');
764 END IF;
765
766 IF (g_asn_debug = 'Y') THEN
767 asn_debug.put_line('### po_header_id ' || release_id_record.po_header_id);
768 asn_debug.put_line('### release_num ' || release_id_record.release_num);
769 asn_debug.put_line('### po_line_id ' || release_id_record.po_line_id);
770 asn_debug.put_line('### shipment_num ' || release_id_record.shipment_num);
771 asn_debug.put_line('### po_rel_id ' || release_id_record.po_release_id);
772 END IF;
773
774 po_releases_sv4.get_po_release_id(release_id_record);
775
776 IF (g_asn_debug = 'Y') THEN
777 asn_debug.put_line('^^^ po_header_id ' || release_id_record.po_header_id);
778 asn_debug.put_line('^^^ release_num ' || release_id_record.release_num);
779 asn_debug.put_line('^^^ po_line_id ' || release_id_record.po_line_id);
780 asn_debug.put_line('^^^ shipment_num ' || release_id_record.shipment_num);
781 asn_debug.put_line('^^^ po_rel_id ' || release_id_record.po_release_id);
782 END IF;
783
784 IF x_cascaded_table(n).po_line_location_id IS NULL THEN
785 x_cascaded_table(n).po_line_location_id := release_id_record.po_line_location_id;
786
787 IF (g_asn_debug = 'Y') THEN
788 asn_debug.put_line('PO_LINE_LOCATION_ID ' || TO_CHAR(x_cascaded_table(n).po_line_location_id));
789 END IF;
790 END IF;
791
792 IF x_cascaded_table(n).document_shipment_line_num IS NULL THEN
793 x_cascaded_table(n).document_shipment_line_num := release_id_record.shipment_num;
794
795 IF (g_asn_debug = 'Y') THEN
796 asn_debug.put_line('DOCUMENT_SHIPMENT_NUM ' || TO_CHAR(x_cascaded_table(n).document_shipment_line_num));
797 END IF;
798 END IF;
799
800 x_cascaded_table(n).po_release_id := release_id_record.po_release_id;
801 x_cascaded_table(n).error_status := release_id_record.error_record.error_status;
802 rcv_error_pkg.set_error_message(release_id_record.error_record.error_message, x_cascaded_table(n).error_message);
803
804 IF (x_cascaded_table(n).error_message = 'RCV_ITEM_PO_REL_ID') THEN
805 rcv_error_pkg.set_token('NUMBER', x_cascaded_table(n).release_num);
806 END IF;
807
808 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
809 'DOCUMENT_LINE_NUM',
810 FALSE
811 );
812
813 IF (g_asn_debug = 'Y') THEN
814 asn_debug.put_line('PO_RELEASE_ID ' || TO_CHAR(x_cascaded_table(n).po_release_id));
815 END IF;
816 END IF;
817
818 x_progress := '030';
819
820 IF (x_cascaded_table(n).error_status IN('S', 'W'))
821 AND ( x_cascaded_table(n).from_organization_id IS NULL
822 AND x_cascaded_table(n).from_organization_code IS NOT NULL) THEN
823 IF (g_asn_debug = 'Y') THEN
824 asn_debug.put_line('X_progress ' || x_progress);
825 END IF;
826
827 ship_to_org_record.organization_code := x_cascaded_table(n).from_organization_code;
828 ship_to_org_record.organization_id := x_cascaded_table(n).from_organization_id;
829 ship_to_org_record.error_record.error_status := 'S';
830 ship_to_org_record.error_record.error_message := NULL;
831
832 IF (g_asn_debug = 'Y') THEN
833 asn_debug.put_line('In From Organization Procedure');
834 END IF;
835
836 po_orgs_sv.derive_org_info(ship_to_org_record);
837
838 IF (g_asn_debug = 'Y') THEN
839 asn_debug.put_line('From organization code ' || ship_to_org_record.organization_code);
840 asn_debug.put_line('From organization id ' || TO_CHAR(ship_to_org_record.organization_id));
841 asn_debug.put_line('From organization error status ' || ship_to_org_record.error_record.error_status);
842 END IF;
843
844 x_cascaded_table(n).from_organization_code := ship_to_org_record.organization_code;
845 x_cascaded_table(n).from_organization_id := ship_to_org_record.organization_id;
846 x_cascaded_table(n).error_status := ship_to_org_record.error_record.error_status;
847 rcv_error_pkg.set_error_message(ship_to_org_record.error_record.error_message, x_cascaded_table(n).error_message);
848 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
849 'FROM_ORGANIZATION_ID',
850 FALSE
851 );
852 END IF;
853
854 x_progress := '035';
855
856 IF (x_cascaded_table(n).error_status IN('S', 'W'))
857 AND ( x_cascaded_table(n).intransit_owning_org_id IS NULL
858 AND x_cascaded_table(n).intransit_owning_org_code IS NOT NULL) THEN
859 IF (g_asn_debug = 'Y') THEN
860 asn_debug.put_line('X_progress ' || x_progress);
861 END IF;
862
863 ship_to_org_record.organization_code := x_cascaded_table(n).intransit_owning_org_code;
864 ship_to_org_record.organization_id := x_cascaded_table(n).intransit_owning_org_id;
865 ship_to_org_record.error_record.error_status := 'S';
866 ship_to_org_record.error_record.error_message := NULL;
867
868 IF (g_asn_debug = 'Y') THEN
869 asn_debug.put_line('In Intransit Owning Org Record Procedure');
870 END IF;
871
872 po_orgs_sv.derive_org_info(ship_to_org_record);
873
874 IF (g_asn_debug = 'Y') THEN
875 asn_debug.put_line('Intransit organization code ' || ship_to_org_record.organization_code);
876 asn_debug.put_line('Intransit organization id ' || TO_CHAR(ship_to_org_record.organization_id));
877 asn_debug.put_line('Intransit error status ' || ship_to_org_record.error_record.error_status);
878 END IF;
879
880 x_cascaded_table(n).intransit_owning_org_code := ship_to_org_record.organization_code;
881 x_cascaded_table(n).intransit_owning_org_id := ship_to_org_record.organization_id;
882 x_cascaded_table(n).error_status := ship_to_org_record.error_record.error_status;
883 rcv_error_pkg.set_error_message(ship_to_org_record.error_record.error_message, x_cascaded_table(n).error_message);
884 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
885 'INTRANSIT_OWNING_ORG_ID',
886 FALSE
887 );
888 END IF;
889
890 x_progress := '040';
891
892 IF (x_cascaded_table(n).error_status IN('S', 'W'))
893 AND ( x_cascaded_table(n).location_id IS NULL
894 AND x_cascaded_table(n).location_code IS NOT NULL) THEN
895 IF (g_asn_debug = 'Y') THEN
896 asn_debug.put_line('X_progress ' || x_progress);
897 END IF;
898
899 location_id_record.location_code := x_cascaded_table(n).location_code;
900 location_id_record.error_record.error_status := 'S';
901 location_id_record.error_record.error_message := NULL;
902
903 IF (g_asn_debug = 'Y') THEN
904 asn_debug.put_line('Derive location_id');
905 END IF;
906
907 rcv_transactions_interface_sv.get_location_id(location_id_record);
908 x_cascaded_table(n).location_id := location_id_record.location_id;
909 x_cascaded_table(n).error_status := location_id_record.error_record.error_status;
910 rcv_error_pkg.set_error_message(location_id_record.error_record.error_message, x_cascaded_table(n).error_message);
911 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
912 'LOCATION_CODE',
913 FALSE
914 );
915 END IF;
916
917 -- Derive ship_to_location record if information is provided at line level
918
919 x_progress := '045';
920
921 IF (x_cascaded_table(n).error_status IN('S', 'W'))
922 AND ( x_cascaded_table(n).ship_to_location_id IS NULL
923 AND x_cascaded_table(n).ship_to_location_code IS NOT NULL) THEN
924 IF (g_asn_debug = 'Y') THEN
925 asn_debug.put_line('X_progress ' || x_progress);
926 END IF;
927
928 location_id_record.location_code := x_cascaded_table(n).ship_to_location_code;
929 location_id_record.error_record.error_status := 'S';
930 location_id_record.error_record.error_message := NULL;
931
932 IF (g_asn_debug = 'Y') THEN
933 asn_debug.put_line('Derive ship to location_id');
934 END IF;
935
936 rcv_transactions_interface_sv.get_location_id(location_id_record);
937 x_cascaded_table(n).ship_to_location_id := location_id_record.location_id;
938 x_cascaded_table(n).error_status := location_id_record.error_record.error_status;
939 rcv_error_pkg.set_error_message(location_id_record.error_record.error_message, x_cascaded_table(n).error_message);
940 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
941 'SHIP_TO_LOCATION_CODE',
942 FALSE
943 );
944 END IF;
945
946 x_progress := '050';
947
948 IF (x_cascaded_table(n).error_status IN('S', 'W'))
949 AND ( x_cascaded_table(n).routing_header_id IS NULL
950 AND x_cascaded_table(n).routing_code IS NOT NULL) THEN
951 IF (g_asn_debug = 'Y') THEN
952 asn_debug.put_line('X_progress ' || x_progress);
953 END IF;
954
955 routing_header_id_record.routing_code := x_cascaded_table(n).routing_code;
956 routing_header_id_record.error_record.error_status := 'S';
957 routing_header_id_record.error_record.error_message := NULL;
958
959 IF (g_asn_debug = 'Y') THEN
960 asn_debug.put_line('Derive routing_header_id');
961 END IF;
962
963 rcv_transactions_interface_sv.get_routing_header_id(routing_header_id_record);
964 x_cascaded_table(n).routing_header_id := routing_header_id_record.routing_header_id;
965 x_cascaded_table(n).error_status := routing_header_id_record.error_record.error_status;
966 rcv_error_pkg.set_error_message(routing_header_id_record.error_record.error_message, x_cascaded_table(n).error_message);
967 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
968 'ROUTING_CODE',
969 FALSE
970 );
971 END IF;
972
973 x_progress := '070';
974
975 IF (x_cascaded_table(n).error_status IN('S', 'W'))
976 AND ( x_cascaded_table(n).routing_step_id IS NULL
977 AND x_cascaded_table(n).routing_step IS NOT NULL) THEN
978 IF (g_asn_debug = 'Y') THEN
979 asn_debug.put_line('X_progress ' || x_progress);
980 END IF;
981
982 routing_step_id_record.routing_step := x_cascaded_table(n).routing_step;
983 routing_step_id_record.error_record.error_status := 'S';
984 routing_step_id_record.error_record.error_message := NULL;
985
986 IF (g_asn_debug = 'Y') THEN
987 asn_debug.put_line('Derive routing Step Id');
988 END IF;
989
990 rcv_transactions_interface_sv.get_routing_step_id(routing_step_id_record);
991 x_cascaded_table(n).routing_step_id := routing_step_id_record.routing_step_id;
992 x_cascaded_table(n).error_status := routing_step_id_record.error_record.error_status;
993 rcv_error_pkg.set_error_message(routing_step_id_record.error_record.error_message, x_cascaded_table(n).error_message);
994 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
995 'ROUTING_STEP',
996 FALSE
997 );
998 END IF;
999
1000 x_progress := '080';
1001
1002 IF (x_cascaded_table(n).error_status IN('S', 'W'))
1003 AND ( x_cascaded_table(n).deliver_to_person_id IS NULL
1004 AND x_cascaded_table(n).deliver_to_person_name IS NOT NULL) THEN
1005 IF (g_asn_debug = 'Y') THEN
1006 asn_debug.put_line('X_progress ' || x_progress);
1007 END IF;
1008
1009 employee_id_record.employee_name := x_cascaded_table(n).deliver_to_person_name;
1010 employee_id_record.employee_id := x_cascaded_table(n).deliver_to_person_id;
1011 employee_id_record.error_record.error_status := 'S';
1012 employee_id_record.error_record.error_message := NULL;
1013
1014 IF (g_asn_debug = 'Y') THEN
1015 asn_debug.put_line('In Derive deliver_to_person_id Information');
1016 END IF;
1017
1018 po_employees_sv.derive_employee_info(employee_id_record);
1019
1020 IF (g_asn_debug = 'Y') THEN
1021 asn_debug.put_line('Employee name ' || employee_id_record.employee_name);
1022 asn_debug.put_line('Employee id ' || TO_CHAR(employee_id_record.employee_id));
1023 asn_debug.put_line('Employee error status ' || employee_id_record.error_record.error_status);
1024 END IF;
1025
1026 x_cascaded_table(n).deliver_to_person_name := employee_id_record.employee_name;
1027 x_cascaded_table(n).deliver_to_person_id := employee_id_record.employee_id;
1028 x_cascaded_table(n).error_status := employee_id_record.error_record.error_status;
1029 rcv_error_pkg.set_error_message(employee_id_record.error_record.error_message, x_cascaded_table(n).error_message);
1030 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
1031 'DELIVER_TO_PERSON_ID',
1032 FALSE
1033 );
1034 END IF;
1035
1036 x_progress := '085';
1037
1038 IF (x_cascaded_table(n).error_status IN('S', 'W'))
1039 AND ( x_cascaded_table(n).deliver_to_location_id IS NULL
1040 AND x_cascaded_table(n).deliver_to_location_code IS NOT NULL) THEN
1041 IF (g_asn_debug = 'Y') THEN
1042 asn_debug.put_line('X_progress ' || x_progress);
1043 END IF;
1044
1045 location_id_record.location_code := x_cascaded_table(n).deliver_to_location_code;
1046 location_id_record.error_record.error_status := 'S';
1047 location_id_record.error_record.error_message := NULL;
1048
1049 IF (g_asn_debug = 'Y') THEN
1050 asn_debug.put_line('Derive deliver_to_location_id');
1051 END IF;
1052
1053 rcv_transactions_interface_sv.get_location_id(location_id_record);
1054 x_cascaded_table(n).deliver_to_location_id := location_id_record.location_id;
1055 x_cascaded_table(n).error_status := location_id_record.error_record.error_status;
1056 rcv_error_pkg.set_error_message(location_id_record.error_record.error_message, x_cascaded_table(n).error_message);
1057 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
1058 'DELIVER_TO_LOCATION_CODE',
1059 FALSE
1060 );
1061 END IF;
1062
1063 x_progress := '090';
1064
1065 IF (x_cascaded_table(n).error_status IN('S', 'W'))
1066 AND ( x_cascaded_table(n).locator_id IS NULL
1067 AND x_cascaded_table(n).LOCATOR IS NOT NULL) THEN
1068 IF (g_asn_debug = 'Y') THEN
1069 asn_debug.put_line('X_progress ' || x_progress);
1070 END IF;
1071
1072 locator_id_record.LOCATOR := x_cascaded_table(n).LOCATOR;
1073 locator_id_record.subinventory := x_cascaded_table(n).subinventory;
1074 locator_id_record.to_organization_id := x_cascaded_table(n).to_organization_id;
1075 locator_id_record.error_record.error_status := 'S';
1076 locator_id_record.error_record.error_message := NULL;
1077
1078 IF (g_asn_debug = 'Y') THEN
1079 asn_debug.put_line('Derive locator_id for ' || x_cascaded_table(n).LOCATOR);
1080 asn_debug.put_line(' subinventory is ' || x_cascaded_table(n).subinventory);
1081 END IF;
1082
1083 /*
1084 * bug 724495 add derivation of locator in the preprocessor
1085 */
1086 rcv_transactions_interface_sv.get_locator_id(locator_id_record);
1087 x_cascaded_table(n).locator_id := locator_id_record.locator_id;
1088 x_cascaded_table(n).error_status := locator_id_record.error_record.error_status;
1089 rcv_error_pkg.set_error_message(locator_id_record.error_record.error_message, x_cascaded_table(n).error_message);
1090 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
1091 'LOCATOR',
1092 FALSE
1093 );
1094 END IF;
1095
1096 x_progress := '091';
1097
1098 IF (x_cascaded_table(n).error_status IN('S', 'W'))
1099 AND ( x_cascaded_table(n).reason_id IS NULL
1100 AND x_cascaded_table(n).reason_name IS NOT NULL) THEN
1101 IF (g_asn_debug = 'Y') THEN
1102 asn_debug.put_line('X_progress ' || x_progress);
1103 END IF;
1104
1105 reason_id_record.reason_name := x_cascaded_table(n).reason_name;
1106 reason_id_record.error_record.error_status := 'S';
1107 reason_id_record.error_record.error_message := NULL;
1108
1109 IF (g_asn_debug = 'Y') THEN
1110 asn_debug.put_line('Derive Reason_id');
1111 END IF;
1112
1113 rcv_transactions_interface_sv.get_reason_id(reason_id_record);
1114 x_cascaded_table(n).reason_id := reason_id_record.reason_id;
1115 x_cascaded_table(n).error_status := reason_id_record.error_record.error_status;
1116 rcv_error_pkg.set_error_message(reason_id_record.error_record.error_message, x_cascaded_table(n).error_message);
1117 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
1118 'REASON_NAME',
1119 FALSE
1120 );
1121 END IF;
1122
1123 x_progress := '092';
1124
1125 -- Derive auto_transact_code from transaction_type if it is null
1126
1127 IF (x_cascaded_table(n).error_status IN('S', 'W'))
1128 AND x_cascaded_table(n).auto_transact_code IS NULL THEN
1129 IF (g_asn_debug = 'Y') THEN
1130 asn_debug.put_line('X_progress ' || x_progress);
1131 asn_debug.put_line('Setting auto_transact_code to transaction_type ' || x_cascaded_table(n).transaction_type);
1132 END IF;
1133
1134 x_cascaded_table(n).auto_transact_code := x_cascaded_table(n).transaction_type;
1135 END IF;
1136
1137 x_progress := '093';
1138
1139 -- Change transaction type based on combination of
1140 -- transaction_type and auto_transact_code
1141
1142 IF (x_cascaded_table(n).error_status IN('S', 'W')) THEN
1143 IF (g_asn_debug = 'Y') THEN
1144 asn_debug.put_line('X_progress ' || x_progress);
1145 END IF;
1146
1147 IF x_cascaded_table(n).transaction_type = 'SHIP'
1148 AND x_cascaded_table(n).auto_transact_code = 'DELIVER' THEN
1149 IF (g_asn_debug = 'Y') THEN
1150 asn_debug.put_line('Changing the transaction_type to RECEIVE FROM SHIP');
1151 END IF;
1152
1153 x_cascaded_table(n).transaction_type := 'RECEIVE';
1154 END IF;
1155 END IF;
1156
1157 -- Check whether Qty > 0
1158
1159 x_progress := '097';
1160
1161 IF (g_asn_debug = 'Y') THEN
1162 asn_debug.put_line('X_progress ' || x_progress);
1163 END IF;
1164
1165 IF x_cascaded_table(n).error_status IN('S', 'W')
1166 AND x_cascaded_table(n).quantity <= 0 THEN
1167 IF (g_asn_debug = 'Y') THEN
1168 asn_debug.put_line('Quantity is <= zero. Cascade will fail');
1169 END IF;
1170
1171 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1172 rcv_error_pkg.set_error_message('RCV_ITEM_NO_SHIP_QTY', x_cascaded_table(n).error_message);
1173 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).item_num);
1174 rcv_error_pkg.log_interface_error('QUANTITY', FALSE);
1175 END IF;
1176
1177 -- the following steps will create a set of rows linking the line_record with
1178 -- its corresponding po_line_location rows until the quantity value from
1179 -- the asn is consumed. (Cascade)
1180
1181 /* 2119137 : If the user populates rcv_transactions_interface
1182 with po_line_id, then ROI errors out with
1183 RCV_ASN_NO_PO_LINE_LOCATION_ID when the docment_line_num
1184 is not provided for one time items. Modified the "if" criteria in
1185 such a way that the ROI validation does'nt error out when
1186 po_line_id is populated for one time items. */
1187 x_progress := '098';
1188
1189 IF (g_asn_debug = 'Y') THEN
1190 asn_debug.put_line('X_progress ' || x_progress);
1191 END IF;
1192
1193 IF ( x_cascaded_table(n).po_header_id IS NOT NULL
1194 AND ( x_cascaded_table(n).item_id IS NOT NULL
1195 OR x_cascaded_table(n).vendor_item_num IS NOT NULL
1196 OR x_cascaded_table(n).po_line_id IS NOT NULL
1197 OR x_cascaded_table(n).document_line_num IS NOT NULL)
1198 AND x_cascaded_table(n).error_status IN('S', 'W')
1199 ) THEN
1200 -- Copy record from main table to temp table
1201
1202 IF (g_asn_debug = 'Y') THEN
1203 asn_debug.put_line('Copy record from main table to temp table');
1204 END IF;
1205
1206 current_n := 1;
1207 temp_cascaded_table(current_n) := x_cascaded_table(n);
1208
1209 -- Get all rows which meet this condition
1210 IF (g_asn_debug = 'Y') THEN
1211 asn_debug.put_line('Get all rows which meet this condition');
1212 asn_debug.put_line('Transaction Type = ' || x_cascaded_table(n).transaction_type);
1213 asn_debug.put_line('Auto Transact Code = ' || x_cascaded_table(n).auto_transact_code);
1214 END IF;
1215
1216 -- bug 1362237 Deriving the document_line_num
1217 -- and document_shipment_line_num when line_id and/or line_location_id
1218 -- are provided.
1219
1220 IF temp_cascaded_table(current_n).document_line_num IS NULL
1221 AND temp_cascaded_table(current_n).po_line_id IS NOT NULL THEN
1222 BEGIN
1223 SELECT line_num
1224 INTO temp_cascaded_table(current_n).document_line_num
1225 FROM po_lines
1226 WHERE po_line_id = temp_cascaded_table(current_n).po_line_id;
1227 EXCEPTION
1228 WHEN OTHERS THEN
1229 IF (g_asn_debug = 'Y') THEN
1230 asn_debug.put_line('wrong po_line_id entered in rcv_transactions_interface');
1231 END IF;
1232 END;
1233 END IF;
1234
1235 IF temp_cascaded_table(current_n).document_shipment_line_num IS NULL
1236 AND temp_cascaded_table(current_n).po_line_location_id IS NOT NULL THEN
1237 BEGIN
1238 SELECT shipment_num
1239 INTO temp_cascaded_table(current_n).document_shipment_line_num
1240 FROM po_line_locations
1241 WHERE line_location_id = temp_cascaded_table(current_n).po_line_location_id;
1242 EXCEPTION
1243 WHEN OTHERS THEN
1244 IF (g_asn_debug = 'Y') THEN
1245 asn_debug.put_line('wrong po_line_location_id entered in rcv_transactions_interface');
1246 END IF;
1247 END;
1248 END IF;
1249
1250 IF temp_cascaded_table(current_n).document_distribution_num IS NULL
1251 AND temp_cascaded_table(current_n).po_distribution_id IS NOT NULL THEN
1252 BEGIN
1253 SELECT distribution_num
1254 INTO temp_cascaded_table(current_n).document_distribution_num
1255 FROM po_distributions
1256 WHERE po_distribution_id = temp_cascaded_table(current_n).po_distribution_id;
1257 EXCEPTION
1258 WHEN OTHERS THEN
1259 IF (g_asn_debug = 'Y') THEN
1260 asn_debug.put_line('wrong po_distribution_id entered in rcv_transactions_interface');
1261 END IF;
1262 END;
1263 END IF;
1264
1265 -- 1362237
1266 /* Bug 1898283 : The Receiving control of "Enforce Ship to Location was
1267 not working for ROI. So corrected the code so that it now behaves the same way as
1268 the Form Behaves. i.e.,
1269 Proceed without any error messages if the enforcement is set to "None"
1270 Enter error message in po_interface_errors if the enforcement is "Warning"
1271 Enter error message in po_interface_errors if the enforcement is "Reject"
1272 and error out.
1273 This validation is done by comparing the enforce_ship_location_code from
1274 po_line_locations and assigning the proper ship_location_id into a
1275 temporary variable temp_mirror_ship_to_loc_id and passing the temp
1276 variable as a parameter to open the cursor "Distributions".
1277 */
1278
1279 /* Bug 2208664 : The fix done as part of 1898283 was reverted back and
1280 performed at a different location.
1281 */
1282 IF ( x_cascaded_table(n).transaction_type <> 'DELIVER'
1283 AND NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') <> 'DELIVER') THEN
1284 IF (g_asn_debug = 'Y') THEN
1285 asn_debug.put_line('Open Shipment records');
1286 asn_debug.put_line('PO Header id ' || TO_CHAR(temp_cascaded_table(current_n).po_header_id));
1287 asn_debug.put_line('Item Id ' || TO_CHAR(temp_cascaded_table(current_n).item_id));
1288 asn_debug.put_line('PO Line Num ' || TO_CHAR(temp_cascaded_table(current_n).document_line_num));
1289 asn_debug.put_line('PO Release Id ' || TO_CHAR(temp_cascaded_table(current_n).po_release_id));
1290 asn_debug.put_line('Shipment Line num ' || TO_CHAR(temp_cascaded_table(current_n).document_shipment_line_num));
1291 asn_debug.put_line('PO LINE LOCATION ID ' || TO_CHAR(temp_cascaded_table(current_n).document_distribution_num));
1292 asn_debug.put_line('Ship To Organization ID ' || TO_CHAR(temp_cascaded_table(current_n).to_organization_id));
1293 asn_debug.put_line('Ship To Location Id ' || TO_CHAR(NVL(temp_cascaded_table(current_n).ship_to_location_id, x_header_record.header_record.location_id)));
1294 asn_debug.put_line('Vendor Item Num ' || temp_cascaded_table(current_n).vendor_item_num);
1295 asn_debug.put_line('Proceed to open cursor');
1296 END IF;
1297
1298 /* Bug 2208664 : Nullified the ship_to_location_id when calling
1299 the cursors shipments, count_shipments, distributions and
1300 count_distributions. The proper value of ship_to_location_id will
1301 be set after values are fetched and validated for the
1302 location control code set at PO.
1303 */
1304 OPEN shipments(temp_cascaded_table(current_n).po_header_id,
1305 temp_cascaded_table(current_n).item_id,
1306 temp_cascaded_table(current_n).document_line_num,
1307 temp_cascaded_table(current_n).po_release_id,
1308 temp_cascaded_table(current_n).document_shipment_line_num,
1309 temp_cascaded_table(current_n).to_organization_id,
1310 NULL, -- ship_to_location_id
1311 -- nvl(temp_mirror_ship_to_loc_id,
1312 -- nvl(temp_cascaded_table(current_n).ship_to_location_id,
1313 -- X_header_record.header_record.location_id),
1314 temp_cascaded_table(current_n).vendor_item_num
1315 );
1316 -- count_shipments just gets the count of rows found in shipments
1317
1318 OPEN count_shipments(temp_cascaded_table(current_n).po_header_id,
1319 temp_cascaded_table(current_n).item_id,
1320 temp_cascaded_table(current_n).document_line_num,
1321 temp_cascaded_table(current_n).po_release_id,
1322 temp_cascaded_table(current_n).document_shipment_line_num,
1323 temp_cascaded_table(current_n).to_organization_id,
1324 NULL, -- ship_to_location_id
1325 -- nvl(temp_mirror_ship_to_loc_id,
1326 -- nvl(temp_cascaded_table(current_n).ship_to_location_id,
1327 -- X_header_record.header_record.location_id),
1328 temp_cascaded_table(current_n).vendor_item_num
1329 );
1330 ELSIF( x_cascaded_table(n).transaction_type = 'DELIVER'
1331 OR NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') = 'DELIVER') THEN
1332 IF (g_asn_debug = 'Y') THEN
1333 asn_debug.put_line('Open Distribution records');
1334 asn_debug.put_line('PO Header id ' || TO_CHAR(temp_cascaded_table(current_n).po_header_id));
1335 asn_debug.put_line('Item Id ' || TO_CHAR(temp_cascaded_table(current_n).item_id));
1336 asn_debug.put_line('PO Line Num ' || TO_CHAR(temp_cascaded_table(current_n).document_line_num));
1337 asn_debug.put_line('PO Release Id ' || TO_CHAR(temp_cascaded_table(current_n).po_release_id));
1338 asn_debug.put_line('Shipment Line num ' || TO_CHAR(temp_cascaded_table(current_n).document_shipment_line_num));
1339 asn_debug.put_line('Distribution num ' || TO_CHAR(temp_cascaded_table(current_n).document_distribution_num));
1340 asn_debug.put_line('Ship To Organization ID ' || TO_CHAR(temp_cascaded_table(current_n).to_organization_id));
1341 asn_debug.put_line('Ship To Location Id ' || TO_CHAR(NVL(temp_cascaded_table(current_n).ship_to_location_id, x_header_record.header_record.location_id)));
1342 asn_debug.put_line('Vendor Item Num ' || temp_cascaded_table(current_n).vendor_item_num);
1343 asn_debug.put_line('Proceed to open cursor');
1344 END IF;
1345
1346 OPEN distributions(temp_cascaded_table(current_n).po_header_id,
1347 temp_cascaded_table(current_n).item_id,
1348 temp_cascaded_table(current_n).document_line_num,
1349 temp_cascaded_table(current_n).po_release_id,
1350 temp_cascaded_table(current_n).document_shipment_line_num,
1351 temp_cascaded_table(current_n).document_distribution_num,
1352 temp_cascaded_table(current_n).to_organization_id,
1353 NULL, -- ship_to_location_id
1354 -- nvl(temp_mirror_ship_to_loc_id,
1355 -- nvl(temp_cascaded_table(current_n).ship_to_location_id,
1356 -- X_header_record.header_record.location_id),
1357 temp_cascaded_table(current_n).vendor_item_num
1358 );
1359 -- count_distributions just gets the count of rows found in distributions
1360
1361 OPEN count_distributions(temp_cascaded_table(current_n).po_header_id,
1362 temp_cascaded_table(current_n).item_id,
1363 temp_cascaded_table(current_n).document_line_num,
1364 temp_cascaded_table(current_n).po_release_id,
1365 temp_cascaded_table(current_n).document_shipment_line_num,
1366 temp_cascaded_table(current_n).document_distribution_num,
1367 temp_cascaded_table(current_n).to_organization_id,
1368 NULL, -- ship_to_location_id
1369 -- nvl(temp_mirror_ship_to_loc_id,
1370 -- nvl(temp_cascaded_table(current_n).ship_to_location_id,
1371 -- X_header_record.header_record.location_id),
1372 temp_cascaded_table(current_n).vendor_item_num
1373 );
1374 END IF;
1375
1376 -- Assign shipped quantity to remaining quantity
1377 IF (g_asn_debug = 'Y') THEN
1378 asn_debug.put_line('Assign shipped quantity to remaining quantity');
1379 asn_debug.put_line('Pointer in temp_cascade ' || TO_CHAR(current_n));
1380 END IF;
1381
1382 x_remaining_quantity := temp_cascaded_table(current_n).quantity;
1383 x_bkp_qty := x_remaining_quantity; -- used for decrementing cum qty for first record
1384 x_remaining_qty_po_uom := 0;
1385
1386 IF (g_asn_debug = 'Y') THEN
1387 asn_debug.put_line('Have assigned the quantity');
1388 END IF;
1389
1390 -- Calculate tax_amount_factor for calculating tax_amount for
1391 -- each cascaded line
1392
1393 IF NVL(temp_cascaded_table(current_n).tax_amount, 0) <> 0 THEN
1394 tax_amount_factor := temp_cascaded_table(current_n).tax_amount / x_remaining_quantity;
1395 ELSE
1396 tax_amount_factor := 0;
1397 END IF;
1398
1399 IF (g_asn_debug = 'Y') THEN
1400 asn_debug.put_line('Tax Factor ' || TO_CHAR(tax_amount_factor));
1401 asn_debug.put_line('Shipped Quantity : ' || TO_CHAR(x_remaining_quantity));
1402 END IF;
1403
1404 x_first_trans := TRUE;
1405 transaction_ok := FALSE;
1406
1407 /*
1408 ** Get the count of the number of records depending on the
1409 ** the transaction type
1410 */
1411 IF ( x_cascaded_table(n).transaction_type <> 'DELIVER'
1412 AND NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') <> 'DELIVER') THEN
1413 FETCH count_shipments INTO x_record_count;
1414 ELSE
1415 FETCH count_distributions INTO x_record_count;
1416 END IF;
1417
1418 IF (g_asn_debug = 'Y') THEN
1419 asn_debug.put_line('Before starting Cascade');
1420 END IF;
1421
1422 IF (g_asn_debug = 'Y') THEN
1423 asn_debug.put_line('Record Count = ' || x_record_count);
1424 END IF;
1425
1426 LOOP
1427 IF (g_asn_debug = 'Y') THEN
1428 asn_debug.put_line('Backup Qty ' || TO_CHAR(x_bkp_qty));
1429 asn_debug.put_line('Remaining Quantity ASN UOM ' || TO_CHAR(x_remaining_quantity));
1430 END IF;
1431
1432 IF (g_asn_debug = 'Y') THEN
1433 asn_debug.put_line('open shipments and fetch');
1434 END IF;
1435
1436 /*
1437 ** Fetch the appropriate record
1438 */
1439 IF (g_asn_debug = 'Y') THEN
1440 asn_debug.put_line('DEBUG: transaction_type = ' || x_cascaded_table(n).transaction_type);
1441 END IF;
1442
1443 IF ( x_cascaded_table(n).transaction_type <> 'DELIVER'
1444 AND NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') <> 'DELIVER') THEN
1445 IF (g_asn_debug = 'Y') THEN
1446 asn_debug.put_line('Fetching Shipments Cursor');
1447 END IF;
1448
1449 FETCH shipments INTO x_shipmentdistributionrec;
1450
1451 /*
1452 ** Check if this is the last record
1453 */
1454 IF (shipments%NOTFOUND) THEN
1455 lastrecord := TRUE;
1456 END IF;
1457
1458 rows_fetched := shipments%ROWCOUNT;
1459
1460 IF (g_asn_debug = 'Y') THEN
1461 asn_debug.put_line('Shipment Rows fetched ' || TO_CHAR(rows_fetched));
1462 END IF;
1463 ELSE
1464 IF (g_asn_debug = 'Y') THEN
1465 asn_debug.put_line('Fetching Distributions Cursor');
1466 END IF;
1467
1468 FETCH distributions INTO x_shipmentdistributionrec;
1469
1470 /*
1471 ** Check if this is the last record
1472 */
1473 IF (distributions%NOTFOUND) THEN
1474 lastrecord := TRUE;
1475 END IF;
1476
1477 rows_fetched := distributions%ROWCOUNT;
1478
1479 IF (g_asn_debug = 'Y') THEN
1480 asn_debug.put_line('Distribution Rows fetched ' || TO_CHAR(rows_fetched));
1481 END IF;
1482 END IF;
1483
1484 IF ( lastrecord
1485 OR x_remaining_quantity <= 0) THEN
1486 IF (g_asn_debug = 'Y') THEN
1487 asn_debug.put_line('Hit exit condition');
1488 END IF;
1489
1490 IF NOT x_first_trans THEN -- x_first_trans has been reset which means some cascade has
1491 -- happened. Otherwise current_n = 1
1492 current_n := current_n - 1;
1493 END IF;
1494
1495 -- do the tolerance act here
1496 IF (g_asn_debug = 'Y') THEN
1497 asn_debug.put_line('Temp table pointer ' || TO_CHAR(current_n));
1498 asn_debug.put_line('Check which condition has occured');
1499 END IF;
1500
1501 -- lastrecord...we have run out of rows and we still have quantity to allocate
1502 IF x_remaining_quantity > 0 THEN
1503 IF (g_asn_debug = 'Y') THEN
1504 asn_debug.put_line('There is quantity remaining');
1505 asn_debug.put_line('Need to check qty tolerances');
1506 END IF;
1507
1508 IF rows_fetched > 0
1509 AND NOT x_first_trans THEN -- we had got atleast some rows from our shipments cursor
1510 -- we have atleast one row cascaded (not null line_location_id)
1511 SELECT NVL(po_line_locations.qty_rcv_exception_code, 'NONE')
1512 INTO x_qty_rcv_exception_code
1513 FROM po_line_locations
1514 WHERE line_location_id = temp_cascaded_table(current_n).po_line_location_id;
1515
1516 IF (g_asn_debug = 'Y') THEN
1517 asn_debug.put_line('Qty tolerance exception code ' || NVL(x_qty_rcv_exception_code, 'NONE1'));
1518 END IF;
1519
1520 IF x_qty_rcv_exception_code IN('NONE', 'WARNING') THEN
1521 /* Bug# 1807842 */
1522 IF (temp_cascaded_table(current_n).quantity < x_converted_trx_qty) THEN
1523 IF (g_asn_debug = 'Y') THEN
1524 asn_debug.put_line('Tolerable quantity ' || TO_CHAR(x_converted_trx_qty));
1525 asn_debug.put_line('Current quantity ' || TO_CHAR(temp_cascaded_table(current_n).quantity));
1526 asn_debug.put_line('Current shipped quantity ' || TO_CHAR(temp_cascaded_table(current_n).quantity_shipped));
1527 asn_debug.put_line('Assign remaining ASN UOM qty ' || TO_CHAR(x_remaining_quantity) || ' to last record');
1528 asn_debug.put_line('Assign remaining PO UOM qty ' || TO_CHAR(x_remaining_qty_po_uom) || ' to last record');
1529 END IF;
1530
1531 temp_cascaded_table(current_n).quantity := temp_cascaded_table(current_n).quantity + x_remaining_quantity;
1532 temp_cascaded_table(current_n).quantity_shipped := temp_cascaded_table(current_n).quantity_shipped + x_remaining_quantity;
1533 temp_cascaded_table(current_n).source_doc_quantity := temp_cascaded_table(current_n).source_doc_quantity + x_remaining_qty_po_uom;
1534 temp_cascaded_table(current_n).primary_quantity := temp_cascaded_table(current_n).primary_quantity
1535 + convert_into_correct_qty(x_remaining_quantity,
1536 temp_cascaded_table(1).unit_of_measure,
1537 temp_cascaded_table(1).item_id,
1538 temp_cascaded_table(1).primary_unit_of_measure
1539 );
1540 END IF; /* Bug# 1807842 */
1541
1542 -- Vendor Cum Qty
1543 IF NVL(temp_cascaded_table(current_n).vendor_cum_shipped_qty, 0) <> 0 THEN
1544 temp_cascaded_table(current_n).vendor_cum_shipped_qty := temp_cascaded_table(current_n).vendor_cum_shipped_qty + temp_cascaded_table(current_n).primary_quantity;
1545 END IF;
1546
1547 temp_cascaded_table(current_n).tax_amount := ROUND(temp_cascaded_table(current_n).quantity * tax_amount_factor, 6);
1548
1549 IF (g_asn_debug = 'Y') THEN
1550 asn_debug.put_line('Current quantity ' || TO_CHAR(temp_cascaded_table(current_n).quantity));
1551 asn_debug.put_line('Current shipped quantity ' || TO_CHAR(temp_cascaded_table(current_n).quantity_shipped));
1552 asn_debug.put_line('Current source document quantity ' || TO_CHAR(temp_cascaded_table(current_n).source_doc_quantity));
1553 asn_debug.put_line('Current primary quantity ' || TO_CHAR(temp_cascaded_table(current_n).primary_quantity));
1554 asn_debug.put_line('Current Tax Amount ' || TO_CHAR(temp_cascaded_table(current_n).tax_amount));
1555 END IF;
1556
1557 IF x_qty_rcv_exception_code = 'WARNING' THEN
1558 IF (g_asn_debug = 'Y') THEN
1559 asn_debug.put_line('IN WARNING');
1560 END IF;
1561
1562 temp_cascaded_table(current_n).error_status := 'W';
1563 temp_cascaded_table(current_n).error_message := 'RCV_SHIP_QTY_OVER_TOLERANCE';
1564
1565 IF (g_asn_debug = 'Y') THEN
1566 asn_debug.put_line('Group Id ' || TO_CHAR(temp_cascaded_table(current_n).GROUP_ID));
1567 asn_debug.put_line('Header Interface Id ' || TO_CHAR(temp_cascaded_table(current_n).header_interface_id));
1568 asn_debug.put_line('IN Trans Id ' || TO_CHAR(temp_cascaded_table(current_n).interface_transaction_id));
1569 END IF;
1570
1571 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_warning;
1572 rcv_error_pkg.set_error_message('RCV_SHIP_QTY_OVER_TOLERANCE', x_cascaded_table(n).error_message);
1573 rcv_error_pkg.set_token('QTY_A', temp_cascaded_table(current_n).quantity);
1574 rcv_error_pkg.set_token('QTY_B', temp_cascaded_table(current_n).quantity - x_remaining_quantity);
1575 rcv_error_pkg.log_interface_warning('QUANTITY');
1576
1577 IF (g_asn_debug = 'Y') THEN
1578 asn_debug.put_line('Error Status ' || temp_cascaded_table(current_n).error_status);
1579 asn_debug.put_line('Error message ' || temp_cascaded_table(current_n).error_message);
1580 asn_debug.put_line('Need to insert into po_interface_errors');
1581 END IF;
1582 END IF;
1583
1584 IF (g_asn_debug = 'Y') THEN
1585 asn_debug.put_line('Current quantity ' || TO_CHAR(temp_cascaded_table(current_n).quantity));
1586 asn_debug.put_line('Current shipped quantity ' || TO_CHAR(temp_cascaded_table(current_n).quantity_shipped));
1587 asn_debug.put_line('Current source document quantity ' || TO_CHAR(temp_cascaded_table(current_n).source_doc_quantity));
1588 asn_debug.put_line('Current primary quantity ' || TO_CHAR(temp_cascaded_table(current_n).primary_quantity));
1589 asn_debug.put_line('Current Tax Amount ' || TO_CHAR(temp_cascaded_table(current_n).tax_amount));
1590 END IF;
1591 ELSIF x_qty_rcv_exception_code = 'REJECT' THEN
1592 IF (g_asn_debug = 'Y') THEN
1593 asn_debug.put_line('Extra ASN UOM Quantity ' || TO_CHAR(x_remaining_quantity));
1594 asn_debug.put_line('Extra PO UOM Quantity ' || TO_CHAR(x_remaining_qty_po_uom));
1595 END IF;
1596
1597 IF (g_asn_debug = 'Y') THEN
1598 asn_debug.put_line('delete the temp table ');
1599 END IF;
1600
1601 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1602 rcv_error_pkg.set_error_message('RCV_SHIP_QTY_OVER_TOLERANCE', x_cascaded_table(n).error_message);
1603 rcv_error_pkg.set_token('QTY_A', x_cascaded_table(n).quantity);
1604 rcv_error_pkg.set_token('QTY_B', x_cascaded_table(n).quantity - x_remaining_quantity);
1605 rcv_error_pkg.log_interface_error('QUANTITY', FALSE);
1606
1607 IF temp_cascaded_table.COUNT > 0 THEN
1608 FOR i IN 1 .. temp_cascaded_table.COUNT LOOP
1609 temp_cascaded_table.DELETE(i);
1610 END LOOP;
1611 END IF;
1612
1613 IF (g_asn_debug = 'Y') THEN
1614 asn_debug.put_line('mark the actual table with error status');
1615 asn_debug.put_line('Error Status ' || x_cascaded_table(n).error_status);
1616 asn_debug.put_line('Error message ' || x_cascaded_table(n).error_message);
1617 END IF;
1618
1619 IF (g_asn_debug = 'Y') THEN
1620 asn_debug.put_line('Need to insert a row into po_interface_errors');
1621 END IF;
1622 END IF;
1623 ELSE
1624 IF rows_fetched = 0 THEN
1625 IF (g_asn_debug = 'Y') THEN
1626 asn_debug.put_line('No rows were retrieved from cursor.');
1627 END IF;
1628 ELSIF x_first_trans THEN
1629 IF (g_asn_debug = 'Y') THEN
1630 asn_debug.put_line('No rows were cascaded');
1631 END IF;
1632 END IF;
1633
1634 x_temp_count := 1;
1635 x_cascaded_table(n).error_status := 'E';
1636
1637 /* nwang add error messages */
1638
1639 /* Bug 2340533 - Added a message RCV_ASN_NO_OPEN_SHIPMENTS which conveys that
1640 no shipments exists for receiving for the given PO.
1641 */
1642 IF ( x_cascaded_table(n).transaction_type <> 'DELIVER'
1643 AND NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') <> 'DELIVER') THEN
1644 -- Bug 2551443 Removed po_distributions from the FROM clause
1645 SELECT COUNT(*)
1646 INTO x_temp_count
1647 FROM po_line_locations pll,
1648 po_lines pl,
1649 po_headers ph
1650 WHERE ph.po_header_id = temp_cascaded_table(current_n).po_header_id
1651 AND pll.po_header_id = ph.po_header_id
1652 AND pl.line_num = NVL(temp_cascaded_table(current_n).document_line_num, pl.line_num)
1653 AND NVL(pll.po_release_id, 0) = NVL(temp_cascaded_table(current_n).po_release_id, NVL(pll.po_release_id, 0))
1654 AND pll.shipment_num = NVL(temp_cascaded_table(current_n).document_shipment_line_num, pll.shipment_num)
1655 AND pll.po_line_id = pl.po_line_id;
1656
1657 IF x_temp_count = 0 THEN
1658 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1659 rcv_error_pkg.set_error_message('RCV_ASN_NO_OPEN_SHIPMENTS', x_cascaded_table(n).error_message);
1660 rcv_error_pkg.set_token('PONUM', temp_cascaded_table(current_n).document_num);
1661 rcv_error_pkg.log_interface_error('DOCUMENT_NUM', FALSE);
1662 ELSE
1663 SELECT NVL(pl.item_id, 0),
1664 NVL(pll.approved_flag, 'N'),
1665 NVL(pll.cancel_flag, 'N'),
1666 NVL(pll.closed_code, 'OPEN'),
1667 pll.shipment_type,
1668 pll.ship_to_organization_id,
1669 pll.ship_to_location_id,
1670 NVL(pl.vendor_product_num, '-999')
1671 INTO x_item_id,
1672 x_approved_flag,
1673 x_cancel_flag,
1674 x_closed_code,
1675 x_shipment_type,
1676 x_ship_to_organization_id,
1677 x_ship_to_location_id,
1678 x_vendor_product_num
1679 FROM po_line_locations pll,
1680 po_lines pl,
1681 po_headers ph
1682 WHERE ph.po_header_id = temp_cascaded_table(current_n).po_header_id
1683 AND pll.po_header_id = ph.po_header_id
1684 AND pl.line_num = NVL(temp_cascaded_table(current_n).document_line_num, pl.line_num)
1685 AND NVL(pll.po_release_id, 0) = NVL(temp_cascaded_table(current_n).po_release_id, NVL(pll.po_release_id, 0))
1686 AND pll.shipment_num = NVL(temp_cascaded_table(current_n).document_shipment_line_num, pll.shipment_num)
1687 AND pll.po_line_id = pl.po_line_id;
1688 END IF; -- x_temp_count = 0
1689 ELSIF( x_cascaded_table(n).transaction_type = 'DELIVER'
1690 OR NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') = 'DELIVER') THEN
1691 SELECT COUNT(*)
1692 INTO x_temp_count
1693 FROM po_distributions pod,
1694 po_line_locations pll,
1695 po_lines pl,
1696 po_headers ph
1697 WHERE ph.po_header_id = temp_cascaded_table(current_n).po_header_id
1698 AND pll.po_header_id = ph.po_header_id
1699 AND pll.line_location_id = pod.line_location_id
1700 AND pl.line_num = NVL(temp_cascaded_table(current_n).document_line_num, pl.line_num)
1701 AND NVL(pll.po_release_id, 0) = NVL(temp_cascaded_table(current_n).po_release_id, NVL(pll.po_release_id, 0))
1702 AND pll.shipment_num = NVL(temp_cascaded_table(current_n).document_shipment_line_num, pll.shipment_num)
1703 AND pll.po_line_id = pl.po_line_id
1704 AND pod.distribution_num = NVL(temp_cascaded_table(current_n).document_distribution_num, pod.distribution_num);
1705
1706 IF x_temp_count = 0 THEN
1707 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1708 rcv_error_pkg.set_error_message('RCV_ASN_INVALID_DIST_NUM', x_cascaded_table(n).error_message);
1709 rcv_error_pkg.set_token('NUMBER', temp_cascaded_table(current_n).document_distribution_num);
1710 rcv_error_pkg.log_interface_error('DOCUMENT_DISTRIBUTION_NUM', FALSE);
1711 ELSE
1712 SELECT NVL(pl.item_id, 0),
1713 NVL(pll.approved_flag, 'N'),
1714 NVL(pll.cancel_flag, 'N'),
1715 NVL(pll.closed_code, 'OPEN'),
1716 pll.shipment_type,
1717 pll.ship_to_organization_id,
1718 pll.ship_to_location_id,
1719 NVL(pl.vendor_product_num, '-999')
1720 INTO x_item_id,
1721 x_approved_flag,
1722 x_cancel_flag,
1723 x_closed_code,
1724 x_shipment_type,
1725 x_ship_to_organization_id,
1726 x_ship_to_location_id,
1727 x_vendor_product_num
1728 FROM po_distributions pod,
1729 po_line_locations pll,
1730 po_lines pl,
1731 po_headers ph
1732 WHERE ph.po_header_id = temp_cascaded_table(current_n).po_header_id
1733 AND pll.po_header_id = ph.po_header_id
1734 AND pll.line_location_id = pod.line_location_id
1735 AND pl.line_num = NVL(temp_cascaded_table(current_n).document_line_num, pl.line_num)
1736 AND NVL(pll.po_release_id, 0) = NVL(temp_cascaded_table(current_n).po_release_id, NVL(pll.po_release_id, 0))
1737 AND pll.shipment_num = NVL(temp_cascaded_table(current_n).document_shipment_line_num, pll.shipment_num)
1738 AND pll.po_line_id = pl.po_line_id
1739 AND pod.distribution_num = NVL(temp_cascaded_table(current_n).document_distribution_num, pod.distribution_num);
1740 END IF; -- x_temp_count = 0;
1741 END IF; -- transaction_type <> 'DELIVER'
1742
1743 IF (x_temp_count <> 0) THEN
1744 IF x_item_id <> NVL(temp_cascaded_table(current_n).item_id, x_item_id) THEN
1745 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1746 rcv_error_pkg.set_error_message('RCV_ASN_ITEM_NOT_ALLOWED', x_cascaded_table(n).error_message);
1747 rcv_error_pkg.set_token('NUMBER', temp_cascaded_table(current_n).item_num);
1748 rcv_error_pkg.log_interface_error('ITEM_NUM', FALSE);
1749 END IF;
1750
1751 IF x_approved_flag <> 'Y' THEN
1752 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1753 rcv_error_pkg.set_error_message('RCV_ASN_SHIPMT_NOT_APPROVED', x_cascaded_table(n).error_message);
1754 rcv_error_pkg.set_token('NUMBER', temp_cascaded_table(current_n).document_shipment_line_num);
1755 rcv_error_pkg.set_token('PO_NUM', temp_cascaded_table(current_n).document_num);
1756 rcv_error_pkg.log_interface_error('DOCUMENT_SHIPMENT_LINE_NUM', FALSE);
1757 END IF;
1758
1759 IF x_cancel_flag <> 'N' THEN
1760 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1761 rcv_error_pkg.set_error_message('RCV_ASN_SHIPMT_CANCELLED', x_cascaded_table(n).error_message);
1762 rcv_error_pkg.set_token('NUMBER', temp_cascaded_table(current_n).document_shipment_line_num);
1763 rcv_error_pkg.log_interface_error('DOCUMENT_SHIPMENT_LINE_NUM', FALSE);
1764 END IF;
1765
1766 IF x_closed_code = 'FINALLY_CLOSED' THEN
1767 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1768 rcv_error_pkg.set_error_message('RCV_ASN_SHIPMT_FINALLY_CLOSED', x_cascaded_table(n).error_message);
1769 rcv_error_pkg.set_token('NUMBER', temp_cascaded_table(current_n).document_shipment_line_num);
1770 rcv_error_pkg.log_interface_error('DOCUMENT_SHIPMENT_LINE_NUM', FALSE);
1771 END IF;
1772
1773 IF x_shipment_type NOT IN('STANDARD', 'BLANKET', 'SCHEDULED') THEN
1774 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1775 rcv_error_pkg.set_error_message('RCV_ASN_INVAL_SHIPMT_TYPE', x_cascaded_table(n).error_message);
1776 rcv_error_pkg.set_token('NUMBER', temp_cascaded_table(current_n).document_shipment_line_num);
1777 rcv_error_pkg.log_interface_error('DOCUMENT_SHIPMENT_LINE_NUM', FALSE);
1778 END IF;
1779
1780 IF x_ship_to_organization_id <> NVL(temp_cascaded_table(current_n).to_organization_id, x_ship_to_organization_id) THEN
1781 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1782 rcv_error_pkg.set_error_message('RCV_ASN_INVAL_SHIP_TO_ORG', x_cascaded_table(n).error_message);
1783 rcv_error_pkg.set_token('VALUE', temp_cascaded_table(current_n).to_organization_code);
1784 rcv_error_pkg.log_interface_error('TO_ORGANIZATION_CODE', FALSE);
1785 END IF;
1786
1787 IF x_ship_to_location_id <> NVL(NVL(temp_cascaded_table(current_n).ship_to_location_id, x_header_record.header_record.location_id), x_ship_to_location_id) THEN
1788 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1789 rcv_error_pkg.set_error_message('RCV_ASN_INVAL_SHIP_TO_LOC', x_cascaded_table(n).error_message);
1790 rcv_error_pkg.set_token('VALUE', temp_cascaded_table(current_n).ship_to_location_code);
1791 rcv_error_pkg.log_interface_error('SHIP_TO_LOCATION_CODE', FALSE);
1792 END IF;
1793
1794 IF x_vendor_product_num <> NVL(temp_cascaded_table(current_n).vendor_item_num, x_vendor_product_num) THEN
1795 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1796 rcv_error_pkg.set_error_message('RCV_ASN_INVAL_VENDOR_PROD_NUM', x_cascaded_table(n).error_message);
1797 rcv_error_pkg.set_token('NUMBER', temp_cascaded_table(current_n).vendor_item_num);
1798 rcv_error_pkg.log_interface_error('VENDOR_ITEM_NUM', FALSE);
1799 END IF;
1800 END IF; -- x_temp_count = 0;
1801
1802
1803
1804 -- Delete the temp_cascaded_table just to be sure
1805
1806 IF temp_cascaded_table.COUNT > 0 THEN
1807 FOR i IN 1 .. temp_cascaded_table.COUNT LOOP
1808 temp_cascaded_table.DELETE(i);
1809 END LOOP;
1810 END IF;
1811 END IF;
1812 ELSE
1813 IF (g_asn_debug = 'Y') THEN
1814 asn_debug.put_line('Remaining ASN UOM quantity is zero ' || TO_CHAR(x_remaining_quantity));
1815 asn_debug.put_line('Remaining PO UOM quantity is zero ' || TO_CHAR(x_remaining_qty_po_uom));
1816 asn_debug.put_line('Return the cascaded rows back to the calling procedure');
1817 END IF;
1818 END IF;
1819
1820 -- close cursors
1821 IF (g_asn_debug = 'Y') THEN
1822 asn_debug.put_line('Close cursors shipments, count_shipments, distributions, count_disributions');
1823 END IF;
1824
1825 IF shipments%ISOPEN THEN
1826 CLOSE shipments;
1827 END IF;
1828
1829 IF count_shipments%ISOPEN THEN
1830 CLOSE count_shipments;
1831 END IF;
1832
1833 IF distributions%ISOPEN THEN
1834 CLOSE distributions;
1835 END IF;
1836
1837 IF count_distributions%ISOPEN THEN
1838 CLOSE count_distributions;
1839 END IF;
1840
1841 EXIT;
1842 END IF;
1843
1844 -- eliminate the row if it fails the date check
1845
1846 IF (g_asn_debug = 'Y') THEN
1847 asn_debug.put_line('Count in temp_cascade_table : ' || TO_CHAR(temp_cascaded_table.COUNT));
1848 asn_debug.put_line('Cursor record ' || TO_CHAR(rows_fetched));
1849 asn_debug.put_line('Check date tolerance');
1850 END IF;
1851
1852 check_date_tolerance(NVL(temp_cascaded_table(1).expected_receipt_date, x_header_record.header_record.expected_receipt_date), -- Bug 487222
1853 x_shipmentdistributionrec.promised_date,
1854 x_shipmentdistributionrec.days_early_receipt_allowed,
1855 x_shipmentdistributionrec.days_late_receipt_allowed,
1856 x_shipmentdistributionrec.receipt_days_exception_code
1857 );
1858
1859 /* bug 1060261 - added error message to be shown when the expected date is outside tolerance range */
1860 IF (x_shipmentdistributionrec.receipt_days_exception_code = 'REJECT') THEN
1861 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1862 rcv_error_pkg.set_error_message('RCV_ASN_DATE_OUT_TOL', x_cascaded_table(n).error_message);
1863 rcv_error_pkg.set_token('DELIVERY DATE', NVL(temp_cascaded_table(1).expected_receipt_date, x_header_record.header_record.expected_receipt_date));
1864 rcv_error_pkg.log_interface_error('EXPECTED_RECEIPT_DATE', FALSE);
1865 END IF;
1866
1867 IF (g_asn_debug = 'Y') THEN
1868 asn_debug.put_line('Days exception Code ' || NVL(x_shipmentdistributionrec.receipt_days_exception_code, 'XXX'));
1869 END IF;
1870
1871 -- Check shipto_location enforcement
1872
1873 check_shipto_enforcement(x_shipmentdistributionrec.ship_to_location_id,
1874 NVL(temp_cascaded_table(1).ship_to_location_id, x_header_record.header_record.location_id),
1875 x_shipmentdistributionrec.enforce_ship_to_location_code
1876 );
1877
1878 IF (g_asn_debug = 'Y') THEN
1879 asn_debug.put_line('Enforce ShipToLocation ' || NVL(x_shipmentdistributionrec.enforce_ship_to_location_code, 'XXX'));
1880 END IF;
1881
1882 /* Bug 2208664 : Enter error message in po_interface_errors if enforce_ship_to_location_code is 'WARNING', and
1883 Enter error message in po_interface_errors if enforce_ship_to_location_code is 'REJECT' and error out
1884 */
1885 IF (x_shipmentdistributionrec.enforce_ship_to_location_code = 'REJECT') THEN
1886 BEGIN
1887 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1888 rcv_error_pkg.set_error_message('RCV_ASN_INVAL_SHIP_TO_LOC', x_cascaded_table(n).error_message);
1889 rcv_error_pkg.set_token('VALUE', temp_cascaded_table(current_n).ship_to_location_code);
1890 rcv_error_pkg.log_interface_error('SHIP_TO_LOCATION_CODE', FALSE);
1891 x_shipmentdistributionrec.ship_to_location_id := NVL(temp_cascaded_table(1).ship_to_location_id, x_header_record.header_record.location_id);
1892 END;
1893 ELSIF(x_shipmentdistributionrec.enforce_ship_to_location_code = 'WARNING') THEN
1894 BEGIN
1895 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_warning;
1896 rcv_error_pkg.set_error_message('RCV_ASN_INVAL_SHIP_TO_LOC', x_cascaded_table(n).error_message);
1897 rcv_error_pkg.set_token('VALUE', temp_cascaded_table(current_n).ship_to_location_code);
1898 rcv_error_pkg.log_interface_warning('SHIP_TO_LOCATION_CODE');
1899 x_shipmentdistributionrec.ship_to_location_id := NVL(temp_cascaded_table(1).ship_to_location_id, x_header_record.header_record.location_id);
1900 END;
1901 END IF;
1902
1903 IF (x_shipmentdistributionrec.receipt_days_exception_code = 'NONE')
1904 AND -- derived by the date tolerance procedure
1905 (x_shipmentdistributionrec.enforce_ship_to_location_code IN('NONE', 'WARNING')) THEN
1906 -- derived by shipto_enforcement
1907
1908 -- Changes to accept Vendor_Item_num without ITEM_ID/NUM
1909 -- Item_id could be null if the ASN has the vendor_item_num provided
1910 -- We need to put a value into item_id based on the cursor
1911 -- We need to also figure out the primary unit for the item_id
1912 -- We will do it for the first record only. Subsequent records in the
1913 -- temp_table are copies of the previous one
1914
1915 -- Assuming that vendor_item_num refers to a single item. If the items
1916 -- could be different then we need to move this somewhere below
1917
1918 IF (x_first_trans)
1919 AND temp_cascaded_table(current_n).item_id IS NULL THEN
1920 temp_cascaded_table(current_n).item_id := x_shipmentdistributionrec.item_id;
1921
1922 IF (g_asn_debug = 'Y') THEN
1923 asn_debug.put_line('Cursor Item Id is ' || TO_CHAR(temp_cascaded_table(current_n).item_id));
1924 END IF;
1925
1926 /* Start Bug# 3193969 - For one time items or EAM items which donot have an
1927 item_id, primary uom should be the base uom of the class to which the
1928 transaction uom is associated to */
1929 IF x_cascaded_table(n).primary_unit_of_measure IS NULL THEN
1930 IF temp_cascaded_table(current_n).item_id IS NULL THEN
1931 BEGIN
1932 SELECT muom.unit_of_measure
1933 INTO temp_cascaded_table(current_n).primary_unit_of_measure
1934 FROM mtl_units_of_measure muom,
1935 mtl_units_of_measure tuom
1936 WHERE tuom.unit_of_measure = temp_cascaded_table(current_n).unit_of_measure
1937 AND tuom.uom_class = muom.uom_class
1938 AND muom.base_uom_flag = 'Y';
1939
1940 IF (g_asn_debug = 'Y') THEN
1941 asn_debug.put_line('Transaction UOM: ' || temp_cascaded_table(current_n).unit_of_measure);
1942 asn_debug.put_line('Primary UOM for one time item: ' || temp_cascaded_table(current_n).primary_unit_of_measure);
1943 END IF;
1944 EXCEPTION
1945 WHEN NO_DATA_FOUND THEN
1946 temp_cascaded_table(current_n).error_status := 'W';
1947 temp_cascaded_table(current_n).error_message := 'Need an error message';
1948
1949 IF (g_asn_debug = 'Y') THEN
1950 asn_debug.put_line('Primary UOM error for one time items');
1951 END IF;
1952 END;
1953 ELSE
1954 BEGIN
1955 SELECT primary_unit_of_measure
1956 INTO temp_cascaded_table(current_n).primary_unit_of_measure
1957 FROM mtl_system_items
1958 WHERE mtl_system_items.inventory_item_id = temp_cascaded_table(current_n).item_id
1959 AND mtl_system_items.organization_id = temp_cascaded_table(current_n).to_organization_id;
1960
1961 IF (g_asn_debug = 'Y') THEN
1962 asn_debug.put_line('Primary UOM: ' || temp_cascaded_table(current_n).primary_unit_of_measure);
1963 END IF;
1964 EXCEPTION
1965 WHEN NO_DATA_FOUND THEN
1966 temp_cascaded_table(current_n).error_status := 'W';
1967 temp_cascaded_table(current_n).error_message := 'Need an error message';
1968
1969 IF (g_asn_debug = 'Y') THEN
1970 asn_debug.put_line('Primary UOM error');
1971 END IF;
1972 END;
1973 END IF;
1974 END IF;
1975 /* End Bug# 3193969 */
1976 END IF;
1977
1978 insert_into_table := FALSE;
1979 already_allocated_qty := 0;
1980
1981 /*
1982 ** Get the available quantity for the shipment or distribution
1983 ** that is available for allocation by this interface transaction
1984 */
1985 IF ( x_cascaded_table(n).transaction_type <> 'DELIVER'
1986 AND NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') <> 'DELIVER') THEN
1987 /*Bug# 1548597 */
1988 rcv_quantities_s.get_available_quantity('RECEIVE',
1989 x_shipmentdistributionrec.line_location_id,
1990 'VENDOR',
1991 NULL,
1992 NULL,
1993 NULL,
1994 x_converted_trx_qty,
1995 x_tolerable_qty,
1996 x_shipmentdistributionrec.unit_meas_lookup_code,
1997 x_secondary_available_qty
1998 );
1999
2000 -- If qtys have already been allocated for this po_line_location_id during
2001 -- a cascade process which has not been written to the db yet, we need to
2002 -- decrement it from the total available quantity
2003 -- We traverse the actual pl/sql table and accumulate the quantity by matching the
2004 -- po_line_location_id
2005
2006 IF n > 1 THEN -- We will do this for all rows except the 1st
2007 FOR i IN 1 ..(n - 1) LOOP
2008 IF x_cascaded_table(i).po_line_location_id = x_shipmentdistributionrec.line_location_id THEN
2009 already_allocated_qty := already_allocated_qty + x_cascaded_table(i).source_doc_quantity;
2010 END IF;
2011 END LOOP;
2012 END IF;
2013 ELSE
2014 /* Bug# 1548597*/
2015 rcv_quantities_s.get_available_quantity('DIRECT RECEIPT',
2016 x_shipmentdistributionrec.po_distribution_id,
2017 'VENDOR',
2018 NULL,
2019 NULL,
2020 NULL,
2021 x_converted_trx_qty,
2022 x_tolerable_qty,
2023 x_shipmentdistributionrec.unit_meas_lookup_code,
2024 x_secondary_available_qty
2025 );
2026
2027 /* Bug# 1337787 - Calculated the x_tolerable_qty in
2028 rcv_quantities_s.get_available_quantity procedure */
2029
2030 -- x_tolerable_qty := x_converted_trx_qty;
2031
2032 -- If qtys have already been allocated for this po_distribution_id during
2033 -- a cascade process which has not been written to the db yet, we need to
2034 -- decrement it from the total available quantity
2035 -- We traverse the actual pl/sql table and accumulate the quantity by matching the
2036 -- po_distribution_id
2037
2038 IF n > 1 THEN -- We will do this for all rows except the 1st
2039 FOR i IN 1 ..(n - 1) LOOP
2040 IF x_cascaded_table(i).po_distribution_id = x_shipmentdistributionrec.po_distribution_id THEN
2041 already_allocated_qty := already_allocated_qty + x_cascaded_table(i).source_doc_quantity;
2042 END IF;
2043 END LOOP;
2044 END IF;
2045 END IF;
2046
2047 IF (g_asn_debug = 'Y') THEN
2048 asn_debug.put_line('After call to get_available quantity');
2049 asn_debug.put_line('Available Quantity ' || TO_CHAR(x_converted_trx_qty));
2050 asn_debug.put_line('Tolerable Quantity ' || TO_CHAR(x_tolerable_qty));
2051 asn_debug.put_line('Already Shipped Quantity ' || TO_CHAR(NVL(x_shipmentdistributionrec.quantity_shipped, 0)));
2052 asn_debug.put_line('Pointer to temp table ' || TO_CHAR(current_n));
2053 END IF;
2054
2055 -- if qty has already been allocated then reduce available and tolerable
2056 -- qty by the allocated amount
2057
2058 IF NVL(already_allocated_qty, 0) > 0 THEN
2059 x_converted_trx_qty := x_converted_trx_qty - already_allocated_qty;
2060 x_tolerable_qty := x_tolerable_qty - already_allocated_qty;
2061
2062 IF x_converted_trx_qty < 0 THEN
2063 x_converted_trx_qty := 0;
2064 END IF;
2065
2066 IF x_tolerable_qty < 0 THEN
2067 x_tolerable_qty := 0;
2068 END IF;
2069
2070 IF (g_asn_debug = 'Y') THEN
2071 asn_debug.put_line('Have some allocated quantity. Will reduce qty');
2072 asn_debug.put_line('Allocated Qty ' || TO_CHAR(already_allocated_qty));
2073 asn_debug.put_line('After reducing by allocated qty');
2074 asn_debug.put_line('Available Quantity ' || TO_CHAR(x_converted_trx_qty));
2075 asn_debug.put_line('Tolerable Quantity ' || TO_CHAR(x_tolerable_qty));
2076 asn_debug.put_line('Already Shipped Quantity ' || TO_CHAR(NVL(x_shipmentdistributionrec.quantity_shipped, 0)));
2077 asn_debug.put_line('Pointer to temp table ' || TO_CHAR(current_n));
2078 END IF;
2079 END IF;
2080
2081 -- We can use the first record since the item_id and uom are not going to change
2082 -- Check that we can convert between ASN-> PO uom
2083 -- PO -> ASN uom
2084 -- PO -> PRIMARY uom
2085 -- If any of the conversions fail then we cannot use that record
2086
2087 x_remaining_qty_po_uom := 0; -- initialize
2088 po_asn_uom_qty := 0; -- initialize
2089 po_primary_uom_qty := 0; -- initialize
2090 x_remaining_qty_po_uom := convert_into_correct_qty(x_remaining_quantity,
2091 temp_cascaded_table(1).unit_of_measure,
2092 temp_cascaded_table(1).item_id,
2093 x_shipmentdistributionrec.unit_meas_lookup_code
2094 );
2095 -- using arbit qty for PO->ASN, PO->PRIMARY UOM conversion as this is just a check
2096
2097 po_asn_uom_qty := convert_into_correct_qty(1000,
2098 x_shipmentdistributionrec.unit_meas_lookup_code,
2099 temp_cascaded_table(1).item_id,
2100 temp_cascaded_table(1).unit_of_measure
2101 );
2102 po_primary_uom_qty := convert_into_correct_qty(1000,
2103 x_shipmentdistributionrec.unit_meas_lookup_code,
2104 temp_cascaded_table(1).item_id,
2105 temp_cascaded_table(1).primary_unit_of_measure
2106 );
2107
2108 IF x_remaining_qty_po_uom = 0
2109 OR -- no point in going further for this record
2110 po_asn_uom_qty = 0
2111 OR -- as we cannot convert between the ASN -> PO uoms
2112 po_primary_uom_qty = 0 THEN -- PO -> ASN uom, PO -> PRIMARY UOM
2113 IF (g_asn_debug = 'Y') THEN
2114 asn_debug.put_line('Need an error message in the interface tables');
2115 asn_debug.put_line('Cannot interconvert between diff UOMs');
2116 asn_debug.put_line('This po_line cannot be used as the uoms ');
2117 asn_debug.put_line(temp_cascaded_table(1).unit_of_measure || ' ' || x_shipmentdistributionrec.unit_meas_lookup_code);
2118 asn_debug.put_line('cannot be converted for item ' || TO_CHAR(temp_cascaded_table(1).item_id));
2119 END IF;
2120 ELSE -- we have converted the qty between uoms succesfully
2121 IF (g_asn_debug = 'Y') THEN
2122 asn_debug.put_line('Current Item Id ' || TO_CHAR(temp_cascaded_table(1).item_id));
2123 asn_debug.put_line('Current ASN Quantity ' || TO_CHAR(x_remaining_quantity));
2124 asn_debug.put_line('Current ASN UOM ' || temp_cascaded_table(1).unit_of_measure);
2125 asn_debug.put_line('Converted PO UOM Quantity ' || TO_CHAR(x_remaining_qty_po_uom));
2126 asn_debug.put_line('PO UOM ' || x_shipmentdistributionrec.unit_meas_lookup_code);
2127 END IF;
2128
2129 -- If last row set available = tolerable - shipped
2130 -- else = available - shipped
2131 /*
2132 ** Debug: We're a bit screwed here. How do we know if the shipment is taken into account here. I guess if the transaction
2133 ** has the shipment line id then we should take the quantity from the shipped quantity. Need to walk through the different
2134 ** scenarios
2135 */
2136 IF rows_fetched = x_record_count THEN
2137 x_converted_trx_qty := x_tolerable_qty - NVL(x_shipmentdistributionrec.quantity_shipped, 0);
2138
2139 IF (g_asn_debug = 'Y') THEN
2140 asn_debug.put_line('Last Row : ' || TO_CHAR(x_converted_trx_qty));
2141 END IF;
2142 ELSE
2143 x_converted_trx_qty := x_converted_trx_qty - NVL(x_shipmentdistributionrec.quantity_shipped, 0);
2144
2145 IF (g_asn_debug = 'Y') THEN
2146 asn_debug.put_line('Not Last Row : ' || TO_CHAR(x_converted_trx_qty));
2147 END IF;
2148 END IF;
2149
2150 IF x_converted_trx_qty > 0 THEN
2151 IF (x_converted_trx_qty < x_remaining_qty_po_uom) THEN -- compare like uoms
2152 IF (g_asn_debug = 'Y') THEN
2153 asn_debug.put_line('We are in > Qty branch');
2154 END IF;
2155
2156 x_remaining_qty_po_uom := x_remaining_qty_po_uom - x_converted_trx_qty;
2157 -- change asn uom qty so both qtys are in sync
2158
2159 x_remaining_quantity := convert_into_correct_qty(x_remaining_qty_po_uom,
2160 x_shipmentdistributionrec.unit_meas_lookup_code,
2161 temp_cascaded_table(1).item_id,
2162 temp_cascaded_table(1).unit_of_measure
2163 );
2164 insert_into_table := TRUE;
2165 ELSE
2166 IF (g_asn_debug = 'Y') THEN
2167 asn_debug.put_line('We are in <= Qty branch ');
2168 END IF;
2169
2170 x_converted_trx_qty := x_remaining_qty_po_uom;
2171 insert_into_table := TRUE;
2172 x_remaining_qty_po_uom := 0;
2173 x_remaining_quantity := 0;
2174 END IF;
2175 ELSE -- no qty for this record but if last row we need it
2176 IF rows_fetched = x_record_count THEN -- last row needs to be inserted anyway
2177 -- so that the row can be used based on qty tolerance
2178 -- checks
2179 IF (g_asn_debug = 'Y') THEN
2180 asn_debug.put_line('Quantity is less then 0 but last record');
2181 END IF;
2182
2183 insert_into_table := TRUE;
2184 x_converted_trx_qty := 0;
2185 ELSE
2186 IF (g_asn_debug = 'Y') THEN
2187 asn_debug.put_line('<= 0 Quantity but more records in cursor');
2188 END IF;
2189
2190 x_remaining_qty_po_uom := 0; -- we may have a diff uom on the next iteration
2191
2192 IF (g_asn_debug = 'Y') THEN
2193 asn_debug.put_line('We have to deal with remaining_qty > 0 and x_converted_trx_qty -ve');
2194 END IF;
2195
2196 insert_into_table := FALSE;
2197 END IF;
2198 END IF;
2199 END IF; -- remaining_qty_po_uom <> 0
2200
2201 IF insert_into_table THEN
2202 IF (x_first_trans) THEN
2203 IF (g_asn_debug = 'Y') THEN
2204 asn_debug.put_line('First Time ' || TO_CHAR(current_n));
2205 END IF;
2206
2207 x_first_trans := FALSE;
2208
2209 IF NVL(temp_cascaded_table(current_n).vendor_cum_shipped_qty, 0) <> 0 THEN
2210 IF (g_asn_debug = 'Y') THEN
2211 asn_debug.put_line('The cum qty from vendor is ' || TO_CHAR(temp_cascaded_table(current_n).vendor_cum_shipped_qty));
2212 END IF;
2213
2214 /* The vendor sends us cum_qty which also includes the current shipment ???.
2215 We need to allocate the correct cum_qty to each row as the cascade happens
2216 The algorithm is as follows
2217
2218 reset cum_qty = cum_qty - total_shipped_qty (x_bkp_qty) in the 1st run
2219 Later reset cum_qty = cum_qty + primary_qty for each insert.Since we always
2220 copy the previous record this should work pretty well */
2221 temp_cascaded_table(current_n).vendor_cum_shipped_qty := temp_cascaded_table(current_n).vendor_cum_shipped_qty
2222 - convert_into_correct_qty(x_bkp_qty,
2223 temp_cascaded_table(current_n).unit_of_measure,
2224 temp_cascaded_table(current_n).item_id,
2225 temp_cascaded_table(current_n).primary_unit_of_measure
2226 );
2227
2228 IF (g_asn_debug = 'Y') THEN
2229 asn_debug.put_line('Cum qty - current shipment ' || TO_CHAR(temp_cascaded_table(current_n).vendor_cum_shipped_qty));
2230 END IF;
2231 END IF;
2232 ELSE
2233 IF (g_asn_debug = 'Y') THEN
2234 asn_debug.put_line('Next Time ' || TO_CHAR(current_n));
2235 END IF;
2236
2237 temp_cascaded_table(current_n) := temp_cascaded_table(current_n - 1);
2238 END IF;
2239
2240 /* source_doc_quantity -> in po_uom
2241 primary_quantity -> in primary_uom
2242 cum_qty -> in primary_uom
2243 quantity,quantity_shipped -> in ASN uom */
2244 temp_cascaded_table(current_n).source_doc_quantity := x_converted_trx_qty; -- in po uom
2245 temp_cascaded_table(current_n).source_doc_unit_of_measure := x_shipmentdistributionrec.unit_meas_lookup_code;
2246
2247 -- bug 1363369 fix carried forward FROM bug# 1337314
2248 -- No need to do the following conversion if the cursor returns one row
2249 -- for a corresponding record in the interface, as the quantity is already in asn uom.
2250 -- If the cursor fetches more than one row then the quantity in the interface will be
2251 -- distributed accross the fetched rows and hence need to do the following conversion.
2252 IF x_record_count > 1 THEN
2253 temp_cascaded_table(current_n).quantity := convert_into_correct_qty(x_converted_trx_qty,
2254 x_shipmentdistributionrec.unit_meas_lookup_code,
2255 temp_cascaded_table(current_n).item_id,
2256 temp_cascaded_table(current_n).unit_of_measure
2257 ); -- in asn uom
2258 END IF;
2259
2260 temp_cascaded_table(current_n).quantity_shipped := temp_cascaded_table(current_n).quantity; -- in asn uom
2261
2262 -- Primary qty in Primary UOM
2263 temp_cascaded_table(current_n).primary_quantity := convert_into_correct_qty(x_converted_trx_qty,
2264 x_shipmentdistributionrec.unit_meas_lookup_code,
2265 temp_cascaded_table(current_n).item_id,
2266 temp_cascaded_table(current_n).primary_unit_of_measure
2267 );
2268
2269 -- Assuming vendor_cum_shipped_qty is in PRIMARY UOM
2270
2271 IF NVL(temp_cascaded_table(current_n).vendor_cum_shipped_qty, 0) <> 0 THEN
2272 temp_cascaded_table(current_n).vendor_cum_shipped_qty := temp_cascaded_table(current_n).vendor_cum_shipped_qty + temp_cascaded_table(current_n).primary_quantity;
2273 END IF;
2274
2275 temp_cascaded_table(current_n).inspection_status_code := 'NOT INSPECTED';
2276 temp_cascaded_table(current_n).interface_source_code := 'RCV';
2277 temp_cascaded_table(current_n).currency_code := x_shipmentdistributionrec.currency_code;
2278 temp_cascaded_table(current_n).po_unit_price := x_shipmentdistributionrec.unit_price;
2279 temp_cascaded_table(current_n).tax_amount := ROUND(temp_cascaded_table(current_n).quantity * tax_amount_factor, 4);
2280
2281 IF (g_asn_debug = 'Y') THEN
2282 asn_debug.put_line('Current Tax Amount ' || TO_CHAR(temp_cascaded_table(current_n).tax_amount));
2283 END IF;
2284
2285 temp_cascaded_table(current_n).category_id := x_shipmentdistributionrec.category_id;
2286 temp_cascaded_table(current_n).item_description := x_shipmentdistributionrec.item_description;
2287 temp_cascaded_table(current_n).unit_of_measure_class := x_shipmentdistributionrec.unit_of_measure_class;
2288
2289 IF temp_cascaded_table(current_n).to_organization_id IS NULL THEN
2290 temp_cascaded_table(current_n).to_organization_id := x_shipmentdistributionrec.ship_to_organization_id;
2291 END IF;
2292
2293 temp_cascaded_table(current_n).po_line_id := x_shipmentdistributionrec.po_line_id;
2294 temp_cascaded_table(current_n).po_line_location_id := x_shipmentdistributionrec.line_location_id;
2295
2296 IF x_shipmentdistributionrec.enforce_ship_to_location_code = 'WARNING' THEN
2297 IF (g_asn_debug = 'Y') THEN
2298 asn_debug.put_line('Message to warn about different shiptolocations');
2299 END IF;
2300 END IF;
2301
2302 /* Bug 1845702
2303 * Currency rate and date can be changed at the time of receipt
2304 * depending on the profile ALLOW_RATE_OVERRIDE_FOR_USER_RATE_TYPE.
2305 * This was not handled in the open interface. Introduced code
2306 * to handle the changes at the time of receipt
2307 */
2308 fnd_profile.get('ALLOW_RATE_OVERRIDE_FOR_USER_RATE_TYPE', x_allow_rate_override);
2309
2310 /* ksareddy - 2329928 Ported changes by bao in branch to cache set_of_books_id */
2311 IF (rcv_transactions_interface_sv.x_set_of_books_id IS NULL) THEN
2312 SELECT set_of_books_id
2313 INTO rcv_transactions_interface_sv.x_set_of_books_id
2314 FROM financials_system_parameters;
2315 END IF;
2316
2317 x_sob_id := rcv_transactions_interface_sv.x_set_of_books_id;
2318
2319 /*
2320 SELECT set_of_books_id
2321 INTO x_sob_id
2322 FROM financials_system_parameters;
2323 */
2324 IF (x_shipmentdistributionrec.match_option = 'P') THEN
2325 IF ( x_shipmentdistributionrec.rate_type = 'User'
2326 AND x_allow_rate_override = 'Y') THEN
2327 temp_cascaded_table(current_n).currency_conversion_date := x_shipmentdistributionrec.rate_date;
2328 ELSIF( x_shipmentdistributionrec.rate_type = 'User'
2329 AND x_allow_rate_override = 'N') THEN
2330 temp_cascaded_table(current_n).currency_conversion_date := x_shipmentdistributionrec.rate_date;
2331 temp_cascaded_table(current_n).currency_conversion_rate := x_shipmentdistributionrec.rate;
2332 ELSIF(x_shipmentdistributionrec.rate_type <> 'User') THEN
2333 temp_cascaded_table(current_n).currency_conversion_date := x_shipmentdistributionrec.rate_date;
2334 temp_cascaded_table(current_n).currency_conversion_rate := x_shipmentdistributionrec.rate;
2335 END IF;
2336 ELSIF(x_shipmentdistributionrec.match_option = 'R') THEN
2337 IF ( x_shipmentdistributionrec.rate_type = 'User'
2338 AND x_allow_rate_override = 'N') THEN
2339 temp_cascaded_table(current_n).currency_conversion_rate := x_shipmentdistributionrec.rate;
2340 ELSIF(x_shipmentdistributionrec.rate_type <> 'User') THEN
2341 x_rate := gl_currency_api.get_rate(x_sob_id,
2342 x_shipmentdistributionrec.currency_code,
2343 NVL(temp_cascaded_table(current_n).currency_conversion_date, SYSDATE),
2344 x_shipmentdistributionrec.rate_type
2345 );
2346 x_rate := ROUND(x_rate, 15);
2347 temp_cascaded_table(current_n).currency_conversion_rate := x_rate;
2348 END IF;
2349 END IF;
2350
2351 IF (temp_cascaded_table(current_n).currency_conversion_rate IS NULL) THEN
2352 temp_cascaded_table(current_n).currency_conversion_rate := x_shipmentdistributionrec.rate;
2353 END IF;
2354
2355 --Bug#2708861.Added the following so that rate_type gets defaulted from po_headers.
2356 IF (temp_cascaded_table(current_n).currency_conversion_type IS NULL) THEN
2357 temp_cascaded_table(current_n).currency_conversion_type := x_shipmentdistributionrec.rate_type;
2358 END IF;
2359
2360 IF (temp_cascaded_table(current_n).currency_conversion_date IS NULL) THEN
2361 IF (x_shipmentdistributionrec.rate_type = 'User') THEN
2362 temp_cascaded_table(current_n).currency_conversion_date := x_shipmentdistributionrec.rate_date;
2363 ELSE
2364 temp_cascaded_table(current_n).currency_conversion_date := SYSDATE;
2365 END IF;
2366 END IF;
2367
2368 /*
2369 ** Copy the distribution specific information only if this is a direct receipt.
2370 */
2371 IF ( x_cascaded_table(n).transaction_type = 'DELIVER'
2372 OR NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') = 'DELIVER') THEN
2373 temp_cascaded_table(current_n).po_distribution_id := x_shipmentdistributionrec.po_distribution_id;
2374 temp_cascaded_table(current_n).charge_account_id := x_shipmentdistributionrec.code_combination_id;
2375 temp_cascaded_table(current_n).req_distribution_id := x_shipmentdistributionrec.req_distribution_id;
2376 -- temp_cascaded_table(current_n).currency_conversion_date := x_ShipmentDistributionRec.rate_date;
2377 -- temp_cascaded_table(current_n).currency_conversion_rate := x_ShipmentDistributionRec.rate;
2378 temp_cascaded_table(current_n).destination_type_code := x_shipmentdistributionrec.destination_type_code;
2379 temp_cascaded_table(current_n).destination_context := x_shipmentdistributionrec.destination_type_code;
2380
2381 IF (NVL(temp_cascaded_table(current_n).deliver_to_location_id, 0) = 0) THEN
2382 temp_cascaded_table(current_n).deliver_to_location_id := x_shipmentdistributionrec.deliver_to_location_id;
2383 END IF;
2384
2385 /* Bug 2392074 - If the deliver_to_person mentioned in the po_distributions is
2386 invalid or inactive at the time of Receipt we need to clear the deliver to person,
2387 as this is an optional field. */
2388 IF (NVL(temp_cascaded_table(current_n).deliver_to_person_id, 0) = 0) THEN
2389 temp_cascaded_table(current_n).deliver_to_person_id := x_shipmentdistributionrec.deliver_to_person_id;
2390
2391 IF (temp_cascaded_table(current_n).deliver_to_person_id IS NOT NULL) THEN
2392 BEGIN
2393 SELECT NVL(MAX(hre.full_name), 'notfound')
2394 INTO x_full_name
2395 FROM hr_employees_current_v hre
2396 WHERE ( hre.inactive_date IS NULL
2397 OR hre.inactive_date > SYSDATE)
2398 AND hre.employee_id = temp_cascaded_table(current_n).deliver_to_person_id;
2399
2400 IF (x_full_name = 'notfound') THEN
2401 temp_cascaded_table(current_n).deliver_to_person_id := NULL;
2402 END IF;
2403 EXCEPTION
2404 WHEN NO_DATA_FOUND THEN
2405 temp_cascaded_table(current_n).deliver_to_person_id := NULL;
2406
2407 IF (g_asn_debug = 'Y') THEN
2408 asn_debug.put_line('The deliver to person entered in PO is currently inactive');
2409 asn_debug.put_line(' So it is cleared off');
2410 END IF;
2411 WHEN OTHERS THEN
2412 temp_cascaded_table(current_n).deliver_to_person_id := NULL;
2413
2414 IF (g_asn_debug = 'Y') THEN
2415 asn_debug.put_line('Some exception has occured');
2416 asn_debug.put_line('This exception is due to the PO deliver to person');
2417 asn_debug.put_line('The deliver to person is optional');
2418 asn_debug.put_line('So cleared off the deliver to person');
2419 END IF;
2420 END;
2421 END IF;
2422 END IF;
2423
2424 IF (temp_cascaded_table(current_n).subinventory IS NULL) THEN
2425 temp_cascaded_table(current_n).subinventory := x_shipmentdistributionrec.destination_subinventory;
2426 END IF;
2427
2428 temp_cascaded_table(current_n).wip_entity_id := x_shipmentdistributionrec.wip_entity_id;
2429 temp_cascaded_table(current_n).wip_operation_seq_num := x_shipmentdistributionrec.wip_operation_seq_num;
2430 temp_cascaded_table(current_n).wip_resource_seq_num := x_shipmentdistributionrec.wip_resource_seq_num;
2431 temp_cascaded_table(current_n).wip_repetitive_schedule_id := x_shipmentdistributionrec.wip_repetitive_schedule_id;
2432 temp_cascaded_table(current_n).wip_line_id := x_shipmentdistributionrec.wip_line_id;
2433 temp_cascaded_table(current_n).bom_resource_id := x_shipmentdistributionrec.bom_resource_id;
2434
2435 -- bug 1361786
2436 IF (temp_cascaded_table(current_n).ussgl_transaction_code IS NULL) THEN
2437 temp_cascaded_table(current_n).ussgl_transaction_code := x_shipmentdistributionrec.ussgl_transaction_code;
2438 END IF;
2439 END IF;
2440
2441 current_n := current_n + 1;
2442
2443 IF (g_asn_debug = 'Y') THEN
2444 asn_debug.put_line('Increment pointer by 1 ' || TO_CHAR(current_n));
2445 END IF;
2446 END IF;
2447 END IF;
2448 END LOOP;
2449 -- current_n := current_n - 1; -- point to the last row in the record structure before going back
2450
2451 ELSE
2452 -- error_status and error_message are set after validate_quantity_shipped
2453 IF (g_asn_debug = 'Y') THEN
2454 asn_debug.put_line('No po_header_id/item_id ');
2455 END IF;
2456
2457 IF (g_asn_debug = 'Y') THEN
2458 asn_debug.put_line('Status = ' || x_cascaded_table(n).error_status);
2459 END IF;
2460
2461 IF x_cascaded_table(n).error_status IN('S', 'W', 'F') THEN
2462 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
2463
2464 IF (x_cascaded_table(n).error_message IS NULL) THEN
2465 rcv_error_pkg.set_error_message('RCV_ASN_NO_PO_LINE_LOCATION_ID', x_cascaded_table(n).error_message);
2466 rcv_error_pkg.set_token('DOCUMENT_NUM', x_cascaded_table(n).document_num);
2467 END IF;
2468
2469 rcv_error_pkg.log_interface_error('DOCUMENT_NUM', FALSE);
2470 END IF;
2471
2472 RETURN;
2473 END IF; -- of (asn quantity_shipped was valid)
2474
2475 IF shipments%ISOPEN THEN
2476 CLOSE shipments;
2477 END IF;
2478
2479 IF count_shipments%ISOPEN THEN
2480 CLOSE count_shipments;
2481 END IF;
2482
2483 IF distributions%ISOPEN THEN
2484 CLOSE distributions;
2485 END IF;
2486
2487 IF count_distributions%ISOPEN THEN
2488 CLOSE count_distributions;
2489 END IF;
2490
2491 IF (g_asn_debug = 'Y') THEN
2492 asn_debug.put_line('Exit derive_shipment_line');
2493 END IF;
2494 EXCEPTION
2495 WHEN OTHERS THEN
2496 IF shipments%ISOPEN THEN
2497 CLOSE shipments;
2498 END IF;
2499
2500 IF count_shipments%ISOPEN THEN
2501 CLOSE count_shipments;
2502 END IF;
2503
2504 IF distributions%ISOPEN THEN
2505 CLOSE distributions;
2506 END IF;
2507
2508 IF count_distributions%ISOPEN THEN
2509 CLOSE count_distributions;
2510 END IF;
2511
2512 x_cascaded_table(n).error_status := 'F';
2513
2514 IF (g_asn_debug = 'Y') THEN
2515 asn_debug.put_line(TO_CHAR(n));
2516 asn_debug.put_line(SQLERRM);
2517 asn_debug.put_line('error ' || x_progress);
2518 END IF;
2519 END derive_shipment_line;
2520
2521 /*===========================================================================
2522
2523 PROCEDURE NAME: default_shipment_line()
2524
2525 ===========================================================================*/
2526 PROCEDURE default_shipment_line(
2527 x_cascaded_table IN OUT NOCOPY rcv_shipment_object_sv.cascaded_trans_tab_type,
2528 n IN BINARY_INTEGER,
2529 x_header_id IN rcv_headers_interface.header_interface_id%TYPE,
2530 x_header_record IN rcv_shipment_header_sv.headerrectype
2531 ) IS
2532 x_progress VARCHAR2(3);
2533 x_locator_control NUMBER;
2534 x_default_subinventory VARCHAR2(10);
2535 x_default_locator_id NUMBER;
2536 x_success BOOLEAN;
2537 x_tax_name VARCHAR2(50); -- Bug 6331613
2538 x_vendor_site_id NUMBER;
2539 x_vendor_site_code VARCHAR2(20);
2540
2541 /* bug2382337
2542 * Change the name of the parameters passed into the cursor
2543 */
2544 CURSOR shipments(
2545 v_header_id NUMBER,
2546 v_line_id NUMBER,
2547 v_line_location_id NUMBER
2548 ) IS
2549 SELECT ph.revision_num,
2550 pl.line_num,
2551 pl.item_description,
2552 pll.tax_code_id,
2553 pll.po_release_id,
2554 pll.ship_to_location_id,
2555 pll.ship_to_organization_id,
2556 pll.shipment_num,
2557 pll.receiving_routing_id,
2558 pll.country_of_origin_code
2559 FROM po_line_locations pll,
2560 po_lines pl,
2561 po_headers ph
2562 WHERE ph.po_header_id = pl.po_header_id
2563 AND pl.po_line_id = pll.po_line_id
2564 AND ph.po_header_id = v_header_id
2565 AND pl.po_line_id = v_line_id
2566 AND pll.line_location_id = v_line_location_id
2567 AND NVL(pll.approved_flag, 'N') = 'Y'
2568 AND NVL(pll.cancel_flag, 'N') = 'N'
2569 AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
2570 AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED');
2571
2572 default_po_info shipments%ROWTYPE;
2573 BEGIN
2574 IF (g_asn_debug = 'Y') THEN
2575 asn_debug.put_line('Enter default_shipment_line');
2576 END IF;
2577
2578 x_progress := '000';
2579 -- set default_shipment_line values
2580
2581 x_cascaded_table(n).header_interface_id := x_header_id;
2582 x_cascaded_table(n).shipment_line_status_code := 'OPEN';
2583
2584 IF x_cascaded_table(n).receipt_source_code IS NULL THEN
2585 x_cascaded_table(n).receipt_source_code := NVL(x_header_record.header_record.receipt_source_code, 'VENDOR');
2586
2587 IF (g_asn_debug = 'Y') THEN
2588 asn_debug.put_line('Defaulting from HEADER RECEIPT_SOURCE_CODE ' || x_cascaded_table(n).receipt_source_code);
2589 END IF;
2590 END IF;
2591
2592 IF x_cascaded_table(n).source_document_code IS NULL THEN
2593 x_cascaded_table(n).source_document_code := 'PO';
2594
2595 IF (g_asn_debug = 'Y') THEN
2596 asn_debug.put_line('Defaulting SOURCE_DOCUMENT_CODE ' || x_cascaded_table(n).source_document_code);
2597 END IF;
2598 END IF;
2599
2600 /* Fix for bug 2564646.
2601 If AUTO_TRANSACT_CODE is RECEIVE then it means it is a receive
2602 transaction and destination_type_code should be RECEIVING. Even
2603 if the end user populates destination_type_code as INVENTORY
2604 when AUTO_TRANSACT_CODE is RECEIVE, we now overwrite the value
2605 of DESTINATION_TYPE_CODE to RECEIVING by adding the OR condition
2606 to the following IF statement.
2607 */
2608 IF x_cascaded_table(n).destination_type_code IS NULL
2609 OR ( x_cascaded_table(n).destination_type_code = 'INVENTORY'
2610 AND x_cascaded_table(n).auto_transact_code = 'RECEIVE') THEN
2611 x_cascaded_table(n).destination_type_code := 'RECEIVING';
2612
2613 IF (g_asn_debug = 'Y') THEN
2614 asn_debug.put_line('Defaulting DESTINATION_TYPE_CODE ' || x_cascaded_table(n).destination_type_code);
2615 END IF;
2616 END IF;
2617
2618 IF x_cascaded_table(n).transaction_type IS NULL THEN
2619 x_cascaded_table(n).transaction_type := 'SHIP';
2620
2621 IF (g_asn_debug = 'Y') THEN
2622 asn_debug.put_line('Defaulting TRANSACTION_TYPE ' || x_cascaded_table(n).transaction_type);
2623 END IF;
2624 END IF;
2625
2626 IF x_cascaded_table(n).processing_mode_code IS NULL THEN
2627 x_cascaded_table(n).processing_mode_code := 'BATCH';
2628
2629 IF (g_asn_debug = 'Y') THEN
2630 asn_debug.put_line('Defaulting PROCESSING_MODE_CODE ' || x_cascaded_table(n).processing_mode_code);
2631 END IF;
2632 END IF;
2633
2634 x_cascaded_table(n).processing_status_code := 'RUNNING';
2635
2636 IF x_cascaded_table(n).processing_status_code IS NULL THEN
2637 -- This has to be set to running otherwise C code in rvtbm
2638 -- will not pick it up
2639 x_cascaded_table(n).processing_status_code := 'RUNNING';
2640
2641 IF (g_asn_debug = 'Y') THEN
2642 asn_debug.put_line('Defaulting PROCESSING_STATUS_CODE ' || x_cascaded_table(n).processing_status_code);
2643 END IF;
2644 END IF;
2645
2646 IF x_cascaded_table(n).transaction_status_code IS NULL THEN
2647 x_cascaded_table(n).transaction_status_code := 'PENDING';
2648
2649 IF (g_asn_debug = 'Y') THEN
2650 asn_debug.put_line('Defaulting TRANSACTION_STATUS_CODE ' || x_cascaded_table(n).transaction_status_code);
2651 END IF;
2652 END IF;
2653
2654 -- Default auto_transact_code if it is null
2655
2656 IF x_cascaded_table(n).auto_transact_code IS NULL THEN
2657 IF (g_asn_debug = 'Y') THEN
2658 asn_debug.put_line('Setting auto_transact_code to transaction_type ' || x_cascaded_table(n).transaction_type);
2659 END IF;
2660
2661 x_cascaded_table(n).auto_transact_code := x_cascaded_table(n).transaction_type;
2662 END IF;
2663
2664 -- default only if all attributes are null
2665
2666 IF x_cascaded_table(n).vendor_id IS NULL
2667 AND x_cascaded_table(n).vendor_name IS NULL
2668 AND x_cascaded_table(n).vendor_num IS NULL THEN
2669 x_cascaded_table(n).vendor_id := x_header_record.header_record.vendor_id;
2670 x_cascaded_table(n).vendor_name := x_header_record.header_record.vendor_name;
2671 x_cascaded_table(n).vendor_num := x_header_record.header_record.vendor_num;
2672
2673 IF (g_asn_debug = 'Y') THEN
2674 asn_debug.put_line('Defaulting from HEADER VENDOR_ID ' || TO_CHAR(x_cascaded_table(n).vendor_id));
2675 asn_debug.put_line('Defaulting from HEADER VENDOR_NAME ' || x_cascaded_table(n).vendor_name);
2676 asn_debug.put_line('Defaulting from HEADER VENDOR_NUM ' || x_cascaded_table(n).vendor_num);
2677 END IF;
2678 END IF;
2679
2680 -- default only if all attributes are null
2681
2682 IF x_cascaded_table(n).vendor_site_id IS NULL
2683 AND x_cascaded_table(n).vendor_site_code IS NULL THEN
2684 x_cascaded_table(n).vendor_site_id := x_header_record.header_record.vendor_site_id;
2685 x_cascaded_table(n).vendor_site_code := x_header_record.header_record.vendor_site_code;
2686
2687 /* Fix for bug 2296720.
2688 If both vendor_site_id and vendor_site_code are not populated
2689 in interface tables, and if there are multiple vendor sites
2690 associated to a particular vendor then we default them from PO
2691 using the po_header_id of rcv_transactions_interface for each
2692 line.
2693 */
2694 IF x_cascaded_table(n).vendor_site_id IS NULL
2695 AND x_cascaded_table(n).vendor_site_code IS NULL THEN
2696 SELECT vendor_site_id
2697 INTO x_vendor_site_id
2698 FROM po_headers
2699 WHERE po_header_id = x_cascaded_table(n).po_header_id
2700 AND vendor_id = x_cascaded_table(n).vendor_id;
2701
2702 SELECT vendor_site_code
2703 INTO x_vendor_site_code
2704 FROM po_vendor_sites
2705 WHERE vendor_site_id = x_vendor_site_id
2706 AND vendor_id = x_cascaded_table(n).vendor_id;
2707
2708 x_cascaded_table(n).vendor_site_id := x_vendor_site_id;
2709 x_cascaded_table(n).vendor_site_code := x_vendor_site_code;
2710 END IF;
2711
2712 IF (g_asn_debug = 'Y') THEN
2713 asn_debug.put_line('Defaulting from HEADER VENDOR_SITE_ID ' || TO_CHAR(x_cascaded_table(n).vendor_site_id));
2714 asn_debug.put_line('Defaulting from HEADER VENDOR_SITE_CODE ' || x_cascaded_table(n).vendor_site_code);
2715 END IF;
2716 END IF;
2717
2718 -- default only if all attributes are null
2719
2720 IF x_cascaded_table(n).from_organization_id IS NULL
2721 AND x_cascaded_table(n).from_organization_code IS NULL THEN
2722 x_cascaded_table(n).from_organization_id := x_header_record.header_record.from_organization_id;
2723 x_cascaded_table(n).from_organization_code := x_header_record.header_record.from_organization_code;
2724
2725 IF (g_asn_debug = 'Y') THEN
2726 asn_debug.put_line('Defaulting from HEADER FROM_ORGANIZATION_ID ' || TO_CHAR(x_cascaded_table(n).from_organization_id));
2727 asn_debug.put_line('Defaulting from HEADER FROM_ORGANIZATION_CODE ' || x_cascaded_table(n).from_organization_code);
2728 END IF;
2729 END IF;
2730
2731 -- default only if all attributes are null
2732
2733 IF x_cascaded_table(n).to_organization_id IS NULL
2734 AND x_cascaded_table(n).to_organization_code IS NULL THEN
2735 x_cascaded_table(n).to_organization_id := x_header_record.header_record.ship_to_organization_id;
2736 x_cascaded_table(n).to_organization_code := x_header_record.header_record.ship_to_organization_code;
2737
2738 IF (g_asn_debug = 'Y') THEN
2739 asn_debug.put_line('Defaulting from HEADER TO_ORGANIZATION_ID ' || TO_CHAR(x_cascaded_table(n).to_organization_id));
2740 asn_debug.put_line('Defaulting from HEADER TO_ORGANIZATION_CODE ' || x_cascaded_table(n).to_organization_code);
2741 END IF;
2742 END IF;
2743
2744 -- default only if all attributes are null
2745
2746 IF x_cascaded_table(n).currency_code IS NULL
2747 AND x_cascaded_table(n).currency_conversion_type IS NULL
2748 AND x_cascaded_table(n).currency_conversion_rate IS NULL
2749 AND x_cascaded_table(n).currency_conversion_date IS NULL THEN
2750 x_cascaded_table(n).currency_code := x_header_record.header_record.currency_code;
2751 x_cascaded_table(n).currency_conversion_type := x_header_record.header_record.conversion_rate_type;
2752 x_cascaded_table(n).currency_conversion_rate := x_header_record.header_record.conversion_rate;
2753 x_cascaded_table(n).currency_conversion_date := x_header_record.header_record.conversion_rate_date;
2754
2755 IF (g_asn_debug = 'Y') THEN
2756 asn_debug.put_line('Defaulting from HEADER CURRENCY_CODE ' || x_cascaded_table(n).currency_code);
2757 asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_TYPE ' || x_cascaded_table(n).currency_conversion_type);
2758 asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_RATE ' || TO_CHAR(x_cascaded_table(n).currency_conversion_rate));
2759 asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_DATE ' || TO_CHAR(x_cascaded_table(n).currency_conversion_date, 'DD/MM/YYYY'));
2760 END IF;
2761 END IF;
2762
2763 IF ( x_cascaded_table(n).ship_to_location_id IS NULL
2764 AND x_cascaded_table(n).ship_to_location_code IS NULL) THEN -- Check this with George
2765 x_cascaded_table(n).ship_to_location_code := x_header_record.header_record.location_code;
2766 x_cascaded_table(n).ship_to_location_id := x_header_record.header_record.location_id;
2767
2768 IF (g_asn_debug = 'Y') THEN
2769 asn_debug.put_line('Defaulting from HEADER LOCATION_ID ' || TO_CHAR(x_cascaded_table(n).location_id));
2770 END IF;
2771 END IF;
2772
2773 IF x_cascaded_table(n).shipment_num IS NULL THEN
2774 x_cascaded_table(n).shipment_num := x_header_record.header_record.shipment_num;
2775
2776 IF (g_asn_debug = 'Y') THEN
2777 asn_debug.put_line('Defaulting from HEADER SHIPMENT_NUM ' || x_cascaded_table(n).shipment_num);
2778 END IF;
2779 END IF;
2780
2781 IF x_cascaded_table(n).freight_carrier_code IS NULL THEN
2782 x_cascaded_table(n).freight_carrier_code := x_header_record.header_record.freight_carrier_code;
2783
2784 IF (g_asn_debug = 'Y') THEN
2785 asn_debug.put_line('Defaulting from HEADER FREIGHT_CARRIER_CODE ' || x_cascaded_table(n).freight_carrier_code);
2786 END IF;
2787 END IF;
2788
2789 IF x_cascaded_table(n).bill_of_lading IS NULL THEN
2790 x_cascaded_table(n).bill_of_lading := x_header_record.header_record.bill_of_lading;
2791
2792 IF (g_asn_debug = 'Y') THEN
2793 asn_debug.put_line('Defaulting from HEADER BILL_OF_LADING ' || x_cascaded_table(n).bill_of_lading);
2794 END IF;
2795 END IF;
2796
2797 IF x_cascaded_table(n).packing_slip IS NULL THEN
2798 x_cascaded_table(n).packing_slip := x_header_record.header_record.packing_slip;
2799
2800 IF (g_asn_debug = 'Y') THEN
2801 asn_debug.put_line('Defaulting from HEADER PACKING_SLIP ' || x_cascaded_table(n).packing_slip);
2802 END IF;
2803 END IF;
2804
2805 IF x_cascaded_table(n).shipped_date IS NULL THEN
2806 x_cascaded_table(n).shipped_date := x_header_record.header_record.shipped_date;
2807
2808 IF (g_asn_debug = 'Y') THEN
2809 asn_debug.put_line('Defaulting from HEADER SHIPPED_DATE ' || TO_CHAR(x_cascaded_table(n).shipped_date, 'DD/MM/YYYY'));
2810 END IF;
2811 END IF;
2812
2813 IF x_cascaded_table(n).expected_receipt_date IS NULL THEN
2814 x_cascaded_table(n).expected_receipt_date := x_header_record.header_record.expected_receipt_date;
2815
2816 IF (g_asn_debug = 'Y') THEN
2817 asn_debug.put_line('Defaulting from HEADER EXPECTED_RECEIPT_DATE ' || TO_CHAR(x_cascaded_table(n).expected_receipt_date, 'DD/MM/YYYY'));
2818 END IF;
2819 END IF;
2820
2821 IF x_cascaded_table(n).num_of_containers IS NULL THEN
2822 x_cascaded_table(n).num_of_containers := x_header_record.header_record.num_of_containers;
2823
2824 IF (g_asn_debug = 'Y') THEN
2825 asn_debug.put_line('Defaulting from HEADER NUM_OF_CONTAINERS ' || TO_CHAR(x_cascaded_table(n).num_of_containers));
2826 END IF;
2827 END IF;
2828
2829 IF x_cascaded_table(n).waybill_airbill_num IS NULL THEN
2830 x_cascaded_table(n).waybill_airbill_num := x_header_record.header_record.waybill_airbill_num;
2831
2832 IF (g_asn_debug = 'Y') THEN
2833 asn_debug.put_line('Defaulting from HEADER WAYBILL_AIRBILL_NUM ' || x_cascaded_table(n).waybill_airbill_num);
2834 END IF;
2835 END IF;
2836
2837 IF x_cascaded_table(n).tax_name IS NULL THEN
2838 x_cascaded_table(n).tax_name := x_header_record.header_record.tax_name;
2839
2840 IF (g_asn_debug = 'Y') THEN
2841 asn_debug.put_line('Defaulting from HEADER TAX_NAME ' || x_cascaded_table(n).tax_name);
2842 END IF;
2843 END IF;
2844
2845 IF x_cascaded_table(n).item_revision IS NULL THEN
2846 IF (g_asn_debug = 'Y') THEN
2847 asn_debug.put_line('Enter defaulting item revision');
2848 END IF;
2849
2850 item_id_record.item_id := x_cascaded_table(n).item_id;
2851 item_id_record.po_line_id := x_cascaded_table(n).po_line_id;
2852 item_id_record.po_line_location_id := x_cascaded_table(n).po_line_location_id;
2853 item_id_record.to_organization_id := x_cascaded_table(n).to_organization_id;
2854 item_id_record.item_revision := x_cascaded_table(n).item_revision;
2855 item_id_record.error_record.error_status := 'S';
2856 item_id_record.error_record.error_message := NULL;
2857 default_item_revision(item_id_record);
2858 x_cascaded_table(n).item_revision := item_id_record.item_revision;
2859
2860 IF (g_asn_debug = 'Y') THEN
2861 asn_debug.put_line(NVL(item_id_record.item_revision, 'Item Revision is null'));
2862 END IF;
2863
2864 x_cascaded_table(n).error_status := item_id_record.error_record.error_status;
2865 rcv_error_pkg.set_error_message(item_id_record.error_record.error_message, x_cascaded_table(n).error_message);
2866 END IF;
2867
2868 IF x_cascaded_table(n).po_revision_num IS NULL
2869 OR x_cascaded_table(n).freight_carrier_code IS NULL
2870 OR x_cascaded_table(n).document_line_num IS NULL
2871 OR x_cascaded_table(n).item_description IS NULL
2872 OR x_cascaded_table(n).tax_name IS NULL
2873 OR
2874 --FRKHAN 12/18/98 add country of origin check
2875 x_cascaded_table(n).country_of_origin_code IS NULL
2876 OR x_cascaded_table(n).po_release_id IS NULL
2877 OR ( x_cascaded_table(n).ship_to_location_id IS NULL
2878 AND x_cascaded_table(n).ship_to_location_code IS NULL)
2879 OR ( x_cascaded_table(n).to_organization_id IS NULL
2880 AND x_cascaded_table(n).to_organization_code IS NULL)
2881 OR x_cascaded_table(n).document_shipment_line_num IS NULL
2882 OR ( x_cascaded_table(n).routing_header_id IS NULL
2883 AND x_cascaded_table(n).routing_code IS NULL) THEN
2884 IF (g_asn_debug = 'Y') THEN
2885 asn_debug.put_line('Defaulting values from PO ');
2886 END IF;
2887
2888 OPEN shipments(x_cascaded_table(n).po_header_id,
2889 x_cascaded_table(n).po_line_id,
2890 x_cascaded_table(n).po_line_location_id
2891 );
2892 FETCH shipments INTO default_po_info;
2893
2894 IF shipments%FOUND THEN
2895 IF x_cascaded_table(n).po_revision_num IS NULL THEN
2896 IF (g_asn_debug = 'Y') THEN
2897 asn_debug.put_line('Defaulting PO HEADER revision num ' || default_po_info.revision_num);
2898 END IF;
2899
2900 x_cascaded_table(n).po_revision_num := default_po_info.revision_num;
2901 END IF;
2902
2903 IF x_cascaded_table(n).document_line_num IS NULL THEN
2904 IF (g_asn_debug = 'Y') THEN
2905 asn_debug.put_line('Defaulting po line num ' || TO_CHAR(default_po_info.line_num));
2906 END IF;
2907
2908 x_cascaded_table(n).document_line_num := default_po_info.line_num;
2909 END IF;
2910
2911 IF x_cascaded_table(n).item_description IS NULL THEN
2912 IF (g_asn_debug = 'Y') THEN
2913 asn_debug.put_line('Defaulting item description from PO ' || default_po_info.item_description);
2914 END IF;
2915
2916 x_cascaded_table(n).item_description := default_po_info.item_description;
2917 END IF;
2918
2919 IF x_cascaded_table(n).tax_name IS NULL
2920 AND default_po_info.tax_code_id IS NOT NULL THEN
2921 IF (g_asn_debug = 'Y') THEN
2922 asn_debug.put_line('Defaulting tax name based on PO ' || TO_CHAR(default_po_info.tax_code_id));
2923 END IF;
2924
2925 -- Need to join to ap_tax_codes to get tax_name
2926
2927 BEGIN
2928 SELECT NAME
2929 INTO x_tax_name
2930 FROM ap_tax_codes
2931 WHERE ap_tax_codes.tax_id = default_po_info.tax_code_id;
2932
2933 x_cascaded_table(n).tax_name := x_tax_name;
2934 EXCEPTION
2935 WHEN OTHERS THEN
2936 IF (g_asn_debug = 'Y') THEN
2937 asn_debug.put_line('Some error occured in the tax name derivation');
2938 END IF;
2939 END;
2940 END IF;
2941
2942 --FRKHAN 12/18/98 default country of origin from PO
2943 IF x_cascaded_table(n).country_of_origin_code IS NULL THEN
2944 IF (g_asn_debug = 'Y') THEN
2945 asn_debug.put_line('Defaulting country of origin from PO ' || default_po_info.country_of_origin_code);
2946 END IF;
2947
2948 x_cascaded_table(n).country_of_origin_code := default_po_info.country_of_origin_code;
2949 END IF;
2950
2951 IF x_cascaded_table(n).po_release_id IS NULL THEN
2952 IF (g_asn_debug = 'Y') THEN
2953 asn_debug.put_line('Defaulting PO Release ID from PO ' || TO_CHAR(default_po_info.po_release_id));
2954 END IF;
2955
2956 x_cascaded_table(n).po_release_id := default_po_info.po_release_id;
2957 END IF;
2958
2959 IF x_cascaded_table(n).ship_to_location_id IS NULL THEN
2960 IF (g_asn_debug = 'Y') THEN
2961 asn_debug.put_line('Defaulting PO ship_to_location_id ' || TO_CHAR(default_po_info.ship_to_location_id));
2962 END IF;
2963
2964 x_cascaded_table(n).ship_to_location_id := default_po_info.ship_to_location_id;
2965 END IF;
2966
2967 IF x_cascaded_table(n).to_organization_id IS NULL THEN
2968 IF (g_asn_debug = 'Y') THEN
2969 asn_debug.put_line('Defaulting PO to_organization_id ' || TO_CHAR(default_po_info.ship_to_organization_id));
2970 END IF;
2971
2972 x_cascaded_table(n).to_organization_id := default_po_info.ship_to_organization_id;
2973 END IF;
2974
2975 IF x_cascaded_table(n).document_shipment_line_num IS NULL THEN
2976 IF (g_asn_debug = 'Y') THEN
2977 asn_debug.put_line('Defaulting PO shipment_line_num ' || TO_CHAR(default_po_info.shipment_num));
2978 END IF;
2979
2980 x_cascaded_table(n).document_shipment_line_num := default_po_info.shipment_num;
2981 END IF;
2982
2983 IF x_cascaded_table(n).routing_header_id IS NULL THEN
2984 IF (g_asn_debug = 'Y') THEN
2985 asn_debug.put_line('Defaulting PO routing_header_id ' || TO_CHAR(default_po_info.receiving_routing_id));
2986 END IF;
2987
2988 x_cascaded_table(n).routing_header_id := default_po_info.receiving_routing_id;
2989 END IF;
2990 END IF;
2991 END IF;
2992
2993 IF (g_asn_debug = 'Y') THEN
2994 asn_debug.put_line('Primary UOM = ' || x_cascaded_table(n).primary_unit_of_measure);
2995 END IF;
2996
2997 /*
2998 ** Default the subinventory and locator if they have not been set either through the interface
2999 ** or defaulted from the purchase order
3000 */
3001 IF ( x_cascaded_table(n).destination_type_code = 'INVENTORY'
3002 AND ( NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') = 'DELIVER'
3003 OR x_cascaded_table(n).transaction_type = 'DELIVER')) THEN
3004 /*
3005 ** A subinventory must have been defined on the po or a default
3006 ** must be available for the item. If it's not already defined
3007 ** then go get it out of inventory. If you're using express
3008 ** then it's ok to get the default rather than having it be
3009 ** defined on the record
3010 */
3011 IF (x_cascaded_table(n).subinventory IS NULL) THEN
3012 /*
3013 ** If you're using express then it's ok to get the default
3014 ** rather than having it be defined on the record
3015 */
3016 x_progress := '120';
3017 po_subinventories_s.get_default_subinventory(x_cascaded_table(n).to_organization_id,
3018 x_cascaded_table(n).item_id,
3019 x_cascaded_table(n).subinventory
3020 );
3021
3022 IF (g_asn_debug = 'Y') THEN
3023 asn_debug.put_line('Defaulting subinventory from item: Sub = ' || x_cascaded_table(n).subinventory);
3024 END IF;
3025 END IF; -- (X_cascaded_table(n).subinventory IS NULL)
3026
3027 /*
3028 ** See if org/sub/item is under locator control. If the sub is
3029 ** not available then don't do this call since it won't matter
3030 ** because the row will fail without a sub
3031 */
3032 IF (x_cascaded_table(n).subinventory IS NOT NULL) THEN
3033 x_progress := '122';
3034 po_subinventories_s.get_locator_control(x_cascaded_table(n).to_organization_id,
3035 x_cascaded_table(n).subinventory,
3036 x_cascaded_table(n).item_id,
3037 x_locator_control
3038 );
3039
3040 IF (g_asn_debug = 'Y') THEN
3041 asn_debug.put_line('Locator Control = ' || TO_CHAR(x_locator_control));
3042 END IF;
3043
3044 /*
3045 ** If locator control is 2 which means it is under predefined
3046 ** locator contol or 3 which means it's under dynamic (any value)
3047 ** locator control then you need to go get the default locator id
3048 */
3049 IF ( ( x_locator_control = 2
3050 OR x_locator_control = 3)
3051 AND x_cascaded_table(n).locator_id IS NULL) THEN
3052 x_progress := '123';
3053 po_subinventories_s.get_default_locator(x_cascaded_table(n).to_organization_id,
3054 x_cascaded_table(n).item_id,
3055 x_cascaded_table(n).subinventory,
3056 x_cascaded_table(n).locator_id
3057 );
3058
3059 IF (g_asn_debug = 'Y') THEN
3060 asn_debug.put_line('Defaulting locator from Sub: Locator_id = ' || TO_CHAR(x_cascaded_table(n).locator_id));
3061 END IF;
3062 END IF;
3063 END IF;
3064
3065 x_default_subinventory := x_cascaded_table(n).subinventory;
3066 x_default_locator_id := x_cascaded_table(n).locator_id;
3067 /*
3068 ** Call the put away function
3069 */
3070 x_success := rcv_sub_locator_sv.put_away_api(x_cascaded_table(n).po_line_location_id,
3071 x_cascaded_table(n).po_distribution_id,
3072 x_cascaded_table(n).shipment_line_id,
3073 x_cascaded_table(n).receipt_source_code,
3074 x_cascaded_table(n).from_organization_id,
3075 x_cascaded_table(n).to_organization_id,
3076 x_cascaded_table(n).item_id,
3077 x_cascaded_table(n).item_revision,
3078 x_cascaded_table(n).vendor_id,
3079 x_cascaded_table(n).ship_to_location_id,
3080 x_cascaded_table(n).deliver_to_location_id,
3081 x_cascaded_table(n).deliver_to_person_id,
3082 x_cascaded_table(n).quantity,
3083 x_cascaded_table(n).primary_quantity,
3084 x_cascaded_table(n).primary_unit_of_measure,
3085 x_cascaded_table(n).quantity,
3086 x_cascaded_table(n).unit_of_measure,
3087 x_cascaded_table(n).routing_header_id,
3088 x_default_subinventory,
3089 x_default_locator_id,
3090 x_cascaded_table(n).subinventory,
3091 x_cascaded_table(n).locator_id
3092 );
3093 END IF; -- (X_cascaded_table(n).destination_type_code = 'INVENTORY' AND...)
3094
3095 /*
3096 ** Make sure to set the location_id properly
3097 */
3098 IF ( NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') = 'DELIVER'
3099 OR x_cascaded_table(n).transaction_type = 'DELIVER') THEN
3100 x_cascaded_table(n).location_id := x_cascaded_table(n).deliver_to_location_id;
3101 ELSE
3102 x_cascaded_table(n).location_id := x_cascaded_table(n).ship_to_location_id;
3103 END IF;
3104
3105 IF (g_asn_debug = 'Y') THEN
3106 asn_debug.put_line('Set Location_id = ' || TO_CHAR(x_cascaded_table(n).location_id));
3107 END IF;
3108
3109 IF x_cascaded_table(n).waybill_airbill_num IS NULL THEN
3110 x_cascaded_table(n).waybill_airbill_num := x_header_record.header_record.waybill_airbill_num;
3111
3112 IF (g_asn_debug = 'Y') THEN
3113 asn_debug.put_line('Defaulting from HEADER WAYBILL_AIRBILL_NUM ' || x_cascaded_table(n).waybill_airbill_num);
3114 END IF;
3115 END IF;
3116
3117 -- if not a one-time purchase item
3118 -- This may not be needed as we would have encoutered problems during cascade
3119 -- if primary_unit_of_measure was absent ???
3120
3121 -- if (x_cascaded_table(n).item_id is not null) then
3122
3123 -- null;
3124 -- select min(primary_unit_of_measure)
3125 -- into x_cascaded_table(n).primary_unit_of_measure
3126 -- from mtl_system_items
3127 -- where inventory_item_id = x_cascaded_table(n).item_id and
3128 -- organization_id = x_cascaded_table(n).to_organization_id;
3129 -- else
3130
3131 -- if it's a one-time item, use the base uom for the class
3132
3133 -- begin
3134
3135 -- SELECT min(unit_of_measure)
3136 -- INTO x_cascaded_table(n).primary_unit_of_measure
3137 -- FROM mtl_units_of_measure mum
3138 -- WHERE uom_class = x_cascaded_table(n).unit_of_measure_class
3139 -- AND mum.base_uom_flag = 'Y';
3140
3141 -- exception
3142
3143 -- when no_data_found then null;
3144
3145 -- end;
3146
3147
3148 -- end if;
3149
3150 x_progress := '010';
3151
3152 IF (g_asn_debug = 'Y') THEN
3153 asn_debug.put_line('Exit default_shipment_line');
3154 END IF;
3155 END default_shipment_line;
3156
3157 /*===========================================================================
3158
3159 PROCEDURE NAME: validate_shipment_line()
3160
3161 ===========================================================================*/
3162 PROCEDURE validate_shipment_line(
3163 x_cascaded_table IN OUT NOCOPY rcv_shipment_object_sv.cascaded_trans_tab_type,
3164 n IN BINARY_INTEGER,
3165 x_asn_type IN rcv_headers_interface.asn_type%TYPE,
3166 x_header_record IN rcv_shipment_header_sv.headerrectype
3167 ) IS
3168 x_progress VARCHAR2(3) := NULL;
3169 x_sob_id NUMBER := NULL;
3170 x_val_open_ok BOOLEAN := NULL;
3171 /* Added the following variable for bug 3009663 */
3172 x_allow_substitute_receipts VARCHAR2(1) := 'N';
3173 BEGIN
3174 IF (x_cascaded_table(n).error_status NOT IN('S', 'W')) THEN
3175 RETURN;
3176 END IF;
3177
3178 IF (g_asn_debug = 'Y') THEN
3179 asn_debug.put_line('Enter validate_shipment_line');
3180 END IF;
3181
3182 x_progress := '000';
3183
3184 /*Bug 2327318 Implemented the validation Transaction date should not be greater than
3185 sysdate */
3186 IF (x_cascaded_table(n).transaction_date > SYSDATE) THEN
3187 rcv_error_pkg.set_error_message('RCV_TRX_FUTURE_DATE_NA', x_cascaded_table(n).error_message);
3188 rcv_error_pkg.log_interface_error('TRANSACTION_DATE');
3189 END IF;
3190
3191 -- bug 642624 validate if PO and GL periods are open in pre-processor
3192
3193
3194 /* Bug 2653229 - To check if the transaction date falls in the open period only
3195 when the auto transact code is not SHIP. */
3196 IF (x_cascaded_table(n).auto_transact_code <> 'SHIP') THEN
3197 -- need this block to handle the exception when no period is defined for the txn date
3198
3199 /*
3200 BEGIN
3201 SELECT set_of_books_id
3202 INTO x_sob_id
3203 FROM financials_system_parameters;
3204
3205 x_val_open_ok := PO_DATES_S.val_open_period(x_cascaded_table(n).transaction_date,x_sob_id,'PO',
3206 x_cascaded_table(n).to_organization_id) AND
3207 PO_DATES_S.val_open_period(x_cascaded_table(n).transaction_date,x_sob_id,'SQLGL',
3208 x_cascaded_table(n).to_organization_id);
3209 EXCEPTION
3210 WHEN OTHERS THEN
3211 x_val_open_ok := FALSE;
3212
3213 END;
3214 */
3215 /* Bug# 2379848 - We were only checking for GL and PO periods
3216 and not for INV periods. Also we were displaying the same
3217 error message always */
3218 BEGIN
3219 SELECT set_of_books_id
3220 INTO x_sob_id
3221 FROM financials_system_parameters;
3222 EXCEPTION
3223 WHEN NO_DATA_FOUND THEN
3224 NULL;
3225
3226 IF (g_asn_debug = 'Y') THEN
3227 asn_debug.put_line('Set of books id not defined');
3228 END IF;
3229 END;
3230
3231 BEGIN
3232 x_val_open_ok := po_dates_s.val_open_period(x_cascaded_table(n).transaction_date,
3233 x_sob_id,
3234 'SQLGL',
3235 x_cascaded_table(n).to_organization_id
3236 );
3237 EXCEPTION
3238 WHEN OTHERS THEN
3239 x_val_open_ok := FALSE;
3240 END;
3241
3242 IF NOT(x_val_open_ok) THEN
3243 rcv_error_pkg.set_error_message('PO_CNL_NO_PERIOD', x_cascaded_table(n).error_message);
3244 rcv_error_pkg.log_interface_error('TRANSACTION_DATE');
3245 END IF;
3246
3247 BEGIN
3248 x_val_open_ok := po_dates_s.val_open_period(x_cascaded_table(n).transaction_date,
3249 x_sob_id,
3250 'INV',
3251 x_cascaded_table(n).to_organization_id
3252 );
3253 EXCEPTION
3254 WHEN OTHERS THEN
3255 x_val_open_ok := FALSE;
3256 END;
3257
3258 IF NOT(x_val_open_ok) THEN
3259 rcv_error_pkg.set_error_message('PO_INV_NO_OPEN_PERIOD', x_cascaded_table(n).error_message);
3260 rcv_error_pkg.log_interface_error('TRANSACTION_DATE');
3261 END IF;
3262
3263 BEGIN
3264 x_val_open_ok := po_dates_s.val_open_period(x_cascaded_table(n).transaction_date,
3265 x_sob_id,
3266 'PO',
3267 x_cascaded_table(n).to_organization_id
3268 );
3269 EXCEPTION
3270 WHEN OTHERS THEN
3271 x_val_open_ok := FALSE;
3272 END;
3273
3274 IF NOT(x_val_open_ok) THEN
3275 rcv_error_pkg.set_error_message('PO_PO_ENTER_OPEN_GL_DATE', x_cascaded_table(n).error_message);
3276 rcv_error_pkg.log_interface_error('TRANSACTION_DATE');
3277 END IF; /* End of Bug# 2379848 */
3278 END IF; -- auto transact code = SHIP
3279
3280 IF (x_asn_type = 'ASBN') THEN
3281 quantity_invoiced_record.quantity_invoiced := x_cascaded_table(n).quantity_invoiced;
3282 quantity_invoiced_record.error_record.error_status := 'S';
3283 quantity_invoiced_record.error_record.error_message := NULL;
3284 rcv_transactions_interface_sv1.validate_quantity_invoiced(quantity_invoiced_record);
3285 x_cascaded_table(n).error_status := quantity_invoiced_record.error_record.error_status;
3286 rcv_error_pkg.set_error_message(quantity_invoiced_record.error_record.error_message, x_cascaded_table(n).error_message);
3287 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'QUANTITY_INVOICED');
3288 END IF;
3289
3290 IF (g_asn_debug = 'Y') THEN
3291 asn_debug.put_line('Before call to validate UOM');
3292 asn_debug.put_line('Quantity ' || TO_CHAR(x_cascaded_table(n).quantity));
3293 END IF;
3294
3295 uom_record.quantity_shipped := x_cascaded_table(n).quantity;
3296 uom_record.unit_of_measure := x_cascaded_table(n).unit_of_measure;
3297 uom_record.item_id := x_cascaded_table(n).item_id;
3298 uom_record.po_line_id := x_cascaded_table(n).po_line_id;
3299 uom_record.to_organization_id := x_cascaded_table(n).to_organization_id;
3300 uom_record.po_header_id := x_cascaded_table(n).po_header_id;
3301 uom_record.primary_unit_of_measure := x_cascaded_table(n).primary_unit_of_measure;
3302 uom_record.error_record.error_status := 'S';
3303 uom_record.error_record.error_message := NULL;
3304
3305 IF (g_asn_debug = 'Y') THEN
3306 asn_debug.put_line('Validating UOM');
3307 END IF;
3308
3309 rcv_transactions_interface_sv1.validate_uom(uom_record);
3310 x_cascaded_table(n).error_status := uom_record.error_record.error_status;
3311 rcv_error_pkg.set_error_message(uom_record.error_record.error_message, x_cascaded_table(n).error_message);
3312 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'UNIT_OF_MEASURE');
3313 item_id_record.item_id := x_cascaded_table(n).item_id;
3314 item_id_record.po_line_id := x_cascaded_table(n).po_line_id;
3315 item_id_record.to_organization_id := x_cascaded_table(n).to_organization_id;
3316 item_id_record.item_description := x_cascaded_table(n).item_description;
3317 item_id_record.item_num := x_cascaded_table(n).item_num;
3318 item_id_record.vendor_item_num := NULL; -- x_cascaded_table(n).vendor_item_num;
3319 /* bug 608353 */
3320 item_id_record.use_mtl_lot := x_cascaded_table(n).use_mtl_lot;
3321 item_id_record.use_mtl_serial := x_cascaded_table(n).use_mtl_serial;
3322 item_id_record.error_record.error_status := 'S';
3323 item_id_record.error_record.error_message := NULL;
3324
3325 IF (g_asn_debug = 'Y') THEN
3326 asn_debug.put_line('Validating Item');
3327 asn_debug.put_line(TO_CHAR(x_cascaded_table(n).item_id));
3328 END IF;
3329
3330 /*
3331 ** If this is a one time item shipment and you've matched up based on a
3332 ** document line num then skip the processing based on setting the validation
3333 ** for the item to be the same as what is set on the line.
3334 */
3335 IF ( x_cascaded_table(n).item_id IS NULL
3336 AND x_cascaded_table(n).po_line_id IS NOT NULL) THEN
3337 item_id_record.error_record.error_status := x_cascaded_table(n).error_status;
3338 item_id_record.error_record.error_message := x_cascaded_table(n).error_message;
3339 ELSE
3340 rcv_transactions_interface_sv1.validate_item(item_id_record, x_cascaded_table(n).auto_transact_code); -- bug 608353
3341 END IF;
3342
3343 x_cascaded_table(n).error_status := item_id_record.error_record.error_status;
3344 rcv_error_pkg.set_error_message(item_id_record.error_record.error_message, x_cascaded_table(n).error_message);
3345 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'ITEM_NUM');
3346 item_id_record.item_description := x_cascaded_table(n).item_description;
3347 item_id_record.error_record.error_status := 'S';
3348 item_id_record.error_record.error_message := NULL;
3349
3350 IF (g_asn_debug = 'Y') THEN
3351 asn_debug.put_line('Validating Item Description ' || x_cascaded_table(n).item_description);
3352 END IF;
3353
3354 rcv_transactions_interface_sv1.validate_item_description(item_id_record);
3355 x_cascaded_table(n).error_status := item_id_record.error_record.error_status;
3356 rcv_error_pkg.set_error_message(item_id_record.error_record.error_message, x_cascaded_table(n).error_message);
3357
3358 IF (g_asn_debug = 'Y') THEN
3359 asn_debug.put_line('Error status after validate item description ' || x_cascaded_table(n).error_status);
3360 END IF;
3361
3362 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'ITEM_DESCRIPTION');
3363
3364 IF (x_cascaded_table(n).substitute_item_id IS NOT NULL) THEN
3365 sub_item_id_record.substitute_item_num := x_cascaded_table(n).substitute_item_num;
3366 sub_item_id_record.substitute_item_id := x_cascaded_table(n).substitute_item_id;
3367 sub_item_id_record.po_line_id := x_cascaded_table(n).po_line_id;
3368 sub_item_id_record.to_organization_id := x_cascaded_table(n).to_organization_id;
3369 sub_item_id_record.vendor_id := x_cascaded_table(n).vendor_id;
3370 sub_item_id_record.error_record.error_status := 'S';
3371 sub_item_id_record.error_record.error_message := NULL;
3372
3373 IF (g_asn_debug = 'Y') THEN
3374 asn_debug.put_line('Validating Substitute Item');
3375 END IF;
3376
3377 /* Added the check on po shipments allow_substitute_receipt flag - Bug 3009663. */
3378 BEGIN
3379 SELECT NVL(pll.allow_substitute_receipts_flag, 'N')
3380 INTO x_allow_substitute_receipts
3381 FROM po_line_locations pll
3382 WHERE pll.line_location_id = x_cascaded_table(n).po_line_location_id;
3383 EXCEPTION
3384 WHEN OTHERS THEN
3385 IF (g_asn_debug = 'Y') THEN
3386 asn_debug.put_line('Hit an exception');
3387 asn_debug.put_line(SQLERRM);
3388 asn_debug.put_line(' While validating substitute item');
3389 END IF;
3390
3391 rcv_error_pkg.set_sql_error_message('validate_shipment_line', x_progress);
3392 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
3393 rcv_error_pkg.log_interface_error('PO_LINE_LOCATION_ID');
3394 END;
3395
3396 IF (g_asn_debug = 'Y') THEN
3397 asn_debug.put_line('Allow substitute receipts flag in PO shipments ' || x_allow_substitute_receipts);
3398 END IF;
3399
3400 IF (x_allow_substitute_receipts = 'Y') THEN
3401 rcv_transactions_interface_sv1.validate_substitute_item(sub_item_id_record);
3402 x_cascaded_table(n).error_status := sub_item_id_record.error_record.error_status;
3403 rcv_error_pkg.set_error_message(sub_item_id_record.error_record.error_message, x_cascaded_table(n).error_message);
3404 ELSE
3405 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
3406 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_NOT_ALLOWED', x_cascaded_table(n).error_message);
3407 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).substitute_item_num);
3408 END IF;
3409
3410 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'SUBSTITUTE_ITEM_NUM');
3411 END IF;
3412
3413 IF (x_cascaded_table(n).item_revision IS NOT NULL) THEN
3414 item_revision_record.item_revision := x_cascaded_table(n).item_revision;
3415 item_revision_record.po_line_id := x_cascaded_table(n).po_line_id;
3416 item_revision_record.to_organization_id := x_cascaded_table(n).to_organization_id;
3417 item_revision_record.item_id := x_cascaded_table(n).item_id;
3418 item_revision_record.error_record.error_status := 'S';
3419 item_revision_record.error_record.error_message := NULL;
3420
3421 IF (g_asn_debug = 'Y') THEN
3422 asn_debug.put_line('Validating Item Revision');
3423 END IF;
3424
3425 rcv_transactions_interface_sv1.validate_item_revision(item_revision_record);
3426 x_cascaded_table(n).error_status := item_revision_record.error_record.error_status;
3427 rcv_error_pkg.set_error_message(item_revision_record.error_record.error_message, x_cascaded_table(n).error_message);
3428 x_cascaded_table(n).item_revision := item_revision_record.item_revision;
3429 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'ITEM_REVISION');
3430 END IF;
3431
3432 IF (x_cascaded_table(n).freight_carrier_code IS NOT NULL) THEN
3433 freight_carrier_record.to_organization_id := x_cascaded_table(n).to_organization_id;
3434 freight_carrier_record.freight_carrier_code := x_cascaded_table(n).freight_carrier_code;
3435 freight_carrier_record.po_header_id := x_cascaded_table(n).po_header_id;
3436 freight_carrier_record.error_record.error_status := 'S';
3437 freight_carrier_record.error_record.error_message := NULL;
3438
3439 IF (g_asn_debug = 'Y') THEN
3440 asn_debug.put_line('Validating Freight Carrier');
3441 END IF;
3442
3443 rcv_transactions_interface_sv1.validate_freight_carrier(freight_carrier_record);
3444 x_cascaded_table(n).error_status := freight_carrier_record.error_record.error_status;
3445 rcv_error_pkg.set_error_message(freight_carrier_record.error_record.error_message, x_cascaded_table(n).error_message);
3446 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'FREIGHT_CARRIER_CODE');
3447 END IF;
3448
3449 /*
3450 ** Validate Destination Type. This value is always required
3451 */
3452 po_lookup_code_record.lookup_code := x_cascaded_table(n).destination_type_code;
3453 po_lookup_code_record.lookup_type := 'RCV DESTINATION TYPE';
3454 po_lookup_code_record.error_record.error_status := 'S';
3455 po_lookup_code_record.error_record.error_message := NULL;
3456
3457 IF (g_asn_debug = 'Y') THEN
3458 asn_debug.put_line('Validating Destination Type Code');
3459 END IF;
3460
3461 rcv_transactions_interface_sv1.validate_po_lookup_code(po_lookup_code_record);
3462 x_cascaded_table(n).error_status := po_lookup_code_record.error_record.error_status;
3463 rcv_error_pkg.set_error_message(po_lookup_code_record.error_record.error_message, x_cascaded_table(n).error_message);
3464 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'DESTINATION_TYPE_CODE');
3465 /*
3466 ** Validate ship_to_location. This value is always required
3467 */
3468 location_record.location_id := x_cascaded_table(n).ship_to_location_id;
3469 location_record.to_organization_id := x_cascaded_table(n).to_organization_id;
3470 location_record.destination_type_code := x_cascaded_table(n).destination_type_code;
3471 location_record.location_type_code := 'SHIP_TO';
3472 location_record.transaction_date := x_cascaded_table(n).transaction_date;
3473 location_record.error_record.error_status := 'S';
3474 location_record.error_record.error_message := NULL;
3475
3476 IF (g_asn_debug = 'Y') THEN
3477 asn_debug.put_line('Validating Ship To Location');
3478 END IF;
3479
3480 rcv_transactions_interface_sv1.validate_location(location_record);
3481 x_cascaded_table(n).error_status := location_record.error_record.error_status;
3482 rcv_error_pkg.set_error_message(location_record.error_record.error_message, x_cascaded_table(n).error_message);
3483 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'SHIP_TO_LOCATION_ID');
3484 /*
3485 ** Validate deliver to person. This value is always optional
3486 */
3487 employee_record.employee_id := x_cascaded_table(n).deliver_to_person_id;
3488 employee_record.to_organization_id := x_cascaded_table(n).to_organization_id;
3489 employee_record.destination_type_code := x_cascaded_table(n).destination_type_code;
3490 employee_record.transaction_date := x_cascaded_table(n).transaction_date;
3491 employee_record.error_record.error_status := 'S';
3492 employee_record.error_record.error_message := NULL;
3493
3494 IF (g_asn_debug = 'Y') THEN
3495 asn_debug.put_line('Validating Deliver to Person');
3496 END IF;
3497
3498 rcv_transactions_interface_sv1.validate_employee(employee_record);
3499 x_cascaded_table(n).error_status := employee_record.error_record.error_status;
3500 rcv_error_pkg.set_error_message(employee_record.error_record.error_message, x_cascaded_table(n).error_message);
3501 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'DELIVER_TO_PERSON_ID');
3502
3503 /*
3504 ** Validate deliver to person. This value is always optional
3505 */
3506 /* removing validation of deliver to person 2 - the code is exactly the same resulting
3507 ** in double error messages - whatever validation this was meant to be
3508 ** it is currently incorrect
3509 */
3510
3511 /*
3512 ** Validate routing record bug 639750
3513 */
3514 IF ( x_cascaded_table(n).transaction_type = 'DELIVER'
3515 OR NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') = 'DELIVER') THEN
3516 IF (g_asn_debug = 'Y') THEN
3517 asn_debug.put_line('Validating routing_header_id');
3518 END IF;
3519
3520 /* 1 is Standard Receipt, 2 is Inspection Required */
3521 IF ( (x_cascaded_table(n).routing_header_id) IN(1, 2)
3522 AND NVL(rcv_setup_s.get_override_routing, 'N') = 'N') THEN
3523 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
3524 rcv_error_pkg.set_error_message('RCV_ASN_DELIVER_ROUTING_FAILED', x_cascaded_table(n).error_message);
3525 rcv_error_pkg.log_interface_error('ROUTING_HEADER_ID');
3526 END IF;
3527 END IF;
3528
3529 /*
3530 ** Validate deliver_to_location. If this is an expense or shop floor
3531 ** destination then the value is required
3532 */
3533 IF ( x_cascaded_table(n).transaction_type = 'DELIVER'
3534 OR NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') = 'DELIVER') THEN
3535 location_record.location_id := x_cascaded_table(n).deliver_to_location_id;
3536 location_record.to_organization_id := x_cascaded_table(n).to_organization_id;
3537 location_record.destination_type_code := x_cascaded_table(n).destination_type_code;
3538 location_record.location_type_code := 'DELIVER_TO';
3539 location_record.transaction_date := x_cascaded_table(n).transaction_date;
3540 location_record.error_record.error_status := 'S';
3541 location_record.error_record.error_message := NULL;
3542
3543 IF (g_asn_debug = 'Y') THEN
3544 asn_debug.put_line('Validating Deliver To Location');
3545 END IF;
3546
3547 rcv_transactions_interface_sv1.validate_location(location_record);
3548 x_cascaded_table(n).error_status := location_record.error_record.error_status;
3549 rcv_error_pkg.set_error_message(location_record.error_record.error_message, x_cascaded_table(n).error_message);
3550 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'DELIVER_TO_LOCATION_ID');
3551 END IF;
3552
3553 /*
3554 ** Validate subinventory if inventory destination or if not inventory
3555 ** destintion make sure to null out the subinventory
3556 */
3557 IF ( x_cascaded_table(n).transaction_type = 'DELIVER'
3558 OR NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') = 'DELIVER') THEN
3559 IF (x_cascaded_table(n).destination_type_code = 'INVENTORY') THEN
3560 subinventory_record.subinventory := x_cascaded_table(n).subinventory;
3561 subinventory_record.to_organization_id := x_cascaded_table(n).to_organization_id;
3562 subinventory_record.destination_type_code := x_cascaded_table(n).destination_type_code;
3563 subinventory_record.item_id := x_cascaded_table(n).item_id;
3564 subinventory_record.transaction_date := x_cascaded_table(n).transaction_date;
3565 subinventory_record.error_record.error_status := 'S';
3566 subinventory_record.error_record.error_message := NULL;
3567
3568 IF (g_asn_debug = 'Y') THEN
3569 asn_debug.put_line('Validating Subinventory');
3570 END IF;
3571
3572 rcv_transactions_interface_sv1.validate_subinventory(subinventory_record);
3573 x_cascaded_table(n).error_status := subinventory_record.error_record.error_status;
3574 rcv_error_pkg.set_error_message(subinventory_record.error_record.error_message, x_cascaded_table(n).error_message);
3575 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'SUBINVENTORY');
3576 ELSE
3577 x_cascaded_table(n).subinventory := NULL;
3578 END IF;
3579 END IF;
3580
3581 /*
3582 ** Validate locator if inventory destination or if not inventory
3583 ** destintion make sure to null out the locator_id
3584 */
3585 IF (g_asn_debug = 'Y') THEN
3586 asn_debug.put_line('Trx Type = ' || x_cascaded_table(n).transaction_type || 'Auto Trx Code = ' || x_cascaded_table(n).auto_transact_code);
3587 END IF;
3588
3589 IF ( x_cascaded_table(n).transaction_type = 'DELIVER'
3590 OR NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') = 'DELIVER') THEN
3591 IF (g_asn_debug = 'Y') THEN
3592 asn_debug.put_line('Valid Loc - Destination Type Code = ' || x_cascaded_table(n).destination_type_code);
3593 END IF;
3594
3595 IF (x_cascaded_table(n).destination_type_code = 'INVENTORY') THEN
3596 locator_record.locator_id := x_cascaded_table(n).locator_id;
3597 locator_record.subinventory := x_cascaded_table(n).subinventory;
3598 locator_record.to_organization_id := x_cascaded_table(n).to_organization_id;
3599 locator_record.destination_type_code := x_cascaded_table(n).destination_type_code;
3600 locator_record.item_id := x_cascaded_table(n).item_id;
3601 locator_record.transaction_date := x_cascaded_table(n).transaction_date;
3602
3603 IF (g_asn_debug = 'Y') THEN
3604 asn_debug.put_line('Locator = ' || TO_CHAR(locator_record.locator_id));
3605 asn_debug.put_line('Subinventory = ' || locator_record.subinventory);
3606 asn_debug.put_line('To Org = ' || locator_record.to_organization_id);
3607 asn_debug.put_line('Dest Type = ' || locator_record.destination_type_code);
3608 asn_debug.put_line('Item Id = ' || locator_record.item_id);
3609 END IF;
3610
3611 locator_record.error_record.error_status := 'S';
3612 locator_record.error_record.error_message := NULL;
3613
3614 IF (g_asn_debug = 'Y') THEN
3615 asn_debug.put_line('Validating Locator');
3616 END IF;
3617
3618 rcv_transactions_interface_sv1.validate_locator(locator_record);
3619 x_cascaded_table(n).error_status := locator_record.error_record.error_status;
3620 rcv_error_pkg.set_error_message(locator_record.error_record.error_message, x_cascaded_table(n).error_message);
3621
3622 IF (g_asn_debug = 'Y') THEN
3623 asn_debug.put_line('Loc Error Status = ' || locator_record.error_record.error_status);
3624 asn_debug.put_line('Loc Error Msg = ' || locator_record.error_record.error_message);
3625 END IF;
3626
3627 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'LOCATOR');
3628 ELSE
3629 x_cascaded_table(n).locator_id := NULL;
3630 x_cascaded_table(n).LOCATOR := NULL;
3631 END IF;
3632 END IF;
3633
3634 IF (x_cascaded_table(n).tax_name IS NOT NULL) THEN
3635 IF (x_asn_type = 'ASBN') THEN
3636 tax_name_record.tax_name := x_cascaded_table(n).tax_name;
3637 tax_name_record.error_record.error_status := 'S';
3638 tax_name_record.error_record.error_message := NULL;
3639
3640 IF (g_asn_debug = 'Y') THEN
3641 asn_debug.put_line('Validating Tax Code');
3642 END IF;
3643
3644 rcv_transactions_interface_sv1.validate_tax_code(tax_name_record);
3645 x_cascaded_table(n).error_status := tax_name_record.error_record.error_status;
3646 rcv_error_pkg.set_error_message(tax_name_record.error_record.error_message, x_cascaded_table(n).error_message);
3647 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'TAX_NAME');
3648 END IF;
3649 END IF;
3650
3651 --FRKHAN 12/18/98 validate country of origin code
3652 IF (x_cascaded_table(n).country_of_origin_code IS NOT NULL) THEN
3653 country_of_origin_record.country_of_origin_code := x_cascaded_table(n).country_of_origin_code;
3654 country_of_origin_record.error_record.error_status := 'S';
3655 country_of_origin_record.error_record.error_message := NULL;
3656
3657 IF (g_asn_debug = 'Y') THEN
3658 asn_debug.put_line('Validating Country of Origin Code');
3659 END IF;
3660
3661 rcv_transactions_interface_sv1.validate_country_of_origin(country_of_origin_record);
3662 x_cascaded_table(n).error_status := country_of_origin_record.error_record.error_status;
3663 rcv_error_pkg.set_error_message(country_of_origin_record.error_record.error_message, x_cascaded_table(n).error_message);
3664 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'COUNTRY_OF_ORIGIN_CODE');
3665 END IF;
3666
3667 asl_record.po_header_id := x_cascaded_table(n).po_header_id;
3668 asl_record.vendor_id := x_cascaded_table(n).vendor_id;
3669 asl_record.vendor_site_id := x_cascaded_table(n).vendor_site_id;
3670 asl_record.item_id := x_cascaded_table(n).item_id;
3671 asl_record.to_organization_id := x_cascaded_table(n).to_organization_id;
3672 asl_record.error_record.error_status := 'S';
3673 asl_record.error_record.error_message := NULL;
3674
3675 IF (g_asn_debug = 'Y') THEN
3676 asn_debug.put_line('Validating ASL');
3677 END IF;
3678
3679 rcv_transactions_interface_sv1.validate_asl(asl_record);
3680 x_cascaded_table(n).error_status := asl_record.error_record.error_status;
3681 rcv_error_pkg.set_error_message(asl_record.error_record.error_message, x_cascaded_table(n).error_message);
3682 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'SUPPLY_AGREEMENT_FLAG');
3683
3684 IF NVL(x_cascaded_table(n).vendor_cum_shipped_qty, 0) <> 0 THEN
3685 cum_quantity_record.to_organization_id := x_cascaded_table(n).to_organization_id;
3686 cum_quantity_record.po_header_id := x_cascaded_table(n).po_header_id;
3687 cum_quantity_record.vendor_cum_shipped_qty := x_cascaded_table(n).vendor_cum_shipped_qty;
3688 cum_quantity_record.item_id := x_cascaded_table(n).item_id;
3689 cum_quantity_record.vendor_id := x_cascaded_table(n).vendor_id;
3690 cum_quantity_record.vendor_site_id := x_cascaded_table(n).vendor_site_id;
3691 cum_quantity_record.primary_unit_of_measure := x_cascaded_table(n).primary_unit_of_measure;
3692 cum_quantity_record.quantity_shipped := x_cascaded_table(n).quantity;
3693 cum_quantity_record.unit_of_measure := x_cascaded_table(n).unit_of_measure;
3694 cum_quantity_record.transaction_date := x_cascaded_table(n).transaction_date;
3695 cum_quantity_record.error_record.error_status := 'S';
3696 cum_quantity_record.error_record.error_message := NULL;
3697
3698 IF (g_asn_debug = 'Y') THEN
3699 asn_debug.put_line('Validating Cum Qty Shipped');
3700 END IF;
3701
3702 rcv_transactions_interface_sv1.validate_cum_quantity_shipped(cum_quantity_record);
3703 x_cascaded_table(n).error_status := cum_quantity_record.error_record.error_status;
3704 rcv_error_pkg.set_error_message(cum_quantity_record.error_record.error_message, x_cascaded_table(n).error_message);
3705
3706 /* WDK - hack, errors are downgraded to warning */
3707 IF (x_cascaded_table(n).error_status = rcv_error_pkg.g_ret_sts_error) THEN
3708 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_warning;
3709 END IF;
3710
3711 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'VENDOR_CUM_SHIPPED_QTY');
3712 END IF; -- vendor_cum_shipped_qty <> 0
3713
3714 IF (g_asn_debug = 'Y') THEN
3715 asn_debug.put_line('Validating ref integrity');
3716 END IF;
3717
3718 ref_integrity_record.to_organization_id := x_cascaded_table(n).to_organization_id;
3719 ref_integrity_record.po_line_location_id := x_cascaded_table(n).po_line_location_id;
3720 ref_integrity_record.po_header_id := x_cascaded_table(n).po_header_id;
3721 ref_integrity_record.po_line_id := x_cascaded_table(n).po_line_id;
3722 ref_integrity_record.vendor_id := x_cascaded_table(n).vendor_id;
3723 ref_integrity_record.vendor_site_id := x_cascaded_table(n).vendor_site_id;
3724 ref_integrity_record.vendor_item_num := x_cascaded_table(n).vendor_item_num;
3725 ref_integrity_record.po_revision_num := x_cascaded_table(n).po_revision_num;
3726 ref_integrity_record.error_record.error_status := 'S';
3727 ref_integrity_record.error_record.error_message := NULL;
3728
3729 IF (g_asn_debug = 'Y') THEN
3730 asn_debug.put_line('Validating Ref Integ');
3731 END IF;
3732
3733 rcv_transactions_interface_sv1.validate_ref_integ(ref_integrity_record, x_header_record);
3734 x_cascaded_table(n).error_status := ref_integrity_record.error_record.error_status;
3735 rcv_error_pkg.set_error_message(ref_integrity_record.error_record.error_message, x_cascaded_table(n).error_message);
3736 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'DOCUMENT_NUM');
3737
3738 -- If substitute item has been specified then we need to switch the item_id with the
3739 -- substitute item. Also make sure that we can receive the substitute item in the
3740 -- ASN UOM. Convert the primary_quantity in item.primary uom to the substitute_item.primary_uom
3741 -- If this fails then the transaction is in error
3742
3743
3744 IF x_cascaded_table(n).substitute_item_id IS NOT NULL THEN
3745 exchange_sub_item(x_cascaded_table, n);
3746 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'SUBSTITUTE_ITEM_ID');
3747
3748 IF x_cascaded_table(n).error_status NOT IN('S', 'W') THEN
3749 IF (g_asn_debug = 'Y') THEN
3750 asn_debug.put_line('Some problems in exchange');
3751 END IF;
3752 END IF;
3753 END IF;
3754
3755 /* <Consigned Inventory Pre-Processor FPI START> */
3756
3757 /* Reject ASBN transaction if it's a shipment against Consigned PO */
3758 IF (x_asn_type = 'ASBN')
3759 AND (x_cascaded_table(n).po_line_location_id IS NOT NULL) THEN
3760 l_consigned_po_rec.po_line_location_id := x_cascaded_table(n).po_line_location_id;
3761 l_consigned_po_rec.error_record.error_status := 'S';
3762 l_consigned_po_rec.error_record.error_message := NULL;
3763
3764 IF (g_asn_debug = 'Y') THEN
3765 asn_debug.put_line('Validating ASBN for Consigned PO');
3766 END IF;
3767
3768 rcv_transactions_interface_sv1.validate_consigned_po(l_consigned_po_rec);
3769
3770 IF (g_asn_debug = 'Y') THEN
3771 asn_debug.put_line('After Validating ASBN for Consigned PO');
3772 END IF;
3773
3774 x_cascaded_table(n).error_status := l_consigned_po_rec.error_record.error_status;
3775 rcv_error_pkg.set_error_message(l_consigned_po_rec.error_record.error_message, x_cascaded_table(n).error_message);
3776
3777 IF (g_asn_debug = 'Y') THEN
3778 asn_debug.put_line('Error status ' || l_consigned_po_rec.error_record.error_status);
3779 asn_debug.put_line('Error name: ' || l_consigned_po_rec.error_record.error_message);
3780 END IF;
3781
3782 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'PO_LINE_LOCATION_ID');
3783 END IF; -- IF (x_cascaded_table(n).error_status in ('S','W')) AND (X_asn_type = 'ASBN')
3784
3785 /*
3786 ** Reject ASN, ASBN or Receipt transactions against Consumption PO
3787 */
3788 IF (x_cascaded_table(n).po_header_id IS NOT NULL)
3789 AND (x_cascaded_table(n).po_release_id IS NULL) THEN
3790 l_consumption_po_rec.po_header_id := x_cascaded_table(n).po_header_id;
3791 l_consumption_po_rec.error_record.error_status := 'S';
3792 l_consumption_po_rec.error_record.error_message := NULL;
3793
3794 IF (g_asn_debug = 'Y') THEN
3795 asn_debug.put_line('Validating Consumption PO');
3796 END IF;
3797
3798 rcv_transactions_interface_sv1.validate_consumption_po(l_consumption_po_rec);
3799
3800 IF (g_asn_debug = 'Y') THEN
3801 asn_debug.put_line('After Validating Consumption PO');
3802 END IF;
3803
3804 x_cascaded_table(n).error_status := l_consumption_po_rec.error_record.error_status;
3805 rcv_error_pkg.set_error_message(l_consumption_po_rec.error_record.error_message, x_cascaded_table(n).error_message);
3806
3807 IF (g_asn_debug = 'Y') THEN
3808 asn_debug.put_line('Error status ' || l_consumption_po_rec.error_record.error_status);
3809 asn_debug.put_line('Error name: ' || l_consumption_po_rec.error_record.error_message);
3810 END IF;
3811
3812 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'PO_HEADER_ID');
3813 END IF; -- IF (x_cascaded_table(n).error_status in ('S','W'))
3814
3815 /*
3816 ** Reject ASN, ASBN or Receipt transactions against Consumption Release
3817 */
3818 IF (x_cascaded_table(n).po_release_id IS NOT NULL) THEN
3819 l_consumption_release_rec.po_release_id := x_cascaded_table(n).po_release_id;
3820 l_consumption_release_rec.error_record.error_status := 'S';
3821 l_consumption_release_rec.error_record.error_message := NULL;
3822
3823 IF (g_asn_debug = 'Y') THEN
3824 asn_debug.put_line('Validating Consumption Release');
3825 END IF;
3826
3827 rcv_transactions_interface_sv1.validate_consumption_release(l_consumption_release_rec);
3828
3829 IF (g_asn_debug = 'Y') THEN
3830 asn_debug.put_line('After Validating Consumption Release');
3831 END IF;
3832
3833 x_cascaded_table(n).error_status := l_consumption_release_rec.error_record.error_status;
3834 rcv_error_pkg.set_error_message(l_consumption_release_rec.error_record.error_message, x_cascaded_table(n).error_message);
3835
3836 IF (g_asn_debug = 'Y') THEN
3837 asn_debug.put_line('Error status ' || l_consumption_release_rec.error_record.error_status);
3838 asn_debug.put_line('Error name: ' || l_consumption_release_rec.error_record.error_message);
3839 END IF;
3840
3841 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'PO_RELEASE_ID');
3842 END IF; -- IF (x_cascaded_table(n).error_status in ('S','W'))
3843 /* <Consigned Inventory Pre-Processor FPI END> */
3844 EXCEPTION
3845 WHEN rcv_error_pkg.e_fatal_error THEN
3846 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
3847 WHEN OTHERS THEN
3848 IF (g_asn_debug = 'Y') THEN
3849 asn_debug.put_line('I have hit an exception');
3850 asn_debug.put_line(SQLERRM);
3851 asn_debug.put_line('Exit validate_shipment_line');
3852 END IF;
3853
3854 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_unexp_error;
3855 rcv_error_pkg.set_sql_error_message('validate_shipment_line', x_progress);
3856 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
3857 rcv_error_pkg.log_interface_error('TRANSACTIONS_INTERFACE_ID');
3858 END validate_shipment_line;
3859
3860 /*===========================================================================
3861
3862 PROCEDURE NAME: get_location_id()
3863
3864 ===========================================================================*/
3865 PROCEDURE get_location_id(
3866 x_location_id_record IN OUT NOCOPY rcv_shipment_object_sv.location_id_record_type
3867 ) IS
3868 BEGIN
3869 SELECT MAX(location_id)
3870 INTO x_location_id_record.location_id
3871 FROM hr_locations
3872 WHERE location_code = x_location_id_record.location_code;
3873
3874 IF (x_location_id_record.location_id IS NULL) THEN
3875 x_location_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
3876 rcv_error_pkg.set_error_message('RCV_ASN_LOCATION_ID', x_location_id_record.error_record.error_message);
3877 END IF;
3878 EXCEPTION
3879 WHEN OTHERS THEN
3880 x_location_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
3881 rcv_error_pkg.set_sql_error_message('get_location_id', '000');
3882 x_location_id_record.error_record.error_message := rcv_error_pkg.get_last_message;
3883 END get_location_id;
3884
3885 /*===========================================================================
3886
3887 PROCEDURE NAME: get_locator_id()
3888
3889 ===========================================================================*/
3890 PROCEDURE get_locator_id(
3891 x_locator_id_record IN OUT NOCOPY rcv_shipment_line_sv.locator_id_record_type
3892 ) IS
3893 BEGIN
3894 IF (g_asn_debug = 'Y') THEN
3895 asn_debug.put_line('inside get_locator_id');
3896 END IF;
3897
3898 /* Bug 3017707 - As locators with the same name can exist in two different organizations, added a filter on organization_id */
3899 SELECT NVL(MAX(ml.inventory_location_id), -999)
3900 INTO x_locator_id_record.locator_id
3901 FROM mtl_item_locations_kfv ml
3902 WHERE ml.concatenated_segments = x_locator_id_record.LOCATOR
3903 AND ( ml.disable_date > SYSDATE
3904 OR ml.disable_date IS NULL)
3905 AND NVL(ml.subinventory_code, 'z') = NVL(x_locator_id_record.subinventory, 'z')
3906 AND x_locator_id_record.to_organization_id = ml.organization_id;
3907
3908 IF (x_locator_id_record.locator_id IS NULL) THEN
3909 x_locator_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
3910 rcv_error_pkg.set_error_message('RCV_ALL_INVALID_LOCATOR', x_locator_id_record.error_record.error_message);
3911 END IF;
3912 EXCEPTION
3913 WHEN OTHERS THEN
3914 x_locator_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
3915 rcv_error_pkg.set_sql_error_message('get_locator_id', '000');
3916 x_locator_id_record.error_record.error_message := rcv_error_pkg.get_last_message;
3917 END get_locator_id;
3918
3919 /*===========================================================================
3920
3921 PROCEDURE NAME: get_routing_header_id()
3922
3923 ===========================================================================*/
3924 PROCEDURE get_routing_header_id(
3925 x_routing_header_id_record IN OUT NOCOPY rcv_shipment_line_sv.routing_header_id_rec_type
3926 ) IS
3927 BEGIN
3928 SELECT MAX(routing_header_id)
3929 INTO x_routing_header_id_record.routing_header_id
3930 FROM rcv_routing_headers
3931 WHERE routing_name = x_routing_header_id_record.routing_code;
3932
3933 IF (x_routing_header_id_record.routing_header_id IS NULL) THEN
3934 x_routing_header_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
3935 rcv_error_pkg.set_error_message('RCV_ASN_ROUTING_HEADER_ID', x_routing_header_id_record.error_record.error_message);
3936 END IF;
3937 EXCEPTION
3938 WHEN OTHERS THEN
3939 x_routing_header_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
3940 rcv_error_pkg.set_sql_error_message('get_routing_header_id', '000');
3941 x_routing_header_id_record.error_record.error_message := rcv_error_pkg.get_last_message;
3942 END get_routing_header_id;
3943
3944 /*===========================================================================
3945
3946 PROCEDURE NAME: get_routing_step_id()
3947
3948 ===========================================================================*/
3949 PROCEDURE get_routing_step_id(
3950 x_routing_step_id_record IN OUT NOCOPY rcv_shipment_line_sv.routing_step_id_rec_type
3951 ) IS
3952 BEGIN
3953 SELECT MAX(routing_step_id)
3954 INTO x_routing_step_id_record.routing_step_id
3955 FROM rcv_routing_steps
3956 WHERE step_name = x_routing_step_id_record.routing_step;
3957
3958 IF (x_routing_step_id_record.routing_step_id IS NULL) THEN
3959 x_routing_step_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
3960 rcv_error_pkg.set_error_message('RCV_ASN_ROUTING_STEP_ID', x_routing_step_id_record.error_record.error_message);
3961 END IF;
3962 EXCEPTION
3963 WHEN OTHERS THEN
3964 x_routing_step_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
3965 rcv_error_pkg.set_sql_error_message('get_routing_step_id', '000');
3966 x_routing_step_id_record.error_record.error_message := rcv_error_pkg.get_last_message;
3967 END get_routing_step_id;
3968
3969 /*===========================================================================
3970
3971 PROCEDURE NAME: get_reason_id()
3972
3973 ===========================================================================*/
3974 PROCEDURE get_reason_id(
3975 x_reason_id_record IN OUT NOCOPY rcv_shipment_line_sv.reason_id_record_type
3976 ) IS
3977 BEGIN
3978 SELECT MAX(reason_id)
3979 INTO x_reason_id_record.reason_id
3980 FROM mtl_transaction_reasons
3981 WHERE reason_name = x_reason_id_record.reason_name;
3982
3983 IF (x_reason_id_record.reason_id IS NULL) THEN
3984 x_reason_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
3985 rcv_error_pkg.set_error_message('RCV_ASN_REASON_ID', x_reason_id_record.error_record.error_message);
3986 END IF;
3987 EXCEPTION
3988 WHEN OTHERS THEN
3989 x_reason_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
3990 rcv_error_pkg.set_sql_error_message('get_reason_id', '000');
3991 x_reason_id_record.error_record.error_message := rcv_error_pkg.get_last_message;
3992 END get_reason_id;
3993
3994 /*==========================================================================
3995
3996 PROCEDURE NAME: default_item_revision()
3997
3998 ============================================================================*/
3999 PROCEDURE default_item_revision(
4000 x_item_revision_record IN OUT NOCOPY rcv_shipment_line_sv.item_id_record_type
4001 ) IS
4002 x_revision_control_flag VARCHAR2(1);
4003 x_number_of_inv_dest NUMBER;
4004 x_item_rev_exists BOOLEAN;
4005 BEGIN
4006 /* Check whether item is under revision control */
4007 SELECT DECODE(msi.revision_qty_control_code,
4008 1, 'N',
4009 2, 'Y',
4010 'N'
4011 )
4012 INTO x_revision_control_flag
4013 FROM mtl_system_items msi
4014 WHERE inventory_item_id = x_item_revision_record.item_id
4015 AND organization_id = x_item_revision_record.to_organization_id;
4016
4017 /* If item is under revision control
4018
4019 if revision is null then try to pick up item_revision from po_lines
4020
4021 if revision is still null and
4022 there are any destination_type=INVENTORY then
4023
4024 try to pick up latest revision from mtl_item_revisions
4025
4026 end if
4027 else
4028 item should not have any revisions which we will validate in the validation phase */
4029 IF x_revision_control_flag = 'Y' THEN
4030 IF (g_asn_debug = 'Y') THEN
4031 asn_debug.put_line('Item is under revision control');
4032 END IF;
4033
4034 IF x_item_revision_record.item_revision IS NULL THEN -- pick up revision from source document
4035 IF (g_asn_debug = 'Y') THEN
4036 asn_debug.put_line('Picking up from source document');
4037 END IF;
4038
4039 SELECT item_revision
4040 INTO x_item_revision_record.item_revision
4041 FROM po_lines
4042 WHERE po_lines.po_line_id = x_item_revision_record.po_line_id;
4043 END IF;
4044
4045 IF x_item_revision_record.item_revision IS NULL THEN -- see whether any destination_type = 'INVENTORY'
4046 SELECT COUNT(*)
4047 INTO x_number_of_inv_dest
4048 FROM po_distributions pd
4049 WHERE pd.line_location_id = x_item_revision_record.po_line_location_id
4050 AND pd.destination_type_code = 'INVENTORY';
4051 END IF;
4052
4053 IF x_item_revision_record.item_revision IS NULL
4054 AND x_number_of_inv_dest > 0 THEN -- still null and destination_type = INVENTORY
4055 -- default latest implementation
4056 IF (g_asn_debug = 'Y') THEN
4057 asn_debug.put_line('Picking up latest implementation since source doc is null');
4058 END IF;
4059
4060 po_items_sv2.get_latest_item_rev(x_item_revision_record.item_id,
4061 x_item_revision_record.to_organization_id,
4062 x_item_revision_record.item_revision,
4063 x_item_rev_exists
4064 );
4065 END IF;
4066 END IF;
4067 EXCEPTION
4068 WHEN OTHERS THEN
4069 IF (g_asn_debug = 'Y') THEN
4070 asn_debug.put_line('In procedure default item_revision');
4071 END IF;
4072 END default_item_revision;
4073
4074 /*===========================================================================
4075
4076 PROCEDURE NAME: check_date_tolerance()
4077
4078 ===========================================================================*/
4079 PROCEDURE check_date_tolerance(
4080 expected_receipt_date IN DATE,
4081 promised_date IN DATE,
4082 days_early_receipt_allowed IN NUMBER,
4083 days_late_receipt_allowed IN NUMBER,
4084 receipt_days_exception_code IN OUT NOCOPY VARCHAR2
4085 ) IS
4086 x_sysdate DATE := SYSDATE;
4087 high_range_date DATE;
4088 low_range_date DATE;
4089 BEGIN
4090 IF (g_asn_debug = 'Y') THEN
4091 asn_debug.put_line('Check date tolerance');
4092 END IF;
4093
4094 IF (expected_receipt_date IS NOT NULL) THEN
4095 IF (promised_date IS NOT NULL) THEN
4096 low_range_date := promised_date - NVL(days_early_receipt_allowed, 0);
4097 high_range_date := promised_date + NVL(days_late_receipt_allowed, 0);
4098 ELSE
4099 low_range_date := x_sysdate - NVL(days_early_receipt_allowed, 0);
4100 high_range_date := x_sysdate + NVL(days_late_receipt_allowed, 0);
4101 END IF;
4102
4103 IF ( expected_receipt_date >= low_range_date
4104 AND expected_receipt_date <= high_range_date) THEN
4105 receipt_days_exception_code := 'NONE';
4106 ELSE
4107 IF receipt_days_exception_code = 'REJECT' THEN
4108 receipt_days_exception_code := 'REJECT';
4109 ELSIF receipt_days_exception_code = 'WARNING' THEN
4110 receipt_days_exception_code := 'NONE';
4111 END IF;
4112 END IF;
4113 ELSE
4114 receipt_days_exception_code := 'NONE';
4115 END IF;
4116
4117 IF receipt_days_exception_code IS NULL THEN
4118 IF (g_asn_debug = 'Y') THEN
4119 asn_debug.put_line('In null days exception code');
4120 END IF;
4121
4122 receipt_days_exception_code := 'NONE';
4123 END IF;
4124 END check_date_tolerance;
4125
4126 FUNCTION convert_into_correct_qty(
4127 source_qty IN NUMBER,
4128 source_uom IN VARCHAR2,
4129 item_id IN NUMBER,
4130 dest_uom IN VARCHAR2
4131 )
4132 RETURN NUMBER IS
4133 correct_qty NUMBER;
4134 BEGIN
4135 IF source_uom <> dest_uom THEN
4136
4137 /*
4138 ** Bug 4898703 -
4139 ** Reverted the fix made in Bug 4145660. Modified code in
4140 ** RCVPRETB.pls to handle the rounding issues rather than
4141 ** modifying this procedure which gets called from too many
4142 ** other places.
4143 */
4144 po_uom_s.uom_convert(source_qty,
4145 source_uom,
4146 item_id,
4147 dest_uom,
4148 correct_qty
4149 );
4150 ELSE
4151 correct_qty := source_qty;
4152 END IF;
4153
4154 RETURN(correct_qty);
4155 EXCEPTION
4156 WHEN OTHERS THEN
4157 IF (g_asn_debug = 'Y') THEN
4158 asn_debug.put_line('Could not convert between UOMs');
4159 asn_debug.put_line('Will return 0');
4160 END IF;
4161
4162 correct_qty := 0;
4163 RETURN(correct_qty);
4164 END;
4165
4166 PROCEDURE check_shipto_enforcement(
4167 po_ship_to_location_id IN NUMBER,
4168 asn_ship_to_location_id IN NUMBER,
4169 enforce_ship_to_location_code IN OUT NOCOPY VARCHAR2
4170 ) IS
4171 BEGIN
4172 IF enforce_ship_to_location_code <> 'NONE' THEN
4173 IF enforce_ship_to_location_code = 'REJECT' THEN
4174 IF NVL(asn_ship_to_location_id, po_ship_to_location_id) = po_ship_to_location_id THEN
4175 enforce_ship_to_location_code := 'NONE';
4176 ELSE
4177 enforce_ship_to_location_code := 'REJECT';
4178 END IF;
4179 END IF;
4180
4181 IF enforce_ship_to_location_code = 'WARNING' THEN
4182 IF NVL(asn_ship_to_location_id, po_ship_to_location_id) = po_ship_to_location_id THEN
4183 enforce_ship_to_location_code := 'NONE';
4184 ELSE
4185 enforce_ship_to_location_code := 'WARNING';
4186 END IF;
4187 END IF;
4188 END IF;
4189 END check_shipto_enforcement;
4190
4191 PROCEDURE exchange_sub_item(
4192 v_cascaded_table IN OUT NOCOPY rcv_shipment_object_sv.cascaded_trans_tab_type,
4193 n IN BINARY_INTEGER
4194 ) IS
4195 x_item_id NUMBER;
4196 x_primary_uom mtl_system_items.primary_unit_of_measure%TYPE := NULL;
4197 x_uom_class VARCHAR2(10);
4198 x_uom_count NUMBER(10);
4199 prim_uom_qty NUMBER;
4200 x_error_status VARCHAR2(1);
4201 BEGIN
4202 x_error_status := rcv_error_pkg.g_ret_sts_error;
4203
4204 SELECT COUNT(*)
4205 INTO x_uom_count
4206 FROM mtl_item_uoms_view
4207 WHERE organization_id = v_cascaded_table(n).to_organization_id
4208 AND inventory_item_id(+) = v_cascaded_table(n).substitute_item_id
4209 AND unit_of_measure = v_cascaded_table(n).unit_of_measure;
4210
4211 IF x_uom_count = 0 THEN
4212 IF (g_asn_debug = 'Y') THEN
4213 asn_debug.put_line('The substitute item cannot be received in ASN uom' || v_cascaded_table(n).unit_of_measure);
4214 END IF;
4215
4216 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_NOT_ALLOWED');
4217 RAISE e_validation_error;
4218 END IF;
4219
4220 SELECT MAX(primary_unit_of_measure)
4221 INTO x_primary_uom
4222 FROM mtl_system_items
4223 WHERE mtl_system_items.inventory_item_id = v_cascaded_table(n).item_id
4224 AND mtl_system_items.organization_id = v_cascaded_table(n).to_organization_id;
4225
4226 IF x_primary_uom IS NULL THEN
4227 IF (g_asn_debug = 'Y') THEN
4228 asn_debug.put_line('No Primary UOM for substitute item');
4229 END IF;
4230
4231 rcv_error_pkg.set_error_message('RCV_UOM_NO_CONV_PRIMARY');
4232 RAISE e_validation_error;
4233 END IF;
4234
4235 IF (g_asn_debug = 'Y') THEN
4236 asn_debug.put_line('Primary UOM for substitute item is ' || x_primary_uom);
4237 END IF;
4238
4239 IF x_primary_uom <> v_cascaded_table(n).primary_unit_of_measure THEN
4240 prim_uom_qty := convert_into_correct_qty(v_cascaded_table(n).quantity,
4241 v_cascaded_table(n).unit_of_measure,
4242 v_cascaded_table(n).item_id,
4243 x_primary_uom
4244 );
4245
4246 IF prim_uom_qty = 0 THEN
4247 IF (g_asn_debug = 'Y') THEN
4248 asn_debug.put_line('Not possible to convert between asn and primary UOM');
4249 END IF;
4250
4251 rcv_error_pkg.set_error_message('RCV_UOM_NO_CONV_PRIMARY');
4252 RAISE e_validation_error;
4253 END IF;
4254
4255 v_cascaded_table(n).primary_unit_of_measure := x_primary_uom;
4256 v_cascaded_table(n).primary_quantity := prim_uom_qty;
4257 END IF;
4258
4259 x_item_id := v_cascaded_table(n).item_id;
4260 v_cascaded_table(n).item_id := v_cascaded_table(n).substitute_item_id;
4261 v_cascaded_table(n).substitute_item_id := x_item_id; -- Just for debugging purposes.
4262
4263 -- Check other fields that need to be reassigned/nulled out possibly
4264 EXCEPTION
4265 WHEN e_validation_error THEN
4266 v_cascaded_table(n).error_status := x_error_status;
4267 v_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
4268
4269 IF v_cascaded_table(n).error_message = 'RCV_ITEM_SUB_NOT_ALLOWED' THEN
4270 rcv_error_pkg.set_token('ITEM', v_cascaded_table(n).substitute_item_id);
4271 ELSIF v_cascaded_table(n).error_message = 'RCV_UOM_NO_CONV_PRIMARY' THEN
4272 rcv_error_pkg.set_token('SHIPMENT_UNIT', v_cascaded_table(n).primary_unit_of_measure);
4273 rcv_error_pkg.set_token('PRIMARY_UNIT', x_primary_uom);
4274 END IF;
4275 END exchange_sub_item;
4276
4277 /*===========================================================================
4278
4279 PROCEDURE NAME: get_po_header_id()
4280
4281 ===========================================================================*/
4282 PROCEDURE get_po_header_id(
4283 x_po_header_id_record IN OUT NOCOPY rcv_shipment_line_sv.document_num_record_type
4284 ) IS
4285 BEGIN
4286 /* type_lookup_code will never be SCHEDULED in po_headers. This
4287 * should be PLANNED. Because of this, for PLANNED POs get_po_header_id
4288 * used to fail and hence open interface used to fail.
4289 * Changing SCHEDULED to PLANNED.
4290 */
4291 SELECT MAX(po_header_id)
4292 INTO x_po_header_id_record.po_header_id
4293 FROM po_headers
4294 WHERE segment1 = x_po_header_id_record.document_num
4295 AND type_lookup_code IN('STANDARD', 'BLANKET', 'PLANNED'); -- Could be a quotation with same number
4296
4297 IF (x_po_header_id_record.po_header_id IS NULL) THEN
4298 x_po_header_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
4299 rcv_error_pkg.set_error_message('RCV_ITEM_PO_ID', x_po_header_id_record.error_record.error_message);
4300 rcv_error_pkg.set_token('PO_NUMBER', x_po_header_id_record.document_num);
4301 rcv_error_pkg.set_token('SHIPMENT', '');
4302 END IF;
4303 EXCEPTION
4304 WHEN OTHERS THEN
4305 x_po_header_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
4306 rcv_error_pkg.set_sql_error_message('get_po_header_id', '000');
4307 x_po_header_id_record.error_record.error_message := rcv_error_pkg.get_last_message;
4308 END get_po_header_id;
4309
4310 /*===========================================================================
4311
4312 PROCEDURE NAME: get_item_id()
4313
4314 ===========================================================================*/
4315 PROCEDURE get_item_id(
4316 x_item_id_record IN OUT NOCOPY rcv_shipment_line_sv.item_id_record_type
4317 ) IS
4318 BEGIN
4319 IF (x_item_id_record.item_num IS NOT NULL) THEN
4320 SELECT MIN(inventory_item_id),
4321 MIN(primary_unit_of_measure),
4322 MIN(lot_control_code), -- bug 608353
4323 MIN(serial_number_control_code)
4324 INTO x_item_id_record.item_id,
4325 x_item_id_record.primary_unit_of_measure,
4326 x_item_id_record.use_mtl_lot, -- bug 608353
4327 x_item_id_record.use_mtl_serial
4328 FROM mtl_item_flexfields
4329 WHERE item_number = x_item_id_record.item_num
4330 AND organization_id = x_item_id_record.to_organization_id;
4331
4332 IF (x_item_id_record.item_id IS NULL) THEN
4333 SELECT MIN(inventory_item_id),
4334 MIN(primary_unit_of_measure),
4335 MIN(lot_control_code), -- bug 608353
4336 MIN(serial_number_control_code)
4337 INTO x_item_id_record.item_id,
4338 x_item_id_record.primary_unit_of_measure,
4339 x_item_id_record.use_mtl_lot,
4340 x_item_id_record.use_mtl_serial
4341 FROM mtl_item_flexfields
4342 WHERE item_number = x_item_id_record.vendor_item_num
4343 AND organization_id = x_item_id_record.to_organization_id;
4344 END IF;
4345 END IF;
4346
4347 IF (x_item_id_record.item_id IS NULL) THEN
4348 x_item_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_warning;
4349 rcv_error_pkg.set_error_message('RCV_ITEM_PO_ID', x_item_id_record.error_record.error_message);
4350 rcv_error_pkg.set_token('ITEM', x_item_id_record.item_num);
4351 END IF;
4352 EXCEPTION
4353 WHEN OTHERS THEN
4354 x_item_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
4355 rcv_error_pkg.set_sql_error_message('get_item_id', '000');
4356 x_item_id_record.error_record.error_message := rcv_error_pkg.get_last_message;
4357 END get_item_id;
4358
4359 /*===========================================================================
4360
4361 PROCEDURE NAME: get_sub_item_id()
4362
4363 ===========================================================================*/
4364 PROCEDURE get_sub_item_id(
4365 x_sub_item_id_record IN OUT NOCOPY rcv_shipment_line_sv.sub_item_id_record_type
4366 ) IS
4367 BEGIN
4368 IF (x_sub_item_id_record.substitute_item_num IS NOT NULL) THEN
4369 SELECT MAX(inventory_item_id)
4370 INTO x_sub_item_id_record.substitute_item_id
4371 FROM mtl_system_items_kfv
4372 WHERE concatenated_segments = x_sub_item_id_record.substitute_item_num;
4373 ELSE
4374 SELECT MAX(inventory_item_id)
4375 INTO x_sub_item_id_record.substitute_item_id
4376 FROM mtl_system_items_kfv
4377 WHERE concatenated_segments = x_sub_item_id_record.vendor_item_num;
4378 END IF;
4379
4380 IF (x_sub_item_id_record.substitute_item_id IS NULL) THEN
4381 x_sub_item_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
4382 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_ID', x_sub_item_id_record.error_record.error_message);
4383 rcv_error_pkg.set_token('ITEM', x_sub_item_id_record.substitute_item_num);
4384 END IF;
4385 EXCEPTION
4386 WHEN OTHERS THEN
4387 x_sub_item_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
4388 rcv_error_pkg.set_sql_error_message('get_sub_item_id', '000');
4389 x_sub_item_id_record.error_record.error_message := rcv_error_pkg.get_last_message;
4390 END get_sub_item_id;
4391
4392 /*===========================================================================
4393
4394 PROCEDURE NAME: get_po_line_id()
4395
4396 ===========================================================================*/
4397 PROCEDURE get_po_line_id(
4398 x_po_line_id_record IN OUT NOCOPY rcv_shipment_line_sv.po_line_id_record_type
4399 ) IS
4400 BEGIN
4401 SELECT po_line_id,
4402 item_id
4403 INTO x_po_line_id_record.po_line_id,
4404 x_po_line_id_record.item_id
4405 FROM po_lines
4406 WHERE po_header_id = x_po_line_id_record.po_header_id
4407 AND line_num = x_po_line_id_record.document_line_num;
4408
4409 IF (x_po_line_id_record.po_line_id IS NULL) THEN
4410 RAISE NO_DATA_FOUND;
4411 END IF;
4412 EXCEPTION
4413 WHEN NO_DATA_FOUND THEN
4414 x_po_line_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
4415 rcv_error_pkg.set_error_message('RCV_ITEM_PO_LINE_ID', x_po_line_id_record.error_record.error_message);
4416 rcv_error_pkg.set_token('NUMBER', x_po_line_id_record.document_line_num);
4417 WHEN OTHERS THEN
4418 x_po_line_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
4419 rcv_error_pkg.set_sql_error_message('get_po_line_id', '000');
4420 x_po_line_id_record.error_record.error_message := rcv_error_pkg.get_last_message;
4421 END get_po_line_id;
4422
4423 /*===========================================================================
4424
4425 PROCEDURE NAME: get_org_id()
4426
4427 This call is done by EDI to obtain the org_id give the location id
4428
4429 ===========================================================================*/
4430 PROCEDURE get_org_id_from_hr_loc_id(
4431 p_hr_location_id IN NUMBER,
4432 x_organization_id OUT NOCOPY NUMBER
4433 ) IS
4434 BEGIN
4435 SELECT inventory_organization_id
4436 INTO x_organization_id
4437 FROM hr_locations
4438 WHERE location_id = p_hr_location_id;
4439 EXCEPTION
4440 WHEN OTHERS THEN
4441 x_organization_id := NULL;
4442 END get_org_id_from_hr_loc_id;
4443 END rcv_transactions_interface_sv;
[Home] [Help]