1 PACKAGE DPP_UIWRAPPER_PVT AUTHID CURRENT_USER as
2 /* $Header: dppvuiws.pls 120.11.12010000.6 2010/03/26 12:23:44 rvkondur ship $ */
3
4 DPP_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
5 DPP_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
6 DPP_DEBUG_MEDIUM_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
7
8 resource_locked EXCEPTION;
9 pragma EXCEPTION_INIT(resource_locked, -54);
10
11 TYPE search_criteria_rec_type IS RECORD
12 (
13 search_criteria VARCHAR2(50), -- contains criteria name
14 search_text VARCHAR2(255) -- contains criteria value
15 );
16 TYPE search_criteria_tbl_type IS TABLE OF search_criteria_rec_type INDEX BY BINARY_INTEGER;
17
18 TYPE vendor_rec_type IS RECORD
19 (
20 vendor_id NUMBER,
21 vendor_number VARCHAR2(30),
22 vendor_name VARCHAR2(240)
23 );
24
25 TYPE vendor_tbl_type IS TABLE OF vendor_rec_type INDEX BY BINARY_INTEGER;
26
27 TYPE vendor_site_rec_type IS RECORD
28 (
29 vendor_id NUMBER,
30 vendor_site_id NUMBER,
31 vendor_site_code VARCHAR2(15),
32 address_line1 VARCHAR2(240),
33 address_line2 VARCHAR2(240),
34 address_line3 VARCHAR2(240),
35 city VARCHAR2(25),
36 state VARCHAR2(150),
37 zip VARCHAR2(20),
38 country VARCHAR2(25)
39 );
40
41 TYPE vendor_site_tbl_type IS TABLE OF vendor_site_rec_type INDEX BY BINARY_INTEGER;
42
43 TYPE vendor_contact_rec_type IS RECORD
44 (
45 vendor_site_id NUMBER,
46 vendor_contact_id NUMBER,
47 contact_first_name VARCHAR2(15),
48 contact_middle_name VARCHAR2(15),
49 contact_last_name VARCHAR2(20),
50 contact_phone VARCHAR2(40),
51 contact_email_address VARCHAR2(2000),
52 contact_fax VARCHAR2(40)
53 );
54
55 TYPE vendor_contact_tbl_type IS TABLE OF vendor_contact_rec_type INDEX BY BINARY_INTEGER;
56
57 TYPE customer_rec_type IS RECORD
58 (
59 customer_id NUMBER,
60 customer_number VARCHAR2(30),
61 customer_name VARCHAR2(360)
62 );
63
64 TYPE customer_tbl_type IS TABLE OF customer_rec_type INDEX BY BINARY_INTEGER;
65
66 TYPE item_rec_type IS RECORD
67 (
68 inventory_item_id NUMBER,
69 item_number VARCHAR2(240),
70 DESCRIPTION VARCHAR2(240)
71 );
72
73 TYPE item_tbl_type IS TABLE OF item_rec_type INDEX BY BINARY_INTEGER;
74 TYPE itemnum_rec_type IS RECORD
75 (
76 inventory_item_id NUMBER,
77 item_number VARCHAR2(240),
78 description VARCHAR2(240),
79 vendor_part_no VARCHAR2(240)
80 );
81
82 TYPE itemnum_tbl_type IS TABLE OF itemnum_rec_type INDEX BY BINARY_INTEGER;
83
84
85 TYPE warehouse_rec_type IS RECORD
86 (
87 warehouse_id NUMBER,
88 warehouse_code VARCHAR2(3),
89 Warehouse_Name VARCHAR2(240)
90 );
91
92 TYPE warehouse_tbl_type IS TABLE OF warehouse_rec_type INDEX BY BINARY_INTEGER;
93
94 TYPE dpp_inv_hdr_rec_type IS RECORD
95 (
96 org_id NUMBER,
97 effective_start_date DATE,
98 effective_end_date DATE,
99 currency_code VARCHAR2(15)
100 );
101
102 TYPE dpp_inv_cov_rct_rec_type IS RECORD
103 (
104 Date_Received DATE,
105 Onhand_quantity NUMBER
106 );
107
108 TYPE dpp_inv_cov_rct_tbl_type IS TABLE OF dpp_inv_cov_rct_rec_type INDEX BY BINARY_INTEGER;
109
110 TYPE inventorydetails_rec_type IS RECORD
111 (
112 Transaction_Line_Id NUMBER,
113 Inventory_Item_ID NUMBER,
114 UOM_Code VARCHAR2(3),
115 Onhand_Quantity NUMBER,
116 Covered_quantity NUMBER
117 );
118
119 TYPE inventorydetails_tbl_type IS TABLE OF inventorydetails_rec_type INDEX BY BINARY_INTEGER;
120
121 TYPE dpp_cust_inv_rec_type IS RECORD
122 (
123 Customer_ID NUMBER,
124 Inventory_Item_ID NUMBER,
125 UOM_Code VARCHAR2(3),
126 Onhand_Quantity NUMBER
127 );
128
129 TYPE dpp_cust_inv_tbl_type IS TABLE OF dpp_cust_inv_rec_type INDEX BY BINARY_INTEGER;
130
131 TYPE dpp_cust_price_rec_type IS RECORD
132 (
133 Customer_ID NUMBER,
134 Inventory_Item_ID NUMBER,
135 UOM_Code VARCHAR2(15),
136 Last_Price NUMBER,
137 invoice_currency_code VARCHAR2(15),
138 price_change NUMBER,
139 converted_price_change NUMBER
140 );
141
142 TYPE dpp_cust_price_tbl_type IS TABLE OF dpp_cust_price_rec_type INDEX BY BINARY_INTEGER;
143
144 TYPE dpp_list_price_rec_type IS RECORD
145 (
146 Inventory_Item_ID NUMBER,
147 List_Price NUMBER
148 );
149
150 TYPE dpp_list_price_tbl_type IS TABLE OF dpp_list_price_rec_type INDEX BY BINARY_INTEGER;
151
152 -- for AME
153 TYPE approval_rec_type IS RECORD (
154 OBJECT_TYPE VARCHAR2(30)
155 ,OBJECT_ID NUMBER
156 ,STATUS_CODE VARCHAR2(30)
157 ,ACTION_CODE VARCHAR2(30)
158 ,ACTION_PERFORMED_BY NUMBER -- fnd user_id
159 );
160
161 TYPE approverRecord is record(
162 user_id number,
163 person_id number,
164 first_name varchar2(150),
165 last_name varchar2(150),
166 api_insertion varchar2(1),
167 authority varchar2(1),
168 approval_status varchar2(50),
169 approval_type_id number,
170 group_or_chain_id number,
171 occurrence number,
172 source varchar2(500),
173 approver_sequence number,
174 approver_email varchar2(240),
175 approver_group_name varchar2(50)
176 );
177
178 TYPE approversTable IS TABLE OF approverRecord INDEX BY BINARY_INTEGER;
179
180 TYPE dpp_txn_hdr_rec_type IS RECORD
181 (
182 Transaction_Header_ID NUMBER,
183 Transaction_number VARCHAR2(240),
184 Process_code VARCHAR2(240),
185 claim_id NUMBER,
186 claim_type_flag VARCHAR2(30),
187 claim_creation_source VARCHAR2(20)
188 );
189
190
191 TYPE dpp_txn_line_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
192 ---
193
194 PROCEDURE search_vendors(
195 p_search_criteria IN search_criteria_tbl_type
196 ,x_vendor_tbl OUT NOCOPY vendor_tbl_type
197 ,x_rec_count OUT NOCOPY NUMBER
198 ,x_return_status OUT NOCOPY VARCHAR2
199 );
200
201 PROCEDURE search_vendor_sites(
202 p_search_criteria IN search_criteria_tbl_type
203 ,x_vendor_site_tbl OUT NOCOPY vendor_site_tbl_type
204 ,x_rec_count OUT NOCOPY NUMBER
205 ,x_return_status OUT NOCOPY VARCHAR2
206 );
207
208 PROCEDURE search_vendor_contacts(
209 p_search_criteria IN search_criteria_tbl_type
210 ,x_vendor_contact_tbl OUT NOCOPY vendor_contact_tbl_type
211 ,x_rec_count OUT NOCOPY NUMBER
212 ,x_return_status OUT NOCOPY VARCHAR2
213 );
214
215 PROCEDURE search_items(
216 p_search_criteria IN search_criteria_tbl_type
217 ,x_item_tbl OUT NOCOPY itemnum_tbl_type
218 ,x_rec_count OUT NOCOPY NUMBER
219 ,x_return_status OUT NOCOPY VARCHAR2
220 );
221
222 PROCEDURE search_customer_items(
223 p_search_criteria IN search_criteria_tbl_type
224 ,x_customer_item_tbl OUT NOCOPY item_tbl_type
225 ,x_rec_count OUT NOCOPY NUMBER
226 ,x_return_status OUT NOCOPY VARCHAR2
227 );
228
229 PROCEDURE search_customer_items_all(
230 p_search_criteria IN search_criteria_tbl_type
231 ,x_customer_item_tbl OUT NOCOPY item_tbl_type
232 ,x_rec_count OUT NOCOPY NUMBER
233 ,x_return_status OUT NOCOPY VARCHAR2
234 );
235
236 PROCEDURE search_warehouses(
237 p_search_criteria IN search_criteria_tbl_type
238 ,x_warehouse_tbl OUT NOCOPY warehouse_tbl_type
239 ,x_rec_count OUT NOCOPY NUMBER
240 ,x_return_status OUT NOCOPY VARCHAR2
241 );
242
243 PROCEDURE Get_InventoryDetails(
244 p_hdr_rec IN dpp_inv_hdr_rec_type
245 ,p_inventorydetails_tbl IN OUT NOCOPY inventorydetails_tbl_type
246 ,x_rec_count OUT NOCOPY NUMBER
247 ,x_return_status OUT NOCOPY VARCHAR2
248 );
249
250 PROCEDURE Get_CustomerInventory(
251 p_hdr_rec IN dpp_inv_hdr_rec_type
252 ,p_cust_inv_tbl IN OUT NOCOPY dpp_cust_inv_tbl_type
253 ,x_rec_count OUT NOCOPY NUMBER
254 ,x_return_status OUT NOCOPY VARCHAR2
255 );
256
257 PROCEDURE search_customers(
258 p_search_criteria IN search_criteria_tbl_type
259 ,x_customer_tbl OUT NOCOPY customer_tbl_type
260 ,x_rec_count OUT NOCOPY NUMBER
261 ,x_return_status OUT NOCOPY VARCHAR2
262 );
263
264 PROCEDURE search_customers_all(
265 p_search_criteria IN search_criteria_tbl_type
266 ,x_customer_tbl OUT NOCOPY customer_tbl_type
267 ,x_rec_count OUT NOCOPY NUMBER
268 ,x_return_status OUT NOCOPY VARCHAR2
269 );
270
271 PROCEDURE Get_LastPrice(
272 p_hdr_rec IN dpp_inv_hdr_rec_type
273 ,p_cust_price_tbl IN OUT NOCOPY dpp_cust_price_tbl_type
274 ,x_rec_count OUT NOCOPY NUMBER
275 ,x_return_status OUT NOCOPY VARCHAR2
276 );
277
278 PROCEDURE Get_ListPrice(
279 p_hdr_rec IN dpp_inv_hdr_rec_type
280 ,p_listprice_tbl IN OUT NOCOPY dpp_list_price_tbl_type
281 ,x_rec_count OUT NOCOPY NUMBER
282 ,x_return_status OUT NOCOPY VARCHAR2
283 );
284
285 PROCEDURE Get_Vendor(
286 p_vendor_rec IN OUT NOCOPY vendor_rec_type
287 ,x_rec_count OUT NOCOPY NUMBER
288 ,x_return_status OUT NOCOPY VARCHAR2
289 );
290
291 PROCEDURE Get_Vendor_Site(
292 p_vendor_site_rec IN OUT NOCOPY vendor_site_rec_type
293 ,x_rec_count OUT NOCOPY NUMBER
294 ,x_return_status OUT NOCOPY VARCHAR2
295 );
296
297 PROCEDURE Get_Vendor_Contact(
298 p_vendor_contact_rec IN OUT NOCOPY vendor_contact_rec_type
299 ,x_rec_count OUT NOCOPY NUMBER
300 ,x_return_status OUT NOCOPY VARCHAR2
301 );
302
303 PROCEDURE Get_Warehouse(
304 p_warehouse_tbl IN OUT NOCOPY warehouse_tbl_type
305 ,x_rec_count OUT NOCOPY NUMBER
306 ,x_return_status OUT NOCOPY VARCHAR2
307 );
308
309 PROCEDURE Get_Customer(
310 p_customer_tbl IN OUT NOCOPY customer_tbl_type
311 ,x_rec_count OUT NOCOPY NUMBER
312 ,x_return_status OUT NOCOPY VARCHAR2
313 );
314
315 PROCEDURE Get_Product(
316 p_item_tbl IN OUT NOCOPY item_tbl_type
317 ,p_org_id IN NUMBER
318 ,x_rec_count OUT NOCOPY NUMBER
319 ,x_return_status OUT NOCOPY VARCHAR2
320 );
321
322 --- for AME
323
324 PROCEDURE Get_AllApprovers(
325 p_api_version IN NUMBER
326 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
327 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
328
329 ,x_return_status OUT NOCOPY VARCHAR2
330 ,x_msg_data OUT NOCOPY VARCHAR2
331 ,x_msg_count OUT NOCOPY NUMBER
332
333 ,p_approval_rec IN approval_rec_type
334 ,p_approversOut OUT NOCOPY approversTable
335 );
336
337 PROCEDURE Process_User_Action (
338 p_api_version IN NUMBER
339 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
340 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
341 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
342
343 ,x_return_status OUT NOCOPY VARCHAR2
344 ,x_msg_data OUT NOCOPY VARCHAR2
345 ,x_msg_count OUT NOCOPY NUMBER
346
347 ,p_approval_rec IN approval_rec_type
348 ,p_approver_id IN NUMBER
349 ,x_final_approval_flag OUT NOCOPY VARCHAR2
350 );
351
352 PROCEDURE Raise_Business_Event(
353 p_api_version IN NUMBER
354 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
355 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
356 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
357
358 ,x_return_status OUT NOCOPY VARCHAR2
359 ,x_msg_count OUT NOCOPY NUMBER
360 ,x_msg_data OUT NOCOPY VARCHAR2
361
362 ,p_txn_hdr_rec IN dpp_txn_hdr_rec_type
363 ,p_txn_line_id IN dpp_txn_line_tbl_type
364 );
365 PROCEDURE check_transaction(
369 ,x_msg_data OUT NOCOPY VARCHAR2
366 p_transaction_header_id IN NUMBER
367 ,p_status_change IN VARCHAR2
368 ,x_rec_count OUT NOCOPY NUMBER
370 ,x_return_status OUT NOCOPY VARCHAR2
371 );
372 PROCEDURE convert_currency(
373 p_from_currency IN VARCHAR2
374 ,p_to_currency IN VARCHAR2
375 ,p_conv_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
376 ,p_conv_rate IN NUMBER DEFAULT FND_API.G_MISS_NUM
377 ,p_conv_date IN DATE DEFAULT SYSDATE
378 ,p_from_amount IN NUMBER
379 ,x_return_status OUT NOCOPY VARCHAR2
380 ,x_to_amount OUT NOCOPY NUMBER
381 ,x_rate OUT NOCOPY NUMBER
382 );
383 END DPP_UIWRAPPER_PVT;
[Home] [Help]