1 PACKAGE CN_REASONS_PUB AUTHID CURRENT_USER AS
2 -- $Header: cnpresns.pls 115.2 2003/07/04 01:35:20 jjhuang ship $
3 -- +======================================================================+
4 -- | Copyright (c) 1994 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +======================================================================+
8 --
9 -- Package Name
10 -- CN_REASON_PUB
11 -- Purpose
12 -- Package specifications for Analyst Notes JSP. The following flow diagram
13 -- shows how Analyst Notes JSP interacts with all the APIs.
14 -- |-------------| |------------| |-------------|
15 -- |-------------| |Rosetta | |cn_reasons_ | |cn_reasons_ |
16 -- |cnreason.jsp |-->|Wrapper |-->|pub |-->|pvt |---|
17 -- |-------------| |CnReasonsPub | |cnpresns.pls| |cnvresns.pls | |
18 -- |-------------| |------------| |-------------| |
19 -- |-------------| |
20 -- |cn_reasons_ | |
21 -- |pkg |<--|
22 -- |cntresns.pls |
23 -- |-------------|
24 -- History
25 -- 04/02/02 Rao.Chenna Created
26 -- 06/16/03 Julia Huang Added show_last_analyst_note for 11.5.10
27 TYPE worksheet_rec IS RECORD(
28 payment_worksheet_id number := fnd_api.g_miss_num,
29 role_id number := fnd_api.g_miss_num,
30 worksheet_status varchar2(30) := fnd_api.g_miss_char,
31 payrun_name varchar2(80) := fnd_api.g_miss_char,
32 payrun_id number := fnd_api.g_miss_num,
33 pay_period_id number := fnd_api.g_miss_num,
34 payrun_status varchar2(80) := fnd_api.g_miss_char,
35 period_name varchar2(30) := fnd_api.g_miss_char,
36 salesrep_id number := fnd_api.g_miss_num,
37 resource_id number := fnd_api.g_miss_num,
38 salesrep_name varchar2(360) := fnd_api.g_miss_char,
39 employee_number varchar2(30) := fnd_api.g_miss_char,
40 pay_group_id number := fnd_api.g_miss_num,
41 pay_group_name varchar2(80) := fnd_api.g_miss_char);
42 --
43 TYPE notes_rec IS RECORD(
44 reason_history_id number := fnd_api.g_miss_num,
45 reason_id number := fnd_api.g_miss_num,
46 updated_table varchar2(30) := fnd_api.g_miss_char,
47 upd_table_id number := fnd_api.g_miss_num,
48 reason varchar2(4000) := fnd_api.g_miss_char,
49 reason_code varchar2(30) := fnd_api.g_miss_char,
50 reason_meaning varchar2(80) := fnd_api.g_miss_char,
51 lookup_type varchar2(30) := fnd_api.g_miss_char,
52 update_flag varchar2(30) := fnd_api.g_miss_char,
53 dml_flag varchar2(30) := fnd_api.g_miss_char,
54 attribute_category varchar2(30) := fnd_api.g_miss_char,
55 attribute1 varchar2(30) := fnd_api.g_miss_char,
56 attribute2 varchar2(30) := fnd_api.g_miss_char,
57 attribute3 varchar2(30) := fnd_api.g_miss_char,
58 attribute4 varchar2(30) := fnd_api.g_miss_char,
59 attribute5 varchar2(30) := fnd_api.g_miss_char,
60 attribute6 varchar2(30) := fnd_api.g_miss_char,
61 attribute7 varchar2(30) := fnd_api.g_miss_char,
62 attribute8 varchar2(30) := fnd_api.g_miss_char,
63 attribute9 varchar2(30) := fnd_api.g_miss_char,
64 attribute10 varchar2(30) := fnd_api.g_miss_char,
65 attribute11 varchar2(30) := fnd_api.g_miss_char,
66 attribute12 varchar2(30) := fnd_api.g_miss_char,
67 attribute13 varchar2(30) := fnd_api.g_miss_char,
68 attribute14 varchar2(30) := fnd_api.g_miss_char,
69 attribute15 varchar2(30) := fnd_api.g_miss_char,
70 last_update_date date := fnd_api.g_miss_date,
71 last_updated_by number := fnd_api.g_miss_num,
72 last_updated_username varchar2(100) := fnd_api.g_miss_char,
73 object_version_number number := fnd_api.g_miss_num);
74 --
75 TYPE notes_tbl IS
76 TABLE OF notes_rec INDEX BY BINARY_INTEGER ;
77 --
78 /*--------------------------------------------------------------------------
79 API name : show_analyst_notes
80 Type : Public
81 Pre-reqs :
82 Usage : This is the main procedure that gets the data populate the
83 Analyst Notes JSP.
84 Desc :
85 Parameters
86 IN : p_api_version IN NUMBER,
87 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
88 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
89 p_commit IN VARCHAR2 := CN_API.G_FALSE,
90 p_first IN NUMBER,
91 p_last IN NUMBER,
92 p_payment_worksheet_id IN NUMBER,
93 p_table_name IN VARCHAR2,
94 p_lookup_type IN VARCHAR2,
95
96 OUT NOCOPY : x_return_status OUT NOCOPY VARCHAR2,
97 x_msg_count OUT NOCOPY NUMBER,
98 x_msg_data OUT NOCOPY VARCHAR2,
99 x_loading_status OUT NOCOPY VARCHAR2,
100 x_worksheet_rec OUT NOCOPY cn_reasons_pub.worksheet_rec,
101 x_notes_tbl OUT NOCOPY cn_reasons_pub.notes_tbl,
102 x_notes_count OUT NOCOPY NUMBER
103
104 Notes : This JSP has two sections. One is Worksheet
105 information and second is showing the notes corresponding to
106 the Worksheet. x_worksheet_rec populate the first section and
107 x_notes_tbl populates the second section
108 --------------------------------------------------------------------------*/
109 PROCEDURE show_analyst_notes(
110 p_api_version IN NUMBER,
111 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
112 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
113 p_commit IN VARCHAR2 := CN_API.G_FALSE,
114 p_first IN NUMBER,
115 p_last IN NUMBER,
116 p_payment_worksheet_id IN NUMBER,
117 p_table_name IN VARCHAR2,
118 p_lookup_type IN VARCHAR2,
119 x_return_status OUT NOCOPY VARCHAR2,
120 x_msg_count OUT NOCOPY NUMBER,
121 x_msg_data OUT NOCOPY VARCHAR2,
122 x_loading_status OUT NOCOPY VARCHAR2,
123 x_worksheet_rec OUT NOCOPY cn_reasons_pub.worksheet_rec,
124 x_notes_tbl OUT NOCOPY cn_reasons_pub.notes_tbl,
125 x_notes_count OUT NOCOPY NUMBER);
126 /*--------------------------------------------------------------------------
127 API name : manage_analyst_notes
128 Type : Public
129 Pre-reqs :
130 Usage : This procedure is used to insert or update an analyst notes.
131 Desc :
132 Parameters
133 IN : p_api_version IN NUMBER,
134 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
135 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
136 p_commit IN VARCHAR2 := CN_API.G_FALSE,
137 p_notes_tbl IN cn_reasons_pub.notes_tbl
138
139 OUT NOCOPY : x_return_status OUT NOCOPY VARCHAR2,
140 x_msg_count OUT NOCOPY NUMBER,
141 x_msg_data OUT NOCOPY VARCHAR2,
142 x_loading_status OUT NOCOPY VARCHAR2);
143 Notes : From the JSP data comes through p_notes_tbl PL/SQL table. Based
144 on the dml_flag either the record is inserted into the cn_reasons
145 table or updated. When the record is updated, original content
146 will be copied to cn_reason_history table.
147 --------------------------------------------------------------------------*/
148 PROCEDURE manage_analyst_notes(
149 p_api_version IN NUMBER,
150 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
151 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
152 p_commit IN VARCHAR2 := CN_API.G_FALSE,
153 p_notes_tbl IN cn_reasons_pub.notes_tbl,
154 x_return_status OUT NOCOPY VARCHAR2,
155 x_msg_count OUT NOCOPY NUMBER,
156 x_msg_data OUT NOCOPY VARCHAR2,
157 x_loading_status OUT NOCOPY VARCHAR2);
158 /*--------------------------------------------------------------------------
159 API name : remove_analyst_notes
160 Type : Public
161 Pre-reqs :
162 Usage : This procedure is used to remove the record from cn_reasons table
163 Desc :
164 Parameters
165 IN : p_api_version IN NUMBER,
166 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
167 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
168 p_commit IN VARCHAR2 := CN_API.G_FALSE,
169 p_payment_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
170 p_reason_id IN NUMBER := FND_API.G_MISS_NUM,
171 OUT NOCOPY : x_return_status OUT NOCOPY VARCHAR2,
172 x_msg_count OUT NOCOPY NUMBER,
173 x_msg_data OUT NOCOPY VARCHAR2,
174 x_loading_status OUT NOCOPY VARCHAR2
175 Notes :
176 --------------------------------------------------------------------------*/
177 PROCEDURE remove_analyst_notes(
178 p_api_version IN NUMBER,
179 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
180 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
181 p_commit IN VARCHAR2 := CN_API.G_FALSE,
182 p_payment_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
183 p_reason_id IN NUMBER := FND_API.G_MISS_NUM,
184 x_return_status OUT NOCOPY VARCHAR2,
185 x_msg_count OUT NOCOPY NUMBER,
186 x_msg_data OUT NOCOPY VARCHAR2,
187 x_loading_status OUT NOCOPY VARCHAR2);
188 --
189
190 /*--------------------------------------------------------------------------
191 API name : show_last_analyst_note
192 Type : Public
193 Pre-reqs :
194 Usage : This is the main procedure that gets the data populate the
195 Analyst Notes JSP.
196 Desc :
197 Parameters
198 IN : p_api_version IN NUMBER,
199 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
200 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
201 p_commit IN VARCHAR2 := CN_API.G_FALSE,
202 p_first IN NUMBER,
203 p_last IN NUMBER,
204 p_payment_worksheet_id IN NUMBER,
205 p_table_name IN VARCHAR2,
206 p_lookup_type IN VARCHAR2,
207
208 OUT NOCOPY : x_return_status OUT NOCOPY VARCHAR2,
209 x_msg_count OUT NOCOPY NUMBER,
210 x_msg_data OUT NOCOPY VARCHAR2,
211 x_loading_status OUT NOCOPY VARCHAR2,
212 x_worksheet_rec OUT NOCOPY cn_reasons_pub.worksheet_rec,
213 x_notes_tbl OUT NOCOPY cn_reasons_pub.notes_tbl,
214 x_notes_count OUT NOCOPY NUMBER
215
216 Notes : This JSP has two sections. One is Worksheet
217 information and second is showing the notes corresponding to
218 the Worksheet. x_worksheet_rec populate the first section and
219 x_notes_tbl populates the second section
220 11.5.10 by Julia Huang
221 --------------------------------------------------------------------------*/
222 PROCEDURE show_last_analyst_note(
223 p_api_version IN NUMBER,
224 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
225 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
226 p_commit IN VARCHAR2 := CN_API.G_FALSE,
227 p_payment_worksheet_id IN NUMBER,
228 p_table_name IN VARCHAR2,
229 p_lookup_type IN VARCHAR2,
230 x_return_status OUT NOCOPY VARCHAR2,
231 x_msg_count OUT NOCOPY NUMBER,
232 x_msg_data OUT NOCOPY VARCHAR2,
233 x_loading_status OUT NOCOPY VARCHAR2,
234 x_notes_tbl OUT NOCOPY cn_reasons_pub.notes_tbl,
235 x_notes_count OUT NOCOPY NUMBER);
236 END; -- Package spec