[Home] [Help]
966:
967: -- Delete from Csp_Usage_Histories
968: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_USAGE_HISTORIES';
969:
970: -- Delete from Csp_Supply_Chain
971: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUPPLY_CHAIN';
972:
973:
974:
967: -- Delete from Csp_Usage_Histories
968: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_USAGE_HISTORIES';
969:
970: -- Delete from Csp_Supply_Chain
971: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUPPLY_CHAIN';
972:
973:
974:
975: IF FND_API.to_Boolean( p_commit )
1107:
1108: G_LAST_RUN_DATE := NVL(G_LAST_RUN_DATE,FND_API.G_MISS_DATE);
1109: If G_LAST_RUN_DATE < trunc(sysdate) - 1 Then
1110: l_get_app_info := fnd_installation.get_app_info('CSP',l_status,l_industry, l_oracle_schema);
1111: -- Delete from CSP_SUPPLY_CHAIN
1112: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUPPLY_CHAIN';
1113: -- Standard Start of API savepoint
1114: SAVEPOINT CREATE_USAGE_PVT;
1115: -- Build Supply Chain
1108: G_LAST_RUN_DATE := NVL(G_LAST_RUN_DATE,FND_API.G_MISS_DATE);
1109: If G_LAST_RUN_DATE < trunc(sysdate) - 1 Then
1110: l_get_app_info := fnd_installation.get_app_info('CSP',l_status,l_industry, l_oracle_schema);
1111: -- Delete from CSP_SUPPLY_CHAIN
1112: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUPPLY_CHAIN';
1113: -- Standard Start of API savepoint
1114: SAVEPOINT CREATE_USAGE_PVT;
1115: -- Build Supply Chain
1116: Create_Supply_Chain (
2127: -- API body
2128: --
2129: l_get_app_info := fnd_installation.get_app_info('CSP',l_status,l_industry, l_oracle_schema);
2130: --- Delete from Supply Chain
2131: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUPPLY_CHAIN' ;
2132:
2133: open c_parameters(p_level_id);
2134: Fetch c_parameters into l_node_type;
2135: close c_parameters;
2137: If l_node_type in ('ORGANIZATON_WH','SUBINVENTORY') then
2138: l_level_id := substr(p_level_id,1,instr(p_level_id,'.',-1,1) - 1);
2139: End If;
2140:
2141: INSERT INTO CSP_SUPPLY_CHAIN (
2142: source_type,
2143: source_organization_id,
2144: source_subinventory,
2145: organization_id,
2190: cri.inventory_item_id;
2191:
2192: loop
2193: l_supply_level := l_supply_level + 1;
2194: insert into csp_supply_chain(
2195: source_type,
2196: source_organization_id,
2197: source_subinventory,
2198: organization_id,
2220: from mtl_parameters mp,
2221: mtl_system_items_b msib,
2222: mtl_item_sub_inventories misi,
2223: mtl_secondary_inventories msi,
2224: csp_supply_chain csc
2225: where mp.organization_id = csc.source_organization_id
2226: and misi.organization_id (+) = csc.source_organization_id
2227: and misi.inventory_item_id (+) = csc.inventory_item_id
2228: and misi.secondary_inventory (+) = csc.source_subinventory
2364: Open c_Period_Size;
2365: Fetch c_Period_size into l_period_size;
2366: Close c_Period_size;
2367:
2368: INSERT INTO CSP_SUPPLY_CHAIN (
2369: source_type,
2370: source_organization_id,
2371: source_subinventory,
2372: organization_id,
2432: sysdate;
2433:
2434: loop
2435: l_supply_level := l_supply_level + 1;
2436: insert into csp_supply_chain(
2437: source_type,
2438: source_organization_id,
2439: source_subinventory,
2440: organization_id,
2464: from mtl_parameters mp,
2465: mtl_system_items_b msib,
2466: mtl_item_sub_inventories misi,
2467: mtl_secondary_inventories msi,
2468: csp_supply_chain csc,
2469: mtl_interorg_ship_methods mism
2470: where mp.organization_id = csc.source_organization_id
2471: and misi.organization_id (+) = csc.source_organization_id
2472: and misi.inventory_item_id (+) = csc.inventory_item_id
2675: fnd_global.user_id,
2676: sysdate,
2677: fnd_global.user_id,
2678: sysdate
2679: from csp_supply_chain csc,
2680: csp_usage_histories cuh
2681: where cuh.history_data_type = 1
2682: and cuh.period_start_date > G_LAST_RUN_DATE
2683: and cuh.organization_id = csc.organization_id