[Home] [Help]
PACKAGE: APPS.MTL_CCEOI_VAR_PVT
Source
1 PACKAGE MTL_CCEOI_VAR_PVT AS
2 /* $Header: INVVCCVS.pls 120.1 2005/06/22 05:28:06 appldev ship $ */
3 -- ################# New Datatypes #################################
4 -- New record Type for Item informations
5 TYPE INV_ITEM_REC_TYPE IS RECORD(
6 INVENTORY_ITEM_ID NUMBER DEFAULT NULL
7 , ITEM_SEGMENT1 VARCHAR2(40) DEFAULT NULL
8 , ITEM_SEGMENT2 VARCHAR2(40) DEFAULT NULL
9 , ITEM_SEGMENT3 VARCHAR2(40) DEFAULT NULL
10 , ITEM_SEGMENT4 VARCHAR2(40) DEFAULT NULL
11 , ITEM_SEGMENT5 VARCHAR2(40) DEFAULT NULL
12 , ITEM_SEGMENT6 VARCHAR2(40) DEFAULT NULL
13 , ITEM_SEGMENT7 VARCHAR2(40) DEFAULT NULL
14 , ITEM_SEGMENT8 VARCHAR2(40) DEFAULT NULL
15 , ITEM_SEGMENT9 VARCHAR2(40) DEFAULT NULL
16 , ITEM_SEGMENT10 VARCHAR2(40) DEFAULT NULL
17 , ITEM_SEGMENT11 VARCHAR2(40) DEFAULT NULL
18 , ITEM_SEGMENT12 VARCHAR2(40) DEFAULT NULL
19 , ITEM_SEGMENT13 VARCHAR2(40) DEFAULT NULL
20 , ITEM_SEGMENT14 VARCHAR2(40) DEFAULT NULL
21 , ITEM_SEGMENT15 VARCHAR2(40) DEFAULT NULL
22 , ITEM_SEGMENT16 VARCHAR2(40) DEFAULT NULL
23 , ITEM_SEGMENT17 VARCHAR2(40) DEFAULT NULL
24 , ITEM_SEGMENT18 VARCHAR2(40) DEFAULT NULL
25 , ITEM_SEGMENT19 VARCHAR2(40) DEFAULT NULL
26 , ITEM_SEGMENT20 VARCHAR2(40) DEFAULT NULL
27 );
28 --
29 -- New record type for Locator information
30 TYPE INV_LOCATOR_REC_TYPE IS RECORD(
31 LOCATOR_ID NUMBER DEFAULT NULL
32 , LOCATOR_SEGMENT1 VARCHAR2(40) DEFAULT NULL
33 , LOCATOR_SEGMENT2 VARCHAR2(40) DEFAULT NULL
34 , LOCATOR_SEGMENT3 VARCHAR2(40) DEFAULT NULL
35 , LOCATOR_SEGMENT4 VARCHAR2(40) DEFAULT NULL
36 , LOCATOR_SEGMENT5 VARCHAR2(40) DEFAULT NULL
37 , LOCATOR_SEGMENT6 VARCHAR2(40) DEFAULT NULL
38 , LOCATOR_SEGMENT7 VARCHAR2(40) DEFAULT NULL
39 , LOCATOR_SEGMENT8 VARCHAR2(40) DEFAULT NULL
40 , LOCATOR_SEGMENT9 VARCHAR2(40) DEFAULT NULL
41 , LOCATOR_SEGMENT10 VARCHAR2(40) DEFAULT NULL
42 , LOCATOR_SEGMENT11 VARCHAR2(40) DEFAULT NULL
43 , LOCATOR_SEGMENT12 VARCHAR2(40) DEFAULT NULL
44 , LOCATOR_SEGMENT13 VARCHAR2(40) DEFAULT NULL
45 , LOCATOR_SEGMENT14 VARCHAR2(40) DEFAULT NULL
46 , LOCATOR_SEGMENT15 VARCHAR2(40) DEFAULT NULL
47 , LOCATOR_SEGMENT16 VARCHAR2(40) DEFAULT NULL
48 , LOCATOR_SEGMENT17 VARCHAR2(40) DEFAULT NULL
49 , LOCATOR_SEGMENT18 VARCHAR2(40) DEFAULT NULL
50 , LOCATOR_SEGMENT19 VARCHAR2(40) DEFAULT NULL
51 , LOCATOR_SEGMENT20 VARCHAR2(40) DEFAULT NULL
52 );
53 --
54 -- New record type for adjustment account
55 TYPE ADJUSTACCOUNT_REC_TYPE IS RECORD(
56 ADJUSTMENT_ACCOUNT_ID NUMBER DEFAULT NULL
57 , ACCOUNT_SEGMENT1 VARCHAR2(25) DEFAULT NULL
58 , ACCOUNT_SEGMENT2 VARCHAR2(25) DEFAULT NULL
59 , ACCOUNT_SEGMENT3 VARCHAR2(25) DEFAULT NULL
60 , ACCOUNT_SEGMENT4 VARCHAR2(25) DEFAULT NULL
61 , ACCOUNT_SEGMENT5 VARCHAR2(25) DEFAULT NULL
62 , ACCOUNT_SEGMENT6 VARCHAR2(25) DEFAULT NULL
63 , ACCOUNT_SEGMENT7 VARCHAR2(25) DEFAULT NULL
64 , ACCOUNT_SEGMENT8 VARCHAR2(25) DEFAULT NULL
65 , ACCOUNT_SEGMENT9 VARCHAR2(25) DEFAULT NULL
66 , ACCOUNT_SEGMENT10 VARCHAR2(25) DEFAULT NULL
67 , ACCOUNT_SEGMENT11 VARCHAR2(25) DEFAULT NULL
68 , ACCOUNT_SEGMENT12 VARCHAR2(25) DEFAULT NULL
69 , ACCOUNT_SEGMENT13 VARCHAR2(25) DEFAULT NULL
70 , ACCOUNT_SEGMENT14 VARCHAR2(25) DEFAULT NULL
71 , ACCOUNT_SEGMENT15 VARCHAR2(25) DEFAULT NULL
72 , ACCOUNT_SEGMENT16 VARCHAR2(25) DEFAULT NULL
73 , ACCOUNT_SEGMENT17 VARCHAR2(25) DEFAULT NULL
74 , ACCOUNT_SEGMENT18 VARCHAR2(25) DEFAULT NULL
75 , ACCOUNT_SEGMENT19 VARCHAR2(25) DEFAULT NULL
76 , ACCOUNT_SEGMENT20 VARCHAR2(25) DEFAULT NULL
77 , ACCOUNT_SEGMENT21 VARCHAR2(25) DEFAULT NULL
78 , ACCOUNT_SEGMENT22 VARCHAR2(25) DEFAULT NULL
79 , ACCOUNT_SEGMENT23 VARCHAR2(25) DEFAULT NULL
80 , ACCOUNT_SEGMENT24 VARCHAR2(25) DEFAULT NULL
81 , ACCOUNT_SEGMENT25 VARCHAR2(25) DEFAULT NULL
82 , ACCOUNT_SEGMENT26 VARCHAR2(25) DEFAULT NULL
83 , ACCOUNT_SEGMENT27 VARCHAR2(25) DEFAULT NULL
84 , ACCOUNT_SEGMENT28 VARCHAR2(25) DEFAULT NULL
85 , ACCOUNT_SEGMENT29 VARCHAR2(25) DEFAULT NULL
86 , ACCOUNT_SEGMENT30 VARCHAR2(25) DEFAULT NULL
87 );
88 --
89 -- New record data type for attribute category
90 TYPE INV_ATTRIB_CATEGORY_REC_TYPE IS RECORD(
91 INVENTORY_ITEM_ID NUMBER DEFAULT NULL
92 , ATTRIBUTE1 VARCHAR2(150) DEFAULT NULL
93 , ATTRIBUTE2 VARCHAR2(150) DEFAULT NULL
94 , ATTRIBUTE3 VARCHAR2(150) DEFAULT NULL
95 , ATTRIBUTE4 VARCHAR2(150) DEFAULT NULL
96 , ATTRIBUTE5 VARCHAR2(150) DEFAULT NULL
97 , ATTRIBUTE6 VARCHAR2(150) DEFAULT NULL
98 , ATTRIBUTE7 VARCHAR2(150) DEFAULT NULL
99 , ATTRIBUTE8 VARCHAR2(150) DEFAULT NULL
100 , ATTRIBUTE9 VARCHAR2(150) DEFAULT NULL
101 , ATTRIBUTE10 VARCHAR2(150) DEFAULT NULL
102 , ATTRIBUTE11 VARCHAR2(150) DEFAULT NULL
103 , ATTRIBUTE12 VARCHAR2(150) DEFAULT NULL
104 , ATTRIBUTE13 VARCHAR2(150) DEFAULT NULL
105 , ATTRIBUTE14 VARCHAR2(150) DEFAULT NULL
106 , ATTRIBUTE15 VARCHAR2(150) DEFAULT NULL
107 );
108 --
109 -- New record data type for SKU information
110 TYPE INV_SKU_REC_TYPE IS RECORD (
111 INVENTORY_ITEM_FLAG VARCHAR2(1) DEFAULT NULL
112 , REVISION_QTY_CONTROL_CODE NUMBER DEFAULT NULL
113 , REVISION VARCHAR2(3) DEFAULT NULL
114 , LOT_CONTROL_CODE NUMBER DEFAULT NULL
115 , LOT_NUMBER VARCHAR2(80) DEFAULT NULL -- INVCONV
116 , EXPIRATION_DATE DATE DEFAULT NULL --LOT
117 , SERIAL_NUMBER_CONTROL_CODE NUMBER DEFAULT NULL
118 , SERIAL_NUMBER VARCHAR2(30) DEFAULT NULL
119 , ALLOWED_UNITS_LOOKUP_CODE NUMBER DEFAULT NULL
120 , LOCATION_CONTROL_CODE NUMBER DEFAULT NULL
121 , RESTRICT_LOCATORS_CODE NUMBER DEFAULT NULL
122 );
123 --
124 --################# VARIABLES ###########################
125 --
126 -- Cycle Count header record
127 G_CYCLE_COUNT_HEADER_REC MTL_CYCLE_COUNT_HEADERS%ROWTYPE;
128 --
129 -- Cycle Count Entries record
130 G_CYCLE_COUNT_ENTRY_REC MTL_CYCLE_COUNT_ENTRIES%ROWTYPE;
131 --
132 -- Open request exist
133 G_OPEN_REQUEST BOOLEAN default FALSE;
134 --
135 -- SKU record for the current entrie
136 G_SKU_REC MTL_CCEOI_VAR_PVT.INV_SKU_REC_TYPE;
137 --
138 -- Adjustment amount
139 G_ADJUSTMENT_AMOUNT NUMBER DEFAULT NULL;
140 --
141 -- Adjustment Variance percentage
142 G_ADJ_VARIANCE_PERCENTAGE NUMBER DEFAULT NULL;
143 --
144 -- Adjustment quantity
145 G_ADJUSTMENT_QUANTITY NUMBER DEFAULT NULL;
146 -- item costs
147 G_ITEM_COST NUMBER DEFAULT NULL;
148 --
149 -- Stock locator control code for the given organization
150 G_STOCK_LOCATOR_CONTROL_CODE NUMBER DEFAULT NULL;
151 --
152 -- Action Codes
153 G_EXPORT CONSTANT integer := 10 ;
154 G_VALIDATE CONSTANT integer := 11;
155 G_CREATE CONSTANT integer := 12;
156 G_VALSIM CONSTANT integer := 13;
157 G_PROCESS CONSTANT integer := 14;
158 G_CREPRO CONSTANT integer := 15;
159 --
160 -- Get who and concurrent program information.
161 G_LoginID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
162 G_UserID NUMBER:= FND_GLOBAL.USER_ID;
163 G_ProgramAppID NUMBER:= FND_GLOBAL.PROG_APPL_ID;
164 G_ProgramID NUMBER:= FND_GLOBAL.CONC_PROGRAM_ID;
165 G_RequestID NUMBER:= FND_GLOBAL.CONC_REQUEST_ID;
166 --
167 -- Temporary Se No - for Unschedule Entry
168 G_Seq_No NUMBER;
169 -- Current interface record ID
170 G_cc_entry_interface_id
171 MTL_CC_ENTRIES_INTERFACE.cc_entry_interface_id%type DEFAULT NULL;
172 --
173 -- LOCATOR_CONTROL of Subinventory on ORGANIZATION LEVEL
174 G_ITEM_LOCATOR_TYPE MTL_SYSTEM_ITEMS.LOCATION_CONTROL_CODE%TYPE :=
175 G_SKU_REC.LOCATION_CONTROL_CODE;
176 --
177 -- LOCATOR_CONTROL of Item level
178 G_SUB_LOCATOR_TYPE
179 MTL_SECONDARY_INVENTORIES.LOCATOR_TYPE%TYPE DEFAULT NULL;
180 --
181 -- Current Inventory Item ID
182 G_INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%type DEFAULT NULL;
183 --
184 -- Cycle Count Orientation Code
185 G_ORIENTATION_CODE number := G_CYCLE_COUNT_HEADER_REC.ORIENTATION_CODE;
186 --
187 -- Cycle Count Header ID
188 G_CC_HEADER_ID number := G_CYCLE_COUNT_HEADER_REC.CYCLE_COUNT_HEADER_ID;
189 --
190 -- Current Locator ID
191 G_LOCATOR_ID MTL_ITEM_LOCATIONS.INVENTORY_LOCATION_ID%TYPE DEFAULT NULL;
192 --
193 -- Primary unit of measure code of the current item
194 G_PRIMARY_UOM_CODE MTL_SYSTEM_ITEMS.PRIMARY_UOM_CODE%TYPE default null;
195 --
196 -- Unit of measure code
197 G_UOM_CODE MTL_CYCLE_COUNT_ENTRIES.COUNT_UOM_CURRENT%TYPE default null;
198 --
199 -- Count Quantity
200 G_COUNT_QUANTITY MTL_CC_ENTRIES_INTERFACE.COUNT_QUANTITY%TYPE default null;
201 --
202 -- System quantity
203 G_SYSTEM_QUANTITY MTL_ONHAND_QUANTITIES.TRANSACTION_QUANTITY%TYPE default null;
204 --
205 -- Employee-ID of the counter
206 G_EMPLOYEE_ID MTL_EMPLOYEES_CURRENT_VIEW.Employee_ID%type default null;
207 --
208 -- Count Date
209 G_COUNT_DATE date default null;
210 --
211 -- SUBINVENTORY
212 G_SUBINVENTORY MTL_CC_ENTRIES_INTERFACE.SUBINVENTORY%TYPE default null ;
213 -- Adjustment Account ID
214 G_ADJUST_ACCOUNT_ID MTL_CC_ENTRIES_INTERFACE.ADJUSTMENT_ACCOUNT_ID%type
215 default null;
216
217 -- flag to determine whether the record that is being processed
218 -- is stored in the interface
219 G_REC_IN_SYSTEM BOOLEAN DEFAULT TRUE;
220
221 -- LPN ID
222 G_LPN_ID MTL_CC_ENTRIES_INTERFACE.PARENT_LPN_ID%TYPE default null ;
223 -- System Quantity
224 G_LPN_ITEM_SYSTEM_QTY NUMBER DEFAULT 0;
225
226 -- CONTIANER ENABLED FLAG
227 G_CONTAINER_ENABLED_FLAG NUMBER DEFAULT NULL;
228 -- CONTIANER_ADJUSTMENT_OPTION
229 G_CONTAINER_ADJUSTMENT_OPTION NUMBER DEFAULT NULL;
230 -- CONTAINER_DECREPANCY_OPTION
231 G_CONTAINER_DISCREPANCY_OPTION NUMBER DEFAULT NULL;
232
233 -- Cost Group
234 G_COST_GROUP_ID NUMBER DEFAULT NULL;
235
236 TYPE INV_CCEOI_ID_TABLE_TYPE is TABLE OF NUMBER
237 INDEX BY BINARY_INTEGER;
238
239 --
240 -- temporary record variable to include all changes for an processing of the
241 -- interface records. IF the the processing is not simulated then
242 -- the values of that record will be inserted into the DB
243 G_CC_ENTRY_REC_TMP MTL_CYCLE_COUNT_ENTRIES%ROWTYPE;
244 -- New record Type forentries interface
245 TYPE INV_CCEOI_TYPE IS RECORD(
246 cost_group_name VARCHAR2(40),
247 cost_group_id NUMBER,
248 parent_lpn VARCHAR2(40),
249 parent_lpn_id NUMBER,
250 outermost_lpn_id NUMBER,
251 cc_entry_interface_id NUMBER,
252 organization_id NUMBER,
253 last_update_date DATE ,
254 last_updated_by NUMBER,
255 creation_date DATE ,
256 created_by NUMBER,
257 last_update_login NUMBER,
258 cc_entry_interface_group_id NUMBER,
259 cycle_count_entry_id NUMBER,
260 action_code NUMBER,
261 cycle_count_header_id NUMBER,
262 cycle_count_header_name VARCHAR2(30),
263 count_list_sequence NUMBER,
264 inventory_item_id NUMBER,
265 item_segment1 VARCHAR2(40),
266 item_segment2 VARCHAR2(40),
267 item_segment3 VARCHAR2(40),
268 item_segment4 VARCHAR2(40),
269 item_segment5 VARCHAR2(40),
270 item_segment6 VARCHAR2(40),
271 item_segment7 VARCHAR2(40),
272 item_segment8 VARCHAR2(40),
273 item_segment9 VARCHAR2(40),
274 item_segment10 VARCHAR2(40),
275 item_segment11 VARCHAR2(40),
276 item_segment12 VARCHAR2(40),
277 item_segment13 VARCHAR2(40),
278 item_segment14 VARCHAR2(40),
279 item_segment15 VARCHAR2(40),
280 item_segment16 VARCHAR2(40),
281 item_segment17 VARCHAR2(40),
282 item_segment18 VARCHAR2(40),
283 item_segment19 VARCHAR2(40),
284 item_segment20 VARCHAR2(40),
285 revision VARCHAR2(3) ,
286 subinventory VARCHAR2(10),
287 locator_id NUMBER,
288 locator_segment1 VARCHAR2(40),
289 locator_segment2 VARCHAR2(40),
290 locator_segment3 VARCHAR2(40),
291 locator_segment4 VARCHAR2(40),
292 locator_segment5 VARCHAR2(40),
293 locator_segment6 VARCHAR2(40),
294 locator_segment7 VARCHAR2(40),
295 locator_segment8 VARCHAR2(40),
296 locator_segment9 VARCHAR2(40),
297 locator_segment10 VARCHAR2(40),
298 locator_segment11 VARCHAR2(40),
299 locator_segment12 VARCHAR2(40),
300 locator_segment13 VARCHAR2(40),
301 locator_segment14 VARCHAR2(40),
302 locator_segment15 VARCHAR2(40),
303 locator_segment16 VARCHAR2(40),
304 locator_segment17 VARCHAR2(40),
305 locator_segment18 VARCHAR2(40),
306 locator_segment19 VARCHAR2(40),
307 locator_segment20 VARCHAR2(40),
308 lot_number VARCHAR2(80), -- INVCONV
309 serial_number VARCHAR2(30),
310 primary_uom_quantity NUMBER,
311 count_uom VARCHAR2(3) ,
312 count_unit_of_measure VARCHAR2(25),
313 count_quantity NUMBER ,
314 system_quantity NUMBER ,
315 adjustment_account_id NUMBER ,
316 account_segment1 VARCHAR2(25),
317 account_segment2 VARCHAR2(25),
318 account_segment3 VARCHAR2(25),
319 account_segment4 VARCHAR2(25),
320 account_segment5 VARCHAR2(25),
321 account_segment6 VARCHAR2(25),
322 account_segment7 VARCHAR2(25),
323 account_segment8 VARCHAR2(25),
324 account_segment9 VARCHAR2(25),
325 account_segment10 VARCHAR2(25),
326 account_segment11 VARCHAR2(25),
327 account_segment12 VARCHAR2(25),
328 account_segment13 VARCHAR2(25),
329 account_segment14 VARCHAR2(25),
330 account_segment15 VARCHAR2(25),
331 account_segment16 VARCHAR2(25),
332 account_segment17 VARCHAR2(25),
333 account_segment18 VARCHAR2(25),
334 account_segment19 VARCHAR2(25),
335 account_segment20 VARCHAR2(25),
336 account_segment21 VARCHAR2(25),
337 account_segment22 VARCHAR2(25),
338 account_segment23 VARCHAR2(25),
339 account_segment24 VARCHAR2(25),
340 account_segment25 VARCHAR2(25),
341 account_segment26 VARCHAR2(25),
342 account_segment27 VARCHAR2(25),
343 account_segment28 VARCHAR2(25),
344 account_segment29 VARCHAR2(25),
345 account_segment30 VARCHAR2(25),
346 count_date DATE ,
347 employee_id NUMBER ,
348 employee_full_name VARCHAR2(240),
349 reference VARCHAR2(240),
350 transaction_reason_id NUMBER ,
351 transaction_reason VARCHAR2(30),
352 request_id NUMBER ,
353 program_application_id NUMBER ,
354 program_id NUMBER ,
355 program_update_date DATE ,
356 lock_flag NUMBER(1) ,
357 process_flag NUMBER(1) ,
358 process_mode NUMBER(1) ,
359 valid_flag NUMBER(1) ,
360 delete_flag NUMBER(1) ,
361 status_flag NUMBER ,
362 error_flag NUMBER ,
363 attribute_category VARCHAR2(30),
364 attribute1 VARCHAR2(150),
368 attribute5 VARCHAR2(150),
365 attribute2 VARCHAR2(150),
366 attribute3 VARCHAR2(150),
367 attribute4 VARCHAR2(150),
369 attribute6 VARCHAR2(150),
370 attribute7 VARCHAR2(150),
371 attribute8 VARCHAR2(150),
372 attribute9 VARCHAR2(150),
373 attribute10 VARCHAR2(150),
374 attribute11 VARCHAR2(150),
375 attribute12 VARCHAR2(150),
376 attribute13 VARCHAR2(150),
377 attribute14 VARCHAR2(150),
378 attribute15 VARCHAR2(150),
379 project_id NUMBER ,
380 task_id NUMBER,
381 -- BEGIN INVCONV
382 secondary_uom VARCHAR2(3),
386 -- END INVCONV
383 secondary_unit_of_measure VARCHAR2(25),
384 secondary_count_quantity NUMBER,
385 secondary_system_quantity NUMBER
387 );
388
389 -- BEGIN INVCONV
390 G_TRACKING_QUANTITY_IND MTL_SYSTEM_ITEMS.tracking_quantity_ind%TYPE default null;
391 G_SECONDARY_DEFAULT_IND MTL_SYSTEM_ITEMS.secondary_default_ind%TYPE default null;
392 G_PROCESS_COSTING_ENABLED_FLAG MTL_SYSTEM_ITEMS.process_costing_enabled_flag%TYPE default null;
393 G_PROCESS_ENABLED_FLAG MTL_PARAMETERS.process_enabled_flag%TYPE default null;
394 G_SECONDARY_UOM_CODE MTL_SYSTEM_ITEMS.secondary_uom_code%TYPE default null;
395
396 G_SECONDARY_COUNT_UOM MTL_SYSTEM_ITEMS.secondary_uom_code%TYPE default null;
397 G_SECONDARY_COUNT_QUANTITY MTL_CC_ENTRIES_INTERFACE.SECONDARY_COUNT_QUANTITY%TYPE default null;
398 G_SECONDARY_SYSTEM_QUANTITY MTL_CC_ENTRIES_INTERFACE.SECONDARY_SYSTEM_QUANTITY%TYPE default null;
399 G_LPN_ITEM_SEC_SYSTEM_QTY NUMBER default null;
400 G_SEC_ADJUSTMENT_QUANTITY NUMBER default null;
401 -- END INVCONV
402
403 END MTL_CCEOI_VAR_PVT;