1 PACKAGE RCV_ROI_TRANSACTION
2 /* $Header: RCVPRETS.pls 120.3.12010000.2 2008/10/10 01:03:03 vthevark ship $*/
3 AS
4
5 CURSOR asn_shipments(
6 v_shipment_header_id NUMBER,
7 v_shipment_line_id NUMBER,
8 v_po_header_id NUMBER,
9 v_item_id NUMBER,
10 v_po_line_num NUMBER,
11 v_po_shipment_num NUMBER,
12 v_po_release_id NUMBER,
13 v_ship_to_org_id NUMBER,
14 v_ship_to_location_id NUMBER,
15 v_vendor_product_num VARCHAR2,
16 v_include_closed_po varchar2
17 )
18 IS
19 SELECT rsl.po_line_location_id,
20 pll.unit_meas_lookup_code,
21 pll.unit_of_measure_class,
22 NVL(pll.promised_date, pll.need_by_date) promised_date,
23 rsl.to_organization_id ship_to_organization_id,
24 pll.quantity quantity_ordered,
25 0, --quantity_shipped,
26 pll.receipt_days_exception_code,
27 pll.qty_rcv_tolerance,
28 pll.qty_rcv_exception_code,
29 pll.days_early_receipt_allowed,
30 pll.days_late_receipt_allowed,
31 NVL(pll.price_override, pl.unit_price) unit_price,
32 pll.match_option, -- 1845702
33 rsl.category_id,
34 rsl.item_description,
35 pl.po_line_id,
36 ph.currency_code,
37 ph.rate_type, -- 1845702
38 0 po_distribution_id,
39 0 code_combination_id,
40 0 req_distribution_id,
41 0, --0 deliver_to_location_id,
42 0, -- 0 deliver_to_person_id,
43 ph.rate_date rate_date, --1845702
44 ph.rate rate, --1845702
45 rsl.destination_type_code, --'' destination_type_code,
46 rsl.to_organization_id destination_organization_id, -- 0 destination_organization_id,
47 rsl.to_subinventory, -- '' destination_subinventory,
48 0 wip_entity_id,
49 0 wip_operation_seq_num,
50 0 wip_resource_seq_num,
51 0 wip_repetitive_schedule_id,
52 0 wip_line_id,
53 0 bom_resource_id,
54 '' ussgl_transaction_code,
55 rsl.ship_to_location_id, --pll.ship_to_location_id,
56 NVL(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code,
57 rsl.shipment_line_id,
58 rsl.item_id -- pl.item_id
59 FROM
60 po_line_locations pll,
61 po_lines pl,
62 po_headers ph,
63 RCV_SHIPMENT_LINES RSL,
64 RCV_SHIPMENT_HEADERS RSH
65 WHERE
66 rsh.shipment_header_id = v_shipment_header_id
67 and rsl.shipment_header_id = rsh.shipment_header_id
68 and rsl.shipment_line_id = nvl(v_shipment_line_id,rsl.shipment_line_id)
69 and rsl.po_header_id = ph.po_header_id
70 and ph.po_header_id = nvl(v_po_header_id,ph.po_header_id)
71 and ((v_po_header_id is not null
72 and pl.line_num=NVL(v_po_line_num, pl.line_num)
73 and pll.shipment_num =
74 nvl(v_po_shipment_num,pll.shipment_num))
75 OR
76 v_po_header_id is null)
77 and rsl.po_line_id = pl.po_line_id
78 and rsl.po_line_location_id = pll.line_location_id
79 and nvl(rsl.po_release_id,0) = nvl(v_po_release_id,nvl(rsl.po_release_id,0))
80 and nvl(rsl.item_id,0) = nvl(v_item_id,nvl(rsl.item_id,0))
81 and rsl.to_organization_id = nvl(v_ship_to_org_id,rsl.to_organization_id)
82 and nvl(rsl.shipment_line_status_code,'EXPECTED') <> 'FULLY RECEIVED'
83 and nvl(rsl.asn_line_flag,'N') = 'Y'
84 and rsh.receipt_source_code = 'VENDOR'
85 AND rsl.ship_to_location_id = NVL(v_ship_to_location_id, rsl.ship_to_location_id)
86 and pll.po_line_id = pl.po_line_id
87 --and nvl(pl.item_id,0) = nvl(rsl.item_id,0)
88 and NVL(pll.po_release_id, 0) = NVL(rsl.po_release_id, NVL(pll.po_release_id, 0))
89 AND NVL(pll.approved_flag, 'N') = 'Y'
90 AND NVL(pll.cancel_flag, 'N') = 'N'
91 AND ( ( (NVL(v_include_closed_po, 'N') = 'Y')
92 AND (NVL(pll.closed_code, 'OPEN')
93 <> 'FINALLY CLOSED'))
94 OR
95 ( (NVL(v_include_closed_po, 'N') = 'N')
96 AND
97 ( NVL(pll.closed_code, 'OPEN') NOT IN
98 ( 'FINALLY CLOSED', 'CLOSED',
99 'CLOSED FOR RECEIVING')))
100 )
101 AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED')
102 AND pll.ship_to_organization_id =NVL(rsl.to_organization_id, pll.ship_to_organization_id)
103 AND pll.ship_to_location_id =NVL(rsl.ship_to_location_id, pll.ship_to_location_id)
104 AND NVL(pl.vendor_product_num, '-999') =NVL(v_vendor_product_num,NVL(pl.vendor_product_num, '-999'))
105 ORDER BY NVL(pll.promised_date, pll.need_by_date);
106
107 CURSOR count_asn_shipments(
108 v_shipment_header_id NUMBER,
109 v_shipment_line_id NUMBER,
110 v_po_header_id NUMBER,
111 v_item_id NUMBER,
112 v_po_line_num NUMBER,
113 v_po_shipment_num NUMBER,
114 v_po_release_id NUMBER,
115 v_ship_to_org_id NUMBER,
116 v_ship_to_location_id NUMBER,
117 v_vendor_product_num VARCHAR2,
118 v_include_closed_po varchar2
119 )
120 IS
121 SELECT COUNT(*)
122 FROM po_line_locations pll,
123 po_lines pl,
124 po_headers ph,
125 RCV_SHIPMENT_LINES RSL,
126 RCV_SHIPMENT_HEADERS RSH
127 WHERE
128 rsh.shipment_header_id = v_shipment_header_id
129 and rsl.shipment_header_id = rsh.shipment_header_id
130 and rsl.shipment_line_id = nvl(v_shipment_line_id,rsl.shipment_line_id)
131 and rsl.po_header_id = ph.po_header_id
132 and ph.po_header_id = nvl(v_po_header_id,ph.po_header_id)
133 and ((v_po_header_id is not null
134 and pl.line_num=NVL(v_po_line_num, pl.line_num)
135 and pll.shipment_num =
136 nvl(v_po_shipment_num,pll.shipment_num))
137 OR
138 v_po_header_id is null)
139 and rsl.po_line_id = pl.po_line_id
140 and rsl.po_line_location_id = pll.line_location_id
141 and nvl(rsl.po_release_id,0) = nvl(v_po_release_id,nvl(rsl.po_release_id,0))
142 and nvl(rsl.item_id,0) = nvl(v_item_id,nvl(rsl.item_id,0))
143 and rsl.to_organization_id = nvl(v_ship_to_org_id,rsl.to_organization_id)
144 and (nvl(rsl.shipment_line_status_code,'EXPECTED') <> 'FULLY RECEIVED')
145 and rsh.receipt_source_code = 'VENDOR'
146 and nvl(rsl.asn_line_flag,'N') = 'Y'
147 AND rsl.ship_to_location_id = NVL(v_ship_to_location_id, rsl.ship_to_location_id)
148 and pll.po_line_id = pl.po_line_id
149 --and nvl(pl.item_id,0) = nvl(rsl.item_id,0)
150 and NVL(pll.po_release_id, 0) = NVL(rsl.po_release_id, NVL(pll.po_release_id, 0))
151 AND NVL(pll.approved_flag, 'N') = 'Y'
152 AND NVL(pll.cancel_flag, 'N') = 'N'
153 AND ( ( (NVL(v_include_closed_po, 'N') = 'Y')
154 AND (NVL(pll.closed_code, 'OPEN')
155 <> 'FINALLY CLOSED'))
156 OR
157 ( (NVL(v_include_closed_po, 'N') = 'N')
158 AND ( NVL(pll.closed_code, 'OPEN') NOT IN
159 ( 'FINALLY CLOSED', 'CLOSED',
160 'CLOSED FOR RECEIVING')))
161 )
162 AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED')
163 AND pll.ship_to_organization_id =NVL(rsl.to_organization_id, pll.ship_to_organization_id)
164 AND pll.ship_to_location_id =NVL(rsl.ship_to_location_id, pll.ship_to_location_id)
165 AND NVL(pl.vendor_product_num, '-999') =NVL(v_vendor_product_num,NVL(pl.vendor_product_num, '-999'));
166
167 /* Bug 3648871.
168 * When v_po_header_id was null, we did not join po_distributions
169 * table with other tables. Hence there was performance problem.
170 * Changed the condition join po_distributions with po_line_locations
171 * and po_lines in cursors asn_distributions and count_asn_distributions.
172 */
173 CURSOR asn_distributions(
174 v_shipment_header_id NUMBER,
175 v_shipment_line_id NUMBER,
176 v_po_header_id NUMBER,
177 v_item_id NUMBER,
178 v_po_line_num NUMBER,
179 v_po_shipment_num NUMBER,
180 v_po_distribution_num NUMBER,
181 v_po_release_id NUMBER,
182 v_ship_to_org_id NUMBER,
183 v_ship_to_location_id NUMBER,
184 v_vendor_product_num VARCHAR2,
185 v_include_closed_po varchar2
186 )
187 IS
188 SELECT rsl.po_line_location_id,
189 pll.unit_meas_lookup_code,
190 pll.unit_of_measure_class,
191 NVL(pll.promised_date, pll.need_by_date) promised_date,
192 rsl.to_organization_id ship_to_organization_id,
193 pll.quantity quantity_ordered,
194 0, --quantity_shipped,
195 pll.receipt_days_exception_code,
196 pll.qty_rcv_tolerance,
197 pll.qty_rcv_exception_code,
198 pll.days_early_receipt_allowed,
199 pll.days_late_receipt_allowed,
200 NVL(pll.price_override, pl.unit_price) unit_price,
201 pll.match_option, -- 1845702
202 rsl.category_id,
203 rsl.item_description,
204 pl.po_line_id,
205 ph.currency_code,
206 ph.rate_type, -- 1845702
207 pod.po_distribution_id,
208 pod.code_combination_id,
209 pod.req_distribution_id,
210 pod.deliver_to_location_id,
211 pod.deliver_to_person_id,
212 pod.rate_date, pod.rate,
213 pod.destination_type_code,
214 pod.destination_organization_id,
215 pod.destination_subinventory,
216 pod.wip_entity_id,
217 pod.wip_operation_seq_num,
218 pod.wip_resource_seq_num,
219 pod.wip_repetitive_schedule_id,
220 pod.wip_line_id,
221 pod.bom_resource_id,
222 pod.ussgl_transaction_code,
223 pll.ship_to_location_id,
224 NVL(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code,
225 rsl.shipment_line_id,
226 rsl.item_id
227 FROM
228 po_distributions pod,
229 po_line_locations pll,
230 po_lines pl,
231 po_headers ph,
232 RCV_SHIPMENT_LINES RSL,
233 RCV_SHIPMENT_HEADERS RSH
234 WHERE
235 rsh.shipment_header_id = v_shipment_header_id
236 and rsl.shipment_header_id = rsh.shipment_header_id
237 and rsl.shipment_line_id = nvl(v_shipment_line_id,rsl.shipment_line_id)
238 and rsl.po_header_id = ph.po_header_id
239 and ph.po_header_id = nvl(v_po_header_id,ph.po_header_id)
240 and ((v_po_header_id is not null
241 and pl.line_num=NVL(v_po_line_num, pl.line_num)
242 and pll.shipment_num =
243 nvl(v_po_shipment_num,pll.shipment_num)
244 and pod.distribution_num =
245 nvl(v_po_distribution_num,pod.distribution_num))
246 OR
247 v_po_header_id is null)
248 and pod.po_header_id = ph.po_header_id
249 and pod.line_location_id = pll.line_location_id
250 and rsl.po_line_id = pl.po_line_id
251 and rsl.po_line_location_id = pll.line_location_id
252 and nvl(rsl.po_release_id,0) = nvl(v_po_release_id,nvl(rsl.po_release_id,0))
253 and nvl(rsl.item_id,0) = nvl(v_item_id,nvl(rsl.item_id,0))
254 and rsl.to_organization_id = nvl(v_ship_to_org_id,rsl.to_organization_id)
255 and (nvl(rsl.shipment_line_status_code,'EXPECTED') <> 'FULLY RECEIVED')
256 and rsh.receipt_source_code = 'VENDOR'
257 and nvl(rsl.asn_line_flag,'N') = 'Y'
258 AND rsl.ship_to_location_id = NVL(v_ship_to_location_id, rsl.ship_to_location_id)
259 and pll.po_line_id = pl.po_line_id
260 --and nvl(pl.item_id,0) = nvl(rsl.item_id,0)
261 and NVL(pll.po_release_id, 0) = NVL(rsl.po_release_id, NVL(pll.po_release_id, 0))
262 AND NVL(pll.approved_flag, 'N') = 'Y'
263 AND NVL(pll.cancel_flag, 'N') = 'N'
264 AND ( ( (NVL(v_include_closed_po, 'N') = 'Y')
265 AND (NVL(pll.closed_code, 'OPEN')
266 <> 'FINALLY CLOSED'))
267 OR
268 ( (NVL(v_include_closed_po, 'N') = 'N')
269 AND ( NVL(pll.closed_code, 'OPEN') NOT IN
270 ( 'FINALLY CLOSED', 'CLOSED',
271 'CLOSED FOR RECEIVING')))
272 )
273 AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED')
274 AND pll.ship_to_organization_id =NVL(rsl.to_organization_id, pll.ship_to_organization_id)
275 AND pll.ship_to_location_id =NVL(rsl.ship_to_location_id, pll.ship_to_location_id)
276 AND NVL(pl.vendor_product_num, '-999') =NVL(v_vendor_product_num,NVL(pl.vendor_product_num, '-999'));
277
278 CURSOR count_asn_distributions(
279 v_shipment_header_id NUMBER,
280 v_shipment_line_id NUMBER,
281 v_po_header_id NUMBER,
282 v_item_id NUMBER,
283 v_po_line_num NUMBER,
284 v_po_shipment_num NUMBER,
285 v_po_distribution_num NUMBER,
286 v_po_release_id NUMBER,
287 v_ship_to_org_id NUMBER,
288 v_ship_to_location_id NUMBER,
289 v_vendor_product_num VARCHAR2,
290 v_include_closed_po varchar2
291 )
292 IS
293 SELECT COUNT(*)
294 FROM
295 po_distributions pod,
296 po_line_locations pll,
297 po_lines pl,
298 po_headers ph,
299 RCV_SHIPMENT_LINES RSL,
300 RCV_SHIPMENT_HEADERS RSH
301 WHERE
302 rsh.shipment_header_id = v_shipment_header_id
303 and rsl.shipment_header_id = rsh.shipment_header_id
304 and rsl.shipment_line_id = nvl(v_shipment_line_id,rsl.shipment_line_id)
305 and rsl.po_header_id = ph.po_header_id
306 and ph.po_header_id = nvl(v_po_header_id,ph.po_header_id)
307 and ((v_po_header_id is not null
308 and pl.line_num=NVL(v_po_line_num, pl.line_num)
309 and pll.shipment_num =
310 nvl(v_po_shipment_num,pll.shipment_num)
311 and pod.distribution_num =
312 nvl(v_po_distribution_num,pod.distribution_num))
313 OR
314 v_po_header_id is null)
315 and pod.po_header_id = ph.po_header_id
316 and pod.line_location_id = pll.line_location_id
317 and rsl.po_line_id = pl.po_line_id
318 and rsl.po_line_location_id = pll.line_location_id
319 and nvl(rsl.po_release_id,0) = nvl(v_po_release_id,nvl(rsl.po_release_id,0))
320 and nvl(rsl.item_id,0) = nvl(v_item_id,nvl(rsl.item_id,0))
321 and rsl.to_organization_id = nvl(v_ship_to_org_id,rsl.to_organization_id)
322 and (nvl(rsl.shipment_line_status_code,'EXPECTED') <> 'FULLY RECEIVED')
323 and rsh.receipt_source_code = 'VENDOR'
324 and nvl(rsl.asn_line_flag,'N') = 'Y'
325 AND rsl.ship_to_location_id = NVL(v_ship_to_location_id, rsl.ship_to_location_id)
326 and pll.po_line_id = pl.po_line_id
327 --and nvl(pl.item_id,0) = nvl(rsl.item_id,0)
328 and NVL(pll.po_release_id, 0) = NVL(rsl.po_release_id, NVL(pll.po_release_id, 0))
329 AND NVL(pll.approved_flag, 'N') = 'Y'
330 AND NVL(pll.cancel_flag, 'N') = 'N'
331 AND ( ( (NVL(v_include_closed_po, 'N') = 'Y')
332 AND (NVL(pll.closed_code, 'OPEN')
333 <> 'FINALLY CLOSED'))
334 OR
335 ( (NVL(v_include_closed_po, 'N') = 'N')
336 AND ( NVL(pll.closed_code, 'OPEN') NOT IN
337 ( 'FINALLY CLOSED', 'CLOSED',
338 'CLOSED FOR RECEIVING')))
339 )
340 AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED')
341 AND pll.ship_to_organization_id =NVL(rsl.to_organization_id, pll.ship_to_organization_id)
342 AND pll.ship_to_location_id =NVL(rsl.ship_to_location_id, pll.ship_to_location_id)
343 AND NVL(pl.vendor_product_num, '-999') =NVL(v_vendor_product_num,NVL(pl.vendor_product_num, '-999'));
344
345 PROCEDURE derive_vendor_rcv_line (
346 X_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
347 n IN OUT NOCOPY binary_integer,
348 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
349 X_header_record IN rcv_roi_preprocessor.header_rec_type);
350
351
352 PROCEDURE default_vendor_rcv_line (
353 X_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
354 n IN binary_integer,
355 X_header_id IN rcv_headers_interface.header_interface_id%type,
356 X_header_record IN rcv_roi_preprocessor.header_rec_type);
357
358
359 PROCEDURE validate_vendor_rcv_line (
360 X_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
361 n IN binary_integer,
362 X_asn_type IN rcv_headers_interface.asn_type%type,
363 X_header_record IN rcv_roi_preprocessor.header_rec_type);
364
365
366 PROCEDURE derive_ship_to_org_info (
367 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
368 n IN OUT NOCOPY binary_integer,
369 X_header_record IN rcv_roi_preprocessor.header_rec_type);
370
371 PROCEDURE derive_vendor_info (
372 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
373 n IN OUT NOCOPY binary_integer);
374
375 PROCEDURE derive_vendor_site_info (
376 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
377 n IN OUT NOCOPY binary_integer);
378
379 PROCEDURE derive_po_header_info (
380 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
381 n IN OUT NOCOPY binary_integer);
382
383 PROCEDURE derive_asn_header_info (
384 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
385 n IN OUT NOCOPY binary_integer,
386 x_header_record IN rcv_roi_preprocessor.header_rec_type);
387 PROCEDURE derive_item_info (
388 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
389 n IN OUT NOCOPY binary_integer);
390
391 PROCEDURE derive_substitute_item_info (
392 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
393 n IN OUT NOCOPY binary_integer);
394
395 PROCEDURE derive_po_line_info (
396 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
397 n IN OUT NOCOPY binary_integer);
398
399 PROCEDURE derive_from_org_info (
400 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
401 n IN OUT NOCOPY binary_integer);
402
403 PROCEDURE derive_transit_org_info (
404 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
405 n IN OUT NOCOPY binary_integer);
406
407 PROCEDURE derive_location_info (
408 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
409 n IN OUT NOCOPY binary_integer);
410
411 PROCEDURE derive_ship_to_location_info (
412 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
413 n IN OUT NOCOPY binary_integer);
414
415 PROCEDURE derive_routing_header_info (
416 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
417 n IN OUT NOCOPY binary_integer);
418
419 PROCEDURE derive_routing_step_info (
420 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
421 n IN OUT NOCOPY binary_integer);
422
423 PROCEDURE derive_deliver_to_person_info (
424 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
425 n IN OUT NOCOPY binary_integer);
426
427 PROCEDURE derive_deliver_to_loc_info (
428 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
429 n IN OUT NOCOPY binary_integer);
430
431 PROCEDURE derive_from_locator_id (
432 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
433 n IN OUT NOCOPY binary_integer);
434
435 PROCEDURE derive_to_locator_id (
436 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
437 n IN OUT NOCOPY binary_integer);
438
439 PROCEDURE validate_non_services_fields(
440 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
441 n IN BINARY_INTEGER );
442
443 PROCEDURE default_from_subloc_info (
444 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
445 n IN binary_integer);
446
447 PROCEDURE default_to_subloc_info (
448 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
449 n IN binary_integer);
450
451 PROCEDURE derive_reason_info (
452 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
453 n IN OUT NOCOPY binary_integer);
454
455 PROCEDURE derive_auto_transact_code (
456 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
457 n IN OUT NOCOPY binary_integer);
458
459 PROCEDURE derive_vendor_rcv_line_qty (
460 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
461 n IN OUT NOCOPY binary_integer,
462 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
463 X_header_record IN rcv_roi_preprocessor.header_rec_type);
464
465 PROCEDURE validate_transaction_date (
466 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
467 n IN binary_integer);
468
469 PROCEDURE validate_quantity_invoiced (
470 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
471 n IN binary_integer);
472
473 PROCEDURE validate_transaction_uom (
474 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
475 n IN binary_integer);
476
477 PROCEDURE validate_item_info (
478 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
479 n IN binary_integer);
480
481 PROCEDURE validate_freight_carrier_code (
482 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
483 n IN binary_integer);
484
485 PROCEDURE validate_dest_type (
486 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
487 n IN binary_integer);
488
489 PROCEDURE validate_ship_to_loc (
490 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
491 n IN binary_integer);
492
493 PROCEDURE validate_deliver_to_person (
494 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
495 n IN binary_integer);
496
497 PROCEDURE validate_routing_record(
498 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
499 n IN binary_integer);
500
501 PROCEDURE validate_deliver_to_loc (
502 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
503 n IN binary_integer);
504
505 PROCEDURE validate_subinventory (
506 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
507 n IN binary_integer);
508
509 PROCEDURE validate_locator (
510 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
511 n IN binary_integer);
512
513 PROCEDURE validate_tax_code (
514 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
515 n IN binary_integer,
516 /* Bug3454491 (1) */
517 x_asn_type IN rcv_headers_interface.asn_type%TYPE);
518
519 PROCEDURE validate_country_of_origin (
520 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
521 n IN binary_integer);
522
523 PROCEDURE validate_asl (
524 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
525 n IN binary_integer);
526
527 PROCEDURE validate_consigned_inventory (
528 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
529 n IN binary_integer,
530 /* Bug3454491 (2) */
531 x_asn_type IN rcv_headers_interface.asn_type%TYPE);
532
533 PROCEDURE validate_shipped_qty (
534 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
535 n IN binary_integer);
536
537 PROCEDURE validate_ref_integrity (
538 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
539 n IN binary_integer,
540 X_header_record IN rcv_roi_preprocessor.header_rec_type);
541
542 PROCEDURE exchange_sub_items (
543 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
544 n IN binary_integer);
545
546 PROCEDURE validate_ref_integ (
547 x_ref_integrity_rec IN OUT NOCOPY rcv_shipment_line_sv.ref_integrity_record_type,
548 X_header_record IN rcv_roi_preprocessor.header_rec_type);
549
550 PROCEDURE validate_inspection_status (
551 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
552 n IN binary_integer);
553
554 PROCEDURE validate_transaction_type (
555 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
556 n IN binary_integer);
557
558 /** OPM change Bug# 3061052**/
559 -- added validate_opm_attributes to validate OPM specific columns.
560 PROCEDURE validate_opm_attributes(
561 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
562 n IN BINARY_INTEGER ) ;
563
564 PROCEDURE validate_temp_labor_info(
565 v_trans_tab IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
566 n IN binary_integer,
567 v_header_record IN rcv_roi_preprocessor.header_rec_type);
568
569 PROCEDURE validate_amount(
570 v_trans_tab IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
571 n IN binary_integer,
572 v_header_record IN rcv_roi_preprocessor.header_rec_type );
573
574 PROCEDURE HANDLE_RCV_ASN_TRANSACTIONS (V_TRANS_TAB IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
575 V_HEADER_RECORD IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type);
576
577
578 PROCEDURE derive_vendor_trans_del (
579 X_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
580 n IN OUT NOCOPY binary_integer,
581 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
582 X_header_record IN rcv_roi_preprocessor.header_rec_type);
583
584 Procedure derive_parent_id(
585 X_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
586 n IN OUT NOCOPY binary_integer );
587
588 PROCEDURE derive_trans_del_line_quantity (
589 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
590 n IN OUT NOCOPY binary_integer,
591 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type);
592
593 PROCEDURE default_vendor_trans_del (
594 X_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
595 n IN binary_integer);
596
597 PROCEDURE derive_correction_line (
598 X_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
599 n IN OUT NOCOPY binary_integer,
600 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
601 X_header_record IN rcv_roi_preprocessor.header_rec_type);
602
603
604 PROCEDURE derive_correction_line_qty (
605 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
606 n IN OUT NOCOPY binary_integer,
607 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type);
608
609
610 PROCEDURE derive_correction_line_amt (
611 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
612 n IN OUT NOCOPY binary_integer,
613 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type);
614
615 PROCEDURE default_vendor_correct (
616 X_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
617 n IN binary_integer);
618
619
620 FUNCTION convert_into_correct_qty(source_qty in number,
621 source_uom in varchar2,
622 item_id in number,
623 dest_uom in varchar2)
624 RETURN NUMBER;
625
626
627 PROCEDURE get_interface_available_qty (
628 X_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
629 n IN binary_integer,
630 x_interface_qty OUT NOCOPY number);
631
632
633 PROCEDURE get_interface_available_amt(
634 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
635 n IN BINARY_INTEGER,
636 x_interface_amt OUT NOCOPY number);
637
638 PROCEDURE update_interface_available_qty (
639 X_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
640 n IN binary_integer);
641
642 PROCEDURE update_interface_available_amt (
643 X_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
644 n IN binary_integer);
645
646 PROCEDURE update_total_transaction_qty(
647 p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%type,
648 p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%type,
649 p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%type,
650 p_primary_quantity IN rcv_transactions_interface.quantity%type,
651 p_transaction_type IN rcv_transactions_interface.transaction_type%type,
652 x_interface_transaction_qty OUT NOCOPY
653 rcv_transactions_interface.interface_transaction_qty%type
654 );
655
656 PROCEDURE update_total_transaction_amt(
657 p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%type,
658 p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%type,
659 p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%type,
660 p_amount IN rcv_transactions_interface.amount%type,
661 p_transaction_type IN rcv_transactions_interface.transaction_type%type,
662 x_interface_transaction_amt OUT NOCOPY
663 rcv_transactions_interface.interface_transaction_amt%type
664 );
665
666
667 PROCEDURE derive_vendor_rcv_line_amt(
668 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
669 n IN OUT NOCOPY BINARY_INTEGER,
670 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
671 x_header_record IN rcv_roi_preprocessor.header_rec_type );
672
673 PROCEDURE validate_amt_based_rcv_line(
674 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
675 n IN BINARY_INTEGER,
676 x_asn_type IN rcv_headers_interface.asn_type%TYPE,
677 x_header_record IN rcv_roi_preprocessor.header_rec_type
678 );
679
680
681 /* Complex work.
682 * We have a separate procedure derive_matching_basis
683 * to get the matching basis now. This is because we
684 * are going to get the matching_basis from
685 * po_line_locations and hence moved it from
686 * derive_po_line_info.
687 */
688 PROCEDURE derive_matching_basis(
689 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
690 n IN BINARY_INTEGER
691 );
692
693 /* The following procedure and functions are added as part of Bug#6375015 fix.
694 Procedure get_deliver_to_person_from_po()
695 Function get_deliver_to_person_from_rt()
696 Function get_deliver_to_person_from_rti() */
697
698 PROCEDURE get_deliver_to_person_from_po(
699 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
700 n IN BINARY_INTEGER
701 );
702
703 FUNCTION get_deliver_to_person_from_rt(
704 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
705 n IN BINARY_INTEGER
706 ) RETURN NUMBER;
707
708 FUNCTION get_deliver_to_person_from_rti(
709 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
710 n IN BINARY_INTEGER
711 ) RETURN NUMBER;
712
713 PROCEDURE validate_lcm_line (
714 X_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
715 n IN binary_integer,
716 X_asn_type IN rcv_headers_interface.asn_type%type,
717 X_header_record IN rcv_roi_preprocessor.header_rec_type);
718
719 END RCV_ROI_TRANSACTION;
720
[Home] [Help]