1 PACKAGE WMS_CONTROL_BOARD AUTHID CURRENT_USER AS
2 /* $Header: WMSCBCPS.pls 120.1 2005/06/17 05:42:30 appldev $ */
3
4 -- Record Type for the task status distribution data for the performance chart
5 TYPE cb_chart_status_rec_type is RECORD
6 (
7 status VARCHAR2(400) := NULL
8 , task_count NUMBER := NULL
9 );
10
11 -- Table type definition for an array of cb_chart_status_rec_type records.
12 TYPE cb_chart_status_tbl_type is TABLE OF cb_chart_status_rec_type
13 INDEX BY BINARY_INTEGER;
14
15 -- Record Type for the task type distribution data for the performance chart
16 TYPE cb_chart_type_rec_type is RECORD
17 (
18 type VARCHAR2(100) := NULL
19 , task_count NUMBER := NULL
20 );
21
22 -- Table type definition for an array of cb_chart_type_rec_type records.
23 TYPE cb_chart_type_tbl_type is TABLE OF cb_chart_type_rec_type
24 INDEX BY BINARY_INTEGER;
25
26 -- constance for null values
27 ln_null CONSTANT NUMBER := 0;
28 lc_null CONSTANT VARCHAR2(4) := 'NULL';
29
30
31 -- Procedure definition to get task status distribution
32 -- Bug # 1800521, added new input parameter to status,task distribution procedure
33 -- so that sql has same where clause as the control board find window.
34 -- After making changes in the control board form to split the task and
35 -- exception views into separate ones, it makes sense to have the
36 -- performance chart use the same where clause that is set either through
37 -- the query find window or selecting the nodes in the trees
38 -- The where clause is now set as a form level parameter
39
40 /* --Bug#2483984 Performace Tuning of WMS Control Board
41 -- now there are separate FROM and WHERE clauses for active, pending and completed tasks */
42 PROCEDURE get_status_dist (
43 x_status_chart_data OUT NOCOPY /* file.sql.39 change */ cb_chart_status_tbl_type
44 , x_status_data_count OUT NOCOPY /* file.sql.39 change */ NUMBER
45 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
46 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
47 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
48 , p_cq_type IN NUMBER
49 , p_at_from IN VARCHAR2
50 , p_pt_from IN VARCHAR2
51 , p_ct_from IN VARCHAR2
52 , p_at_where IN VARCHAR2
53 , p_pt_where IN VARCHAR2
54 , p_ct_where IN VARCHAR2
55 , p_acy_from IN VARCHAR2
56 , p_pcy_from IN VARCHAR2
57 , p_ccy_from IN VARCHAR2
58 , p_acy_where IN VARCHAR2
59 , p_pcy_where IN VARCHAR2
60 , p_ccy_where IN VARCHAR2
61 );
62
63 -- Procedure definition to get task type distribution
64 /* --Bug#2483984 Performace Tuning of WMS Control Board
65 -- now there are separate FROM and WHERE clauses for active, pending and completed tasks */
66 PROCEDURE get_type_dist(
67 x_type_chart_data OUT NOCOPY /* file.sql.39 change */ cb_chart_type_tbl_type
68 , x_type_data_count OUT NOCOPY /* file.sql.39 change */ NUMBER
69 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
70 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
71 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
72 , p_cq_type IN NUMBER
73 , p_at_from IN VARCHAR2
74 , p_pt_from IN VARCHAR2
75 , p_ct_from IN VARCHAR2
76 , p_at_where IN VARCHAR2
77 , p_pt_where IN VARCHAR2
78 , p_ct_where IN VARCHAR2
79 , p_acy_from IN VARCHAR2
80 , p_pcy_from IN VARCHAR2
81 , p_ccy_from IN VARCHAR2
82 , p_acy_where IN VARCHAR2
83 , p_pcy_where IN VARCHAR2
84 , p_ccy_where IN VARCHAR2
85 );
86
87 -----------------------------------------------------------
88 -- Procedure to check whether a change in status is validate
89 -----------------------------------------------------------
90 FUNCTION is_status_valid(
91 p_from_status IN NUMBER
92 , p_to_status IN NUMBER ) RETURN VARCHAR2;
93
94
95 /*****kkoothan*** Part of Fix#2163139 *******/
96 /*** Added one more default null parameter ***/
97 /*** p_transaction_source_type_id in ***/
98 /*** lock_row to handle cycle count Tasks. ***/
99 PROCEDURE lock_row(
100 p_rowid IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
101 , p_transaction_temp_id IN NUMBER
102 , p_task_id IN NUMBER
103 , p_status IN NUMBER
104 , p_priority IN NUMBER
105 , p_person_id IN NUMBER
106 , p_person_resource_id IN NUMBER
107 , p_transaction_source_type_id IN NUMBER DEFAULT NULL --kkoothan
108 );
109
110 -- Procedure definition to manipulate the warehosue task
111 -- resource information and priority.
112 /*****kkoothan*** Part of Fix#2163139 *******/
113 /*** Added one more default null parameter ***/
114 /*** p_transaction_source_type_id in ***/
115 /*** task_manipulator to handle cycle count Tasks***/
116 PROCEDURE task_manipulator(
117 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
118 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
119 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
120 ,x_task_id OUT NOCOPY /* file.sql.39 change */ NUMBER
121 ,p_updated_by IN NUMBER
122 ,p_task_id IN NUMBER DEFAULT NULL
123 ,p_transaction_temp_id IN NUMBER
124 ,p_organization_id IN NUMBER
125 ,p_person_resource_id IN NUMBER
126 ,p_person_id IN NUMBER
127 ,p_priority IN NUMBER
128 ,p_from_status IN NUMBER
129 ,p_to_status IN NUMBER
130 ,p_user_task_type IN NUMBER DEFAULT NULL
131 ,p_task_type IN NUMBER DEFAULT NULL
132 ,p_transaction_source_type_id IN NUMBER DEFAULT NULL -- kkoothan
133 ,p_last_update_date IN DATE DEFAULT NULL ); /* Bug 2372652 */
134 -- Bug# 1728558, added p_user_task_type parameter
135
136 PROCEDURE mydebug(msg in varchar2);
137
138 END WMS_CONTROL_BOARD;
[Home] [Help]