[Home] [Help]
1909: where i.item_name = ln.item_name
1910: UNION
1911: select msi.inventory_item_id
1912: from msc_system_items msi,
1913: msc_trading_partners part,
1914: msc_trading_partner_maps map,
1915: msc_company_sites cs,
1916: msc_companies c
1917: where msi.plan_id = -1 and
1947: ln.item_name IS NULL
1948: UNION
1949: select mis.inventory_item_id
1950: from msc_item_suppliers mis,
1951: msc_trading_partners mtp,
1952: msc_trading_partner_maps map,
1953: msc_trading_partner_maps map1,
1954: msc_trading_partner_maps map2,
1955: msc_company_relationships r,
2030: ln.line_id = t_line_id(j) and
2031: NVL(ln.row_status, G_PROCESS) = G_PROCESS and
2032: not exists ( select msi.inventory_item_id
2033: from msc_system_items msi,
2034: msc_trading_partners part,
2035: msc_trading_partner_maps map,
2036: msc_company_sites cs,
2037: msc_companies c
2038: where msi.plan_id = -1 and
2054: UPPER(c.company_name) = upper(ln.publisher_company)
2055: /* For Consumption Advice, Item should be valid in the Org modelled as the customer (publisher) */
2056: union select 1
2057: from msc_system_items msi,
2058: msc_trading_partners part,
2059: msc_trading_partner_maps map,
2060: msc_company_sites cs,
2061: msc_companies c
2062: where
2095: flv.language = p_language)) AND
2096: exists (SELECT 'exists'
2097: FROM msc_companies c0,
2098: msc_company_sites s0,
2099: msc_trading_partners mtp,
2100: msc_trading_partners mtp1,
2101: msc_trading_partner_maps maps,
2102: msc_trading_partner_maps maps1,
2103: msc_trading_partner_maps maps2,
2096: exists (SELECT 'exists'
2097: FROM msc_companies c0,
2098: msc_company_sites s0,
2099: msc_trading_partners mtp,
2100: msc_trading_partners mtp1,
2101: msc_trading_partner_maps maps,
2102: msc_trading_partner_maps maps1,
2103: msc_trading_partner_maps maps2,
2104: msc_trading_partner_sites mtps,
2141: exists( select 'exist'
2142: from msc_companies c,
2143: msc_company_sites s,
2144: msc_trading_partner_maps m,
2145: msc_trading_partners t
2146: where upper(c.company_name) = upper(ln.publisher_company)
2147: and c.company_id = s.company_id
2148: and upper(s.company_site_name) = upper(ln.publisher_site)
2149: and m.company_key = s.company_site_id
2446: NVL(ln.row_status, G_PROCESS) = G_SUCCESS
2447: UNION
2448: select msi.inventory_item_id
2449: from msc_system_items msi,
2450: msc_trading_partners part,
2451: msc_trading_partner_maps map,
2452: msc_company_sites cs,
2453: msc_companies c,
2454: msc_supdem_lines_interface ln
7387: INTO t_cust(j),
7388: t_cust_id(j),
7389: t_cust_site(j),
7390: t_cust_site_id(j)
7391: FROM msc_trading_partners mtp,
7392: msc_trading_partners mtp1,
7393: msc_trading_partner_maps maps,
7394: msc_trading_partner_maps maps1,
7395: msc_trading_partner_maps maps2,
7388: t_cust_id(j),
7389: t_cust_site(j),
7390: t_cust_site_id(j)
7391: FROM msc_trading_partners mtp,
7392: msc_trading_partners mtp1,
7393: msc_trading_partner_maps maps,
7394: msc_trading_partner_maps maps1,
7395: msc_trading_partner_maps maps2,
7396: msc_trading_partner_sites mtps,
7773: msc_items itm1,
7774: msc_trading_partner_maps map,
7775: msc_trading_partner_maps map1,
7776: msc_trading_partner_maps map2,
7777: msc_trading_partners mtp,
7778: msc_company_relationships r
7779: where itm.inventory_item_id (+)= msi.base_item_id and
7780: msi.organization_id = mis.organization_id and
7781: msi.sr_instance_id = mis.sr_instance_id and
7818: msc_items itm,
7819: msc_trading_partner_maps map,
7820: msc_trading_partner_maps map1,
7821: msc_trading_partner_maps map2,
7822: msc_trading_partners mtp,
7823: msc_company_relationships r
7824: where mis.inventory_item_id = p_item_id and
7825: mis.plan_id = -1 and
7826: mis.organization_id = mtp.sr_tp_id and
7850: p_item_id IN NUMBER
7851: ) IS
7852: SELECT mis.vmi_flag,
7853: mis.enable_vmi_auto_replenish_flag
7854: FROM msc_trading_partners mtp,
7855: msc_trading_partner_sites mtps,
7856: msc_trading_partners mtp1,
7857: msc_item_suppliers mis
7858: WHERE Upper(mtp.partner_name) = Upper(p_supplier)
7852: SELECT mis.vmi_flag,
7853: mis.enable_vmi_auto_replenish_flag
7854: FROM msc_trading_partners mtp,
7855: msc_trading_partner_sites mtps,
7856: msc_trading_partners mtp1,
7857: msc_item_suppliers mis
7858: WHERE Upper(mtp.partner_name) = Upper(p_supplier)
7859: AND mtp.partner_type = 1
7860: AND mtps.partner_id = mtp.partner_id
8494: l_base_item_name,
8495: l_planner_code--Bug 4424426
8496: from msc_system_items msi,
8497: msc_items itm,
8498: msc_trading_partners part,
8499: msc_trading_partner_maps map
8500: where msi.inventory_item_id = t_item_id(j) and
8501: msi.organization_id = part.sr_tp_id and
8502: msi.sr_instance_id = part.sr_instance_id and
8569: l_base_item_id,
8570: l_base_item_name
8571: from msc_system_items msi,
8572: msc_items itm,
8573: msc_trading_partners part,
8574: msc_trading_partner_maps map
8575: where itm.inventory_item_id (+)=
8576: msi.base_item_id and
8577: msi.inventory_item_id = t_item_id(j) and
8618: into l_item_name,
8619: l_desc,
8620: l_uom
8621: from msc_system_items msi,
8622: msc_trading_partners part,
8623: msc_trading_partner_maps map
8624: where msi.plan_id = -1 and
8625: msi.inventory_item_id = t_item_id(j) and
8626: msi.organization_id = part.sr_tp_id and
8679: into l_item_name,
8680: l_desc,
8681: l_uom
8682: from msc_system_items msi,
8683: msc_trading_partners part,
8684: msc_trading_partner_maps map
8685: where msi.plan_id = -1 and
8686: msi.inventory_item_id = t_item_id(j) and
8687: msi.organization_id = part.sr_tp_id and
10018:
10019: UNION ALL
10020: SELECT msi.inventory_item_id
10021: FROM msc_system_items msi,
10022: msc_trading_partners mtp,
10023: msc_trading_partner_maps mtpm,
10024: msc_company_sites mcs,
10025: msc_companies mc
10026: WHERE msi.plan_id = -1 AND
10135: mssn.line_id = t_line_id(j) AND
10136: NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
10137: NOT EXISTS (SELECT msi.serial_number_control_code
10138: FROM msc_system_items msi,
10139: msc_trading_partners mtp,
10140: msc_trading_partner_maps mtpm
10141: WHERE msi.plan_id = -1 AND
10142: msi.inventory_item_id = mssn.inventory_item_id AND
10143: msi.sr_instance_id = mtp.sr_instance_id AND