1 PACKAGE inv_rcv_std_deliver_apis AS
2 /* $Header: INVSTDDS.pls 120.2 2006/03/07 14:02:39 gayu noship $*/
3
4 /*******************************************************
5 * Name: create_mobile_rcvtxn_interface_rec
6 *
7 * Description:
8 *
9 *
10 * Flow:
11 *
12 *
13 *******************************************************/
14 g_po_distribution_id NUMBER := 0; -- global variable for testing only
15 g_rcvtxn_detail_index NUMBER := 1;
16 -- global variable which stores the line in input table to detail in the matching algo
17
18 -- input for matching algorithm
19 g_rcvtxn_match_table_gross inv_rcv_common_apis.cascaded_trans_tab_type;
20
21 -- this is the record type for RCV_TRANSACTION Block
22 -- which includes DB items from RCV_TRANSACTIONS_V
23 -- and NON-DB items as is in the form.
24 TYPE rcvtxn_transaction_rec_tp IS RECORD(
25 from_organization_id NUMBER
26 , to_organization_id NUMBER
27 , source_document_code VARCHAR2(30)
28 , receipt_source_code VARCHAR2(30)
29 , rcv_transaction_id NUMBER
30 , transaction_date DATE
31 , transaction_type VARCHAR2(30)
32 , primary_uom VARCHAR2(25)
33 , primary_quantity NUMBER
34 , po_header_id NUMBER
35 , po_revision_num NUMBER
36 , po_release_id NUMBER
37 , vendor_id NUMBER
38 , vendor_site_id NUMBER
39 , po_line_id NUMBER
40 , po_unit_price NUMBER
41 , category_id NUMBER
42 , item_id NUMBER
43 , serial_number_control_code NUMBER
44 , lot_control_code NUMBER
45 , item_revision VARCHAR2(3)
46 , po_line_location_id NUMBER
47 , po_distribution_id NUMBER
48 , employee_id NUMBER(9)
49 , comments VARCHAR2(240)
50 , req_header_id NUMBER
51 , req_line_id NUMBER
52 , shipment_header_id NUMBER
53 , shipment_line_id NUMBER
54 , packing_slip VARCHAR2(80)
55 , government_context VARCHAR2(30)
56 , ussgl_transaction_code VARCHAR2(30)
57 , inspection_status_code VARCHAR2(30)
58 , inspection_quality_code VARCHAR2(30)
59 , vendor_lot_num VARCHAR2(30)
60 , vendor_item_number VARCHAR2(30)
61 , substitute_unordered_code VARCHAR2(30)
62 , routing_id NUMBER
63 , routing_step_id NUMBER
64 , reason_id NUMBER
65 , currency_code VARCHAR2(30)
66 , currency_conversion_rate NUMBER
67 , currency_conversion_date DATE
68 , currency_conversion_type VARCHAR2(30)
69 , req_distribution_id NUMBER
70 , destination_type_code_hold VARCHAR2(30)
71 , location_id NUMBER
72 , deliver_to_person_id NUMBER
73 , deliver_to_location_id NUMBER
74 , subinventory VARCHAR2(10)
75 , un_number_id NUMBER
76 , hazard_class_id NUMBER
77 , creation_date DATE
78 , attribute_category VARCHAR2(30)
79 , attribute1 VARCHAR2(150)
80 , attribute2 VARCHAR2(150)
81 , attribute3 VARCHAR2(150)
82 , attribute4 VARCHAR2(150)
83 , attribute5 VARCHAR2(150)
84 , attribute6 VARCHAR2(150)
85 , attribute7 VARCHAR2(150)
86 , attribute8 VARCHAR2(150)
87 , attribute9 VARCHAR2(150)
88 , attribute10 VARCHAR2(150)
89 , attribute11 VARCHAR2(150)
90 , attribute12 VARCHAR2(150)
91 , attribute13 VARCHAR2(150)
92 , attribute14 VARCHAR2(150)
93 , attribute15 VARCHAR2(150)
94 , qa_collection_id NUMBER
95 , oe_order_header_id NUMBER
96 , oe_order_line_id NUMBER
97 , customer_id NUMBER
98 , customer_site_id NUMBER
99 -- These are now non database items
100 ,destination_type_code_pqry VARCHAR2(30)
101 , destination_type_code VARCHAR2(30)
102 , subinventory_hold VARCHAR2(30)
103 , subinventory_dsp VARCHAR2(30)
104 , destination_context_nb VARCHAR2(30)
105 , wip_entity_id NUMBER
106 , wip_line_id NUMBER
107 , wip_repetitive_schedule_id NUMBER
108 , wip_resource_seq_num NUMBER
109 , wip_operation_seq_num NUMBER
110 , bom_resource_id_nb NUMBER
111 , locator_id NUMBER
112 , subinventory_locator_control NUMBER
113 , transaction_quantity NUMBER
114 , transaction_uom VARCHAR2(30)
115 , transaction_date_nb DATE
116 , inspection_detail VARCHAR2(1)
117 , interface_transaction_id NUMBER
118 , put_away_rule_id NUMBER
119 , put_away_strategy_id NUMBER
120 , lpn_id NUMBER
121 , transfer_lpn_id NUMBER
122 , cost_group_id NUMBER
123 , mmtt_temp_id NUMBER
124 , transfer_cost_group_id NUMBER
125 , secondary_uom VARCHAR2(25) --OPM Convergence
126 , secondary_uom_code VARCHAR2(3) --OPM Convergence
127 , sec_transaction_quantity NUMBER --OPM Convergence
128 , from_subinventory_code VARCHAR2(30)
129 , from_locator_id NUMBER
130 );
131
132 TYPE rcvtxn_enter_rec_cursor_rec IS RECORD(
133 from_organization_id NUMBER
134 , to_organization_id NUMBER
135 , source_document_code VARCHAR2(30)
136 , receipt_source_code VARCHAR2(30)
137 , rcv_transaction_id NUMBER
138 , transaction_date DATE
139 , transaction_type VARCHAR2(30)
140 , primary_uom VARCHAR2(25)
141 , primary_quantity NUMBER
142 , po_header_id NUMBER
143 , po_revision_num NUMBER
144 , po_release_id NUMBER
145 , vendor_id NUMBER
146 , vendor_site_id NUMBER
147 , po_line_id NUMBER
148 , po_unit_price NUMBER
149 , category_id NUMBER
150 , item_id NUMBER
151 , serial_number_control_code NUMBER
152 , lot_control_code NUMBER
153 , item_revision VARCHAR2(3)
154 , po_line_location_id NUMBER
155 , po_distribution_id NUMBER
156 , employee_id NUMBER(9)
157 , comments VARCHAR2(240)
158 , req_header_id NUMBER
159 , req_line_id NUMBER
160 , shipment_header_id NUMBER
161 , shipment_line_id NUMBER
162 , packing_slip VARCHAR2(80)
163 , government_context VARCHAR2(30)
164 , ussgl_transaction_code VARCHAR2(30)
165 , inspection_status_code VARCHAR2(30)
166 , inspection_quality_code VARCHAR2(30)
167 , vendor_lot_num VARCHAR2(30)
168 , vendor_item_number VARCHAR2(30)
169 , substitute_unordered_code VARCHAR2(30)
170 , routing_id NUMBER
171 , routing_step_id NUMBER
172 , reason_id NUMBER
173 , currency_code VARCHAR2(30)
174 , currency_conversion_rate NUMBER
175 , currency_conversion_date DATE
176 , currency_conversion_type VARCHAR2(30)
177 , req_distribution_id NUMBER
178 , destination_type_code_hold VARCHAR2(30)
179 , final_destination_type_code VARCHAR2(30)
180 , location_id NUMBER
181 , final_deliver_to_person_id NUMBER(9)
182 , final_deliver_to_location_id NUMBER
183 , subinventory VARCHAR2(10)
184 , un_number_id NUMBER
185 , hazard_class_id NUMBER
186 , creation_date DATE
187 , attribute_category VARCHAR2(30)
188 , attribute1 VARCHAR2(150)
189 , attribute2 VARCHAR2(150)
190 , attribute3 VARCHAR2(150)
191 , attribute4 VARCHAR2(150)
192 , attribute5 VARCHAR2(150)
193 , attribute6 VARCHAR2(150)
194 , attribute7 VARCHAR2(150)
195 , attribute8 VARCHAR2(150)
196 , attribute9 VARCHAR2(150)
197 , attribute10 VARCHAR2(150)
198 , attribute11 VARCHAR2(150)
199 , attribute12 VARCHAR2(150)
200 , attribute13 VARCHAR2(150)
201 , attribute14 VARCHAR2(150)
202 , attribute15 VARCHAR2(150)
203 , qa_collection_id NUMBER
204 , oe_order_header_id NUMBER
205 , oe_order_line_id NUMBER
206 , customer_id NUMBER
207 , customer_site_id NUMBER
208 , wip_entity_id NUMBER
209 , po_operation_seq_num NUMBER
210 , po_resource_seq_num NUMBER
211 , wip_repetitive_schedule_id NUMBER
212 , wip_line_id NUMBER
213 , bom_resource_id NUMBER
214 , final_subinventory VARCHAR2(10)
215 , secondary_quantity NUMBER --OPM Convergence
216 , secondary_uom VARCHAR2(25) --OPM Convergence
217 , from_subinventory_code VARCHAR2(30)
218 , from_locator_id NUMBER
219 );
220
221 PROCEDURE insert_lot_serial(
222 p_lot_serial_break_tbl IN inv_rcv_common_apis.trans_rec_tb_tp
223 , p_transaction_temp_id IN NUMBER
224 , p_lot_control_code IN NUMBER
225 , p_serial_control_code IN NUMBER
226 , p_interface_transaction_id IN NUMBER
227 );
228
229 -- MANEESH - BEGIN CHANGES - FOR OUTSIDE PROCESSING ITEM
230
231 PROCEDURE create_osp_std_rcvtxn_intf_rec(
232 p_organization_id IN NUMBER
233 , p_po_header_id IN NUMBER
234 , p_po_release_id IN NUMBER
235 , p_po_line_id IN NUMBER
236 , p_po_distribution_id IN NUMBER
237 , p_item_id IN NUMBER
238 , p_vendor_id IN NUMBER
239 , p_revision IN VARCHAR2
240 , p_rcvtxn_qty IN NUMBER
241 , p_rcvtxn_uom IN VARCHAR2
242 , p_transaction_temp_id IN NUMBER
243 , x_status OUT NOCOPY VARCHAR2
244 , x_message OUT NOCOPY VARCHAR2
245 , p_secondary_qty IN NUMBER DEFAULT NULL --OPM Convergence
246 );
247
248 -- MANEESH - END CHANGES - FOR OUTSIDE PROCESSING ITEM
249
250 PROCEDURE create_std_rcvtxn_intf_rec(
251 p_organization_id IN NUMBER
252 , p_po_header_id IN NUMBER
253 , p_po_release_id IN NUMBER
254 , p_po_line_id IN NUMBER
255 , p_shipment_header_id IN NUMBER
256 , p_oe_order_header_id IN NUMBER
257 , p_receipt_num IN VARCHAR2
258 , p_item_id IN NUMBER
259 , p_vendor_id IN NUMBER
260 , p_revision IN VARCHAR2
261 , p_subinventory_code IN VARCHAR2
262 , p_locator_id IN NUMBER
263 , p_rcvtxn_qty IN NUMBER
264 , p_rcvtxn_uom IN VARCHAR2
265 , p_transaction_temp_id IN NUMBER
266 , p_lot_control_code IN NUMBER
267 , p_serial_control_code IN NUMBER
268 , x_status OUT NOCOPY VARCHAR2
269 , x_message OUT NOCOPY VARCHAR2
270 , p_deliver_to_location_id IN NUMBER DEFAULT NULL
271 , p_sec_rcvtxn_qty IN NUMBER DEFAULT NULL --OPM Convergence
272 , p_secondary_uom IN VARCHAR2 DEFAULT NULL --OPMConvergence
273 , p_rcv_act_rjt_qty_type IN NUMBER DEFAULT NULL --BUG 4309432
274 );
275
276 /* FP-J Lot/Serial Support Enhancement
277 * Added two new parameters p_transfer_lpn_id and p_lot_number
278 * with default NULL values
279 */
280 PROCEDURE create_putaway_rcvtxn_intf_rec(
281 p_organization_id IN NUMBER
282 , p_reference_id IN NUMBER
283 , p_reference IN VARCHAR2
284 , p_reference_type_code IN NUMBER
285 , p_item_id IN NUMBER
286 , p_revision IN VARCHAR2
287 , p_subinventory_code IN VARCHAR2
288 , p_locator_id IN NUMBER
289 , p_rcvtxn_qty IN NUMBER
290 , p_rcvtxn_uom_code IN VARCHAR2
291 , p_transaction_temp_id IN NUMBER
292 , p_lot_control_code IN NUMBER
293 , p_serial_control_code IN NUMBER
294 , p_original_txn_temp_id IN NUMBER
295 , x_return_status OUT NOCOPY VARCHAR2
296 , x_msg_count OUT NOCOPY VARCHAR2
297 , x_msg_data OUT NOCOPY VARCHAR2
298 , p_inspection_status_code IN NUMBER
299 , p_lpn_id IN NUMBER
300 , p_transfer_lpn_id IN NUMBER DEFAULT NULL
301 , p_lot_number IN VARCHAR2 DEFAULT NULL
302 , p_parent_txn_id IN NUMBER DEFAULT NULL
303 , p_secondary_quantity IN NUMBER DEFAULT NULL --OPM Convergence
304 , p_secondary_uom IN VARCHAR2 DEFAULT NULL --OPM Convergence
305 );
306
307 PROCEDURE rcvtxn_clear_global;
308
309 PROCEDURE update_rcv_serials_supply(
310 x_return_status OUT NOCOPY VARCHAR2
311 , x_msg_count OUT NOCOPY NUMBER
312 , x_msg_data OUT NOCOPY VARCHAR2
313 , p_shipment_line_id NUMBER
314 );
315 PROCEDURE Match_putaway_rcvtxn_intf_rec(
316 p_organization_id IN NUMBER
317 , p_reference_id IN NUMBER
318 , p_reference IN VARCHAR2
319 , p_reference_type_code IN NUMBER
320 , p_item_id IN NUMBER
321 , p_revision IN VARCHAR2
322 , p_subinventory_code IN VARCHAR2
323 , p_locator_id IN NUMBER
324 , p_rcvtxn_qty IN NUMBER
325 , p_rcvtxn_uom_code IN VARCHAR2
326 , p_transaction_temp_id IN NUMBER
327 , p_lot_control_code IN NUMBER
331 , x_msg_count OUT NOCOPY VARCHAR2
328 , p_serial_control_code IN NUMBER
329 , p_original_txn_temp_id IN NUMBER
330 , x_return_status OUT NOCOPY VARCHAR2
332 , x_msg_data OUT NOCOPY VARCHAR2
333 , p_inspection_status_code IN NUMBER
334 , p_lpn_id IN NUMBER
335 , p_transfer_lpn_id IN NUMBER DEFAULT NULL
336 , p_lot_number IN VARCHAR2 DEFAULT NULL
337 , p_parent_txn_id IN NUMBER DEFAULT NULL
338 , p_secondary_quantity IN NUMBER DEFAULT NULL --OPM Convergence
339 , p_secondary_uom IN VARCHAR2 DEFAULT NULL --OPM Convergence
340 , p_inspection_status IN NUMBER DEFAULT NULL
341 , p_primary_uom_code IN VARCHAR2
342 );
343
344 END inv_rcv_std_deliver_apis;