eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MSC_SATP_FUNC dependencies on MSC_REGIONS

Line 78: msc_regions_temp mrt

74: SELECT intransit_time,
75: ((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
76: INTO l_intransit_time, l_level
77: FROM mtl_interorg_ship_methods mism,
78: msc_regions_temp mrt
79: WHERE mism.from_location_id = p_from_location_id
80: AND mism.ship_method = p_ship_method
81: AND mism.to_region_id = mrt.region_id
82: AND mrt.session_id = p_session_id

Line 121: msc_regions_temp mrt

117: SELECT ship_method,
118: ((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
119: INTO l_ship_method, l_level
120: FROM mtl_interorg_ship_methods mism,
121: msc_regions_temp mrt
122: WHERE mism.from_location_id = p_from_location_id
123: AND mism.to_region_id = mrt.region_id
124: AND mrt.session_id = p_session_id
125: AND mrt.partner_site_id = p_partner_site_id

Line 187: msc_regions_temp mrt

183: SELECT intransit_time,
184: ((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
185: INTO l_intransit_time, l_level
186: FROM mtl_interorg_ship_methods mism,
187: msc_regions_temp mrt
188: WHERE mism.from_location_id = p_from_location_id
189: AND mism.default_flag = 1
190: AND mism.to_region_id = mrt.region_id
191: AND mrt.session_id = p_session_id

Line 1633: -- and store in MSC_REGIONS_TEMP table.

1629: RETURN order_sch_wb.debug_session_id;
1630: END Get_Session_id;
1631:
1632: -- savirine, Aug 29, 2001: created the procedure get_regions. This would be called to get region information
1633: -- and store in MSC_REGIONS_TEMP table.
1634:
1635: -- savirine, Sep 5, 2001: added parameters p_session_id and p_dblink and changed the region info selection
1636: -- to dynamic sql so that if the ATP request is coming from the source and both ERP and APS
1637: -- instances are different ( if the p_dblink is not null it means both

Line 1875: l_stmt:= 'INSERT into msc_regions_temp' || l_dynstring ||

1871: BEGIN
1872: -- Modified the SQL for bug 2484964. For better performance
1873: -- avoid the sub-query.
1874: -- also update Partner_type for supplier intransit LT project
1875: l_stmt:= 'INSERT into msc_regions_temp' || l_dynstring ||
1876: ' (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
1877: -- SELECT :p_session_id, commented for performance tuning bug 2484964
1878: (SELECT DISTINCT :p_session_id,
1879: :p_customer_site_id,

Line 1901: l_stmt:= 'INSERT into msc_regions_temp' || l_dynstring ||

1897:
1898: -- also update Partner_type for supplier intransit LT project
1899: -- partner_type is also included in the where clause
1900: --2814895, changed l_customer site_id to l_partner_site_id
1901: l_stmt:= 'INSERT into msc_regions_temp' || l_dynstring ||
1902: ' (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
1903: SELECT :p_session_id,
1904: :l_partner_site_id,
1905: a.region_id,

Line 1915: FROM msc_regions_temp' || l_dynstring || ' c

1911: AND a.region_type = 10
1912: AND a.zone_level IS NOT NULL
1913: AND b.region_id IN (
1914: SELECT c.region_id
1915: FROM msc_regions_temp' || l_dynstring || ' c
1916: WHERE c.session_id = :p_session_id1
1917: AND c.partner_site_id = :p_partner_site_id1
1918: AND c.partner_type = :partner_type1)';
1919:

Line 1981: FROM MSC_REGIONS

1977:
1978: FOR i in REVERSE 0..3 LOOP
1979: BEGIN
1980: l_stmt := 'SELECT region_id
1981: FROM MSC_REGIONS
1982: WHERE sr_instance_id = :p_instance_id';
1983:
1984: -- Bug 3010834: Backport bug 2882331 to 11.5.9 to be included in I.1
1985: IF l_cnt <= 3 THEN

Line 2093: INSERT INTO msc_regions_temp

2089:
2090: -- Bug 2837366 : krajan : Catch the DUP_VAL_ON_INDEX error
2091: -- also update Partner_type for supplier intransit LT project
2092: BEGIN
2093: INSERT INTO msc_regions_temp
2094: (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2095: -- Begin Bug 2498174
2096: -- Changed Query to enhance performance
2097: SELECT DISTINCT p_session_id,

Line 2103: FROM MSC_REGIONS

2099: region_id,
2100: region_type,
2101: 'N',
2102: l_customer_type -- For supplier intransit LT project
2103: FROM MSC_REGIONS
2104: WHERE sr_instance_id = p_instance_id
2105: START WITH region_id = l_region_id
2106: CONNECT BY PRIOR parent_region_id = region_id;
2107: -- Removed Subquery for performance Bug 2498174

Line 2117: INSERT INTO msc_regions_temp

2113:
2114: -- Begin Bug 2498174
2115: -- Ensure that regions and zones query has instance_id filter
2116: -- Changed Query to enhance performance
2117: INSERT INTO msc_regions_temp
2118: (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2119: SELECT p_session_id,
2120: p_customer_site_id,
2121: a.region_id,

Line 2125: FROM MSC_REGIONS_TEMP c, MSC_ZONE_REGIONS b, MSC_REGIONS a

2121: a.region_id,
2122: a.zone_level,
2123: 'Y',
2124: l_customer_type -- For supplier intransit LT project
2125: FROM MSC_REGIONS_TEMP c, MSC_ZONE_REGIONS b, MSC_REGIONS a
2126: WHERE a.region_id = b.parent_region_id
2127: AND c.region_id = b.region_id
2128: AND a.sr_instance_id = b.sr_instance_id
2129: AND b.sr_instance_id = p_instance_id

Line 2246: l_stmt := ' INSERT INTO msc_regions_temp' || l_dynstring ||

2242: /* bug 3425497: First insert into table locally and then transfer over dblink
2243: IF p_dblink IS NOT NULL THEN
2244:
2245: -- also update Partner_type for supplier intransit LT project
2246: l_stmt := ' INSERT INTO msc_regions_temp' || l_dynstring ||
2247: ' (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2248: SELECT :p_session_id,
2249: :p_customer_site_id1,
2250: region_id,

Line 2275: INSERT INTO msc_regions_temp

2271:
2272: ELSE -- bug 2974334. Change the SQL into static if dbink is null.
2273:
2274: -- also update Partner_type for supplier intransit LT project
2275: INSERT INTO msc_regions_temp
2276: (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2277: SELECT p_session_id, p_customer_site_id, region_id, region_type, l_NO, l_customer_type
2278: FROM WSH_REGION_LOCATIONS
2279: WHERE location_id IN

Line 2296: INSERT INTO msc_regions_temp

2292:
2293: END IF;
2294: */
2295:
2296: INSERT INTO msc_regions_temp
2297: (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2298: SELECT p_session_id, p_customer_site_id, wrl.region_id, wrl.region_type, l_NO, l_partner_type --2814895
2299: FROM WSH_REGION_LOCATIONS WRL,
2300: HZ_CUST_SITE_USES_ALL SITE_USES_ALL,

Line 2320: INSERT INTO msc_regions_temp

2316: ELSIF (NVL(p_party_site_id, -1) <> -1) THEN --2814895, only adding it if nvl(p_calling_module,-99) <> 724
2317:
2318: l_partner_type := l_party_type;
2319:
2320: INSERT INTO msc_regions_temp
2321: (session_id,partner_site_id,region_id,region_type,zone_flag, partner_type) --2814895
2322: SELECT p_session_id,p_party_site_id,wrl.region_id,wrl.region_type,l_NO,l_partner_type
2323: FROM WSH_REGION_LOCATIONS WRL,
2324: HZ_PARTY_SITES PARTY_SITE

Line 2345: l_stmt := 'INSERT INTO msc_regions_temp' || l_dynstring ||

2341: /* 3425497: first insert locally.
2342: IF p_dblink IS NOT NULL THEN
2343:
2344: -- also update Partner_type for supplier intransit LT project
2345: l_stmt := 'INSERT INTO msc_regions_temp' || l_dynstring ||
2346: ' (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2347: SELECT :p_session_id,
2348: -1,
2349: region_id,

Line 2368: INSERT INTO msc_regions_temp

2364:
2365: ELSE
2366:
2367: -- also update Partner_type for supplier intransit LT project
2368: INSERT INTO msc_regions_temp
2369: (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2370: SELECT p_session_id, -1, region_id, region_type, l_NO, l_customer_type
2371: FROM WSH_REGION_LOCATIONS
2372: WHERE location_id = p_location_id

Line 2379: INSERT INTO msc_regions_temp

2375:
2376: END IF; -- bug 2974334. Change the SQL into static if dbink is null
2377: */
2378: -- also update Partner_type for supplier intransit LT project
2379: INSERT INTO msc_regions_temp
2380: (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2381: SELECT p_session_id, -1, region_id, region_type, l_NO, l_customer_type --2814895
2382: FROM WSH_REGION_LOCATIONS
2383: WHERE location_id = p_location_id

Line 2401: l_stmt:= 'INSERT into msc_regions_temp' || l_dynstring ||

2397: IF p_dblink IS NOT NULL THEN
2398:
2399: -- also update Partner_type for supplier intransit LT project
2400: -- partner_type is also included in the where clause
2401: l_stmt:= 'INSERT into msc_regions_temp' || l_dynstring ||
2402: ' (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2403: SELECT DISTINCT :p_session_id,
2404: :p_customer_site_id,
2405: a.region_id,

Line 2410: MSC_REGIONS_TEMP' || l_dynstring || ' c

2406: a.zone_level,
2407: :l_YES,
2408: :partner_type
2409: FROM WSH_REGIONS a, WSH_ZONE_REGIONS b,
2410: MSC_REGIONS_TEMP' || l_dynstring || ' c
2411: WHERE a.region_id = b.parent_region_id
2412: AND a.region_type = 10
2413: AND a.zone_level IS NOT NULL
2414: AND b.region_id = c.region_id

Line 2432: INSERT into msc_regions_temp

2428: ELSE
2429:
2430: -- also update Partner_type for supplier intransit LT project
2431: -- partner_type is also included in the where clause
2432: INSERT into msc_regions_temp
2433: (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2434: SELECT DISTINCT p_session_id,
2435: p_customer_site_id,
2436: a.region_id,

Line 2440: FROM WSH_REGIONS a, WSH_ZONE_REGIONS b, MSC_REGIONS_TEMP c

2436: a.region_id,
2437: a.zone_level,
2438: l_YES,
2439: l_customer_type
2440: FROM WSH_REGIONS a, WSH_ZONE_REGIONS b, MSC_REGIONS_TEMP c
2441: WHERE a.region_id = b.parent_region_id
2442: AND a.region_type = 10
2443: AND a.zone_level IS NOT NULL
2444: AND b.region_id = c.region_id

Line 2455: INSERT into msc_regions_temp

2451: */
2452:
2453: -- also update Partner_type for supplier intransit LT project
2454: -- partner_type is also included in the where clause
2455: INSERT into msc_regions_temp
2456: (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2457: SELECT DISTINCT p_session_id,
2458: p_customer_site_id,
2459: a.region_id,

Line 2463: FROM WSH_REGIONS a, WSH_ZONE_REGIONS b, MSC_REGIONS_TEMP c

2459: a.region_id,
2460: a.zone_level,
2461: l_YES,
2462: l_customer_type
2463: FROM WSH_REGIONS a, WSH_ZONE_REGIONS b, MSC_REGIONS_TEMP c
2464: WHERE a.region_id = b.parent_region_id
2465: AND a.region_type = 10
2466: AND a.zone_level IS NOT NULL
2467: AND b.region_id = c.region_id

Line 2479: l_stmt:= 'INSERT into msc_regions_temp' || l_dynstring ||

2475: /*bug3520746 do not push the data across dblink
2476: --bug 3425497: Now if dblink is not null then transfer the data across dblink
2477: IF p_dblink IS NOT NULL THEN
2478: --first insert data across dblink
2479: l_stmt:= 'INSERT into msc_regions_temp' || l_dynstring ||
2480: ' (SESSION_ID, PARTNER_SITE_ID, REGION_ID, REGION_TYPE, ZONE_FLAG, PARTNER_TYPE)
2481: select SESSION_ID, PARTNER_SITE_ID, REGION_ID, REGION_TYPE, ZONE_FLAG, PARTNER_TYPE
2482: from msc_regions_temp
2483: where session_id = :p_session_id';

Line 2482: from msc_regions_temp

2478: --first insert data across dblink
2479: l_stmt:= 'INSERT into msc_regions_temp' || l_dynstring ||
2480: ' (SESSION_ID, PARTNER_SITE_ID, REGION_ID, REGION_TYPE, ZONE_FLAG, PARTNER_TYPE)
2481: select SESSION_ID, PARTNER_SITE_ID, REGION_ID, REGION_TYPE, ZONE_FLAG, PARTNER_TYPE
2482: from msc_regions_temp
2483: where session_id = :p_session_id';
2484: IF PG_DEBUG in ('Y', 'C') THEN
2485: msc_sch_wb.atp_debug('l_stmt:= ' || l_stmt);
2486: END IF;

Line 2494: delete msc_regions_temp where session_id = p_session_id;

2490: msc_sch_wb.atp_debug('Row Count := ' || SQL%ROWCOUNT);
2491: END IF;
2492:
2493: --now delete the data locally
2494: delete msc_regions_temp where session_id = p_session_id;
2495: END IF;
2496: */
2497: EXCEPTION
2498: WHEN DUP_VAL_ON_INDEX THEN

Line 2510: insert into msc_regions_temp

2506:
2507: BEGIN
2508: /* Replace IN clause with = join
2509: -- also update Partner_type for supplier intransit LT project
2510: insert into msc_regions_temp
2511: (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2512: select p_session_id,
2513: p_customer_site_id,
2514: region_id,

Line 2532: insert into msc_regions_temp

2528: and location_source = 'HZ';
2529:
2530: */
2531:
2532: insert into msc_regions_temp
2533: (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2534: select p_session_id,
2535: p_customer_site_id,
2536: mrl.region_id,

Line 2556: INSERT INTO msc_regions_temp

2552:
2553: -- Insert Zones data
2554: -- also update Partner_type for supplier intransit LT project
2555: -- partner_type is also included in the where clause
2556: INSERT INTO msc_regions_temp
2557: (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2558: SELECT DISTINCT p_session_id,
2559: p_customer_site_id,
2560: a.region_id,

Line 2564: FROM MSC_REGIONS a, MSC_ZONE_REGIONS b

2560: a.region_id,
2561: a.zone_level,
2562: 'Y',
2563: l_customer_type
2564: FROM MSC_REGIONS a, MSC_ZONE_REGIONS b
2565: WHERE a.region_id = b.parent_region_id
2566: AND a.region_type = 10
2567: AND a.zone_level IS NOT NULL
2568: AND a.sr_instance_id = b.sr_instance_id

Line 2572: FROM msc_regions_temp c

2568: AND a.sr_instance_id = b.sr_instance_id
2569: and b.sr_instance_id = p_instance_id
2570: AND b.region_id IN (
2571: SELECT c.region_id
2572: FROM msc_regions_temp c
2573: WHERE c.session_id = p_session_id
2574: AND c.partner_site_id = p_customer_site_id
2575: AND c.partner_type = l_customer_type -- For supplier intransit LT project
2576: );

Line 2579: INSERT INTO msc_regions_temp

2575: AND c.partner_type = l_customer_type -- For supplier intransit LT project
2576: );
2577: */
2578:
2579: INSERT INTO msc_regions_temp
2580: (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2581: SELECT DISTINCT p_session_id,
2582: p_customer_site_id,
2583: a.region_id,

Line 2587: FROM MSC_REGIONS a, MSC_ZONE_REGIONS b, msc_regions_temp c

2583: a.region_id,
2584: a.zone_level,
2585: 'Y',
2586: l_customer_type
2587: FROM MSC_REGIONS a, MSC_ZONE_REGIONS b, msc_regions_temp c
2588: WHERE a.region_id = b.parent_region_id
2589: AND a.region_type = 10
2590: AND a.zone_level IS NOT NULL
2591: AND a.sr_instance_id = b.sr_instance_id

Line 2614: insert into msc_regions_temp

2610: ELSE -- IF nvl(p_customer_site_id,-1) <> -1 THEN -- For supplier intransit LT project
2611:
2612: BEGIN
2613: -- Populating region data for supplier site
2614: insert into msc_regions_temp
2615: (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2616: select p_session_id,
2617: p_supplier_site_id,
2618: region_id,

Line 2661: delete msc_regions_temp where session_id = p_session_id;

2657: END IF;
2658: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2659:
2660: --bug 3425497: delete data from local table
2661: delete msc_regions_temp where session_id = p_session_id;
2662: return;
2663: END IF;
2664: END Get_Regions_Shipping;
2665:

Line 2687: msc_regions_temp mrt

2683: IS
2684: SELECT intransit_time,
2685: ((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
2686: FROM mtl_interorg_ship_methods mism,
2687: msc_regions_temp mrt
2688: WHERE mism.from_location_id = p_from_loc_id
2689: AND mism.ship_method = x_ship_method
2690: AND mism.to_region_id = mrt.region_id
2691: AND mrt.session_id = p_session_id

Line 2702: msc_regions_temp mrt

2698: IS
2699: SELECT ship_method, intransit_time,
2700: ((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
2701: FROM mtl_interorg_ship_methods mism,
2702: msc_regions_temp mrt
2703: WHERE mism.from_location_id = p_from_loc_id
2704: AND mism.default_flag = 1
2705: AND mism.to_region_id = mrt.region_id
2706: AND mrt.session_id = p_session_id