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