[Home] [Help]
PACKAGE BODY: APPS.JTF_OBJECTS_PVT
Source
1 PACKAGE BODY JTF_OBJECTS_PVT AS
2 /* $Header: jtfvobmb.pls 115.4 2004/04/20 11:49:10 abraina ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_OBJECTS_PVT';
5
6 --------------------------------------------------------------------------
7 -- Start of comments
8 -- Procedure : GET_OBJECT_INSTANCE_NAME
9 -- Description : Will determine the Name of the Object Instance based
10 -- on the objects definition in JTF_OBJECTS. This function
11 -- is used in the JTF_OBJECT_MAPPINGS_V.
12 -- Parameters :
13 -- name direction type required?
14 -- ---- --------- ---- ---------
15 -- p_ObjectCode IN VARCHAR2 required
16 -- p_ObjectID IN VARCHAR2 required
17 -- RETURN VARCHAR2
18 --
19 -- Notes :
20 --
21 -- End of comments
22 --------------------------------------------------------------------------
23 FUNCTION GET_OBJECT_INSTANCE_NAME
24 ( p_ObjectCode IN VARCHAR2
25 , p_ObjectID IN VARCHAR2
26 )RETURN VARCHAR2
27 IS
28 CURSOR c_JTFObjectDefinition
29 /*****************************************************************************
30 ** Get the Object definition parameters required to build the query
31 *****************************************************************************/
32 (b_ObjectCode IN VARCHAR2
33 )IS SELECT select_id
34 , select_name
35 , select_details
36 , from_table
37 , where_clause
38 FROM jtf_objects_b
39 WHERE object_code = b_ObjectCode;
40
41 CURSOR c_SelectIDType
42 (b_table_name IN VARCHAR2
43 ,b_column_name IN VARCHAR2
44 ,p_oracle_schema IN VARCHAR2 )IS SELECT DISTINCT data_type
45 FROM all_tab_columns
46 WHERE table_name = b_table_name
47 AND column_name = b_column_name
48 AND owner = p_oracle_schema ;
49
50 l_SelectID VARCHAR2(200);
51 l_SelectName VARCHAR2(2000);
52 l_SelectDetails VARCHAR2(2000);
53 l_FromTable VARCHAR2(200);
54 l_WhereClause VARCHAR2(2000);
55 l_Query VARCHAR2(6400);
56 l_SelectIDType VARCHAR2(106);
57 l_ObjectInstanceName VARCHAR2(80);
58
59 l_return_status BOOLEAN;
60 l_status VARCHAR2(1);
61 l_oracle_schema VARCHAR2(30);
62 l_industry VARCHAR2(1);
63
64 BEGIN
65 /*****************************************************************************
66 ** Get the Object Definition
67 *****************************************************************************/
68 FOR l_JTFObject IN c_JTFObjectDefinition(p_ObjectCode)
69 LOOP
70 l_SelectID := l_JTFObject.select_id;
71 l_SelectName := l_JTFObject.select_name;
72 l_SelectDetails := l_JTFObject.select_details;
73 l_FromTable := l_JTFObject.from_table;
74 l_WhereClause := l_JTFObject.where_clause;
75 END LOOP;
76
77 l_return_status := FND_INSTALLATION.GET_APP_INFO(
78 application_short_name => 'JTF',
79 status => l_status,
80 industry => l_industry,
81 oracle_schema => l_oracle_schema);
82
83 if (NOT l_return_status) or (l_oracle_schema IS NULL)
84 then
85 -- defaulted to the JTF
86 l_oracle_schema := 'JTF';
87 end if;
88
89 /*****************************************************************************
90 ** Get the datatype of the select ID
91 *****************************************************************************/
92 FOR r_SelectIDType IN c_SelectIDType(UPPER(l_FromTable)
93 ,UPPER(l_SelectID)
94 ,l_oracle_schema)
95 LOOP
96 l_SelectIDType := r_SelectIDType.data_type;
97 END LOOP;
98
99 /*****************************************************************************
100 ** Build the query
101 *****************************************************************************/
102 IF (l_SelectIDType = 'NUMBER')
103 THEN
104 l_Query := 'SELECT '||l_SelectName ||
105 ' FROM '||l_FromTable;
106
107 IF (l_WhereClause IS NOT NULL)
108 THEN
109 l_Query := l_Query ||' WHERE '||l_WhereClause ||
110 ' AND '||l_SelectID||' = :1';
111 ELSE
112 l_Query := l_Query||' WHERE ' ||l_SelectID||' = :1';
113 END IF;
114
115 ELSE
116 l_Query := 'SELECT '||l_SelectName ||
117 ' FROM '||l_FromTable;
118
119 IF (l_WhereClause IS NOT NULL)
120 THEN
121 l_Query := l_Query ||' WHERE '||l_WhereClause ||
122 ' AND '||l_SelectID||' = :1';
123 ELSE
124 l_Query := l_Query ||' WHERE ' ||l_SelectID||' = :1';
125 END IF;
126
127 END IF;
128
129 EXECUTE IMMEDIATE l_query
130 INTO l_ObjectInstanceName
131 USING p_ObjectID;
132
133 RETURN l_ObjectInstanceName;
134
135 EXCEPTION
136 WHEN OTHERS
137 THEN RETURN l_query||SQLERRM;
138
139 END GET_OBJECT_INSTANCE_NAME;
140
141 END JTF_OBJECTS_PVT;