1 PACKAGE DPP_UTILITY_PVT AUTHID CURRENT_USER as
2 /* $Header: dppvutls.pls 120.15 2011/09/13 09:05:31 rvkondur ship $ */
3
4 ------------------------------------------------------------------------------
5 -- HISTORY
6 -- 24-Aug-2007 JAJOSE Creation
7
8 ------------------------------------------------------------------------------
9 DPP_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
10 DPP_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
11 DPP_DEBUG_MEDIUM_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
12
13 resource_locked EXCEPTION;
14 pragma EXCEPTION_INIT(resource_locked, -54);
15
16 TYPE search_criteria_rec_type IS RECORD
17 (
18 search_criteria VARCHAR2(50), -- contains criteria name
19 search_text VARCHAR2(255) -- contains criteria value
20 );
21 TYPE search_criteria_tbl_type IS TABLE OF search_criteria_rec_type INDEX BY BINARY_INTEGER;
22
23
24 TYPE vendor_rec_type IS RECORD
25 (
26 vendor_id NUMBER,
27 vendor_number VARCHAR2(30),
28 vendor_name VARCHAR2(240)
29 );
30
31 TYPE vendor_tbl_type IS TABLE OF vendor_rec_type INDEX BY BINARY_INTEGER;
32
33 TYPE vendor_site_rec_type IS RECORD
34 (
35 vendor_id NUMBER,
36 vendor_site_id NUMBER,
37 vendor_site_code VARCHAR2(15),
38 address_line1 VARCHAR2(240),
39 address_line2 VARCHAR2(240),
40 address_line3 VARCHAR2(240),
41 city VARCHAR2(25),
42 state VARCHAR2(150),
43 zip VARCHAR2(20),
44 country VARCHAR2(25)
45 );
46
47 TYPE vendor_site_tbl_type IS TABLE OF vendor_site_rec_type INDEX BY BINARY_INTEGER;
48
49 TYPE vendor_contact_rec_type IS RECORD
50 (
51 vendor_site_id NUMBER,
52 vendor_contact_id NUMBER,
53 contact_first_name VARCHAR2(15),
54 contact_middle_name VARCHAR2(15),
55 contact_last_name VARCHAR2(20),
56 contact_phone VARCHAR2(40),
57 contact_email_address VARCHAR2(2000),
58 contact_fax VARCHAR2(40)
59 );
60
61 TYPE vendor_contact_tbl_type IS TABLE OF vendor_contact_rec_type INDEX BY BINARY_INTEGER;
62
63 TYPE customer_rec_type IS RECORD
64 (
65 customer_id NUMBER,
66 customer_number VARCHAR2(30),
67 customer_name VARCHAR2(360)
68 );
69
70 TYPE customer_tbl_type IS TABLE OF customer_rec_type INDEX BY BINARY_INTEGER;
71
72 TYPE item_rec_type IS RECORD
73 (
74 inventory_item_id NUMBER,
75 item_number VARCHAR2(240),
76 DESCRIPTION VARCHAR2(240)
77 );
78
79 TYPE item_tbl_type IS TABLE OF item_rec_type INDEX BY BINARY_INTEGER;
80 TYPE itemnum_rec_type IS RECORD
81 (
82 inventory_item_id NUMBER,
83 item_number VARCHAR2(240),
84 DESCRIPTION VARCHAR2(240),
85 vendor_part_no VARCHAR2(240)
86 );
87
88 TYPE itemnum_tbl_type IS TABLE OF itemnum_rec_type INDEX BY BINARY_INTEGER;
89
90
91 TYPE warehouse_rec_type IS RECORD
92 (
93 warehouse_id NUMBER,
94 warehouse_code VARCHAR2(3),
95 Warehouse_Name VARCHAR2(240)
96 );
97
98 TYPE warehouse_tbl_type IS TABLE OF warehouse_rec_type INDEX BY BINARY_INTEGER;
99
100 TYPE dpp_inv_hdr_rec_type IS RECORD
101 (
102 org_id NUMBER,
103 effective_start_date DATE,
104 effective_end_date DATE,
105 currency_code VARCHAR2(15)
106 );
107
108 TYPE dpp_inv_cov_rct_rec_type IS RECORD
109 (
110 Date_Received DATE,
111 Onhand_quantity NUMBER
112 );
113
114 TYPE dpp_inv_cov_rct_tbl_type IS TABLE OF dpp_inv_cov_rct_rec_type INDEX BY BINARY_INTEGER;
115
116 TYPE dpp_inv_cov_wh_rec_type IS RECORD
117 (
118 warehouse_id NUMBER,
119 Warehouse_Name VARCHAR2(240),
120 Covered_quantity NUMBER,
121 rct_line_tbl dpp_inv_cov_rct_tbl_type
122 );
123
124 TYPE dpp_inv_cov_wh_tbl_type IS TABLE OF dpp_inv_cov_wh_rec_type INDEX BY BINARY_INTEGER;
125
126 TYPE dpp_inv_cov_rec_type IS RECORD
127 (
128 Transaction_Line_Id NUMBER,
129 Inventory_ITem_ID NUMBER,
130 UOM_Code VARCHAR2(3),
131 Onhand_Quantity NUMBER,
132 Covered_quantity NUMBER,
133 wh_line_tbl dpp_inv_cov_wh_tbl_type
134 );
135
136 TYPE dpp_inv_cov_tbl_type IS TABLE OF dpp_inv_cov_rec_type INDEX BY BINARY_INTEGER;
137
138 TYPE inventorydetails_rec_type IS RECORD
139 (
140 Transaction_Line_Id NUMBER,
141 Inventory_Item_ID NUMBER,
142 UOM_Code VARCHAR2(3),
143 Onhand_Quantity NUMBER,
144 Covered_quantity NUMBER
145 );
146
147 TYPE inventorydetails_tbl_type IS TABLE OF inventorydetails_rec_type INDEX BY BINARY_INTEGER;
148
149 TYPE dpp_cust_inv_rec_type IS RECORD
150 (
151 Customer_ID NUMBER,
152 Inventory_Item_ID NUMBER,
153 UOM_Code VARCHAR2(3),
154 Onhand_Quantity NUMBER
155 );
156
157 TYPE dpp_cust_inv_tbl_type IS TABLE OF dpp_cust_inv_rec_type INDEX BY BINARY_INTEGER;
158
159 TYPE dpp_cust_price_rec_type IS RECORD
160 (
161 Customer_ID NUMBER,
162 Inventory_Item_ID NUMBER,
163 UOM_Code VARCHAR2(15),
164 Last_Price NUMBER,
165 invoice_currency_code VARCHAR2(15),
166 price_change NUMBER,
167 converted_price_change NUMBER
168 );
169
170 TYPE dpp_cust_price_tbl_type IS TABLE OF dpp_cust_price_rec_type INDEX BY BINARY_INTEGER;
171
172 TYPE dpp_list_price_rec_type IS RECORD
173 (
174 Inventory_Item_ID NUMBER,
175 List_Price NUMBER
176 );
177
178 TYPE dpp_list_price_tbl_type IS TABLE OF dpp_list_price_rec_type INDEX BY BINARY_INTEGER;
179
180 PROCEDURE search_vendors(
181 p_search_criteria IN search_criteria_tbl_type
182 ,x_vendor_tbl OUT NOCOPY vendor_tbl_type
183 ,x_rec_count OUT NOCOPY NUMBER
184 ,x_return_status OUT NOCOPY VARCHAR2
185 );
186
187 PROCEDURE search_vendor_sites(
188 p_search_criteria IN search_criteria_tbl_type
189 ,x_vendor_site_tbl OUT NOCOPY vendor_site_tbl_type
190 ,x_rec_count OUT NOCOPY NUMBER
191 ,x_return_status OUT NOCOPY VARCHAR2
192 );
193
194 PROCEDURE search_vendor_contacts(
195 p_search_criteria IN search_criteria_tbl_type
196 ,x_vendor_contact_tbl OUT NOCOPY vendor_contact_tbl_type
197 ,x_rec_count OUT NOCOPY NUMBER
198 ,x_return_status OUT NOCOPY VARCHAR2
199 );
200
201 PROCEDURE search_items(
202 p_search_criteria IN search_criteria_tbl_type
203 ,x_item_tbl OUT NOCOPY itemnum_tbl_type
204 ,x_rec_count OUT NOCOPY NUMBER
205 ,x_return_status OUT NOCOPY VARCHAR2
206 );
207
208 PROCEDURE search_customer_items(
209 p_search_criteria IN search_criteria_tbl_type
210 ,x_customer_item_tbl OUT NOCOPY item_tbl_type
211 ,x_rec_count OUT NOCOPY NUMBER
212 ,x_return_status OUT NOCOPY VARCHAR2
213 );
214
215 PROCEDURE search_customer_items_all(
216 p_search_criteria IN search_criteria_tbl_type
217 ,x_customer_item_tbl OUT NOCOPY item_tbl_type
218 ,x_rec_count OUT NOCOPY NUMBER
219 ,x_return_status OUT NOCOPY VARCHAR2
220 );
221
222 PROCEDURE search_warehouses(
223 p_search_criteria IN search_criteria_tbl_type
224 ,x_warehouse_tbl OUT NOCOPY warehouse_tbl_type
225 ,x_rec_count OUT NOCOPY NUMBER
226 ,x_return_status OUT NOCOPY VARCHAR2
227 );
228
229 PROCEDURE Get_CoveredInventory(
230 p_hdr_rec IN dpp_inv_hdr_rec_type
231 ,p_covered_inv_tbl IN OUT NOCOPY dpp_inv_cov_tbl_type
232 ,x_return_status OUT NOCOPY VARCHAR2
233 );
234
235 PROCEDURE Get_InventoryDetails(
236 p_hdr_rec IN dpp_inv_hdr_rec_type
237 ,p_inventorydetails_tbl IN OUT NOCOPY inventorydetails_tbl_type
238 ,x_rec_count OUT NOCOPY NUMBER
239 ,x_return_status OUT NOCOPY VARCHAR2
240 );
241
242 PROCEDURE Get_CustomerInventory(
243 p_hdr_rec IN dpp_inv_hdr_rec_type
244 ,p_cust_inv_tbl IN OUT NOCOPY dpp_cust_inv_tbl_type
245 ,x_rec_count OUT NOCOPY NUMBER
246 ,x_return_status OUT NOCOPY VARCHAR2
247 );
248
249 PROCEDURE search_customers(
250 p_search_criteria IN search_criteria_tbl_type
251 ,x_customer_tbl OUT NOCOPY customer_tbl_type
252 ,x_rec_count OUT NOCOPY NUMBER
253 ,x_return_status OUT NOCOPY VARCHAR2
254 );
255
256 PROCEDURE search_customers_all(
257 p_search_criteria IN search_criteria_tbl_type
258 ,x_customer_tbl OUT NOCOPY customer_tbl_type
259 ,x_rec_count OUT NOCOPY NUMBER
260 ,x_return_status OUT NOCOPY VARCHAR2
261 );
262
263 PROCEDURE Get_LastPrice(
264 p_hdr_rec IN dpp_inv_hdr_rec_type
265 ,p_cust_price_tbl IN OUT NOCOPY dpp_cust_price_tbl_type
266 ,x_rec_count OUT NOCOPY NUMBER
267 ,x_return_status OUT NOCOPY VARCHAR2
268 );
269
270 PROCEDURE Get_ListPrice(
271 p_hdr_rec IN dpp_inv_hdr_rec_type
272 ,p_listprice_tbl IN OUT NOCOPY dpp_list_price_tbl_type
273 ,x_rec_count OUT NOCOPY NUMBER
274 ,x_return_status OUT NOCOPY VARCHAR2
275 );
276
277 PROCEDURE Get_Vendor(
278 p_vendor_rec IN OUT NOCOPY vendor_rec_type
279 ,x_rec_count OUT NOCOPY NUMBER
280 ,x_return_status OUT NOCOPY VARCHAR2
281 );
282
283 PROCEDURE Get_Vendor_Site(
284 p_vendor_site_rec IN OUT NOCOPY vendor_site_rec_type
285 ,x_rec_count OUT NOCOPY NUMBER
286 ,x_return_status OUT NOCOPY VARCHAR2
287 );
288
289 PROCEDURE Get_Vendor_Contact(
290 p_vendor_contact_rec IN OUT NOCOPY vendor_contact_rec_type
291 ,x_rec_count OUT NOCOPY NUMBER
292 ,x_return_status OUT NOCOPY VARCHAR2
293 );
294
295 PROCEDURE Get_Warehouse(
296 p_warehouse_tbl IN OUT NOCOPY warehouse_tbl_type
297 ,x_rec_count OUT NOCOPY NUMBER
298 ,x_return_status OUT NOCOPY VARCHAR2
299 );
300
301 PROCEDURE Get_Customer(
302 p_customer_tbl IN OUT NOCOPY customer_tbl_type
303 ,x_rec_count OUT NOCOPY NUMBER
304 ,x_return_status OUT NOCOPY VARCHAR2
305 );
306
307 PROCEDURE Get_Product(
308 p_item_tbl IN OUT NOCOPY item_tbl_type
309 ,p_org_id IN NUMBER
310 ,x_rec_count OUT NOCOPY NUMBER
311 ,x_return_status OUT NOCOPY VARCHAR2
312 );
313
314
315 --To be used incase we are storing the log messages in the fnd_log_messages table
316 --Currently all debug messages are going into the DPP_LOG_MESSAGES table
317
318 PROCEDURE debug_message (p_log_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
319 p_module_name IN VARCHAR2,
320 p_text IN VARCHAR2
321 );
322
323 -- Do Not use this in future
324 PROCEDURE debug_message(
325 p_message_text IN VARCHAR2,
326 p_message_level IN NUMBER := NULL
327 );
328
329
330 PROCEDURE error_message(
331 p_message_name VARCHAR2,
332 p_token_name VARCHAR2 := NULL,
333 P_token_value VARCHAR2 := NULL
334 );
335
336 PROCEDURE get_EmailAddress(
337 p_user_id IN NUMBER
338 ,x_email_address OUT NOCOPY VARCHAR2
339 ,x_return_status OUT NOCOPY VARCHAR2
340 );
341
342 ---------------------------------------------------------------------
343 -- PROCEDURE
344 -- Convert_Currency
345 -- NOTE
346
347 -- HISTORY
348 --parameter p_from_currency IN VARCHAR2,
349 -- p_to_currency IN VARCHAR2,
350 -- p_conv_date IN DATE DEFAULT SYSDATE,
351 -- p_from_amount IN NUMBER,
352 -- x_to_amount OUT NUMBER
353 -- If x_conversion_type = 'User', and the relationship between the
354 -- two currencies is not fixed, x_user_rate will be used as the
355 -- conversion rate to convert the amount
356 -- else no_user_rate is required
357 ---------------------------------------------------------------------
358
359 PROCEDURE convert_currency(
360 p_from_currency IN VARCHAR2
361 ,p_to_currency IN VARCHAR2
362 ,p_conv_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
363 ,p_conv_rate IN NUMBER DEFAULT FND_API.G_MISS_NUM
364 ,p_conv_date IN DATE DEFAULT SYSDATE
365 ,p_from_amount IN NUMBER
366 ,x_return_status OUT NOCOPY VARCHAR2
367 ,x_to_amount OUT NOCOPY NUMBER
368 ,x_rate OUT NOCOPY NUMBER);
369
370 ---------------------------------------------------------------------
371 -- PROCEDURE
372 -- Convert_functional_Curr
373 -- NOTE
374 -- This procedures takes in amount and converts it to the functional currency
375 -- and returns the converted amount,exchange_rate,set_of_book_id,
376 -- f-nctional_currency_code,exchange_rate_date
377
378 -- HISTORY
379
380 --parameter x_Amount1 IN OUT NUMBER -- reqd Parameter -- amount to be converted
381 -- x_TC_CURRENCY_CODE IN OUT VARCHAR2,
382 -- x_Set_of_books_id OUT NUMBER,
383 -- x_MRC_SOB_TYPE_CODE OUT NUMBER, 'P' and 'R'
384 -- We only do it for primary ('P' because we donot supprot MRC)
385 -- x_FC_CURRENCY_CODE OUT VARCHAR2,
386 -- x_EXCHANGE_RATE_TYPE OUT VARCHAR2,
387 -- comes from a DPP profile or what ever is passed
388 -- x_EXCHANGE_RATE_DATE OUT DATE,
389 -- could come from a DPP profile but right now is sysdate
390 -- x_EXCHANGE_RATE OUT VARCHAR2,
391 -- x_return_status OUT VARCHAR2
392 -- The following is the rule in the GL API
393 -- If x_conversion_type = 'User', and the relationship between the
394 -- two currencies is not fixed, x_user_rate will be used as the
395 -- conversion rate to convert the amount
396 -- else no_user_rate is required
397
398 ---------------------------------------------------------------------
399
400
401 PROCEDURE calculate_functional_curr(
402 p_from_amount IN NUMBER
403 ,p_conv_date IN DATE DEFAULT SYSDATE
404 ,p_tc_currency_code IN VARCHAR2
405 ,p_org_id IN NUMBER DEFAULT NULL
406 ,x_to_amount OUT NOCOPY NUMBER
407 ,x_set_of_books_id OUT NOCOPY NUMBER
408 ,x_mrc_sob_type_code OUT NOCOPY VARCHAR2
409 ,x_fc_currency_code OUT NOCOPY VARCHAR2
410 ,x_exchange_rate_type IN OUT NOCOPY VARCHAR2
411 ,x_exchange_rate IN OUT NOCOPY NUMBER
412 ,x_return_status OUT NOCOPY VARCHAR2);
413
414 PROCEDURE check_Transaction(
415 p_transaction_header_id IN NUMBER
416 ,p_status_change IN VARCHAR2
417 ,x_rec_count OUT NOCOPY NUMBER
418 ,x_msg_data OUT NOCOPY VARCHAR2
419 ,x_return_status OUT NOCOPY VARCHAR2);
420
421 --======================================================================
422 -- FUNCTION
423 -- CurrRound
424 --
425 -- PURPOSE
426 -- Returns the round value for an amount based on the currency
427 --
428 -- HISTORY
429 -- 12-Sep-2011 rvkondur Create.
430 --======================================================================
431 FUNCTION CurrRound(
432 p_amount IN NUMBER,
433 p_currency_code IN VARCHAR2
434 )
435 RETURN NUMBER;
436
437 END DPP_UTILITY_PVT;
438
[Home] [Help]