1 PACKAGE BODY CSP_MIN_MAX_PLANNING AS
2 /*$Header: cspppmmb.pls 120.7 2011/07/25 21:13:59 hhaugeru ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSP_MIN_MAX_PLANNING';
5 g_node_level_id Varchar2(2000):= 1;
6
7 PROCEDURE NODE_LEVEL_ID(p_level_id IN VARCHAR2)
8 IS
9 BEGIN
10 g_node_level_id := p_level_id;
11 End;
12
13 FUNCTION NODE_LEVEL_ID return VARCHAR2 is
14 BEGIN
15 return(g_node_level_id);
16 End;
17
18
19 PROCEDURE RUN_MIN_MAX
20 ( errbuf OUT NOCOPY varchar2
21 ,retcode OUT NOCOPY number
22 ,p_org_id IN NUMBER
23 ,P_level_id IN VARCHAR2
24 ,p_level IN NUMBER
25 ,P_SUBINV_ENABLE_FLAG IN NUMBER
26 ,p_subinv IN VARCHAR2
27 ,p_selection IN NUMBER
28 ,p_cat_set_id IN NUMBER
29 ,p_catg_struct_id IN NUMBER
30 ,p_Catg_lo IN VARCHAR2
31 ,p_catg_hi IN VARCHAR2
32 ,p_item_lo IN VARCHAR2
33 ,p_item_hi IN VARCHAR2
34 ,p_planner_lo IN VARCHAR2
35 ,p_planner_hi IN VARCHAR2
36 ,p_buyer_lo IN VARCHAR2
37 ,p_buyer_hi IN VARCHAR2
38 ,p_sort IN VARCHAR2
39 --,p_range IN NUMBER
40 --,p_low IN VARCHAR2
41 --,p_high IN VARCHAR2
42 ,p_d_cutoff IN VARCHAR2
43 ,p_d_cutoff_rel IN NUMBER
44 ,p_s_cutoff IN VARCHAR2
45 ,p_s_cutoff_rel IN NUMBER
46 ,p_user_id IN NUMBER
47 ,p_restock IN NUMBER
48 ,p_handle_rep_item IN NUMBER
49 ,p_dd_loc_id IN NUMBER
50 ,p_net_unrsv IN NUMBER
51 ,p_net_rsv IN NUMBER
52 ,p_net_wip IN NUMBER
53 ,p_include_po IN NUMBER
54 ,p_include_mo IN NUMBER
55 ,p_include_wip IN NUMBER
56 ,p_include_if IN NUMBER
57 ,p_include_nonnet IN NUMBER
58 ,p_lot_ctl IN NUMBER
59 ,p_display_mode IN NUMBER
60 ,p_show_desc IN NUMBER
61 ,p_pur_revision IN NUMBER
62 )IS
63
64 l_req_id NUMBER;
65 l_org_id NUMBER;
66 l_level NUMBER;
67 l_subinv VARCHAR2(2000);
68 l_location_id NUMBER;
69
70 l_org_name Varchar2(2000);
71
72 l_msg_index_out NUMBER;
73 x_msg_data_temp Varchar2(2000);
74 x_msg_data Varchar2(4000);
75 g_retcode number := 0;
76
77 l_lo_request_id number := 0;
78 l_hi_request_id number := 0;
79 l_parent_request_id number := 0;
80 l_done varchar2(1);
81
82 CURSOR PLANNING_NODE_REC IS
83 SELECT NODE_TYPE,ORGANIZATION_ID,SECONDARY_INVENTORY
84 FROM CSP_PLANNING_PARAMETERS
85 WHERE LEVEL_ID LIKE p_level_id||'%'
86 and node_type = 'ORGANIZATION_WH'
87 union all
88 SELECT cpp.NODE_TYPE,cpp.ORGANIZATION_ID,cpp.SECONDARY_INVENTORY
89 FROM CSP_PLANNING_PARAMETERS cpp,
90 mtl_secondary_inventories msi
91 WHERE LEVEL_ID LIKE p_level_id||'%'
92 and node_type = 'SUBINVENTORY'
93 and msi.organization_id = cpp.organization_id
94 and cpp.condition_type = 'G'
95 and msi.secondary_inventory_name = cpp.secondary_inventory
96 and (disable_date is null or trunc(disable_date) > trunc(sysdate));
97 --and status_id = 1; /* Fix for R12 same as 115.10 bug 4960060 */
98
99 Cursor c_parent_request is
100 select max(request_id)
101 from fnd_concurrent_requests fcr,fnd_concurrent_programs fcp
102 where fcr.program_application_id = fcp.application_id
103 and fcr.concurrent_program_id = fcp.concurrent_program_id
104 and fcp.application_id = 523
105 and fcp.concurrent_program_name = 'CSPPLMMX'
106 and fcr.phase_code <> 'C';
107
108
109 cursor c_done is
110 select 'x'
111 from fnd_concurrent_requests fcr,
112 fnd_concurrent_programs fcp
113 where fcr.concurrent_program_id = fcp.concurrent_program_id
114 and fcr.program_application_id = fcp.application_id
115 and fcp.application_id = 401
116 and fcp.concurrent_program_name = 'INVISMMX'
117 and fcr.phase_code <> 'C'
118 and fcr.request_id between l_lo_request_id and l_hi_request_id
119 and fcr.parent_request_id = l_parent_request_id;
120
121
122 BEGIN
123
124 FOR Rec IN PLANNING_NODE_REC LOOP
125 IF (Rec.NODE_TYPE <> 'REGION' AND Rec.ORGANIZATION_ID is NOT NULL) THEN
126
127 IF (Rec.NODE_TYPE = 'SUBINVENTORY' and Rec.SECONDARY_INVENTORY is NOT NULL) THEN
128 l_org_id := Rec.ORGANIZATION_ID;
129 l_level := 2;
130 l_subinv := Rec.SECONDARY_INVENTORY;
131
132 Begin
133 SELECT min(pla.location_id) inv_loc_id
134 into l_location_id
135 from csp_rs_cust_relations rcr,
136 hz_cust_acct_sites cas,
137 hz_cust_site_uses csu,
138 po_location_associations pla,
139 csp_sec_inventories csi
140 where rcr.customer_id = cas.cust_account_id
141 and cas.cust_acct_site_id = csu.cust_acct_site_id
142 and csu.site_use_code = 'SHIP_TO'
143 and csu.site_use_id = pla.site_use_id
144 and rcr.resource_type = csi.owner_resource_type
145 and rcr.resource_id = csi.owner_resource_id
146 and csi.organization_id = rec.organization_id
147 and csi.secondary_inventory_name = rec.secondary_inventory
148 and csu.primary_flag = 'Y';
149 Exception
150 when no_data_found then
151 L_LOCATION_ID := Null;
152 End;
153
154 If L_LOCATION_ID is null Then
155 fnd_message.set_name('CSP','CSP_SUBINV_NO_SHIPTO_LOCATION');
156 fnd_message.set_token('SUBINV',Rec.SECONDARY_INVENTORY);
157 fnd_msg_pub.add;
158 If fnd_msg_pub.count_msg > 0 Then
159 FOR i IN REVERSE 1..fnd_msg_pub.count_msg
160 Loop
161 fnd_msg_pub.get(p_msg_index => i,
162 p_encoded => 'F',
163 p_data => x_msg_data_temp,
164 p_msg_index_out => l_msg_index_out);
165 x_msg_data := x_msg_data || x_msg_data_temp;
166 End Loop;
167 FND_FILE.put_line(FND_FILE.log,x_msg_data);
168 fnd_msg_pub.delete_msg;
169 x_msg_data := null;
170 g_retcode := 1;
171 End if;
172 End if;
173
174
175 Elsif (Rec.NODE_TYPE = 'ORGANIZATION_WH' and Rec.ORGANIZATION_ID is NOT NULL) THEN
176 l_org_id := Rec.ORGANIZATION_ID;
177 l_level := 1;
178 l_subinv := Null;
179
180 Begin
181 SELECT LOCATION_ID
182 INTO L_LOCATION_ID
183 FROM HR_ORGANIZATION_UNITS
184 WHERE ORGANIZATION_ID = Rec.ORGANIZATION_ID;
185 Exception
186 when no_data_found then
187 L_LOCATION_ID := Null;
188 End;
189
190 If L_LOCATION_ID is null Then
191 Begin
192 select name
193 into l_org_name
194 from hr_all_organization_units
195 where organization_id = rec.organization_id;
196 Exception
197 when no_data_found then
198 l_org_name := Null;
199 End;
200
201 fnd_message.set_name('CSP','CSP_ORG_NO_SHIPTO_LOCATION');
202 fnd_message.set_token('ORG',l_org_name);
203 fnd_msg_pub.add;
204 If fnd_msg_pub.count_msg > 0 Then
205 FOR i IN REVERSE 1..fnd_msg_pub.count_msg
206 Loop
207 fnd_msg_pub.get(p_msg_index => i,
208 p_encoded => 'F',
209 p_data => x_msg_data_temp,
210 p_msg_index_out => l_msg_index_out);
211 x_msg_data := x_msg_data || x_msg_data_temp;
212 End Loop;
213 FND_FILE.put_line(FND_FILE.log,x_msg_data);
214 fnd_msg_pub.delete_msg;
215 x_msg_data := null;
216 g_retcode := 1;
217 End if;
218 End if;
219 End if;
220 retcode := g_retcode;
221
222 If L_LOCATION_ID is not null then
223 l_req_id := FND_REQUEST.SUBMIT_REQUEST
224 ('INV',
225 'INVISMMX',
226 'Min-max planning report',
227 NULL,
228 NULL
229 ,l_org_id,l_level,1,l_subinv,p_selection,p_cat_set_id
230 ,p_catg_struct_id
231 ,p_Catg_lo
232 ,p_catg_hi
233 ,p_item_lo
234 ,p_item_hi
235 ,p_planner_lo
236 ,p_planner_hi
237 ,p_buyer_lo
238 ,p_buyer_hi
239 ,p_sort
240 -- ,null --p_range
241 -- ,null --p_low
242 -- ,null --p_high
243 ,p_d_cutoff
244 ,p_d_cutoff_rel
245 ,p_s_cutoff
246 ,p_s_cutoff_rel
247 ,p_user_id
248 ,p_restock,p_handle_rep_item,L_LOCATION_ID,p_net_unrsv,p_net_rsv
249 ,p_net_wip,p_include_po
250 ,p_include_mo
251 ,p_include_wip,p_include_if
252 ,p_include_nonnet
253 ,p_lot_ctl,p_display_mode,p_show_desc,p_pur_revision,chr(0),
254 '','','',
255 '','','','','','','','','','',
256 '','','','','','','','','','',
257 '','','','','','','','','','',
258 '','','','','','','','','','',
259 '','','','','','','','','','',
260 '','','','','','','','','','');
261
262 if l_lo_request_id = 0 then
263 l_lo_request_id := l_req_id;
264 else
265 l_lo_request_id := least(l_lo_request_id,l_req_id);
266 end if;
267 l_hi_request_id := greatest(l_hi_request_id,l_req_id);
268 commit;
269 End if;
270 End if;
271 End Loop;
272
273 open c_parent_request;
274 fetch c_parent_request into l_parent_request_id;
275 close c_parent_request;
276
277 loop
278 open c_done;
279 fetch c_done into l_done;
280 if c_done%notfound then
281 exit;
282 end if;
283 close c_done;
284 dbms_lock.sleep(10);
285 end loop;
286 close c_done;
287
288 END RUN_MIN_MAX;
289 END;