1 PACKAGE EAM_ASSET_SEARCH_PVT AUTHID CURRENT_USER as
2 /* $Header: EAMVASES.pls 115.5 2002/11/20 19:02:40 aan ship $ */
3
4
5 TYPE VARCHAR2_REC is record
6 (
7 descr_flex_context_code VARCHAR2(30),
8 end_user_column_name VARCHAR2(30),
9 operator VARCHAR2(10),
10 attribute_value VARCHAR2(150)
11 );
12 TYPE NUMBER_REC is record
13 (
14 descr_flex_context_code VARCHAR2(30),
15 end_user_column_name VARCHAR2(30),
16 operator VARCHAR2(10),
17 attribute_value NUMBER
18 );
19 TYPE DATE_REC is record
20 (
21 descr_flex_context_code VARCHAR2(30),
22 end_user_column_name VARCHAR2(30),
23 operator VARCHAR2(10),
24 attribute_value DATE
25 );
26
27 TYPE VARCHAR2_TBL_TYPE IS TABLE OF VARCHAR2_REC
28 INDEX BY BINARY_INTEGER;
29
30 TYPE NUMBER_TBL_TYPE IS TABLE OF NUMBER_REC
31 INDEX BY BINARY_INTEGER;
32
33 TYPE DATE_TBL_TYPE IS TABLE OF DATE_REC
34 INDEX BY BINARY_INTEGER;
35
36 -- Start of comments
37 -- API name : BUILD_SEARCH_SQL
38 -- Type : Private
39 -- Function :
40 -- Pre-reqs : None.
41 -- Parameters :
42 -- IN p_api_version IN NUMBER Required
43 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
44 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
45 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
46 --
47 -- p_application_id IN NUMBER Optional Default = 401 (INV)
48 -- p_descr_flexfield_name IN VARCHAR2 Opt Default = 'MTL_EAM_ASSET_ATTR_VALUES'
49 -- p_search_set_id IN NUMBER
50 -- p_where_clause IN VARCHAR2
51 -- p_purge_option IN VARCHAR2 Optional Default = FND_API.G_FALSE
52
53 -- OUT x_return_status OUT VARCHAR2(1)
54 -- x_msg_count OUT NUMBER
55 -- x_msg_data OUT VARCHAR2(2000)
56 --
57 -- x_sql_stmt OUT VARCHAR2
58
59
60
61 -- Version Initial version 1.0
62 --
63 -- Notes : This API Build the dynamic SQL to retrieve the Asset Numbers based on
64 -- extensible attributes criteria as identified by the search_set_id in
65 -- mtl_eam_asset_search_temp table.
66 --
67 -- End of comments
68
69
70 PROCEDURE BUILD_SEARCH_SQL
71 (
72 p_api_version IN NUMBER,
73 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
74 p_commit IN VARCHAR2 := fnd_api.g_false,
75 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
76 p_application_id IN NUMBER := 401,
77 p_descr_flexfield_name IN VARCHAR2 := 'MTL_EAM_ASSET_ATTR_VALUES',
78 p_search_set_id IN NUMBER,
79 p_where_clause IN VARCHAR2 := NULL,
80 p_purge_option IN VARCHAR2 := fnd_api.g_false,
81 x_sql_stmt OUT NOCOPY VARCHAR2,
82 x_return_status OUT NOCOPY VARCHAR2,
83 x_msg_count OUT NOCOPY NUMBER,
84 x_msg_data OUT NOCOPY VARCHAR2
85 );
86
87
88
89 -- Start of comments
90 -- API name :
91 -- Type : Private
92 -- Function : GET_ATTRIBUTE_COLUMN_NAME
93 -- Pre-reqs : None.
94 -- Parameters :
95 -- IN
96 -- p_application_id IN NUMBER Optional Default = 401 (INV)
97 -- p_descr_flexfield_name IN VARCHAR2 Opt Default = 'MTL_EAM_ASSET_ATTR_VALUES'
98 -- p_descr_flex_context_code IN VARCHAR2 Required
99 -- p_end_user_column_name IN VARCHAR2 Required
100 --
101 -- RETURNS VARCHAR2
102
103 --
104 -- Notes : This function returns the column name where a specific attribute value
105 -- is stored in table MTL_EAM_ASSET_ATTR_VALUES based on flexfield metadata
106 --
107 -- End of comments
108
109 FUNCTION GET_ATTRIBUTE_COLUMN_NAME
110 (
111 p_application_id IN NUMBER := 401,
112 p_descr_flexfield_name IN VARCHAR2 := 'MTL_EAM_ASSET_ATTR_VALUES',
113 p_descr_flex_context_code IN VARCHAR2,
114 p_end_user_column_name IN VARCHAR2
115 )
116 RETURN VARCHAR2;
117
118
119 END EAM_ASSET_SEARCH_PVT;
120