[Home] [Help]
PACKAGE: APPS.WMS_TASK_MGMT_PUB
Source
1 PACKAGE WMS_TASK_MGMT_PUB AS
2 /*$Header: WMSTKMPS.pls 120.1 2007/12/28 05:47:01 anviswan noship $ */
3
4 TYPE task_output_rectype
5 IS
6 RECORD
7 (
8 TASK_ID NUMBER,
9 TRANSACTION_NUMBER NUMBER,
10 PARENT_LINE_ID NUMBER,
11 INVENTORY_ITEM_ID NUMBER,
12 ITEM VARCHAR2(240),
13 ITEM_DESCRIPTION VARCHAR2(240),
14 UNIT_WEIGHT NUMBER,
15 WEIGHT_UOM_CODE VARCHAR2(3),
16 DISPLAY_WEIGHT NUMBER,
17 UNIT_VOLUME NUMBER,
18 VOLUME_UOM_CODE VARCHAR2(3),
19 DISPLAY_VOLUME NUMBER,
20 TIME_ESTIMATE NUMBER,
21 ORGANIZATION_ID NUMBER,
22 ORGANIZATION_CODE VARCHAR2(3),
23 REVISION VARCHAR2(3),
24 SUBINVENTORY VARCHAR2(10),
25 LOCATOR_ID NUMBER,
26 LOCATOR VARCHAR2(240),
27 TRANSACTION_TYPE_ID NUMBER,
28 TRANSACTION_ACTION_ID NUMBER,
29 TRANSACTION_SOURCE_TYPE_ID NUMBER,
30 TRANSACTION_SOURCE_TYPE VARCHAR2(240),
31 TRANSACTION_SOURCE_ID NUMBER,
32 TRANSACTION_SOURCE_LINE_ID NUMBER,
33 TO_ORGANIZATION_ID NUMBER,
34 TO_ORGANIZATION_CODE VARCHAR2(3),
35 TO_SUBINVENTORY VARCHAR2(10),
36 TO_LOCATOR_ID NUMBER,
37 TO_LOCATOR VARCHAR2(204),
38 TRANSACTION_UOM VARCHAR2(3),
39 TRANSACTION_QUANTITY NUMBER,
40 USER_TASK_TYPE_ID NUMBER,
41 USER_TASK_TYPE VARCHAR2(4),
42 PERSON_ID NUMBER,
43 PERSON_ID_ORIGINAL NUMBER,
44 PERSON VARCHAR2(240),
45 EFFECTIVE_START_DATE DATE,
46 EFFECTIVE_END_DATE DATE,
47 PERSON_RESOURCE_ID NUMBER,
48 PERSON_RESOURCE_CODE VARCHAR2(10),
49 MACHINE_RESOURCE_ID NUMBER,
50 MACHINE_RESOURCE_CODE VARCHAR2(10),
51 EQUIPMENT_INSTANCE VARCHAR2(30),
52 STATUS_ID NUMBER,
53 STATUS_ID_ORIGINAL NUMBER,
54 STATUS VARCHAR2(80),
55 CREATION_TIME DATE,
56 DISPATCHED_TIME DATE,
57 LOADED_TIME DATE,
58 DROP_OFF_TIME DATE,
59 MMTT_LAST_UPDATE_DATE DATE,
60 MMTT_LAST_UPDATED_BY NUMBER,
61 WDT_LAST_UPDATE_DATE DATE,
62 WDT_LAST_UPDATED_BY NUMBER,
63 PRIORITY NUMBER,
64 PRIORITY_ORIGINAL NUMBER,
65 TASK_TYPE_ID NUMBER,
66 TASK_TYPE VARCHAR2(80),
67 MOVE_ORDER_LINE_ID NUMBER,
68 PICK_SLIP_NUMBER NUMBER,
69 CARTONIZATION_ID NUMBER,
70 ALLOCATED_LPN_ID NUMBER,
71 CONTAINER_ITEM_ID NUMBER,
72 CONTENT_LPN_ID NUMBER,
73 TO_LPN_ID NUMBER,
74 CONTAINER_ITEM VARCHAR2(240),
75 CARTONIZATION_LPN VARCHAR2(30),
76 ALLOCATED_LPN VARCHAR2(30),
77 CONTENT_LPN VARCHAR2(30),
78 TO_LPN VARCHAR2(30),
79 REFERENCE VARCHAR2(240),
80 REFERENCE_ID NUMBER,
81 CUSTOMER_ID NUMBER,
82 CUSTOMER VARCHAR2(240),
83 SHIP_TO_LOCATION_ID NUMBER,
84 SHIP_TO_STATE VARCHAR2(60),
85 SHIP_TO_COUNTRY VARCHAR2(60),
86 SHIP_TO_POSTAL_CODE VARCHAR2(60),
87 DELIVERY_ID NUMBER,
88 DELIVERY VARCHAR2(30),
89 SHIP_METHOD VARCHAR2(80),
90 CARRIER_ID NUMBER,
91 CARRIER VARCHAR2(360),
92 SHIPMENT_DATE DATE,
93 SHIPMENT_PRIORITY VARCHAR2(80),
94 WIP_ENTITY_TYPE NUMBER,
95 WIP_ENTITY_ID NUMBER,
96 ASSEMBLY_ID NUMBER,
97 ASSEMBLY VARCHAR2(240),
98 LINE_ID NUMBER,
99 LINE VARCHAR2(10),
100 DEPARTMENT_ID NUMBER ,
101 DEPARTMENT VARCHAR2(10),
102 SOURCE_HEADER VARCHAR2(240),
103 LINE_NUMBER VARCHAR2(240),
104 OPERATION_PLAN_ID NUMBER,
105 OPERATION_PLAN VARCHAR2(80),
106 RESULT VARCHAR2(1),
107 ERROR VARCHAR2(240),
108 IS_MODIFIED VARCHAR2(1),
109 /*FROM_LPN_ID NUMBER,
110 FROM_LPN VARCHAR2(30),
111 NUM_OF_CHILD_TASKS NUMBER ,
112 EXPANSION_CODE VARCHAR2(1),
113 PLANS_TASKS VARCHAR2(80),
114 OP_PLAN_INSTANCE_ID NUMBER,
115 OPERATION_SEQUENCE NUMBER,
116 TRANSACTION_SET_ID NUMBER,
117 PICKED_LPN_ID NUMBER,
118 PICKED_LPN VARCHAR2(30),
119 LOADED_LPN VARCHAR2(30),
120 LOADED_LPN_ID NUMBER,
121 DROP_LPN VARCHAR2(30),
122 SECONDARY_TRANSACTION_QUANTITY NUMBER,
123 SECONDARY_TRANSACTION_UOM VARCHAR2(3),
124 PRIMARY_PRODUCT VARCHAR2(240));*/
125 --Munish added columns with new sequence as in table wms_waveplan_tasks_temp
126 EXPANSION_CODE VARCHAR2(1),
127 FROM_LPN VARCHAR2(30),
128 FROM_LPN_ID NUMBER,
129 NUM_OF_CHILD_TASKS NUMBER ,
130 OPERATION_SEQUENCE NUMBER,
131 OP_PLAN_INSTANCE_ID NUMBER,
132 PLANS_TASKS VARCHAR2(80),
133 TRANSACTION_SET_ID NUMBER,
134 PICKED_LPN_ID NUMBER,
135 PICKED_LPN VARCHAR2(30),
136 LOADED_LPN VARCHAR2(30),
137 LOADED_LPN_ID NUMBER,
138 DROP_LPN VARCHAR2(30) ,
139 --Munish added 3 new columns got added in R12 in table wms_waveplan_tasks_temp
140 secondary_transaction_quantity NUMBER ,
141 secondary_transaction_uom VARCHAR2(3),
142 primary_product VARCHAR2(240) ,
143 --anjana
144 load_seq_number NUMBER );--this column was added to wms_waveplan_tasks_temp as a part of OTM project.
145
146 TYPE task_input_rectype
147 IS
148 RECORD
149 (
150 field_name wms_saved_queries.field_name%TYPE,
151 field_value wms_saved_queries.field_value%TYPE );
152
153 TYPE task_tab_type
154 IS
155 TABLE OF task_output_rectype INDEX BY binary_integer;
156
157 TYPE main_tab_type
158 IS
159 TABLE OF task_input_rectype INDEX BY binary_integer;
160
161 TYPE query_tab_type
162 IS
163 TABLE OF task_input_rectype INDEX BY binary_integer;
164 g_main_tab main_tab_type;
165
166 TYPE op_plan_rec
167 is
168 RECORD
169 (
170 system_task_type NUMBER,
171 organization_id NUMBER,
172 eabled_flag VARCHAR2(1),
173 activity_type_id NUMBER,
174 common_to_all_org VARCHAR2(1),
175 plan_type_id NUMBER );
176
177 TYPE task_qty_rec_type
178 IS
179 RECORD
180 (
181 quantity NUMBER ,
182 uom VARCHAR2(3) ,
183 return_status VARCHAR2(1) );
184 TYPE task_rec_type
185 IS
186 RECORD
187 (
188 transaction_temp_id NUMBER ,
189 return_status VARCHAR2(1) );
190 TYPE task_detail_rec_type
191 IS
192 RECORD
193 (
194 Parent_task_id NUMBER, --This will correspond to the record in task table
195 Lot_number VARCHAR2 (30),
196 Lot_expiration_date DATE,
197 Lot_Primary_quantity NUMBER,
198 Lot_Transaction_quantity NUMBER,
199 From_serial_number VARCHAR2 (30),
200 To_serial_number VARCHAR2 (30),
201 Number_of_Serials NUMBER , --Number of Serials
202 Lot_status_id NUMBER, --Material Status Id.
203 Serial_status_id NUMBER --Material Status Id.
204 );
205 TYPE QTY_CHANGED_REC_TYPE
206 IS
207 RECORD
208 (
209 transaction_quantity NUMBER ,
210 primary_quantity NUMBER );
211 TYPE NEW_TASK_REC
212 IS
213 RECORD
214 (
215 transaction_temp_id NUMBER );
216 TYPE task_qty_tbl_type
217 IS
218 TABLE OF task_qty_rec_type INDEX BY BINARY_INTEGER;
219 TYPE task_tbl_type
220 IS
221 TABLE OF task_rec_type INDEX BY BINARY_INTEGER;
222 TYPE task_detail_tbl_type
223 IS
224 TABLE OF task_detail_rec_type INDEX BY BINARY_INTEGER;
225 TYPE QTY_CHANGED_TBL_TYPE
226 IS
227 TABLE OF QTY_CHANGED_REC_TYPE INDEX BY BINARY_INTEGER;
228 TYPE new_task_tbl
229 IS
230 TABLE OF NEW_TASK_REC INDEX BY BINARY_INTEGER;
231 task_detail_table task_detail_tbl_type;
232 new_task_table new_task_tbl;
233
234 TYPE task_record_type
235 is
236 RECORD
237 (
238 transaction_number NUMBER,
239 status VARCHAR2(1),
240 error VARCHAR2(230) );
241
242
243 PROCEDURE modify_task ( p_transaction_number IN NUMBER DEFAULT NULL ,
244 p_task_table IN WMS_TASK_MGMT_PUB.task_tab_type ,
245 p_new_task_status IN NUMBER DEFAULT NULL ,
246 p_new_task_priority IN NUMBER DEFAULT NULL ,
247 p_new_task_type IN VARCHAR2 DEFAULT NULL ,
248 p_new_carton_lpn_id IN NUMBER DEFAULT NULL ,
249 p_new_operation_plan_id IN NUMBER DEFAULT NULL ,
250 p_person_id IN NUMBER DEFAULT NULL ,
251 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
252 x_updated_tasks OUT NOCOPY WMS_TASK_MGMT_PUB.task_tab_type ,
253 x_return_status OUT NOCOPY VARCHAR2 ,
254 x_msg_count OUT NOCOPY NUMBER ,
255 x_msg_data OUT NOCOPY VARCHAR2 );
256
257 PROCEDURE query_task ( p_transaction_number IN NUMBER DEFAULT NULL ,
258 p_query_name IN VARCHAR2 ,
259 x_task_tab OUT NOCOPY task_tab_type ,
260 x_return_status OUT NOCOPY VARCHAR2 ,
261 x_msg_count OUT NOCOPY NUMBER ,
262 x_msg_data OUT NOCOPY VARCHAR2 );
263
264 PROCEDURE initialize_main_table;
265
266
267 PROCEDURE split_task ( p_source_transaction_number IN NUMBER DEFAULT NULL ,
268 p_split_quantities IN task_qty_tbl_type ,
269 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
270 x_resultant_tasks OUT NOCOPY WMS_TASK_MGMT_PUB.task_tab_type ,
271 x_resultant_task_details OUT NOCOPY task_detail_tbl_type ,
272 x_return_status OUT NOCOPY VARCHAR2 ,
273 x_msg_count OUT NOCOPY NUMBER ,
274 x_msg_data OUT NOCOPY VARCHAR2 );
275
276 procedure delete_tasks ( p_transaction_number IN NUMBER DEFAULT NULL ,
277 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
278 p_wms_task IN WMS_TASK_MGMT_PUB.task_tab_type ,
279 x_undeleted_tasks OUT NOCOPY WMS_TASK_MGMT_PUB.task_tab_type ,
280 x_return_status OUT NOCOPY VARCHAR2 ,
281 x_msg_count OUT NOCOPY NUMBER ,
282 x_msg_data OUT NOCOPY VARCHAR2 );
283
284 --praveen
285 PROCEDURE cancel_task(
286 p_transaction_number IN NUMBER DEFAULT NULL,
287 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
288 p_wms_task IN WMS_TASK_MGMT_PUB.task_tab_type,
289 x_unprocessed_crossdock_tasks OUT NOCOPY WMS_TASK_MGMT_PUB.task_tab_type,
290 x_return_status OUT NOCOPY VARCHAR2,
291 x_msg_count OUT NOCOPY NUMBER,
292 x_msg_data OUT NOCOPY VARCHAR2
293 );
294
295 END WMS_TASK_MGMT_PUB;