[Home] [Help]
PACKAGE: APPS.RLM_DP_SV
Source
1 PACKAGE RLM_DP_SV AUTHID CURRENT_USER as
2 /*$Header: RLMDPWPS.pls 120.4 2011/05/24 11:49:30 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 g_dsp_start_time DATE := SYSDATE; --Bugfix 10053830
103
104 /*===========================================================================
105 PROCEDURE NAME: DemandProcessor
106
107 DESCRIPTION: This procedure will be called from the inbound gateway
108 Based on the schedule_header_id passed from the
109 inbound gateway the parameters passed by EDI
110 is only schedule_header_id which is used to
111 get all the demand for that schedule. This is
112 then collected and the validate_demand is called
113
114 PARAMETERS: errbuf OUT NOCOPY VARCHAR2
115 retcode OUT NOCOPY VARCHAR2
116 p_schedule_purpose_code VARCHAR2 DEFAULT NULL
117 p_from_date DATE DEFAULT NULL
118 p_to_date DATE DEFAULT NULL
119 p_from_customer_ext VARCHAR2 DEFAULT NULL
120 p_to_customer_ext VARCHAR2 DEFAULT NULL
121 p_from_ship_to_ext VARCHAR2 DEFAULT NULL
122 p_to_ship_to_ext VARCHAR2 DEFAULT NULL
123 p_warn_replace_schedule VARCHAR2 DEFAULT 'N'
124 p_order_by_schedule_type VARCHAR2 DEFAULT 'N',
125 p_child_processes NUMBER DEFAULT 0
126 p_request_id NUMBER DEFAULT NULL
127
128 DESIGN REFERENCES: rladphld.rtf
129
130 ALGORITHM:
131
132 NOTES:
133
134 OPEN ISSUES:
135
136 CLOSED ISSUES:
137
138 CHANGE HISTORY: created abkulkar 03/25/99
139 Added p_org_id rlanka 03/30/05
140 ===========================================================================*/
141 PROCEDURE DemandProcessor( errbuf OUT NOCOPY VARCHAR2,
142 retcode OUT NOCOPY VARCHAR2,
143 p_org_id NUMBER,
144 p_schedule_purpose_code VARCHAR2 DEFAULT NULL,
145 p_from_date VARCHAR2 DEFAULT NULL,
146 p_to_date VARCHAR2 DEFAULT NULL,
147 p_from_customer_ext VARCHAR2 DEFAULT NULL,
148 p_to_customer_ext VARCHAR2 DEFAULT NULL,
149 p_from_ship_to_ext VARCHAR2 DEFAULT NULL,
150 p_to_ship_to_ext VARCHAR2 DEFAULT NULL,
151 p_header_id NUMBER DEFAULT NULL,
152 p_dummy VARCHAR2 DEFAULT NULL,
153 p_cust_ship_from_ext VARCHAR2 DEFAULT NULL,
154 p_warn_replace_schedule VARCHAR2 DEFAULT 'N',
155 p_order_by_schedule_type VARCHAR2 DEFAULT 'N',
156 p_child_processes NUMBER DEFAULT 0,
157 p_request_id NUMBER DEFAULT NULL);
158
159
160 /*===========================================================================
161 PROCEDURE NAME: PurgeInterfaceLines
162
163 DESCRIPTION: This procedure deletes the fully processed lines from the
164 interface tables
165
166 PARAMETERS: x_header_id IN NUMBER
167
168 DESIGN REFERENCES: rladphld.rtf
169
170 ALGORITHM:
171
172 NOTES:
173
174 OPEN ISSUES:
175
176 CLOSED ISSUES:
177
178 CHANGE HISTORY: created mnandell 10/25/99
179 ===========================================================================*/
180
181 PROCEDURE PurgeInterfaceLines(x_header_id IN NUMBER);
182 --
183 /*===========================================================================
184 PROCEDURE NAME: UpdateHeaderPS
185
186 DESCRIPTION: This procedure Updates the headers process STatus
187 based on interface lines
188
189 PARAMETERS: x_header_id IN NUMBER
190
191 DESIGN REFERENCES: rladphld.rtf
192
193 ALGORITHM:
194
195 NOTES:
196
197 OPEN ISSUES:
198
199 CLOSED ISSUES:
200
201 CHANGE HISTORY: created mnandell 10/25/99
202 ===========================================================================*/
203 PROCEDURE UpdateHeaderPS (x_HeaderId IN NUMBER,
204 x_ScheduleHeaderId IN NUMBER);
205
206 /*===========================================================================
207 PROCEDURE NAME: UpdateGroupPS
208
209 DESCRIPTION: This procedure Updates the lines process STatus
210 for the entire group
211
212 PARAMETERS: x_header_id IN NUMBER
213
214 DESIGN REFERENCES: rladphld.rtf
215
216 ALGORITHM:
217
218 NOTES:
219
220 OPEN ISSUES:
221
222 CLOSED ISSUES:
223
224 CHANGE HISTORY: created mnandell 10/25/99
225 ===========================================================================*/
226 PROCEDURE UpdateGroupPS(x_header_id IN NUMBER,
227 x_ScheduleHeaderId IN NUMBER,
228 x_Group_rec IN rlm_dp_sv.t_Group_rec,
229 x_status IN NUMBER,
230 x_UpdateLevel IN VARCHAR2 DEFAULT 'GROUP');
231
232 /*===========================================================================
233 FUNCTION NAME: LockHeader
234
235 DESCRIPTION: This procedure locks the header for the entire transaction
236
237 PARAMETERS: x_header_id IN NUMBER
238
239 DESIGN REFERENCES: rladphld.rtf
240
241 ALGORITHM:
242
243 NOTES:
244
245 OPEN ISSUES:
246
247 CLOSED ISSUES:
248
249 CHANGE HISTORY: created mnandell 10/25/99
250 ===========================================================================*/
251 FUNCTION LockHeader (x_HeaderId IN NUMBER,
252 v_Sched_rec OUT NOCOPY RLM_INTERFACE_HEADERS%ROWTYPE)
253 RETURN BOOLEAN;
254
255 /*=========================================================================
256
257 PROCEDURE NAME: RunExceptionReport
258
259 DESCRIPTION: This procedure runs the exception report
260 if there are any message for the DSP run
261
262 PARAMETERS: x_requestId IN NUMBER
263 x_OrgId IN NUMBER DEFAULT NULL
264
265 DESIGN REFERENCES: rladphld.rtf
266
267 ALGORITHM:
268
269 NOTES:
270
271 OPEN ISSUES:
272
273 CLOSED ISSUES:
274
275 CHANGE HISTORY: created mnandell 10/25/99
276 Add x_OrgId rlanka 03/30/05
277 ===========================================================================*/
278
279 PROCEDURE RunExceptionReport(x_requestId IN NUMBER,
280 x_OrgId IN NUMBER DEFAULT NULL);
281
282
283 /*===========================================================================
284 FUNCTION NAME: CheckForecast
285
286 DESCRIPTION: This procedure checks for MRP forecast lines in a group
287
288 PARAMETERS: x_header_id IN NUMBER
289 x_Group_rec IN rlm_dp_sv.t_Group_rec
290
291 DESIGN REFERENCES:
292
293 ALGORITHM:
294
295 NOTES:
296
297 OPEN ISSUES:
298
299 CLOSED ISSUES:
300
301 CHANGE HISTORY: created asutar 05/24/2001
302 ===========================================================================*/
303
304 FUNCTION CheckForecast(x_header_id IN NUMBER,
305 x_Group_rec IN rlm_dp_sv.t_Group_rec)
306 RETURN BOOLEAN;
307
308 /*===========================================================================
309 PROCEDURE NAME: ChildProcess
310
311 DESCRIPTION: Executable for Concurrent program RLMDSPCHILD
312
313 PARAMETERS: errbuf OUT NOCOPY VARCHAR2
314 retcode OUT NOCOPY VARCHAR2
315 p_request_id IN NUMBER
316 p_header_id IN NUMBER
317 p_index IN NUMBER
318
319 DESIGN REFERENCES:
320
321 ALGORITHM:
322
323 NOTES:
324
325 OPEN ISSUES:
326
327 CLOSED ISSUES:
328
329 CHANGE HISTORY: created asutar 07/24/03
330 Added p_org_id rlanka 03/30/05
331 ===========================================================================*/
332
333 PROCEDURE ChildProcess(
334 errbuf OUT NOCOPY VARCHAR2,
335 retcode OUT NOCOPY VARCHAR2,
336 p_request_id IN NUMBER,
337 p_header_id IN NUMBER,
338 p_index IN NUMBER,
339 p_org_id IN NUMBER);
340
341 /*===========================================================================
342 PROCEDURE NAME: SubmitChildRequests
343
344 DESCRIPTION: This procedure submits DSP child requests and
345 populates child request table
346
347 PARAMETERS: x_header_id IN NUMBER,
348 x_num_child IN NUMBER,
349 x_child_req_id IN OUT NOCOPY g_request_tbl
350
351
352 DESIGN REFERENCES:
353
354 ALGORITHM:
355
356 NOTES:
357
358 OPEN ISSUES:
359
360 CLOSED ISSUES:
361
362 CHANGE HISTORY: created asutar 07/24/2003
363 ===========================================================================*/
364
365 PROCEDURE SubmitChildRequests(
366 x_header_id IN NUMBER,
367 x_num_child IN NUMBER,
368 x_child_req_id IN OUT NOCOPY g_request_tbl);
369
370 /*===========================================================================
371 PROCEDURE NAME: ProcessGroups
372
373 DESCRIPTION: This procedure loops thru groups and calls Managedemand,
374 ManageForecast and RecDemand for each group. p_index is
375 null for serial processing of groups within a schedule.
376
377 PARAMETERS: p_sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
378 p_header_id IN NUMBER,
379 p_index IN NUMBER DEFAULT NULL)
380
381 DESIGN REFERENCES:
382
383 ALGORITHM:
384
385 NOTES:
386
387 OPEN ISSUES:
388
389 CLOSED ISSUES:
390
391 CHANGE HISTORY: created asutar 07/24/2003
392 ===========================================================================*/
393
394 PROCEDURE ProcessGroups (p_sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
395 p_header_id IN NUMBER,
396 p_index IN NUMBER DEFAULT NULL,
397 p_dspMode IN VARCHAR2 DEFAULT k_SEQ_DSP);
398
399 /*===========================================================================
400 PROCEDURE NAME: CreateChildGroups
401
402 DESCRIPTION: This procedure marks groups with child process index
406 x_num_child IN OUT NOCOPY NUMBER
403 for parallelization.
404
405 PARAMETERS: x_header_id IN NUMBER,
407
408
409 DESIGN REFERENCES:
410
411 ALGORITHM:
412
413 NOTES:
414
415 OPEN ISSUES:
416
417 CLOSED ISSUES:
418
419 CHANGE HISTORY: created asutar 07/24/2003
420 ===========================================================================*/
421
422 PROCEDURE CreateChildGroups( x_header_id IN NUMBER,
423 x_num_child IN OUT NOCOPY NUMBER);
424
425 /*===========================================================================
426 PROCEDURE NAME: ProcessChildRequests
427
428 DESCRIPTION: This procedure waits for each child request to finish
429 and updates the group status accordingly
430
431 PARAMETERS: x_header_id IN NUMBER
432 x_Group_rec IN rlm_dp_sv.t_Group_rec
433
434 DESIGN REFERENCES:
435
436 ALGORITHM:
437
438 NOTES:
439
440 OPEN ISSUES:
441
442 CLOSED ISSUES:
443
444 CHANGE HISTORY: created asutar 07/24/2003
445 ===========================================================================*/
446
447 PROCEDURE ProcessChildRequests(x_header_id IN NUMBER,
448 x_child_req_id IN g_request_tbl);
449
450 END RLM_DP_SV;