[Home] [Help]
PACKAGE BODY: APPS.FLM_EKB_HEALTH_STATUS
Source
1 package body flm_ekb_health_status as
2 /* $Header: FLMKBHSB.pls 120.5.12020000.3 2012/08/14 17:38:00 pding ship $ * */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FLM_EKB_HEALTH_STATUS';
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 Procedure main(
22 ERRBUF OUT NOCOPY VARCHAR2
23 ,RETCODE OUT NOCOPY VARCHAR2
24 ,p_organization_code IN VARCHAR2
25 ) is
26
27 p_organization_id number;
28 x_return_status varchar2(1);
29 x_error_code number;
30
31 Type all_orgs is table of number;
32 l_all_orgs all_orgs;
33
34 begin
35
36
37 If p_organization_code is null then
38
39 FND_FILE.put_line(FND_FILE.LOG, 'Organization Code : ALL Organizations');
40
41 select organization_id
42 Bulk Collect
43 into l_all_orgs
44 from MTL_PARAMETERS;
45
46 For i in l_all_orgs.First..l_all_orgs.Last loop
47
48 -- dbms_output.put_line('Current Org is : '||l_all_orgs(i));
49
50 process_health_status(
51 p_organization_id => l_all_orgs(i)
52 ,x_return_status => x_return_status
53 ,x_error_code => x_error_code
54 );
55
56 end loop;
57
58 else
59
60 FND_FILE.put_line(FND_FILE.LOG, 'Organization Code : '||p_organization_code);
61
62 select organization_id
63 into p_organization_id
64 from MTL_PARAMETERS
65 where organization_code = p_organization_code;
66
67 process_health_status(
68 p_organization_id => p_organization_id
69 ,x_return_status => x_return_status
70 ,x_error_code => x_error_code
71 );
72
73 end if;
74
75 end;
76
77
78
79 procedure process_health_status(
80 p_organization_id IN NUMBER
81 ,x_return_status OUT NOCOPY VARCHAR2
82 ,x_error_code OUT NOCOPY VARCHAR2
83 ) is
84
85 l_pull_sequence_id number;
86 l_row_count number := 0;
87 l_recv_date date;
88 l_kanban_activity_id number;
89 l_update_required varchar2(1) := 'Y';
90
91
92 l_health number;
93 l_actual_stock number;
94 l_good_health number;
95 l_bad_health number;
96 -- x_error_code number;
97 -- x_return_status number;
98 x_good_health number;
99 x_bad_health number;
100 l_continue_processing varchar2(1);
101 conc_status Boolean;
102
103
104 -- Fixed issue to calculate inventory health when there are no cards in full status.
105 Cursor c_cards_for_ps (l_pull_sequence_id number )is
106 select
107 COUNT(KANBAN_CARD_ID) Total_cards,
108 nvl(SUM(KANBAN_SIZE),0) Total_size
109 FROM MTL_KANBAN_CARDS
110 WHERE PULL_SEQUENCE_ID = l_pull_sequence_id
111 AND SUPPLY_STATUS >= 2 AND SUPPLY_STATUS < 3 --Supply Status Full
112 AND CARD_STATUS in (1,2); /*Bug 14262914:Only considering Active and Hold card*/
113
114
115 Cursor c_ps_for_org is
116 Select
117 PULL_SEQUENCE_ID,
118 ORGANIZATION_ID,
119 ((nvl(AVG_DEPENDENT_DEMAND,0) + nvl(AVG_INDEPENDENT_DEMAND,0)) * nvl(SAFETY_STOCK_DAYS,0)) SAFETY_STOCK
120 FROM MTL_KANBAN_PULL_SEQUENCES
121 where ORGANIZATION_ID = p_organization_id
122 and SAFETY_STOCK_DAYS > 0
123 and kanban_plan_id = -1;
124
125
126 Begin
127
128 for c_ps_activity in c_ps_for_org loop -- Start of loop for picking up ps for org.
129
130
131 l_good_health := 0;
132 l_bad_health := 0;
133 l_continue_processing := 'Y';
134
135 /* custom hook placeholder begin */
136
137
138 FLM_KANBAN_CUSTOM_PKG.HEALTH_FOR_PS(p_pull_sequence_id => c_ps_activity.pull_sequence_id
139 ,x_return_status => x_return_status
140 ,x_error_code => x_error_code
141 ,x_good_health => l_good_health
142 ,x_bad_health => l_bad_health
143 );
144
145
146 if x_good_health is not null and x_bad_health is not null and x_return_status = 'S' then
147
148
149 l_good_health := x_good_health;
150 l_bad_health := x_bad_health;
151
152 else
153
154 /* call for anoop's api to get the good and bad health */
155
156 l_good_health := flm_kanban_config_params.get_good_inv_health_per( p_org_id => c_ps_activity.organization_id );
157 l_bad_health := flm_kanban_config_params.get_bad_inv_health_per( p_org_id => c_ps_activity.organization_id );
158
159 -- dbms_output.put_line('Pull sequence ID : '||l_pull_sequence_id);
160 -- dbms_output.put_line('Good health is : '||l_good_health);
161 -- dbms_output.put_line('Bad health is : '||l_bad_health);
162
163 end if;
164
165 if l_good_health = 0 OR l_bad_health = 0 then
166
167 FND_FILE.put_line(FND_FILE.LOG, 'Please setup Good and Bad Inventory Health for the Pull Sequence '||c_ps_activity.pull_sequence_id);
168 -- dbms_output.put_line('Please run the Actual Demand Calculation first');
169 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', 'Warning: Setup missing for Good and Bad Inventory Health ');
170 l_continue_processing := 'N';
171
172
173 end if;
174
175 /* custom hook placeholder end */
176
177
178 if c_ps_activity.safety_stock > 0 and l_continue_processing = 'Y' then
179 l_pull_sequence_id := c_ps_activity.pull_sequence_id;
180 -- dbms_output.put_line('Pull sequence id : '||l_pull_sequence_id);
181 l_actual_stock := 0;
182 l_health := 0;
183
184 for c_card_activity in c_cards_for_ps(l_pull_sequence_id) loop -- Start of for loop for every card_id
185
186 -- dbms_output.put_line('Kanban card id : '||c_card_activity.kanban_card_id);
187 -- dbms_output.put_line('Kanban activity id : '||c_card_activity.kanban_activity_id);
188
189 l_actual_stock := l_actual_stock + c_card_activity.Total_size;
190
191
192 -- dbms_output.put_line('Update Successful for KANBAN_ACTIVITY_ID : '||c_card_activity.kanban_activity_id);
193
194
195 End loop;
196 /*Bug 14262914: when we calculate the inventory health, it should be actual stock/safety stock instead of (actual stock - safety stock)/safety stock*/
197 l_health := (l_actual_stock/ c_ps_activity.safety_stock ) * 100;
198
199 /*Bug 13052019: In CAB we got feedback from customer that having inventory between bad and good
200 inventory levels is Good. Lower than Bad level is Bad and Higher than good level is Warning. Thus
201 changed update of INV_HEALTH_STATUS accordingly*/
202
203 if l_health >= l_good_health then
204 UPDATE MTL_KANBAN_PULL_SEQUENCES
205 SET INV_HEALTH_STATUS = 2, --Warning
206 LAST_UPDATE_DATE = sysdate,
207 LAST_UPDATED_BY = G_USER_ID
208 WHERE PULL_SEQUENCE_ID = l_pull_sequence_id;
209 elsif l_health < l_bad_health then
210 UPDATE MTL_KANBAN_PULL_SEQUENCES
211 SET INV_HEALTH_STATUS = 1, --Bad
212 LAST_UPDATE_DATE = sysdate,
213 LAST_UPDATED_BY = G_USER_ID
214 WHERE PULL_SEQUENCE_ID = l_pull_sequence_id;
215 elsif l_health < l_good_health and l_health >= l_bad_health then
216 UPDATE MTL_KANBAN_PULL_SEQUENCES
217 SET INV_HEALTH_STATUS = 3, --Good
218 LAST_UPDATE_DATE = sysdate,
219 LAST_UPDATED_BY = G_USER_ID
220 WHERE PULL_SEQUENCE_ID = l_pull_sequence_id;
221 end if;
222
223 end if; -- end if for c_ps_activity.safety_stock > 0
224
225 End loop;
226
227
228 select count(*)
229 into l_row_count
230 from flm_ekb_run_details
231 where organization_id = p_organization_id;
232
233 if l_row_count = 0 then
234 insert into flm_ekb_run_details(
235 Organization_id,
236 Actual_demand_run_date,
237 Last_demand_request_id,
238 Lead_time_run_date,
239 Last_lead_time_request_id,
240 Health_status_run_date,
241 Last_Health_status_request_id,
242 Unmoved_cards_run_date,
243 Last_Unmoved_cards_request_id,
244 creation_date,
245 created_by,
246 last_updated_by,
247 last_update_date,
248 last_update_login
249 )
250 values(p_organization_id
251 ,null
252 ,null
253 ,null
254 ,null
255 ,sysdate
256 ,G_REQUEST_ID
257 ,null
258 ,null
259 ,sysdate
260 ,G_USER_ID
261 ,G_USER_ID
262 ,sysdate
263 ,G_LOGIN_ID
264 );
265
266 else
267
268 Update flm_ekb_run_details
269 set Health_status_run_date = sysdate,
270 Last_Health_status_request_id = G_REQUEST_ID,
271 last_updated_by = G_USER_ID,
272 last_update_date = sysdate,
273 last_update_login = G_LOGIN_ID
274 where organization_id = p_organization_id;
275
276 end if;
277
278
279
280
281
282
283 /* This part is for testing
284 select count(*)
285 into l_row_count
286 from mtl_kanban_pull_sequences
287 where INV_HEALTH_STATUS = 1
288 and organization_id = p_organization_id;
289
290 FND_FILE.put_line(FND_FILE.LOG, 'Total rows with Bad Health are : '||l_row_count);
291 -- dbms_output.put_line('Total rows with Good Health are : '||l_row_count);
292
293 select count(*)
294 into l_row_count
295 from mtl_kanban_pull_sequences
296 where INV_HEALTH_STATUS = 2
297 and organization_id = p_organization_id;
298
299 FND_FILE.put_line(FND_FILE.LOG, 'Total rows with Warning Health are : '||l_row_count);
300 -- dbms_output.put_line('Total rows with Warning Health are : '||l_row_count);
301
302 select count(*)
303 into l_row_count
304 from mtl_kanban_pull_sequences
305 where INV_HEALTH_STATUS = 3
306 and organization_id = p_organization_id;
307
308 FND_FILE.put_line(FND_FILE.LOG, 'Total rows with Good Health are : '||l_row_count);
309 -- dbms_output.put_line('Total rows with Bad Health are : '||l_row_count);
310 end of testing */
311
312 end;
313
314
315 End flm_ekb_health_status;