[Home] [Help]
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,
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
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,
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
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,
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)
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,
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)
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,
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
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,
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
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
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,
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,