PACKAGE BODY: APPS.CSTPLPOP

Source

1 PACKAGE BODY CSTPLPOP AS
2 /* $Header: CSTLPOPB.pls 120.1 2008/02/14 12:03:53 smsasidh 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       SELECT /*+ INDEX_JOIN (mmt3 MTL_MATERIAL_TRANSACTIONS_N1 MTL_MATERIAL_TRANSACTIONS_N15) */
11       Max(Trunc(transaction_date)) transaction_date
12       INTO
13       max_transaction_date_tmp
14       FROM
15       mtl_material_transactions mmt3
16       WHERE
17       mmt3.organization_id = l_org_id AND
18       mmt3.inventory_item_id = l_item_id AND
19       mmt3.transaction_source_type_id = 1 AND
20       --Added for Bug # 6819625 for MTL_MATERIAL_TRANSACTIONS_N15 index usage
21       mmt3.transaction_action_id = 27 AND
22       mmt3.transaction_type_id = 18;
23 
24       if (max_transaction_date_tmp is null) then
25 	return 0;
26       end if;
27 
28       --Added hint for performance Bug # 6819625
29       SELECT /*+ INDEX_JOIN (mmt2 MTL_MATERIAL_TRANSACTIONS_N1 MTL_MATERIAL_TRANSACTIONS_N15) */
30       To_number(Substr(MAX(To_char(mmt2.creation_date,
31       'YYYY-MM-DD-HH24-MI-SS:') ||
32       To_char(mmt2.transaction_id)), 21))
33       INTO
34       transaction_id_tmp
35       FROM
36       mtl_material_transactions mmt2
37       WHERE
38       mmt2.organization_id = l_org_id AND
39       mmt2.inventory_item_id = l_item_id AND
40       mmt2.transaction_type_id = 18 AND
41       --Added for Bug # 6819625 for MTL_MATERIAL_TRANSACTIONS_N15 index usage
42       mmt2.transaction_action_id = 27 AND
43       mmt2.transaction_source_type_id = 1 AND
44       mmt2.transaction_date BETWEEN max_transaction_date_tmp
45       AND max_transaction_date_tmp + 1;
46 
47       SELECT
48       mmt.transaction_cost
49       INTO
50       po_price_tmp
51       FROM
52       mtl_material_transactions mmt
53       WHERE
54       mmt.transaction_id = transaction_id_tmp;
55 
56       RETURN po_price_tmp;
57     END;
58 END CSTPLPOP;
59