APPS.CSTPLPOP dependencies on MTL_MATERIAL_TRANSACTIONS

Line 11: SELECT /*+ INDEX (mmt3 MTL_MATERIAL_TRANSACTIONS_N1) */

7:
8: BEGIN
9: --Added hint for performance Bug # 6819625
10: --Removed INDEX_JOIN hint and added U2 index in Exists for bug 10300541
11: SELECT /*+ INDEX (mmt3 MTL_MATERIAL_TRANSACTIONS_N1) */
12: Max(Trunc(transaction_date)) transaction_date
13: INTO
14: max_transaction_date_tmp
15: FROM

Line 16: mtl_material_transactions mmt3

12: Max(Trunc(transaction_date)) transaction_date
13: INTO
14: max_transaction_date_tmp
15: FROM
16: mtl_material_transactions mmt3
17: WHERE
18: mmt3.organization_id = l_org_id AND
19: mmt3.inventory_item_id = l_item_id AND
20: EXISTS(SELECT /*+ INDEX(mmt MTL_MATERIAL_TRANSACTIONS_U2)*/1 FROM Mtl_material_transactions mmt WHERE

Line 20: EXISTS(SELECT /*+ INDEX(mmt MTL_MATERIAL_TRANSACTIONS_U2)*/1 FROM Mtl_material_transactions mmt WHERE

16: mtl_material_transactions mmt3
17: WHERE
18: mmt3.organization_id = l_org_id AND
19: mmt3.inventory_item_id = l_item_id AND
20: EXISTS(SELECT /*+ INDEX(mmt MTL_MATERIAL_TRANSACTIONS_U2)*/1 FROM Mtl_material_transactions mmt WHERE
21: mmt.transaction_source_type_id = 1 AND
22: --Added for Bug # 6819625 for MTL_MATERIAL_TRANSACTIONS_N15 index usage
23: mmt.transaction_action_id = 27 AND
24: mmt.transaction_type_id = 18

Line 22: --Added for Bug # 6819625 for MTL_MATERIAL_TRANSACTIONS_N15 index usage

18: mmt3.organization_id = l_org_id AND
19: mmt3.inventory_item_id = l_item_id AND
20: EXISTS(SELECT /*+ INDEX(mmt MTL_MATERIAL_TRANSACTIONS_U2)*/1 FROM Mtl_material_transactions mmt WHERE
21: mmt.transaction_source_type_id = 1 AND
22: --Added for Bug # 6819625 for MTL_MATERIAL_TRANSACTIONS_N15 index usage
23: mmt.transaction_action_id = 27 AND
24: mmt.transaction_type_id = 18
25: AND mmt.transaction_id=mmt3.transaction_id);
26:

Line 33: SELECT /*+ INDEX_JOIN (mmt2 MTL_MATERIAL_TRANSACTIONS_N1 MTL_MATERIAL_TRANSACTIONS_N15) */

29: return 0;
30: end if;
31:
32: --Added hint for performance Bug # 6819625
33: SELECT /*+ INDEX_JOIN (mmt2 MTL_MATERIAL_TRANSACTIONS_N1 MTL_MATERIAL_TRANSACTIONS_N15) */
34: To_number(Substr(MAX(To_char(mmt2.creation_date,
35: 'YYYY-MM-DD-HH24-MI-SS:') ||
36: To_char(mmt2.transaction_id)), 21))
37: INTO

Line 40: mtl_material_transactions mmt2

36: To_char(mmt2.transaction_id)), 21))
37: INTO
38: transaction_id_tmp
39: FROM
40: mtl_material_transactions mmt2
41: WHERE
42: mmt2.organization_id = l_org_id AND
43: mmt2.inventory_item_id = l_item_id AND
44: mmt2.transaction_type_id = 18 AND

Line 45: --Added for Bug # 6819625 for MTL_MATERIAL_TRANSACTIONS_N15 index usage

41: WHERE
42: mmt2.organization_id = l_org_id AND
43: mmt2.inventory_item_id = l_item_id AND
44: mmt2.transaction_type_id = 18 AND
45: --Added for Bug # 6819625 for MTL_MATERIAL_TRANSACTIONS_N15 index usage
46: mmt2.transaction_action_id = 27 AND
47: mmt2.transaction_source_type_id = 1 AND
48: mmt2.transaction_date BETWEEN max_transaction_date_tmp
49: AND max_transaction_date_tmp + 1;

Line 56: mtl_material_transactions mmt

52: mmt.transaction_cost
53: INTO
54: po_price_tmp
55: FROM
56: mtl_material_transactions mmt
57: WHERE
58: mmt.transaction_id = transaction_id_tmp;
59:
60: RETURN po_price_tmp;