[Home] [Help]
PACKAGE: APPS.RLM_DP_SV
Source
1 PACKAGE RLM_DP_SV as
2 /*$Header: RLMDPWPS.pls 120.2.12000000.2 2007/04/09 10:14:49 sunilku ship $*/
3 /*===========================================================================
4 PACKAGE NAME: RLM_DP_SV
5
6 DESCRIPTION: Contains all server side code for the dsp wrapper.
7
8 CLIENT/SERVER: Server
9
10 LIBRARY NAME: None
11
12 OWNER:
13
14 PROCEDURE/FUNCTIONS:
15
16 GLOBALS:
17
18 ===========================================================================*/
19 C_SDEBUG CONSTANT NUMBER := rlm_core_sv.C_LEVEL1;
20 C_DEBUG CONSTANT NUMBER := rlm_core_sv.C_LEVEL2;
21 C_TDEBUG CONSTANT NUMBER := rlm_core_sv.C_LEVEL3;
22
23 k_MANUAL CONSTANT VARCHAR2(30) := 'MANUAL';
24
25 k_ORIGINAL CONSTANT VARCHAR2(30) := 'ORIGINAL';
26 k_REPLACE CONSTANT VARCHAR2(30) := 'REPLACE';
27 k_REPLACE_ALL CONSTANT VARCHAR2(30) := 'REPLACE_ALL';
28 k_CHANGE CONSTANT VARCHAR2(30) := 'CHANGE';
29 k_CANCEL CONSTANT VARCHAR2(30) := 'CANCELLATION';
30 k_DELETE CONSTANT VARCHAR2(30) := 'DELETE';
31 k_INSERT CONSTANT VARCHAR2(30) := 'INSERT';
32 k_CONFIRMATION CONSTANT VARCHAR2(30) := 'CONFIRMATION';
33 k_ADD CONSTANT VARCHAR2(30) := 'ADD';
34 k_RECEIPT CONSTANT VARCHAR2(80) := 'RECEIPT';
35 k_PLANNING CONSTANT VARCHAR2(30) := 'PLANNING_RELEASE';
36 k_SHIPPING CONSTANT VARCHAR2(30) := 'SHIPPING';
37 k_SEQUENCED CONSTANT VARCHAR2(30) := 'SEQUENCED';
38 k_A CONSTANT VARCHAR2(1) := 'A';
39 k_B CONSTANT VARCHAR2(1) := 'B';
40 k_C CONSTANT VARCHAR2(1) := 'C';
41 k_D CONSTANT VARCHAR2(1) := 'D';
42 k_E CONSTANT VARCHAR2(1) := 'E';
43 k_F CONSTANT VARCHAR2(1) := 'F';
44 k_G CONSTANT VARCHAR2(1) := 'G';
45 k_VNULL CONSTANT VARCHAR2(25) := 'THIS_IS_A_NULL_VALUE';
46 k_NNULL CONSTANT NUMBER := -19999999999;
47 k_DNULL CONSTANT DATE := to_date('01/01/1930','dd/mm/yyyy');
48 k_PARALLEL_DSP CONSTANT VARCHAR2(10) := 'PARALLEL';
49 k_SEQ_DSP CONSTANT VARCHAR2(10) := 'SEQUENTIAL';
50 e_HeaderLocked EXCEPTION;
51 edi_test_indicator VARCHAR2(3); /*2554058*/
52
53 -- For sweeper program when wf is enabled
54 g_warn_replace_schedule VARCHAR2(1) DEFAULT 'N';
55
56 -- stype
57 g_order_by_schedule_type VARCHAR2(1) DEFAULT 'N';
58
59 TYPE g_request_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
60
61 -- ER 3992531: Added min_start_date_time to the following record structure.
62
63 TYPE t_Group_rec IS RECORD(
64 customer_id rlm_interface_headers.customer_id%TYPE,
65 ship_from_org_id rlm_interface_lines.ship_from_org_id%TYPE,
66 ship_to_address_id rlm_interface_lines.ship_to_address_id%TYPE,
67 ship_to_site_use_id rlm_interface_lines.ship_to_site_use_id%TYPE,
68 ship_to_org_id rlm_interface_lines.ship_to_org_id%TYPE,
69 customer_item_id rlm_interface_lines.customer_item_id%TYPE,
70 inventory_item_id rlm_interface_lines.inventory_item_id%TYPE,
71 schedule_item_num rlm_interface_lines.schedule_item_num%TYPE,
72 industry_attribute15 rlm_interface_lines.industry_attribute15%TYPE,
73 order_header_id rlm_interface_lines.order_header_id%TYPE,
74 --cust_production_seq_num rlm_interface_lines.cust_production_seq_num%TYPE,
75 schedule_type_one rlm_interface_headers.schedule_type%TYPE,
76 schedule_type_two rlm_interface_headers.schedule_type%TYPE,
77 schedule_type_three rlm_interface_headers.schedule_type%TYPE,
78 deliver_to_org_id rlm_interface_lines.deliver_to_org_id%TYPE,
79 --need for forecast module
80 bill_to_site_use_id rlm_interface_lines.bill_to_site_use_id%TYPE,
81 bill_to_address_id rlm_interface_lines.bill_to_address_id%TYPE,
82 match_within rlm_cust_shipto_terms.match_within_key%TYPE,
83 match_within_rec rlm_core_sv.t_Match_rec,
84 match_across rlm_cust_shipto_terms.match_across_key%TYPE,
85 match_across_rec rlm_core_sv.t_Match_rec,
86 setup_terms_rec rlm_setup_terms_sv.setup_terms_rec_typ,
87 cutoff_days NUMBER,
88 disposition_code rlm_cust_shipto_terms.unshipped_firm_disp_cd%TYPE,
89 frozen_days NUMBER,
90 isSourced BOOLEAN,
91 roll_forward_frozen_flag VARCHAR2(1),
92 blanket_number rlm_cust_shipto_terms.blanket_number%TYPE,
93 min_start_date_time rlm_interface_lines.start_date_time%TYPE,
94 intmed_ship_to_org_id rlm_interface_lines.intmed_ship_to_org_id%TYPE,--Bugfix 5911991
95 intrmd_ship_to_id rlm_interface_lines.intrmd_ship_to_id%TYPE, --Bugfix 5911991
96 ship_to_customer_id rlm_interface_lines.ship_to_customer_id%TYPE
97 );
98 g_md_total NUMBER:=0;
99 g_mf_total NUMBER:=0;
100 g_rd_total NUMBER:=0;
101
102 /*===========================================================================
103 PROCEDURE NAME: DemandProcessor
104
105 DESCRIPTION: This procedure will be called from the inbound gateway
106 Based on the schedule_header_id passed from the
107 inbound gateway the parameters passed by EDI
108 is only schedule_header_id which is used to
109 get all the demand for that schedule. This is
110 then collected and the validate_demand is called
111
112 PARAMETERS: errbuf OUT NOCOPY VARCHAR2
113 retcode OUT NOCOPY VARCHAR2
114 p_schedule_purpose_code VARCHAR2 DEFAULT NULL
115 p_from_date DATE DEFAULT NULL
116 p_to_date DATE DEFAULT NULL
117 p_from_customer_ext VARCHAR2 DEFAULT NULL
118 p_to_customer_ext VARCHAR2 DEFAULT NULL
119 p_from_ship_to_ext VARCHAR2 DEFAULT NULL
120 p_to_ship_to_ext VARCHAR2 DEFAULT NULL
121 p_warn_replace_schedule VARCHAR2 DEFAULT 'N'
122 p_order_by_schedule_type VARCHAR2 DEFAULT 'N',
123 p_child_processes NUMBER DEFAULT 0
124 p_request_id NUMBER DEFAULT NULL
125
126 DESIGN REFERENCES: rladphld.rtf
127
128 ALGORITHM:
129
130 NOTES:
131
132 OPEN ISSUES:
133
134 CLOSED ISSUES:
135
136 CHANGE HISTORY: created abkulkar 03/25/99
137 Added p_org_id rlanka 03/30/05
138 ===========================================================================*/
139 PROCEDURE DemandProcessor( errbuf OUT NOCOPY VARCHAR2,
140 retcode OUT NOCOPY VARCHAR2,
141 p_org_id NUMBER,
142 p_schedule_purpose_code VARCHAR2 DEFAULT NULL,
143 p_from_date VARCHAR2 DEFAULT NULL,
144 p_to_date VARCHAR2 DEFAULT NULL,
145 p_from_customer_ext VARCHAR2 DEFAULT NULL,
146 p_to_customer_ext VARCHAR2 DEFAULT NULL,
147 p_from_ship_to_ext VARCHAR2 DEFAULT NULL,
148 p_to_ship_to_ext VARCHAR2 DEFAULT NULL,
149 p_header_id NUMBER DEFAULT NULL,
150 p_dummy VARCHAR2 DEFAULT NULL,
151 p_cust_ship_from_ext VARCHAR2 DEFAULT NULL,
152 p_warn_replace_schedule VARCHAR2 DEFAULT 'N',
153 p_order_by_schedule_type VARCHAR2 DEFAULT 'N',
154 p_child_processes NUMBER DEFAULT 0,
155 p_request_id NUMBER DEFAULT NULL);
156
157
158 /*===========================================================================
159 PROCEDURE NAME: PurgeInterfaceLines
160
161 DESCRIPTION: This procedure deletes the fully processed lines from the
162 interface tables
163
164 PARAMETERS: x_header_id IN NUMBER
165
166 DESIGN REFERENCES: rladphld.rtf
167
168 ALGORITHM:
169
170 NOTES:
171
172 OPEN ISSUES:
173
174 CLOSED ISSUES:
175
176 CHANGE HISTORY: created mnandell 10/25/99
177 ===========================================================================*/
178
179 PROCEDURE PurgeInterfaceLines(x_header_id IN NUMBER);
180 --
181 /*===========================================================================
182 PROCEDURE NAME: UpdateHeaderPS
183
184 DESCRIPTION: This procedure Updates the headers process STatus
185 based on interface lines
186
187 PARAMETERS: x_header_id IN NUMBER
188
189 DESIGN REFERENCES: rladphld.rtf
190
191 ALGORITHM:
192
193 NOTES:
194
195 OPEN ISSUES:
196
197 CLOSED ISSUES:
198
199 CHANGE HISTORY: created mnandell 10/25/99
200 ===========================================================================*/
201 PROCEDURE UpdateHeaderPS (x_HeaderId IN NUMBER,
202 x_ScheduleHeaderId IN NUMBER);
203
204 /*===========================================================================
205 PROCEDURE NAME: UpdateGroupPS
206
207 DESCRIPTION: This procedure Updates the lines process STatus
208 for the entire group
209
210 PARAMETERS: x_header_id IN NUMBER
211
212 DESIGN REFERENCES: rladphld.rtf
213
214 ALGORITHM:
215
216 NOTES:
217
218 OPEN ISSUES:
219
220 CLOSED ISSUES:
221
222 CHANGE HISTORY: created mnandell 10/25/99
223 ===========================================================================*/
224 PROCEDURE UpdateGroupPS(x_header_id IN NUMBER,
225 x_ScheduleHeaderId IN NUMBER,
226 x_Group_rec IN rlm_dp_sv.t_Group_rec,
227 x_status IN NUMBER,
228 x_UpdateLevel IN VARCHAR2 DEFAULT 'GROUP');
229
230 /*===========================================================================
231 FUNCTION NAME: LockHeader
232
233 DESCRIPTION: This procedure locks the header for the entire transaction
234
235 PARAMETERS: x_header_id IN NUMBER
236
237 DESIGN REFERENCES: rladphld.rtf
238
239 ALGORITHM:
240
241 NOTES:
242
243 OPEN ISSUES:
244
245 CLOSED ISSUES:
246
247 CHANGE HISTORY: created mnandell 10/25/99
248 ===========================================================================*/
249 FUNCTION LockHeader (x_HeaderId IN NUMBER,
250 v_Sched_rec OUT NOCOPY RLM_INTERFACE_HEADERS%ROWTYPE)
251 RETURN BOOLEAN;
252
253 /*=========================================================================
254
255 PROCEDURE NAME: RunExceptionReport
256
257 DESCRIPTION: This procedure runs the exception report
258 if there are any message for the DSP run
259
260 PARAMETERS: x_requestId IN NUMBER
261 x_OrgId IN NUMBER DEFAULT NULL
262
263 DESIGN REFERENCES: rladphld.rtf
264
265 ALGORITHM:
266
267 NOTES:
268
269 OPEN ISSUES:
270
271 CLOSED ISSUES:
272
273 CHANGE HISTORY: created mnandell 10/25/99
274 Add x_OrgId rlanka 03/30/05
275 ===========================================================================*/
276
277 PROCEDURE RunExceptionReport(x_requestId IN NUMBER,
278 x_OrgId IN NUMBER DEFAULT NULL);
279
280
281 /*===========================================================================
282 FUNCTION NAME: CheckForecast
283
284 DESCRIPTION: This procedure checks for MRP forecast lines in a group
285
286 PARAMETERS: x_header_id IN NUMBER
287 x_Group_rec IN rlm_dp_sv.t_Group_rec
288
289 DESIGN REFERENCES:
290
291 ALGORITHM:
292
293 NOTES:
294
295 OPEN ISSUES:
296
297 CLOSED ISSUES:
298
299 CHANGE HISTORY: created asutar 05/24/2001
300 ===========================================================================*/
301
302 FUNCTION CheckForecast(x_header_id IN NUMBER,
303 x_Group_rec IN rlm_dp_sv.t_Group_rec)
304 RETURN BOOLEAN;
305
306 /*===========================================================================
307 PROCEDURE NAME: ChildProcess
308
309 DESCRIPTION: Executable for Concurrent program RLMDSPCHILD
310
311 PARAMETERS: errbuf OUT NOCOPY VARCHAR2
312 retcode OUT NOCOPY VARCHAR2
313 p_request_id IN NUMBER
314 p_header_id IN NUMBER
315 p_index IN NUMBER
316
317 DESIGN REFERENCES:
318
319 ALGORITHM:
320
321 NOTES:
322
323 OPEN ISSUES:
324
325 CLOSED ISSUES:
326
327 CHANGE HISTORY: created asutar 07/24/03
328 Added p_org_id rlanka 03/30/05
329 ===========================================================================*/
330
331 PROCEDURE ChildProcess(
332 errbuf OUT NOCOPY VARCHAR2,
333 retcode OUT NOCOPY VARCHAR2,
334 p_request_id IN NUMBER,
335 p_header_id IN NUMBER,
336 p_index IN NUMBER,
337 p_org_id IN NUMBER);
338
339 /*===========================================================================
340 PROCEDURE NAME: SubmitChildRequests
341
342 DESCRIPTION: This procedure submits DSP child requests and
343 populates child request table
344
345 PARAMETERS: x_header_id IN NUMBER,
346 x_num_child IN NUMBER,
347 x_child_req_id IN OUT NOCOPY g_request_tbl
348
349
350 DESIGN REFERENCES:
351
352 ALGORITHM:
353
354 NOTES:
355
356 OPEN ISSUES:
357
358 CLOSED ISSUES:
359
360 CHANGE HISTORY: created asutar 07/24/2003
361 ===========================================================================*/
362
363 PROCEDURE SubmitChildRequests(
364 x_header_id IN NUMBER,
365 x_num_child IN NUMBER,
366 x_child_req_id IN OUT NOCOPY g_request_tbl);
367
368 /*===========================================================================
369 PROCEDURE NAME: ProcessGroups
370
371 DESCRIPTION: This procedure loops thru groups and calls Managedemand,
372 ManageForecast and RecDemand for each group. p_index is
373 null for serial processing of groups within a schedule.
374
375 PARAMETERS: p_sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
376 p_header_id IN NUMBER,
377 p_index IN NUMBER DEFAULT NULL)
378
379 DESIGN REFERENCES:
380
381 ALGORITHM:
382
383 NOTES:
384
385 OPEN ISSUES:
386
387 CLOSED ISSUES:
388
389 CHANGE HISTORY: created asutar 07/24/2003
390 ===========================================================================*/
391
392 PROCEDURE ProcessGroups (p_sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
393 p_header_id IN NUMBER,
394 p_index IN NUMBER DEFAULT NULL,
395 p_dspMode IN VARCHAR2 DEFAULT k_SEQ_DSP);
396
397 /*===========================================================================
398 PROCEDURE NAME: CreateChildGroups
399
400 DESCRIPTION: This procedure marks groups with child process index
401 for parallelization.
402
403 PARAMETERS: x_header_id IN NUMBER,
404 x_num_child IN OUT NOCOPY NUMBER
405
406
407 DESIGN REFERENCES:
408
409 ALGORITHM:
410
411 NOTES:
412
413 OPEN ISSUES:
414
415 CLOSED ISSUES:
416
417 CHANGE HISTORY: created asutar 07/24/2003
418 ===========================================================================*/
419
420 PROCEDURE CreateChildGroups( x_header_id IN NUMBER,
421 x_num_child IN OUT NOCOPY NUMBER);
422
423 /*===========================================================================
424 PROCEDURE NAME: ProcessChildRequests
425
426 DESCRIPTION: This procedure waits for each child request to finish
427 and updates the group status accordingly
428
429 PARAMETERS: x_header_id IN NUMBER
430 x_Group_rec IN rlm_dp_sv.t_Group_rec
431
432 DESIGN REFERENCES:
433
434 ALGORITHM:
435
436 NOTES:
437
438 OPEN ISSUES:
439
440 CLOSED ISSUES:
441
442 CHANGE HISTORY: created asutar 07/24/2003
443 ===========================================================================*/
444
445 PROCEDURE ProcessChildRequests(x_header_id IN NUMBER,
446 x_child_req_id IN g_request_tbl);
447
448 END RLM_DP_SV;