eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE: APPS.WMS_WAVEPLAN_TASKS_PVT

Source


1 PACKAGE wms_waveplan_tasks_pvt AS
2 /* $Header: WMSVTKPS.pls 120.8.12010000.1 2008/07/28 18:38:16 appldev ship $ */
3 
4 TYPE transaction_temp_table_type IS TABLE OF wms_waveplan_tasks_temp.transaction_temp_id%TYPE INDEX BY BINARY_INTEGER;
5 TYPE task_type_id_table_type     IS TABLE OF wms_waveplan_tasks_temp.task_type_id%TYPE INDEX BY BINARY_INTEGER;
6 TYPE result_table_type           IS TABLE OF wms_waveplan_tasks_temp.result%TYPE INDEX BY BINARY_INTEGER;
7 TYPE message_table_type          IS TABLE OF wms_waveplan_tasks_temp.error%TYPE INDEX BY BINARY_INTEGER;
8 TYPE task_id_table_type          IS TABLE OF wms_waveplan_tasks_temp.task_id%TYPE INDEX BY BINARY_INTEGER;
9 TYPE time_per_task_table_type    IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
10 TYPE time_uom_table_type         IS TABLE OF bom_resources.unit_of_measure%TYPE INDEX BY BINARY_INTEGER;
11 
12 TYPE lookup_meaning_table IS TABLE OF mfg_lookups.meaning%TYPE INDEX BY BINARY_INTEGER;
13 TYPE row_ids_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
14 TYPE lookup_meaning_table_type IS TABLE OF mfg_lookups.meaning%TYPE INDEX BY BINARY_INTEGER;
15 
16 
17 g_task_types                   lookup_meaning_table;
18 g_task_types_orig              lookup_meaning_table;
19 g_status_codes                 lookup_meaning_table;
20 g_status_codes_orig            lookup_meaning_table;
21 g_plan_task_types              lookup_meaning_table;
22 g_plan_task_types_orig         lookup_meaning_table;
23 g_plan_status_codes            lookup_meaning_table;
24 g_plan_status_codes_orig       lookup_meaning_table;
25 
26 g_task_type_pick	CONSTANT NUMBER := 1;
27 g_task_type_putaway	CONSTANT NUMBER := 2;
28 g_task_type_cycle_count CONSTANT NUMBER := 3;
29 g_task_type_replenish	CONSTANT NUMBER := 4;
30 g_task_type_mo_transfer CONSTANT NUMBER := 5;
31 g_task_type_mo_issue	CONSTANT NUMBER := 6;
32 g_task_type_staging_move	CONSTANT NUMBER := 7;
33 g_task_type_inspection		CONSTANT NUMBER := 8;
34 
35 
36 
37 --Procedures for labor estimations
38 PROCEDURE mark_rows(p_transaction_temp_id IN  wms_waveplan_tasks_pvt.transaction_temp_table_type,
39 	       p_task_type_id        IN  wms_waveplan_tasks_pvt.task_type_id_table_type,
40 	       x_return_status       OUT nocopy varchar2);
41 
42 PROCEDURE unmark_rows;
43 
44 PROCEDURE query_tasks
45  (p_add                         BOOLEAN  DEFAULT FALSE,
46   p_organization_id             NUMBER   DEFAULT NULL,
47   p_subinventory_code           VARCHAR2 DEFAULT NULL,
48   p_locator_id                  NUMBER   DEFAULT NULL,
49   p_to_subinventory_code        VARCHAR2 DEFAULT NULL,
50   p_to_locator_id               NUMBER   DEFAULT NULL,
51   p_inventory_item_id           NUMBER   DEFAULT NULL,
52   p_category_set_id             NUMBER   DEFAULT NULL,
53   p_item_category_id            NUMBER   DEFAULT NULL,
54   p_person_id                   NUMBER   DEFAULT NULL,
55   p_person_resource_id          NUMBER   DEFAULT NULL,
56   p_equipment_type_id           NUMBER   DEFAULT NULL,
57   p_machine_resource_id         NUMBER   DEFAULT NULL,
58   p_machine_instance            VARCHAR2 DEFAULT NULL,
59   p_user_task_type_id           NUMBER   DEFAULT NULL,
60   p_from_task_quantity          NUMBER   DEFAULT NULL,
61   p_to_task_quantity            NUMBER   DEFAULT NULL,
62   p_from_task_priority          NUMBER   DEFAULT NULL,
63   p_to_task_priority            NUMBER   DEFAULT NULL,
64   p_from_creation_date          DATE     DEFAULT NULL,
65   p_to_creation_date            DATE     DEFAULT NULL,
66   p_is_unreleased               BOOLEAN  DEFAULT FALSE,
67   p_is_pending                  BOOLEAN  DEFAULT FALSE,
68   p_is_queued                   BOOLEAN  DEFAULT FALSE,
69   p_is_dispatched               BOOLEAN  DEFAULT FALSE,
70   p_is_active                   BOOLEAN  DEFAULT FALSE,
71   p_is_loaded                   BOOLEAN  DEFAULT FALSE,
72   p_is_completed                BOOLEAN  DEFAULT FALSE,
73   p_include_inbound             BOOLEAN  DEFAULT FALSE,
74   p_include_outbound            BOOLEAN  DEFAULT FALSE,
75   p_include_crossdock           BOOLEAN  DEFAULT FALSE,
76   p_include_manufacturing       BOOLEAN  DEFAULT FALSE,
77   p_include_warehousing         BOOLEAN  DEFAULT FALSE,
78   p_from_po_header_id           NUMBER   DEFAULT NULL,
79   p_to_po_header_id             NUMBER   DEFAULT NULL,
80   p_from_purchase_order         VARCHAR2 DEFAULT NULL,
81   p_to_purchase_order           VARCHAR2 DEFAULT NULL,
82   p_from_rma_header_id          NUMBER   DEFAULT NULL,
83   p_to_rma_header_id            NUMBER   DEFAULT NULL,
84   p_from_rma                    VARCHAR2 DEFAULT NULL,
85   p_to_rma                      VARCHAR2 DEFAULT NULL,
86   p_from_requisition_header_id  NUMBER   DEFAULT NULL,
87   p_to_requisition_header_id    NUMBER   DEFAULT NULL,
88   p_from_requisition            VARCHAR2 DEFAULT NULL,
89   p_to_requisition              VARCHAR2 DEFAULT NULL,
90   p_from_shipment_number        VARCHAR2 DEFAULT NULL,
91   p_to_shipment_number          VARCHAR2 DEFAULT NULL,
92   p_include_sales_orders        BOOLEAN  DEFAULT TRUE,
93   p_include_internal_orders     BOOLEAN  DEFAULT TRUE,
94   p_from_sales_order_id         NUMBER   DEFAULT NULL,
95   p_to_sales_order_id           NUMBER   DEFAULT NULL,
96   p_from_pick_slip_number       NUMBER   DEFAULT NULL,
97   p_to_pick_slip_number         NUMBER   DEFAULT NULL,
98   p_customer_id                 NUMBER   DEFAULT NULL,
99   p_customer_category           VARCHAR2 DEFAULT NULL,
100   p_delivery_id                 NUMBER   DEFAULT NULL,
101   p_carrier_id                  NUMBER   DEFAULT NULL,
102   p_ship_method                 VARCHAR2 DEFAULT NULL,
103   p_shipment_priority           VARCHAR2 DEFAULT NULL,
104   p_trip_id                     NUMBER   DEFAULT NULL,
105   p_from_shipment_date          DATE     DEFAULT NULL,
106   p_to_shipment_date            DATE     DEFAULT NULL,
107   p_ship_to_state               VARCHAR2 DEFAULT NULL,
108   p_ship_to_country             VARCHAR2 DEFAULT NULL,
109   p_ship_to_postal_code         VARCHAR2 DEFAULT NULL,
110   p_from_number_of_order_lines  NUMBER   DEFAULT NULL,
111   p_to_number_of_order_lines    NUMBER   DEFAULT NULL,
112   p_manufacturing_type          VARCHAR2 DEFAULT NULL,
113   p_from_job                    VARCHAR2 DEFAULT NULL,
114   p_to_job                      VARCHAR2 DEFAULT NULL,
115   p_assembly_id                 NUMBER   DEFAULT NULL,
116   p_from_start_date             DATE     DEFAULT NULL,
117   p_to_start_date               DATE     DEFAULT NULL,
118   p_from_line                   VARCHAR2 DEFAULT NULL,
119   p_to_line                     VARCHAR2 DEFAULT NULL,
120   p_department_id               NUMBER   DEFAULT NULL,
121   p_include_replenishment       BOOLEAN  DEFAULT TRUE,
122   p_from_replenishment_mo       VARCHAR2 DEFAULT NULL,
123   p_to_replenishment_mo         VARCHAR2 DEFAULT NULL,
124   p_include_mo_transfer         BOOLEAN  DEFAULT TRUE,
125   p_include_mo_issue            BOOLEAN  DEFAULT TRUE,
126   p_from_transfer_issue_mo      VARCHAR2 DEFAULT NULL,
127   p_to_transfer_issue_mo        VARCHAR2 DEFAULT NULL,
128   p_include_lpn_putaway         BOOLEAN  DEFAULT TRUE,
129   p_include_staging_move        BOOLEAN  DEFAULT FALSE,
130   p_include_cycle_count         BOOLEAN  DEFAULT TRUE,
131   p_cycle_count_name            VARCHAR2 DEFAULT NULL,
132   x_record_count            OUT NOCOPY NUMBER,
133   x_return_status           OUT NOCOPY VARCHAR2,
134   x_msg_data                OUT NOCOPY VARCHAR2,
135   x_msg_count               OUT NOCOPY NUMBER,
136   p_query_independent_tasks  BOOLEAN DEFAULT TRUE,
137   p_query_planned_tasks      BOOLEAN DEFAULT TRUE,
138   p_is_pending_plan             BOOLEAN DEFAULT FALSE,
139   p_is_inprogress_plan             BOOLEAN DEFAULT FALSE,
140   p_is_completed_plan             BOOLEAN DEFAULT FALSE,
141   p_is_cancelled_plan             BOOLEAN DEFAULT FALSE,
142   p_is_aborted_plan             BOOLEAN DEFAULT FALSE,
143   p_activity_id                 NUMBER  DEFAULT NULL,
144   p_plan_type_id                 NUMBER  DEFAULT NULL,
145   p_op_plan_id                 NUMBER  DEFAULT NULL,
146    -- R12 : Additional Query Criteria
147    p_item_type_code                            VARCHAR2 DEFAULT NULL,
148    p_age_uom_code                              VARCHAR2 DEFAULT NULL,
149    p_age_min                                   NUMBER DEFAULT NULL,
150    p_age_max                                   NUMBER DEFAULT NULL,
151    p_order_type_id                             NUMBER DEFAULT NULL,
152    p_time_till_shipment_uom_code               VARCHAR2 DEFAULT NULL,
153    p_time_till_shipment                        NUMBER DEFAULT NULL,
154    p_time_till_appt_uom_code                   VARCHAR2 DEFAULT NULL,
155    p_time_till_appt                            NUMBER DEFAULT NULL,
156    p_summary_mode				NUMBER DEFAULT 0
157    -- R12 : Additional Query Criteria
158   );
159 
160 PROCEDURE update_task (
161       p_transaction_temp_id     IN              wms_waveplan_tasks_pvt.transaction_temp_table_type,
162       p_task_type_id            IN              wms_waveplan_tasks_pvt.task_type_id_table_type,
163       p_employee                IN              VARCHAR2,
164       p_employee_id             IN              NUMBER,
165       p_user_task_type          IN              VARCHAR2,
166       p_user_task_type_id       IN              NUMBER,
167       p_effective_start_date    IN              DATE,
168       p_effective_end_date      IN              DATE,
169       p_person_resource_id      IN              NUMBER,
170       p_person_resource_code    IN              VARCHAR2,
171       p_force_employee_change   IN              BOOLEAN,
172       p_to_status               IN              VARCHAR2,
173       p_to_status_id            IN              NUMBER,
174       p_update_priority_type    IN              VARCHAR2,
175       p_update_priority         IN              NUMBER,
176       p_clear_priority          IN              VARCHAR2,
177       x_result                  OUT NOCOPY      wms_waveplan_tasks_pvt.result_table_type,
178       x_message                 OUT NOCOPY      wms_waveplan_tasks_pvt.message_table_type,
179       x_task_id                 OUT NOCOPY      wms_waveplan_tasks_pvt.task_id_table_type,
180       x_return_status           OUT NOCOPY      VARCHAR2,
181       x_return_msg              OUT NOCOPY      VARCHAR2,
182       x_msg_count               OUT NOCOPY      NUMBER);
183 
184 PROCEDURE remove_tasks (p_transaction_temp_id   IN  wms_waveplan_tasks_pvt.transaction_temp_table_type,
185 			x_record_count          OUT NOCOPY NUMBER,
186 			x_return_status         OUT nocopy VARCHAR2,
187 			x_msg_data              OUT nocopy varchar2);
188 
189 PROCEDURE save_tasks(
190 		     p_task_action               VARCHAR2,
191 		     p_commit                    BOOLEAN,
192 		     p_user_id                   NUMBER,
193 		     p_login_id                  NUMBER,
194 		     x_save_count     OUT nocopy NUMBER,
195 		     x_return_status  OUT nocopy VARCHAR2,
196 		     x_msg_data       OUT nocopy VARCHAR2,
197 		     x_msg_count      OUT nocopy NUMBER);
198 
199 -- Record Type for the task status distribution data for the performance chart
200 TYPE cb_chart_status_rec_type is RECORD
201   (status		VARCHAR2(400) 	:= NULL,
202    task_count           NUMBER          := NULL );
203 
204 -- Record Type for the Task Summary information
205 TYPE wms_task_summary_rec_type IS RECORD
206   ( wms_task_type	NUMBER,
207     task_count		NUMBER   := 0);
208 
209 -- Table Type for the task summary information
210 TYPE wms_task_summary_tbl_type IS TABLE OF wms_task_summary_rec_type
211 	INDEX BY BINARY_INTEGER;
212 
213 g_wms_task_summary_tbl	wms_task_summary_tbl_type;
214 
215 -- Table type definition for an array of cb_chart_status_rec_type records.
216 TYPE cb_chart_status_tbl_type is TABLE OF cb_chart_status_rec_type
217   INDEX BY BINARY_INTEGER;
218 
219 
220 -- Procedure definition to get task status distribution
221 PROCEDURE get_status_dist(x_status_chart_data	OUT nocopy cb_chart_status_tbl_type,
222 			  x_status_data_count	OUT nocopy NUMBER,
223 			  x_return_status	OUT nocopy VARCHAR2,
224 			  x_msg_count	       	OUT nocopy NUMBER,
225 			  x_msg_data            OUT nocopy VARCHAR2,
226 			  p_task_type_id        IN         NUMBER DEFAULT NULL);
227 
228 
229 -- Record Type for the task type distribution data for the performance chart
230 TYPE cb_chart_type_rec_type is RECORD
231   (type		VARCHAR2(100) 	:= NULL,
232    task_count	NUMBER  	:= NULL);
233 
234 -- Table type definition for an array of cb_chart_type_rec_type records.
235 TYPE cb_chart_type_tbl_type is TABLE OF cb_chart_type_rec_type
236   INDEX BY BINARY_INTEGER;
237 
238 -- Procedure definition to get task type distribution
239 PROCEDURE get_type_dist(x_type_chart_data    OUT nocopy	 cb_chart_type_tbl_type,
240 			x_type_data_count    OUT nocopy  NUMBER,
241 			x_return_status	     OUT nocopy	 VARCHAR2,
242 			x_msg_count	     OUT nocopy	 NUMBER,
243 			x_msg_data     	     OUT nocopy	 VARCHAR2,
244 			p_task_type_id       IN          NUMBER DEFAULT NULL);
245 
246 PROCEDURE calculate_summary(p_calculate_time    IN  BOOLEAN  DEFAULT FALSE,
247 			    p_time_per_task     IN  wms_waveplan_tasks_pvt.time_per_task_table_type,
248 			    p_time_per_task_uom IN  wms_waveplan_tasks_pvt.time_uom_table_type,
249 			    p_time_uom_code     IN  VARCHAR2 DEFAULT NULL,
250 			    p_time_uom          IN  VARCHAR2 DEFAULT NULL,
251 			    p_calculate_volume  IN  BOOLEAN  DEFAULT FALSE,
252 			    p_volume_uom_code   IN  VARCHAR2 DEFAULT NULL,
253 			    p_volume_uom        IN  VARCHAR2 DEFAULT NULL,
254 			    p_calculate_weight  IN  BOOLEAN  DEFAULT FALSE,
255 			    p_weight_uom_code   IN  VARCHAR2 DEFAULT NULL,
256 			    p_weight_uom        IN  VARCHAR2 DEFAULT NULL,
257 			    x_total_tasks       OUT nocopy NUMBER,
258 			    x_total_time        OUT nocopy NUMBER,
259 			    x_total_weight      OUT nocopy NUMBER,
260 			    x_total_volume      OUT nocopy NUMBER,
261 			    x_return_status     OUT nocopy VARCHAR2,
262 			    x_msg_data          OUT nocopy VARCHAR2,
263 			    x_msg_count         OUT nocopy NUMBER);
264 
265 FUNCTION get_generic_select
266  (p_is_unreleased               BOOLEAN DEFAULT FALSE,
267   p_is_pending                  BOOLEAN DEFAULT FALSE,
268   p_is_queued                   BOOLEAN DEFAULT FALSE,
269   p_is_dispatched               BOOLEAN DEFAULT FALSE,
270   p_is_active                   BOOLEAN DEFAULT FALSE,
271   p_is_loaded                   BOOLEAN DEFAULT FALSE,
272   p_is_completed                BOOLEAN DEFAULT FALSE,
273   p_populate_merged_tasks       BOOLEAN DEFAULT FALSE)
274   RETURN VARCHAR2;
275 
276 FUNCTION get_generic_from
277  (p_is_queued                   BOOLEAN DEFAULT FALSE,
278   p_is_dispatched               BOOLEAN DEFAULT FALSE,
279   p_is_active                   BOOLEAN DEFAULT FALSE,
280   p_is_loaded                   BOOLEAN DEFAULT FALSE,
281   p_is_completed                BOOLEAN DEFAULT FALSE,
282   p_item_category_id            NUMBER  DEFAULT NULL,
283   p_category_set_id             NUMBER  DEFAULT NULL,
284   p_populate_merged_tasks       BOOLEAN DEFAULT FALSE)
285   RETURN VARCHAR2;
286 
287 FUNCTION get_generic_where
288  (p_add                         BOOLEAN  DEFAULT FALSE,
289   p_organization_id             NUMBER   DEFAULT NULL,
290   p_subinventory_code           VARCHAR2 DEFAULT NULL,
291   p_locator_id                  NUMBER   DEFAULT NULL,
292   p_to_subinventory_code        VARCHAR2 DEFAULT NULL,
293   p_to_locator_id               NUMBER   DEFAULT NULL,
294   p_inventory_item_id           NUMBER   DEFAULT NULL,
295   p_category_set_id             NUMBER   DEFAULT NULL,
296   p_item_category_id            NUMBER   DEFAULT NULL,
297   p_person_id                   NUMBER   DEFAULT NULL,
298   p_person_resource_id          NUMBER   DEFAULT NULL,
299   p_equipment_type_id           NUMBER   DEFAULT NULL,
300   p_machine_resource_id         NUMBER   DEFAULT NULL,
301   p_machine_instance            VARCHAR2 DEFAULT NULL,
302   p_user_task_type_id           NUMBER   DEFAULT NULL,
303   p_from_task_quantity          NUMBER   DEFAULT NULL,
304   p_to_task_quantity            NUMBER   DEFAULT NULL,
305   p_from_task_priority          NUMBER   DEFAULT NULL,
306   p_to_task_priority            NUMBER   DEFAULT NULL,
307   p_from_creation_date          DATE     DEFAULT NULL,
308   p_to_creation_date            DATE     DEFAULT NULL,
309   p_include_cycle_count         BOOLEAN  DEFAULT FALSE,
310   p_is_unreleased               BOOLEAN  DEFAULT FALSE,
311   p_is_pending                  BOOLEAN  DEFAULT FALSE,
312   p_is_queued                   BOOLEAN  DEFAULT FALSE,
313   p_is_dispatched               BOOLEAN  DEFAULT FALSE,
314   p_is_active                   BOOLEAN  DEFAULT FALSE,
315   p_is_loaded                   BOOLEAN  DEFAULT FALSE,
316   p_is_completed                BOOLEAN  DEFAULT FALSE,
317   p_populate_merged_tasks       BOOLEAN  DEFAULT FALSE,
318   p_outbound_tasks_cycle        BOOLEAN  DEFAULT FALSE,      -- bug #4661615
319    -- R12 : Additional Query Criteria
320    p_item_type_code                            VARCHAR2 DEFAULT NULL,
321    p_age_uom_code                              VARCHAR2 DEFAULT NULL,
322    p_age_min                                   NUMBER DEFAULT NULL,
323    p_age_max                                   NUMBER DEFAULT NULL
324    -- R12 : Additional Query Criteria
325   )
326   RETURN VARCHAR2;
327 
328 PROCEDURE cancel_plans(x_return_status    OUT nocopy VARCHAR2,
329                        x_ret_code          OUT nocopy wms_waveplan_tasks_pvt.message_table_type,
330              p_transaction_temp_table wms_waveplan_tasks_pvt.transaction_temp_table_type);
331 
332    PROCEDURE cancel_task (
333       p_transaction_temp_id     IN              wms_waveplan_tasks_pvt.transaction_temp_table_type,
334       p_task_type_id            IN              wms_waveplan_tasks_pvt.task_type_id_table_type,
335       p_is_crossdock            IN              BOOLEAN DEFAULT FALSE, /* Bug 5623122 */
336       x_result                  OUT NOCOPY      wms_waveplan_tasks_pvt.result_table_type,
337       x_message                 OUT NOCOPY      wms_waveplan_tasks_pvt.message_table_type,
338       x_task_id                 OUT NOCOPY      wms_waveplan_tasks_pvt.task_id_table_type,
339       x_return_status           OUT NOCOPY      VARCHAR2,
340       x_return_msg              OUT NOCOPY      VARCHAR2,
341       x_msg_count               OUT NOCOPY      NUMBER
342    );
343 
344 
345 
346 FUNCTION get_final_query RETURN VARCHAR2;
347 
348 PROCEDURE set_status_codes;
349 
350 PROCEDURE set_task_type;
351 
352 --Change
353 PROCEDURE set_plan_task_types;
354 
355   PROCEDURE set_plan_status_codes;
356 PROCEDURE find_visible_columns;
357 
358 FUNCTION get_task_summary RETURN wms_task_summary_tbl_type;
359 
360 PROCEDURE set_task_summary(p_wms_task_summary_tbl wms_task_summary_tbl_type);
361 
362 END wms_waveplan_tasks_pvt;