[Home] [Help]
PACKAGE: APPS.WMS_TASK_MGMT_PUB
Source
1 PACKAGE WMS_TASK_MGMT_PUB AUTHID CURRENT_USER AS
2 /*$Header: WMSTKMPS.pls 120.1.12020000.3 2013/04/08 10:13:59 schiluve ship $ */
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(255), --Modified for 14243507
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 secondary_qty NUMBER, -- Added for Bug 16099247
183 uom VARCHAR2(3) ,
184 return_status VARCHAR2(1) );
185 TYPE task_rec_type
186 IS
187 RECORD
188 (
189 transaction_temp_id NUMBER ,
190 return_status VARCHAR2(1) );
191 TYPE task_detail_rec_type
192 IS
193 RECORD
194 (
195 Parent_task_id NUMBER, --This will correspond to the record in task table
196 Lot_number VARCHAR2 (30),
197 Lot_expiration_date DATE,
198 Lot_Primary_quantity NUMBER,
199 Lot_Secondary_quantity NUMBER, -- Added for Bug 16099247
200 Lot_Transaction_quantity NUMBER,
201 From_serial_number VARCHAR2 (30),
202 To_serial_number VARCHAR2 (30),
203 Number_of_Serials NUMBER , --Number of Serials
204 Lot_status_id NUMBER, --Material Status Id.
205 Serial_status_id NUMBER --Material Status Id.
206 );
207 TYPE QTY_CHANGED_REC_TYPE
208 IS
209 RECORD
210 (
211 transaction_quantity NUMBER ,
212 primary_quantity NUMBER ,
213 secondary_quantity NUMBER); -- Added for Bug 16099247
214 TYPE NEW_TASK_REC
215 IS
216 RECORD
217 (
218 transaction_temp_id NUMBER );
219 TYPE task_qty_tbl_type
220 IS
221 TABLE OF task_qty_rec_type INDEX BY BINARY_INTEGER;
222 TYPE task_tbl_type
223 IS
224 TABLE OF task_rec_type INDEX BY BINARY_INTEGER;
225 TYPE task_detail_tbl_type
226 IS
227 TABLE OF task_detail_rec_type INDEX BY BINARY_INTEGER;
228 TYPE QTY_CHANGED_TBL_TYPE
229 IS
230 TABLE OF QTY_CHANGED_REC_TYPE INDEX BY BINARY_INTEGER;
231 TYPE new_task_tbl
232 IS
233 TABLE OF NEW_TASK_REC INDEX BY BINARY_INTEGER;
234 task_detail_table task_detail_tbl_type;
235 new_task_table new_task_tbl;
236
237 TYPE task_record_type
238 is
239 RECORD
240 (
241 transaction_number NUMBER,
242 status VARCHAR2(1),
243 error VARCHAR2(230) );
244
245
246 PROCEDURE modify_task ( p_transaction_number IN NUMBER DEFAULT NULL ,
247 p_task_table IN WMS_TASK_MGMT_PUB.task_tab_type ,
248 p_new_task_status IN NUMBER DEFAULT NULL ,
249 p_new_task_priority IN NUMBER DEFAULT NULL ,
250 p_new_task_type IN VARCHAR2 DEFAULT NULL ,
251 p_new_carton_lpn_id IN NUMBER DEFAULT NULL ,
252 p_new_operation_plan_id IN NUMBER DEFAULT NULL ,
253 p_person_id IN NUMBER DEFAULT NULL ,
254 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
255 x_updated_tasks OUT NOCOPY WMS_TASK_MGMT_PUB.task_tab_type ,
256 x_return_status OUT NOCOPY VARCHAR2 ,
257 x_msg_count OUT NOCOPY NUMBER ,
258 x_msg_data OUT NOCOPY VARCHAR2 );
259
260 PROCEDURE query_task ( p_transaction_number IN NUMBER DEFAULT NULL ,
261 p_query_name IN VARCHAR2 ,
262 x_task_tab OUT NOCOPY task_tab_type ,
263 x_return_status OUT NOCOPY VARCHAR2 ,
264 x_msg_count OUT NOCOPY NUMBER ,
265 x_msg_data OUT NOCOPY VARCHAR2 );
266
267 PROCEDURE initialize_main_table;
268
269
270 PROCEDURE split_task ( p_source_transaction_number IN NUMBER DEFAULT NULL ,
271 p_split_quantities IN task_qty_tbl_type ,
272 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
273 x_resultant_tasks OUT NOCOPY WMS_TASK_MGMT_PUB.task_tab_type ,
274 x_resultant_task_details OUT NOCOPY task_detail_tbl_type ,
275 x_return_status OUT NOCOPY VARCHAR2 ,
276 x_msg_count OUT NOCOPY NUMBER ,
277 x_msg_data OUT NOCOPY VARCHAR2 );
278
279 procedure delete_tasks ( p_transaction_number IN NUMBER DEFAULT NULL ,
280 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
281 p_wms_task IN WMS_TASK_MGMT_PUB.task_tab_type ,
282 x_undeleted_tasks OUT NOCOPY WMS_TASK_MGMT_PUB.task_tab_type ,
283 x_return_status OUT NOCOPY VARCHAR2 ,
284 x_msg_count OUT NOCOPY NUMBER ,
285 x_msg_data OUT NOCOPY VARCHAR2 );
286
287 --praveen
288 PROCEDURE cancel_task(
289 p_transaction_number IN NUMBER DEFAULT NULL,
290 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
291 p_wms_task IN WMS_TASK_MGMT_PUB.task_tab_type,
292 x_unprocessed_crossdock_tasks OUT NOCOPY WMS_TASK_MGMT_PUB.task_tab_type,
293 x_return_status OUT NOCOPY VARCHAR2,
294 x_msg_count OUT NOCOPY NUMBER,
295 x_msg_data OUT NOCOPY VARCHAR2
296 );
297
298 END WMS_TASK_MGMT_PUB;