1 PACKAGE DPP_UIWRAPPER_PVT as
2 /* $Header: dppvuiws.pls 120.11 2008/05/16 06:20:44 sdasan noship $ */
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(40),
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(40),
78 description VARCHAR2(240),
79 vendor_part_no VARCHAR2(40)
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_warehouses(
230 p_search_criteria IN search_criteria_tbl_type
231 ,x_warehouse_tbl OUT NOCOPY warehouse_tbl_type
232 ,x_rec_count OUT NOCOPY NUMBER
233 ,x_return_status OUT NOCOPY VARCHAR2
234 );
235
236 PROCEDURE Get_InventoryDetails(
237 p_hdr_rec IN dpp_inv_hdr_rec_type
238 ,p_inventorydetails_tbl IN OUT NOCOPY inventorydetails_tbl_type
239 ,x_rec_count OUT NOCOPY NUMBER
240 ,x_return_status OUT NOCOPY VARCHAR2
241 );
242
243 PROCEDURE Get_CustomerInventory(
244 p_hdr_rec IN dpp_inv_hdr_rec_type
245 ,p_cust_inv_tbl IN OUT NOCOPY dpp_cust_inv_tbl_type
246 ,x_rec_count OUT NOCOPY NUMBER
247 ,x_return_status OUT NOCOPY VARCHAR2
248 );
249
250 PROCEDURE search_customers(
251 p_search_criteria IN search_criteria_tbl_type
252 ,x_customer_tbl OUT NOCOPY customer_tbl_type
253 ,x_rec_count OUT NOCOPY NUMBER
254 ,x_return_status OUT NOCOPY VARCHAR2
255 );
256
257 PROCEDURE Get_LastPrice(
258 p_hdr_rec IN dpp_inv_hdr_rec_type
259 ,p_cust_price_tbl IN OUT NOCOPY dpp_cust_price_tbl_type
260 ,x_rec_count OUT NOCOPY NUMBER
261 ,x_return_status OUT NOCOPY VARCHAR2
262 );
263
264 PROCEDURE Get_ListPrice(
265 p_hdr_rec IN dpp_inv_hdr_rec_type
266 ,p_listprice_tbl IN OUT NOCOPY dpp_list_price_tbl_type
267 ,x_rec_count OUT NOCOPY NUMBER
268 ,x_return_status OUT NOCOPY VARCHAR2
269 );
270
271 PROCEDURE Get_Vendor(
272 p_vendor_rec IN OUT NOCOPY vendor_rec_type
273 ,x_rec_count OUT NOCOPY NUMBER
274 ,x_return_status OUT NOCOPY VARCHAR2
275 );
276
277 PROCEDURE Get_Vendor_Site(
278 p_vendor_site_rec IN OUT NOCOPY vendor_site_rec_type
279 ,x_rec_count OUT NOCOPY NUMBER
280 ,x_return_status OUT NOCOPY VARCHAR2
281 );
282
283 PROCEDURE Get_Vendor_Contact(
284 p_vendor_contact_rec IN OUT NOCOPY vendor_contact_rec_type
285 ,x_rec_count OUT NOCOPY NUMBER
286 ,x_return_status OUT NOCOPY VARCHAR2
287 );
288
289 PROCEDURE Get_Warehouse(
290 p_warehouse_tbl IN OUT NOCOPY warehouse_tbl_type
291 ,x_rec_count OUT NOCOPY NUMBER
292 ,x_return_status OUT NOCOPY VARCHAR2
293 );
294
295 PROCEDURE Get_Customer(
296 p_customer_tbl IN OUT NOCOPY customer_tbl_type
297 ,x_rec_count OUT NOCOPY NUMBER
298 ,x_return_status OUT NOCOPY VARCHAR2
299 );
300
301 PROCEDURE Get_Product(
302 p_item_tbl IN OUT NOCOPY item_tbl_type
303 ,p_org_id IN NUMBER
304 ,x_rec_count OUT NOCOPY NUMBER
305 ,x_return_status OUT NOCOPY VARCHAR2
306 );
307
308 --- for AME
309
310 PROCEDURE Get_AllApprovers(
311 p_api_version IN NUMBER
312 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
313 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
314
315 ,x_return_status OUT NOCOPY VARCHAR2
316 ,x_msg_data OUT NOCOPY VARCHAR2
317 ,x_msg_count OUT NOCOPY NUMBER
318
319 ,p_approval_rec IN approval_rec_type
320 ,p_approversOut OUT NOCOPY approversTable
321 );
322
323 PROCEDURE Process_User_Action (
324 p_api_version IN NUMBER
325 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
326 ,p_commit 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_approver_id IN NUMBER
335 ,x_final_approval_flag OUT NOCOPY VARCHAR2
336 );
337
338 PROCEDURE Raise_Business_Event(
339 p_api_version IN NUMBER
340 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
341 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
342 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
343
347
344 ,x_return_status OUT NOCOPY VARCHAR2
345 ,x_msg_count OUT NOCOPY NUMBER
346 ,x_msg_data OUT NOCOPY VARCHAR2
348 ,p_txn_hdr_rec IN dpp_txn_hdr_rec_type
349 ,p_txn_line_id IN dpp_txn_line_tbl_type
350 );
351 PROCEDURE check_transaction(
352 p_transaction_header_id IN NUMBER
353 ,p_status_change IN VARCHAR2
354 ,x_rec_count OUT NOCOPY NUMBER
355 ,x_msg_data OUT NOCOPY VARCHAR2
356 ,x_return_status OUT NOCOPY VARCHAR2
357 );
358 PROCEDURE convert_currency(
359 p_from_currency IN VARCHAR2
360 ,p_to_currency IN VARCHAR2
361 ,p_conv_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
362 ,p_conv_rate IN NUMBER DEFAULT FND_API.G_MISS_NUM
363 ,p_conv_date IN DATE DEFAULT SYSDATE
364 ,p_from_amount IN NUMBER
365 ,x_return_status OUT NOCOPY VARCHAR2
366 ,x_to_amount OUT NOCOPY NUMBER
367 ,x_rate OUT NOCOPY NUMBER
368 );
369 END DPP_UIWRAPPER_PVT;