1 PACKAGE DPP_UTILITY_PVT as
2 /* $Header: dppvutls.pls 120.8 2008/05/16 06:18:26 sdasan noship $ */
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(40),
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(40),
84 DESCRIPTION VARCHAR2(240),
85 vendor_part_no VARCHAR2(40)
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_warehouses(
216 p_search_criteria IN search_criteria_tbl_type
217 ,x_warehouse_tbl OUT NOCOPY warehouse_tbl_type
218 ,x_rec_count OUT NOCOPY NUMBER
219 ,x_return_status OUT NOCOPY VARCHAR2
220 );
221
222 PROCEDURE Get_CoveredInventory(
223 p_hdr_rec IN dpp_inv_hdr_rec_type
224 ,p_covered_inv_tbl IN OUT NOCOPY dpp_inv_cov_tbl_type
225 ,x_return_status OUT NOCOPY VARCHAR2
226 );
227
228 PROCEDURE Get_InventoryDetails(
229 p_hdr_rec IN dpp_inv_hdr_rec_type
230 ,p_inventorydetails_tbl IN OUT NOCOPY inventorydetails_tbl_type
231 ,x_rec_count OUT NOCOPY NUMBER
232 ,x_return_status OUT NOCOPY VARCHAR2
233 );
234
235 PROCEDURE Get_CustomerInventory(
236 p_hdr_rec IN dpp_inv_hdr_rec_type
237 ,p_cust_inv_tbl IN OUT NOCOPY dpp_cust_inv_tbl_type
238 ,x_rec_count OUT NOCOPY NUMBER
239 ,x_return_status OUT NOCOPY VARCHAR2
240 );
241
242 PROCEDURE search_customers(
243 p_search_criteria IN search_criteria_tbl_type
244 ,x_customer_tbl OUT NOCOPY customer_tbl_type
245 ,x_rec_count OUT NOCOPY NUMBER
246 ,x_return_status OUT NOCOPY VARCHAR2
247 );
248
249 PROCEDURE Get_LastPrice(
250 p_hdr_rec IN dpp_inv_hdr_rec_type
251 ,p_cust_price_tbl IN OUT NOCOPY dpp_cust_price_tbl_type
252 ,x_rec_count OUT NOCOPY NUMBER
253 ,x_return_status OUT NOCOPY VARCHAR2
254 );
255
256 PROCEDURE Get_ListPrice(
257 p_hdr_rec IN dpp_inv_hdr_rec_type
258 ,p_listprice_tbl IN OUT NOCOPY dpp_list_price_tbl_type
259 ,x_rec_count OUT NOCOPY NUMBER
260 ,x_return_status OUT NOCOPY VARCHAR2
261 );
262
263 PROCEDURE Get_Vendor(
264 p_vendor_rec IN OUT NOCOPY vendor_rec_type
265 ,x_rec_count OUT NOCOPY NUMBER
266 ,x_return_status OUT NOCOPY VARCHAR2
267 );
268
269 PROCEDURE Get_Vendor_Site(
270 p_vendor_site_rec IN OUT NOCOPY vendor_site_rec_type
271 ,x_rec_count OUT NOCOPY NUMBER
272 ,x_return_status OUT NOCOPY VARCHAR2
273 );
274
275 PROCEDURE Get_Vendor_Contact(
276 p_vendor_contact_rec IN OUT NOCOPY vendor_contact_rec_type
277 ,x_rec_count OUT NOCOPY NUMBER
278 ,x_return_status OUT NOCOPY VARCHAR2
279 );
280
281 PROCEDURE Get_Warehouse(
282 p_warehouse_tbl IN OUT NOCOPY warehouse_tbl_type
283 ,x_rec_count OUT NOCOPY NUMBER
284 ,x_return_status OUT NOCOPY VARCHAR2
285 );
286
287 PROCEDURE Get_Customer(
288 p_customer_tbl IN OUT NOCOPY customer_tbl_type
289 ,x_rec_count OUT NOCOPY NUMBER
290 ,x_return_status OUT NOCOPY VARCHAR2
291 );
292
293 PROCEDURE Get_Product(
294 p_item_tbl IN OUT NOCOPY item_tbl_type
295 ,p_org_id IN NUMBER
296 ,x_rec_count OUT NOCOPY NUMBER
297 ,x_return_status OUT NOCOPY VARCHAR2
298 );
299
300
301 --To be used incase we are storing the log messages in the fnd_log_messages table
302 --Currently all debug messages are going into the DPP_LOG_MESSAGES table
303
304 /*PROCEDURE debug_message (p_log_level IN NUMBER,
305 p_module_name IN VARCHAR2,
306 p_text IN VARCHAR2);*/
307
308 PROCEDURE debug_message(
309 p_message_text IN VARCHAR2,
310 p_message_level IN NUMBER := NULL
311 );
312
313 PROCEDURE error_message(
314 p_message_name VARCHAR2,
315 p_token_name VARCHAR2 := NULL,
316 P_token_value VARCHAR2 := NULL
317 );
318
319 PROCEDURE get_EmailAddress(
320 p_user_id IN NUMBER
321 ,x_email_address OUT NOCOPY VARCHAR2
322 ,x_return_status OUT NOCOPY VARCHAR2
323 );
324
325 ---------------------------------------------------------------------
326 -- PROCEDURE
327 -- Convert_Currency
328 -- NOTE
329
330 -- HISTORY
331 --parameter p_from_currency IN VARCHAR2,
332 -- p_to_currency IN VARCHAR2,
333 -- p_conv_date IN DATE DEFAULT SYSDATE,
334 -- p_from_amount IN NUMBER,
335 -- x_to_amount OUT NUMBER
336 -- If x_conversion_type = 'User', and the relationship between the
337 -- two currencies is not fixed, x_user_rate will be used as the
338 -- conversion rate to convert the amount
339 -- else no_user_rate is required
340 ---------------------------------------------------------------------
341
342 PROCEDURE convert_currency(
343 p_from_currency IN VARCHAR2
344 ,p_to_currency IN VARCHAR2
345 ,p_conv_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
346 ,p_conv_rate IN NUMBER DEFAULT FND_API.G_MISS_NUM
347 ,p_conv_date IN DATE DEFAULT SYSDATE
348 ,p_from_amount IN NUMBER
349 ,x_return_status OUT NOCOPY VARCHAR2
350 ,x_to_amount OUT NOCOPY NUMBER
351 ,x_rate OUT NOCOPY NUMBER);
352
353 ---------------------------------------------------------------------
354 -- PROCEDURE
355 -- Convert_functional_Curr
356 -- NOTE
357 -- This procedures takes in amount and converts it to the functional currency
358 -- and returns the converted amount,exchange_rate,set_of_book_id,
359 -- f-nctional_currency_code,exchange_rate_date
360
361 -- HISTORY
362
363 --parameter x_Amount1 IN OUT NUMBER -- reqd Parameter -- amount to be converted
364 -- x_TC_CURRENCY_CODE IN OUT VARCHAR2,
365 -- x_Set_of_books_id OUT NUMBER,
366 -- x_MRC_SOB_TYPE_CODE OUT NUMBER, 'P' and 'R'
367 -- We only do it for primary ('P' because we donot supprot MRC)
368 -- x_FC_CURRENCY_CODE OUT VARCHAR2,
369 -- x_EXCHANGE_RATE_TYPE OUT VARCHAR2,
370 -- comes from a DPP profile or what ever is passed
371 -- x_EXCHANGE_RATE_DATE OUT DATE,
372 -- could come from a DPP profile but right now is sysdate
373 -- x_EXCHANGE_RATE OUT VARCHAR2,
374 -- x_return_status OUT VARCHAR2
375 -- The following is the rule in the GL API
376 -- If x_conversion_type = 'User', and the relationship between the
377 -- two currencies is not fixed, x_user_rate will be used as the
378 -- conversion rate to convert the amount
379 -- else no_user_rate is required
380
381 ---------------------------------------------------------------------
382
383
384 PROCEDURE calculate_functional_curr(
385 p_from_amount IN NUMBER
386 ,p_conv_date IN DATE DEFAULT SYSDATE
387 ,p_tc_currency_code IN VARCHAR2
388 ,p_org_id IN NUMBER DEFAULT NULL
389 ,x_to_amount OUT NOCOPY NUMBER
390 ,x_set_of_books_id OUT NOCOPY NUMBER
391 ,x_mrc_sob_type_code OUT NOCOPY VARCHAR2
392 ,x_fc_currency_code OUT NOCOPY VARCHAR2
393 ,x_exchange_rate_type IN OUT NOCOPY VARCHAR2
394 ,x_exchange_rate IN OUT NOCOPY NUMBER
395 ,x_return_status OUT NOCOPY VARCHAR2);
396
397 PROCEDURE check_Transaction(
398 p_transaction_header_id IN NUMBER
399 ,p_status_change IN VARCHAR2
400 ,x_rec_count OUT NOCOPY NUMBER
401 ,x_msg_data OUT NOCOPY VARCHAR2
402 ,x_return_status OUT NOCOPY VARCHAR2);
403 END DPP_UTILITY_PVT;
404
[Home] [Help]