1 Package Csd_Logistics_Util AS
2 /* $Header: csdulogs.pls 120.7 2006/06/14 15:59:55 vparvath noship $ */
3
4 /*---------------------------------------------------------------------------*/
5 /* Record name: ItemAttributes_Rec_Type */
6 /* description: */
7 /* SU: Reccrd used for holding item attributes values for a given inventory*/
8 /* item id */
9 /*---------------------------------------------------------------------------*/
10 TYPE ItemAttributes_Rec_Type IS RECORD(
11 Serial_Code NUMBER,
12 Lot_Code NUMBER,
13 Revision_Code NUMBER,
14 IB_Flag VARCHAR2(1),
15 reservable_type VARCHAR2(1));
16 -- Define global variables here
17 g_Concatenated_Segments VARCHAR2(40);
18
19 TYPE PRODTXN_DB_ATTR_REC IS RECORD (
20 est_detail_id NUMBER,
21 repair_line_id NUMBER,
22 curr_submit_order_flag VARCHAR2(1),
23 curr_book_order_flag VARCHAR2(1),
24 curr_release_order_flag VARCHAR2(1),
25 curr_ship_order_flag VARCHAR2(1),
26 object_version_num NUMBER,
27 txn_type_id NUMBER);
28
29 create_order EXCEPTION;
30 /* Serial number reservation changes, begin*/
31
32 type CSD_SERIAL_RESERVE_REC_TYPE IS RECORD (
33
34 Inventory_Item_Id MTL_RESERVATIONS.INVENTORY_ITEM_ID%type,
35 Inv_organization_Id MTL_RESERVATIONS.ORGANIZATION_ID%type,
36 Reservation_uom_code MTL_RESERVATIONS.RESERVATION_UOM_CODE%type,
37 Serial_Number MTL_RESERVATIONS.SERIAL_NUMBER%type,
38 Lot_Number MTL_RESERVATIONS.LOT_NUMBER%type,
39 Locator_id MTL_RESERVATIONS.LOCATOR_ID%type,
40 Revision MTL_RESERVATIONS.REVISION%type,
41 Order_Header_Id MTL_RESERVATIONS.DEMAND_SOURCE_HEADER_ID%type,
42 Order_line_Id MTL_RESERVATIONS.DEMAND_SOURCE_LINE_ID%type,
43 Subinventory_Code MTL_RESERVATIONS.SUBINVENTORY_CODE%type,
44 order_schedule_date DATE
45
46 );
47 /* Serial number reservation changes, end*/
48 /*---------------------------------------------------------------------------*/
49 /* procedure name: Validate_PriceListID */
50 /* description : Validate Price List for a given Price List Id */
51 /* SU: comment this helper routine as this validation is done by charges API.*/
52 /*---------------------------------------------------------------------------*/
53 -- Procedure Validate_PriceListID
54 -- ( p_Price_List_Id IN NUMBER
55 -- ) ;
56
57 /*---------------------------------------------------------------------------*/
58 /* procedure name: Validate_InventoryItemID */
59 /* description : Helper routine that Validates item for a given item ID */
60 /* in the mtl system items table */
61 /* Parameters Required: */
62 /* p_Inventory_Item_Id IN Item identifier */
63 /* x_return_status OUT Standard API paramater */
64 /* x_msg_count OUT Standard API paramater */
65 /* x_msg_data OUT Standard API paramater */
66 /*---------------------------------------------------------------------------*/
67 PROCEDURE Validate_InventoryItemID(p_Inventory_Item_ID IN NUMBER,
68 x_return_status OUT NOCOPY VARCHAR2,
69 x_msg_count OUT NOCOPY NUMBER,
70 x_msg_data OUT NOCOPY VARCHAR2);
71
72 /*---------------------------------------------------------------------------*/
73 /* procedure name: Validate_TxnBillingTypeID */
74 /* description : Helper rutine that validates Billing type for a given Txn */
75 /* Billing Type ID, */
76 /* SU:02/24 Business Process Id, Line Category code, operating Unit */
77 /* Parameters Required: */
78 /* p_Txn_Billing_Type_Id IN Txn billing type identifier */
79 /* p_BusinessProcessID IN Business process id */
80 /* p_LineOrderCategoryCode IN Line Order Category Code */
81 /* p_Operating_Unit_Id IN Org_ID */
82 /*---------------------------------------------------------------------------*/
83 PROCEDURE Validate_TxnBillingTypeID(p_Txn_Billing_Type_Id IN NUMBER,
84 p_BusinessProcessID IN NUMBER,
85 p_LineOrderCategoryCode IN VARCHAR2,
86 p_Operating_Unit_Id IN NUMBER);
87
88 /*---------------------------------------------------------------------------*/
89 /* procedure name: Validate_Revision */
90 /* description : Define helper routine that validates Revision for a given */
91 /* Inventory Item Id */
92 /* Parameters Required: */
93 /* p_Inventory_Item_Id IN Item identifier */
94 /* p_Revision IN Revision from mtl serial numbers */
95 /*---------------------------------------------------------------------------*/
96 PROCEDURE Validate_Revision(p_Inventory_Item_Id IN NUMBER,
97 p_Revision IN VARCHAR2);
98
99 /*---------------------------------------------------------------------------*/
100 /* procedure name: Validate_Instance_ID */
101 /* description : SU:02/24: Validates Instance Id for a given */
102 /* Instance Id, Inventory Item Id, party id and account id */
103 /* SU:02/24 and returns serial number and instance number */
104 /* Parameters Required: */
105 /* p_Inventory_Item_Id IN Item identifier */
106 /* p_Instance_ID IN Instance ID to be validated */
107 /* p_Party_Id IN owner party identifier */
108 /* p_Account_ID IN owner account identifier */
109 /* x_Instance_Number OUT Instance number from Item instances */
110 /* x_Serial_Number OUT Serial number from Item instances */
111 /*---------------------------------------------------------------------------*/
112 PROCEDURE Validate_Instance_ID(p_Inventory_Item_Id IN NUMBER,
113 p_Instance_ID IN NUMBER,
114 p_Party_Id IN NUMBER,
115 p_Account_ID IN NUMBER,
116 x_Instance_Number OUT NOCOPY VARCHAR2,
117 x_Serial_Number OUT NOCOPY VARCHAR2);
118
119 /*---------------------------------------------------------------------------*/
120 /* procedure name: Validate_LotNumber */
121 /* description : Validate Lot Number for a given Inventory Item Id and Lot */
122 /* Parameters Required: */
123 /* p_Inventory_Item_Id IN Item identifier */
124 /* p_Lot_Number IN Lot number to be validated */
125 /*---------------------------------------------------------------------------*/
126 PROCEDURE Validate_LotNumber(p_Inventory_Item_Id IN NUMBER,
127 p_Lot_Number IN VARCHAR2);
128
129 /*---------------------------------------------------------------------------*/
130 /* procedure name: Validate_SerialNumber */
131 /* description : Validate Serial Number for a given Inv Item Id */
132 /* Parameters Required: */
133 /* p_Inventory_Item_Id IN Item identifier */
134 /* p_Serial_Number IN Serial_Number from mtl serial numbers */
135 /*---------------------------------------------------------------------------*/
136 PROCEDURE Validate_SerialNumber(p_Inventory_Item_Id IN NUMBER,
137 p_Serial_Number IN VARCHAR2);
138
139 /*---------------------------------------------------------------------------*/
140 /* procedure name: Validate_ReasonCode */
141 /* description : Helper routing to validate Reason Code against the List */
142 /* of values in fnd lookups */
143 /* Parameters Required: */
144 /* p_ReasonCode -> Lookup value to validate */
145 /*---------------------------------------------------------------------------*/
146 PROCEDURE Validate_ReasonCode(p_ReasonCode IN VARCHAR2);
147
148 /*---------------------------------------------------------------------------*/
149 /* procedure name: Validate_UOM */
150 /* description : Helper routine used to validate Unit Of Measure of an */
151 /* inventory item id */
152 /* Parameters Required: */
153 /* p_Inventory_Item_Id IN Item identifier */
154 /* p_Unit_Of_Measure IN Unit of Measure */
155 /*---------------------------------------------------------------------------*/
156 PROCEDURE Validate_UOM(p_Inventory_Item_Id IN NUMBER,
157 p_Unit_Of_Measure IN VARCHAR2);
158
159 /*---------------------------------------------------------------------------*/
160 /* procedure name: Validate_PartySiteID */
161 /* description : Define Helper routine to validate Party_Site_Id for a */
162 /* SU:02/24: given party, party site and party use type */
163 /* Parameters Required: */
164 /* p_Party_ID IN Unique party identifier */
165 /* p_Party_Site_Id IN unique party site identifier */
166 /* p_Site_Use_type IN i.e. SHIP_TO and BILL_TO */
167 /*---------------------------------------------------------------------------*/
168 PROCEDURE Validate_PartySiteID(p_Party_ID IN NUMBER,
169 p_Party_Site_Id IN NUMBER,
170 p_Site_Use_type IN VARCHAR2);
171
172 /*---------------------------------------------------------------------------*/
173 /* procedure name: Build_ProductTxnRec */
174 /* description : */
175 /* Purpose of this API is to build product txn record, by copying all the */
176 /* values from p_UpdateProdTxnRec to Product_Txn_Rec */
177 /* Do the check for G_MISS_XXX for following attributes and copy to */
178 /* x_product_Txn_Rec accordingly. */
179 /* Action_Type, Action_Code, Txn_Billing_Type_ID, Inventory_Item_Id, */
180 /* Price_List_Id, Quantity, Revision, */
181 /* source Serial_Number, non_source_Serial_Number, Lot_Number, */
182 /* Sub_Inventory, Instance_Id, Return_Reason, Return_By_Date, */
183 /* PO_Number, Invoice_To_Org_Id, Ship_To_Org_Id, */
184 /* Unit_Of_Measure */
185 /* Set values for WHO columns. Copy all DFF columns */
186 /* Copy Object_Version_Number column too */
187 /* Parameters Required: */
188 /* p_UpdateProductTrxn_Rec IN user input values are stored in this record */
189 /* x_Product_Txn_Rec IN OUT database values are stored in this record*/
190 /*---------------------------------------------------------------------------*/
191 PROCEDURE Build_ProductTxnRec(p_Upd_ProdTxn_Rec IN CSD_LOGISTICS_PUB.Upd_ProdTxn_Rec_Type,
192 x_Product_Txn_Rec IN OUT NOCOPY Csd_Process_Pvt.Product_Txn_Rec);
193
194 /*---------------------------------------------------------------------------*/
195 /* procedure name: Get_Concatenated_Segments */
196 /* description : Define helper routine to get concatenated segments name */
197 /* for a given Inventory Item Id */
198 /* Parameters Required: */
199 /* p_Inventory_Item_Id IN Item identifier */
200 /* x_Concatenated_Segments OUT Concatenated segments from mtl system ites */
201 /* Notes: Once the Inventory_Item_Id is validated the global variable */
202 /* g_Concatenated_Segments is populated and then is going to be used by */
203 /* different helper routines to report error messages. */
204 /*---------------------------------------------------------------------------*/
205 PROCEDURE Get_Concatenated_Segments(p_inventory_item_Id IN NUMBER,
206 x_Concatenated_Segments OUT NOCOPY VARCHAR2);
207
208 /*---------------------------------------------------------------------------*/
209 /* procedure name: Validate_source_SerialNumber */
210 /* description : Helper Routine to validate source_Serial_Number for a */
211 /* given serial number */
212 /* Parameters Required: */
213 /* p_Inventory_Item_Id IN Item identifier */
214 /* x_Concatenated_Segments OUT Concatenated segments from mtl system ites */
215 /*---------------------------------------------------------------------------*/
216 PROCEDURE Validate_source_SerialNumber(p_Inventory_Item_ID IN NUMBER,
217 p_Serial_Number IN VARCHAR2,
218 p_Serial_Control_Code IN NUMBER);
219
220 /*---------------------------------------------------------------------------*/
221 /* procedure name: Set_ProductTrxnRec_Flags */
222 /* description : */
223 /* SU: This procedure is a helper routine to read the values from record */
224 /* structure UpdateProductTrxn_rec, which is an input parameter for */
225 /* wrapper API CSD_Process_PVt.Update_Product_Txn_Wrapr and set values */
226 /* in record structure Product_Txn_Rec which is an out parameter */
227 /* On Error: This procedure is built not to raise any exceptions, as no */
228 /* exceptions are expected in the body. */
229 /* Parameters Required: */
230 /* p_UpdateProductTrxn_Rec IN user input values are stored in this record */
231 /* x_Product_Txn_Rec IN OUT database values are stored in this record*/
232 /*---------------------------------------------------------------------------*/
233 Procedure Set_ProductTrxnRec_Flags(p_Upd_ProdTxn_Rec IN CSD_LOGISTICS_PUB.Upd_ProdTxn_Rec_Type,
234 x_Product_Txn_Rec IN OUT NOCOPY CSD_PROCESS_PVT.Product_Txn_Rec);
235
236 /*---------------------------------------------------------------------------*/
237 /* procedure name: Validate_ProductTrxnRec */
238 /* description : */
239 /* SU: This procedure is a helper routine to validate input values from */
240 /* record structure UpdateProductTrxn_Rec to make sure that values passed */
241 /* are valid values. This procedure should be called when it is determined */
245 /* X_Msg_Data will have a value if X_Msg_Count has value 1 */
242 /* that a specific attribute value can be changed by user. */
243 /* On Error: X_Return_Status variable will have the return status value */
244 /* X_Msg_Count will have the count of messages in message stack */
246 /* Parameters Required: */
247 /* p_UpdateProductTrxn_Rec IN user input values are stored in this record */
248 /* x_Product_Txn_Rec IN OUT database values are stored in this record*/
249 /* x_return_status OUT Standard API paramater */
250 /* x_msg_count OUT Standard API paramater */
251 /* x_msg_data OUT Standard API paramater */
252 /*---------------------------------------------------------------------------*/
253 PROCEDURE Validate_ProductTrxnRec(p_Upd_ProdTxn_Rec IN CSD_LOGISTICS_PUB.Upd_ProdTxn_Rec_Type,
254 p_Product_Txn_Rec IN Csd_Process_Pvt.Product_Txn_Rec,
255 x_return_status OUT NOCOPY VARCHAR2,
256 x_msg_count OUT NOCOPY NUMBER,
257 x_msg_data OUT NOCOPY VARCHAR2);
258
259 /*---------------------------------------------------------------------------*/
260 /* procedure name: Compare_ProductTrxnRec */
261 /* description : compares all the input values with database values */
262 /* SU: This API will compare user passed input values in record structure */
263 /* UpdateProductTrxn_Rec and Database values captured in Record structure */
264 /* Product_Txn_rec.This is because whether attributes values can be updated*/
265 /* depends on product transaction status value and action type values. */
266 /* These validations are done in the following API and error message is */
267 /* raised when an attribute value is not supposed to be changed. */
268 /* On Error : X_Return_Status variable will have the return status value */
269 /* X_Msg_Count will have the count of messages in message stack */
270 /* X_Msg_Data will have a value if X_Msg_Count has value 1 */
271 /* Parameters Required: */
272 /* p_UpdateProductTrxn_Rec IN user input values are stored in this record */
273 /* p_Product_Txn_Rec IN database values are stored in this record */
274 /* x_return_status OUT Standard API paramater */
275 /* x_msg_count OUT Standard API paramater */
276 /* x_msg_data OUT Standard API paramater */
277 /*---------------------------------------------------------------------------*/
278 PROCEDURE Compare_ProductTrxnRec(p_Upd_ProdTxn_Rec IN CSD_LOGISTICS_PUB.Upd_ProdTxn_Rec_Type,
279 p_Product_Txn_Rec IN Csd_Process_Pvt.Product_Txn_Rec,
280 x_return_status OUT NOCOPY VARCHAR2,
281 x_msg_count OUT NOCOPY NUMBER,
282 x_msg_data OUT NOCOPY VARCHAR2);
283
284 /*---------------------------------------------------------------------------*/
285 /* procedure name: Get_ProdTrxnStatus_Meaning */
286 /* description : gets prod txn status meaning for a prod txn status code */
287 /* in fnd lookups */
288 /* Parameters Required: */
289 /* p_ProdTxnStatus_Code IN Lookup code for product transaction status */
290 /*---------------------------------------------------------------------------*/
291 FUNCTION Get_ProdTrxnStatus_Meaning(p_ProdTxnStatus_Code IN VARCHAR2)
292 RETURN VARCHAR2;
293
294 /*---------------------------------------------------------------------------*/
295 /* procedure name: Get_ItemAttributes */
296 /* description : */
297 /* SU: Gets item attributes like serial number control code, revision */
298 /* qty control code, lot number control code, IB Flag for a givent item */
299 /* in service validation organzation */
300 /* Parameters Required: */
301 /* p_Inventory_Item_Id IN Item identifier */
302 /* x_ItemAttributes OUT returned values include serial_code, */
303 /* Revision_Code, Lot_Code and IB_Flag for a given Item */
304 /*---------------------------------------------------------------------------*/
305 PROCEDURE Get_ItemAttributes(p_Inventory_Item_Id IN NUMBER,
306 p_inv_org_id IN NUMBER,
307 x_ItemAttributes OUT NOCOPY ItemAttributes_Rec_Type);
308
309
310 /*---------------------------------------------------------------------------*/
311 /* procedure name: interface_prodtxn */
312 /* description : */
313 /* interfaces a given product transaction including all the prod txns */
314 /* under that incident id. */
315 /* Parameters Required: */
316 /* p_product_txn_id IN product transaction record */
317 /* x_return_status OUT return status */
318 /*---------------------------------------------------------------------------*/
319 procedure interface_prodtxn
320 (
321 x_return_status OUT NOCOPY VARCHAR2,
325 );
322 p_product_txn_rec IN CSD_PROCESS_PVT.PRODUCT_TXN_REC,
323 p_prodtxn_db_attr IN CSD_LOGISTICS_UTIL.PRODTXN_DB_ATTR_REC,
324 px_order_rec IN OUT NOCOPY csd_process_pvt.om_interface_rec
326
327 /*------------------------------------------------------------------------*/
328 /* procedure name: book_prodtxn */
329 /* description : */
330 /* Books the prod txn record in Depot schema */
331 /* Parameters Required: */
332 /* p_product_txn_rec IN product transaction record */
333 /* x_return_status OUT return status */
334 /*------------------------------------------------------------------------*/
335 procedure book_prodtxn
336 (
337 x_return_status OUT NOCOPY VARCHAR2,
338 p_product_txn_rec IN CSD_PROCESS_PVT.PRODUCT_TXN_REC,
339 p_prodtxn_db_attr IN CSD_LOGISTICS_UTIL.PRODTXN_DB_ATTR_REC,
340 px_order_rec IN OUT NOCOPY csd_process_pvt.om_interface_rec
341
342 );
343
344 /*------------------------------------------------------------------------*/
345 /* procedure name: upd_prodtxn_n_chrgline */
346 /* description : */
347 /* Updates the prod txn record in Depot schema and charge line */
348 /* Parameters Required: */
349 /* p_product_txn_rec IN product transaction record */
350 /* x_return_status OUT return status */
351 /*------------------------------------------------------------------------*/
352 procedure upd_prodtxn_n_chrgline
353 (
354 p_product_txn_rec IN OUT NOCOPY CSD_PROCESS_PVT.PRODUCT_TXN_REC,
355 p_prodtxn_db_attr IN CSD_LOGISTICS_UTIL.PRODTXN_DB_ATTR_REC,
356 x_estimate_detail_id OUT NOCOPY NUMBER,
357 x_repair_line_id OUT NOCOPY NUMBER,
358 x_add_to_order_flag OUT NOCOPY VARCHAR2,
359 x_add_to_order_id OUT NOCOPY NUMBER,
360 x_transaction_type_id OUT NOCOPY NUMBER
361 ) ;
362
363
364 FUNCTION get_prodtxn_db_attr (p_product_txn_id IN NUMBER)
365 RETURN CSD_LOGISTICS_UTIL.PRODTXN_DB_ATTR_REC;
366
367
368 FUNCTION get_order_rec (p_repair_line_id IN NUMBER)
369 RETURN csd_process_pvt.om_interface_rec;
370
371
372 /*------------------------------------------------------------------------*/
373 /* procedure name: pickrelease_prodtxn */
374 /* description : */
375 /* pick releases the prod txn record in Depot schema */
376 /* Parameters Required: */
377 /* p_product_txn_rec IN product transaction record */
378 /* */
379 /*------------------------------------------------------------------------*/
380 procedure pickrelease_prodtxn
381 (
382 x_return_status OUT NOCOPY VARCHAR2,
383 p_product_txn_rec IN CSD_PROCESS_PVT.PRODUCT_TXN_REC,
384 p_prodtxn_db_attr IN CSD_LOGISTICS_UTIL.PRODTXN_DB_ATTR_REC,
385 px_order_rec IN OUT NOCOPY csd_process_pvt.om_interface_rec
386 );
387
388 /*------------------------------------------------------------------------*/
389 /* procedure name: ship_prodtxn */
390 /* description : */
391 /* ships the prod txn record */
392 /* Parameters Required: */
393 /* p_product_txn_rec IN product transaction record */
394 /* x_return_status OUT return status */
395 /*------------------------------------------------------------------------*/
396 procedure ship_prodtxn
397 (
398 x_return_status OUT NOCOPY VARCHAR2,
399 p_product_txn_rec IN CSD_PROCESS_PVT.PRODUCT_TXN_REC,
400 p_prodtxn_db_attr IN CSD_LOGISTICS_UTIL.PRODTXN_DB_ATTR_REC,
401 px_order_rec IN OUT NOCOPY csd_process_pvt.om_interface_rec
402 );
403
404
405 /*------------------------------------------------------------------------*/
406 /* procedure name: cancel_prodtxn */
407 /* description : */
408 /* Cancels the prod txn record */
409 /* Parameters Required: */
410 /* p_order_header_id IN order header id */
411 /* p_order_line_id IN order line id */
412 /*------------------------------------------------------------------------*/
413 PROCEDURE cancel_prodtxn
414 ( p_api_version IN NUMBER,
415 p_commit IN VARCHAR2,
416 p_init_msg_list IN VARCHAR2,
417 x_return_status OUT NOCOPY VARCHAR2,
418 x_msg_count OUT NOCOPY NUMBER,
419 x_msg_data OUT NOCOPY VARCHAR2,
420 p_prod_txn_id IN NUMBER,
421 p_order_header_id IN NUMBER,
422 p_order_line_id IN NUMBER
423 );
424
425 /*------------------------------------------------------------------------*/
426 /* procedure name: Reserve_Serial_Number */
427 /* description : */
428 /* Reserves a given serial numbers for the given order */
432 /*------------------------------------------------------------------------*/
429 /* Parameters Required: */
430 /* p_serial_reserve_rec IN CSD_SERIAL_RESERVE_REC_TYPE */
431 /* p_return_status OUT VARCHAR2(1) */
433 PROCEDURE Reserve_Serial_Number
434 ( p_serial_reserve_Rec IN CSD_SERIAL_RESERVE_REC_TYPE,
435 x_return_status OUT NOCOPY VARCHAR2
436 );
437
438 /*------------------------------------------------------------------------*/
439 /* procedure name: Unreserve_Serial_Number */
440 /* description : */
441 /* Removes a reservation for the given order */
442 /* Parameters Required: */
443 /* p_serial_reserve_rec IN CSD_SERIAL_RESERVE_REC_TYPE */
444 /* p_return_status OUT VARCHAR2(1) */
445 /*------------------------------------------------------------------------*/
446 PROCEDURE Unreserve_Serial_Number
447 ( p_serial_reserve_Rec IN CSD_SERIAL_RESERVE_REC_TYPE,
448 x_return_status OUT NOCOPY VARCHAR2
449 );
450
451 END Csd_Logistics_Util;
[Home] [Help]