1 PACKAGE BODY CSP_MIN_MAX_PLANNING AS
2 /*$Header: cspppmmb.pls 120.3 2006/02/02 23:03:41 hhaugeru noship $*/
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 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 g_retcode := 1;
170 End if;
171 End if;
172
173
174 Elsif (Rec.NODE_TYPE = 'ORGANIZATION_WH' and Rec.ORGANIZATION_ID is NOT NULL) THEN
175 l_org_id := Rec.ORGANIZATION_ID;
176 l_level := 1;
177 l_subinv := Null;
178
179 Begin
180 SELECT LOCATION_ID
181 INTO L_LOCATION_ID
182 FROM HR_ORGANIZATION_UNITS
183 WHERE ORGANIZATION_ID = Rec.ORGANIZATION_ID;
184 Exception
185 when no_data_found then
186 L_LOCATION_ID := Null;
187 End;
188
189 If L_LOCATION_ID is null Then
190 Begin
191 select name
192 into l_org_name
193 from hr_all_organization_units
194 where organization_id = rec.organization_id;
195 Exception
196 when no_data_found then
197 l_org_name := Null;
198 End;
199
200 fnd_message.set_name('CSP','CSP_ORG_NO_SHIPTO_LOCATION');
201 fnd_message.set_token('ORG',l_org_name);
202 fnd_msg_pub.add;
203 If fnd_msg_pub.count_msg > 0 Then
204 FOR i IN REVERSE 1..fnd_msg_pub.count_msg
205 Loop
206 fnd_msg_pub.get(p_msg_index => i,
207 p_encoded => 'F',
208 p_data => x_msg_data_temp,
209 p_msg_index_out => l_msg_index_out);
210 x_msg_data := x_msg_data || x_msg_data_temp;
211 End Loop;
212 FND_FILE.put_line(FND_FILE.log,x_msg_data);
213 fnd_msg_pub.delete_msg;
214 g_retcode := 1;
215 End if;
216 End if;
217 End if;
218 retcode := g_retcode;
219
220 If L_LOCATION_ID is not null then
221 l_req_id := FND_REQUEST.SUBMIT_REQUEST
222 ('INV',
223 'INVISMMX',
224 'Min-max planning report',
225 NULL,
226 NULL
227 ,l_org_id,l_level,1,l_subinv,p_selection,p_cat_set_id
228 ,p_catg_struct_id
229 ,p_Catg_lo
230 ,p_catg_hi
231 ,p_item_lo
232 ,p_item_hi
233 ,p_planner_lo
234 ,p_planner_hi
235 ,p_buyer_lo
236 ,p_buyer_hi
237 ,p_sort
238 -- ,null --p_range
239 -- ,null --p_low
240 -- ,null --p_high
241 ,p_d_cutoff
242 ,p_d_cutoff_rel
243 ,p_s_cutoff
244 ,p_s_cutoff_rel
245 ,p_user_id
246 ,p_restock,p_handle_rep_item,L_LOCATION_ID,p_net_unrsv,p_net_rsv
247 ,p_net_wip,p_include_po
248 ,p_include_mo
249 ,p_include_wip,p_include_if
250 ,p_include_nonnet
251 ,p_lot_ctl,p_display_mode,p_show_desc,p_pur_revision,chr(0),
252 '','','',
253 '','','','','','','','','','',
254 '','','','','','','','','','',
255 '','','','','','','','','','',
256 '','','','','','','','','','',
257 '','','','','','','','','','',
258 '','','','','','','','','','');
259 l_lo_request_id := least(l_lo_request_id,l_req_id);
260 l_hi_request_id := greatest(l_hi_request_id,l_req_id);
261 commit;
262 End if;
263 End if;
264 End Loop;
265
266 open c_parent_request;
267 fetch c_parent_request into l_parent_request_id;
268 close c_parent_request;
269
270 loop
271 open c_done;
272 fetch c_done into l_done;
273 if c_done%notfound then
274 exit;
275 end if;
276 close c_done;
277 dbms_lock.sleep(10);
278 end loop;
279 close c_done;
280
281 END RUN_MIN_MAX;
282 END;
[Home] [Help]