1 PACKAGE gme_gantt_pkg AS
2 /* $Header: GMEGNTS.pls 120.3 2006/04/06 06:55:55 svgonugu noship $ */
3
4 /***********************************************************/
5 /* Oracle Process Manufacturing Process Execution APIs */
6 /* */
7 /* File Name: GMEGNTS.pls */
8 /* Contents: Package specification of Gantt chart routines*/
9 /* HISTORY */
10 /* SivakumarG 05-APR-2006 Bug#4867640 */
11 /* Added p_to_batch_no parameter to init_session procedure*/
12 /***********************************************************/
13
14 g_null_date VARCHAR2 (14) := '19700101010000';
15
16 TYPE gantttableofoperation IS TABLE OF VARCHAR2 (32000)
17 INDEX BY BINARY_INTEGER;
18
19 TYPE gantttableofactivity IS TABLE OF VARCHAR2 (32000)
20 INDEX BY BINARY_INTEGER;
21
22 TYPE gantttableofresource IS TABLE OF VARCHAR2 (32000)
23 INDEX BY BINARY_INTEGER;
24
25 TYPE gantttableofbatch IS TABLE OF VARCHAR2 (32000)
26 INDEX BY BINARY_INTEGER;
27
28 TYPE shopcalendartabletype IS TABLE OF VARCHAR2 (32000)
29 INDEX BY BINARY_INTEGER;
30
31 TYPE shifttabletype IS TABLE OF VARCHAR2 (32000)
32 INDEX BY BINARY_INTEGER;
33
34 PROCEDURE init_session (
35 p_organization_id IN NUMBER
36 ,p_org_code IN VARCHAR2
37 ,p_batch_no IN VARCHAR2
38 ,p_to_batch_no IN VARCHAR2 --Bug#5032359
39 ,p_from_date IN DATE
40 ,p_to_date IN DATE
41 ,p_resource IN VARCHAR2
42 ,p_product_no IN VARCHAR2
43 ,p_ingredient_no IN VARCHAR2
44 ,p_prim_rsrc_ind IN INTEGER
45 ,p_sec_rsrc_ind IN INTEGER
46 ,p_aux_rsrc_ind IN INTEGER
47 ,p_batch_type IN INTEGER
48 ,p_fpo_type IN INTEGER
49 ,p_released_status IN INTEGER
50 ,p_pending_status IN INTEGER
51 ,p_certified_status IN INTEGER);
52
53 PROCEDURE get_operations (
54 p_batch_id IN NUMBER
55 ,p_resource IN VARCHAR2
56 ,p_prim_rsrc_ind IN NUMBER
57 ,p_sec_rsrc_ind IN NUMBER
58 ,p_aux_rsrc_ind IN NUMBER
59 ,x_nb_operation OUT NOCOPY NUMBER
60 ,x_operations_table OUT NOCOPY gantttableofoperation);
61
62 PROCEDURE get_activities (
63 p_batch_id IN NUMBER
64 ,p_batchstep_no IN NUMBER
65 ,p_resource IN VARCHAR2
66 ,p_prim_rsrc_ind IN NUMBER
67 ,p_sec_rsrc_ind IN NUMBER
68 ,p_aux_rsrc_ind IN NUMBER
69 ,x_nb_activity OUT NOCOPY NUMBER
70 ,x_activity_table OUT NOCOPY gantttableofactivity);
71
72 PROCEDURE get_resources (
73 p_batch_id IN NUMBER
74 ,p_batchstep_no IN NUMBER
75 ,p_activity IN VARCHAR2
76 ,p_resource IN VARCHAR2
77 ,p_prim_rsrc_ind IN NUMBER
78 ,p_sec_rsrc_ind IN NUMBER
79 ,p_aux_rsrc_ind IN NUMBER
80 ,x_nb_resource OUT NOCOPY NUMBER
81 ,x_resource_table OUT NOCOPY gantttableofresource);
82
83 PROCEDURE get_batch_properties (
84 p_batch_id IN NUMBER
85 ,x_batch_properties_str OUT NOCOPY VARCHAR2);
86
87 PROCEDURE close_cursors;
88
89 PROCEDURE get_next_batch_set (
90 x_nb_batches OUT NOCOPY NUMBER
91 ,x_start_of_first_batch OUT NOCOPY DATE
92 ,x_batch_table OUT NOCOPY gantttableofbatch);
93
94 /*======================================================================
95 # Retrieve Shop calendar assigned to the given organization
96 #======================================================================*/
97 PROCEDURE fetch_shop_calendar (
98 p_organization_id IN NUMBER
99 ,p_date IN DATE
100 ,x_calendar_no OUT NOCOPY VARCHAR2
101 ,x_calendar_desc OUT NOCOPY VARCHAR2
102 ,x_calendar_start OUT NOCOPY VARCHAR2
103 ,x_calendar_end OUT NOCOPY VARCHAR2
104 ,x_calendar_assigned OUT NOCOPY VARCHAR2
105 ,x_return_code OUT NOCOPY VARCHAR2
106 ,x_error_msg OUT NOCOPY VARCHAR2);
107
108 /*======================================================================
109 # Retrieve working and non working days for a given date range
110 #======================================================================*/
111 PROCEDURE fetch_work_non_work_days (
112 p_organization_id IN NUMBER
113 ,p_from_date IN DATE
114 ,p_to_date IN DATE
115 ,x_calendar_no OUT NOCOPY VARCHAR2
116 ,x_calendar_desc OUT NOCOPY VARCHAR2
117 ,x_calendar_start OUT NOCOPY VARCHAR2
118 ,x_calendar_end OUT NOCOPY VARCHAR2
119 ,x_rec_count OUT NOCOPY NUMBER
120 ,x_shop_cal_tbl OUT NOCOPY shopcalendartabletype
121 ,x_calendar_assigned OUT NOCOPY VARCHAR2
122 ,x_return_code OUT NOCOPY VARCHAR2
123 ,x_error_msg OUT NOCOPY VARCHAR2);
124
125 /*======================================================================
126 # Check whether batch/FPO/Step can be re-scheduled based on shop calendar.
127 # Returns:
128 # S : Batch/Step can be rescheduled
129 # C : Prompt the user to reschedule without using shop calendar
130 # P : Prompt the user to reschedule ignoring contiguity constraints
131 # F : Batch/Step cannot be reschedule
132 #======================================================================*/
133 PROCEDURE validate_reschedule_event (
134 p_batch_id IN NUMBER
135 ,p_organization_id IN NUMBER
136 ,p_primary_prod_no IN VARCHAR2
137 ,p_start_date IN DATE
138 ,p_end_date IN DATE
139 ,p_entity_type IN VARCHAR2
140 ,x_return_code OUT NOCOPY VARCHAR2
141 ,x_error_msg OUT NOCOPY VARCHAR2);
142
143 TYPE batchidtab IS TABLE OF gme_batch_header.batch_id%TYPE;
144
145 TYPE batchnotab IS TABLE OF gme_batch_header.batch_no%TYPE;
146
147 TYPE batchstatustab IS TABLE OF gme_batch_header.batch_status%TYPE;
148
149 TYPE batchtypetab IS TABLE OF gme_batch_header.batch_type%TYPE;
150
151 TYPE batchdatetab IS TABLE OF gme_batch_header.plan_start_date%TYPE;
152
153 TYPE batchstepnotab IS TABLE OF gme_batch_steps.batchstep_no%TYPE;
154
155 TYPE batchqtytab IS TABLE OF gme_batch_steps.plan_step_qty%TYPE;
156
157 TYPE batchdesctab IS TABLE OF gmd_operations_b.oprn_desc%TYPE;
158
159 TYPE batchclasstab IS TABLE OF gmd_operation_class_b.oprn_class%TYPE;
160
161 TYPE batchumtab IS TABLE OF gmd_operations_b.process_qty_um%TYPE;
162
163 TYPE batchverstab IS TABLE OF gmd_operations_b.oprn_vers%TYPE;
164
165 TYPE activitycodetab IS TABLE OF gmd_activities_b.activity%TYPE;
166
167 TYPE costancodetab IS TABLE OF gmd_activities_b.cost_analysis_code%TYPE;
168
169 TYPE batchresourcestab IS TABLE OF cr_rsrc_mst.resources%TYPE;
170
171 TYPE batchintindtab IS TABLE OF gme_batch_step_resources.prim_rsrc_ind%TYPE;
172
173 TYPE itemnotab IS TABLE OF mtl_system_items_kfv.concatenated_segments%TYPE;
174
175 -- TYPE calendar_date IS TABLE OF mr_shcl_dtl.calendar_date%TYPE;
176
177 /*======================================================================
178 # For timezone changes.
179 # Returns:
180 # date : date in the client timezone
181 # HISTORY
182 # Bharati Satpute Bug3315440 21-JAN-2004
183 #======================================================================*/
184 FUNCTION date_to_clientdt (dateval DATE)
185 RETURN DATE;
186 END;
[Home] [Help]