[Home] [Help]
PACKAGE BODY: APPS.MSC_DRP_SRC_ALLOC_RULES
Source
1 PACKAGE BODY MSC_DRP_SRC_ALLOC_RULES AS
2 /* $Header: MSCALOCB.pls 120.0 2005/10/26 12:41 rawasthi noship $ */
3 l_debug varchar2(30) := FND_PROFILE.Value('MRP_DEBUG');
4
5
6 /********************************************************
7 PROCEDURE : log_message
8 ********************************************************/
9
10 PROCEDURE log_message( p_user_info IN VARCHAR2) IS
11 BEGIN
12 FND_FILE.PUT_LINE(FND_FILE.LOG, p_user_info);
13 EXCEPTION
14 WHEN OTHERS THEN
15 RAISE;
16 END log_message;
17
18
19 PROCEDURE MISSING_SRC_ALLOC_RULES (
20 errbuf OUT NOCOPY VARCHAR2,
21 retcode OUT NOCOPY VARCHAR2,
22 p_instance_id IN NUMBER,
23 p_assignment_set IN VARCHAR2,
24 p_validation IN NUMBER ) IS
25
26 CURSOR c1 (l_instance_id number, l_assignment_set VARCHAR2 ) IS
27 SELECT
28 distinct
29 MAS.ASSIGNMENT_SET_NAME,
30 MSR.SOURCING_RULE_NAME,
31 MTP1.ORGANIZATION_CODE FROM_ORG,
32 MTP2.ORGANIZATION_CODE TO_ORG,
33 MSI.ITEM_NAME,
34 decode(MSA.ASSIGNMENT_TYPE,3,'Item-Instance',6,'Item-Instance-Organization',9,'Item-Instance-region') ASSIGNMENT_LEVEL
35 FROM
36 MSC_ASSIGNMENT_SETS MAS,
37 MSC_SR_ASSIGNMENTS MSA,
38 MSC_SR_SOURCE_ORG MSSO,
39 MSC_SR_RECEIPT_ORG MSRO,
40 MSC_SOURCING_RULES MSR,
41 MSC_SYSTEM_ITEMS MSI,
42 MSC_TRADING_PARTNERS MTP1,
43 MSC_TRADING_PARTNERS MTP2
44 WHERE MAS.ASSIGNMENT_SET_NAME= l_assignment_set
45 AND MAS.ASSIGNMENT_SET_ID = MSA.ASSIGNMENT_SET_ID
46 AND MSA.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
47 AND MSA.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
48 AND nvl(MSA.ORGANIZATION_ID, MSI.ORGANIZATION_ID) = MSI.ORGANIZATION_ID
49 AND MSI.PLAN_ID = -1
50 AND MSI.DRP_PLANNED = 1
51 AND MSA.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
52 AND MSA.SOURCING_RULE_ID = MSRO.SOURCING_RULE_ID
53 AND MSA.SOURCING_RULE_TYPE =1
54 AND MSA.ASSIGNMENT_TYPE in (3,6,9)
55 AND MSRO.SR_RECEIPT_ID = MSSO.SR_RECEIPT_ID
56 AND MSSO.SOURCE_TYPE=1
57 AND MSSO.SOURCE_ORGANIZATION_ID = MTP1.SR_TP_ID
58 AND MSSO.SR_INSTANCE_ID= MTP1.SR_INSTANCE_ID
59 AND MTP1.PARTNER_TYPE=3
60 AND nvl(MSA.ORGANIZATION_ID, MSI.ORGANIZATION_ID) = MTP2.SR_TP_ID
61 AND MSA.SR_INSTANCE_ID= MTP2.SR_INSTANCE_ID
62 AND MTP2.PARTNER_TYPE=3
63 AND MSA.SR_INSTANCE_ID=l_instance_id
64 and NOT EXISTS (SELECT 1 from msc_sr_assignments msa1 where
65 MSA1.ASSIGNMENT_SET_ID = MSA.ASSIGNMENT_SET_ID
66 AND MSA1.SR_INSTANCE_ID = MSA.SR_INSTANCE_ID
67 AND MSA1.sourcing_rule_type =3
68 AND MSA1.ORGANIZATION_ID = MSSO.SOURCE_ORGANIZATION_ID
69 )
70 UNION ALL
71 SELECT
72 MAS.ASSIGNMENT_SET_NAME,
73 MSR.SOURCING_RULE_NAME,
74 MTP1.ORGANIZATION_CODE FROM_ORG,
75 MTP2.ORGANIZATION_CODE TO_ORG,
76 MSI.ITEM_NAME,
77 decode(MSA.ASSIGNMENT_TYPE,5,'Category-Instance-Org',8,'Category-Instance-Region') ASSIGNMENT_LEVEL
78 FROM
79 MSC_ASSIGNMENT_SETS MAS,
80 MSC_SR_ASSIGNMENTS MSA,
81 MSC_SR_SOURCE_ORG MSSO,
82 MSC_SR_RECEIPT_ORG MSRO,
83 MSC_SOURCING_RULES MSR,
84 MSC_ITEM_CATEGORIES CAT,
85 MSC_SYSTEM_ITEMS MSI,
86 MSC_TRADING_PARTNERS MTP1,
87 MSC_TRADING_PARTNERS MTP2
88 WHERE MAS.ASSIGNMENT_SET_NAME= l_assignment_set
89 AND MAS.ASSIGNMENT_SET_ID = MSA.ASSIGNMENT_SET_ID
90 AND NVL(MSA.ORGANIZATION_ID, CAT.ORGANIZATION_ID)
91 = CAT.ORGANIZATION_ID
92 AND MSA.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
93 AND CAT.CATEGORY_NAME = MSA.CATEGORY_NAME
94 AND CAT.CATEGORY_SET_ID = MSA.CATEGORY_SET_ID
95 AND MSI.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID
96 AND MSI.ORGANIZATION_ID = CAT.ORGANIZATION_ID
97 AND MSI.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
98 AND MSI.PLAN_ID = -1
99 AND MSI.DRP_PLANNED = 1
100 AND MSA.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
101 AND MSA.SOURCING_RULE_ID = MSRO.SOURCING_RULE_ID
102 AND MSA.SOURCING_RULE_TYPE =1
103 AND MSA.ASSIGNMENT_TYPE in (5,8)
104 AND MSRO.SR_RECEIPT_ID = MSSO.SR_RECEIPT_ID
105 AND MSSO.SOURCE_TYPE=1
106 AND MSSO.SOURCE_ORGANIZATION_ID = MTP1.SR_TP_ID
107 AND MSSO.SR_INSTANCE_ID= MTP1.SR_INSTANCE_ID
108 AND MTP1.PARTNER_TYPE=3
109 AND nvl(MSA.ORGANIZATION_ID, CAT.ORGANIZATION_ID) = MTP2.SR_TP_ID
110 AND MSA.SR_INSTANCE_ID= MTP2.SR_INSTANCE_ID
111 AND MTP2.PARTNER_TYPE=3
112 AND MSA.SR_INSTANCE_ID=l_instance_id
113 and NOT EXISTS (SELECT 1 from msc_sr_assignments msa1 where
114 MSA1.ASSIGNMENT_SET_ID = MSA.ASSIGNMENT_SET_ID
115 AND MSA1.SR_INSTANCE_ID = MSA.SR_INSTANCE_ID
116 AND MSA1.sourcing_rule_type =3
117 AND MSA1.ORGANIZATION_ID = MSSO.SOURCE_ORGANIZATION_ID
118 );
119
120
121
122 CURSOR src_rule_cur (l_instance_id number, l_assignment_set VARCHAR2) IS
123 SELECT
124 distinct
125 MAS.ASSIGNMENT_SET_NAME,
126 MSR.SOURCING_RULE_NAME,
127 MTP1.ORGANIZATION_CODE FROM_ORG,
128 MTP2.ORGANIZATION_CODE TO_ORG,
129 MSI.ITEM_NAME,
130 decode(MSA.ASSIGNMENT_TYPE,3,'Item-Instance',6,'Item-Instance-Organization',9,'Item-Instance-region') ASSIGNMENT_LEVEL
131 FROM
132 MSC_ASSIGNMENT_SETS MAS,
133 MSC_SR_ASSIGNMENTS MSA,
134 MSC_SR_SOURCE_ORG MSSO,
135 MSC_SR_RECEIPT_ORG MSRO,
136 MSC_SOURCING_RULES MSR,
137 MSC_SYSTEM_ITEMS MSI,
138 MSC_TRADING_PARTNERS MTP1,
139 MSC_TRADING_PARTNERS MTP2
140 WHERE MAS.ASSIGNMENT_SET_NAME= l_assignment_set
141 AND MAS.ASSIGNMENT_SET_ID = MSA.ASSIGNMENT_SET_ID
142 AND MSA.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
143 AND MSA.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
144 AND nvl(MSA.ORGANIZATION_ID, MSI.ORGANIZATION_ID) = MSI.ORGANIZATION_ID
145 AND MSI.PLAN_ID = -1
146 AND MSI.DRP_PLANNED = 1
147 AND MSA.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
148 AND MSA.SOURCING_RULE_ID = MSRO.SOURCING_RULE_ID
149 AND MSA.SOURCING_RULE_TYPE =1
150 AND MSA.ASSIGNMENT_TYPE in (3,6,9)
151 AND MSRO.SR_RECEIPT_ID = MSSO.SR_RECEIPT_ID
152 AND MSSO.SOURCE_TYPE=1
153 AND MSSO.SOURCE_ORGANIZATION_ID = MTP1.SR_TP_ID
154 AND MSSO.SR_INSTANCE_ID= MTP1.SR_INSTANCE_ID
155 AND MTP1.PARTNER_TYPE=3
156 AND nvl(MSA.ORGANIZATION_ID, MSI.ORGANIZATION_ID) = MTP2.SR_TP_ID
157 AND MSA.SR_INSTANCE_ID= MTP2.SR_INSTANCE_ID
158 AND MTP2.PARTNER_TYPE=3
159 AND MSA.SR_INSTANCE_ID=l_instance_id
160 AND NOT EXISTS (
161 select 1 from MSC_SR_ASSIGNMENTS MSA1, MSC_DRP_ALLOC_RULES MDAR, MSC_DRP_ALLOC_RULE_DATES MDARD,
162 MSC_DRP_ALLOC_RECEIPT_RULES MDARR
163 WHERE MSA1.ASSIGNMENT_SET_ID = MAS.ASSIGNMENT_SET_ID
164 AND MSA1.SOURCING_RULE_TYPE = 3
165 AND (MSA1.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID OR MSA1.INVENTORY_ITEM_ID is null)
166 AND MSA1.ALLOCATION_RULE_ID = MDAR.RULE_ID
167 AND MDAR.RULE_ID = MDARD.RULE_ID
168 AND nvl(MDARD.DISABLE_DATE, SYSDATE) >= SYSDATE
169 AND MDARD.TIME_PHASE_ID = MDARR.TIME_PHASE_ID
170 AND MDARR.SR_INSTANCE_ID = MSA1.SR_INSTANCE_ID
171 AND (MDARR.TO_ORGANIZATION_ID = nvl(MSA.ORGANIZATION_ID, MSI.ORGANIZATION_ID) AND
172 MSA1.ORGANIZATION_ID = MSSO.SOURCE_ORGANIZATION_ID)
173 )
174 UNION ALL
175 SELECT
176 MAS.ASSIGNMENT_SET_NAME,
177 MSR.SOURCING_RULE_NAME,
178 MTP1.ORGANIZATION_CODE FROM_ORG,
179 MTP2.ORGANIZATION_CODE TO_ORG,
180 MSI.ITEM_NAME,
181 decode(MSA.ASSIGNMENT_TYPE,5,'Category-Instance-Org',8,'Category-Instance-Region') ASSIGNMENT_LEVEL
182 FROM
183 MSC_ASSIGNMENT_SETS MAS,
184 MSC_SR_ASSIGNMENTS MSA,
185 MSC_SR_SOURCE_ORG MSSO,
186 MSC_SR_RECEIPT_ORG MSRO,
187 MSC_SOURCING_RULES MSR,
188 MSC_ITEM_CATEGORIES CAT,
189 MSC_SYSTEM_ITEMS MSI,
190 MSC_TRADING_PARTNERS MTP1,
191 MSC_TRADING_PARTNERS MTP2
192 WHERE MAS.ASSIGNMENT_SET_NAME= l_assignment_set
193 AND MAS.ASSIGNMENT_SET_ID = MSA.ASSIGNMENT_SET_ID
194 AND NVL(MSA.ORGANIZATION_ID, CAT.ORGANIZATION_ID)
195 = CAT.ORGANIZATION_ID
196 AND MSA.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
197 AND CAT.CATEGORY_NAME = MSA.CATEGORY_NAME
198 AND CAT.CATEGORY_SET_ID = MSA.CATEGORY_SET_ID
199 AND MSI.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID
200 AND MSI.ORGANIZATION_ID = CAT.ORGANIZATION_ID
201 AND MSI.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
202 AND MSI.PLAN_ID = -1
203 AND MSI.DRP_PLANNED = 1
204 AND MSA.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
205 AND MSA.SOURCING_RULE_ID = MSRO.SOURCING_RULE_ID
206 AND MSA.SOURCING_RULE_TYPE =1
207 AND MSA.ASSIGNMENT_TYPE in (5,8)
208 AND MSRO.SR_RECEIPT_ID = MSSO.SR_RECEIPT_ID
209 AND MSSO.SOURCE_TYPE=1
210 AND MSSO.SOURCE_ORGANIZATION_ID = MTP1.SR_TP_ID
211 AND MSSO.SR_INSTANCE_ID= MTP1.SR_INSTANCE_ID
212 AND MTP1.PARTNER_TYPE=3
213 AND nvl(MSA.ORGANIZATION_ID, CAT.ORGANIZATION_ID) = MTP2.SR_TP_ID
214 AND MSA.SR_INSTANCE_ID= MTP2.SR_INSTANCE_ID
215 AND MTP2.PARTNER_TYPE=3
216 AND MSA.SR_INSTANCE_ID=l_instance_id
217 AND NOT EXISTS (
218 select 1 from MSC_SR_ASSIGNMENTS MSA1, MSC_DRP_ALLOC_RULES MDAR, MSC_DRP_ALLOC_RULE_DATES MDARD,
219 MSC_DRP_ALLOC_RECEIPT_RULES MDARR
220 WHERE MSA1.ASSIGNMENT_SET_ID = MAS.ASSIGNMENT_SET_ID
221 AND MSA1.SOURCING_RULE_TYPE = 3
222 AND (MSA1.CATEGORY_NAME = CAT.CATEGORY_NAME OR MSA1.CATEGORY_NAME is null)
223 AND MSA1.ALLOCATION_RULE_ID = MDAR.RULE_ID
224 AND MDAR.RULE_ID = MDARD.RULE_ID
225 AND nvl(MDARD.DISABLE_DATE, SYSDATE) >= SYSDATE
226 AND MDARD.TIME_PHASE_ID = MDARR.TIME_PHASE_ID
227 AND MDARR.SR_INSTANCE_ID = MSA1.SR_INSTANCE_ID
228 AND (MDARR.TO_ORGANIZATION_ID = nvl(MSA.ORGANIZATION_ID, CAT.ORGANIZATION_ID) AND
229 MSA1.ORGANIZATION_ID = MSSO.SOURCE_ORGANIZATION_ID)
230 )
231 order by ASSIGNMENT_SET_NAME, SOURCING_RULE_NAME ;
232
233
234 CURSOR alloc_rule_cur (l_instance_id number, l_assignment_set VARCHAR2) IS
235 select MAS.ASSIGNMENT_SET_NAME,
236 MDAR.NAME,
237 MTP1.ORGANIZATION_CODE FROM_ORG,
238 MTP2.ORGANIZATION_CODE TO_ORG,
239 MSI.ITEM_NAME,
240 decode(MSA1.ASSIGNMENT_TYPE,6,'Item-Instance-Org') ASSIGNMENT_LEVEL
241 FROM MSC_ASSIGNMENT_SETS MAS,
242 MSC_SR_ASSIGNMENTS MSA1,
243 MSC_DRP_ALLOC_RULES MDAR,
244 MSC_DRP_ALLOC_RULE_DATES MDARD,
245 MSC_SYSTEM_ITEMS MSI,
246 MSC_DRP_ALLOC_RECEIPT_RULES MDARR,
247 MSC_TRADING_PARTNERS MTP1,
248 MSC_TRADING_PARTNERS MTP2
249 WHERE MAS.ASSIGNMENT_SET_NAME= l_assignment_set
250 AND MAS.ASSIGNMENT_SET_ID = MSA1.ASSIGNMENT_SET_ID
251 AND MSA1.SOURCING_RULE_TYPE = 3
252 AND MSA1.ALLOCATION_RULE_ID = MDAR.RULE_ID
253 AND MDAR.RULE_ID = MDARD.RULE_ID
254 AND nvl(MDARD.DISABLE_DATE, SYSDATE) >= SYSDATE
255 AND MDARD.TIME_PHASE_ID = MDARR.TIME_PHASE_ID
256 AND MDARR.SR_INSTANCE_ID = MSA1.SR_INSTANCE_ID
257 AND MSA1.ASSIGNMENT_TYPE = 6
258 AND MSA1.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
259 AND MSA1.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
260 AND MSA1.ORGANIZATION_ID = MSI.ORGANIZATION_ID
261 AND MSI.PLAN_ID=-1
262 AND MSI.DRP_PLANNED = 1
263 AND MSA1.ORGANIZATION_ID = MTP1.SR_TP_ID
264 AND MSA1.SR_INSTANCE_ID= MTP1.SR_INSTANCE_ID
265 AND MTP1.PARTNER_TYPE=3
266 AND MDARR.TO_ORGANIZATION_ID = MTP2.SR_TP_ID
267 AND MDARR.SR_INSTANCE_ID = MTP2.SR_INSTANCE_ID
268 AND MTP2.PARTNER_TYPE=3
269 AND MSA1.ORGANIZATION_ID <> MDARR.TO_ORGANIZATION_ID
270 AND MSA1.SR_INSTANCE_ID= l_instance_id
271 AND NOT EXISTS (SELECT 1 FROM MSC_SR_ASSIGNMENTS MSA, MSC_SR_SOURCE_ORG MSSO,
272 MSC_SR_RECEIPT_ORG MSRO, MSC_SOURCING_RULES MSR
273 WHERE MSA.ASSIGNMENT_SET_ID = MSA1.ASSIGNMENT_SET_ID
274 AND MSA.SR_INSTANCE_ID = MSA1.SR_INSTANCE_ID
275 AND MSA.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
276 AND MSA.SOURCING_RULE_ID = MSRO.SOURCING_RULE_ID
277 AND MSA.SOURCING_RULE_TYPE =1
278 AND MSA.ASSIGNMENT_TYPE in (3,4,6,9)
279 AND MSRO.SR_RECEIPT_ID = MSSO.SR_RECEIPT_ID
280 AND MSSO.SOURCE_TYPE=1
281 AND (MSA.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID OR MSA.INVENTORY_ITEM_ID is null)
282 AND MSA.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
283 AND (MDARR.TO_ORGANIZATION_ID = nvl(MSA.ORGANIZATION_ID, MSI.ORGANIZATION_ID) AND
284 MSA1.ORGANIZATION_ID = MSSO.SOURCE_ORGANIZATION_ID)
285 )
286 UNION ALL
287 select MAS.ASSIGNMENT_SET_NAME,
288 MDAR.NAME,
289 MTP1.ORGANIZATION_CODE FROM_ORG,
290 MTP2.ORGANIZATION_CODE TO_ORG,
291 MSI.ITEM_NAME,
292 decode(MSA1.ASSIGNMENT_TYPE,5,'Category-Instance-Org') ASSIGNMENT_LEVEL
293 FROM MSC_ASSIGNMENT_SETS MAS,
294 MSC_SR_ASSIGNMENTS MSA1,
295 MSC_DRP_ALLOC_RULES MDAR,
296 MSC_DRP_ALLOC_RULE_DATES MDARD,
297 MSC_SYSTEM_ITEMS MSI,
298 MSC_ITEM_CATEGORIES CAT,
299 MSC_DRP_ALLOC_RECEIPT_RULES MDARR,
300 MSC_TRADING_PARTNERS MTP1,
301 MSC_TRADING_PARTNERS MTP2
302 WHERE MAS.ASSIGNMENT_SET_NAME= l_assignment_set
303 AND MAS.ASSIGNMENT_SET_ID = MSA1.ASSIGNMENT_SET_ID
304 AND MSA1.SOURCING_RULE_TYPE = 3
305 AND MSA1.ALLOCATION_RULE_ID = MDAR.RULE_ID
306 AND MDAR.RULE_ID = MDARD.RULE_ID
307 AND nvl(MDARD.DISABLE_DATE, SYSDATE) >= SYSDATE
308 AND MDARD.TIME_PHASE_ID = MDARR.TIME_PHASE_ID
309 AND MDARR.SR_INSTANCE_ID = MSA1.SR_INSTANCE_ID
310 AND MSA1.ASSIGNMENT_TYPE = 5
311 AND MSA1.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
312 AND CAT.CATEGORY_NAME = MSA1.CATEGORY_NAME
313 AND CAT.CATEGORY_SET_ID = MSA1.CATEGORY_SET_ID
314 AND CAT.ORGANIZATION_ID = MSA1.ORGANIZATION_ID
315 AND MSI.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID
316 AND MSI.ORGANIZATION_ID = CAT.ORGANIZATION_ID
317 AND MSI.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
318 AND MSI.PLAN_ID=-1
319 AND MSI.DRP_PLANNED = 1
320 AND MSA1.ORGANIZATION_ID = MTP1.SR_TP_ID
321 AND MSA1.SR_INSTANCE_ID= MTP1.SR_INSTANCE_ID
322 AND MTP1.PARTNER_TYPE=3
323 AND MDARR.TO_ORGANIZATION_ID = MTP2.SR_TP_ID
324 AND MDARR.SR_INSTANCE_ID = MTP2.SR_INSTANCE_ID
325 AND MTP2.PARTNER_TYPE=3
326 AND MSA1.ORGANIZATION_ID <> MDARR.TO_ORGANIZATION_ID
327 AND MSA1.SR_INSTANCE_ID= l_instance_id
328 AND NOT EXISTS (SELECT 1 FROM MSC_SR_ASSIGNMENTS MSA, MSC_SR_SOURCE_ORG MSSO,
329 MSC_SR_RECEIPT_ORG MSRO, MSC_SOURCING_RULES MSR
330 WHERE MSA.ASSIGNMENT_SET_ID = MSA1.ASSIGNMENT_SET_ID
331 AND MSA.SR_INSTANCE_ID = MSA1.SR_INSTANCE_ID
332 AND MSA.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
333 AND MSA.SOURCING_RULE_ID = MSRO.SOURCING_RULE_ID
334 AND MSA.SOURCING_RULE_TYPE =1
335 AND MSA.ASSIGNMENT_TYPE in (5,8)
336 AND MSRO.SR_RECEIPT_ID = MSSO.SR_RECEIPT_ID
337 AND MSSO.SOURCE_TYPE=1
338 AND MSA.CATEGORY_NAME = MSA1.CATEGORY_NAME
339 AND MSA.CATEGORY_SET_ID = MSA1.CATEGORY_SET_ID
340 AND (MDARR.TO_ORGANIZATION_ID = nvl(MSA.ORGANIZATION_ID, CAT.ORGANIZATION_ID) AND
341 MSA1.ORGANIZATION_ID = MSSO.SOURCE_ORGANIZATION_ID)
342 )
343 order by ASSIGNMENT_SET_NAME, NAME ;
344
345
346 CURSOR src_org_cur (l_instance_id number, l_assignment_set VARCHAR2) IS
347 select mas.assignment_set_name,
348 mdar.name,
349 mtp.organization_code FROM_ORG,
350 decode(msa.assignment_type, 4, 'Instance-Org', 5, 'Category-Instance-Org', 6, 'Item-Instance-Org') ASSIGNMENT_LEVEL
351 FROM msc_assignment_sets mas,
352 msc_sr_assignments msa,
353 msc_drp_alloc_rules mdar,
354 msc_trading_partners mtp,
355 msc_drp_alloc_rule_dates mdard
356 where mas.ASSIGNMENT_SET_NAME= l_assignment_set
357 and mas.assignment_set_id=msa.assignment_set_id
358 and msa.allocation_rule_id = mdar.rule_id
359 and msa.organization_id = mtp.sr_tp_id
360 and msa.sr_instance_id= mtp.sr_instance_id
361 and msa.sourcing_rule_type =3
362 and mtp.partner_type=3
363 and msa.sr_instance_id = l_instance_id
364 and mdard.rule_id = mdar.rule_id
365 and nvl(mdard.disable_date, sysdate) >= sysdate
366 and msa.organization_id not in (select to_organization_id from msc_drp_alloc_receipt_rules mdarr
367 where mdarr.time_phase_id = mdard.time_phase_id
368 and mdarr.sr_instance_id = msa.sr_instance_id
369 );
370
371
372 CURSOR c2 (l_instance_id number, l_assignment_set VARCHAR2) IS
373 select mas.assignment_set_name,
374 mdar.name,
375 mtp.organization_code FROM_ORG,
376 mtp2.organization_code TO_ORG,
377 msi.item_name
378 FROM msc_assignment_sets mas,
379 msc_sr_assignments msa,
380 msc_drp_alloc_rules mdar,
381 msc_trading_partners mtp,
382 msc_trading_partners mtp2,
383 msc_system_items msi,
384 msc_drp_alloc_rule_dates mdard,
385 msc_drp_alloc_receipt_rules mdarr
386 where mas.ASSIGNMENT_SET_NAME= l_assignment_set
387 and mas.assignment_set_id=msa.assignment_set_id
388 and msa.allocation_rule_id = mdar.rule_id
389 and msa.organization_id = mtp.sr_tp_id
390 and msa.sr_instance_id= mtp.sr_instance_id
391 and msa.sourcing_rule_type =3
392 and mtp.partner_type=3
396 and msa.organization_id = msi.organization_id
393 and msa.assignment_type=6
394 and msa.inventory_item_id = msi.inventory_item_id
395 and msa.sr_instance_id=msi.sr_instance_id
397 and msi.plan_id=-1
398 and msi.drp_planned=1
399 and msa.sr_instance_id = l_instance_id
400 and mdard.rule_id = mdar.rule_id
401 and nvl(mdard.disable_date, sysdate) >= sysdate
402 and mdard.dmd_pri_override=1
403 and mdarr.time_phase_id = mdard.time_phase_id
404 and mdarr.sr_instance_id = msa.sr_instance_id
405 and mdarr.dmd_priority is null
406 and mdarr.to_organization_id = mtp2.sr_tp_id
407 and mdarr.sr_instance_id = mtp2.sr_instance_id
408 and mtp2.partner_type=3
409 UNION ALL
410 select mas.assignment_set_name,
411 mdar.name,
412 mtp.organization_code FROM_ORG,
413 mtp2.organization_code TO_ORG,
414 msi.item_name
415 FROM msc_assignment_sets mas,
416 msc_sr_assignments msa,
417 msc_drp_alloc_rules mdar,
418 msc_trading_partners mtp,
419 msc_trading_partners mtp2,
420 MSC_ITEM_CATEGORIES CAT,
421 msc_system_items msi,
422 msc_drp_alloc_rule_dates mdard,
423 msc_drp_alloc_receipt_rules mdarr
424 where mas.ASSIGNMENT_SET_NAME= l_assignment_set
425 and mas.assignment_set_id=msa.assignment_set_id
426 and msa.allocation_rule_id = mdar.rule_id
427 and msa.organization_id = mtp.sr_tp_id
428 and msa.sr_instance_id= mtp.sr_instance_id
429 and msa.sourcing_rule_type =3
430 and mtp.partner_type=3
431 and msa.assignment_type=5
432 and msa.category_name = cat.category_name
433 and msa.category_set_id = cat.category_set_id
434 and msa.organization_id = cat.organization_id
435 and msa.sr_instance_id = cat.sr_instance_id
436 and msi.inventory_item_id = cat.inventory_item_id
437 and msi.organization_id = cat.organization_id
438 and msi.sr_instance_id = cat.sr_instance_id
439 and msi.plan_id=-1
440 and msi.drp_planned=1
441 and msa.sr_instance_id = l_instance_id
442 and mdard.rule_id = mdar.rule_id
443 and nvl(mdard.disable_date, sysdate) >= sysdate
444 and mdard.dmd_pri_override=1
445 and mdarr.time_phase_id = mdard.time_phase_id
446 and mdarr.sr_instance_id = msa.sr_instance_id
447 and mdarr.dmd_priority is null
448 and mdarr.to_organization_id = mtp2.sr_tp_id
449 and mdarr.sr_instance_id = mtp2.sr_instance_id
450 and mtp2.partner_type=3;
451
452 l_org_exists number;
453
454 BEGIN
455
456 retcode := 0;
457 l_org_exists := 0;
458
459 IF p_validation = 1 THEN
460 FND_MESSAGE.SET_NAME('MSC', 'MSC_ALLOC_RULE_NOT_FOUND');
461 LOG_MESSAGE(FND_MESSAGE.GET);
462 log_message(' ');
463
464 log_message('Assignment Set Name Sourcing Rule From org To Org Item Assignment Level');
465 log_message('-------------------- -------------- ---------- -------- ------------ -----------------');
466
467 FOR c1_rec in c1 (p_instance_id, p_assignment_set) LOOP
468
469 BEGIN
470 log_message(rpad(c1_rec.ASSIGNMENT_SET_NAME,25,' ')||
471 rpad(c1_rec.SOURCING_RULE_NAME,20,' ')||
472 rpad(c1_rec.FROM_ORG,11,' ')||
473 rpad(c1_rec.TO_ORG,11,' ')||
474 rpad(c1_rec.ITEM_NAME,20,' ')||
475 rpad(c1_rec.ASSIGNMENT_LEVEL,27,' ')
476 );
477 l_org_exists :=1 ;
478
479 EXCEPTION
480 WHEN NO_DATA_FOUND THEN
481 NULL;
482 WHEN OTHERS THEN
483 LOG_MESSAGE('Error in Concurrent program while looking orgs for Sourcing Rules');
484 LOG_MESSAGE(SQLERRM);
485 retcode := 2;
486 END;
487 END LOOP;
488
489 END IF;
490
491
492 IF p_validation = 2 THEN
493 FND_MESSAGE.SET_NAME('MSC', 'MSC_SRC_ORG_NOT_FOUND');
494 LOG_MESSAGE(FND_MESSAGE.GET);
495 log_message(' ');
496
497 log_message('Assignment Set Name Sourcing Rule From org To Org Item Assignment Level');
498 log_message('-------------------- -------------- ---------- -------- ------------ -----------------');
499
500 FOR src_rule in src_rule_cur (p_instance_id, p_assignment_set) LOOP
501
502 BEGIN
503 log_message(rpad(src_rule.ASSIGNMENT_SET_NAME,25,' ')||
504 rpad(src_rule.SOURCING_RULE_NAME,20,' ')||
505 rpad(src_rule.FROM_ORG,11,' ')||
506 rpad(src_rule.TO_ORG,11,' ')||
507 rpad(src_rule.ITEM_NAME,20,' ')||
508 rpad(src_rule.ASSIGNMENT_LEVEL,27,' ')
509 );
510 l_org_exists :=1 ;
511
512 EXCEPTION
513 WHEN NO_DATA_FOUND THEN
514 NULL;
515 WHEN OTHERS THEN
516 LOG_MESSAGE('Error in Concurrent program while looking orgs for Sourcing Rules');
517 LOG_MESSAGE(SQLERRM);
518 retcode := 2;
519 END;
520 END LOOP;
521 END IF;
522
523
524 IF p_validation = 3 THEN
525 FND_MESSAGE.SET_NAME('MSC', 'MSC_ALLOC_ORG_NOT_FOUND');
526 LOG_MESSAGE(FND_MESSAGE.GET);
527 log_message(' ');
528
532 FOR alloc_rule in alloc_rule_cur (p_instance_id, p_assignment_set) LOOP
529 log_message('Assignment Set Name Allocation Rule From org To Org Item Assignment Level');
530 log_message('-------------------- ---------------- --------- -------- ------------- ------------------');
531
533
534 BEGIN
535 log_message(rpad(alloc_rule.ASSIGNMENT_SET_NAME,25,' ')||
536 rpad(alloc_rule.NAME,20,' ')||
537 rpad(alloc_rule.FROM_ORG,11,' ')||
538 rpad(alloc_rule.TO_ORG,11,' ')||
539 rpad(alloc_rule.ITEM_NAME,20,' ')||
540 rpad(alloc_rule.ASSIGNMENT_LEVEL,27,' ')
541 );
542 l_org_exists :=1 ;
543
544 EXCEPTION
545 WHEN NO_DATA_FOUND THEN
546 NULL;
547 WHEN OTHERS THEN
548 LOG_MESSAGE('Error in Concurrent program while looking orgs for Allocation Rules');
549 LOG_MESSAGE(SQLERRM);
550 retcode := 2;
551 END;
552 END LOOP;
553 END IF;
554
555 IF p_validation = 4 THEN
556 FND_MESSAGE.SET_NAME('MSC', 'MSC_SOURCE_ORG_NOT_FOUND');
557 LOG_MESSAGE(FND_MESSAGE.GET);
558 log_message(' ');
559
560 log_message('Assignment Set Name Allocation Rule From org Assignment Level');
561 log_message('-------------------- ---------------- ---------- ----------------');
562
563 FOR src_org in src_org_cur (p_instance_id, p_assignment_set) LOOP
564
565 BEGIN
566 log_message(rpad(src_org.ASSIGNMENT_SET_NAME,25,' ')||
567 rpad(src_org.NAME,20,' ')||
568 rpad(src_org.FROM_ORG,15,' ')||
569 rpad(src_org.ASSIGNMENT_LEVEL,27,' ')
570 );
571 l_org_exists :=1 ;
572
573 EXCEPTION
574 WHEN NO_DATA_FOUND THEN
575 NULL;
576 WHEN OTHERS THEN
577 LOG_MESSAGE('Error in Concurrent program while looking orgs for Allocation Rules');
578 LOG_MESSAGE(SQLERRM);
579 retcode := 2;
580 END;
581 END LOOP;
582 END IF;
583
584 IF p_validation = 5 THEN
585 FND_MESSAGE.SET_NAME('MSC', 'MSC_MISSING_DMD_PRIORITY');
586 LOG_MESSAGE(FND_MESSAGE.GET);
587 log_message(' ');
588
589 log_message('Assignment Set Name Allocation Rule From org To Org Item');
590 log_message('-------------------- ---------------- ---------- ------- ---------');
591
592 FOR c2_rec in c2 (p_instance_id, p_assignment_set) LOOP
593
594 BEGIN
595 log_message(rpad(c2_rec.ASSIGNMENT_SET_NAME,25,' ')||
596 rpad(c2_rec.NAME,20,' ')||
597 rpad(c2_rec.FROM_ORG,15,' ')||
598 rpad(c2_rec.TO_ORG,15,' ')||
599 rpad(c2_rec.ITEM_NAME,27,' ')
600 );
601 l_org_exists :=1 ;
602
603 EXCEPTION
604 WHEN NO_DATA_FOUND THEN
605 NULL;
606 WHEN OTHERS THEN
607 LOG_MESSAGE('Error in Concurrent program while looking orgs for Allocation Rules');
608 LOG_MESSAGE(SQLERRM);
609 retcode := 2;
610 END;
611 END LOOP;
612 END IF;
613
614 IF l_org_exists =1 THEN
615 retcode := 1;
616 END IF;
617
618 EXCEPTION
619 WHEN OTHERS THEN
620 LOG_MESSAGE('Error in Concurrent program');
621 LOG_MESSAGE(SQLERRM);
622 retcode := 2;
623
624 END MISSING_SRC_ALLOC_RULES;
625
626 END MSC_DRP_SRC_ALLOC_RULES;
627