1 PACKAGE BODY okc_kexp_pvt AS
2 /* $Header: OKCRKEXB.pls 120.0 2005/05/25 23:03:56 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 PROCEDURE load_ksrch_rows
7 ( p_ksearch_where_clause IN VARCHAR2,
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_report_id OUT NOCOPY NUMBER )
10 IS
11 -- Collection table definition
12 TYPE okc_kexp_report_tbl_type IS
13 TABLE OF ROWID INDEX BY BINARY_INTEGER;
14 okc_kexp_pls_tbl okc_kexp_report_tbl_type;
15
16 -- Ref Cursor to concatenate with the where clause from KSEARCH form.
17 TYPE okc_kexp_cur_type IS REF CURSOR;
18 okc_kexp_cur okc_kexp_cur_type;
19
20 l_block_size NUMBER := 1000;
21 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
22 l_qry VARCHAR2(2000);
23 l_index NUMBER;
24 l_rowid ROWID;
25 l_report_seq NUMBER := 0;
26
27 BEGIN
28
29 -- -------------------------------------------------------------------
30 -- Assign Sequence number for this search criteria based call.
31 -- -------------------------------------------------------------------
32 SELECT okc_kexp_report_s1.NEXTVAL
33 INTO l_report_seq
34 FROM dual;
35
36 -- --------------------------------------------------------------------
37 -- Cursor to populate Temp_Table with Contract Header Row_Ids.
38 -- Concatenates the ksearch Where-Clause of the Contract Search form.
39 -- These Row_Ids are the source for the Full-view
40 -- The Full-view is registered with one of the Discoverer Folders.
41 -- The where-clause default value is set in the package specification.
42 -- --------------------------------------------------------------------
43 l_qry := 'SELECT row_id '||
44 'FROM okc_k_headers_v CHRV '||
45 'WHERE '||
46 p_ksearch_where_clause; --> Input Parameter
47
48 -- -----------------------------------------------------------------
49 -- Contract Search Cursor loop begin...
50 -- -----------------------------------------------------------------
51 l_index := 1;
52 OPEN okc_kexp_cur FOR l_qry;
53 LOOP
54 -- ---------------------------------------------------------------
55 -- Load the Temp Table with the current Ksearch where clause into
56 -- a local PL/SQL table via loop Fetch, As Bulk-Fetch is not
57 -- working with the Ref-Cursor defined above.
58 -- ------------------------------------------------------------
59 FETCH okc_kexp_cur INTO l_rowid;
60
61 EXIT WHEN okc_kexp_cur%NOTFOUND;
62
63 okc_kexp_pls_tbl(l_index) := l_rowid;
64
65 l_index := l_index + 1;
66
67 IF okc_kexp_pls_tbl.COUNT = 0 THEN
68 EXIT;
69 END IF;
70
71 -- ------------------------------------------------------
72 -- Bulk Insert into Temp Table
73 -- ------------------------------------------------------
74 FORALL i IN okc_kexp_pls_tbl.FIRST .. okc_kexp_pls_tbl.LAST
75 INSERT
76 INTO OKC_KEXP_REPORT
77 (
78 CONTRACT_HEADER_ROWID,
79 REPORT_ID,
80 REPORT_DATE )
81 VALUES
82 (
83 okc_kexp_pls_tbl(i), -- Row Id
84 l_report_seq, -- Report Id
85 SYSDATE -- Report Date
86 );
87
88 COMMIT;
89
90 -- -------------------------------------
91 -- Delete plsql collection table
92 -- -------------------------------------
93 okc_kexp_pls_tbl.DELETE;
94
95 -- Physical temp table cleanup process to be defined later.
96
97 END LOOP;
98
99 -- --------------------------------------------------
100 -- Prepare out parameters for sucessful completion.
101 -- --------------------------------------------------
102 x_return_status := OKC_API.G_RET_STS_SUCCESS;
103 x_report_id := l_report_seq;
104
105 EXCEPTION
106 WHEN OTHERS THEN
107 -- --------------------------------------------------
108 -- Store SQL error message on message stack
109 -- --------------------------------------------------
110 OKC_API.set_message(p_app_name => G_APP_NAME,
111 p_msg_name => G_UNEXPECTED_ERROR,
112 p_token1 => G_SQLCODE_TOKEN,
113 p_token1_value => SQLCODE,
114 p_token2 => G_SQLERRM_TOKEN,
115 p_token2_value => SQLERRM);
116
117 -- ------------------------------------------
118 -- Return error status to the caller.
119 -- Notify caller of an error as UNEXPETED error
120 -- ------------------------------------------
121 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
122
123 END load_ksrch_rows;
124
125
126 PROCEDURE delete_ksrch_rows(
127 p_from_date IN DATE ,
128 p_to_date IN DATE ,
129 x_return_status OUT NOCOPY VARCHAR2 ) IS
130 BEGIN
131
132 -- ------------------------------------------------------
133 -- To delete rows from the temporary table for a given
134 -- period of days. This procedure will be registered as
135 -- the concurrent program, with two input parameters.
136 -- ------------------------------------------------------
137 IF p_from_date <= p_to_date
138 THEN
139
140 DELETE FROM okc_kexp_report
141 WHERE report_date
142 BETWEEN p_from_date AND p_to_date;
143
144 -- --------------------------------------------------
145 -- Prepare out parameters for sucessful completion.
146 -- --------------------------------------------------
147 x_return_status := OKC_API.G_RET_STS_SUCCESS;
148
149 COMMIT;
150
151 ELSE
152 -- --------------------------------------------------
153 -- Return error status to the caller.
154 -- --------------------------------------------------
155 x_return_status := OKC_API.G_RET_STS_ERROR;
156
157 END IF;
158
159 EXCEPTION
160 WHEN OTHERS THEN
161 -- --------------------------------------------------
162 -- Store SQL error message on message stack
163 -- --------------------------------------------------
164 OKC_API.set_message(p_app_name => G_APP_NAME,
165 p_msg_name => G_UNEXPECTED_ERROR,
166 p_token1 => G_SQLCODE_TOKEN,
167 p_token1_value => SQLCODE,
168 p_token2 => G_SQLERRM_TOKEN,
169 p_token2_value => SQLERRM);
170
171 -- ------------------------------------------
172 -- Return error status to the caller.
173 -- Notify caller of an error as UNEXPETED error
174 -- ------------------------------------------
175 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
176
177 END delete_ksrch_rows;
178
179 FUNCTION get_salesrep_name ( p_id1 IN VARCHAR2,
180 p_id2 IN VARCHAR2 )
181 RETURN VARCHAR2 IS
182 l_name VARCHAR2(255);
183 l_not_found BOOLEAN;
184
185 -- --------------------------------------------------
186 -- Cursor to query the OKX table given the two IDs.
187 -- --------------------------------------------------
188 CURSOR contact_cur IS
189 SELECT name
190 FROM OKX_SALESREPS_V
191 WHERE id1 = p_id1
192 AND id2 = p_id2;
193
194 BEGIN
195 -- --------------------------------------------------
196 -- Fetch Sales Person Name from the OKX table.
197 -- --------------------------------------------------
198 OPEN contact_cur;
199 FETCH contact_cur INTO l_name;
200 l_not_found := contact_cur%NOTFOUND;
201 CLOSE contact_cur;
202
203 IF (l_not_found) THEN
204 RETURN NULL;
205 End if;
206
207 RETURN l_name;
208
209 EXCEPTION
210 WHEN NO_DATA_FOUND THEN
211 -- --------------------------------------------------
212 -- Close the cursor and return null.
213 -- --------------------------------------------------
214 IF (contact_cur%ISOPEN) THEN
215 CLOSE contact_cur;
216 END IF;
217 RETURN NULL;
218 END get_salesrep_name;
219
220 END okc_kexp_pvt;
[Home] [Help]