1 PACKAGE BODY OKE_AUTHORING_UTILS AS
2 /* $Header: OKEAUTLB.pls 115.0 2004/05/14 20:11:43 who noship $ */
3
4
5
6 FUNCTION COLUMN_EXISTS
7 ( p_object_code VARCHAR2
8 , p_column_name VARCHAR2
9 ) RETURN BOOLEAN IS
10 l_view_name varchar2(200);
11 l_found NUMBER;
12 i NUMBER;
13
14 Cursor l_jtfv_csr Is
15 SELECT from_table
16 FROM jtf_objects_vl
17 WHERE object_code = p_object_code
18 AND sysdate between nvl(start_date_active , sysdate-1)
19 and nvl(end_date_active , sysdate+1);
20
21 Cursor l_jtf_source_csr Is
22 SELECT 1 FROM USER_TAB_COLUMNS
23 WHERE table_name = l_view_name
24 AND column_name = p_column_name;
25
26 BEGIN
27 open l_jtfv_csr;
28 fetch l_jtfv_csr into l_view_name;
29 close l_jtfv_csr;
30
31 -- Trim any space and character after that
32 i := INSTR(l_view_name,' ');
33 If (i > 0) Then
34 l_view_name := substr(l_view_name,1,i - 1);
35 End If;
36
37 open l_jtf_source_csr;
38 fetch l_jtf_source_csr into l_found;
39 close l_jtf_source_csr;
40 If (l_found = 1) Then
41 return TRUE;
42 Else
43 return FALSE;
44 End If;
45 EXCEPTION
46 when NO_DATA_FOUND Then
47 If (l_jtfv_csr%ISOPEN) Then
48 close l_jtfv_csr;
49 End If;
50 If (l_jtf_source_csr%ISOPEN) Then
51 close l_jtf_source_csr;
52 End If;
53 return FALSE;
54
55 when OTHERS then
56 If (l_jtfv_csr%ISOPEN) Then
57 close l_jtfv_csr;
58 End If;
59 If (l_jtf_source_csr%ISOPEN) Then
60 close l_jtf_source_csr;
61 End If;
62 return FALSE;
63 END;
64
65 /** this part of the code attempts to retrieve party_id from
66 the parent party role **/
67
68 FUNCTION Retrieve_Party_ID (P_jtot_object_code IN VARCHAR2,
69 P_object_id1 IN VARCHAR2,
70 P_object_id2 IN VARCHAR2) return VARCHAR2
71 IS
72
73 i NUMBER;
74 l_sql_stmt VARCHAR2(1000);
75 v_cursorID INTEGER;
76 v_party_id VARCHAR2(100) := '00';
77 v_dummy INTEGER;
78
79 BEGIN
80
81
82 l_sql_stmt := OKC_UTIL.GET_SQL_FROM_JTFV(P_jtot_object_code);
83
84 IF l_sql_stmt is null THEN
85 return '-1';
86 END IF;
87
88 IF (column_exists(p_jtot_object_code,'PARTY_ID')) THEN
89
90 i := INSTR(l_sql_stmt,'WHERE');
91 If (i > 0) Then
92 l_sql_stmt := SUBSTR(l_sql_stmt,1, i + 5) ||
93 ' ID1 = ' ||''''|| P_OBJECT_ID1 ||''''|| ' AND ' ||
94 ' ID2 = ' ||''''|| P_OBJECT_ID2 ||'''';
95 -- || ' AND ' ||
96 -- SUBSTR(l_sql_stmt,i + 5);
97 Else
98 -- no where clause. Add before ORDER BY if any
99 i := INSTR(l_sql_stmt,'ORDER BY');
100 If (i > 0) Then
101 l_sql_stmt := SUBSTR(l_sql_stmt,1,i-1) ||
102 ' WHERE ID1 = ' ||''''|| P_OBJECT_ID1 ||''''|| ' AND '||
103 ' ID2 = ' ||''''|| P_OBJECT_ID2 ||''''||
104 ' ' || SUBSTR(l_sql_stmt,i);
105 Else
106 -- no where and no order by
107 l_sql_stmt := l_sql_stmt || ' WHERE ID1 = '||''''|| P_OBJECT_ID1 ||''''
108 || ' AND '|| ' ID2 = '||'''' || P_OBJECT_ID2 ||'''';
109 End If;
110 End If;
111
112 END IF;
113
114 l_sql_stmt := 'SELECT PARTY_ID FROM '|| l_sql_stmt;
115
116 v_cursorID := DBMS_SQL.OPEN_CURSOR;
117 dbms_output.put_line(l_sql_stmt);
118 DBMS_SQL.PARSE(v_cursorID,l_sql_stmt,dbms_sql.native);
119 DBMS_SQL.DEFINE_COLUMN(v_cursorID,1,v_party_id,100);
120 v_dummy := DBMS_SQL.EXECUTE(v_cursorID);
121 IF DBMS_SQL.FETCH_ROWS(v_cursorID)= 0 THEN
122 RETURN '-1';
123 END IF;
124 DBMS_SQL.COLUMN_VALUE(v_cursorID,1,v_party_id);
125 DBMS_SQL.CLOSE_CURSOR(v_cursorID);
126
127 return v_party_id;
128
129 EXCEPTION
130 WHEN OTHERS THEN
131 RETURN '-1';
132
133 END;
134
135
136 END OKE_AUTHORING_UTILS;
[Home] [Help]