1 PACKAGE PO_REQ_LINES_SV as
2 /* $Header: POXRQL1S.pls 120.0 2005/06/07 16:21:56 appldev noship $ */
3 /*===========================================================================
4 PACKAGE NAME: po_req_lines_sv
5
6 DESCRIPTION: Contains all server side procedures that access
7 requisition lines entity.
8
9 CLIENT/SERVER: Server
10
11 LIBRARY NAME None
12
13 OWNER: RMULPURY
14
15 PROCEDURE NAMES: val_create_dist
16 create_distribution
17 check_funds
18 delete_line
19 delete_children
20 val_reqs_po_shipment
21 val_reqs_oe_shipment
22 val_reqs_qty_delivered
23 update_reqs_lines_incomplete
24 update_reqs_lines_status
25 remove_req_from_po
26 get_destination_info
27 get_default_destination_info
28 get_default_source_type_info
29 get_vendor_sourcing_info
30 get_dest_type
31 val_dest_details
32 val_destination_type
33
34
35 ===========================================================================*/
36
37 /*===========================================================================
38 PROCEDURE NAME: lock_row_for_buyer_update
39
40 DESCRIPTION: Locks row in table for updating suggested_buyer_id
41
42 PARAMETERS: X_rowid IN VARCHAR2
43
44 DESIGN REFERENCES: POXBWMBW.dd
45
46 ALGORITHM:
47
48 NOTES:
49
50 OPEN ISSUES:
51
52 CLOSED ISSUES:
53
54 CHANGE HISTORY: CMOK 8/8 Created
55 ===========================================================================*/
56
57 PROCEDURE lock_row_for_buyer_update (x_rowid IN VARCHAR2);
58
59 /*===========================================================================
60 PROCEDURE NAME: delete_line
61
62 DESCRIPTION: Cover to delete the children and the
63 requisition line.
64
65 PARAMETERS: X_line_id IN NUMBER
66 X_mode IN VARCHAR2
67
68 DESIGN REFERENCES: MODIFY_REQS.dd
69 POXRQERQ.doc
70
71 ALGORITHM:
72
73 NOTES:
74
75 OPEN ISSUES:
76
77 CLOSED ISSUES:
78
79 CHANGE HISTORY:
80 ===========================================================================*/
81
82 PROCEDURE delete_line(X_line_id IN NUMBER,
83 X_mode IN VARCHAR2,
84 X_transferred_to_oe_flag OUT NOCOPY VARCHAR2);
85
86 /*===========================================================================
87 PROCEDURE NAME: delete_children
88
89 DESCRIPTION: Deletes all the children associated
90 with a requisition line which includes:
91
92 - distributions.
93 - remove req line supply (for 'MODIFY')
94 - attachments.
95
96 PARAMETERS: X_line_id IN NUMBER
97 X_mode IN VARCHAR2
98
99 DESIGN REFERENCES: MODIFY_REQS.dd
100 POXRQERQ.doc
101
102 ALGORITHM:
103
104 NOTES:
105
106 OPEN ISSUES:
107
108 CLOSED ISSUES:
109
110 CHANGE HISTORY:
111 ===========================================================================*/
112
113 PROCEDURE delete_children(X_line_id IN NUMBER,
114 X_mode IN VARCHAR2);
115
116
117
118 /*===========================================================================
119 FUNCTION NAME: val_reqs_po_shipment
120
121 DESCRIPTION: Verify if requisition lines has any open PO shipments.
122
123 If it is, display a message to the user.
124
125 PARAMETERS: X_req_header_id IN NUMBER,
126 X_req_line_id IN NUMBER
127
128 DESIGN REFERENCES: ../POXDOCON.dd
129
130 ALGORITHM: Header: verify if requisition header has requisition
131 lines that are associated with any open PO
132 shipments which are not cancelled or
133 finally closed.
134 Line: verify if requisiton line associated with
135 PO shipment which is not cancelled or
136 finally closed.
137
138 NOTES:
139
140 OPEN ISSUES:
141
142 CLOSED ISSUES:
143
144 CHANGE HISTORY: WLAU 5/12 Created
145 ===========================================================================*/
146 FUNCTION val_reqs_po_shipment
147 (X_req_header_id IN NUMBER,
148 X_req_line_id IN NUMBER) RETURN BOOLEAN;
149
150
151
152 /*===========================================================================
153 FUNCTION NAME: val_reqs_oe_shipment
154
155 DESCRIPTION: If Order Entry is installed, verify if internal
156 requisition has open sales order lines.
157
158 If it is, display a message to the user.
159
160 PARAMETERS: X_req_header_id IN NUMBER,
161 X_req_line_id IN NUMBER
162
163 DESIGN REFERENCES: ../POXDOCON.dd
164
165 ALGORITHM: Header: the requisition header has requisition lines
166 associated with open internal sales
167 order lines.
168
169 Line: the line that is on an open internal
170 sales order line.
171
172 NOTES:
173
174 OPEN ISSUES:
175
176 CLOSED ISSUES:
177
178 CHANGE HISTORY: WLAU 5/12 Created
179 ===========================================================================*/
180 FUNCTION val_reqs_oe_shipment
181 (X_req_header_id IN NUMBER,
182 X_req_line_id IN NUMBER) RETURN BOOLEAN;
183
184
185
186 /*===========================================================================
187 FUNCTION NAME: val_reqs_qty_delivered
188
189 DESCRIPTION: If encumbrance is ON, this procedure is called to
190 verify those requisition lines which are sourced from
191 inventory, must be received and delivered.
192
193 If it is not, display a message to the user.
194
195 PARAMETERS: X_req_header_id IN NUMBER,
196 X_req_line_id IN NUMBER
197
198 DESIGN REFERENCES: ../POXDOCON.dd
199
200 ALGORITHM: Header: Verify if any lines that are sourced from
201 inventory, the quantity must be received
202 and delivered.
203 Line: Verify if a requisition line that is sourced
204 from inventory, the quantity must be
205 received and delivered.
206
207 NOTES:
208
209 OPEN ISSUES:
210
211 CLOSED ISSUES:
212
213 CHANGE HISTORY: WLAU 5/12 Created
214 ===========================================================================*/
215 FUNCTION val_reqs_qty_delivered
216 (X_req_header_id IN NUMBER,
217 X_req_line_id IN NUMBER) RETURN BOOLEAN;
218
219
220 /*===========================================================================
221 PROCEDURE NAME: update_reqs_lines_incomplete
222
223 DESCRIPTION: If control action is 'CANCEL' and encumbrance is ON,
224 invoke this procedure to udpate req. lines cancel
225 flag to incomplete 'I'. This is to indicate that
226 cancel process is partially completed before
227 the unencumbrance user exit is called.
228
229 This update is needed because the unencumbrance user
230 exit will call the GL procedure which commits the
231 update before returning to the caller.
232 bug 1265026 : added oe_install_flaf as a parameter
233
234 PARAMETERS: X_req_header_id IN NUMBER,
235 X_req_line_id IN NUMBER,
236 X_req_control_error_rc IN OUT VARCHAR2
237
238 DESIGN REFERENCES: ../POXDOCON.dd
239
240 ALGORITHM: Header: Update the requisition lines cancel flag to 'I'.
241 Line: Update the line cancel falg to 'I'.
242
243 NOTES:
244
245 OPEN ISSUES:
246
247 CLOSED ISSUES:
248
249 CHANGE HISTORY: WLAU 5/12 Created
250 ===========================================================================*/
251 PROCEDURE update_reqs_lines_incomplete
252 (X_req_header_id IN NUMBER,
253 X_req_line_id IN NUMBER,
254 X_req_control_error_rc IN OUT NOCOPY VARCHAR2,
255 X_oe_installed_flag IN VARCHAR2);
256
257
258
259 /*===========================================================================
260 PROCEDURE NAME: update_reqs_lines_status
261
262 DESCRIPTION: Updates requisition line status fields.
263
264
265 PARAMETERS: X_req_header_id IN NUMBER,
266 X_req_line_id IN NUMBER,
267 X_req_control_action IN VARCHAR2,
268 X_req_control_reason IN VARCHAR2,
269 X_req_action_date IN DATE,
270 X_oe_installed_flag IN VARCHAR2,
271 X_req_control_error_rc IN OUT VARCHAR2
272
273 DESIGN REFERENCES: ../POXDOCON.dd
274
275 ALGORITHM: 1. If control action is 'CANCEL',
276 update requisition line's cancel_flag, cancel_date,
277 and cancel_reason.
278
279 2. If control action is 'FINALLY CLOSE',
280 update requisition line's closed_code, closed_date,
281 and closed_reason.
282 NOTES:
283
284 OPEN ISSUES:
285
286 CLOSED ISSUES:
287
288 CHANGE HISTORY: WLAU 5/12 Created
289 ===========================================================================*/
290 PROCEDURE update_reqs_lines_status
291 (X_req_header_id IN NUMBER,
292 X_req_line_id IN NUMBER,
293 X_req_control_action IN VARCHAR2,
294 X_req_control_reason IN VARCHAR2,
295 X_req_action_date IN DATE,
296 X_oe_installed_flag IN VARCHAR2,
297 X_req_control_error_rc IN OUT NOCOPY VARCHAR2);
298
299
300
301
302
303 /*===========================================================================
304 PROCEDURE NAME: remove_req_from_po
305
306 DESCRIPTION: Routine to update the requisition lines
307 entity that are associated with a Purchase
308 Order or Release to have a null line_location_id
309 for the requisition lines which were placed on
310 a Purchase Order or Release.
311
312 PARAMETERS: X_entity_id IN NUMBER
313 X_entity IN VARCHAR2
314
315 DESIGN REFERENCES: POXPOMPO.doc
316 POXPOREL.doc
317
318 ALGORITHM:
319
320 NOTES: X_entity values may be:
321
322 - 'PURCHASE ORDER'
323 - 'RELEASE'
324
325 OPEN ISSUES:
326
327 CLOSED ISSUES:
328
329 CHANGE HISTORY:
330 ===========================================================================*/
331
332 PROCEDURE remove_req_from_po(X_entity_id IN NUMBER,
333 X_entity IN VARCHAR2);
334
335
336
337 /*===========================================================================
338 PROCEDURE NAME: val_dest_details
339
340 DESCRIPTION: Cover for the validation of the following
341 destination fields:
342
343 - Deliver To Organization
344 - Deliver To Location
345 - Destination Subinventory
346
347 This procedure copies null
348 values into the invalid columns.
349
350 PARAMETERS: x_dest_org_id IN OUT NUMBER
351 x_item_id IN NUMBER
352 x_item_rev IN VARCHAR2
353 x_location_id IN OUT NUMBER
354 x_dest_sub IN OUT VARCHAR2
355 x_dest_type IN VARCHAR2
356 x_code IN VARCHAR2
357 x_sob_id IN NUMBER
358
359 Valid codes: 'ORG' - Validate org,loc,sub
360 'LOC' - Validate loc,sub
361 'SUB' - Validate sub.
362
363
364 DESIGN REFERENCES: POXRQERQ.doc
365
366 ALGORITHM:
367
368 NOTES:
369
370 OPEN ISSUES:
371
372 CLOSED ISSUES:
373
374 CHANGE HISTORY:
375 ===========================================================================*/
376
377 PROCEDURE val_dest_details (x_dest_org_id IN OUT NOCOPY NUMBER,
378 x_item_id IN NUMBER,
382 x_dest_type IN VARCHAR2,
379 x_item_rev IN VARCHAR,
380 x_location_id IN OUT NOCOPY NUMBER,
381 x_dest_sub IN OUT NOCOPY VARCHAR2,
383 x_val_code IN VARCHAR2,
384 x_sob_id IN NUMBER);
385
386
387
388 /*===========================================================================
389 FUNCTION NAME: val_src_type
390
391 DESCRIPTION: Validate the source type.
392
393 This function returns FALSE
394 when the specified source type
395 is INVALID.
396
397 PARAMETERS: x_src_type IN VARCHAR2
398 x_item_id IN NUMBER
399 x_internal_orderable IN VARCHAR2
400 x_stock_enabled_flag IN VARCHAR2
401 x_purchasable IN VARCHAR2
402 x_customer_id IN NUMBER
403 x_outside_op_line_type IN VARCHAR2
404
405
406 DESIGN REFERENCES: POXRQERQ.doc
407
408 ALGORITHM:
409
410 NOTES:
411
412 OPEN ISSUES: DEBUG: This can actually be moved
413 to the client since we obtain all the
414 input values to this function on the
415 w-v-i of the item and destination org
416 fields.
417
418 CLOSED ISSUES:
419
420 CHANGE HISTORY:
421 ===========================================================================*/
422
423 FUNCTION val_src_type ( x_src_type IN VARCHAR2,
424 x_item_id IN NUMBER,
425 x_internal_orderable IN VARCHAR2,
426 x_stock_enabled_flag IN VARCHAR2,
427 x_purchasable IN VARCHAR2,
428 x_customer_id IN NUMBER,
429 x_outside_op_line_type IN VARCHAR2)
430 RETURN BOOLEAN;
431
432
433
434 /*===========================================================================
435 PROCEDURE NAME: update_transferred_to_oe_flag
436
437 DESCRIPTION: Check if there is a line with
438 a source type of 'INVENTORY' and
439 modify the transferred_to_oe_flag
440 on the requisition headers table
441 based on the following rules.
442
443 - If there are no 'INVENTORY' sourced
444 lines then set the transferred_to_oe_flag
445 to NULL
446 - If there is at least one 'INVENTORY'
447 sourced line then set the transferred_to_oe_flag
448 to 'N'.
449
450 PARAMETERS: X_req_hdr_id IN NUMBER
451 X_transferred_to_oe_flag OUT VARCHAR2
452
453 DESIGN REFERENCES: POXRQERQ.doc
454
455 ALGORITHM:
456
457 NOTES:
458
459 OPEN ISSUES:
460
461 CLOSED ISSUES:
462
463 CHANGE HISTORY: RMULPURY 05/11 Created
464 ===========================================================================*/
465
466 PROCEDURE update_transferred_to_oe_flag(X_req_hdr_id IN NUMBER,
467 X_transferred_to_oe_flag OUT NOCOPY VARCHAR2);
468
469
470
471 /*===========================================================================
472 FUNCTION NAME: val_oe_shipment_in_proc
473
474 DESCRIPTION: If Order Entry is installed, verify if internal
475 requisition has open sales order lines and the shipped
476 sales order lines are not in process
477
478 If it is, display a message to the user.
479
480 PARAMETERS: X_req_header_id IN NUMBER,
481 X_req_line_id IN NUMBER
482
483 DESIGN REFERENCES:
484
485 ALGORITHM: Header: the requisition header has requisition lines
486 associated with open internal sales
487 order lines.
488
489 Line: the line that is on an open internal
490 sales order line.
491
492 NOTES:
493
494 OPEN ISSUES:
495 CLOSED ISSUES:
496
497 CHANGE HISTORY: kagarwal 8/2002 Created
498 ===========================================================================*/
499 FUNCTION val_oe_shipment_in_proc
500 (X_req_header_id IN NUMBER,
501 X_req_line_id IN NUMBER) RETURN BOOLEAN;
502
503
504 /*===========================================================================
505 FUNCTION NAME: val_reqs_qty_received
506
507 DESCRIPTION: If Order Entry is installed, verify if internal
508 requisition lines which are sourced from inventory,
509 have been received or not. For this we will check if
510 the 'SHIPMENT' supply exists for the requisition lines.
511
512 If 'SHIPMENT' supply exists return FALSE else TRUE
513
514 PARAMETERS: X_req_header_id IN NUMBER,
515 X_req_line_id IN NUMBER
516
517 DESIGN REFERENCES: ../POXDOCON.dd
518
519 ALGORITHM: Header: Verify if any lines that are sourced from
520 inventory, that the 'SHIPMENT' supply exist.
521 Line: Verify if a requisition line that is sourced
522 from inventory, that the 'SHIPMENT' supply
523 exist.
524
525 NOTES:
526
527 OPEN ISSUES:
528
529 CLOSED ISSUES:
530
531 CHANGE HISTORY: Bug#2607180 (kagarwal) 10/2002 Created
532 ===========================================================================*/
533 FUNCTION val_reqs_qty_received
534 (X_req_header_id IN NUMBER,
535 X_req_line_id IN NUMBER) RETURN BOOLEAN;
536
537 /*===========================================================================
538 PROCEDURE NAME: update_reqs_in_pool_flag
539
540 DESCRIPTION: Checks all the properties that determine a
541 requisition line's in-pool status and updates
542 that line's REQS_IN_POOL_FLAG status
543 accordingly.
544
545 PARAMETERS: x_req_line_id IN NUMBER,
546 x_req_header_id IN NUMBER,
547 x_return_status OUT NOCOPY VARCHAR2
548
549 DESIGN REFERENCES: ReqsInPoolTD.doc
550
551 ALGORITHM: For all applicable requisition lines,
552 set the requisition line's in-pool status to
553 in-pool ('Y') if the requisition line has not
554 been cancelled, not finally closed, not
555 attached to a PO or release, not modified since
556 approval, is vendor-sourced (not part of an
557 internal req), not part of an active sourcing
558 negotiation, and is on an approved requisition.
559
560 NOTES: If both x_req_line_id and x_req_header_id
561 are NULL, no updates are made and the procedure
562 returns with success. If a sql error is encountered,
563 calls po_message_s.sql_error to notify user and
564 returns with status FND_API.G_RET_STS_UNEXP_ERROR.
565 OPEN ISSUES:
566
567 CLOSED ISSUES:
568
569 CHANGE HISTORY: Bug#4075357 12/22/2004 created
570 ==========================================================================*/
574 x_return_status OUT NOCOPY VARCHAR2
571 PROCEDURE update_reqs_in_pool_flag
572 ( x_req_line_id IN NUMBER ,
573 x_req_header_id IN NUMBER ,
575 );
576
577 END po_req_lines_sv;
[Home] [Help]