1 PACKAGE gme_reservations_pvt AUTHID CURRENT_USER AS
2 /* $Header: GMEVRSVS.pls 120.7.12020000.2 2012/07/26 15:46:46 gmurator ship $ */
3 TYPE g_msca_resvns IS REF CURSOR;
4
5 PROCEDURE get_reservations_msca (
6 p_organization_id IN NUMBER
7 ,p_batch_id IN NUMBER
8 ,p_material_detail_id IN NUMBER
9 ,p_subinventory_code IN VARCHAR2
10 ,p_locator_id IN NUMBER
11 ,p_lot_number IN VARCHAR2
12 ,x_return_status OUT NOCOPY VARCHAR2
13 ,x_error_msg OUT NOCOPY VARCHAR2
14 ,x_rsrv_cursor OUT NOCOPY g_msca_resvns);
15
16 PROCEDURE create_batch_reservations (
17 p_batch_id IN NUMBER
18 ,p_timefence IN NUMBER DEFAULT 1000
19 ,x_return_status OUT NOCOPY VARCHAR2);
20
21 -- Bug 6437252
22 -- Added lpn_id parameter.
23 PROCEDURE create_material_reservation (
24 p_matl_dtl_rec IN gme_material_details%ROWTYPE
25 ,p_resv_qty IN NUMBER DEFAULT NULL
26 ,p_sec_resv_qty IN NUMBER DEFAULT NULL
27 ,p_resv_um IN VARCHAR2 DEFAULT NULL
28 ,p_subinventory IN VARCHAR2 DEFAULT NULL
29 ,p_locator_id IN NUMBER DEFAULT NULL
30 ,p_lpn_id IN NUMBER DEFAULT NULL
31 ,p_lot_number IN VARCHAR2 DEFAULT NULL
32 ,x_return_status OUT NOCOPY VARCHAR2);
33
34 PROCEDURE get_material_reservations (
35 p_organization_id IN NUMBER
36 ,p_batch_id IN NUMBER
37 ,p_material_detail_id IN NUMBER
38 ,p_dispense_ind IN VARCHAR2 DEFAULT 'N'
39 ,x_return_status OUT NOCOPY VARCHAR2
40 ,x_reservations_tbl OUT NOCOPY gme_common_pvt.reservations_tab);
41
42 /* Procedure Added in Bug No.9924437 */
43 PROCEDURE get_material_res (
44 p_organization_id IN NUMBER
45 ,p_batch_id IN NUMBER
46 ,p_material_detail_id IN NUMBER
47 ,p_dispense_ind IN VARCHAR2 DEFAULT 'N'
48 ,x_return_status OUT NOCOPY VARCHAR2
49 ,x_reservations_tbl OUT NOCOPY gme_common_pvt.reservations_tab);
50
51 FUNCTION reservation_fully_specified (
52 p_reservation_rec IN mtl_reservations%ROWTYPE
53 ,p_item_location_control IN NUMBER
54 ,p_item_restrict_locators IN NUMBER)
55 RETURN NUMBER;
56
57 PROCEDURE convert_partial_to_dlr (
58 p_reservation_rec IN mtl_reservations%ROWTYPE
59 ,p_material_dtl_rec IN gme_material_details%ROWTYPE
60 ,p_item_rec IN mtl_system_items%ROWTYPE
61 ,p_qty_check IN VARCHAR2 := fnd_api.g_false
62 ,x_reservation_rec OUT NOCOPY mtl_reservations%ROWTYPE
63 ,x_return_status OUT NOCOPY VARCHAR2);
64
65 PROCEDURE delete_batch_reservations (
66 p_organization_id IN NUMBER
67 ,p_batch_id IN NUMBER
68 ,x_return_status OUT NOCOPY VARCHAR2);
69
70 PROCEDURE delete_material_reservations (
71 p_organization_id IN NUMBER
72 ,p_batch_id IN NUMBER
73 ,p_material_detail_id IN NUMBER
74 ,x_return_status OUT NOCOPY VARCHAR2);
75
76 PROCEDURE delete_reservation (
77 p_reservation_id IN NUMBER
78 ,x_return_status OUT NOCOPY VARCHAR2);
79
80 PROCEDURE get_reservation_dtl_qty (
81 p_reservation_rec IN mtl_reservations%ROWTYPE
82 ,p_uom_code IN VARCHAR2
83 ,x_qty OUT NOCOPY NUMBER
84 ,x_return_status OUT NOCOPY VARCHAR2);
85
86 -- Bug 12613813 - add p_locator_only parameter so picking can consider locator also.
87 PROCEDURE get_reserved_qty (
88 p_mtl_dtl_rec IN gme_material_details%ROWTYPE
89 ,p_supply_sub_only IN VARCHAR2 DEFAULT 'F'
90 ,p_locator_only IN VARCHAR2 DEFAULT 'F'
91 ,x_reserved_qty OUT NOCOPY NUMBER
92 ,x_return_status OUT NOCOPY VARCHAR2);
93
94 PROCEDURE relieve_reservation (
95 p_reservation_id IN NUMBER
96 ,p_prim_relieve_qty IN NUMBER
97 ,x_return_status OUT NOCOPY VARCHAR2);
98
99 PROCEDURE update_reservation (
100 p_reservation_id IN NUMBER
101 ,p_revision IN VARCHAR2 DEFAULT NULL
102 ,p_subinventory IN VARCHAR2 DEFAULT NULL
103 ,p_locator_id IN NUMBER DEFAULT NULL
104 ,p_lot_number IN VARCHAR2 DEFAULT NULL
105 ,p_new_qty IN NUMBER DEFAULT NULL
106 ,p_new_sec_qty IN NUMBER DEFAULT NULL
107 ,p_new_uom IN VARCHAR2 DEFAULT NULL
108 ,p_new_date IN DATE DEFAULT NULL
109 ,x_return_status OUT NOCOPY VARCHAR2);
110
111 PROCEDURE query_reservation (
112 p_reservation_id IN NUMBER
113 ,x_reservation_rec OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type
114 ,x_return_status OUT NOCOPY VARCHAR2);
115
116 FUNCTION pending_reservations_exist (
117 p_organization_id IN NUMBER
118 ,p_batch_id IN NUMBER
119 ,p_material_detail_id IN NUMBER)
120 RETURN BOOLEAN;
121
122 -- Bug 13532998 - add p_trans_date parameter.
123 PROCEDURE convert_dtl_reservation (
124 p_reservation_rec IN mtl_reservations%ROWTYPE
125 ,p_material_details_rec IN gme_material_details%ROWTYPE
126 ,p_qty_convert IN NUMBER := NULL
127 ,p_trans_date IN DATE DEFAULT NULL
128 ,x_message_count OUT NOCOPY NUMBER
129 ,x_message_list OUT NOCOPY VARCHAR2
130 ,x_return_status OUT NOCOPY VARCHAR2);
131
132 PROCEDURE auto_detail_line (
133 p_material_details_rec IN gme_material_details%ROWTYPE
134 ,x_return_status OUT NOCOPY VARCHAR2);
135
136 PROCEDURE auto_detail_batch(
137 p_batch_rec IN GME_BATCH_HEADER%ROWTYPE,
138 p_timefence IN NUMBER DEFAULT 100000,
139 x_return_status OUT NOCOPY VARCHAR2);
140
141 --Bug#4604943 created new procedure to take care of validations
142 PROCEDURE validate_mtl_for_reservation(
143 p_material_detail_rec IN GME_MATERIAL_DETAILS%ROWTYPE,
144 x_return_status OUT NOCOPY VARCHAR2);
145 END gme_reservations_pvt;