[Home] [Help]
PACKAGE: APPS.RLM_FORECAST_SV
Source
1 PACKAGE RLM_FORECAST_SV as
2 /*$Header: RLMDPFPS.pls 120.2 2008/02/21 09:19:23 sunilku ship $*/
3 --<TPA_PUBLIC_NAME=RLM_TPA_SV>
4 --<TPA_PUBLIC_FILE_NAME=RLMTPDP>
5 /*===========================================================================
6 PACKAGE NAME: RLM_FORECAST_SV
7
8 DESCRIPTION: Contains the server side code for Manage MRP Forecast
9 API of RLM Demand Processor.
10
11 CLIENT/SERVER: Server
12
13 LIBRARY NAME: None
14
15 OWNER: abkulkar
16
17 PROCEDURE/FUNCTIONS:
18
19 GLOBALS:
20
21 =========================================================================== */
22
23
24 TYPE t_Cursor_ref IS REF CURSOR;
25 --
26 k_ORIGINAL CONSTANT VARCHAR2(30) := 'ORIGINAL';
27 k_REPLACE CONSTANT VARCHAR2(30) := 'REPLACE';
28 k_REPLACE_ALL CONSTANT VARCHAR2(30) := 'REPLACE_ALL';
29 k_CHANGE CONSTANT VARCHAR2(30) := 'CHANGE';
30 k_CANCEL CONSTANT VARCHAR2(30) := 'CANCELLATION';
31 k_DELETE CONSTANT VARCHAR2(30) := 'DELETE';
32 k_INSERT CONSTANT VARCHAR2(30) := 'INSERT';
33 k_UPDATE CONSTANT VARCHAR2(30) := 'UPDATE';
34 k_UPDATE_ATTR CONSTANT VARCHAR2(30) := 'UPDATE_ATTR';
35 k_CONFIRMATION CONSTANT VARCHAR2(30) := 'CONFIRMATION';
36 k_ADD CONSTANT VARCHAR2(30) := 'ADD';
37 k_RECEIPT CONSTANT VARCHAR2(80) := 'RECEIPT';
38 --
39 k_PLANNING CONSTANT VARCHAR2(30) := 'PLANNING_RELEASE';
40 k_SHIPPING CONSTANT VARCHAR2(30) := 'SHIPPING';
41 k_SEQUENCED CONSTANT VARCHAR2(30) := 'SEQUENCED';
42 --
43 k_TDEBUG CONSTANT NUMBER := rlm_CORE_SV.C_LEVEL10;
44 k_SDEBUG CONSTANT NUMBER := rlm_CORE_SV.C_LEVEL11;
45 k_DEBUG CONSTANT NUMBER := rlm_CORE_SV.C_LEVEL12;
46 --
47 k_VNULL CONSTANT VARCHAR2(25) := 'THIS_IS_A_NULL_VALUE';
48 k_NNULL CONSTANT NUMBER := -19999999999;
49 k_DNULL CONSTANT DATE := to_date('01/01/1930','dd/mm/yyyy');
50 --
51 k_ATS CONSTANT VARCHAR2(1) := 'Y';
52 k_NATS CONSTANT VARCHAR2(1) := 'N';
53 --
54 k_PAST_DUE_FIRM CONSTANT VARCHAR2(1) := '0';
55 k_FIRM CONSTANT VARCHAR2(1) := '1';
56 k_FORECAST CONSTANT VARCHAR2(1) := '2';
57 k_MRP_FORECAST CONSTANT VARCHAR2(1) := '6';
58 --
59 k_Weekly CONSTANT VARCHAR2(1) := '2';
60 k_Quarterly CONSTANT VARCHAR2(1) :='5';
61 k_Monthly CONSTANT VARCHAR2(1) :='3';
62 k_Daily CONSTANT VARCHAR2(1) := '1';
63 k_Flexible CONSTANT VARCHAR2(1) := '4';
64 e_group_error EXCEPTION;
65 k_REPLACE_FLAG BOOLEAN := TRUE;
66
67 TYPE t_designator_rec IS RECORD(designator VARCHAR2(30),
68 organization_id NUMBER); --Bugfix 6817494
69
70 TYPE t_designator_tab IS TABLE OF t_designator_rec INDEX BY BINARY_INTEGER;
71
72 g_designator_tab t_designator_tab;
73 --
74 /*===========================================================================
75
76 PROCEDURE NAME: ManageForecast
77
78 DESCRIPTION: Cover function for Manage MRP Forecast API
79
80 PARAMETERS: x_InterfaceHeaderId IN NUMBER
81
82 DESIGN REFERENCES: RLADPHLD.rtf
83 RLADPRDD.rtf
84
85 ALGORITHM:
86
87 NOTES:
88
89 OPEN ISSUES:
90
91 CLOSED ISSUES:
92
93 CHANGE HISTORY: created abkulkar 03/05/99
94 ===========================================================================*/
95 PROCEDURE ManageForecast(x_InterfaceHeaderId IN NUMBER,
96 x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
97 x_Group_rec IN OUT NOCOPY rlm_dp_sv.t_Group_rec,
98 x_ReturnStatus OUT NOCOPY NUMBER);
99 --<TPA_PUBLIC_NAME>
100 /*===========================================================================
101
102 PROCEDURE NAME: ManageGroupForecast
103
104 DESCRIPTION: function for Manage MRP Forecast API
105
106 PARAMETERS: x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE
107 x_Group_rec IN t_Group_rec
108 x_returnStatus OUT NOCOPY NUMBER
109
110 DESIGN REFERENCES: RLADPHLD.rtf
111 RLADPRDD.rtf
112
113 ALGORITHM:
114
115 NOTES:
116
117 OPEN ISSUES:
118
119 CLOSED ISSUES:
120
121 CHANGE HISTORY: created mnandell 03/05/99
122 ===========================================================================*/
123
124 PROCEDURE ManageGroupForecast(x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
125 x_Group_rec IN OUT NOCOPY rlm_dp_sv.t_Group_rec,
126 x_forecast IN OUT NOCOPY
127 mrp_forecast_interface_pk.t_forecast_interface,
128 x_designator IN OUT NOCOPY
129 mrp_forecast_interface_pk.t_forecast_designator,
130 x_ReturnStatus OUT NOCOPY NUMBER);
131 --<TPA_PUBLIC_NAME>
132 /*===========================================================================
133 PROCEDURE NAME: InitializeGroup
134
135 DESCRIPTION: This procedure sets up the group cursor.
136
137 PARAMETERS: x_Sched_rec IN rlm_interface_headers%ROWTYPE
138 x_Group_ref IN OUT NOCOPY t_Cursor_ref
139
140 DESIGN REFERENCES: RLADPRDD.rtf
141
142 ALGORITHM:
143
144 NOTES:
145
146 OPEN ISSUES:
147
148 CLOSED ISSUES:
149
150 CHANGE HISTORY: created abkulkar 03/08/99
151 ===========================================================================*/
152 Procedure InitializeGroup(x_Sched_rec IN rlm_interface_headers%ROWTYPE,
153 x_Group_ref IN OUT NOCOPY rlm_forecast_sv.t_Cursor_ref,
154 x_Group_rec IN rlm_dp_sv.t_Group_rec);
155
156 --<TPA_PUBLIC_NAME>
157 /*===========================================================================
158 FUNCTION NAME: FetchGroup
159
160 DESCRIPTION: This function fetches next group
161
162 PARAMETERS: x_Group_ref IN OUT NOCOPY t_Cursor_ref
163 x_Group_rec IN OUT NOCOPY t_Group_rec
164
165 DESIGN REFERENCES: RLADPRDD.rtf
166
167 ALGORITHM:
168
169 NOTES:
170
171 OPEN ISSUES:
172
173 CLOSED ISSUES:
174
175 CHANGE HISTORY: created abkulkar 03/08/99
176 ===========================================================================*/
177 Function FetchGroup(x_Group_ref IN OUT NOCOPY t_Cursor_ref,
178 x_Group_rec IN OUT NOCOPY rlm_dp_sv.t_Group_rec)
179 RETURN BOOLEAN;
180
181 /*===========================================================================
182
183 PROCEDURE NAME: UpdateGroupStatus
184
185 DESCRIPTION: Updates the process status to x_status for the entire
186 group passed in with the x_group_rec
187
188 PARAMETERS: x_Group_rec IN t_Group_rec
189 x_header_id IN NUMBER,
190 x_ScheduleHeaderId IN NUMBER,
191 x_status IN NUMBER
192
193
194
195 DESIGN REFERENCES: RLADPHLD.rtf
196 RLADPRDD.rtf
197
198 ALGORITHM:
199
200 NOTES:
201
202 OPEN ISSUES:
203
204 CLOSED ISSUES:
205
206 CHANGE HISTORY: created mnandell 03/05/99
207 ===========================================================================*/
208 PROCEDURE UpdateGroupStatus( x_header_id IN NUMBER,
209 x_ScheduleHeaderId IN NUMBER,
210 x_Group_rec IN rlm_dp_sv.t_Group_rec,
211 x_status IN NUMBER,
212 x_UpdateLevel IN VARCHAR2 DEFAULT 'GROUP');
213 --<TPA_PUBLIC_NAME>
214 /*===========================================================================
215
216 PROCEDURE NAME: LockLines
217
218 DESCRIPTION: Locks lines
219
220 DESIGN REFERENCES: RLADPHLD.rtf
221 RLADPRDD.rtf
222
223 ALGORITHM:
224
225 NOTES:
226
227 OPEN ISSUES:
228
229 CLOSED ISSUES:
230
231 CHANGE HISTORY: created mnandell 03/05/99
232 ===========================================================================*/
233 FUNCTION LockLines (x_Group_rec IN rlm_dp_sv.t_Group_rec,
234 x_header_id IN NUMBER)
235 RETURN BOOLEAN;
236
237 /*===========================================================================
238
239 PROCEDURE NAME: LockHeaders
240
241 DESCRIPTION: Locks headers
242
243 DESIGN REFERENCES: RLADPHLD.rtf
244 RLADPRDD.rtf
245
246 ALGORITHM:
247
248 NOTES:
249
250 OPEN ISSUES:
251
252 CLOSED ISSUES:
253
254 CHANGE HISTORY: created mnandell 03/05/99
255 ===========================================================================*/
256 FUNCTION LockHeaders (x_header_id IN NUMBER)
257 RETURN BOOLEAN;
258
259 /*===========================================================================
260 PROCEDURE NAME: UpdateHeaderStatus
261
262 DESCRIPTION: This procedure update the process status for the header
263
264 PARAMETERS: x_HeaderId IN NUMBER
265 x_ScheduleHeaderId IN NUMBER
266 x_ProcessStatus IN NUMBER
267
268 DESIGN REFERENCES: RLADPRDD.rtf
269
270 ALGORITHM:
271
272 NOTES:
273
274 OPEN ISSUES:
275
276 CLOSED ISSUES:
277
278 CHANGE HISTORY: created mnandell 03/08/99
279 ===========================================================================*/
280 PROCEDURE UpdateHeaderStatus(x_HeaderId IN NUMBER,
281 x_ScheduleHeaderId IN NUMBER,
282 x_Status IN NUMBER );
283
284 /*===========================================================================
285 PROCEDURE NAME: ProcessTable
286
287 DESCRIPTION: This procedure processes records returned by MRP API for errors
288
289 PARAMETERS: x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE
290 x_Group_rec IN rlm_dp_sv.t_Group_rec
291 t_Forecast IN mrp_forecast_interface_pk.t_forecast_interface
292
293 DESIGN REFERENCES: RLADPRDD.rtf
294
295 ALGORITHM:
296
297 NOTES:
298
299 OPEN ISSUES:
300
301 CLOSED ISSUES:
302
303 CHANGE HISTORY: created mnandell 03/08/99
304 ===========================================================================*/
305
306 PROCEDURE ProcessTable(
307 x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
308 x_Group_rec IN rlm_dp_sv.t_Group_rec,
309 t_Forecast IN
310 mrp_forecast_interface_pk.t_forecast_interface);
311 --<TPA_PUBLIC_NAME>
312 /*===========================================================================
313 PROCEDURE NAME: LoadForecast
314
315 DESCRIPTION: Loads Forecast records into t_forecast PLSQL Table
316 to be passed to MRP API
317
318 PARAMETERS: x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
319 x_Group_rec IN rlm_dp_sv.t_Group_rec,
320 t_forecast IN OUT NOCOPY mrp_forecast_interface_pk.t_forecast_interface,
321 x_forecast_designator IN
322 mrp_forecast_designators.forecast_deignator%TYPE)
323
324
325 DESIGN REFERENCES: RLADPRDD.rtf
326
327 ALGORITHM:
328
329 NOTES:
330
331 OPEN ISSUES:
332
333 CLOSED ISSUES:
334
335 CHANGE HISTORY: created mnandell 03/08/99
336 ===========================================================================*/
337
338 PROCEDURE LoadForecast(
339 x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
340 x_Group_rec IN rlm_dp_sv.t_Group_rec,
341 t_forecast IN OUT NOCOPY mrp_forecast_interface_pk.t_forecast_interface,
342 x_forecast_designator IN
343 mrp_forecast_designators.forecast_designator%TYPE);
344 --<TPA_PUBLIC_NAME>
345 /*===========================================================================
346 PROCEDURE NAME: initialize_table
347
348 DESCRIPTION: This procedure initializes records in t_forecast PLSQL table
349
350 PARAMETERS: t_forecast IN OUT NOCOPY mrp_forecast_interface_pk.t_forecast_interface
351
352 DESIGN REFERENCES: RLADPRDD.rtf
353
354 ALGORITHM:
355
356 NOTES:
357
358 OPEN ISSUES:
359
360 CLOSED ISSUES:
361
362 CHANGE HISTORY: created mnandell 03/08/99
363 ===========================================================================*/
364 PROCEDURE initialize_table(
365 t_forecast IN OUT NOCOPY mrp_forecast_interface_pk.t_forecast_interface);
366
367
368 /*===========================================================================
369 PROCEDURE NAME: get_designator
370
371 DESCRIPTION: This procedure returns the designator which is to be used
372 for MRP forecast interface API
373
374 PARAMETERS:
375
376 DESIGN REFERENCES: RLADPRDD.rtf
377
378 ALGORITHM:
379
380 NOTES:
381
382 OPEN ISSUES:
383
384 CLOSED ISSUES:
385
386 CHANGE HISTORY: created mnandell 03/08/99
387 ===========================================================================*/
388 PROCEDURE GetDesignator( x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE DEFAULT NULL,
389 x_Group_rec IN rlm_dp_sv.t_Group_rec DEFAULT NULL,
390 x_Customer_id IN NUMBER,
391 x_ShipFromOrgId IN NUMBER,
392 x_Ship_Site_Id IN NUMBER,
393 x_bill_site_id IN NUMBER,
394 x_bill_address_Id IN NUMBER,
395 x_ForecastDesignator IN OUT NOCOPY VARCHAR2,
396 x_ship_to_customer_id IN NUMBER DEFAULT NULL);
397 --<TPA_PUBLIC_NAME>
398 /*===========================================================================
399 PROCEDURE NAME: EmptyForecast
400
401 DESCRIPTION: This procedure deletes old forecast for the designator
402
403 PARAMETERS:
404
405 DESIGN REFERENCES: RLADPRDD.rtf
406
407 ALGORITHM:
408
409 NOTES:
410
411 OPEN ISSUES:
412
413 CLOSED ISSUES:
414
415 CHANGE HISTORY: created asutar 05/30/02
416 ===========================================================================*/
417
418 PROCEDURE EmptyForecast( x_sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
419 x_Group_rec IN OUT NOCOPY rlm_dp_sv.t_Group_rec,
420 x_forecast IN OUT NOCOPY
421 mrp_forecast_interface_pk.t_forecast_interface,
422 x_designator IN OUT NOCOPY
423 mrp_forecast_interface_pk.t_forecast_designator,
424 x_t_designator IN OUT NOCOPY
425 mrp_forecast_interface_pk.t_forecast_designator);
426 --<TPA_PUBLIC_NAME>
427
428 PROCEDURE ProcessReplaceAll (x_sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
429 x_Group_rec IN OUT NOCOPY rlm_dp_sv.t_Group_rec,
430 x_designator IN OUT NOCOPY mrp_forecast_interface_pk.t_forecast_designator);
431
432
433 /*===========================================================================
434 PROCEDURE NAME: GetTPContext
435
436 DESCRIPTION: This procedure returns the tp group context.
437 This procedure returns a null x_ship_to_ece_locn_code,
438 and null x_inter_ship_to_ece_locn_code
439
440 PARAMETERS: x_sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE
441 x_group_rec IN t_Group_rec
442 x_customer_number OUT NOCOPY VARCHAR2
443 x_ship_to_ece_locn_code OUT NOCOPY VARCHAR2
444 x_bill_to_ece_locn_code OUT NOCOPY VARCHAR2
445 x_inter_ship_to_ece_locn_code OUT NOCOPY VARCHAR2
446 x_tp_group_code OUT NOCOPY VARCHAR2
447
448 DESIGN REFERENCES:
449
450 ALGORITHM:
451
452 NOTES:
453
454 CLOSED ISSUES:
455
456 CHANGE HISTORY: created jckwok 12/11/03
457
458 ===========================================================================*/
459 PROCEDURE GetTPContext( x_sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE DEFAULT NULL,
460 x_group_rec IN rlm_dp_sv.t_Group_rec DEFAULT NULL,
461 x_customer_number OUT NOCOPY VARCHAR2,
462 x_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
463 x_bill_to_ece_locn_code OUT NOCOPY VARCHAR2,
464 x_inter_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
465 x_tp_group_code OUT NOCOPY VARCHAR2);
466 --<TPA_TPS>
467
468 /*===========================================================================
469 PROCEDURE NAME: Convert_UOM
470
471 DESCRIPTION: Wrapper for Inventory API for UOM Conversion.
472
473 PARAMETERS: from_uom IN VARCHAR2,
474 to_uom IN VARCHAR2,
475 quantity IN OUT NOCOPY NUMBER,
476 p_item_id IN NUMBER,
477 p_org_id IN NUMBER
478
479 DESIGN REFERENCES:
480
481 ALGORITHM:
482
483 NOTES: Bug 4176961
484
485 CLOSED ISSUES:
486
487 CHANGE HISTORY: created stananth 06/25/05
488
489 ===========================================================================*/
490 PROCEDURE Convert_UOM (from_uom IN VARCHAR2,
491 to_uom IN VARCHAR2,
492 quantity IN OUT NOCOPY NUMBER,
493 p_item_id IN NUMBER,
494 p_org_id IN NUMBER);
495
496 END RLM_FORECAST_SV;