[Home] [Help]
PACKAGE: APPS.WIP_SO_RESERVATIONS
Source
1 PACKAGE WIP_SO_RESERVATIONS AS
2 /* $Header: wipsorvs.pls 120.2.12000000.2 2007/02/23 22:27:33 kboonyap ship $ */
3
4
5 g_package_name CONSTANT VARCHAR2(30) := 'WIP_SO_RESERVATIONS';
6 g_need_to_rollback_exception EXCEPTION;
7
8 /* ER 4378835: Increased length of lot_number from 30 to 80 to support OPM Lot-model changes */
9 TYPE transaction_temp_rec_type is RECORD (
10 demand_source_header_id NUMBER,
11 demand_source_line_id VARCHAR2(30),
12 organization_id NUMBER,
13 inventory_item_id NUMBER,
14 revision VARCHAR2(3),
15 subinventory_code VARCHAR2(10),
16 locator_id NUMBER,
17 lot_control_code NUMBER,
18 lot_number VARCHAR2(80),
19 wip_entity_id NUMBER,
20 transaction_uom VARCHAR2(3),
21 transaction_date DATE,
22 primary_quantity NUMBER,
23 transaction_quantity NUMBER,
24 demand_class_code VARCHAR2(30),
25 lot_expiration_date DATE,
26 transaction_temp_id NUMBER,
27 lpn_id NUMBER
28 );
29
30 TYPE transaction_temp_tbl_type is TABLE OF transaction_temp_rec_type
31 INDEX BY BINARY_INTEGER;
32
33 TYPE demand_temp_rec_type is RECORD (
34 source_header_id NUMBER,
35 source_line_id NUMBER,
36 source_delivery NUMBER,
37 primary_quantity NUMBER,
38 requirement_date VARCHAR2(20),
39 uom_code VARCHAR2(3),
40 subinventory_code VARCHAR(10),
41 conversion_rate NUMBER
42 );
43
44 TYPE demand_temp_tbl_type is TABLE of demand_temp_rec_type
45 INDEX BY BINARY_INTEGER;
46
47 mmtt_count NUMBER := 0;
48 reservation_count NUMBER := 0;
49
50 TYPE move_transaction_intf_rec_type is RECORD (
51 wip_entity_id NUMBER,
52 transaction_id NUMBER,
53 transaction_type NUMBER,
54 organization_id NUMBER,
55 primary_item_id NUMBER,
56 fm_operation_seq_num NUMBER,
57 fm_intraoperation_step_type NUMBER,
58 to_operation_seq_num NUMBER,
59 to_intraoperation_step_type NUMBER,
60 primary_quantity NUMBER,
61 primary_uom VARCHAR2(3),
62 entity_type NUMBER,
63 repetitive_schedule_id NUMBER,
64 transaction_date DATE
65 );
66
67 TYPE move_transaction_intf_tbl_type is TABLE OF move_transaction_intf_rec_type
68 INDEX BY BINARY_INTEGER;
69
70 PROCEDURE get_transaction_lines (
71 p_transaction_header_id IN NUMBER,
72 p_transaction_type IN NUMBER,
73 p_txn_temp_id IN NUMBER := NULL,
74 x_return_status OUT NOCOPY VARCHAR2,
75 x_transaction_tbl OUT NOCOPY transaction_temp_tbl_type);
76
77 --for use in lpn completions
78 PROCEDURE get_transaction_lines (
79 p_header_id IN NUMBER,
80 p_transaction_type IN NUMBER,
81 p_transaction_action_id IN NUMBER,
82 p_primary_quantity IN NUMBER, --lpn passed to inv's transfer_reservation
83 p_lpn_id IN NUMBER, --override quantity in table.
84 p_lot_number IN VARCHAR2,
85 x_return_status OUT NOCOPY VARCHAR2,
86 x_transaction_tbl OUT NOCOPY transaction_temp_tbl_type);
87
88 FUNCTION validate_txn_line_against_rsv(
89 p_transaction_rec IN transaction_temp_rec_type,
90 p_reservation_rec IN inv_reservation_global.mtl_reservation_rec_type,
91 p_transaction_type IN NUMBER,
92 x_return_status OUT NOCOPY VARCHAR2,
93 x_query_reservation OUT NOCOPY VARCHAR2)
94 RETURN BOOLEAN;
95
96 PROCEDURE transfer_reservation(
97 p_transaction_rec IN transaction_temp_rec_type,
98 p_reservation_rec IN inv_reservation_global.mtl_reservation_rec_type,
99 p_transaction_type IN NUMBER,
100 x_return_status OUT NOCOPY VARCHAR2);
101
102 /*Bug 5676680: Added one extra parameter p_transaction_temp_id*/
103 PROCEDURE complete_flow_sched_to_so (
104 p_transaction_header_id IN NUMBER,
105 p_transaction_temp_id IN NUMBER DEFAULT NULL,
106 x_return_status OUT NOCOPY VARCHAR2,
107 x_msg_count OUT NOCOPY NUMBER,
108 x_msg_data OUT NOCOPY VARCHAR2);
109
110
111 --for use in lpn completions
112 PROCEDURE complete_flow_sched_to_so (p_header_id IN NUMBER,
113 p_lpn_id IN NUMBER,
114 p_primary_quantity IN NUMBER, --lpn passed to inv's transfer_reservation
115 p_lot_number IN VARCHAR2,
116 x_return_status OUT NOCOPY VARCHAR2,
117 x_msg_count OUT NOCOPY NUMBER,
118 x_msg_data OUT NOCOPY VARCHAR2);
119 PROCEDURE allocate_completion_to_so (
120 p_organization_id IN NUMBER,
121 p_wip_entity_id IN NUMBER,
122 p_inventory_item_id IN NUMBER,
123 p_transaction_header_id IN NUMBER,
124 p_table_type IN VARCHAR2,--either 'MMTT' or 'WLC'
125 p_primary_quantity IN NUMBER, --lpn passed to inv's transfer_reservation
126 p_lpn_id IN NUMBER, --override quantity in table.
127 p_lot_number IN VARCHAR2,
128 p_txn_temp_id IN NUMBER := NULL,
129 x_return_status OUT NOCOPY VARCHAR2,
130 x_msg_count OUT NOCOPY NUMBER,
131 x_msg_data OUT NOCOPY VARCHAR2);
132
133 --calls above allocate_completion_to_so with table_type = 'MMTT'
134 PROCEDURE allocate_completion_to_so (
135 p_organization_id IN NUMBER,
136 p_wip_entity_id IN NUMBER,
137 p_inventory_item_id IN NUMBER,
138 p_transaction_header_id IN NUMBER,
139 p_txn_temp_id IN NUMBER := NULL,
140 x_return_status OUT NOCOPY VARCHAR2,
141 x_msg_count OUT NOCOPY NUMBER,
142 x_msg_data OUT NOCOPY VARCHAR2);
143
144
145 PROCEDURE return_reservation_to_wip (
146 p_organization_id IN NUMBER,
147 p_wip_entity_id IN NUMBER,
148 p_inventory_item_id IN NUMBER,
149 p_transaction_header_id IN NUMBER,
150 p_txn_temp_id IN NUMBER := NULL,
151 x_return_status OUT NOCOPY VARCHAR2,
152 x_msg_count OUT NOCOPY NUMBER,
153 x_msg_data OUT NOCOPY VARCHAR2);
154
155
156 PROCEDURE split_order_line(
157 p_old_demand_source_line_id IN NUMBER,
158 p_new_demand_source_line_id IN NUMBER,
159 x_return_status OUT NOCOPY VARCHAR2,
160 x_msg_count OUT NOCOPY NUMBER,
161 x_msg_data OUT NOCOPY VARCHAR2);
162
163
164 PROCEDURE make_callback_to_workflow (
165 p_organization_id IN NUMBER,
166 p_inventory_item_id IN NUMBER,
167 p_order_line_id IN NUMBER,
168 p_type IN VARCHAR2,
169 x_return_status OUT NOCOPY VARCHAR2,
170 x_msg_count OUT NOCOPY NUMBER,
171 x_msg_data OUT NOCOPY VARCHAR2);
172
173
174 PROCEDURE respond_to_change_order (
175 p_org_id IN NUMBER,
176 p_header_id IN NUMBER,
177 p_line_id IN NUMBER,
178 x_status OUT NOCOPY VARCHAR2,
179 x_msg_count OUT NOCOPY NUMBER,
180 x_msg_data OUT NOCOPY VARCHAR2);
181
182 PROCEDURE get_move_transaction_lines (
183 p_group_id IN NUMBER,
184 p_wip_entity_id IN NUMBER,
185 x_return_status OUT NOCOPY VARCHAR2,
186 x_move_transaction_tbl OUT NOCOPY move_transaction_intf_tbl_type);
187
188 PROCEDURE scrap_txn_relieve_rsv ( p_group_id IN NUMBER,
189 x_return_status OUT NOCOPY VARCHAR2,
190 x_msg_count OUT NOCOPY NUMBER,
191 x_msg_data OUT NOCOPY VARCHAR2);
192
193 PROCEDURE Relieve_wip_reservation(
194 p_wip_entity_id IN Number,
195 p_organization_id IN Number,
196 p_inventory_item_id IN Number,
197 p_primary_quantity IN Number,
198 x_return_status OUT NOCOPY VARCHAR2,
199 x_msg_count OUT NOCOPY NUMBER,
200 x_msg_data OUT NOCOPY VARCHAR2);
201
202 -- Fixed bug 5471890. Need to create PL/SQL wrapper when calling inventory
203 -- reservation API since some environment failed to compile if we try to
204 -- reference PL/SQL object from form directly.
205 PROCEDURE update_row(p_item_revision IN VARCHAR2,
206 p_reservation_id IN NUMBER,
207 p_requirement_date IN DATE,
208 p_demand_source_header_id IN NUMBER,
209 p_demand_source_line_id IN NUMBER,
210 p_primary_quantity IN NUMBER,
211 p_wip_entity_id IN NUMBER,
212 x_return_status OUT NOCOPY VARCHAR2);
213
214 PROCEDURE lock_row(p_reservation_id IN NUMBER,
215 x_reservation_id OUT NOCOPY NUMBER,
216 x_supply_source_header_id OUT NOCOPY NUMBER,
217 x_organization_id OUT NOCOPY NUMBER,
218 x_demand_source_header_id OUT NOCOPY NUMBER,
219 x_primary_reservation_quantity OUT NOCOPY NUMBER,
220 x_demand_source_line_id OUT NOCOPY NUMBER,
221 x_size OUT NOCOPY NUMBER,
222 x_return_status OUT NOCOPY VARCHAR2);
223
224 -- End fix of bug 5471890.
225
226
227 END WIP_SO_RESERVATIONS;