[Home] [Help]
PACKAGE BODY: APPS.FLM_EKB_LEAD_TIME
Source
1 package body flm_ekb_lead_time as
2 /* $Header: FLMKBLTB.pls 120.3 2012/01/20 12:58:02 sisankar noship $ * */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FLM_EKB_LEAD_TIME';
5 G_REQUEST_ID NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
6 G_PROGAM_APPLICATION_ID NUMBER := FND_GLOBAL.PROG_APPL_ID;
7 G_PROGAM_ID NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
8 G_USER_NAME FND_USER.USER_NAME%TYPE := FND_GLOBAL.USER_NAME;
9 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
10 G_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
11 G_CURRENT_USER_ID NUMBER;
12 G_CURRENT_LOGIN_ID NUMBER;
13 G_ADD_ERRORS_TO_FND_STACK VARCHAR2(1);
14 G_APPLICATION_CONTEXT VARCHAR2(30);
15 G_DATE_FORMAT CONSTANT VARCHAR2(30) := 'SYYYY-MM-DD HH24:MI:SS';
16
17 G_APPLICATION_ID NUMBER(3);
18
19
20 Procedure main(
21 ERRBUF OUT NOCOPY VARCHAR2
22 ,RETCODE OUT NOCOPY VARCHAR2
23 ,p_organization_code IN VARCHAR2
24 ) is
25
26 p_organization_id number;
27 x_return_status varchar2(1);
28 x_error_code number;
29
30 Type all_orgs is table of number;
31 l_all_orgs all_orgs;
32
33 begin
34
35
36 If p_organization_code is null then
37
38 FND_FILE.put_line(FND_FILE.LOG, 'Organization Code : ALL Organizations');
39
40 select organization_id
41 Bulk Collect
42 into l_all_orgs
43 from MTL_PARAMETERS;
44
45 For i in l_all_orgs.First..l_all_orgs.Last loop
46
47 -- dbms_output.put_line('Current Org is : '||l_all_orgs(i));
48
49 process_lead_time(
50 p_organization_id => l_all_orgs(i)
51 ,x_return_status => x_return_status
52 ,x_error_code => x_error_code
53 );
54
55 end loop;
56
57 else
58
59 FND_FILE.put_line(FND_FILE.LOG, 'Organization Code : '||p_organization_code);
60
61 select organization_id
62 into p_organization_id
63 from MTL_PARAMETERS
64 where organization_code = p_organization_code;
65
66 process_lead_time(
67 p_organization_id => p_organization_id
68 ,x_return_status => x_return_status
69 ,x_error_code => x_error_code
70 );
71
72 end if;
73
74 end;
75
76
77 procedure process_lead_time(
78 p_organization_id IN NUMBER
79 ,x_return_status OUT NOCOPY VARCHAR2
80 ,x_error_code OUT NOCOPY VARCHAR2
81 ) is
82
83 l_pull_sequence_id number;
84 l_last_run_date date;
85 l_row_count number := 0;
86 l_recv_date date;
87 l_kanban_activity_id number;
88 l_update_required varchar2(1) := 'Y';
89 l_lead_time number;
90 conc_status Boolean;
91 l_update_last_run varchar2(1) := 'N';
92 l_organization_code varchar2(3);
93
94 Cursor c_cards_for_ps (l_pull_sequence_id number )is
95 select
96 PULL_SEQUENCE_ID,
97 KANBAN_CARD_ID,
98 REPLENISHMENT_ACTIVITY_ID,
99 REPLENISHMENT_CYCLE_ID,
100 REPLENISHMENT_DATE
101 FROM FLM_EKB_ACT_SNAPSHOT
102 WHERE PULL_SEQUENCE_ID = l_pull_sequence_id
103 AND lead_time = 0;
104
105
106 Cursor c_ps_for_org is
107 Select
108 PULL_SEQUENCE_ID,
109 ORGANIZATION_ID
110 FROM MTL_KANBAN_PULL_SEQUENCES
111 where ORGANIZATION_ID = p_organization_id
112 and kanban_plan_id = -1;
113
114
115 Begin
116
117 select count(*)
118 into l_row_count
119 from flm_ekb_run_details
120 where organization_id = p_organization_id;
121
122 if l_row_count = 0 then
123
124 select Organization_code
125 into l_organization_code
126 from mtl_parameters
127 where Organization_id = p_organization_id;
128
129
130 FND_FILE.put_line(FND_FILE.LOG, 'Please run the Actual Demand Calculation first for the Organization : '||l_organization_code);
131 -- dbms_output.put_line('Please run the Actual Demand Calculation first');
132 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', 'Warning: Please run the Actual Demand Calculation before running Lead Time Calculation ');
133 -- Rollback;
134 Return;
135 l_update_required := 'N';
136 else
137 l_update_last_run := 'Y';
138 end if;
139
140 Begin
141
142 select lead_time_run_date
143 into l_last_run_date
144 from flm_ekb_run_details
145 where organization_id = p_organization_id;
146 Exception
147 When no_data_found then
148 null;
149 end;
150 if l_last_run_date is null then
151 l_last_run_date := sysdate - 366;
152 end if;
153
154 -- dbms_output.put_line('Last run date is : '||l_last_run_date);
155
156 for c_ps_activity in c_ps_for_org loop -- Start of loop for picking up ps for org.
157 l_pull_sequence_id := c_ps_activity.pull_sequence_id;
158 -- dbms_output.put_line('Pull sequence id : '||l_pull_sequence_id);
159
160 for c_card_activity in c_cards_for_ps(l_pull_sequence_id) loop -- Start of for loop for every card_id
161
162 -- dbms_output.put_line('Kanban card id : '||c_card_activity.kanban_card_id);
163 -- dbms_output.put_line('Kanban activity id : '||c_card_activity.REPLENISHMENT_activity_id);
164
165 begin
166 -- FND_FILE.put_line(FND_FILE.LOG, 'Kanban Activity ID is : '||c_card_activity.REPLENISHMENT_activity_id);
167 Select
168 KANBAN_ACTIVITY_ID,
169 CREATION_DATE
170 into l_kanban_activity_id,
171 l_recv_date
172 FROM MTL_KANBAN_CARD_ACTIVITY MKCA
173 WHERE KANBAN_CARD_ID = c_card_activity.kanban_card_id
174 AND SUPPLY_STATUS >= 2 AND SUPPLY_STATUS < 3 -- Supply Status Full
175 AND KANBAN_ACTIVITY_ID = ( select min(kanban_activity_id)
176 from MTL_KANBAN_CARD_ACTIVITY
177 where KANBAN_CARD_ID = c_card_activity.kanban_card_id
178 AND SUPPLY_STATUS >= 2 AND SUPPLY_STATUS < 3 -- Supply Status Full
179 AND CREATION_DATE > l_last_run_date
180 AND KANBAN_ACTIVITY_ID > c_card_activity.REPLENISHMENT_activity_id)
181 AND CREATION_DATE > l_last_run_date;
182 Exception
183 When no_data_found then
184 l_update_required := 'N';
185 end;
186
187 if l_update_required = 'Y' then
188 -- FND_FILE.put_line(FND_FILE.LOG, 'Received Activity ID is : '||l_kanban_activity_id);
189 Update FLM_EKB_ACT_SNAPSHOT
190 Set RECEIVED_DATE = l_recv_date,
191 lead_time = to_number( l_recv_date - REPLENISHMENT_DATE ),
192 RECEIVED_ACTIVITY_ID = l_kanban_activity_id,
193 last_update_date = sysdate,
194 last_updated_by = G_LOGIN_ID,
195 last_update_login = G_LOGIN_ID
196 where KANBAN_CARD_ID = c_card_activity.kanban_card_id
197 and REPLENISHMENT_ACTIVITY_ID = c_card_activity.REPLENISHMENT_activity_id
198 and lead_time = 0;
199 if sql%rowcount > 0 then
200
201 -- dbms_output.put_line('RECEIVED_DATE : '||l_recv_date);
202 -- dbms_output.put_line('REPLENISHMENT_DATE : '||c_card_activity.REPLENISHMENT_DATE);
203 -- dbms_output.put_line('lead_time is : '||(l_recv_date - c_card_activity.REPLENISHMENT_DATE ));
204 -- dbms_output.put_line('Update Successful for KANBAN_ACTIVITY_ID : '||c_card_activity.REPLENISHMENT_activity_id);
205 null;
206
207 end if;
208
209 /*
210 select lead_time
211 into l_lead_time
212 from FLM_EKB_ACT_SNAPSHOT
213 where REPLENISHMENT_ACTIVITY_ID = c_card_activity.REPLENISHMENT_activity_id;
214 */
215 -- dbms_output.put_line ('final lead_time is : '||l_lead_time);
216
217
218 end if;
219
220 l_update_required := 'Y';
221
222 End loop;
223
224 End loop;
225
226 if l_update_last_run = 'Y' then
227 Update flm_ekb_run_details
228 set lead_time_run_date = sysdate,
229 Last_lead_time_request_id = g_request_id,
230 last_updated_by = G_LOGIN_ID,
231 last_update_date = sysdate,
232 last_update_login = G_LOGIN_ID
233 where organization_id = p_organization_id;
234
235 end if;
236
237 /* This part is for testing
238 select count(*)
239 into l_row_count
240 from flm_ekb_act_snapshot
241 where lead_time > 0
242 and organization_id = p_organization_id;
243
244 FND_FILE.put_line(FND_FILE.LOG, 'Total rows updated are : '||l_row_count);
245 -- dbms_output.put_line('Total rows updated are : '||l_row_count);
246 end of testing */
247
248 end;
249
250
251 End flm_ekb_lead_time;