1 PACKAGE JMF_SHIKYU_UTIL AUTHID CURRENT_USER as
2 --$Header: JMFUSHKS.pls 120.9 2006/09/20 10:59:32 vchu noship $
3 --+===========================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, California, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| |
9 --| FILENAME : JMFUSHKS.pls |
10 --| |
11 --| DESCRIPTION: Package specification file for the Utility package |
12 --| of the Charge Based SHIKYU project. |
13 --| |
14 --| HISTORY: |
15 --| 29-APR-2005 vchu Created. |
16 --| 28-SEP-2005 vchu Modified signature of the |
17 --| Get_Shikyu_Component_Price procedure |
18 --| 03-OCT-2005 shu Added the debug_output procedure |
19 --| 21-OCT-2005 vchu Added the Get_Shikyu_Offset_Account |
20 --| procedure |
21 --| 26-JUN-2005 nesoni Function Get_Replenish_So_Returned_Qty is |
22 --| modified |
23 --| 30-AUG-2006 rajkrish Added a new procedure to clean up invalid |
24 --| data |
25 --| 08-SEP-2006 vchu Added the new function To_Xsd_Date_String |
26 --| to convert date values into XSD format so |
27 --| that they can be formatted correctly in XML |
28 --| Publisher Reports. |
29 --| 19-SEP-2006 vchu Modified the function To_Xsd_Date_String |
30 --| to take a second optional parameter that |
31 --| denotes the timezone of the offset to be |
32 --| attached to the DateTime string (either |
33 --| Server or Client). |
34 --| 20-SEP-2006 vchu Modified the function To_Xsd_Date_String |
35 --| to take a second optional parameter that |
36 --| denotes whether the time component of the |
37 --| Oracle Date should be omitted. No need for |
38 --| this function to optionally attach the User |
39 --| Timezone offset since it has been decided |
40 --| that UPTZ would not be supported for R12. |
41 --+===========================================================================+
42
43 --===================
44 -- GLOBALS
45 --===================
46
47 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JMF_SHIKYU_UTIL';
48 G_SLEEP_TIME NUMBER := 15;
49
50 TYPE g_request_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
51
52 --==============================
53 -- PROCEDURES/FUNCTIONS
54 --==============================
55
56 --===================================================================
57 -- PROCEDURE : clean_invalid_data
58 -- PARAMETERS :
59 -- COMMENTS : This procedure will clean/freeze the invalid data in the
60 -- SHIKYu tables if the data is corrupted
61 --=====================================================================
62
63 PROCEDURE clean_invalid_data ;
64
65
66
67 --========================================================================
68 -- FUNCTION : Get_Primary_Uom PUBLIC
69 -- PARAMETERS: p_inventory_item_id Item
70 -- p_organization_id Inventory Organization
71 -- COMMENT : This function returns the name of the primary UOM
72 -- of the item specified by the input parameters
73 --========================================================================
74
75 FUNCTION Get_Primary_Uom
76 ( p_inventory_item_id IN NUMBER
77 , p_organization_id IN NUMBER
78 )
79 RETURN VARCHAR2;
80
81 --========================================================================
82 -- FUNCTION : Get_Primary_Uom_Code PUBLIC
83 -- PARAMETERS: p_inventory_item_id Item
84 -- p_organization_id Inventory Organization
85 -- COMMENT : This function returns the code of the primary UOM
86 -- of the item specified by the input parameters
87 --========================================================================
88
89 FUNCTION Get_Primary_Uom_Code
90 ( p_inventory_item_id IN NUMBER
91 , p_organization_id IN NUMBER
92 )
93 RETURN VARCHAR2;
94
95 --========================================================================
96 -- FUNCTION : Get_Uom_Code PUBLIC
97 -- PARAMETERS: p_unit_of_measure Unit of Measure
98 -- COMMENT : This function converts an UOM name to the corresponding
99 -- UOM code
100 --========================================================================
101
102 FUNCTION Get_Uom_Code
103 ( p_unit_of_measure IN VARCHAR2
104 )
105 RETURN VARCHAR2;
106
107 --========================================================================
108 -- FUNCTION : Get_Uom_Conversion_Rate PUBLIC
109 -- PARAMETERS: p_inventory_item_id Inventory Item
110 -- p_organization_id Inventory Organization
111 -- COMMENT : This function returns UOM conversion rate
112 --========================================================================
113
114 FUNCTION Get_Uom_Conversion_Rate
115 ( P_from_unit VARCHAR2
116 , P_to_unit VARCHAR2
117 , P_item_id NUMBER
118 )
119 RETURN NUMBER;
120
121 --========================================================================
122 -- FUNCTION : Get_Replenish_So_Returned_Qty PUBLIC
123 -- PARAMETERS: p_replenishment_so_line_id Replenishment Sales Order line
124 -- COMMENT : This function calculates returned quantity in primary
125 -- UOM against Replenishment Sales Order Line
126 --========================================================================
127
128 FUNCTION Get_Replenish_So_Returned_Qty
129 ( p_replenishment_so_line_id NUMBER
130 )
131 RETURN NUMBER;
132
133 --========================================================================
134 -- FUNCTION : Get_Replenish_So_Received_Qty PUBLIC
135 -- PARAMETERS: p_replenishment_so_line_id Replenishment Sales Order line
136 -- COMMENT : This function calculates received quantity in TP Org primary
137 -- UOM against Replenishment Sales Order Line
138 --========================================================================
139
140 FUNCTION Get_Replenish_So_Received_Qty
141 ( p_replenishment_so_line_id IN NUMBER
142 )
143 RETURN NUMBER;
144
145 -----------------------------------------------------------------------
146 -- FUNCTION Get_Used_Quantity
147 -- Comments: This utility will return the component quantity that has been
148 -- currently issued for WIP job
149 ------------------------------------------------------------------------
150 FUNCTION Get_Used_Quantity
151 ( p_wip_entity_id IN NUMBER
152 , p_shikyu_component_id IN NUMBER
153 , p_organization_id IN NUMBER
154 )
155 RETURN NUMBER;
156
157 -----------------------------------------------------------------
158 --- FUNCTION Get_Primary_Quantity
159 -- Comments: This utility will convert the PO UOM qty into
160 -- Primary qty
161 --------------------------------------------------------------------------------
162 FUNCTION Get_Primary_Quantity
163 ( p_purchasing_UOM IN VARCHAR2
164 , p_quantity IN NUMBER
165 , P_inventory_org_id IN NUMBER
166 , p_inventory_item_id IN NUMBER
167 )
168 RETURN NUMBER;
169
170 ----------------------------------------------------------------
171 --FUNCTION Get_Final_Ship_Date
172 --Comments: This utility returns the ship date with the lead intransit
173 -- days included
174 -----------------------------------------------------------------
175 FUNCTION Get_Final_Ship_Date
176 ( p_oem_organization IN NUMBER
177 , p_tp_organization IN NUMBER
178 , p_scheduled_ship_date IN DATE
179 )
180 RETURN DATE;
181
182 ----------------------------------------------------------
183 -- FUNCTION Get_Allocation_Date
184 -- Comments: This utility returns the allocation need by date
185 -- based on a WIP entity job
186 ---------------------------------------------------------
187 FUNCTION Get_Allocation_Date
188 ( p_wip_entity_id IN NUMBER
189 )
190 RETURN DATE;
191
192 PROCEDURE Get_Subcontract_Order_Org_Ids
193 ( p_subcontract_po_shipment_id NUMBER
194 , x_oem_organization_id OUT NOCOPY NUMBER
195 , x_tp_organization_id OUT NOCOPY NUMBER
196 );
197
198 --=============================================================================
199 -- PROCEDURE NAME: To_Xsd_Date_String
200 -- TYPE : PUBLIC
201 -- PARAMETERS :
202 -- p_date Oracle Date to be converted to XSD Date Format
203 -- p_omit_time Denotes whether the time component of the Oracle Date
204 -- should be omitted
205 -- should be for the Server ('S') or Client timezone ('C')
206 -- RETURN : A String representing the passed in Date in XSD Date Format
207 -- DESCRIPTION : Convert an Oracle DB Date Object to a date string represented
208 -- in the XSD Date Format. This is mainly for use by the
209 -- XML Publisher Reports.
210 -- EXCEPTIONS :
211 --
212 -- CHANGE HISTORY: 07-SEP-06 VCHU Created.
213 --=============================================================================
214
215 FUNCTION To_Xsd_Date_String
216 ( p_date IN DATE
217 , p_omit_time IN VARCHAR2 DEFAULT 'N'
218 )
219 RETURN VARCHAR2;
220
221 PROCEDURE Get_Shikyu_Attributes
222 ( p_organization_id IN NUMBER
223 , p_item_id IN NUMBER
224 , x_outsourced_assembly OUT NOCOPY NUMBER
225 , x_subcontracting_component OUT NOCOPY NUMBER
226 , p_primary_uom_price OUT NOCOPY NUMBER
227 );
228
229 PROCEDURE Get_Shikyu_Component_Price
230 ( p_subcontract_po_shipment_id IN NUMBER
231 , p_shikyu_component_id IN NUMBER
232 , x_component_uom OUT NOCOPY VARCHAR2
233 , x_component_price OUT NOCOPY NUMBER
234 , x_primary_uom OUT NOCOPY VARCHAR2
235 , x_primary_uom_price OUT NOCOPY NUMBER
236 );
237
238 PROCEDURE Get_Replen_Po_Allocated_Qty
239 ( p_replen_po_shipment_id IN NUMBER
240 , x_allocated_primary_uom_qty OUT NOCOPY NUMBER
241 , x_primary_uom OUT NOCOPY VARCHAR2
242 );
243
244 PROCEDURE Get_Replen_Po_Ordered_Qty
245 ( p_replen_po_shipment_id IN NUMBER
246 , x_ordered_primary_uom_qty OUT NOCOPY NUMBER
247 , x_primary_uom OUT NOCOPY VARCHAR2
248 );
249
250 PROCEDURE Get_Replen_So_Allocated_Qty
251 ( p_replen_so_line_id IN NUMBER
252 , x_allocated_qty OUT NOCOPY NUMBER
253 , x_uom OUT NOCOPY VARCHAR2
254 , x_allocated_primary_uom_qty OUT NOCOPY NUMBER
255 , x_primary_uom OUT NOCOPY VARCHAR2
256 );
257
258 -- RETURN the quantity in primary UOM if the actual
259 -- parameter for p_uom is NULL
260 FUNCTION Get_Subcontract_Allocated_Qty
261 ( p_subcontract_po_shipment_id IN NUMBER
262 , p_component_id IN NUMBER
263 )
264 RETURN NUMBER;
265
266 PROCEDURE Get_Replenishment_So_Price
267 ( p_replenishment_so_line_id IN NUMBER
268 , x_uom OUT NOCOPY VARCHAR2
269 , x_price OUT NOCOPY NUMBER
270 );
271
272 --========================================================================
273 -- PROCEDURE : debug_output PUBLIC
274 -- PARAMETERS: p_output_to Identifier of where to output to
275 -- p_api_name Name of the api being called
276 -- p_message Output message
277 -- COMMENT : For outputting messages to FND Log, Concurrent Request Log
278 -- or Concurrent Request Output File.
279 -- PRE-COND :
280 -- EXCEPTIONS:
281 --========================================================================
282 PROCEDURE debug_output
283 ( p_output_to IN VARCHAR2
284 , p_api_name IN VARCHAR2
285 , p_message IN VARCHAR2
286 );
287
288 --===========================================================================
289 -- API NAME : Get_Shikyu_Offset_Account
290 --
291 -- DESCRIPTION:
292 --
293 -- PARAMETERS :
294 -- IN :
295 -- OUT :
296 --
297 -- CHANGE HISTORY: 21-Oct-05 VCHU Created.
298 --===========================================================================
299 PROCEDURE Get_Shikyu_offset_Account
300 ( p_po_shipment_id IN NUMBER
301 , x_offset_account OUT NOCOPY NUMBER
302 );
303
304 /* Batch Processing procedures */
305
306 --========================================================================
307 -- PROCEDURE : Submit_Worker PUBLIC
308 -- PARAMETERS: p_batch_id IN NUMBER Batch reference that identifies set
309 -- of rows to be processed
310 -- p_request_count IN NUMBER Max number of workers allowed
314 -- concurrent request IDs of all the active workers
311 -- p_cp_short_name IN VARCHAR2 Short name of concurrent program
312 -- p_cp_product_code IN VARCHAR2 Owning product of concurrent program
313 -- x_workers IN OUT Table (of type g_request_tbl_type) containing the
315 -- x_request_id OUT NUMBER It returns Concurrent Request ID which is
316 -- submitted recently.
317 -- x_return_status OUT NUMBER Return Status
318 -- COMMENT : This generic procedure is called to submit concurrent requests.
319 -- It returns a table containing list of active workers. This accepts
320 -- batch id as one argument to concurrent program.
321 --========================================================================
322 PROCEDURE Submit_Worker
323 ( p_batch_id IN NUMBER
324 , p_request_count IN NUMBER
325 , p_cp_short_name IN VARCHAR2
326 , p_cp_product_code IN VARCHAR2
327 , x_workers IN OUT NOCOPY g_request_tbl_type
328 , x_request_id OUT NOCOPY NUMBER
329 , x_return_status OUT NOCOPY VARCHAR2
330 );
331
332 --========================================================================
333 -- FUNCTION : Has_Worker_Completed PUBLIC
334 -- PARAMETERS: p_request_id IN NUMBER Unique identifier of a concurrent request.
335 -- RETURNS : BOOLEAN
336 -- COMMENT : This function accepts a unique identifier of concurrent request
337 -- and it returns boolean value. It returns TRUE if the corresponding worker
338 -- has completed, otherwise FALSE.
339 --=========================================================================
340 FUNCTION Has_worker_completed
341 ( p_request_id IN NUMBER
342 )
343 RETURN BOOLEAN;
344
345
346 --========================================================================
347 -- PROCEDURE : Wait_For_Worker PUBLIC
348 -- PARAMETERS: p_workers IN Required
349 -- Table (of type g_request_tbl_type) containing the
350 -- concurrent request IDs of all the active workers
351 -- x_worker_idx OUT Index of the worker (within the p_workers table)
352 -- whose current task has completed and can start
353 -- a new concurrent request.
354 -- COMMENT : This procedure polls submitted workers and suspend
355 -- the program till the completion of one of them; it returns
356 -- the completed worker through x_worker_idx
357 --=========================================================================
358 PROCEDURE Wait_for_worker
359 ( p_workers IN g_request_tbl_type
360 , x_worker_idx OUT NOCOPY BINARY_INTEGER
361 );
362
363
364 --========================================================================
365 -- PROCEDURE : Wait_For_All_Workers PUBLIC
366 -- PARAMETERS: p_workers IN Required
367 -- Table (of type g_request_tbl_type) containing the
368 -- concurrent request IDs of all the active workers
369 -- COMMENT : This procedure polls submitted workers and suspend
370 -- the program till completion of all of workers.
371 --=========================================================================
372 PROCEDURE wait_for_all_workers
373 ( p_workers IN g_request_tbl_type
374 );
375
376 END JMF_SHIKYU_UTIL;
[Home] [Help]