[Home] [Help]
PACKAGE BODY: APPS.FLM_EKB_ACTUAL_DEMAND
Source
1 package body flm_ekb_actual_demand as
2 /* $Header: FLMKBADB.pls 120.5 2012/01/20 13:21:44 sisankar noship $ * */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FLM_EKB_ACTUAL_DEMAND';
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_actual_demand(
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_actual_demand(
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 procedure process_actual_demand(
79 p_organization_id IN NUMBER
80 ,x_return_status OUT NOCOPY VARCHAR2
81 ,x_error_code OUT NOCOPY VARCHAR2
82 ) is
83
84 l_pull_sequence_id number;
85 l_last_run_date date;
86 l_row_count number := 0;
87
88 Cursor c_cards_for_ps ( l_last_run_date Date) is
89 select
90 PULL_SEQUENCE_ID,
91 KANBAN_CARD_ID,
92 INVENTORY_ITEM_ID,
93 ORGANIZATION_ID,
94 SUBINVENTORY_NAME,
95 ( select Meaning from mfg_lookups where Lookup_type = 'MTL_KANBAN_SUPPLY_STATUS' and lookup_code=MKC.SUPPLY_STATUS) SUPPLY_STATUS,
96 ( select Meaning from mfg_lookups where Lookup_type = 'MTL_KANBAN_CARD_STATUS' and lookup_code=MKC.CARD_STATUS) CARD_STATUS,
97 ( select Meaning from mfg_lookups where Lookup_type = 'MTL_KANBAN_CARD_TYPE' and lookup_code=MKC.KANBAN_CARD_TYPE) KANBAN_CARD_TYPE,
98 ( select Meaning from mfg_lookups where Lookup_type = 'MTL_KANBAN_SOURCE_TYPE' and lookup_code=MKC.SOURCE_TYPE) SOURCE_TYPE,
99 KANBAN_SIZE,
100 CURRENT_REPLNSH_CYCLE_ID
101 FROM MTL_KANBAN_CARDS MKC
102 WHERE ORGANIZATION_ID = p_organization_id
103 -- AND PULL_SEQUENCE_ID = l_pull_sequence_id
104 AND LAST_UPDATE_DATE > l_last_run_date;
105
106
107 Cursor c_ps_for_org(l_last_run_date Date) is
108 Select
109 PULL_SEQUENCE_ID,
110 ORGANIZATION_ID
111 FROM MTL_KANBAN_PULL_SEQUENCES
112 where ORGANIZATION_ID = p_organization_id
113 and kanban_plan_id = -1 ;
114 -- and LAST_UPDATE_DATE > l_last_run_date;
115
116
117 Begin
118
119
120 Begin
121
122 select Actual_demand_run_date
123 into l_last_run_date
124 from flm_ekb_run_details
125 where organization_id = p_organization_id;
126 Exception
127 When no_data_found then
128 l_last_run_date := sysdate - 366;
129
130 end;
131
132 if l_last_run_date is null then
133 l_last_run_date := sysdate - 366;
134 end if;
135
136 -- dbms_output.put_line('Last run date is : '||l_last_run_date);
137
138 -- for c_ps_activity in c_ps_for_org loop - Start of loop for picking up ps for org.
139
140 for c_card_activity in c_cards_for_ps(l_last_run_date) loop -- Start of for loop for every card_id
141
142 Insert into FLM_EKB_ACT_SNAPSHOT(
143 ORGANIZATION_ID
144 ,PULL_SEQUENCE_ID
145 ,KANBAN_CARD_ID
146 ,REPLENISHMENT_ACTIVITY_ID
147 ,REPLENISHMENT_CYCLE_ID
148 ,REPLENISHMENT_DATE
149 ,RECEIVED_ACTIVITY_ID
150 ,RECEIVED_DATE
151 ,LEAD_TIME
152 ,KANBAN_SIZE
153 ,Creation_date
154 ,Created_by
155 ,Last_update_date
156 ,Last_updated_by
157 ,Last_Update_login
158 )
159 Select
160 c_card_activity.organization_id,
161 c_card_activity.pull_sequence_id,
162 c_card_activity.kanban_card_id,
163 min(KANBAN_ACTIVITY_ID),
164 REPLENISHMENT_CYCLE_ID,
165 null,
166 null,
167 Null,
168 0,
169 null,
170 sysdate,
171 G_USER_ID,
172 sysdate,
173 G_USER_ID,
174 null
175 FROM MTL_KANBAN_CARD_ACTIVITY MKCA
176 WHERE KANBAN_CARD_ID = c_card_activity.kanban_card_id
177 AND ( ( SUPPLY_STATUS >= 4 AND SUPPLY_STATUS < 5 AND SOURCE_TYPE <> 3 ) OR
178 ( SUPPLY_STATUS >= 5 AND SUPPLY_STATUS < 6 AND SOURCE_TYPE = 3 )
179 ) -- Supply status Inprocess for Intra org kanban and Empty for other kanban types
180 AND CREATION_DATE > l_last_run_date
181 group by c_card_activity.organization_id, c_card_activity.pull_sequence_id,c_card_activity.kanban_card_id , REPLENISHMENT_CYCLE_ID
182 Order by REPLENISHMENT_CYCLE_ID;
183
184
185 Update FLM_EKB_ACT_SNAPSHOT FEAS
186 Set ( REPLENISHMENT_DATE , KANBAN_SIZE ) = (Select CREATION_DATE,KANBAN_SIZE
187 from MTL_KANBAN_CARD_ACTIVITY MKCA
188 where MKCA.Kanban_Activity_id = FEAS.REPLENISHMENT_ACTIVITY_ID)
189 where REPLENISHMENT_DATE is null;
190
191
192 End loop;
193
194 select count(*)
195 into l_row_count
196 from flm_ekb_run_details
197 where organization_id = p_organization_id;
198
199 if l_row_count = 0 then
200 insert into flm_ekb_run_details(
201 Organization_id,
202 Actual_demand_run_date,
203 Last_demand_request_id,
204 Lead_time_run_date,
205 Last_lead_time_request_id,
206 Health_status_run_date,
207 Last_Health_status_request_id,
208 Unmoved_cards_run_date,
209 Last_Unmoved_cards_request_id,
210 creation_date,
211 created_by,
212 last_updated_by,
213 last_update_date,
214 last_update_login
215 )
216 values(p_organization_id
217 ,sysdate
218 ,G_REQUEST_ID
219 ,null
220 ,null
221 ,null
222 ,null
223 ,null
224 ,null
225 ,sysdate
226 ,G_USER_ID
227 ,G_USER_ID
228 ,sysdate
229 ,G_LOGIN_ID
230 );
231
232 else
233
234 Update flm_ekb_run_details
235 set Actual_demand_run_date = sysdate,
236 Last_demand_request_id = G_REQUEST_ID,
237 last_updated_by = G_USER_ID,
238 last_update_date = sysdate,
239 last_update_login = G_LOGIN_ID
240 where organization_id = p_organization_id;
241
242 end if;
243 -- End loop;
244
245 /* This part is for testing
246 select count(*)
247 into l_row_count
248 from flm_ekb_act_snapshot
249 where organization_id = p_organization_id;
250
251 -- FND_FILE.put_line(FND_FILE.LOG, 'Total rows for this organization are : '||l_row_count);
252 -- dbms_output.put_line('Total rows for this organization are : '||l_row_count);
253 end of testing */
254
255 end;
256
257
258 End flm_ekb_actual_demand;