[Home] [Help]
PACKAGE: APPS.WMS_PLAN_TASKS_PVT
Source
1 PACKAGE wms_plan_tasks_pvt AS
2 /* $Header: WMSPTKPS.pls 120.4.12010000.1 2008/07/28 18:36:10 appldev ship $*/
3
4 g_is_add BOOLEAN DEFAULT FALSE;
5 g_organization_id NUMBER DEFAULT NULL;
6 g_subinventory_code VARCHAR2(10) DEFAULT NULL;
7 g_locator_id NUMBER DEFAULT NULL;
8 g_to_subinventory_code VARCHAR2(10) DEFAULT NULL;
9 g_to_locator_id NUMBER DEFAULT NULL;
10 g_inventory_item_id NUMBER DEFAULT NULL;
11 g_category_set_id NUMBER DEFAULT NULL;
12 g_item_category_id NUMBER DEFAULT NULL;
13 g_person_id NUMBER DEFAULT NULL;
14 g_person_resource_id NUMBER DEFAULT NULL;
15 g_equipment_type_id NUMBER DEFAULT NULL;
16 g_machine_resource_id NUMBER DEFAULT NULL;
17 g_machine_instance VARCHAR2(30) DEFAULT NULL;
18 g_user_task_type_id NUMBER DEFAULT NULL;
19 g_from_task_quantity NUMBER DEFAULT NULL;
20 g_to_task_quantity NUMBER DEFAULT NULL;
21 g_from_task_priority NUMBER DEFAULT NULL;
22 g_to_task_priority NUMBER DEFAULT NULL;
23 g_from_creation_date DATE DEFAULT NULL;
24 g_to_creation_date DATE DEFAULT NULL;
25 g_is_unreleased_task BOOLEAN DEFAULT FALSE;
26 g_is_pending_task BOOLEAN DEFAULT FALSE;
27 g_is_queued_task BOOLEAN DEFAULT FALSE;
28 g_is_dispatched_task BOOLEAN DEFAULT FALSE;
29 g_is_active_task BOOLEAN DEFAULT FALSE;
30 g_is_loaded_task BOOLEAN DEFAULT FALSE;
31 g_is_completed_task BOOLEAN DEFAULT FALSE;
32 g_include_inbound BOOLEAN DEFAULT FALSE;
33 g_include_outbound BOOLEAN DEFAULT FALSE;
34 g_include_crossdock BOOLEAN DEFAULT FALSE;
35 g_include_manufacturing BOOLEAN DEFAULT FALSE;
36 g_include_warehousing BOOLEAN DEFAULT FALSE;
37 g_from_po_header_id NUMBER DEFAULT NULL;
38 g_to_po_header_id NUMBER DEFAULT NULL;
39 g_from_purchase_order VARCHAR2(30) DEFAULT NULL;
40 g_to_purchase_order VARCHAR2(30) DEFAULT NULL;
41 g_from_rma_header_id NUMBER DEFAULT NULL;
42 g_to_rma_header_id NUMBER DEFAULT NULL;
43 g_from_rma VARCHAR2(30) DEFAULT NULL;
44 g_to_rma VARCHAR2(30) DEFAULT NULL;
45 g_from_requisition_header_id NUMBER DEFAULT NULL;
46 g_to_requisition_header_id NUMBER DEFAULT NULL;
47 g_from_requisition VARCHAR2(30) DEFAULT NULL;
48 g_to_requisition VARCHAR2(30) DEFAULT NULL;
49 g_from_shipment_number VARCHAR2(30) DEFAULT NULL;
50 g_to_shipment_number VARCHAR2(30) DEFAULT NULL;
51 g_include_sales_orders BOOLEAN DEFAULT TRUE;
52 g_include_internal_orders BOOLEAN DEFAULT TRUE;
53 g_from_sales_order_id NUMBER DEFAULT NULL;
54 g_to_sales_order_id NUMBER DEFAULT NULL;
55 g_from_pick_slip_number NUMBER DEFAULT NULL;
56 g_to_pick_slip_number NUMBER DEFAULT NULL;
57 g_customer_id NUMBER DEFAULT NULL;
58 g_customer_category VARCHAR2(30) DEFAULT NULL;
59 g_delivery_id NUMBER DEFAULT NULL;
60 g_carrier_id NUMBER DEFAULT NULL;
61 g_ship_method VARCHAR2(30) DEFAULT NULL;
62 g_shipment_priority VARCHAR2(30) DEFAULT NULL;
63 g_trip_id NUMBER DEFAULT NULL;
64 g_from_shipment_date DATE DEFAULT NULL;
65 g_to_shipment_date DATE DEFAULT NULL;
66 g_ship_to_state VARCHAR2(30) DEFAULT NULL;
67 g_ship_to_country VARCHAR2(30) DEFAULT NULL;
68 g_ship_to_postal_code VARCHAR2(30) DEFAULT NULL;
69 g_from_number_of_order_lines NUMBER DEFAULT NULL;
70 g_to_number_of_order_lines NUMBER DEFAULT NULL;
71 g_manufacturing_type VARCHAR2(30) DEFAULT NULL;
72 g_from_job VARCHAR2(240) DEFAULT NULL;
73 g_to_job VARCHAR2(240) DEFAULT NULL;
74 g_assembly_id NUMBER DEFAULT NULL;
75 g_from_start_date DATE DEFAULT NULL;
76 g_to_start_date DATE DEFAULT NULL;
77 g_from_line VARCHAR2(30) DEFAULT NULL;
78 g_to_line VARCHAR2(30) DEFAULT NULL;
79 g_department_id NUMBER DEFAULT NULL;
80 g_include_replenishment BOOLEAN DEFAULT TRUE;
81 g_from_replenishment_mo VARCHAR2(30) DEFAULT NULL;
82 g_to_replenishment_mo VARCHAR2(30) DEFAULT NULL;
83 g_include_mo_transfer BOOLEAN DEFAULT TRUE;
84 g_include_mo_issue BOOLEAN DEFAULT TRUE;
85 g_from_transfer_issue_mo VARCHAR2(30) DEFAULT NULL;
86 g_to_transfer_issue_mo VARCHAR2(30) DEFAULT NULL;
87 g_include_lpn_putaway BOOLEAN DEFAULT TRUE;
88 g_include_staging_move BOOLEAN DEFAULT FALSE;
89 g_include_cycle_count BOOLEAN DEFAULT TRUE;
90 g_cycle_count_name VARCHAR2(30) DEFAULT NULL;
91
92 g_is_pending_plan BOOLEAN DEFAULT FALSE;
93 g_is_inprogress_plan BOOLEAN DEFAULT FALSE;
94 g_is_completed_plan BOOLEAN DEFAULT FALSE;
95 g_is_cancelled_plan BOOLEAN DEFAULT FALSE;
96 g_is_aborted_plan BOOLEAN DEFAULT FALSE;
97 g_activity_id NUMBER DEFAULT NULL;
98 g_plan_type_id NUMBER DEFAULT NULL;
99 g_op_plan_id NUMBER DEFAULT NULL;
100 g_inbound_specific_query BOOLEAN DEFAULT FALSE;
101 g_outbound_specific_query BOOLEAN DEFAULT FALSE;
102
103 g_query_independent_tasks BOOLEAN DEFAULT TRUE;
104 g_query_planned_tasks BOOLEAN DEFAULT TRUE;
105 /*Bug 3627575:Added variable to change the where clause in get_generic_where*/
106 g_from_inbound BOOLEAN DEFAULT FALSE;
107
108 g_plans_tasks_record_count NUMBER;
109 g_task_types wms_waveplan_tasks_pvt.lookup_meaning_table;
110 g_task_types_orig wms_waveplan_tasks_pvt.lookup_meaning_table;
111 g_status_codes wms_waveplan_tasks_pvt.lookup_meaning_table;
112 g_status_codes_orig wms_waveplan_tasks_pvt.lookup_meaning_table;
113 g_plan_task_types wms_waveplan_tasks_pvt.lookup_meaning_table;
114 g_plan_task_types_orig wms_waveplan_tasks_pvt.lookup_meaning_table;
115 g_plan_status_codes wms_waveplan_tasks_pvt.lookup_meaning_table;
116 g_plan_status_codes_orig wms_waveplan_tasks_pvt.lookup_meaning_table;
117
118 g_allocated_lpn_visible jtf_custom_grid_cols.visible_flag%TYPE;
119 g_assembly_visible jtf_custom_grid_cols.visible_flag%TYPE;
120 g_carrier_visible jtf_custom_grid_cols.visible_flag%TYPE;
121 g_cartonization_lpn_visible jtf_custom_grid_cols.visible_flag%TYPE;
122 g_container_item_visible jtf_custom_grid_cols.visible_flag%TYPE;
123 g_content_lpn_visible jtf_custom_grid_cols.visible_flag%TYPE;
124 g_customer_visible jtf_custom_grid_cols.visible_flag%TYPE;
125 g_delivery_visible jtf_custom_grid_cols.visible_flag%TYPE;
126 g_department_visible jtf_custom_grid_cols.visible_flag%TYPE;
127 g_line_visible jtf_custom_grid_cols.visible_flag%TYPE;
128 g_line_number_visible jtf_custom_grid_cols.visible_flag%TYPE;
129 g_machine_resource_visible jtf_custom_grid_cols.visible_flag%TYPE;
130 g_person_visible jtf_custom_grid_cols.visible_flag%TYPE;
131 g_person_resource_visible jtf_custom_grid_cols.visible_flag%TYPE;
132 g_ship_method_visible jtf_custom_grid_cols.visible_flag%TYPE;
133 g_ship_to_country_visible jtf_custom_grid_cols.visible_flag%TYPE;
134 g_ship_to_postal_code_visible jtf_custom_grid_cols.visible_flag%TYPE;
135 g_ship_to_state_visible jtf_custom_grid_cols.visible_flag%TYPE;
136 g_source_header_visible jtf_custom_grid_cols.visible_flag%TYPE;
137 g_status_visible jtf_custom_grid_cols.visible_flag%TYPE;
138 g_task_type_visible jtf_custom_grid_cols.visible_flag%TYPE;
139 g_to_locator_visible jtf_custom_grid_cols.visible_flag%TYPE;
140 g_to_lpn_visible jtf_custom_grid_cols.visible_flag%TYPE;
141 g_from_lpn_visible jtf_custom_grid_cols.visible_flag%TYPE;
142 g_to_organization_code_visible jtf_custom_grid_cols.visible_flag%TYPE;
143 g_transaction_action_visible jtf_custom_grid_cols.visible_flag%TYPE;
144 g_txn_source_type_visible jtf_custom_grid_cols.visible_flag%TYPE;
145 g_operation_plan_visible jtf_custom_grid_cols.visible_flag%TYPE;
146 g_user_task_type_visible jtf_custom_grid_cols.visible_flag%TYPE;
147 g_num_of_child_tasks_visible jtf_custom_grid_cols.visible_flag%TYPE;
148 g_op_plan_instance_id_visible jtf_custom_grid_cols.visible_flag%TYPE;
149 g_operation_sequence_visible jtf_custom_grid_cols.visible_flag%TYPE;
150
151 -- Bug #3754781 +1 line.
152 g_inbound_cycle BOOLEAN DEFAULT FALSE;
153
154 TYPE planned_record IS RECORD(
155 is_pending BOOLEAN := FALSE,
156 is_loaded BOOLEAN := FALSE,
157 is_completed BOOLEAN := FALSE
158 );
159
160 g_planned_tasks_rec planned_record;
161
162 SUBTYPE long_sql IS VARCHAR2(32767);
163 SUBTYPE short_sql IS VARCHAR2(3000);
164
165 /* Procedure to write the log messages */
166 PROCEDURE DEBUG(
167 p_message VARCHAR2,
168 p_module VARCHAR2 DEFAULT 'Plans_tasks'
169 );
170
171 /* wrapper procedure to fetch the inbound tasks */
172 PROCEDURE query_inbound_plan_tasks(x_return_status OUT NOCOPY VARCHAR2, p_summary_mode NUMBER DEFAULT 0);
173
174 /* Procedure to fetch the plans specific query */
175 PROCEDURE get_plans(x_plans_query_str OUT NOCOPY VARCHAR2);
176
177 /* Procedure to fetch the tasks specific query */
178 PROCEDURE get_tasks(x_tasks_query_str OUT NOCOPY VARCHAR2, p_summary_mode NUMBER DEFAULT 0);
179
180 /* Common Procedure to get the insert stmt */
181 PROCEDURE get_col_list(x_col_list_str OUT NOCOPY VARCHAR2);
182
183 /* Procedure to fetch completed task records from mmt and wdth
184 * This is more relevant for the Pre-patchset J records and non-inbound
185 * cases */
186 PROCEDURE get_completed_records(
187 x_wdth_select_str OUT NOCOPY VARCHAR2,
188 x_wdth_from_str OUT NOCOPY VARCHAR2,
189 x_wdth_where_str OUT NOCOPY VARCHAR2
190 );
191
192 PROCEDURE get_wdth_plan_records(
193 x_wdth_select_str OUT NOCOPY VARCHAR2,
194 x_wdth_from_str OUT NOCOPY VARCHAR2,
195 x_wdth_where_str OUT NOCOPY VARCHAR2
196 );
197
198 PROCEDURE get_inbound_specific_query(
199 x_inbound_select_str OUT NOCOPY VARCHAR2,
200 x_inbound_from_str OUT NOCOPY VARCHAR2,
201 x_inbound_where_str OUT NOCOPY VARCHAR2,
202 p_is_completed_rec IN NUMBER
203 );
204
205 /* This is used to add-in the outbound query criteria while
206 querying crossdock tasks */
207 PROCEDURE get_outbound_specific_query(
208 x_outbound_from_str OUT NOCOPY VARCHAR2
209 ,x_outbound_where_str OUT NOCOPY VARCHAR2
210 );
211
212
213 /**
214 * Procedure that sets the global variables. This takes in all the
215 * fields on the form as input parameters.
216 * Each of the input parameter is a record representing each of the
217 * tabs on form
218 **/
219 PROCEDURE set_globals(
220 p_organization_id NUMBER DEFAULT NULL
221 , p_subinventory_code VARCHAR2 DEFAULT NULL
222 , p_locator_id NUMBER DEFAULT NULL
223 , p_to_subinventory_code VARCHAR2 DEFAULT NULL
224 , p_to_locator_id NUMBER DEFAULT NULL
225 , p_inventory_item_id NUMBER DEFAULT NULL
226 , p_category_set_id NUMBER DEFAULT NULL
227 , p_item_category_id NUMBER DEFAULT NULL
228 , p_person_id NUMBER DEFAULT NULL
229 , p_person_resource_id NUMBER DEFAULT NULL
230 , p_equipment_type_id NUMBER DEFAULT NULL
231 , p_machine_resource_id NUMBER DEFAULT NULL
232 , p_machine_instance VARCHAR2 DEFAULT NULL
233 , p_user_task_type_id NUMBER DEFAULT NULL
234 , p_from_task_quantity NUMBER DEFAULT NULL
235 , p_to_task_quantity NUMBER DEFAULT NULL
236 , p_from_task_priority NUMBER DEFAULT NULL
237 , p_to_task_priority NUMBER DEFAULT NULL
238 , p_from_creation_date DATE DEFAULT NULL
239 , p_to_creation_date DATE DEFAULT NULL
240 , p_is_unreleased_task BOOLEAN DEFAULT FALSE
241 , p_is_pending_task BOOLEAN DEFAULT FALSE
242 , p_is_queued_task BOOLEAN DEFAULT FALSE
243 , p_is_dispatched_task BOOLEAN DEFAULT FALSE
244 , p_is_active_task BOOLEAN DEFAULT FALSE
245 , p_is_loaded_task BOOLEAN DEFAULT FALSE
246 , p_is_completed_task BOOLEAN DEFAULT FALSE
247 , p_include_inbound BOOLEAN DEFAULT FALSE
248 , p_include_outbound BOOLEAN DEFAULT FALSE
249 , p_include_crossdock BOOLEAN DEFAULT FALSE
250 , p_include_manufacturing BOOLEAN DEFAULT FALSE
251 , p_include_warehousing BOOLEAN DEFAULT FALSE
252 , p_from_po_header_id NUMBER DEFAULT NULL
253 , p_to_po_header_id NUMBER DEFAULT NULL
254 , p_from_purchase_order VARCHAR2 DEFAULT NULL
255 , p_to_purchase_order VARCHAR2 DEFAULT NULL
256 , p_from_rma_header_id NUMBER DEFAULT NULL
257 , p_to_rma_header_id NUMBER DEFAULT NULL
258 , p_from_rma VARCHAR2 DEFAULT NULL
259 , p_to_rma VARCHAR2 DEFAULT NULL
260 , p_from_requisition_header_id NUMBER DEFAULT NULL
261 , p_to_requisition_header_id NUMBER DEFAULT NULL
262 , p_from_requisition VARCHAR2 DEFAULT NULL
263 , p_to_requisition VARCHAR2 DEFAULT NULL
264 , p_from_shipment_number VARCHAR2 DEFAULT NULL
265 , p_to_shipment_number VARCHAR2 DEFAULT NULL
266 , p_include_sales_orders BOOLEAN DEFAULT TRUE
267 , p_include_internal_orders BOOLEAN DEFAULT TRUE
268 , p_from_sales_order_id NUMBER DEFAULT NULL
269 , p_to_sales_order_id NUMBER DEFAULT NULL
270 , p_from_pick_slip_number NUMBER DEFAULT NULL
271 , p_to_pick_slip_number NUMBER DEFAULT NULL
272 , p_customer_id NUMBER DEFAULT NULL
273 , p_customer_category VARCHAR2 DEFAULT NULL
274 , p_delivery_id NUMBER DEFAULT NULL
275 , p_carrier_id NUMBER DEFAULT NULL
276 , p_ship_method VARCHAR2 DEFAULT NULL
277 , p_shipment_priority VARCHAR2 DEFAULT NULL
278 , p_trip_id NUMBER DEFAULT NULL
279 , p_from_shipment_date DATE DEFAULT NULL
280 , p_to_shipment_date DATE DEFAULT NULL
281 , p_ship_to_state VARCHAR2 DEFAULT NULL
282 , p_ship_to_country VARCHAR2 DEFAULT NULL
283 , p_ship_to_postal_code VARCHAR2 DEFAULT NULL
284 , p_from_number_of_order_lines NUMBER DEFAULT NULL
285 , p_to_number_of_order_lines NUMBER DEFAULT NULL
286 , p_manufacturing_type VARCHAR2 DEFAULT NULL
287 , p_from_job VARCHAR2 DEFAULT NULL
288 , p_to_job VARCHAR2 DEFAULT NULL
289 , p_assembly_id NUMBER DEFAULT NULL
290 , p_from_start_date DATE DEFAULT NULL
291 , p_to_start_date DATE DEFAULT NULL
292 , p_from_line VARCHAR2 DEFAULT NULL
293 , p_to_line VARCHAR2 DEFAULT NULL
294 , p_department_id NUMBER DEFAULT NULL
295 , p_include_replenishment BOOLEAN DEFAULT TRUE
296 , p_from_replenishment_mo VARCHAR2 DEFAULT NULL
297 , p_to_replenishment_mo VARCHAR2 DEFAULT NULL
298 , p_include_mo_transfer BOOLEAN DEFAULT TRUE
299 , p_include_mo_issue BOOLEAN DEFAULT TRUE
300 , p_from_transfer_issue_mo VARCHAR2 DEFAULT NULL
301 , p_to_transfer_issue_mo VARCHAR2 DEFAULT NULL
302 , p_include_lpn_putaway BOOLEAN DEFAULT TRUE
303 , p_include_staging_move BOOLEAN DEFAULT FALSE
304 , p_include_cycle_count BOOLEAN DEFAULT TRUE
305 , p_cycle_count_name VARCHAR2 DEFAULT NULL
306 , p_query_independent_tasks BOOLEAN DEFAULT TRUE
307 , p_query_planned_tasks BOOLEAN DEFAULT TRUE
308 , p_is_pending_plan BOOLEAN DEFAULT FALSE
309 , p_is_inprogress_plan BOOLEAN DEFAULT FALSE
310 , p_is_completed_plan BOOLEAN DEFAULT FALSE
311 , p_is_cancelled_plan BOOLEAN DEFAULT FALSE
312 , p_is_aborted_plan BOOLEAN DEFAULT FALSE
313 , p_activity_id NUMBER DEFAULT NULL
314 , p_plan_type_id NUMBER DEFAULT NULL
315 , p_op_plan_id NUMBER DEFAULT NULL) ;
316
317
318 PROCEDURE clear_globals;
319
320 END wms_plan_tasks_pvt;