1 PACKAGE BODY OKL_RV_INTERFACE_PVT AS
2 /* $Header: OKLRRVIB.pls 120.4 2005/07/28 06:32:21 smadhava noship $*/
3
4 G_DEBUG NUMBER := 0;
5 ------------------------------------------------------------------------------
6 -- PROCEDURE debug_message
7 --
8 -- This procedure prints debug message depending on DEBUG flag
9 --
10 -- Calls:
11 -- Called By:
12 ------------------------------------------------------------------------------
13 PROCEDURE debug_message(
14 p_message IN VARCHAR2
15 ) IS
16 BEGIN
17 IF ( G_DEBUG = 1 ) THEN
18 fnd_file.put_line (fnd_file.output, p_message);
19 --dbms_output.put_line (p_message);
20 END IF;
21
22 RETURN;
23 END debug_message;
24
25 ------------------------------------------------------------------------------
26 -- PROCEDURE Report_Error
27 -- It is a generalized routine to display error on Concurrent Manager Log file
28 -- Calls:
29 -- okl_api package
30 -- fnd_msg_pub package
31 -- Called by:
32 -- process_record
33 -- check_input_record
34 -- process_input_record
35 ------------------------------------------------------------------------------
36
37 PROCEDURE Report_Error(
38 x_msg_count OUT NOCOPY NUMBER,
39 x_msg_data OUT NOCOPY VARCHAR2
40 ) IS
41
42 x_msg_index_out NUMBER;
43 x_msg_out VARCHAR2(2000);
44
45 BEGIN
46
47 FOR i in 1..x_msg_count LOOP
48 FND_MSG_PUB.GET(
49 p_msg_index => i,
50 p_encoded => FND_API.G_FALSE,
51 p_data => x_msg_data,
52 p_msg_index_out => x_msg_index_out
53 );
54
55 fnd_file.put_line(fnd_file.output, 'Error '||to_char(i)||': '||x_msg_data);
56 --dbms_output.put_line('Error '||to_char(i)||': '||x_msg_data);
57 END LOOP;
58 return;
59 EXCEPTION
60 WHEN OTHERS THEN
61 NULL;
62 END Report_Error;
63
64
65 PROCEDURE write_to_log(
66 p_message IN VARCHAR2
67 ) IS
68 BEGIN
69 --dbms_output.put_line(p_message);
70 fnd_file.put_line(fnd_file.output, p_message);
71 END write_to_log;
72
73
74 ------------------------------------------------------------------------------
75 -- Validations for ensuring correct individual values
76 -- as well as enforcing unique and foriegn key constraints
77 -- PROCEDURE validate_item id
78 -- PROCEDURE org id -- incorporate check with item id
79 -- PROCEDURE validate_rv_percent
80 -- PROCEDURE validate term
81 -- PROCEDURE validate start date
82 -- PROCEDURE validate end date
83 --
84
85 --------------------------------------
86 -- Validate_Attributes for ITEM ID and ORG ID --
87 --------------------------------------
88 PROCEDURE validate_item_and_org(
89 x_return_status OUT NOCOPY VARCHAR2,
90 p_item_id IN NUMBER,
91 p_org_id IN NUMBER) IS
92
93 BEGIN
94 --Stubbed out - smadhava
95 NULL;
96 END validate_item_and_org;
97
98 -----------------------------------------------------
99 -- Validate_Attributes for: RESIDUAL_VALUE_PERCENT --
100 -----------------------------------------------------
101 PROCEDURE validate_residual(
102 x_return_status OUT NOCOPY VARCHAR2,
103 p_residual_value_percent IN NUMBER) IS
104 BEGIN
105 --Stubbed out - smadhava
106 NULL;
107 END validate_residual;
108
109 ---------------------------------------------
110 -- Validate_Attributes for: TERM_IN_MONTHS --
111 ---------------------------------------------
112 PROCEDURE validate_term(
113 x_return_status OUT NOCOPY VARCHAR2,
114 p_term_in_months IN NUMBER) IS
115 BEGIN
116 --Stubbed out - smadhava
117 NULL;
118 END validate_term;
119
120
121 -----------------------------------------
122 -- Validate_Attributes for: START_DATE --
123 -----------------------------------------
124 PROCEDURE validate_start_date(
125 x_return_status OUT NOCOPY VARCHAR2,
126 p_start_date IN DATE) IS
127 BEGIN
128 --Stubbed out - smadhava
129 NULL;
130 END validate_start_date;
131
132 -----------------------------------------
133 -- Validate_Attributes for: END_DATE --
134 -----------------------------------------
135 PROCEDURE validate_end_date(
136 x_return_status OUT NOCOPY VARCHAR2,
137 p_end_date IN DATE,
138 p_start_date IN DATE) IS
139 BEGIN
140 --Stubbed out - smadhava
141 NULL;
142 END validate_end_date;
143
144 -- Enforces unique key.
145 FUNCTION Validate_Record (p_rv_rec IN okl_rv_interface_pvt.rv_rec_type
146 ,x_return_status OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
147 BEGIN
148 --Stubbed out - smadhava
149 NULL;
150 END Validate_Record;
151
152 ------------------------------------------------------------------------------
153 -- End of attribute and record validations --
154 ------------------------------------------------------------------------------
155 PROCEDURE Check_Input_Record(
156 p_init_msg_list IN VARCHAR2,
157 x_return_status OUT NOCOPY VARCHAR2,
158 x_msg_count OUT NOCOPY NUMBER,
159 x_msg_data OUT NOCOPY VARCHAR2,
160 p_batch_number IN VARCHAR2,
161 p_org_id IN NUMBER,
162 x_total_checked OUT NOCOPY NUMBER,
163 x_total_failed OUT NOCOPY NUMBER
164 ) IS
165 BEGIN
166 --Stubbed out - smadhava
167 NULL;
168 END; -- Check_Input_Record
169
170
171 ------------------------------------------------------------------------------
172 -- PROCEDURE Process_Record
173 -- It Validates Input record and Load record after SUCCESSFUL validation
174 -- Calls:
175 -- Check_Inout_Record
176 -- Load_Input_Record
177 -- Report Error
178 -- Called by:
179 -- Starting point
180 ------------------------------------------------------------------------------
181 PROCEDURE Process_Record (
182 errbuf OUT NOCOPY VARCHAR2
183 ,retcode OUT NOCOPY VARCHAR2
184 ,p_batch_number IN VARCHAR2
185 ,p_org_id IN NUMBER
186 ) IS
187
188 BEGIN
189 --Stubbed out - smadhava
190 NULL;
191 END Process_Record;
192
193
194
195 ------------------------------------------------------------------------------
196 -- PROCEDURE Purge_Record
197 -- It deletes records from the rv interface table.
198 ------------------------------------------------------------------------------
199 PROCEDURE purge_record (
200 errbuf OUT NOCOPY VARCHAR2
201 ,retcode OUT NOCOPY VARCHAR2
202 ,p_batch_number IN VARCHAR2
203 ,p_org_id IN NUMBER
204 ,p_status IN VARCHAR2
205 ) IS
206
207 BEGIN
208 --Stubbed out - smadhava
209 NULL;
210 END purge_record;
211
212
213 ------------------------------------------------------------------------------
214 -- PROCEDURE Update_Interface_Status
215 -- It Changes Status to Interface Table
216 -- Calls:
217 -- None
218 -- Called By:
219 -- check_input_record
220 -- load_input_record
221 ------------------------------------------------------------------------------
222 PROCEDURE Update_Interface_Status (p_batch_number IN VARCHAR2
223 ,p_status IN VARCHAR2
224 ,p_item_id IN NUMBER
225 ,p_org_id IN NUMBER
226 ,p_term IN NUMBER
227 ,p_rv_percent IN NUMBER
228 ,p_start_date IN DATE
229 ,p_end_date IN DATE
230 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
231
232 BEGIN
233 --Stubbed out - smadhava
234 NULL;
235
236 END Update_Interface_Status;
237
238 --*********************** check Interface **************************************
239
240 --+++++++++++++++++++++++ Load Interface +++++++++++++++++++++++++++++++++++++++
241
242
243 ------------------------------------------------------------------------------
244 -- PROCEDURE Load_Input_Record
245 -- It Reads data from Interface Tables and Validates. During process of validation it
246 -- stacks Error, if any, and returns ERROR status to calling process.
247 -- Calls:
248 -- report_error
249 -- update_interface_status
250 -- Called By:
251 -- process_record
252 ------------------------------------------------------------------------------
253 PROCEDURE Load_Input_Record(
254 p_init_msg_list IN VARCHAR2,
255 x_return_status OUT NOCOPY VARCHAR2,
256 x_msg_count OUT NOCOPY NUMBER,
257 x_msg_data OUT NOCOPY VARCHAR2,
258 p_batch_number IN VARCHAR2,
259 x_total_loaded OUT NOCOPY NUMBER
260 ) IS
261
262 BEGIN -- Actual Procedure Starts Here
263 --Stubbed out - smadhava
264 NULL;
265
266 END Load_Input_Record;
267
268 --------------------------------- Load Interface ------------------------------------
269 --
270
271 PROCEDURE GENERATE_RV (
272 ERRBUF OUT NOCOPY VARCHAR2
273 ,RETCODE OUT NOCOPY VARCHAR2
274 ,P_BATCH_NUMBER IN VARCHAR2
275 ,P_ORG_ID IN NUMBER
276 ,P_SO_ITEMS_ONLY_YN IN VARCHAR2 -- get only items used in quotes
277 ,P_START_DATE IN VARCHAR2
278 ,P_END_DATE IN VARCHAR2
279 ,P_TERM_LOWER_RANGE IN NUMBER -- MIN 0
280 ,P_TERM_UPPER_RANGE IN NUMBER
281 ,P_TERM_INTERVAL IN NUMBER -- IN MONTHS
282 ,P_INITIAL_RV IN NUMBER -- MIN 1
283 ,P_DECREMENT_RV_BY IN NUMBER
284 ) IS
285
286 BEGIN
287 --Stubbed out - smadhava
288 NULL;
289
290 END GENERATE_RV;
291
292
293 PROCEDURE GENERATE_RESIDUAL_VALUES
294 (
295 p_init_msg_list IN VARCHAR2
296 ,x_return_status OUT NOCOPY VARCHAR2
297 ,x_msg_count OUT NOCOPY NUMBER
298 ,x_msg_data OUT NOCOPY VARCHAR2
299 ,P_ORG_ID IN NUMBER
300 ,P_SO_ITEMS_ONLY_YN IN VARCHAR2 -- get only items used in quoting
301 ,P_START_DATE IN DATE
302 ,P_END_DATE IN DATE
303 ,P_TERM_LOWER_RANGE IN NUMBER -- MIN 1
304 ,P_TERM_UPPER_RANGE IN NUMBER
305 ,P_TERM_INTERVAL IN NUMBER -- IN MONTHS
306 ,P_INITIAL_RV IN NUMBER -- MIN 1
307 ,P_DECREMENT_RV_BY IN NUMBER
308 ,x_rv_tbl OUT NOCOPY rv_tbl
309 ) IS
310
311
312 BEGIN
313 --Stubbed out - smadhava
314 NULL;
315 END GENERATE_RESIDUAL_VALUES;
316
317
318 END OKL_RV_INTERFACE_PVT;