1 PACKAGE flm_seq_reader_writer AS
2 /* $Header: FLMSQRWS.pls 120.1.12010000.2 2008/08/08 07:38:31 bgaddam ship $ */
3
4
5 --globals
6 g_user_id NUMBER;
7 g_login_id NUMBER;
8 g_job_prefix VARCHAR2(20) := null;
9
10 g_demand_type_SO NUMBER := 2;
11 g_demand_type_PO NUMBER := 100;
12
13 --global descriptive flex columns
14 g_attribute1 VARCHAR2(150) := null;
15 g_attribute2 VARCHAR2(150) := null;
16 g_attribute3 VARCHAR2(150) := null;
17 g_attribute4 VARCHAR2(150) := null;
18 g_attribute5 VARCHAR2(150) := null;
19 g_attribute6 VARCHAR2(150) := null;
20 g_attribute7 VARCHAR2(150) := null;
21 g_attribute8 VARCHAR2(150) := null;
22 g_attribute9 VARCHAR2(150) := null;
23 g_attribute10 VARCHAR2(150) := null;
24 g_attribute11 VARCHAR2(150) := null;
25 g_attribute12 VARCHAR2(150) := null;
26 g_attribute13 VARCHAR2(150) := null;
27 g_attribute14 VARCHAR2(150) := null;
28 g_attribute15 VARCHAR2(150) := null;
29
30 g_days_index INTEGER;
31 g_components_index NUMBER;
32
33
34 TYPE number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
35
36 TYPE schedule_rec_type IS RECORD
37 (
38 primary_item_id NUMBER,
39 org_id NUMBER,
40 wip_entity_id NUMBER,
41 planned_quantity NUMBER,
42 alt_rtg_designator VARCHAR2(10),
43 sch_start_date DATE,
44 sch_completion_date DATE,
45 sch_group_id NUMBER,
46 build_sequence NUMBER,
47 line_id NUMBER,
48 schedule_number VARCHAR2(30),
49 demand_type NUMBER,
50 demand_id NUMBER
51 );
52 TYPE schedule_rec_tbl_type IS TABLE OF schedule_rec_type
53 INDEX BY BINARY_INTEGER;
54
55
56 TYPE wip_flow_schedule_tbl IS TABLE OF wip_flow_schedules%rowtype
57 INDEX BY BINARY_INTEGER;
58
59
60 TYPE cto_line_record_type IS RECORD
61 (
62 demand_source_line VARCHAR2(30),
63 primary_item_id NUMBER,
64 organization_id NUMBER
65 );
66 TYPE cto_line_tbl_type IS TABLE OF cto_line_record_type INDEX BY BINARY_INTEGER;
67
68 TYPE comp_avail_record_type IS RECORD
69 (
70 inventory_item_id NUMBER,
71 requirement_date NUMBER,
72 qty NUMBER
73 );
74 TYPE comp_avail_tbl_type IS TABLE OF comp_avail_record_type INDEX BY BINARY_INTEGER;
75
76 g_days number_tbl_type;
77 sch_rec_tbl schedule_rec_tbl_type;
78 g_cto_line_tbl cto_line_tbl_type;
79 g_components flm_supply_demand.number_tbl_type;
80 g_qtys flm_supply_demand.number_tbl_type;
81
82
83
84
85 /******************************************************************
86 * To get a list of working days for a period (start, end) *
87 * The day will be in Julian format *
88 ******************************************************************/
89 PROCEDURE Init_Working_Days(p_organization_id IN NUMBER,
90 p_start_date IN NUMBER,
91 p_end_date IN NUMBER,
92 x_err_code OUT NOCOPY NUMBER,
93 x_err_msg OUT NOCOPY VARCHAR
94 );
95
96
97 /******************************************************************
98 * To get a list of working days for a period (start, end) *
99 * The day will be in Julian format *
100 ******************************************************************/
101 PROCEDURE Get_Working_Days(p_batch_size IN NUMBER,
102 x_days OUT NOCOPY number_tbl_type,
103 x_found IN OUT NOCOPY NUMBER,
104 x_done_flag OUT NOCOPY INTEGER,
105 x_err_code OUT NOCOPY NUMBER,
106 x_err_msg OUT NOCOPY VARCHAR
107 );
108
109
110 /******************************************************************
111 * To get available build sequence range for a period (start,end) *
112 * The range (start_seq, end_seq) is an open interval (exclusive) *
113 ******************************************************************/
114 PROCEDURE Get_BuildSeq_Range(p_line_id IN NUMBER,
115 p_organization_id IN NUMBER,
116 p_start_date IN NUMBER,
117 p_end_date IN NUMBER,
118 x_start_seq OUT NOCOPY NUMBER,
119 x_end_seq OUT NOCOPY NUMBER,
120 x_err_code OUT NOCOPY NUMBER,
121 x_err_msg OUT NOCOPY VARCHAR
122 );
123
124 /******************************************************************
125 * To initialize globals used by db writer *
126 ******************************************************************/
127 FUNCTION initialize_globals RETURN NUMBER;
128
129
130 /******************************************************************
131 * To add a schedule in schedules table and return wip_id and *
132 * schedule number *
133 ******************************************************************/
134 PROCEDURE add_sch_rec(i_org_id NUMBER,
135 i_primary_item_id NUMBER,
136 i_line_id NUMBER,
137 i_sch_start_date DATE,
138 i_sch_completion_date DATE,
139 i_planned_quantity NUMBER,
140 i_alt_rtg_designator VARCHAR2,
141 i_build_sequence NUMBER,
142 i_schedule_group_id NUMBER,
143 i_demand_type NUMBER,
144 i_demand_id NUMBER,
145 x_wip_entity_id IN OUT NOCOPY NUMBER,
146 x_schedule_number IN OUT NOCOPY VARCHAR2,
147 o_return_code OUT NOCOPY NUMBER
148 );
149
150
151 /******************************************************************
152 * To default the schedule columns and inserting the schedules *
153 ******************************************************************/
154 PROCEDURE create_schedules (o_return_code OUT NOCOPY NUMBER);
155
156
157 /******************************************************************
158 * To default/derive the attribute which are not passed for this *
159 * schedule and copy the attributes which are passed *
160 ******************************************************************/
161 PROCEDURE default_attributes(sch_rec_tbl IN OUT NOCOPY schedule_rec_tbl_type,
162 l_sch_tbl_to_insert IN OUT NOCOPY wip_flow_schedule_tbl,
163 o_return_code OUT NOCOPY NUMBER);
164
165
166 /******************************************************************
167 * Used to insert all the schedule in the table *
168 ******************************************************************/
169 PROCEDURE insert_schedules (i_schedules_tbl IN wip_flow_schedule_tbl,
170 o_return_code OUT NOCOPY NUMBER);
171
172
173 /******************************************************************
174 * gets the wip_entity_id and schedule_number from sequence *
175 ******************************************************************/
176 PROCEDURE get_wip_id_and_sch_num (o_wip_entity_id OUT NOCOPY NUMBER,
177 o_schedule_number OUT NOCOPY VARCHAR2);
178
179
180 /******************************************************************
181 * To get class code based on item and organization *
182 ******************************************************************/
183 FUNCTION get_class_code(i_org_id NUMBER, i_item_id NUMBER )RETURN VARCHAR;
184
185
186 /******************************************************************
187 * To get all account id based on class code *
188 ******************************************************************/
189 PROCEDURE get_account_ids (i_org_id NUMBER, i_class_code VARCHAR2,
190 i_material_act IN OUT NOCOPY NUMBER,
191 i_material_overhead_act IN OUT NOCOPY NUMBER,
192 i_resource_act IN OUT NOCOPY NUMBER,
193 i_outside_processing_act IN OUT NOCOPY NUMBER,
194 i_material_variance_act IN OUT NOCOPY NUMBER,
195 i_resource_variance_act IN OUT NOCOPY NUMBER,
196 i_outside_proc_variance_act IN OUT NOCOPY NUMBER,
197 i_std_cost_adjustment_act IN OUT NOCOPY NUMBER,
198 i_overhead_act IN OUT NOCOPY NUMBER,
199 i_overhead_variance_act IN OUT NOCOPY NUMBER);
200
201
202 /******************************************************************
203 * To get bom revision and bom revision date *
204 ******************************************************************/
205 PROCEDURE get_bom_rev_and_date (i_org_id NUMBER,
206 i_primary_item_id NUMBER,
207 i_sch_completion_date DATE,
208 o_bom_revision OUT NOCOPY VARCHAR,
209 o_bom_revision_date OUT NOCOPY DATE);
210
211
212 /******************************************************************
213 * To get routing revision and routing revision date *
214 ******************************************************************/
215 PROCEDURE get_rtg_rev_and_date (i_org_id NUMBER,
216 i_primary_item_id NUMBER,
217 i_sch_completion_date DATE,
218 o_rtg_revision OUT NOCOPY VARCHAR,
219 o_rtg_revision_date OUT NOCOPY DATE);
220
221
222 /******************************************************************
223 * To get alternate bom designator *
224 ******************************************************************/
225 PROCEDURE get_alt_bom_designator(i_org_id NUMBER,
226 i_primary_item_id NUMBER,
227 i_alt_rtg_designator VARCHAR2,
228 o_alt_bom_designator OUT NOCOPY VARCHAR2);
229
230
231 /******************************************************************
232 * To get the completion subinventory and locator *
233 ******************************************************************/
234 PROCEDURE get_completion_subinv_and_loc (i_org_id NUMBER,
235 i_primary_item_id NUMBER,
236 i_alt_rtg_designator VARCHAR2,
237 o_completion_subinv OUT NOCOPY VARCHAR2,
238 o_completion_locator_id OUT NOCOPY NUMBER);
239
240
241 /******************************************************************
242 * gets the demand class based on demand type *
243 ******************************************************************/
244 PROCEDURE get_demand_class(i_demand_type NUMBER,
245 i_demand_id NUMBER,
246 o_demand_class IN OUT NOCOPY VARCHAR2,
247 o_demand_header IN OUT NOCOPY NUMBER );
248
249 /******************************************************************
250 * gets the project and task added for Bug 6358519 *
251 ******************************************************************/
252 PROCEDURE get_project_task(i_demand_type NUMBER,
253 i_demand_id NUMBER,
254 o_project_id IN OUT NOCOPY NUMBER,
255 o_task_id IN OUT NOCOPY NUMBER );
256
257
258 /******************************************************************
259 * This procedure loops through schedules table, find out *
260 * unique item and alternate bom combinations, and call *
261 * explode for each unique combination *
262 ******************************************************************/
263 PROCEDURE explode_all_items(i_schedules_tbl IN OUT NOCOPY
264 wip_flow_schedule_tbl,
265 o_return_code OUT NOCOPY NUMBER);
266
267
268 /******************************************************************
269 * To explode the item bom *
270 ******************************************************************/
271 PROCEDURE explode_items (i_item_id IN NUMBER,
272 i_org_id IN NUMBER,
273 i_alt_bom IN VARCHAR2,
274 x_error_msg IN OUT NOCOPY VARCHAR2,
275 x_error_code IN OUT NOCOPY NUMBER);
276
277
278 /******************************************************************
279 * To update the mrp_recommendations based on schedules inserted *
280 ******************************************************************/
281 PROCEDURE update_mrp_recommendations(i_schedules_tbl IN
282 wip_flow_schedule_tbl,
283 o_return_code IN OUT NOCOPY NUMBER);
284
285 /******************************************************************
286 * To call the CTO API for each so line *
287 ******************************************************************/
288 PROCEDURE call_cto_api(o_return_code IN OUT NOCOPY NUMBER);
289
290
291 /******************************************************************
292 * call read_comp_avail to intialize global index and pl/sql table*
293 ******************************************************************/
294 PROCEDURE Init_Component_Avail(p_seq_task_id IN NUMBER,
295 p_organization_id IN NUMBER,
296 p_from_date IN DATE,
297 p_to_date IN DATE,
298 x_err_code OUT NOCOPY NUMBER,
299 x_err_msg OUT NOCOPY VARCHAR
300 );
301
302
303 /******************************************************************
304 * To get component availability of a sequencing task by batch *
305 ******************************************************************/
306 PROCEDURE Get_Component_Avail(
307 p_batch_size IN NUMBER,
308 x_ids OUT NOCOPY number_tbl_type,
309 x_qtys OUT NOCOPY number_tbl_type,
310 x_found IN OUT NOCOPY NUMBER,
311 x_done_flag OUT NOCOPY INTEGER,
312 x_err_code OUT NOCOPY NUMBER,
313 x_err_msg OUT NOCOPY VARCHAR
314 );
315
316 END flm_seq_reader_writer;
[Home] [Help]