[Home] [Help]
PACKAGE BODY: APPS.FLM_EKB_UNMOVED_CARDS
Source
1 package body flm_ekb_unmoved_cards as
2 /* $Header: FLMKBUCB.pls 120.3.12020000.2 2012/08/22 14:22:31 sisankar ship $ * */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FLM_EKB_UNMOVED_CARDS';
6 G_REQUEST_ID NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
7 G_PROGAM_APPLICATION_ID NUMBER := FND_GLOBAL.PROG_APPL_ID;
8 G_PROGAM_ID NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
9 G_USER_NAME FND_USER.USER_NAME%TYPE := FND_GLOBAL.USER_NAME;
10 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
11 G_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
12 G_CURRENT_USER_ID NUMBER;
13 G_CURRENT_LOGIN_ID NUMBER;
14 G_ADD_ERRORS_TO_FND_STACK VARCHAR2(1);
15 G_APPLICATION_CONTEXT VARCHAR2(30);
16 G_DATE_FORMAT CONSTANT VARCHAR2(30) := 'SYYYY-MM-DD HH24:MI:SS';
17
18 G_APPLICATION_ID NUMBER(3);
19
20
21 /*
22 TYPE flm_custom_uc_rec IS RECORD (
23 kanban_card_id number,
24 Unmoved_flag varchar2(1),
25 last_activity_data date
26 );
27
28 TYPE flm_custom_uc_table IS TABLE OF flm_custom_uc_rec;
29
30
31 TYPE flm_custom_uc_setup_rec IS RECORD (
32 status varchar2(10),
33 leadtime_days number
34 );
35
36 TYPE flm_custom_uc_setup_table IS TABLE OF flm_custom_uc_setup_rec;
37 */
38
39 Procedure main(
40 ERRBUF OUT NOCOPY VARCHAR2
41 ,RETCODE OUT NOCOPY VARCHAR2
42 ,p_organization_code IN VARCHAR2
43 ) is
44
45 p_organization_id number;
46 x_return_status varchar2(1);
47 x_error_code number;
48
49 Type all_orgs is table of number;
50 l_all_orgs all_orgs;
51
52 begin
53
54
55 If p_organization_code is null then
56
57 FND_FILE.put_line(FND_FILE.LOG, 'Organization Code : ALL Organizations');
58
59 select organization_id
60 Bulk Collect
61 into l_all_orgs
62 from MTL_PARAMETERS;
63
64 For i in l_all_orgs.First..l_all_orgs.Last loop
65
66 -- dbms_output.put_line('Current Org is : '||l_all_orgs(i));
67
68 process_unmoved_cards(
69 p_organization_id => l_all_orgs(i)
70 ,x_return_status => x_return_status
71 ,x_error_code => x_error_code
72 );
73
74 end loop;
75
76 else
77
78 FND_FILE.put_line(FND_FILE.LOG, 'Organization Code : '||p_organization_code);
79
80 select organization_id
81 into p_organization_id
82 from MTL_PARAMETERS
83 where organization_code = p_organization_code;
84
85 process_unmoved_cards(
86 p_organization_id => p_organization_id
87 ,x_return_status => x_return_status
88 ,x_error_code => x_error_code
89 );
90
91 end if;
92
93 end;
94
95
96 procedure process_unmoved_cards(
97 p_organization_id IN NUMBER
98 ,x_return_status OUT NOCOPY VARCHAR2
99 ,x_error_code OUT NOCOPY VARCHAR2
100 ) is
101
102 l_pull_sequence_id number;
103 l_row_count number := 0;
104 l_recv_date date;
105 l_kanban_activity_id number;
106 l_update_required varchar2(1) := 'Y';
107 conc_status Boolean;
108
109
110 l_health number;
111 l_actual_stock number;
112 l_good_health number;
113 l_bad_health number;
114 x_good_health number;
115 x_bad_health number;
116 p_org_id number;
117
118 l_card_id number;
119 l_unmoved_table flm_kanban_config_params.flm_unmovecard_table;
120 l_unmoved_lead_table flm_unmoved_lead_table;
121
122 x_uc_from_custom flm_kanban_custom_pkg.flm_custom_uc_table;
123 x_uc_setup_from_custom flm_kanban_custom_pkg.flm_custom_uc_setup_table;
124 x_pull_sequence_id number;
125
126 -- l_custom_uc_table flm_kanban_custom_pkg.flm_custom_uc_table;
127 -- l_custome_uclead_table flm_kanban_custom_pkg.flm_custom_uc_lead_table;
128
129 l_organization_code varchar2(3);
130
131 l_last_ps number;
132
133 l_ignore_custom varchar2(1);
134 l_uc_custom varchar2(1);
135 l_uc_custom_setup varchar2(1);
136
137
138 Cursor c_cards_for_ps (l_card_id number )is
139 select
140 CREATION_DATE,
141 SUPPLY_STATUS
142 FROM MTL_KANBAN_CARD_ACTIVITY
143 WHERE KANBAN_ACTIVITY_ID = ( Select MAX(KANBAN_ACTIVITY_ID)
144 from MTL_KANBAN_CARD_ACTIVITY MKCA
145 WHERE KANBAN_CARD_ID = l_card_id );
146
147
148 Cursor c_ps_for_org is
149 Select
150 MKCA.CREATION_DATE,
151 MKCA.KANBAN_ACTIVITY_ID,
152 MKC.SUPPLY_STATUS,
153 MKPS.PULL_SEQUENCE_ID,
154 MKC.KANBAN_CARD_ID,
155 MKPS.ORGANIZATION_ID,
156 MKPS.REPLENISHMENT_LEAD_TIME
157 FROM MTL_KANBAN_PULL_SEQUENCES MKPS , MTL_KANBAN_CARDS MKC,
158 MTL_KANBAN_CARD_ACTIVITY MKCA
159 where MKPS.ORGANIZATION_ID = p_organization_id
160 and MKPS.PULL_SEQUENCE_ID = MKC.PULL_SEQUENCE_ID
161 and MKPS.REPLENISHMENT_LEAD_TIME > 0
162 and MKPS.KANBAN_PLAN_ID = -1
163 and MKC.CARD_STATUS in (1,2) -- Exclude Calculation for Cancelled and Planned Cards. Bug 14311023
164 and MKCA.KANBAN_ACTIVITY_ID = ( Select MAX(MKCA1.KANBAN_ACTIVITY_ID)
165 from MTL_KANBAN_CARD_ACTIVITY MKCA1
166 WHERE MKCA1.KANBAN_CARD_ID = MKC.KANBAN_CARD_ID
167 AND MKCA1.SUPPLY_STATUS = MKC.SUPPLY_STATUS)
168 ORDER BY MKPS.PULL_SEQUENCE_ID;
169
170 Begin
171
172 Update MTL_KANBAN_CARDS
173 Set MOVE_STATUS = null
174 where pull_sequence_id in ( select pull_sequence_id from
175 MTL_KANBAN_PULL_SEQUENCES where ORGANIZATION_ID = p_organization_id );
176
177 -- l_unmoved_lead_table := flm_unmoved_lead_table;
178 l_unmoved_table := flm_kanban_config_params.get_unmovedcard_leadtime(p_org_id => p_organization_id);
179 if l_unmoved_table.count = 0 then
180
181 select Organization_code
182 into l_organization_code
183 from mtl_parameters
184 where organization_id = p_organization_id;
185
186 FND_FILE.put_line(FND_FILE.LOG, 'No Setups Exists for Unmoved Cards for organization : '||l_organization_code);
187 -- dbms_output.put_line('Please run the Actual Demand Calculation first');
188 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', 'Warning: No Setups Exists for Unmoved Cards');
189 elsif l_unmoved_table.count > 0 then
190
191 l_unmoved_lead_table := flm_unmoved_lead_table();
192 l_unmoved_lead_table.Extend;
193
194
195 for i in l_unmoved_table.first..l_unmoved_table.Last loop
196
197 l_unmoved_lead_table(i).status := l_unmoved_table(i).Status;
198 l_unmoved_lead_table(i).leadtime := l_unmoved_table(i).leadtime;
199 l_unmoved_lead_table.Extend;
200
201 -- dbms_output.put_line('Status from lead table is : '||l_unmoved_lead_table(i).Status);
202 -- dbms_output.put_line('Lead Time from lead table is : '||l_unmoved_lead_table(i).Leadtime);
203 -- dbms_output.put_line('Lead percent from lead table is : '||l_unmoved_lead_table(i).Leadtime_pct);
204
205 end loop;
206
207 -- select leadtime into l_lead_time from Table(l_unmoved_lead_table);
208
209 l_last_ps := 0;
210 for c_ps_activity in c_ps_for_org loop -- Start of loop for picking up ps for org.
211
212 l_uc_custom := 'N';
213
214 /* start of calculation of Planned Lead Time % */
215
216 If l_last_ps <> c_ps_activity.pull_sequence_id then
217
218 l_ignore_custom := 'N';
219 l_uc_custom_setup := 'N';
220
221 /* Call to check if Custom Hook returns anything for this Pull Sequence */
222
223
224 x_uc_from_custom := flm_kanban_custom_pkg.flm_custom_uc_table();
225 x_uc_setup_from_custom := flm_kanban_custom_pkg.flm_custom_uc_setup_table();
226
227 -- dbms_output.put_line('pull sequence is : '||c_ps_activity.pull_sequence_id);
228
229 FLM_KANBAN_CUSTOM_PKG.UNMOVED_CARDS_HOOK( p_org_id => c_ps_activity.organization_id
230 ,p_pull_sequence_id => c_ps_activity.pull_sequence_id
231 ,x_return_status => x_return_status
232 ,x_error_code => x_error_code
233 ,x_pull_sequence_id => x_pull_sequence_id
234 ,x_uc_from_custom => x_uc_from_custom
235 ,x_uc_setup_from_custom => x_uc_setup_from_custom
236 );
237
238 -- dbms_output.put_line('count of x_uc_from_custom : '||x_uc_from_custom.count );
239 -- dbms_output.put_line('count of x_uc_setup_from_custom : '||x_uc_setup_from_custom.count );
240
241 if (x_uc_from_custom.count = 0 and x_uc_setup_from_custom.count = 0 ) then -- start check of custom_hook
242
243 l_ignore_custom := 'Y';
244
245 elsif (x_uc_setup_from_custom.count > 0 and c_ps_activity.pull_sequence_id = x_pull_sequence_id) then
246
247 l_uc_custom_setup := 'Y';
248
249 elsif (x_uc_from_custom.count > 0 and c_ps_activity.pull_sequence_id = x_pull_sequence_id) then
250
251 l_uc_custom := 'Y';
252
253 end if; -- end check for custom hook
254
255
256
257
258 /* End-of Call to check if Custom Hook returns anything for this Pull Sequence */
259
260 if l_ignore_custom = 'Y' then
261
262 for i in l_unmoved_table.first..l_unmoved_table.Last loop
263 l_unmoved_lead_table(i).leadtime_pct := (( to_number(l_unmoved_lead_table(i).leadtime) * c_ps_activity.REPLENISHMENT_LEAD_TIME ) / 100 );
264 end loop;
265 end if;
266
267 l_last_ps := c_ps_activity.pull_sequence_id;
268
269
270 end if; -- end of check l_last_ps not equal to c_ps_activity.pull_sequence_id
271
272 /* end of calculation of Planned Lead Time % */
273
274 l_card_id := c_ps_activity.kanban_card_id;
275 l_pull_sequence_id := c_ps_activity.pull_sequence_id;
276
277 if l_ignore_custom = 'Y' then -- start l_ignore_custom = Y
278
279
280 -- dbms_output.put_line('Pull sequence id : '||l_pull_sequence_id);
281
282 for i in l_unmoved_lead_table.FIRST..l_unmoved_lead_table.LAST LOOP
283 if to_number(l_unmoved_lead_table(i).status) = c_ps_activity.SUPPLY_STATUS then
284 if (sysdate - c_ps_activity.CREATION_DATE ) > l_unmoved_lead_table(i).leadtime_pct then
285
286 -- dbms_output.put_line('Kanban card id : '||c_card_activity.kanban_card_id);
287 -- dbms_output.put_line('Kanban activity id : '||c_card_activity.kanban_activity_id);
288
289 Update MTL_KANBAN_CARDS
290 SET MOVE_STATUS = 1,
291 LAST_STATUS_CHANGE_DATE = c_ps_activity.CREATION_DATE,
292 LAST_UPDATE_DATE = sysdate,
293 LAST_UPDATED_BY = G_USER_ID
294 Where PULL_SEQUENCE_ID = c_ps_activity.pull_sequence_id
295 and kanban_card_id = c_ps_activity.kanban_card_id;
296
297 -- dbms_output.put_line('Update Successful for KANBAN_ACTIVITY_ID : '||c_card_activity.kanban_activity_id);
298 end if;
299 end if; -- to_number(l_unmoved_lead_table(i).status) = c_ps_activity.SUPPLY_STATUS
300 End loop;
301
302 end if; -- end of l_ignore_custom = Y
303
304
305 if l_uc_custom = 'Y' then
306
307 /* User is passing the information via custom hook call as which cards in this Pull_sequence are to
308 * be marked as Unmoved. No further validation is required */
309
313 LAST_STATUS_CHANGE_DATE = x_uc_from_custom(i).last_activity_date,
310 for i in x_uc_from_custom.FIRST..x_uc_from_custom.LAST LOOP
311 Update MTL_KANBAN_CARDS
312 SET MOVE_STATUS = x_uc_from_custom(i).unmoved_flag,
314 LAST_UPDATE_DATE = sysdate,
315 LAST_UPDATED_BY = G_USER_ID
316 Where PULL_SEQUENCE_ID = l_pull_sequence_id
317 and kanban_card_id = x_uc_from_custom(i).kanban_card_id;
318 end loop;
319
320
321 end if;
322
323
324
325 if l_uc_custom_setup = 'Y' then
326
327 /* User is passing the information via custom hook call as which status in this
328 * pull_sequence should be considered as unmoved and after how many days. */
329
330 -- dbms_output.put_line('Should start here');
331
332 for i in x_uc_setup_from_custom.FIRST..x_uc_setup_from_custom.LAST LOOP
333 if to_number(x_uc_setup_from_custom(i).status) = c_ps_activity.SUPPLY_STATUS then
334 if (sysdate - c_ps_activity.CREATION_DATE ) > x_uc_setup_from_custom(i).leadtime_days then
335
336 -- dbms_output.put_line('Pull sequence id : '||x_pull_sequence_id);
337 -- dbms_output.put_line('Kanban card id : '||c_ps_activity.kanban_card_id);
338 -- dbms_output.put_line('Kanban activity id : '||c_ps_activity.kanban_activity_id);
339 -- dbms_output.put_line('supply status is : '||c_ps_activity.supply_status);
340
341 Update MTL_KANBAN_CARDS
342 SET MOVE_STATUS = 1,
343 LAST_STATUS_CHANGE_DATE = c_ps_activity.CREATION_DATE,
344 LAST_UPDATE_DATE = sysdate,
345 LAST_UPDATED_BY = G_USER_ID
346 Where PULL_SEQUENCE_ID = x_pull_sequence_id
347 and kanban_card_id = c_ps_activity.kanban_card_id;
348
349 -- dbms_output.put_line('Update Successful for KANBAN_ACTIVITY_ID : '||c_card_activity.kanban_activity_id);
350 end if;
351 end if; -- to_number(x_uc_setup_from_custom(i).status) = c_ps_activity.SUPPLY_STATUS
352 End loop;
353
354
355 end if;
356
357
358
359
360 End loop; -- End of loop for picking up ps for org.
361
362
363
364 select count(*)
365 into l_row_count
366 from flm_ekb_run_details
367 where organization_id = p_organization_id;
368
369 if l_row_count = 0 then
370 insert into flm_ekb_run_details(
371 Organization_id,
372 Actual_demand_run_date,
373 Last_demand_request_id,
374 Lead_time_run_date,
375 Last_lead_time_request_id,
376 Health_status_run_date,
377 Last_Health_status_request_id,
378 Unmoved_cards_run_date,
379 Last_Unmoved_cards_request_id,
380 creation_date,
381 created_by,
382 last_updated_by,
383 last_update_date,
384 last_update_login
385 )
386 values(p_organization_id
387 ,null
388 ,null
389 ,null
390 ,null
391 ,null
392 ,null
393 ,sysdate
394 ,G_REQUEST_ID
395 ,sysdate
396 ,G_USER_ID
397 ,G_USER_ID
398 ,sysdate
399 ,G_LOGIN_ID
400 );
401
402 else
403
404 Update flm_ekb_run_details
405 set Unmoved_cards_run_date = sysdate,
406 Last_Unmoved_cards_request_id = G_REQUEST_ID,
407 last_updated_by = G_USER_ID,
408 last_update_date = sysdate,
409 last_update_login = G_LOGIN_ID
410 where organization_id = p_organization_id;
411
412 end if;
413
414
415
416 end if;
417
418
419 /* This part is for testing
420 select count(move_status)
421 into l_row_count
422 from mtl_kanban_cards
423 where Move_status = 1;
424
425 FND_FILE.put_line(FND_FILE.LOG, 'Total rows with Unmoved cards are : '||l_row_count);
426 dbms_output.put_line('Total rows with Unmoved cards are : '||l_row_count);
427
428 end of testing */
429
430 end;
431
432
433 End flm_ekb_unmoved_cards;