[Home] [Help]
PACKAGE: APPS.CSE_DATASTRUCTURES_PUB
Source
1 package cse_datastructures_pub AUTHID CURRENT_USER as
2 -- $Header: CSEDATAS.pls 120.8 2011/01/28 09:25:26 dnema ship $
3
4 G_IN_PROCESS CONSTANT VARCHAR2(30) := 'IN_PROCESS';
5 G_IN_INVENTORY CONSTANT VARCHAR2(30) := 'IN_INVENTORY';
6 G_IN_SERVICE CONSTANT VARCHAR2(30) := 'IN_SERVICE';
7 G_OUT_OF_SERVICE CONSTANT VARCHAR2(30) := 'OUT_OF_SERVICE';
8 G_IN_TRANSIT CONSTANT VARCHAR2(30) := 'IN_TRANSIT';
9 G_INSTALLED CONSTANT VARCHAR2(30) := 'INSTALLED';
10 G_COMPLETE CONSTANT VARCHAR2(30) := 'COMPLETE';
11 G_PENDING CONSTANT VARCHAR2(30) := 'PENDING';
12 G_INTERFACED_TO_PA CONSTANT VARCHAR2(30) := 'INTERFACED_TO_PA';
13 G_RETIRED CONSTANT VARCHAR2(30) := 'RETIRED';
14 G_TXN_ERROR CONSTANT VARCHAR2(1) := 'E';
15 G_BYPASS_FLAG CONSTANT VARCHAR2(1) := 'B';
16 G_SUCCESS_FLAG CONSTANT VARCHAR2(1) := 'S';
17 G_IB_UPDATE CONSTANT VARCHAR2(30) := 'IB_UPDATE';
18 G_PA_INTERFACE CONSTANT VARCHAR2(30) := 'PA_INTERFACE';
19 G_OUTBOUND CONSTANT VARCHAR2(30) := 'OUTBOUND';
20 G_BYPASS CONSTANT VARCHAR2(30) := 'BYPASS';
21 G_NOTIFY CONSTANT VARCHAR2(1) := 'N';
22 G_FA_UPDATE CONSTANT VARCHAR2(30) := 'FA_UPDATE';
23 G_COMP_ADJ_PENDING CONSTANT VARCHAR2(30) := 'COMP_ADJ_PENDING';
24
25 TYPE ASSET_ATTRIBUTES_REC_TYPE IS RECORD
26 ( ASSET_ID NUMBER,
27 BOOK_TYPE_CODE VARCHAR2(15),
28 UNITS NUMBER,
29 TRANSACTED_BY NUMBER,
30 TRANSACTION_DATE DATE,
31 Message_Id NUMBER
32 );
33 TYPE RCV_ATTRIBUTES_REC_TYPE IS RECORD
34 ( RCV_TRANSACTION_ID NUMBER,
35 Message_Id NUMBER
36 );
37
38 TYPE IPV_ATTRIBUTES_REC_TYPE IS RECORD
39 ( INVOICE_DISTRIBUTION_ID NUMBER,
40 Message_Id NUMBER
41 );
42 TYPE MTL_ITEM_REC_TYPE IS RECORD
43 ( INVENTORY_ITEM_ID NUMBER := FND_API.G_MISS_NUM,
44 ORGANIZATION_ID NUMBER := FND_API.G_MISS_NUM,
45 SUBINVENTORY_CODE VARCHAR2(10) := FND_API.G_MISS_CHAR,
46 REVISION VARCHAR2(3) := FND_API.G_MISS_CHAR,
47 TRANSACTION_QUANTITY NUMBER := FND_API.G_MISS_NUM,
48 TRANSACTION_UOM VARCHAR2(3) := FND_API.G_MISS_CHAR,
49 TRANSACTION_TYPE_ID NUMBER := FND_API.G_MISS_NUM,
50 TRANSACTION_ACTION_ID NUMBER := FND_API.G_MISS_NUM,
51 TRANSACTION_SOURCE_ID NUMBER := FND_API.G_MISS_NUM,
52 TRANSACTION_SOURCE_TYPE_ID NUMBER := FND_API.G_MISS_NUM,
53 TRANSFER_LOCATOR_ID NUMBER := FND_API.G_MISS_NUM,
54 TRANSFER_ORGANIZATION_ID NUMBER := FND_API.G_MISS_NUM,
55 TRANSFER_SUBINVENTORY VARCHAR2(10) := FND_API.G_MISS_CHAR,
56 LOCATOR_ID NUMBER := FND_API.G_MISS_NUM,
57 SOURCE_PROJECT_ID NUMBER := FND_API.G_MISS_NUM,
58 SOURCE_TASK_ID NUMBER := FND_API.G_MISS_NUM,
59 FROM_PROJECT_ID NUMBER := FND_API.G_MISS_NUM,
60 FROM_TASK_ID NUMBER := FND_API.G_MISS_NUM,
61 TO_PROJECT_ID NUMBER := FND_API.G_MISS_NUM,
62 TO_TASK_ID NUMBER := FND_API.G_MISS_NUM,
63 TRANSACTION_DATE DATE := FND_API.G_MISS_DATE,
64 LAST_UPDATED_BY NUMBER := FND_API.G_MISS_NUM,
65 SERIAL_NUMBER VARCHAR2(30) := FND_API.G_MISS_CHAR,
66 --LOT_NUMBER VARCHAR2(30) := FND_API.G_MISS_CHAR,
67 LOT_NUMBER VARCHAR2(80) := FND_API.G_MISS_CHAR, --bug 10322621
68 HR_LOCATION_ID NUMBER := FND_API.G_MISS_NUM,
69 PO_DISTRIBUTION_ID NUMBER := FND_API.G_MISS_NUM,
70 SUBINV_LOCATION_ID NUMBER := FND_API.G_MISS_NUM,
71 SHIPMENT_NUMBER VARCHAR2(30) := FND_API.G_MISS_CHAR,
72 TRX_SOURCE_LINE_ID NUMBER := FND_API.G_MISS_NUM,
73 MOVE_ORDER_LINE_ID NUMBER := FND_API.G_MISS_NUM,
74 SERIAL_NUMBER_CONTROL_CODE NUMBER := FND_API.G_MISS_NUM,
75 SHIP_TO_LOCATION_ID NUMBER := FND_API.G_MISS_NUM
76 );
77 TYPE MTL_ITEM_TBL_TYPE is TABLE OF MTL_ITEM_REC_TYPE INDEX BY BINARY_INTEGER;
78
79 TYPE MTL_INSTANCE_REC_TYPE is RECORD
80 ( INSTANCE_ID NUMBER,
81 INSTANCE_NUMBER VARCHAR2(30),
82 INVENTORY_ITEM_ID NUMBER,
83 INVENTORY_REVISION VARCHAR2(3),
84 INV_MASTER_ORGANIZATION_ID NUMBER,
85 SERIAL_NUMBER VARCHAR2(30),
86 MFG_SERIAL_NUMBER_FLAG VARCHAR2(1),
87 --LOT_NUMBER VARCHAR2(30),
88 LOT_NUMBER VARCHAR2(80), --bug 10322621
89 QUANTITY NUMBER,
90 UNIT_OF_MEASURE VARCHAR2(3),
91 INSTANCE_STATUS_ID NUMBER,
92 CUSTOMER_VIEW_FLAG VARCHAR2(1),
93 MERCHANT_VIEW_FLAG VARCHAR2(1),
94 INSTANCE_TYPE_CODE VARCHAR2(30),
95 LOCATION_TYPE_CODE VARCHAR2(30),
96 LOCATION_ID NUMBER,
97 INV_ORGANIZATION_ID NUMBER,
98 INV_SUBINVENTORY_NAME VARCHAR2(10),
99 INV_LOCATOR_ID NUMBER,
100 PA_PROJECT_ID NUMBER,
101 PA_PROJECT_TASK_ID NUMBER
102 );
103 TYPE MTL_INSTANCE_TBL_TYPE is TABLE OF MTL_INSTANCE_REC_TYPE INDEX BY BINARY_INTEGER;
104
105 TYPE MTL_TRX_TYPE is RECORD
106 ( MTL_TRANSACTION_ID NUMBER);
107
108 TYPE PROJ_ITEM_UNINST_ATTR_REC_TYPE IS RECORD
109 ( ITEM_ID NUMBER,
110 REVISION VARCHAR2(3),
111 --LOT_NUMBER VARCHAR2(30),
112 LOT_NUMBER VARCHAR2(80), --bug 10322621
113 SERIAL_NUMBER VARCHAR2(30),
114 QUANTITY NUMBER,
115 NETWORK_LOCATION_ID NUMBER,
116 PARTY_SITE_ID NUMBER,
117 WORK_ORDER_NUMBER VARCHAR2(30),
118 PROJECT_ID NUMBER,
119 TASK_ID NUMBER,
120 TRANSACTION_DATE DATE,
121 TRANSACTED_BY NUMBER,
122 MESSAGE_ID NUMBER
123 );
124 TYPE PROJ_ITEM_UNINST_ATTR_TBL_TYPE is TABLE OF PROJ_ITEM_UNINST_ATTR_REC_TYPE INDEX BY BINARY_INTEGER;
125 TYPE PROJ_ITEM_INST_ATTR_REC_TYPE IS RECORD
126 ( ITEM_ID NUMBER,
127 REVISION VARCHAR2(3),
128 --LOT_NUMBER VARCHAR2(30),
129 LOT_NUMBER VARCHAR2(80), --bug 10322621
130 SERIAL_NUMBER VARCHAR2(30),
131 QUANTITY NUMBER,
132 NETWORK_LOCATION_ID NUMBER,
133 PARTY_SITE_ID NUMBER,
134 WORK_ORDER_NUMBER VARCHAR2(30),
135 PROJECT_ID NUMBER,
136 TASK_ID NUMBER,
137 TRANSACTION_DATE DATE,
138 TRANSACTED_BY NUMBER,
139 MESSAGE_ID NUMBER
140 );
141 TYPE PROJ_ITEM_INST_ATTR_TBL_TYPE is TABLE OF PROJ_ITEM_INST_ATTR_REC_TYPE INDEX BY BINARY_INTEGER;
142 TYPE OUT_OF_SERVICE_ATTR_REC_TYPE IS RECORD
143 ( ITEM_ID NUMBER,
144 REVISION VARCHAR2(3),
145 --LOT_NUMBER VARCHAR2(30),
146 LOT_NUMBER VARCHAR2(80), --bug 10322621
147 SERIAL_NUMBER VARCHAR2(30),
148 QUANTITY NUMBER,
149 NETWORK_LOCATION_ID NUMBER,
150 PARTY_SITE_ID NUMBER,
151 WORK_ORDER_NUMBER VARCHAR2(30),
152 TRANSACTION_DATE DATE,
153 TRANSACTED_BY NUMBER,
154 MESSAGE_ID NUMBER
155 );
156 TYPE OUT_OF_SERVICE_ATTR_TBL_TYPE is TABLE OF OUT_OF_SERVICE_ATTR_REC_TYPE INDEX BY BINARY_INTEGER;
157 TYPE IN_SERVICE_ATTR_REC_TYPE IS RECORD
158 ( ITEM_ID NUMBER,
159 REVISION VARCHAR2(3),
160 --LOT_NUMBER VARCHAR2(30),
161 LOT_NUMBER VARCHAR2(80), --bug 10322621
162 SERIAL_NUMBER VARCHAR2(30),
163 QUANTITY NUMBER,
164 NETWORK_LOCATION_ID NUMBER,
165 PARTY_SITE_ID NUMBER,
166 WORK_ORDER_NUMBER VARCHAR2(30),
167 TRANSACTION_DATE DATE,
168 TRANSACTED_BY NUMBER,
169 MESSAGE_ID NUMBER
170 );
171 TYPE IN_SERVICE_ATTR_TBL_TYPE is TABLE OF IN_SERVICE_ATTR_REC_TYPE INDEX BY BINARY_INTEGER;
172 TYPE ITEM_MOVE_ATTR_REC_TYPE IS RECORD
173 ( ITEM_ID NUMBER,
174 REVISION VARCHAR2(3),
175 --LOT_NUMBER VARCHAR2(30),
176 LOT_NUMBER VARCHAR2(80), --bug 10322621
177 SERIAL_NUMBER VARCHAR2(30),
178 QUANTITY NUMBER,
179 FROM_NETWORK_LOCATION_ID NUMBER,
180 TO_NETWORK_LOCATION_ID NUMBER,
181 FROM_PARTY_SITE_ID NUMBER,
182 TO_PARTY_SITE_ID NUMBER,
183 WORK_ORDER_NUMBER VARCHAR2(30),
184 TRANSACTION_DATE DATE,
185 TRANSACTED_BY NUMBER,
186 MESSAGE_ID NUMBER
187 );
188 TYPE ITEM_MOVE_ATTR_TBL_TYPE is TABLE OF ITEM_MOVE_ATTR_REC_TYPE INDEX BY BINARY_INTEGER;
189
190 TYPE PROJ_ITM_INSV_ATTR_REC_TYPE IS RECORD
191 ( ITEM_ID NUMBER,
192 REVISION VARCHAR2(3),
193 --LOT_NUMBER VARCHAR2(30),
194 LOT_NUMBER VARCHAR2(80), --bug 10322621
195 SERIAL_NUMBER VARCHAR2(30),
196 QUANTITY NUMBER,
197 NETWORK_LOCATION_ID NUMBER,
198 PARTY_SITE_ID NUMBER,
199 WORK_ORDER_NUMBER VARCHAR2(30),
200 PROJECT_ID NUMBER,
201 TASK_ID NUMBER,
202 EFFECTIVE_DATE DATE,
203 TRANSACTION_DATE DATE,
204 TRANSACTED_BY NUMBER,
205 MESSAGE_ID NUMBER
206 );
207 TYPE PROJ_ITM_INSV_ATTR_TBL_TYPE is TABLE OF PROJ_ITM_INSV_ATTR_REC_TYPE INDEX BY BINARY_INTEGER;
208
209 TYPE PROJ_ITM_INSV_PA_ATTR_REC_TYPE IS RECORD
210 ( ITEM_ID NUMBER,
211 INV_MASTER_ORG_ID NUMBER,
212 SERIAL_NUMBER VARCHAR2(30),
213 QUANTITY NUMBER,
214 LOCATION_ID NUMBER,
215 LOCATION_TYPE VARCHAR2(30),
216 PROJECT_ID NUMBER,
217 TASK_ID NUMBER,
218 INSTANCE_ID NUMBER,
219 OBJECT_VERSION_NUMBER NUMBER,
220 TRANSACTION_ID NUMBER,
221 TRANSACTION_DATE DATE,
222 TRANSACTED_BY NUMBER,
223 MESSAGE_ID NUMBER,
224 org_id number,
225 to_project_id NUMBER, -- Added for bug 8670632
226 to_task_id NUMBER -- Added for bug 8670632
227 );
228
229 TYPE PROJ_ITM_INSV_PA_ATTR_TBL_TYPE is TABLE OF PROJ_ITM_INSV_PA_ATTR_REC_TYPE INDEX BY BINARY_INTEGER;
230
231 TYPE Rcv_Txn_Rec_Type IS RECORD (
232 Rcv_Transaction_ID NUMBER,
233 Temp_Txn_Id NUMBER,
234 Organization_ID NUMBER,
235 Txn_Organization_ID NUMBER,
236 PO_Header_Id NUMBER,
237 PO_Line_Id NUMBER,
238 PO_Distribution_Id NUMBER,
239 Project_ID NUMBER,
240 Task_ID NUMBER,
241 Transacted_By NUMBER,
242 Transaction_Date DATE,
243 Inventory_Item_ID NUMBER,
244 Revision_Id VARCHAR2(3),
245 --Lot_Number VARCHAR2(30),
246 LOT_NUMBER VARCHAR2(80), --bug 10322621
247 Serial_Number VARCHAR2(30),
248 Quantity NUMBER,
249 UOM VARCHAR2(3),
250 Amount NUMBER,
251 CSI_Transaction_Id NUMBER,
252 PO_Number VARCHAR2(50),
253 PO_Line_Number VARCHAR2(50),
254 po_vendor_id number,
255 transaction_type varchar2(30),
256 destination_type_code varchar2(30));
257
258 TYPE Rcv_Txn_tbl_Type IS TABLE OF Rcv_Txn_Rec_Type INDEX BY BINARY_INTEGER;
259
260 TYPE IPV_Txn_Rec_Type IS RECORD (
261 Project_Id NUMBER,
262 Task_Id NUMBER,
263 Inventory_Item_Id NUMBER,
264 Item_Name VARCHAR2(30),
265 Serial_Number VARCHAR2(30),
266 Invoice_Distribution_Id NUMBER,
267 Accounting_Date DATE,
268 Invoice_Id NUMBER,
269 IPV NUMBER,
270 Transacted_By NUMBER,
271 Transaction_Date DATE,
272 Organization_Id NUMBER,
273 Invoice_Quantity NUMBER,
274 Price_Var_CC_Id NUMBER,
275 PO_Header_Id NUMBER,
276 PO_line_id NUMBER,
277 PO_distribution_id NUMBER,
278 Cr_CC_Id NUMBER,
279 Vendor_Number VARCHAR2(30),
280 UOM VARCHAR2(15),
281 CSI_Transaction_ID NUMBER,
282 INVOICE_NUMBER VARCHAR2(50));
283 TYPE IPV_Txn_tbl_Type IS TABLE OF IPV_Txn_Rec_Type
284 INDEX BY BINARY_INTEGER;
285
286 TYPE asset_query_rec IS RECORD
287 (
288 parent_mass_addition_id NUMBER := FND_API.G_MISS_NUM
289 ,mass_addition_id NUMBER := FND_API.G_MISS_NUM
290 ,asset_id NUMBER := FND_API.G_MISS_NUM
291 ,search_method VARCHAR2(4) := FND_API.G_MISS_CHAR
292 ,asset_number VARCHAR2(15) := FND_API.G_MISS_CHAR
293 ,category_id NUMBER := FND_API.G_MISS_NUM
294 ,book_type_code VARCHAR2(15) := FND_API.G_MISS_CHAR
295 ,date_placed_in_service DATE := FND_API.G_MISS_DATE
296 ,asset_key_ccid NUMBER := FND_API.G_MISS_NUM
297 ,tag_number VARCHAR2(15) := FND_API.G_MISS_CHAR
298 ,description VARCHAR2(80) := FND_API.G_MISS_CHAR
299 ,manufacturer_name VARCHAR2(30) := FND_API.G_MISS_CHAR
300 ,serial_number VARCHAR2(35) := FND_API.G_MISS_CHAR
301 ,model_number VARCHAR2(40) := FND_API.G_MISS_CHAR
302 ,location_id NUMBER := FND_API.G_MISS_NUM
303 ,employee_id NUMBER := FND_API.G_MISS_NUM
304 ,deprn_employee_id NUMBER := FND_API.G_MISS_NUM
305 ,deprn_expense_ccid NUMBER := FND_API.G_MISS_NUM
306 ,inventory_item_id NUMBER := FND_API.G_MISS_NUM
307 ,distribution_id NUMBER := FND_API.G_MISS_NUM
308 ,current_mtl_cost NUMBER := FND_API.G_MISS_NUM
309 ,current_non_mtl_cost NUMBER := FND_API.G_MISS_NUM
310 ,current_units NUMBER := FND_API.G_MISS_NUM
311 ,pending_adj_mtl_cost NUMBER := FND_API.G_MISS_NUM
312 ,pending_adj_non_mtl_cost NUMBER := FND_API.G_MISS_NUM
313 ,pending_ret_mtl_cost NUMBER := FND_API.G_MISS_NUM
314 ,pending_ret_non_mtl_cost NUMBER := FND_API.G_MISS_NUM
315 ,pending_ret_units NUMBER := FND_API.G_MISS_NUM
316 );
317
318 TYPE asset_query_tbl IS TABLE OF asset_query_rec
319 INDEX BY BINARY_INTEGER;
320
321
322 --bnarayan added for R12
323 TYPE asset_attrib_rec IS RECORD
324 (
325 Instance_ID NUMBER := FND_API.G_MISS_NUM
326 ,Inventory_Item_ID NUMBER := FND_API.G_MISS_NUM
327 ,Serial_Number VARCHAR2(30) := FND_API.G_MISS_CHAR
328 ,Organization_ID NUMBER := FND_API.G_MISS_NUM
329 ,INV_MASTER_Organization_ID NUMBER := FND_API.G_MISS_NUM
330 ,Subinventory_Name VARCHAR2(30) := FND_API.G_MISS_CHAR
331 ,Transaction_Quantity NUMBER := FND_API.G_MISS_NUM
332 ,Transaction_ID NUMBER := FND_API.G_MISS_NUM
333 ,Transaction_Date DATE := FND_API.G_MISS_DATE
334 ,Source_Transaction_type VARCHAR2(30) := FND_API.G_MISS_CHAR
335 ,Depreciable_Flag VARCHAR2(1) := FND_API.G_MISS_CHAR
336 ,Location_Type_Code VARCHAR2(40) := FND_API.G_MISS_CHAR
337 ,Transaction_Type_ID NUMBER := FND_API.G_MISS_NUM
338 ,Source_Header_Ref_ID NUMBER := FND_API.G_MISS_NUM
339 ,RCV_Transaction_ID NUMBER := FND_API.G_MISS_NUM
340 ,PO_Distribution_Id NUMBER := FND_API.G_MISS_NUM
341 ,Inv_Material_Transaction_ID NUMBER := FND_API.G_MISS_NUM
342 ,Location_id NUMBER := FND_API.G_MISS_NUM
343 ,Asset_Category_ID NUMBER := FND_API.G_MISS_NUM
344 ,book_type_code VARCHAR2(15) := FND_API.G_MISS_CHAR
345
346 );
347
348
349 TYPE asset_attrib_tbl IS TABLE OF asset_attrib_rec
350 INDEX BY BINARY_INTEGER;
351
352
353
354
355 TYPE distribution_rec IS RECORD
356 (
357 asset_id NUMBER := FND_API.G_MISS_NUM
358 ,book_type_code VARCHAR2(15) := FND_API.G_MISS_CHAR
359 ,distribution_id NUMBER := FND_API.G_MISS_NUM
360 ,location_id NUMBER := FND_API.G_MISS_NUM
361 ,employee_id NUMBER := FND_API.G_MISS_NUM
362 ,deprn_expense_ccid NUMBER := FND_API.G_MISS_NUM
363 ,current_units NUMBER := FND_API.G_MISS_NUM
364 ,pending_ret_units NUMBER := FND_API.G_MISS_NUM
365 );
366
367 TYPE distribution_tbl IS TABLE OF distribution_rec
368 INDEX BY BINARY_INTEGER ;
369
370 G_LIFO_SEARCH CONSTANT VARCHAR2(4) := 'LIFO';
371 G_FIFO_SEARCH CONSTANT VARCHAR2(4) := 'FIFO';
372 G_APPLICATION_NAME CONSTANT VARCHAR2(3) := 'CSE';
373
374 TYPE WFM_TRX_VALUES_REC is RECORD
375 ( INVENTORY_ITEM_ID NUMBER,
379 SERIAL_NUMBER VARCHAR2(30),
376 INVENTORY_REVISION VARCHAR2(3),
377 --LOT_NUMBER VARCHAR2(30),
378 LOT_NUMBER VARCHAR2(80), --bug 10322621
380 QUANTITY NUMBER,
381 NETWORK_LOCATION_ID NUMBER,
382 PARTY_SITE_ID NUMBER,
383 FROM_NETWORK_LOCATION_ID NUMBER,
384 TO_NETWORK_LOCATION_ID NUMBER,
385 FROM_PARTY_SITE_ID NUMBER,
386 TO_PARTY_SITE_ID NUMBER,
387 WORK_ORDER_NUMBER VARCHAR2(30),
388 SOURCE_TRANSACTION_DATE DATE,
389 SOURCE_TRANSACTION_BY NUMBER,
390 TRANSACTION_DATE DATE,
391 TRANSACTED_BY NUMBER,
392 EFFECTIVE_DATE DATE,
393 PROJECT_ID NUMBER,
394 TASK_ID NUMBER,
395 RETURN_STATUS VARCHAR2(1),
396 ERROR_MESSAGE VARCHAR2(2000)
397 );
398
399 TYPE WFM_TRX_VALUES_TBL IS TABLE OF WFM_TRX_VALUES_REC INDEX BY BINARY_INTEGER;
400
401 END CSE_DATASTRUCTURES_PUB;