[Home] [Help]
963:
964: -- Delete from Csp_Usage_Histories
965: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_USAGE_HISTORIES';
966:
967: -- Delete from Csp_Supply_Chain
968: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUPPLY_CHAIN';
969:
970:
971:
964: -- Delete from Csp_Usage_Histories
965: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_USAGE_HISTORIES';
966:
967: -- Delete from Csp_Supply_Chain
968: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUPPLY_CHAIN';
969:
970:
971:
972: IF FND_API.to_Boolean( p_commit )
1104:
1105: G_LAST_RUN_DATE := NVL(G_LAST_RUN_DATE,FND_API.G_MISS_DATE);
1106: If G_LAST_RUN_DATE < trunc(sysdate) - 1 Then
1107: l_get_app_info := fnd_installation.get_app_info('CSP',l_status,l_industry, l_oracle_schema);
1108: -- Delete from CSP_SUPPLY_CHAIN
1109: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUPPLY_CHAIN';
1110: -- Standard Start of API savepoint
1111: SAVEPOINT CREATE_USAGE_PVT;
1112: -- Build Supply Chain
1105: G_LAST_RUN_DATE := NVL(G_LAST_RUN_DATE,FND_API.G_MISS_DATE);
1106: If G_LAST_RUN_DATE < trunc(sysdate) - 1 Then
1107: l_get_app_info := fnd_installation.get_app_info('CSP',l_status,l_industry, l_oracle_schema);
1108: -- Delete from CSP_SUPPLY_CHAIN
1109: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUPPLY_CHAIN';
1110: -- Standard Start of API savepoint
1111: SAVEPOINT CREATE_USAGE_PVT;
1112: -- Build Supply Chain
1113: Create_Supply_Chain (
2147: -- API body
2148: --
2149: l_get_app_info := fnd_installation.get_app_info('CSP',l_status,l_industry, l_oracle_schema);
2150: --- Delete from Supply Chain
2151: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUPPLY_CHAIN' ;
2152:
2153: open c_parameters(p_level_id);
2154: Fetch c_parameters into l_node_type;
2155: close c_parameters;
2157: If l_node_type in ('ORGANIZATON_WH','SUBINVENTORY') then
2158: l_level_id := substr(p_level_id,1,instr(p_level_id,'.',-1,1) - 1);
2159: End If;
2160:
2161: INSERT INTO CSP_SUPPLY_CHAIN (
2162: source_type,
2163: source_organization_id,
2164: source_subinventory,
2165: organization_id,
2210: cri.inventory_item_id;
2211:
2212: loop
2213: l_supply_level := l_supply_level + 1;
2214: insert into csp_supply_chain(
2215: source_type,
2216: source_organization_id,
2217: source_subinventory,
2218: organization_id,
2240: from mtl_parameters mp,
2241: mtl_system_items_b msib,
2242: mtl_item_sub_inventories misi,
2243: mtl_secondary_inventories msi,
2244: csp_supply_chain csc
2245: where mp.organization_id = csc.source_organization_id
2246: and misi.organization_id (+) = csc.source_organization_id
2247: and misi.inventory_item_id (+) = csc.inventory_item_id
2248: and misi.secondary_inventory (+) = csc.source_subinventory
2384: Open c_Period_Size;
2385: Fetch c_Period_size into l_period_size;
2386: Close c_Period_size;
2387:
2388: INSERT INTO CSP_SUPPLY_CHAIN (
2389: source_type,
2390: source_organization_id,
2391: source_subinventory,
2392: organization_id,
2452: sysdate;
2453:
2454: loop
2455: l_supply_level := l_supply_level + 1;
2456: insert into csp_supply_chain(
2457: source_type,
2458: source_organization_id,
2459: source_subinventory,
2460: organization_id,
2484: from mtl_parameters mp,
2485: mtl_system_items_b msib,
2486: mtl_item_sub_inventories misi,
2487: mtl_secondary_inventories msi,
2488: csp_supply_chain csc,
2489: mtl_interorg_ship_methods mism
2490: where mp.organization_id = csc.source_organization_id
2491: and misi.organization_id (+) = csc.source_organization_id
2492: and misi.inventory_item_id (+) = csc.inventory_item_id
2695: fnd_global.user_id,
2696: sysdate,
2697: fnd_global.user_id,
2698: sysdate
2699: from csp_supply_chain csc,
2700: csp_usage_histories cuh
2701: where cuh.history_data_type = 1
2702: and cuh.period_start_date > G_LAST_RUN_DATE
2703: and cuh.organization_id = csc.organization_id