[Home] [Help]
Skip to content
PACKAGE: APPS.CSD_BULK_RECEIVE_UTIL
Source
1 PACKAGE CSD_BULK_RECEIVE_UTIL AUTHID CURRENT_USER AS
2 /* $Header: csdubrus.pls 120.8.12020000.2 2013/04/03 06:59:54 subhat ship $ */
3
4 /* ---------------------------------------------------------*/
5 /* Define global variables */
6 /* ---------------------------------------------------------*/
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_BULK_RECEIVE_UTIL';
8 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvbuts.pls';
9
10 /*--------------------------------------------------*/
11 /* Record name : Bulk_Receive_Rec */
12 /* description : Bulk Receive Rec record type */
13 /* definition */
14 /*--------------------------------------------------*/
15 TYPE BULK_RECEIVE_REC IS RECORD
16 (
17 BULK_RECEIVE_ID NUMBER,
18 TRANSACTION_NUMBER NUMBER,
19 ORIG_PARTY_ID NUMBER,
20 ORIG_CUST_ACCOUNT_ID NUMBER,
21 PARTY_ID NUMBER,
22 CUST_ACCOUNT_ID NUMBER,
23 SERIAL_NUMBER VARCHAR2(30),
24 INVENTORY_ITEM_ID NUMBER,
25 INSTANCE_ID NUMBER,
26 QUANTITY NUMBER,
27 UOM_CODE VARCHAR2(3),
28 STATUS VARCHAR2(30),
29 PROCESS_STATUS VARCHAR2(30),
30 CHANGE_OWNER_FLAG VARCHAR2(1),
31 INTERNAL_SR_FLAG VARCHAR2(1),
32 WARNING_FLAG VARCHAR2(1),
33 WARNING_REASON_CODE VARCHAR2(80),
34 INCIDENT_ID NUMBER,
35 REPAIR_LINE_ID NUMBER,
36 ATTRIBUTE_CATEGORY VARCHAR2(30),
37 ATTRIBUTE1 VARCHAR2(150),
38 ATTRIBUTE2 VARCHAR2(150),
39 ATTRIBUTE3 VARCHAR2(150),
40 ATTRIBUTE4 VARCHAR2(150),
41 ATTRIBUTE5 VARCHAR2(150),
42 ATTRIBUTE6 VARCHAR2(150),
43 ATTRIBUTE7 VARCHAR2(150),
44 ATTRIBUTE8 VARCHAR2(150),
45 ATTRIBUTE9 VARCHAR2(150),
46 ATTRIBUTE10 VARCHAR2(150),
47 ATTRIBUTE11 VARCHAR2(150),
48 ATTRIBUTE12 VARCHAR2(150),
49 ATTRIBUTE13 VARCHAR2(150),
50 ATTRIBUTE14 VARCHAR2(150),
54 CREATION_DATE DATE,
51 ATTRIBUTE15 VARCHAR2(150),
52 OBJECT_VERSION_NUMBER NUMBER,
53 CREATED_BY NUMBER(15),
55 LAST_UPDATED_BY NUMBER(15),
56 LAST_UPDATE_DATE DATE,
57 LAST_UPDATE_LOGIN NUMBER(15)
58 );
59
60
61 /*--------------------------------------------------*/
62 /* Record name : Bulk_AutoReceive_Rec */
63 /* description : Bulk AutoReceive Record definition */
64 /* */
65 /*--------------------------------------------------*/
66 TYPE BULK_AUTORCV_REC IS RECORD
67 (
68
69 BULK_RECEIVE_ID NUMBER,
70 REPAIR_LINE_ID NUMBER,
71 ORDER_LINE_ID NUMBER,
72 ORDER_HEADER_ID NUMBER,
73 -- new parameters added. 12.2 subhat
74 UNDER_RECEIPT_FLAG VARCHAR2(5),
75 RECEIPT_QTY NUMBER,
76 LOCATOR_ID NUMBER,
77 SUBINVENTORY VARCHAR2(30),
78 LOT_NUMBER VARCHAR2(30),
79 ITEM_REVISION VARCHAR2(3),
80 SERIAL_NUMBER VARCHAR2(30),
81 RCV_ATTRIBUTE_CATEGORY VARCHAR2(30),
82 RCV_ATTRIBUTE1 VARCHAR2(150),
83 RCV_ATTRIBUTE2 VARCHAR2(150),
84 RCV_ATTRIBUTE3 VARCHAR2(150),
85 RCV_ATTRIBUTE4 VARCHAR2(150),
86 RCV_ATTRIBUTE5 VARCHAR2(150),
87 RCV_ATTRIBUTE6 VARCHAR2(150),
88 RCV_ATTRIBUTE7 VARCHAR2(150),
89 RCV_ATTRIBUTE8 VARCHAR2(150),
90 RCV_ATTRIBUTE9 VARCHAR2(150),
91 RCV_ATTRIBUTE10 VARCHAR2(150),
92 RCV_ATTRIBUTE11 VARCHAR2(150),
93 RCV_ATTRIBUTE12 VARCHAR2(150),
94 RCV_ATTRIBUTE13 VARCHAR2(150),
95 RCV_ATTRIBUTE14 VARCHAR2(150),
96 RCV_ATTRIBUTE15 VARCHAR2(150)
97 );
98
99 TYPE BULK_AUTORCV_TBL IS TABLE OF BULK_AUTORCV_REC INDEX BY BINARY_INTEGER;
100
101 /*------------------------------------------------------------*/
102 /* Record Name : SR_RO_RMA_REC */
103 /* Description : Record Definition used by the find RO/RMA */
104 /* program */
105 /*------------------------------------------------------------*/
106 TYPE SR_RO_RMA_REC IS RECORD (
107 INCIDENT_ID NUMBER,
108 REPAIR_LINE_ID NUMBER,
109 ORDER_LINE_ID NUMBER,
110 ORDER_HEADER_ID NUMBER,
111 BULK_RECEIVE_ID NUMBER,
112 CREATE_SR_FLAG VARCHAR2(3),
113 CREATE_RO_FLAG VARCHAR2(3),
114 FOUND_RMA_FLAG VARCHAR2(3),
115 RO_QUANTITY NUMBER,
116 CUST_ACCT_ID NUMBER,
117 PARTY_ID NUMBER,
118 QUANTITY NUMBER,
119 INVENTORY_ITEM_ID NUMBER,
120 SERIAL_NUMBER VARCHAR2(30),
121 INSTANCE_ID NUMBER,
122 NEW_RMA VARCHAR2(3),
123 SPLIT_RMA_QTY NUMBER,
124 NEW_RMA_QTY NUMBER,
125 SPLIT_RMA VARCHAR2(3),
126 rev_control_flag VARCHAR2(3),
127 revision VARCHAR2(3),
128 UI_INCIDENT_ID NUMBER,
129 SERIAL_CONTROL_FLAG NUMBER
130 );
131
132 TYPE SR_RO_RMA_TBL IS TABLE OF SR_RO_RMA_REC INDEX BY BINARY_INTEGER;
133
134 TYPE MATCHING_RMA_REC IS RECORD (
135 PROD_TXN_STATUS VARCHAR2(30),
136 SOURCE_SERIAL_NUMBER VARCHAR2(30),
137 SOURCE_INSTANCE_ID NUMBER,
138 RMA_QUANTITY NUMBER,
139 HEADER_ID NUMBER,
140 LINE_ID NUMBER,
141 INV_ITEM_ID NUMBER
142 );
143
144 TYPE MATCHING_RMA_TBL IS TABLE OF MATCHING_RMA_REC INDEX BY BINARY_INTEGER;
145
146 TYPE SERIAL_CONTROL_FLAG IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
147
148 -- global cache variable.
149 G_SERIAL_CONTROL_FLAG SERIAL_CONTROL_FLAG;
150
151 /*-----------------------------------------------------------------*/
152 /* procedure name: validate_bulk_receive_rec */
153 /* description : Validate Bulk Receive record definition */
154 /* */
155 /*-----------------------------------------------------------------*/
156 PROCEDURE validate_bulk_receive_rec
157 (
158 p_party_id IN NUMBER,
159 p_quantity IN NUMBER,
160 p_serial_number IN VARCHAR2,
161 p_inventory_item_id IN NUMBER,
162 x_warning_flag OUT NOCOPY VARCHAR2,
163 x_warning_reason_code OUT NOCOPY VARCHAR2,
164 x_change_owner_flag OUT NOCOPY VARCHAR2,
165 x_internal_sr_flag OUT NOCOPY VARCHAR2
166 );
167
168 /*-----------------------------------------------------------------*/
169 /* procedure name: create_blkrcv_sr */
170 /* description : Procedure to create Service Request */
171 /* */
172 /*-----------------------------------------------------------------*/
173 PROCEDURE create_blkrcv_sr
174 (
175 p_bulk_receive_rec IN csd_bulk_receive_util.bulk_receive_rec,
176 p_sr_notes_tbl IN cs_servicerequest_pub.notes_table,
177 x_incident_id OUT NOCOPY NUMBER,
178 x_incident_number OUT NOCOPY VARCHAR2,
179 x_return_status OUT NOCOPY VARCHAR2,
180 x_msg_count OUT NOCOPY NUMBER,
181 x_msg_data OUT NOCOPY VARCHAR2
182 );
183
184 /*-----------------------------------------------------------------*/
185 /* procedure name: create_blkrcv_ro */
186 /* description : Procedure to create Repair Orders */
187 /* */
191 p_bulk_receive_id IN NUMBER,
188 /*-----------------------------------------------------------------*/
189 PROCEDURE create_blkrcv_ro
190 (
192 x_repair_line_id OUT NOCOPY NUMBER,
193 x_repair_number OUT NOCOPY VARCHAR2,
194 x_ro_status OUT NOCOPY VARCHAR2,
195 x_return_status OUT NOCOPY VARCHAR2,
196 x_msg_count OUT NOCOPY NUMBER,
197 x_msg_data OUT NOCOPY VARCHAR2
198 ) ;
199
200 /*------------------------------------------------------------------*/
201 /* procedure name: create_blkrcv_default_prod_txn */
202 /* description : Procedure to create Default Product Transactions */
203 /* */
204 /*------------------------------------------------------------------*/
205 PROCEDURE create_blkrcv_default_prod_txn
206 (
207 p_bulk_receive_id IN NUMBER,
208 x_return_status OUT NOCOPY VARCHAR2,
209 x_msg_count OUT NOCOPY NUMBER,
210 x_msg_data OUT NOCOPY VARCHAR2
211 );
212
213 /*-----------------------------------------------------------------*/
214 /* procedure name: change_blkrcv_ib_owner */
215 /* description : Procedure to Change the Install Base Owner */
216 /* */
217 /*-----------------------------------------------------------------*/
218 PROCEDURE change_blkrcv_ib_owner
219 (
220 p_bulk_receive_id IN NUMBER,
221 x_return_status OUT NOCOPY VARCHAR2,
222 x_msg_count OUT NOCOPY NUMBER,
223 x_msg_data OUT NOCOPY VARCHAR2
224 );
225
226 /*-----------------------------------------------------------------*/
227 /* procedure name: bulk_auto_receive */
228 /* description : Procedure to Auto Receive */
229 /* */
230 /*-----------------------------------------------------------------*/
231 PROCEDURE bulk_auto_receive
232 (
233 p_bulk_autorcv_tbl IN OUT NOCOPY csd_bulk_receive_util.bulk_autorcv_tbl,
234 x_return_status OUT NOCOPY VARCHAR2,
235 x_msg_count OUT NOCOPY NUMBER,
236 x_msg_data OUT NOCOPY VARCHAR2
237 );
238
239 /*-----------------------------------------------------------------*/
240 /* procedure name: write_to_conc_log */
241 /* description : Procedure to write the error stack to the the */
242 /* Concurrent log */
243 /*-----------------------------------------------------------------*/
244 PROCEDURE write_to_conc_log
245 (
246 p_msg_count IN NUMBER,
247 p_msg_data IN VARCHAR2
248 );
249
250 /*-----------------------------------------------------------------*/
251 /* procedure name: write_to_conc_output */
252 /* description : Procedure to write the output to the Concurrent */
253 /* Output */
254 /*-----------------------------------------------------------------*/
255 PROCEDURE write_to_conc_output
256 (
257 p_transaction_number IN NUMBER
258 );
259
260 -- swai: bug 7657379
261 -- added function to get default repair type and use bulk receiving
262 -- profile option value as backup default value.
263 /*-----------------------------------------------------------------*/
264 /* function name: get_bulk_rcv_def_repair_type */
265 /* description : Function to get the default repair type for */
266 /* bulk receiving, based on defaulting rules and */
267 /* bulk receiving profile option. */
268 /* Output Repair Type ID */
269 /*-----------------------------------------------------------------*/
270 FUNCTION get_bulk_rcv_def_repair_type
271 (
272 p_incident_id IN NUMBER,
273 p_ro_inventory_item_id IN NUMBER
274 ) return NUMBER;
275
276 -- swai: bug 7663674
277 -- added function to get default rma subinv and use bulk receiving
278 -- profile option value as backup default value.
279 /*-----------------------------------------------------------------*/
280 /* function name: get_bulk_rcv_def_sub_inv */
281 /* description : Function to get the default rma subinv for */
282 /* bulk receiving, based on defaulting rules and */
283 /* bulk receiving profile option. */
284 /* Output RMA Subinventory Code */
285 /*-----------------------------------------------------------------*/
286 FUNCTION get_bulk_rcv_def_sub_inv
287 (
288 p_repair_line_id IN NUMBER
289 ) return VARCHAR2;
290
291 /*-----------------------------------------------------------------*/
292 /* procedure name: get_sr_ro_rma_details */
293 /* description : Procedure to get the existing SR,RO,RMA */
294 /* combination */
295 /*-----------------------------------------------------------------*/
296 PROCEDURE get_sr_ro_rma_details
297 (
298 p_transaction_number IN NUMBER,
299 x_sr_ro_rma_tbl IN OUT NOCOPY sr_ro_rma_tbl
300 );
301
302 /*-----------------------------------------------------------------*/
303 /* procedure name: matching_rma_found */
304 /* description : Procedure to get the RMA for the repair order */
308
305 /* passed. */
306 /*-----------------------------------------------------------------*/
307
309 PROCEDURE matching_rma_found
310 (
311 p_repair_line_id IN NUMBER,
312 p_blk_rec_qty IN NUMBER,
313 p_blk_rec_serial_number IN VARCHAR2,
314 p_blk_rec_instance_id IN NUMBER,
315 p_blk_rec_inv_id IN NUMBER,
316 x_rma_found OUT NOCOPY VARCHAR2,
317 x_new_rma OUT NOCOPY VARCHAR2,
318 x_split_rma_qty OUT NOCOPY NUMBER,
319 x_new_rma_qty OUT NOCOPY NUMBER,
320 x_split_rma OUT NOCOPY VARCHAR2,
321 x_order_header_id OUT NOCOPY NUMBER,
322 x_order_line_id OUT NOCOPY NUMBER
323 );
324
325 /*-----------------------------------------------------------------*/
326 /* procedure name: create_new_rma */
327 /* description : Procedure to get the create a new RMA for the */
328 /* over receipt case. */
329 /*-----------------------------------------------------------------*/
330
331 PROCEDURE create_new_rma
332 (
333 p_api_version IN NUMBER DEFAULT 1,
334 p_init_msg_list IN VARCHAR2 DEFAULT 'F',
335 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
336 p_order_header_id IN NUMBER,
337 p_new_rma_qty IN NUMBER,
338 p_repair_line_id IN NUMBER,
339 p_incident_id IN NUMBER,
340 p_rma_quantity IN NUMBER,
341 x_msg_count OUT NOCOPY NUMBER,
342 x_msg_data OUT NOCOPY VARCHAR2,
343 x_return_status OUT NOCOPY VARCHAR2,
344 x_order_line_id OUT NOCOPY NUMBER,
345 x_order_header_id OUT NOCOPY NUMBER
346 ) ;
347
348 /*-----------------------------------------------------------------*/
349 /* procedure name: link_sr_ro_rma_oa_wrapper */
350 /* description : Procedure called from OA page to retreive the */
351 /* SR, RO and RMA. */
352 /*-----------------------------------------------------------------*/
353
354 PROCEDURE link_sr_ro_rma_oa_wrapper(
355 p_bulk_rcv_dtls_tbl IN VARCHAR2_TABLE_200,
356 p_mode IN VARCHAR2,
357 p_incident_id IN NUMBER DEFAULT NULL,
358 x_repair_line_id OUT NOCOPY JTF_NUMBER_TABLE,
359 x_incident_id OUT NOCOPY JTF_NUMBER_TABLE,
360 x_unplanned_receipt_flag OUT NOCOPY VARCHAR2_TABLE_100,
361 x_over_receipt_flag OUT NOCOPY VARCHAR2_TABLE_100,
362 x_under_receipt_flag OUT NOCOPY VARCHAR2_TABLE_100,
363 x_order_header_id OUT NOCOPY JTF_NUMBER_TABLE,
364 x_order_line_id OUT NOCOPY JTF_NUMBER_TABLE,
365 x_over_receipt_qty OUT NOCOPY JTF_NUMBER_TABLE,
366 x_under_receipt_qty OUT NOCOPY JTF_NUMBER_TABLE
367 ) ;
368
369
370 /* ***************************************************************************/
371 /* Procedure Name: after_receipt */
372 /* Description: Performs the action after the PO Receipt concurrent program */
373 /* is finished */
374 /* params: @p_request_group_id: Group Id for receipts submitted. */
375 /* @p_transaction_number: Bulk Receive Transaction Number. */
376 /* ***************************************************************************/
377
378 procedure after_receipt(p_request_group_id IN NUMBER,
379 p_transaction_number IN NUMBER
380 );
381
382 /* ***************************************************************************/
383 /* Procedure Name: pre_process_rma. */
384 /* Description: Checks if the RMA is ready to be received. If the RMA is in */
385 /* SUBMITTED status, books the RMA and if the RMA is in ENTERED */
386 /* status, then it submits the RMA to OM and books it. */
387 /* ***************************************************************************/
388
389 procedure pre_process_rma (p_repair_line_id IN NUMBER,
390 px_order_header_id IN OUT NOCOPY NUMBER,
391 px_order_line_id IN OUT NOCOPY NUMBER,
392 x_return_status OUT NOCOPY VARCHAR2,
393 x_msg_count OUT NOCOPY NUMBER,
394 x_msg_data OUT NOCOPY VARCHAR2
395 );
396
397 /* ***************************************************************************/
398 /* Procedure Name: create_new_ship_line. */
399 /* Description : Creates a new Ship line for the over-receipt quantity */
400 /* ***************************************************************************/
401
402 procedure create_new_ship_line
403 (
404 p_api_version IN VARCHAR2,
405 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
406 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
407 p_order_header_id IN NUMBER,
408 p_new_ship_qty IN NUMBER,
409 p_repair_line_id IN NUMBER,
410 p_incident_id IN NUMBER,
411 x_return_status OUT NOCOPY VARCHAR2,
412 x_msg_count OUT NOCOPY NUMBER,
413 x_msg_data OUT NOCOPY VARCHAR2
414 );
415 -- 12.2, subhat
416 FUNCTION split_varchar2_tbl (
417 px_tbl_type IN OUT NOCOPY VARCHAR2,
418 p_delimiter IN VARCHAR2
419 ) RETURN VARCHAR2;
420 /*-----------------------------------------------------------------*/
421 /* Function name: get_latest_open_sr */
422 /* description : The function will return the latest open SR */
423 /* if any for the customer account. */
424 /*-----------------------------------------------------------------*/
425
426 FUNCTION get_latest_open_sr
427 (
428 p_account_id in NUMBER,
429 p_party_id in NUMBER) RETURN NUMBER;
430
431 /*-----------------------------------------------------------------*/
432 /* Function name: get_num_in_list */
433 /* description : The function will return the JTF_NUMBER_TABLE */
434 /* type when a string containing numbers is passed to it.*/
435 /*-----------------------------------------------------------------*/
436
437 FUNCTION get_num_in_list(p_in_string IN varchar2) RETURN JTF_NUMBER_TABLE;
438
439 END CSD_BULK_RECEIVE_UTIL;