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