1 PACKAGE RLM_BLANKET_SV AUTHID CURRENT_USER as
2 /*$Header: RLMDPBOS.pls 120.1.12010000.1 2008/07/21 09:44:07 appldev ship $*/
3 --<TPA_PUBLIC_NAME=RLM_TPA_SV>
4 --<TPA_PUBLIC_FILE_NAME=RLMTPDP>
5
6 /*===========================================================================
7 PACKAGE NAME: RLM_BLANKET_SV
8
9 DESCRIPTION: Contains the server side code for blanket order API
10 of RLM Demand Processor.
11
12 CLIENT/SERVER: Server
13
14 DESIGN REFERENCES: rlabldld.rtf
15
16 LIBRARY NAME: None
17
18 OWNER: rlanka
19
20 PROCEDURE/FUNCTIONS:
21
22 GLOBALS:
23
24 =========================================================================== */
25
26 g_SundayDOW CONSTANT VARCHAR2(1) := to_char(to_date('05/01/1997','dd/mm/yyyy'),'D');
27 g_MondayDOW CONSTANT VARCHAR2(1) := to_char(to_date('06/01/1997','dd/mm/yyyy'),'D');
28 g_TuesdayDOW CONSTANT VARCHAR2(1) := to_char(to_date('07/01/1997','dd/mm/yyyy'),'D');
29 g_WednesdayDOW CONSTANT VARCHAR2(1) := to_char(to_date('08/01/1997','dd/mm/yyyy'),'D');
30 g_ThursdayDOW CONSTANT VARCHAR2(1) := to_char(to_date('09/01/1997','dd/mm/yyyy'),'D');
31 g_FridayDOW CONSTANT VARCHAR2(1) := to_char(to_date('10/01/1997','dd/mm/yyyy'),'D');
32 g_SaturdayDOW CONSTANT VARCHAR2(1) := to_char(to_date('11/01/1997','dd/mm/yyyy'),'D');
33 g_CalcIntransit BOOLEAN := TRUE;
34 --
35 k_DNULL CONSTANT DATE := to_date('01/01/1930','dd/mm/yyyy');
36 k_NNULL CONSTANT NUMBER := -19999999999;
37 --
38 k_PAST_DUE_FIRM CONSTANT VARCHAR2(1) := '0';
39 k_FIRM CONSTANT VARCHAR2(1) := '1';
40 k_FORECAST CONSTANT VARCHAR2(1) := '2';
41 k_MRP_FORECAST CONSTANT VARCHAR2(1) := '6';
42 k_RECT CONSTANT VARCHAR2(1) := '4';
43 k_AUTH CONSTANT VARCHAR2(1) := '3';
44 --
45 C_TDEBUG NUMBER :=rlm_core_sv.C_LEVEL4;
46 C_SDEBUG NUMBER :=rlm_core_sv.C_LEVEL5;
47 C_DEBUG NUMBER :=rlm_core_sv.C_LEVEL6;
48 --
49 TYPE g_NUM_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
50 g_LineIDTab g_NUM_TBL_TYPE;
51 g_RSOIDTab g_NUM_TBL_TYPE;
52 --
53 e_RSOCreationError EXCEPTION;
54 --
55 --anjana
56 k_PLANNING CONSTANT VARCHAR2(30) := 'PLANNING_RELEASE';
57 k_SHIPPING CONSTANT VARCHAR2(30) := 'SHIPPING';
58 k_SEQUENCED CONSTANT VARCHAR2(30) := 'SEQUENCED';
59
60
61 /*===========================================================================
62 PROCEDURE NAME: DeriveRSO
63
64 DESCRIPTION: This procedure is called from ManageDemand to derive
65 order_header_ids for all the lines within that group.
66 This in turn calls the following procedures/functions
67 (a) QueryRSO
68 (b) CalcEffectiveDates
69 (c) CalcPriorEffectDates
70 (d) CreateRSOHeader
71 (e) InsertRSO
72 (f) UpdateLinesWithRSO
73
74 PARAMETERS: x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE
75 x_Group_rec IN t_group_rec
76 x_header_id IN RLM_INTERFACE_HEADERS.HEADER_ID%TYPE
77
78 DESIGN REFERENCES: rlabldld.rtf
79
80 CHANGE HISTORY: created rlanka 10/10/02
81 ===========================================================================*/
82 PROCEDURE DeriveRSO(x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
83 x_Group_rec IN RLM_DP_SV.t_Group_rec,
84 x_return_status OUT NOCOPY NUMBER);
85 --<TPA_PUBLIC_NAME>
86
87
88 /*==================================================================================
89 PROCEDURE NAME: QueryRSO
90
91 DESCRIPTION: This procedure queries the RLM_BLANKET_RSO table
92 to find a RSO whose effective start and end dates
93 encompass the request date on the line.
94 x_start_date => Effective start date of the RSO, x_rso_hdr_id
95 x_end_date => Effective end date of the RSO, x_rso_hdr_id
96 x_maxend_date => Max. end date with the current context
97 x_minstart_date => Min. start date with the current context
98
99 PARAMETERS: p_customer_id IN NUMBER,
100 p_request_date IN DATE,
101 p_cust_item_id IN NUMBER,
102 x_Group_rec IN RLM_DP_SV.t_Group_rec,
103 x_rso_hdr_id OUT NOCOPY NUMBER,
104 x_start_date OUT NOCOPY DATE,
105 x_end_date OUT NOCOPY DATE,
106 x_maxend_date OUT NOCOPY DATE,
107 x_minstart_date OUT NOCOPY DATE
108
109 DESIGN REFERENCES: rlabldld.rtf
110
111 CHANGE HISTORY: created rlanka 10/10/02
112 ====================================================================================*/
113 PROCEDURE QueryRSO(p_customer_id IN NUMBER,
114 p_request_date IN DATE,
115 p_cust_item_id IN NUMBER,
116 x_Group_rec IN RLM_DP_SV.t_Group_rec,
117 x_rso_hdr_id OUT NOCOPY NUMBER,
118 x_start_date OUT NOCOPY DATE,
119 x_end_date OUT NOCOPY DATE,
120 x_maxend_date OUT NOCOPY DATE,
121 x_minstart_date OUT NOCOPY DATE);
122
123
124 /*===================================================================================
125 PROCEDURE NAME: CalcEffectiveDates
126
127 DESCRIPTION: This procedure is used to calculate the effective
128 start and end dates for a new RSO, based on the highest
129 end date in the RLM_BLANKET_RSO.
130
131 PARAMETERS: x_cust_po_num IN RLM_INTERFACE_LINES.cust_po_number%TYPE
132 x_Group_rec IN t_group_rec
133 x_header_id IN RLM_INTERFACE_HEADERS.HEADER_ID%TYPE
134
135 DESIGN REFERENCES: rlabldld.rtf
136
137 CHANGE HISTORY: created rlanka 10/10/02
138 ====================================================================================*/
139 PROCEDURE CalcEffectiveDates(x_Group_rec IN RLM_DP_SV.t_Group_rec,
140 p_request_date IN DATE,
141 x_start_date OUT NOCOPY DATE,
142 x_end_date OUT NOCOPY DATE,
143 x_maxend_date IN OUT NOCOPY DATE);
144
145
146 /*==================================================================================
147 PROCEDURE NAME: CalcPriorEffectDates
148
149 DESCRIPTION: This procedure is used to calculate the prior effective
150 start and end dates for a new RSO, based on the lowest
151 start date in the RLM_BLANKET_RSO table.
152
153 PARAMETERS: x_Group_rec IN t_group_rec
154 p_request_date IN DATE
155 x_start_date OUT NOCOPY DATE
156 x_end_date OUT NOCOPY DATE
157 x_minstart_date IN OUT NOCOPY DATE
158
159 DESIGN REFERENCES: rlabldld.rtf
160
161 CHANGE HISTORY: created rlanka 10/10/02
162 ====================================================================================*/
163 PROCEDURE CalcPriorEffectDates(x_Group_rec IN RLM_DP_SV.t_Group_rec,
164 p_request_date IN DATE,
165 x_start_date OUT NOCOPY DATE,
166 x_end_date OUT NOCOPY DATE,
167 x_minstart_date IN OUT NOCOPY DATE);
168
169 /*===========================================================================
170 PROCEDURE NAME: InsertRSO
171
172 DESCRIPTION: This procedure is used to insert a new row in the
173 RLM_BLANKET_RSO table. Every entry in this table is
174 unique.
175
176 PARAMETERS: x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE
177 x_Group_rec IN t_group_rec
178 p_rso_hdr_id IN NUMBER
179 p_start_date IN DATE
180 p_end_date IN DATE
181
182 DESIGN REFERENCES: rlabldld.rtf
183
184 CHANGE HISTORY: created rlanka 10/10/02
185 ===========================================================================*/
186 PROCEDURE InsertRSO(x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
187 x_Group_rec IN RLM_DP_SV.t_Group_rec,
188 p_rso_hdr_id IN NUMBER,
189 p_start_date IN DATE,
190 p_end_date IN DATE);
191 --<TPA_PUBLIC_NAME>
192
193
194 /*===========================================================================
195 PROCEDURE NAME: CreateRSOHeader
196
197 DESCRIPTION: This procedure calls the Process Order API to create
198 a release sales order and book it. Mandatory
199 parameters are blanket_number and customer_id, every
200 other value is obtained from defaulting rules
201
202 PARAMETERS: x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE
203 x_Group_rec IN t_group_rec
204 x_rso_hdr_id OUT NOCOPY NUMBER
205
206 DESIGN REFERENCES: rlabldld.rtf
207
208 CHANGE HISTORY: created rlanka 10/10/02
209 ===========================================================================*/
210 PROCEDURE CreateRSOHeader(x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
211 x_Group_rec IN RLM_DP_SV.t_Group_rec,
212 x_rso_hdr_id OUT NOCOPY NUMBER);
213 --<TPA_PUBLIC_NAME>
214
215 /*===========================================================================
216 PROCEDURE NAME: UpdateLinesWithRSO
217
218 DESCRIPTION: Once all order_header_ids have been derived for each
219 line, this procedure uses bulk updates to update the
220 interface and schedule lines.
221
222 PARAMETERS: x_header_id IN NUMBER
223
224 DESIGN REFERENCES: rlabldld.rtf
225
226 CHANGE HISTORY: created rlanka 10/10/02
227 ===========================================================================*/
228 PROCEDURE UpdateLinesWithRSO(x_header_id IN NUMBER);
229
230 /*===============================================================================
231 PROCEDURE NAME: InsertOMMessages
232
233 DESCRIPTION: This procedure records all the errors/warnings
234 from Process Order API, when attempting to create/book
235 a release sales order.
236
237 PARAMETERS: x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE
238 x_Group_rec IN t_group_rec
239 x_msg_count IN NUMBER
240 x_msg_level IN VARCHAR2
241 x_token IN VARCHAR2
242 x_msg_name IN VARCHAR2
243
244 CHANGE HISTORY: created rlanka 10/10/02
245 ================================================================================*/
246 PROCEDURE InsertOMMessages(x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
247 x_Group_rec IN RLM_DP_SV.t_Group_rec,
248 x_msg_count IN NUMBER,
249 x_msg_level IN VARCHAR2,
250 x_token IN VARCHAR2,
251 x_msg_name IN VARCHAR2);
252 --<TPA_PUBLIC_NAME>
253
254
255 /*===========================================================================
256 PROCEDURE NAME: GetTPContext
257
258 DESCRIPTION: This procedure returns the tp group context.
259 and null x_ship_to_ece_locn_code,
260 and null x_inter_ship_to_ece_locn_code
261
262 PARAMETERS: x_sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE
263 x_group_rec IN t_Group_rec
264 x_customer_number OUT VARCHAR2
265 x_ship_to_ece_locn_code OUT VARCHAR2
266 x_bill_to_ece_locn_code OUT VARCHAR2
267 x_inter_ship_to_ece_locn_code OUT VARCHAR2
268 x_tp_group_code OUT VARCHAR2
269
270 CHANGE HISTORY: created rlanka 02/12/2003
271
272 ===========================================================================*/
273 PROCEDURE GetTPContext( x_sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
274 x_group_rec IN rlm_dp_sv.t_Group_rec,
275 x_customer_number OUT NOCOPY VARCHAR2,
276 x_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
277 x_bill_to_ece_locn_code OUT NOCOPY VARCHAR2,
278 x_inter_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
279 x_tp_group_code OUT NOCOPY VARCHAR2);
280 --<TPA_TPS>
281
282 --4302492 : Added the following procedure
283 /*===========================================================================
284 PROCEDURE NAME: CalFenceDays
285
286 DESCRIPTION: This procedure returns number of fence days
287
288 PARAMETERS: x_sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE
289 x_group_rec IN t_Group_rec
290 x_fence_days OUT NUMBER
291
292 CHANGE HISTORY: created anviswan 30/06/2005
293
294 ===========================================================================*/
295 Procedure CalFenceDays(x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
296 x_Group_rec IN rlm_dp_sv.t_Group_rec,
297 x_fence_days OUT NOCOPY NUMBER);
298
299 END RLM_BLANKET_SV;
[Home] [Help]