[Home] [Help]
PACKAGE: APPS.CSP_RECEIVE_PVT
Source
1 PACKAGE CSP_RECEIVE_PVT AS
2 /* $Header: cspvrcvs.pls 120.1 2006/07/20 06:10:36 hhaugeru noship $*/
3
4 G_PROD_CODE CONSTANT VARCHAR2(5) := 'CSP';
5
6 TYPE rcv_hdr_rec_type IS RECORD (
7 header_interface_id NUMBER,
8 group_id NUMBER,
9 vendor_id NUMBER ,
10 vendor_site_id NUMBER ,
11 source_type_code VARCHAR2(30),
12 receipt_source_code VARCHAR2(25),
13 ship_to_org_id NUMBER ,
14 rcv_shipment_num VARCHAR2(30),
15 receipt_header_id NUMBER,
16 receipt_num VARCHAR2(30),
17 bill_of_lading VARCHAR2(25),
18 packing_slip VARCHAR2(25),
19 shipped_date DATE,
20 freight_carrier_code VARCHAR2(25),
21 expected_receipt_date DATE,
22 employee_id NUMBER,
23 waybill_airbill_num VARCHAR2(20),
24 usggl_transaction_code VARCHAR2(30),
25 processing_request_id NUMBER,
26 customer_id NUMBER,
27 customer_site_id NUMBER);
28
29 TYPE rcv_rec_type IS RECORD(
30 interface_transaction_id NUMBER
31 , transaction_interface_id NUMBER
32 , header_interface_id NUMBER
33 , group_id NUMBER
34 , inv_loc_assignment_id NUMBER
35 , source_type_code VARCHAR2(30)
36 , receipt_source_code VARCHAR2(30)
37 , order_type_code VARCHAR2(30)
38 , order_type VARCHAR2(80)
39 , po_header_id NUMBER
40 , po_number VARCHAR2(30)
41 , po_line_id NUMBER
42 , po_line_number NUMBER
43 , po_line_location_id NUMBER
44 , po_shipment_number NUMBER
45 , po_release_id NUMBER
46 , po_release_number NUMBER
47 , req_header_id NUMBER
48 , req_number VARCHAR2(80)
49 , req_line_id NUMBER
50 , req_line NUMBER
51 , req_distribution_id NUMBER
52 , rcv_shipment_header_id NUMBER
53 , rcv_shipment_number VARCHAR2(30)
54 , rcv_shipment_line_id NUMBER
55 , rcv_line_number NUMBER
56 , from_organization_id NUMBER
57 , to_organization_id NUMBER
58 , vendor_id NUMBER
59 , SOURCE VARCHAR2(255)
60 , vendor_site_id NUMBER
61 , outside_operation_flag VARCHAR2(1)
62 , receipt_exception VARCHAR2(1)
63 , item_id NUMBER
64 , uom_code VARCHAR2(3)
65 , primary_uom VARCHAR2(25)
66 , primary_uom_class VARCHAR2(10)
67 , item_allowed_units_lookup_code NUMBER
68 , item_locator_control NUMBER
69 , restrict_locators_code VARCHAR2(1)
70 , restrict_subinventories_code VARCHAR2(1)
71 , shelf_life_code NUMBER
72 , shelf_life_days NUMBER
73 , serial_number_control_code NUMBER
74 , lot_control_code NUMBER
75 , item_rev_control_flag_to VARCHAR2(1)
76 , item_rev_control_flag_from VARCHAR2(1)
77 , item_number VARCHAR2(40)
78 , item_revision VARCHAR2(3)
79 , item_description VARCHAR2(240)
80 , item_category_id NUMBER
81 , hazard_class VARCHAR2(40)
82 , un_number VARCHAR2(30)
83 , vendor_item_number VARCHAR2(30)
84 , ship_to_location_id NUMBER
85 , ship_to_location VARCHAR2(60)
86 , packing_slip VARCHAR2(80)
87 , routing_id NUMBER
88 , routing_name VARCHAR2(30)
89 , need_by_date DATE
90 , expected_receipt_date DATE
91 , ordered_qty NUMBER
92 , ordered_uom VARCHAR2(25)
93 , ussgl_transaction_code VARCHAR2(30)
94 , government_context VARCHAR2(30)
95 , inspection_required_flag VARCHAR2(1)
96 , receipt_required_flag VARCHAR2(1)
97 , enforce_ship_to_location_code VARCHAR2(30)
98 , substitute_receipt VARCHAR2(30)
99 , unit_price NUMBER
100 , currency_code VARCHAR2(30)
101 , currency_conversion_type VARCHAR2(30)
102 , currency_conversion_date DATE
103 , currency_conversion_rate NUMBER
104 , note_to_receiver VARCHAR2(240)
105 , destination_type_code VARCHAR2(30)
106 , deliver_to_person_id NUMBER
107 , deliver_to_location_id NUMBER
108 , destination_subinventory VARCHAR2(10)
109 , attribute_category VARCHAR2(30)
110 , attribute1 VARCHAR2(240)
111 , attribute2 VARCHAR2(240)
112 , attribute3 VARCHAR2(240)
113 , attribute4 VARCHAR2(240)
114 , attribute5 VARCHAR2(240)
115 , attribute6 VARCHAR2(240)
116 , attribute7 VARCHAR2(240)
117 , attribute8 VARCHAR2(240)
118 , attribute9 VARCHAR2(240)
119 , attribute10 VARCHAR2(240)
120 , attribute11 VARCHAR2(240)
121 , attribute12 VARCHAR2(240)
122 , attribute13 VARCHAR2(240)
123 , attribute14 VARCHAR2(240)
124 , attribute15 VARCHAR2(240)
125 , closed_code VARCHAR2(30)
126 , asn_type VARCHAR2(30)
127 , bill_of_lading VARCHAR2(30)
128 , shipped_date DATE
129 , freight_carrier_code VARCHAR2(30)
130 , waybill_airbill_num VARCHAR2(80)
131 , freight_bill_num VARCHAR2(35)
132 , vendor_lot_num VARCHAR2(80)
133 , container_num VARCHAR2(35)
134 , truck_num VARCHAR2(35)
135 , bar_code_label VARCHAR2(35)
136 , rate_type_display VARCHAR2(30)
137 , match_option VARCHAR2(25)
138 , country_of_origin_code VARCHAR2(2)
139 , oe_order_header_id NUMBER
140 , oe_order_num NUMBER
141 , oe_order_line_id NUMBER
142 , oe_order_line_num NUMBER
143 , customer_id NUMBER
144 , customer_site_id NUMBER
145 , customer_item_num VARCHAR2(50)
146 , pll_note_to_receiver VARCHAR2(240)
147 , po_distribution_id NUMBER
148 , qty_ordered NUMBER
149 , wip_entity_id NUMBER
150 , wip_operation_seq_num NUMBER
151 , wip_resource_seq_num NUMBER
152 , wip_repetitive_schedule_id NUMBER
153 , wip_line_id NUMBER
154 , bom_resource_id NUMBER
155 , destination_type VARCHAR2(80)
156 , LOCATION VARCHAR2(60)
157 , currency_conversion_rate_pod NUMBER
158 , currency_conversion_date_pod DATE
159 , project_id NUMBER
160 , task_id NUMBER
161 , locator_id NUMBER
162 , employee_id NUMBER
163 , lot_number VARCHAR2(80)
164 , transaction_quantity NUMBER
165 , transaction_uom VARCHAR2(25)
166 , primary_quantity NUMBER
167 , lot_quantity NUMBER
168 , lot_primary_quantity NUMBER
169 , expiration_date DATE,
170 status_id NUMBER,
171 product_transaction_id NUMBER,
172 product_code VARCHAR2(5) ,
173 att_exist VARCHAR2(1) ,
174 update_mln VARCHAR2(1) ,
175 description VARCHAR2(256) ,
176 vendor_name VARCHAR2(240) ,
177 supplier_lot_number VARCHAR2(150) ,
178 origination_date DATE ,
179 date_code VARCHAR2(150) ,
180 grade_code VARCHAR2(150) ,
181 change_date DATE ,
182 maturity_date DATE ,
183 retest_date DATE ,
184 age NUMBER ,
185 item_size NUMBER ,
186 color VARCHAR2(150) ,
187 volume NUMBER ,
188 volume_uom VARCHAR2(3) ,
189 place_of_origin VARCHAR2(150) ,
190 best_by_date DATE ,
191 length NUMBER ,
192 length_uom VARCHAR2(3) ,
193 recycled_content NUMBER ,
194 thickness NUMBER ,
195 thickness_uom VARCHAR2(3) ,
196 width NUMBER ,
197 width_uom VARCHAR2(3) ,
198 curl_wrinkle_fold VARCHAR2(150) ,
199 territory_code VARCHAR2(30) ,
200 fm_serial_number VARCHAR2(30),
201 to_serial_number VARCHAR2(30),
202 update_msn VARCHAR2(1) ,
203 vendor_serial_number VARCHAR2(30) ,
204 vendor_lot_number VARCHAR2(80) ,
205 parent_serial_number VARCHAR2(30) ,
206 time_since_new NUMBER,
207 cycles_since_new NUMBER,
208 time_since_overhaul NUMBER,
209 cycles_since_overhaul NUMBER,
210 time_since_repair NUMBER,
211 cycles_since_repair NUMBER,
212 time_since_visit NUMBER,
213 cycles_since_visit NUMBER,
214 time_since_mark NUMBER,
215 cycles_since_mark NUMBER,
216 number_of_repairs NUMBER,
217 set_of_books_id_sob NUMBER,
218 reason_id NUMBER,
219 currency_code_sob VARCHAR2(15),
220 department_code VARCHAR2(10),
221 comments VARCHAR2(240) );
222
223
224 TYPE csp_global_var_rec IS RECORD (
225 transaction_interface_id NUMBER);
226
227 g_csp_var_rec csp_global_var_rec;
228
229 TYPE rcv_rec_tbl_type IS TABLE OF rcv_rec_type INDEX BY BINARY_INTEGER;
230
231 PROCEDURE receive_shipments
232 (P_Api_Version_Number IN NUMBER,
233 P_init_Msg_List IN VARCHAR2,
234 P_Commit IN VARCHAR2,
235 P_Validation_Level IN NUMBER,
236 p_receive_hdr_rec IN rcv_hdr_rec_type,
237 p_receive_rec_tbl IN rcv_rec_tbl_type,
238 X_Return_Status OUT NOCOPY VARCHAR2,
239 X_Msg_Count OUT NOCOPY NUMBER,
240 X_Msg_Data OUT NOCOPY VARCHAR2);
241
242 PROCEDURE insert_rcv_hdr_interface
243 (P_Api_Version_Number IN NUMBER,
244 P_init_Msg_List IN VARCHAR2,
245 P_Commit IN VARCHAR2,
246 P_Validation_Level IN NUMBER,
247 X_Return_Status OUT NOCOPY VARCHAR2,
248 X_Msg_Count OUT NOCOPY NUMBER,
249 X_Msg_Data OUT NOCOPY VARCHAR2,
250 p_header_interface_id IN NUMBER,
251 p_group_id IN NUMBER,
252 p_receipt_source_code IN VARCHAR2,
253 p_source_type_code IN VARCHAR2,
254 p_vendor_id IN NUMBER,
255 p_vendor_site_id IN NUMBER,
256 p_ship_to_org_id IN NUMBER,
257 p_shipment_num IN VARCHAR2,
258 p_receipt_header_id IN NUMBER,
259 p_receipt_num IN VARCHAR2,
260 p_bill_of_lading IN VARCHAR2,
261 p_packing_slip IN VARCHAR2,
262 p_shipped_date IN DATE,
263 p_freight_carrier_code IN VARCHAR2,
264 p_expected_receipt_date IN DATE,
265 p_employee_id IN NUMBER,
266 p_waybill_airbill_num IN VARCHAR2,
267 p_usggl_transaction_code IN VARCHAR2,
268 p_processing_request_id IN NUMBER,
269 p_customer_id IN NUMBER,
270 p_customer_site_id IN NUMBER,
271 x_header_interface_id OUT NOCOPY NUMBER,
272 x_group_id OUT NOCOPY NUMBER);
273
274 PROCEDURE insert_rcv_txn_interface
275 (P_Api_Version_Number IN NUMBER,
276 P_init_Msg_List IN VARCHAR2,
277 P_Commit IN VARCHAR2,
278 P_Validation_Level IN NUMBER,
279 X_Return_Status OUT NOCOPY VARCHAR2,
280 X_Msg_Count OUT NOCOPY NUMBER,
281 X_Msg_Data OUT NOCOPY VARCHAR2,
282 x_interface_transaction_id OUT NOCOPY NUMBER,
283 p_receive_rec IN rcv_rec_type);
284
285 /*----------------------------------------------------------------------------
286 * PROCEDURE: insert_lots_interface
287 * Description:
288 * This procedure inserts a record into MTL_TRANSACTION_LOTS_INTERFACE
289 * If there already exists a record with the transaction_interface_id
290 * and lot_number combination THEN
291 * Update transaction_quantity and primary_quantity
292 * Else
293 * Insert a new record into MTL_TRANSACTION_LOTS_INTERFACE
294 *
295 * @param p_api_version - Version of the API
296 * @param p_init_msg_list - Flag to initialize message list
297 * @param x_return_status
298 * Return status indicating Success (S), Error (E), Unexpected Error (U)
299 * @param x_msg_count
300 * Number of messages in message list
301 * @param x_msg_data
302 * Stacked messages text
303 * @param p_transaction_interface_id - MTLI.Interface Transaction ID
304 * @param p_lot_number - Lot Number
305 * @param p_transaction_quantity - Transaction Quantity for the lot
306 * @param p_primary_quantity - Primary Quantity for the lot
307 * @param p_organization_id - Organization ID
308 * @param p_inventory_item_id - Inventory Item ID
309 * @param x_serial_transaction_temp_id
310 * - Serial Transaction Temp Id (for lot and serial controlled item)
311 * @param p_product_transaction_id - Product Transaction Id. This parameter
312 * is stamped with the transaction identifier with
313 * @param p_product_code - Code of the product creating this record
314 * @ return: NONE
315 *---------------------------------------------------------------------------*/
316
317 PROCEDURE insert_lots_interface (
318 p_api_version IN NUMBER
319 , p_init_msg_list IN VARCHAR2
320 , x_return_status OUT NOCOPY VARCHAR2
321 , x_msg_count OUT NOCOPY NUMBER
322 , x_msg_data OUT NOCOPY VARCHAR2
323 , p_serial_transaction_temp_id IN NUMBER
324 , p_transaction_interface_id IN NUMBER
325 , p_lot_number IN VARCHAR2
326 , p_transaction_quantity IN NUMBER
327 , p_primary_quantity IN NUMBER
328 , p_organization_id IN NUMBER
329 , p_inventory_item_id IN NUMBER
330 , p_product_transaction_id IN NUMBER
331 , p_product_code IN VARCHAR2);
332
333 /*----------------------------------------------------------------------------
334 * PROCEDURE: insert_serial_interface
335 * Description:
336 * This procedure inserts a record into MTL_SERIAL_NUMBERS_INTERFACE
337 * Generate transaction_interface_id if the parameter is NULL
338 * Generate product_transaction_id if the parameter is NULL
339 * The insert logic is based on the parameter p_att_exist.
340 * If p_att_exist is "N" Then (attributes are not available in table)
341 * Read the input parameters (including attributes) into a PL/SQL table
342 * Insert one record into MSNI with the from and to serial numbers passed
343 * Else
344 * Loop through each serial number between the from and to serial number
345 * Fetch the attributes into one row of the PL/SQL table and
346 * For each row in the PL/SQL table, insert one MSNI record
347 * End If
348 *
349 * @param p_api_version - Version of the API
350 * @param p_init_msg_list - Flag to initialize message list
351 * @param x_return_status
352 * Return status indicating Success (S), Error (E), Unexpected Error (U)
353 * @param x_msg_count
354 * Number of messages in message list
355 * @param x_msg_data
356 * Stacked messages text
357 * @param p_transaction_interface_id - MTLI.Interface Transaction ID
358 * @param p_fm_serial_number - From Serial Number
359 * @param p_to_serial_number - To Serial Number
360 * @param p_organization_id - Organization ID
361 * @param p_inventory_item_id - Inventory Item ID
362 * @param p_status_id - Material Status for the lot
363 * @param p_product_transaction_id - Product Transaction Id. This parameter
364 * is stamped with the transaction identifier with
365 * @param p_product_code - Code of the product creating this record
366 *
367 * @ return: NONE
368 *---------------------------------------------------------------------------*/
369
370 PROCEDURE insert_serial_interface(
371 p_api_version IN NUMBER
372 , p_init_msg_list IN VARCHAR2
373 , x_return_status OUT NOCOPY VARCHAR2
374 , x_msg_count OUT NOCOPY NUMBER
375 , x_msg_data OUT NOCOPY VARCHAR2
376 , px_transaction_interface_id IN OUT NOCOPY NUMBER
377 , p_product_transaction_id IN NUMBER
378 , p_product_code IN VARCHAR2
379 , p_fm_serial_number IN VARCHAR2
380 , p_to_serial_number IN VARCHAR2) ;
381
385 /*********************************************************************/
382 /**********************************************************************/
383 -- This procedure is to process interface
384 -- transaction records online.
386 PROCEDURE rcv_online_request ( p_group_id IN NUMBER,
387 x_return_status OUT NOCOPY VARCHAR2,
388 x_msg_data OUT NOCOPY VARCHAR2);
389
390 FUNCTION USER_INPUT_REQUIRED(p_header_id IN
391 number) RETURN VARCHAR2;
392
393 END CSP_RECEIVE_PVT;