[Home] [Help]
PACKAGE BODY: APPS.JTF_OBJECTS_PVT
Source
1 PACKAGE BODY JTF_OBJECTS_PVT AS
2 /* $Header: jtfvobmb.pls 120.1.12020000.2 2012/11/30 13:25:26 aditysin 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 col, user_synonyms syn
46 WHERE syn.synonym_name = b_table_name
47 AND col.owner = syn.table_owner
48 AND col.table_name = syn.table_name
49 AND col.column_name = b_column_name;
50
51 l_SelectID VARCHAR2(200);
52 l_SelectName VARCHAR2(2000);
53 l_SelectDetails VARCHAR2(2000);
54 l_FromTable VARCHAR2(200);
55 l_WhereClause VARCHAR2(2000);
56 l_Query VARCHAR2(6400);
57 l_SelectIDType VARCHAR2(106);
58 l_ObjectInstanceName VARCHAR2(80);
59
60 l_return_status BOOLEAN;
61 l_status VARCHAR2(1);
62 l_oracle_schema VARCHAR2(30);
63 l_industry VARCHAR2(1);
64
65 BEGIN
66 /*****************************************************************************
67 ** Get the Object Definition
68 *****************************************************************************/
69 FOR l_JTFObject IN c_JTFObjectDefinition(p_ObjectCode)
70 LOOP
71 l_SelectID := l_JTFObject.select_id;
72 l_SelectName := l_JTFObject.select_name;
73 l_SelectDetails := l_JTFObject.select_details;
74 l_FromTable := l_JTFObject.from_table;
75 l_WhereClause := l_JTFObject.where_clause;
76 END LOOP;
77
78 l_return_status := FND_INSTALLATION.GET_APP_INFO(
79 application_short_name => 'JTF',
80 status => l_status,
81 industry => l_industry,
82 oracle_schema => l_oracle_schema);
83
84 if (NOT l_return_status) or (l_oracle_schema IS NULL)
85 then
86 -- defaulted to the JTF
87 l_oracle_schema := 'JTF';
88 end if;
89
90 /*****************************************************************************
91 ** Get the datatype of the select ID
92 *****************************************************************************/
93 FOR r_SelectIDType IN c_SelectIDType(UPPER(l_FromTable)
94 ,UPPER(l_SelectID)
95 ,l_oracle_schema)
96 LOOP
97 l_SelectIDType := r_SelectIDType.data_type;
98 END LOOP;
99
100 /*****************************************************************************
101 ** Build the query
102 *****************************************************************************/
103 IF (l_SelectIDType = 'NUMBER')
104 THEN
105 l_Query := 'SELECT '||l_SelectName ||
106 ' FROM '||l_FromTable;
107
108 IF (l_WhereClause IS NOT NULL)
109 THEN
110 l_Query := l_Query ||' WHERE '||l_WhereClause ||
111 ' AND '||l_SelectID||' = :1';
112 ELSE
113 l_Query := l_Query||' WHERE ' ||l_SelectID||' = :1';
114 END IF;
115
116 ELSE
117 l_Query := 'SELECT '||l_SelectName ||
118 ' FROM '||l_FromTable;
119
120 IF (l_WhereClause IS NOT NULL)
121 THEN
122 l_Query := l_Query ||' WHERE '||l_WhereClause ||
123 ' AND '||l_SelectID||' = :1';
124 ELSE
125 l_Query := l_Query ||' WHERE ' ||l_SelectID||' = :1';
126 END IF;
127
128 END IF;
129
130 EXECUTE IMMEDIATE l_query
131 INTO l_ObjectInstanceName
132 USING p_ObjectID;
133
134 RETURN l_ObjectInstanceName;
135
136 EXCEPTION
137 WHEN OTHERS
138 THEN RETURN l_query||SQLERRM;
139
140 END GET_OBJECT_INSTANCE_NAME;
141
142 END JTF_OBJECTS_PVT;