[Home] [Help]
246: swap_join_inputs(cql) */ mmt.inventory_item_id,
247: mmt.organization_id,
248: cql.layer_id, max(mmt.transaction_id) transaction_id,
249: trunc (mmt.transaction_date) asofdate
250: from mtl_material_transactions mmt,
251: cst_quantity_layers cql,
252: mtl_parameters mp
253: where mp.primary_cost_method <> 1
254: and mp.default_cost_group_id = mmt.cost_group_id
343: to SYSDATE now and the mmt table rows are not updated once inserted.
344: */
345: SELECT Max(TRANSACTION_ID)
346: INTO l_processed_txn_id
347: FROM MTL_MATERIAL_TRANSACTIONS mmt;
348:
349: /* Bug: 4936377
350: Store the max cost_update_id from cec into bis_refresh_log table
351: We are simply picking the max cost update id as the end date is defaulted
358: BIS_COLLECTION_UTILITIES.WRAPUP(
359: p_status => true,
360: p_period_from => l_start_date,
361: p_period_to => l_end_date,
362: p_attribute1 => 'mtl_material_transactions',
363: p_attribute2 => l_processed_txn_id,
364: p_attribute3 => 'cst_elemental_costs',
365: p_attribute4 => l_processed_cost_id
366: );
584: l_last_run_to_date := trunc(fnd_date.displayDT_to_date(BIS_COLLECTION_UTILITIES.get_last_refresh_period('eni_dbi_item_cost_f')));
585: /** Bug: 4956685, 4936377
586: Fetch the last Processed transaction id and cost update id from bis refresh log table.
587: **/
588: SELECT MAX(decode(brl.attribute1,'mtl_material_transactions',attribute2,NULL))
589: ,MAX(decode(brl.attribute3,'cst_elemental_costs' ,attribute4,NULL))
590: INTO l_processed_txn_id, l_processed_cost_id
591: FROM bis_refresh_log brl
592: WHERE brl.object_name = 'eni_dbi_item_cost_f';
592: WHERE brl.object_name = 'eni_dbi_item_cost_f';
593:
594: eni_dbi_util_pkg.log('End Period of last cost collection was ' || to_char(l_last_run_to_date));
595: eni_dbi_util_pkg.log('Incremental cost collection will collect records on and after the above date');
596: eni_dbi_util_pkg.log('Last processed transaction_id from mtl_material_transactions table as stored in bis_refresh_log table is:' || l_processed_txn_id);
597: eni_dbi_util_pkg.log('Last processed cost update_id from cst_elemental_costs table as stored in bis_refresh_log table is:' || l_processed_cost_id);
598:
599: --eni_dbi_util_pkg.log('End Period of last cost collection was ' || to_char(l_last_run_to_date));
600: --eni_dbi_util_pkg.log('Incremental cost collection will collect records on and after the above date');
794: **/
795: IF l_processed_txn_id IS NULL THEN
796: SELECT NVL( MAX( transaction_id), 0)
797: INTO l_processed_txn_id
798: FROM mtl_material_transactions mmt
799: WHERE mmt.transaction_date < l_last_run_to_date;
800: END IF; -- l_processed_txn_id
801:
802: /**
803: Bug: 4956685 We have the last Processed transaction_id from mmt table
804: This modified query will have a different nested query on the mmt table
805: with a predicate on transaction_id instead of last_run_date
806: */
807: eni_dbi_util_pkg.log('Processing mtl_material_transactions.transaction_id greater than ' || l_processed_txn_id);
808:
809: insert /*+ append parallel(a) */ into eni_dbi_item_cost_stg a
810: (effective_date,
811: inventory_item_id, organization_id, item_cost, material_cost,
837: swap_join_inputs(cql) */ mmt.inventory_item_id,
838: mmt.organization_id,
839: cql.layer_id, max(mmt.transaction_id) transaction_id,
840: trunc (mmt.transaction_date) asofdate
841: from mtl_material_transactions mmt,
842: cst_quantity_layers cql,
843: mtl_parameters mp
844: where mp.primary_cost_method <> 1
845: and mp.default_cost_group_id = mmt.cost_group_id
959:
960: --Bug: 4956685 Query the max txn id from mmt table
961: SELECT Max(TRANSACTION_ID)
962: INTO l_processed_txn_id
963: FROM MTL_MATERIAL_TRANSACTIONS;
964:
965: --Bug: 4936377 Query the max cost update id from cec table
966: SELECT Max(COST_UPDATE_ID)
967: INTO l_processed_cost_id
970: BIS_COLLECTION_UTILITIES.WRAPUP(
971: p_status => true,
972: p_period_from => l_last_run_to_date,
973: p_period_to => sysdate,
974: p_attribute1 => 'mtl_material_transactions',
975: p_attribute2 => l_processed_txn_id,
976: p_attribute3 => 'cst_elemental_costs',
977: p_attribute4 => l_processed_cost_id
978: );