1 PACKAGE RCV_CORE_S AS
2 /* $Header: RCVCOCOS.pls 120.1 2005/08/12 02:42:40 spendokh noship $*/
3
4 /*===========================================================================
5 PROCEDURE NAME: val_destination_info
6
7 DESCRIPTION: Procedure validates whether the ship_to_location,
8 deliver_to_location_id, destination organizations,
9 destination subinventory or the deliver_to_person_id
10 that are specified as defaults on the PO are active
11 for the transaction date. If not, destination fields
12 are nulled out.
13
14 If the destination of an item in INVENTORY then
15 looks up the default locator for that subinventory for
16 the line item.
17
18 PARAMETERS:
19
20 Parameter IN/OUT Datatype Description
21 ------------- ------ ---------- ----------------------------
22 x_trx_date IN DATE Transaction Date
23
24 x_receipt_source_code IN VARCHAR2 Receipt Source Code:
25 VENDOR
26 INVENTORY
27 INTERNAL
28
29 x_line_loc_id IN NUMBER Line Location ID
30
31 x_dist_id IN NUMBER Distribution ID
32
33 x_ship_line_id IN NUMBER Shipment Line ID
34
35 x_org_id IN NUMBER Organization ID
36
37 x_item_id IN NUMBER Line Item ID
38
39 x_ship_to_loc_id OUT NUMBER Ship-To Location ID
40
41 x_ship_to_loc_code OUT VARCHAR2 Ship-To Location Code
42
43 x_deliver_to_loc_id OUT NUMBER Deliver-To Location ID
44
45 x_deliver_to_loc_code OUT VARCHAR2 Deliver-To Location Code
46
47 x_dest_subinv OUT VARCHAR2 Destination Subinventory
48
49 x_locator_id OUT NUMBER Locator ID
50
51 x_locator OUT VARCHAR2 Locator
52
53 x_dest_org_id OUT NUMBER Destination Organization ID
54
55 x_dest_org_code OUT VARCHAR2 Destination Organization Code
56
57 x_dest_type_code OUT VARCHAR2 Destination Type Code
58
59 x_deliver_to_person_id OUT NUMBER Deliver-To Person ID
60
61 x_deliver_to_person OUT VARCHAR2 Deliver-To Person
62
63 RETURN VALUE: Returns default values for:
64 Ship-to location id
65 Ship-to location code
66 Deliver-to location id
67 Deliver-to location code
68 Destination Subinventory
69 Locator id
70 Locator
71 Destination organization id
72 Destination organization code
73 Destination type code
74 EXPENSE
75 INVENTORY
76 SHOP FLOOR
77 Deliver-to person id
78 Deliver-to person
79
80 DESIGN REFERENCES: RCVRCERC.dd
81
82 ALGORITHM:
83
84 NOTES:
85
86 OPEN ISSUES:
87
88 CLOSED ISSUES:
89
90 CHANGE HISTORY:
91 ===========================================================================*/
92
93 PROCEDURE val_destination_info (x_receipt_source_code IN VARCHAR2,
94 x_trx_date IN DATE,
95 x_line_loc_id IN NUMBER,
96 x_dist_id IN NUMBER,
97 x_ship_line_id IN NUMBER,
98 x_org_id IN NUMBER,
99 x_item_id IN NUMBER,
100 x_ship_to_loc_id OUT NOCOPY NUMBER,
101 x_ship_to_loc_code OUT NOCOPY VARCHAR2,
102 x_deliver_to_loc_id OUT NOCOPY NUMBER,
103 x_deliver_to_loc_code OUT NOCOPY VARCHAR2,
104 x_dest_subinv OUT NOCOPY VARCHAR2,
105 x_locator_id OUT NOCOPY NUMBER,
106 x_locator OUT NOCOPY VARCHAR2,
107 x_dest_org_id OUT NOCOPY NUMBER,
108 x_dest_org_code OUT NOCOPY VARCHAR2,
109 x_dest_type_code OUT NOCOPY VARCHAR2,
110 x_deliver_to_person_id OUT NOCOPY NUMBER,
111 x_deliver_to_person OUT NOCOPY VARCHAR2);
112
113 /*===========================================================================
114 PROCEDURE NAME: get_receiving_controls
115
116 DESCRIPTION: Gets the following receiving controls:
117
118 Enforce Ship-to Location
119 Allow Substitute Receipts
120 Receipt Routing Header ID
121 Quantity Receipt Tolerance
122 Quantity Received Exception Code
123 Days Early Receipt Allowed
124 Days Late Receipt Allowed
125 Receipt Date Exception Code
126
127 To determine the controls, the procedure searches up
128 a path:
129
130 1) po_line_locations
131 2) mtl_system_items
132 3) po_vendors
133 4) rcv_parameters
134
135 PARAMETERS: x_line_loc_id IN NUMBER
136 x_item_id IN NUMBER
137 x_vendor_id IN NUMBER
138 x_org_id IN NUMBER
139 x_enforce_ship_to_loc IN OUT VARCHAR2
140 x_allow_substitutes IN OUT VARCHAR2
141 x_routing_id IN OUT NUMBER
142 x_qty_rcv_tolerance IN OUT NUMBER
143 x_qty_rcv_exception IN OUT VARCHAR2
144 x_days_early_receipt IN OUT NUMBER
145 x_days_late_receipt IN OUT NUMBER
146 x_rcv_date_exception IN OUT VARCHAR2
147
148 RETURN VALUE:
149
150 DESIGN REFERENCES: RCVRCERC.dd
151 RCVTXERT.dd
152
153 ALGORITHM:
154
155 NOTES:
156
157 OPEN ISSUES:
158
159 CLOSED ISSUES:
160
161 CHANGE HISTORY:
162 ===========================================================================*/
163
164 PROCEDURE get_receiving_controls(x_line_loc_id IN NUMBER,
165 x_item_id IN NUMBER,
166 x_vendor_id IN NUMBER,
167 x_org_id IN NUMBER,
168 x_enforce_ship_to_loc IN OUT NOCOPY VARCHAR2,
169 x_allow_substitutes IN OUT NOCOPY VARCHAR2,
170 x_routing_id IN OUT NOCOPY NUMBER,
171 x_qty_rcv_tolerance IN OUT NOCOPY NUMBER,
172 x_qty_rcv_exception IN OUT NOCOPY VARCHAR2,
173 x_days_early_receipt IN OUT NOCOPY NUMBER,
174 x_days_late_receipt IN OUT NOCOPY NUMBER,
175 x_rcv_date_exception IN OUT NOCOPY VARCHAR2,
176 p_payment_type IN VARCHAR2 DEFAULT NULL --Bug 4549207
177 );
178
179
180 PROCEDURE get_receiving_controls -- <BUG 3365446>
181 ( p_order_type_lookup_code IN VARCHAR2
182 , p_purchase_basis IN VARCHAR2
183 , p_line_location_id IN NUMBER
184 , p_item_id IN NUMBER
185 , p_org_id IN NUMBER
186 , p_vendor_id IN NUMBER
187 , p_drop_ship_flag IN VARCHAR2 := 'N'
188 , x_enforce_ship_to_loc_code OUT NOCOPY VARCHAR2
189 , x_allow_substitute_receipts OUT NOCOPY VARCHAR2
190 , x_routing_id OUT NOCOPY NUMBER
191 , x_routing_name OUT NOCOPY VARCHAR2
192 , x_qty_rcv_tolerance OUT NOCOPY NUMBER
193 , x_qty_rcv_exception_code OUT NOCOPY VARCHAR2
194 , x_days_early_receipt_allowed OUT NOCOPY NUMBER
195 , x_days_late_receipt_allowed OUT NOCOPY NUMBER
196 , x_receipt_days_exception_code OUT NOCOPY VARCHAR2
197 , p_payment_type IN VARCHAR2 DEFAULT NULL --Bug 4549207
198 );
199
200 /*===========================================================================
201 FUNCTION NAME: val_unique_receipt_num
202
203 DESCRIPTION: Function searches through rcv_shipment_headers looking
204 for the receipt number passed in. If it finds a
205 duplicate, it returns a value of FALSE. If it doesn't
206 it searches through the po_history_receipts table
207 looking for the receipt number passed in. If it finds
208 a duplicate, it returns a value of FALSE, otherwise it
209 returns TRUE.
210
211 PARAMETERS: x_receipt_num IN VARCHAR2
212
213 RETURN VALUE: TRUE if receipt number is unique, FALSE otherwise.
214
215 DESIGN REFERENCES: RCVRCERC.dd
216
217 ALGORITHM:
218
219 NOTES:
220
221 OPEN ISSUES:
222
223 CLOSED ISSUES:
224
225 CHANGE HISTORY:
226 ===========================================================================*/
227
228 FUNCTION val_unique_receipt_num(x_receipt_num IN VARCHAR2) RETURN BOOLEAN;
229
230 /*===========================================================================
231 FUNCTION NAME: val_unique_shipment_num
232
233 DESCRIPTION: Function searches through rcv_shipment_headers to
234 validate that a shipment number is unique for
235 a given vendor.
236
237 PARAMETERS: x_shipment_num IN VARCHAR2
238 x_vendor_id IN NUMBER
239
240 RETURN VALUE: TRUE if shipment number is unique, FALSE otherwise.
241
242 DESIGN REFERENCES: RCVRCERC.dd
243
244 ALGORITHM:
245
246 NOTES:
247
248 OPEN ISSUES:
249
250 CLOSED ISSUES:
251
252 CHANGE HISTORY:
253 ===========================================================================*/
254
255 FUNCTION val_unique_shipment_num (x_shipment_num IN VARCHAR2,
256 x_vendor_id IN NUMBER) RETURN BOOLEAN;
257
258 /*===========================================================================
259 PROCEDURE NAME: get_ussgl_info
260
261 DESCRIPTION: Gets ussgl_transaction_code, government_context from
262 po_line_locations.
263
264 PARAMETERS: x_line_location_id IN NUMBER
265 x_ussgl_trx_code OUT VARCHAR2
266 x_govt_context OUT VARCHAR2
267
268 DESIGN REFERENCES: RCVRCMUR.dd
269
270 ALGORITHM:
271
272 NOTES:
273
274 OPEN ISSUES:
275
276 CLOSED ISSUES:
277
278 CHANGE HISTORY:
279 ===========================================================================*/
280
281 PROCEDURE get_ussgl_info(x_line_location_id IN NUMBER,
282 x_ussgl_trx_code OUT NOCOPY VARCHAR2,
283 x_govt_context OUT NOCOPY VARCHAR2);
284
285 /*===========================================================================
286
287 PROCEDURE NAME: val_po_shipment
288
289 DESCRIPTION: Call RCV_QUANTITIES_S.get_available_quantity to get the
290 unit of measure, available and tolerable quantities for a
291 po shipment.
292
293 PARAMETERS:
294
295 Parameter IN/OUT Datatype Description
296 -------------------- -------- ---------- ----------------------------------
297 x_trx_type IN VARCHAR2 Transaction Type:
298 RECEIVE
299 MATCH
300
301 x_parent_id IN NUMBER Line_location_id for Vendor Receipts
302 Shipment_line_id for Internal
303 Receipts
304
305 x_receipt_source_code IN VARCHAR2 Receipt Source Code:
306 VENDOR
307 INTERNAL
308 INVENTORY
309
310 x_parent_trx_type IN VARCHAR Parent Transaction Type
311
312 x_grand_parent_id IN NUMBER Grand Parent ID
313
314 x_correction_type IN VARCHAR2 Correction Type
315
316 x_available_quantity IN OUT NUMBER Quantity Available to Receive
317
318 x_tolerable_qty IN OUT NUMBER Quantity Toleralbe to Receive
319
320 x_uom IN OUT VARCHAR2 Unit of Measure
321
322
323 DESIGN REFERENCES: RCVRCMUR.dd
324
325
326 ALGORITHM:
327
328 NOTES:
329
330 OPEN ISSUES:
331
332 CLOSED ISSUES:
333
334 CHANGE HISTORY:
335 ===========================================================================*/
336
337 PROCEDURE val_po_shipment(x_trx_type IN VARCHAR2,
338 x_parent_id IN NUMBER,
339 x_receipt_source_code IN VARCHAR2,
340 x_parent_trx_type IN VARCHAR2,
341 x_grand_parent_id IN NUMBER,
342 x_correction_type IN VARCHAR2,
343 x_available_quantity IN OUT NOCOPY NUMBER,
344 x_tolerable_qty IN OUT NOCOPY NUMBER,
345 x_uom IN OUT NOCOPY VARCHAR2);
346
347 /*===========================================================================
348 PROCEDURE NAME: val_exp_cas_func
349
350 DESCRIPTION:
351 - Ship to location is not available and the destination is to
352 receiving
353 - Deliver to Person/location is not available for Expense
354 Destination type and the destination is to final
355 PARAMETERS:
356
357 RETURN VALUE:
358
359 DESIGN REFERENCES: RCVRCERC.dd
360
361 ALGORITHM:
362
363 NOTES:
364
365 OPEN ISSUES:
366
367 CLOSED ISSUES:
368
369 CHANGE HISTORY:
370 ===========================================================================*/
371 PROCEDURE val_exp_cas_func;
372
373
374 /*===========================================================================
375 PROCEDURE NAME: get_outside_processing_info
376
377 DESCRIPTION: Gets the following outside processing detials :
378
379 Job Schedule
380 Operation Sequence number
381 Department
382 WIp Line
383 BOM resource Id
384
385 PARAMETERS:
386 x_po_distribution_id IN NUMBER
387 x_organization_id IN NUMBER
391 x_wip_line OUT VARCHAR2
388 x_job_schedule OUT VARCHAR2
389 x_operation_seq_num OUT VARCHAR2
390 x_department OUT VARCHAR2
392 x_bom_resource_id OUT NUMBER,
393 x_po_operation_seq_num OUT NUMBER,
394 x_po_resource_seq_num OUT NUMBER
395
396 The po_operation and resource sequence numbers are off the po
397 distribution and is used for inserting the transaction rather
398 than the operation_seq_num which is derived from the wip tables
399 and shows the next operation rather than the current one. This
400 value is used for display purposes
401
402
403 RETURN VALUE:
404
405 DESIGN REFERENCES: RCVRCERC.dd
406 RCVTXERT.dd
407
408 ALGORITHM:
409
410 NOTES:
411
412 OPEN ISSUES:
413
414 CLOSED ISSUES:
415
416 CHANGE HISTORY:
417 ===========================================================================*/
418 procedure get_outside_processing_info ( x_po_distribution_id IN NUMBER,
419 x_organization_id IN NUMBER,
420 x_job_schedule OUT NOCOPY VARCHAR2,
421 x_operation_seq_num OUT NOCOPY VARCHAR2,
422 x_department OUT NOCOPY VARCHAR2,
423 x_wip_line OUT NOCOPY VARCHAR2,
424 x_bom_resource_id OUT NOCOPY NUMBER,
425 x_po_operation_seq_num OUT NOCOPY NUMBER,
426 x_po_resource_seq_num OUT NOCOPY NUMBER
427 );
428
429 /*===========================================================================
430 FUNCTION NAME: get_note_count
431
432 DESCRIPTION:
433
434 PARAMETERS: x_header_id IN NUMBER
435 x_line_id IN NUMBER
436 x_location_id IN NUMBER
437 x_po_line_id IN NUMBER
438 x_po_release_id IN NUMBER
439 x_po_header_id IN NUMBER
440 x_item_id IN NUMBER
441
442 RETURN VALUE: NUMBER
443
444 DESIGN REFERENCES: RCVRCERC.dd
445 RCVTXERT.dd
446
447 ALGORITHM:
448
449 NOTES:
450
451 OPEN ISSUES:
452
453 CLOSED ISSUES:
454
455 CHANGE HISTORY:
456 ===========================================================================*/
457 FUNCTION GET_NOTE_COUNT ( x_header_id IN NUMBER,
458 x_line_id IN NUMBER,
459 x_location_id IN NUMBER,
460 x_po_line_id IN NUMBER,
461 x_po_release_id IN NUMBER,
462 x_po_header_id IN NUMBER,
463 x_item_id IN NUMBER) RETURN NUMBER ;
464 /*==========================================================================*/
465 PROCEDURE OUT_OP_INFO ( x_wip_entity_id IN NUMBER,
466 x_organization_id IN NUMBER,
467 x_wip_repetitive_schedule_id IN NUMBER,
468 x_wip_operation_seq_num IN NUMBER,
469 x_wip_resource_seq_num IN NUMBER,
470 x_job_schedule_dsp OUT NOCOPY VARCHAR2,
471 x_op_seq_num_dsp OUT NOCOPY VARCHAR2,
472 x_department_code OUT NOCOPY VARCHAR2);
473 /*==========================================================================*/
474 PROCEDURE WIP_LINE_INFO ( x_wip_line_id IN NUMBER,
475 x_org_id IN NUMBER,
476 x_wip_line_dsp OUT NOCOPY VARCHAR2);
477
478 /*===========================================================================
479 PROCEDURE NAME: DERIVE_SHIPMENT_INFO
480
481 DESCRIPTION: Procedure derives the shipment_header_id or shipment_num
482 based on information provided
483
484 PARAMETERS:
485
486 DESIGN REFERENCES:
487
488 ALGORITHM:
489
490 NOTES:
491
492 OPEN ISSUES:
493
494 CLOSED ISSUES:
495
496 CHANGE HISTORY: Raj Bhakta 07/09/97 Created
497 ===========================================================================*/
498
499 PROCEDURE DERIVE_SHIPMENT_INFO (
500 X_header_record IN OUT NOCOPY RCV_SHIPMENT_HEADER_SV.HeaderRecType);
501
502 /*===========================================================================
503 PROCEDURE NAME: DEFAULT_SHIPMENT_INFO
504
505 DESCRIPTION: Procedure defaults in information about the shipment record
506 based on information provided
507
508 PARAMETERS:
509
510 DESIGN REFERENCES:
511
512 ALGORITHM:
513
514 NOTES:
515
516 OPEN ISSUES:
517
518 CLOSED ISSUES:
519
520 CHANGE HISTORY: Raj Bhakta 07/09/97 Created
521 ===========================================================================*/
522
523 PROCEDURE DEFAULT_SHIPMENT_INFO (
524 X_header_record IN OUT NOCOPY RCV_SHIPMENT_HEADER_SV.HeaderRecType);
525
526 /*===========================================================================
527 PROCEDURE NAME: VALIDATE_SHIPMENT_NUMBER
528
529 DESCRIPTION: Procedure validates the shipment record and returns
530 error status and error message based on pre-defined
531 business rules.
532
533 PARAMETERS:
534
535 DESIGN REFERENCES:
536
537 ALGORITHM:
538
539 NOTES:
540
541 OPEN ISSUES:
542
543 CLOSED ISSUES:
544
545 CHANGE HISTORY: Raj Bhakta 10/30/96 Created
546 ===========================================================================*/
547
548 PROCEDURE VALIDATE_SHIPMENT_NUMBER (
549 X_header_record IN OUT NOCOPY RCV_SHIPMENT_HEADER_SV.HeaderRecType);
550
551 END RCV_CORE_S;