1 PACKAGE BODY CSTPLPOP AS
2 /* $Header: CSTLPOPB.pls 120.2 2010/11/29 05:07:44 pbasrani ship $ */
3 FUNCTION po_price(l_org_id NUMBER, l_item_id NUMBER) RETURN NUMBER IS
4 po_price_tmp NUMBER ;
5 max_transaction_date_tmp DATE;
6 transaction_id_tmp NUMBER;
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
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
25 AND mmt.transaction_id=mmt3.transaction_id);
26
27
28 if (max_transaction_date_tmp is null) then
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
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
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;
50
51 SELECT
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;
61 END;
62 END CSTPLPOP;
63
[Home] [Help]