1 PACKAGE jtf_rs_salesreps_pub AS
2 /* $Header: jtfrspss.pls 120.3 2005/07/19 18:56:53 repuri ship $ */
3 /*#
4 * Salesperson create and update API
5 * This API contains the procedures to insert and update Salesrep record.
6 * @rep:scope public
7 * @rep:product JTF
8 * @rep:displayname Salespersons API
9 * @rep:category BUSINESS_ENTITY JTF_RS_SALESREP
10 */
11 /*****************************************************************************************
12 This is a public API that caller will invoke.
13 It provides procedures for managing Salesreps, like
14 create and update Salesreps from other modules.
15 Its main procedures are as following:
16 Create Salesreps
17 Update Salesreps
18 ******************************************************************************************/
19
20
21 /* Procedure to create the Salesreps
22 based on input values passed by calling routines. */
23 /*#
24 * Create Salesreps API
25 * This procedure allows the user to create a salesrep record.
26 * @param p_api_version API version
27 * @param p_init_msg_list Initialization of the message list
28 * @param p_commit Commit
29 * @param p_resource_id Resource Identifier
30 * @param p_sales_credit_type_id Sales Credit Identifier
31 * @param p_name The sales person's name.
32 * @param p_status The status of this salesperson.
33 * @param p_start_date_active Date on which the salesperson becomes active. This value can not be NULL, and the start date must be less than the end date.
34 * @param p_end_date_active The effective end date for the salespersons. If no end date is provided, the salesperson is active indefinitely.
35 * @param p_org_id Organization Identifier
36 * @param p_gl_id_rev Accounting flexfield used for Revenue accounts
37 * @param p_gl_id_freight Accounting flexfield used for Freight accounts
38 * @param p_gl_id_rec Accounting flexfield used for Receivables accounts
39 * @param p_set_of_books_id Set of books identifier, used by Oracle Accounts Receivables
40 * @param p_salesrep_number Salesperson Number
41 * @param p_email_address Email address of the salesperson
42 * @param p_wh_update_date This date is sent to the data warehouse
43 * @param p_sales_tax_geocode Sales tax code, it associates the salesperson with a unique tax jurisdiction
44 * @param p_sales_tax_inside_city_limits Indicates that the tax jurisdiction for this address is within city limits
45 * @param x_return_status Output parameter for return status
46 * @param x_msg_count Output parameter for number of user messages from this procedure
47 * @param x_msg_data Output parameter containing last user message from this procedure
48 * @param x_salesrep_id Out parameter for Salesrep Identifier
49 * @rep:scope public
50 * @rep:lifecycle active
51 * @rep:displayname Create Salesreps API
52 */
53 PROCEDURE create_salesrep
54 (P_API_VERSION IN NUMBER,
55 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
56 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
57 P_RESOURCE_ID IN JTF_RS_SALESREPS.RESOURCE_ID%TYPE,
58 P_SALES_CREDIT_TYPE_ID IN JTF_RS_SALESREPS.SALES_CREDIT_TYPE_ID%TYPE,
59 P_NAME IN JTF_RS_SALESREPS.NAME%TYPE DEFAULT NULL,
60 P_STATUS IN JTF_RS_SALESREPS.STATUS%TYPE DEFAULT NULL,
61 P_START_DATE_ACTIVE IN JTF_RS_SALESREPS.START_DATE_ACTIVE%TYPE DEFAULT SYSDATE,
62 P_END_DATE_ACTIVE IN JTF_RS_SALESREPS.END_DATE_ACTIVE%TYPE DEFAULT NULL,
63 P_ORG_ID IN JTF_RS_SALESREPS.ORG_ID%TYPE DEFAULT FND_API.G_MISS_NUM,
64 P_GL_ID_REV IN JTF_RS_SALESREPS.GL_ID_REV%TYPE DEFAULT NULL,
65 P_GL_ID_FREIGHT IN JTF_RS_SALESREPS.GL_ID_FREIGHT%TYPE DEFAULT NULL,
66 P_GL_ID_REC IN JTF_RS_SALESREPS.GL_ID_REC%TYPE DEFAULT NULL,
67 P_SET_OF_BOOKS_ID IN JTF_RS_SALESREPS.SET_OF_BOOKS_ID%TYPE DEFAULT NULL,
68 P_SALESREP_NUMBER IN JTF_RS_SALESREPS.SALESREP_NUMBER%TYPE DEFAULT NULL,
69 P_EMAIL_ADDRESS IN JTF_RS_SALESREPS.EMAIL_ADDRESS%TYPE DEFAULT NULL,
70 P_WH_UPDATE_DATE IN JTF_RS_SALESREPS.WH_UPDATE_DATE%TYPE DEFAULT NULL,
71 P_SALES_TAX_GEOCODE IN JTF_RS_SALESREPS.SALES_TAX_GEOCODE%TYPE DEFAULT NULL,
72 P_SALES_TAX_INSIDE_CITY_LIMITS IN JTF_RS_SALESREPS.SALES_TAX_INSIDE_CITY_LIMITS%TYPE DEFAULT NULL,
73 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
74 X_MSG_COUNT OUT NOCOPY NUMBER,
75 X_MSG_DATA OUT NOCOPY VARCHAR2,
76 X_SALESREP_ID OUT NOCOPY JTF_RS_SALESREPS.SALESREP_ID%TYPE
77 );
78
79 --Create Salesrep Migration API, used for one-time migration of salesrep data
80 --The API includes SALESREP_ID, ORG_ID as its Input Parameters
81 /*#
82 * Create Salesreps Migration API
83 * This procedure is used for one-time migration of salesrep data
84 * The API includes salesrep_id and org_id as its Input Parameters
85 * @param p_api_version API version
86 * @param p_init_msg_list Initialization of the message list
87 * @param p_commit Commit
88 * @param p_resource_id Resource Identifier
89 * @param p_sales_credit_type_id Sales Credit Identifier
90 * @param p_name The sales person's name.
91 * @param p_status The status of this salesperson.
92 * @param p_start_date_active Date on which the salesperson becomes active. This value can not be NULL, and the start date must be less than the end date.
93 * @param p_end_date_active The effective end date for the salespersons. If no end date is provided, the salesperson is active indefinitely.
94 * @param p_gl_id_rev Accounting flexfield used for Revenue accounts
95 * @param p_gl_id_freight Accounting flexfield used for Freight accounts
96 * @param p_gl_id_rec Accounting flexfield used for Receivables accounts
97 * @param p_set_of_books_id Set of books identifier, used by Oracle Accounts Receivables
98 * @param p_salesrep_number Salesperson Number
99 * @param p_email_address Email address of the salesperson
100 * @param p_wh_update_date This date is sent to the data warehouse
101 * @param p_sales_tax_geocode Sales tax code, it associates the salesperson with a unique tax jurisdiction
102 * @param p_sales_tax_inside_city_limits Indicates that the tax jurisdiction for this address is within city limits
103 * @param p_salesrep_id Salesperson Identifier
104 * @param p_org_id Organization Identifier
105 * @param p_attribute1 Descriptive flexfield Segment 1
106 * @param p_attribute2 Descriptive flexfield Segment 2
107 * @param p_attribute3 Descriptive flexfield Segment 3
108 * @param p_attribute4 Descriptive flexfield Segment 4
109 * @param p_attribute5 Descriptive flexfield Segment 5
110 * @param p_attribute6 Descriptive flexfield Segment 6
111 * @param p_attribute7 Descriptive flexfield Segment 7
112 * @param p_attribute8 Descriptive flexfield Segment 8
113 * @param p_attribute9 Descriptive flexfield Segment 9
114 * @param p_attribute10 Descriptive flexfield Segment 10
115 * @param p_attribute11 Descriptive flexfield Segment 11
116 * @param p_attribute12 Descriptive flexfield Segment 12
117 * @param p_attribute13 Descriptive flexfield Segment 13
118 * @param p_attribute14 Descriptive flexfield Segment 14
119 * @param p_attribute15 Descriptive flexfield Segment 15
120 * @param p_attribute_category Descriptive flexfield structure definition column
121 * @param x_return_status Output parameter for return status
122 * @param x_msg_count Output parameter for number of user messages from this procedure
123 * @param x_msg_data Output parameter containing last user message from this procedure
124 * @param x_salesrep_id Out parameter for Salesrep Identifier
125 * @rep:scope internal
126 * @rep:lifecycle obsolete
127 * @rep:displayname Create Salesreps Migration API
128 */
129 PROCEDURE create_salesrep_migrate
130 (P_API_VERSION IN NUMBER,
131 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
132 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
133 P_RESOURCE_ID IN JTF_RS_SALESREPS.RESOURCE_ID%TYPE,
134 P_SALES_CREDIT_TYPE_ID IN JTF_RS_SALESREPS.SALES_CREDIT_TYPE_ID%TYPE,
135 P_NAME IN JTF_RS_SALESREPS.NAME%TYPE DEFAULT NULL,
136 P_STATUS IN JTF_RS_SALESREPS.STATUS%TYPE DEFAULT NULL,
137 P_START_DATE_ACTIVE IN JTF_RS_SALESREPS.START_DATE_ACTIVE%TYPE DEFAULT SYSDATE,
138 P_END_DATE_ACTIVE IN JTF_RS_SALESREPS.END_DATE_ACTIVE%TYPE DEFAULT NULL,
139 P_GL_ID_REV IN JTF_RS_SALESREPS.GL_ID_REV%TYPE DEFAULT NULL,
140 P_GL_ID_FREIGHT IN JTF_RS_SALESREPS.GL_ID_FREIGHT%TYPE DEFAULT NULL,
141 P_GL_ID_REC IN JTF_RS_SALESREPS.GL_ID_REC%TYPE DEFAULT NULL,
142 P_SET_OF_BOOKS_ID IN JTF_RS_SALESREPS.SET_OF_BOOKS_ID%TYPE DEFAULT NULL,
143 P_SALESREP_NUMBER IN JTF_RS_SALESREPS.SALESREP_NUMBER%TYPE DEFAULT NULL,
144 P_EMAIL_ADDRESS IN JTF_RS_SALESREPS.EMAIL_ADDRESS%TYPE DEFAULT NULL,
145 P_WH_UPDATE_DATE IN JTF_RS_SALESREPS.WH_UPDATE_DATE%TYPE DEFAULT NULL,
146 P_SALES_TAX_GEOCODE IN JTF_RS_SALESREPS.SALES_TAX_GEOCODE%TYPE DEFAULT NULL,
147 P_SALES_TAX_INSIDE_CITY_LIMITS IN JTF_RS_SALESREPS.SALES_TAX_INSIDE_CITY_LIMITS%TYPE DEFAULT NULL,
148 P_SALESREP_ID IN JTF_RS_SALESREPS.SALESREP_ID%TYPE,
149 P_ORG_ID IN JTF_RS_SALESREPS.ORG_ID%TYPE,
150 P_ATTRIBUTE_CATEGORY IN JTF_RS_SALESREPS.ATTRIBUTE_CATEGORY%TYPE DEFAULT NULL,
151 P_ATTRIBUTE1 IN JTF_RS_SALESREPS.ATTRIBUTE1%TYPE DEFAULT NULL,
152 P_ATTRIBUTE2 IN JTF_RS_SALESREPS.ATTRIBUTE2%TYPE DEFAULT NULL,
153 P_ATTRIBUTE3 IN JTF_RS_SALESREPS.ATTRIBUTE3%TYPE DEFAULT NULL,
154 P_ATTRIBUTE4 IN JTF_RS_SALESREPS.ATTRIBUTE4%TYPE DEFAULT NULL,
155 P_ATTRIBUTE5 IN JTF_RS_SALESREPS.ATTRIBUTE5%TYPE DEFAULT NULL,
156 P_ATTRIBUTE6 IN JTF_RS_SALESREPS.ATTRIBUTE6%TYPE DEFAULT NULL,
157 P_ATTRIBUTE7 IN JTF_RS_SALESREPS.ATTRIBUTE7%TYPE DEFAULT NULL,
158 P_ATTRIBUTE8 IN JTF_RS_SALESREPS.ATTRIBUTE8%TYPE DEFAULT NULL,
159 P_ATTRIBUTE9 IN JTF_RS_SALESREPS.ATTRIBUTE9%TYPE DEFAULT NULL,
160 P_ATTRIBUTE10 IN JTF_RS_SALESREPS.ATTRIBUTE10%TYPE DEFAULT NULL,
161 P_ATTRIBUTE11 IN JTF_RS_SALESREPS.ATTRIBUTE11%TYPE DEFAULT NULL,
162 P_ATTRIBUTE12 IN JTF_RS_SALESREPS.ATTRIBUTE12%TYPE DEFAULT NULL,
163 P_ATTRIBUTE13 IN JTF_RS_SALESREPS.ATTRIBUTE13%TYPE DEFAULT NULL,
164 P_ATTRIBUTE14 IN JTF_RS_SALESREPS.ATTRIBUTE14%TYPE DEFAULT NULL,
165 P_ATTRIBUTE15 IN JTF_RS_SALESREPS.ATTRIBUTE15%TYPE DEFAULT NULL,
166 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
167 X_MSG_COUNT OUT NOCOPY NUMBER,
168 X_MSG_DATA OUT NOCOPY VARCHAR2,
169 X_SALESREP_ID OUT NOCOPY JTF_RS_SALESREPS.SALESREP_ID%TYPE
170 );
171
172 --Creating a Global Variable to be used for setting the flag,
173 --when the create_salesrep_migrate gets called
174
175 G_SRP_ID_PUB_FLAG VARCHAR2(1) := 'Y';
176 G_SALESREP_ID JTF_RS_SALESREPS.SALESREP_ID%TYPE := NULL;
177 G_ORG_ID JTF_RS_SALESREPS.ORG_ID%TYPE := NULL;
178 G_ATTRIBUTE1 JTF_RS_SALESREPS.ATTRIBUTE1%TYPE := NULL;
179 G_ATTRIBUTE2 JTF_RS_SALESREPS.ATTRIBUTE2%TYPE := NULL;
180 G_ATTRIBUTE3 JTF_RS_SALESREPS.ATTRIBUTE3%TYPE := NULL;
181 G_ATTRIBUTE4 JTF_RS_SALESREPS.ATTRIBUTE4%TYPE := NULL;
182 G_ATTRIBUTE5 JTF_RS_SALESREPS.ATTRIBUTE5%TYPE := NULL;
183 G_ATTRIBUTE6 JTF_RS_SALESREPS.ATTRIBUTE6%TYPE := NULL;
184 G_ATTRIBUTE7 JTF_RS_SALESREPS.ATTRIBUTE7%TYPE := NULL;
185 G_ATTRIBUTE8 JTF_RS_SALESREPS.ATTRIBUTE8%TYPE := NULL;
186 G_ATTRIBUTE9 JTF_RS_SALESREPS.ATTRIBUTE9%TYPE := NULL;
187 G_ATTRIBUTE10 JTF_RS_SALESREPS.ATTRIBUTE10%TYPE := NULL;
188 G_ATTRIBUTE11 JTF_RS_SALESREPS.ATTRIBUTE11%TYPE := NULL;
189 G_ATTRIBUTE12 JTF_RS_SALESREPS.ATTRIBUTE12%TYPE := NULL;
190 G_ATTRIBUTE13 JTF_RS_SALESREPS.ATTRIBUTE13%TYPE := NULL;
191 G_ATTRIBUTE14 JTF_RS_SALESREPS.ATTRIBUTE14%TYPE := NULL;
192 G_ATTRIBUTE15 JTF_RS_SALESREPS.ATTRIBUTE15%TYPE := NULL;
193 G_ATTRIBUTE_CATEGORY JTF_RS_SALESREPS.ATTRIBUTE_CATEGORY%TYPE := NULL;
194
195 /* Procedure to update the Salesreps
196 based on input values passed by calling routines. */
197 /*#
198 * Update Salesreps API
199 * This procedure allows the user to update a salesrep record.
200 * @param p_api_version API version
201 * @param p_init_msg_list Initialization of the message list
202 * @param p_commit Commit
203 * @param p_salesrep_id Salesperson Identifier
204 * @param p_sales_credit_type_id Sales Credit Identifier
205 * @param p_name The sales person's name.
206 * @param p_status The status of this salesperson.
207 * @param p_start_date_active Date on which the salesperson becomes active. This value can not be NULL, and the start date must be less than the end date.
208 * @param p_end_date_active The effective end date for the salespersons. If no end date is provided, the salesperson is active indefinitely.
209 * @param p_gl_id_rev Accounting flexfield used for Revenue accounts
210 * @param p_gl_id_freight Accounting flexfield used for Freight accounts
211 * @param p_gl_id_rec Accounting flexfield used for Receivables accounts
212 * @param p_set_of_books_id Set of books identifier, used by Oracle Accounts Receivables
213 * @param p_salesrep_number Salesperson Number
214 * @param p_email_address Email address of the salesperson
215 * @param p_wh_update_date This date is sent to the data warehouse
216 * @param p_sales_tax_geocode Sales tax code, it associates the salesperson with a unique tax jurisdiction
217 * @param p_sales_tax_inside_city_limits Indicates that the tax jurisdiction for this address is within city limits
218 * @param p_org_id Organization Identifier
219 * @param p_object_version_number The object version number of the salesrep derives from the jtf_rs_salesreps table.
220 * @param x_return_status Output parameter for return status
221 * @param x_msg_count Output parameter for number of user messages from this procedure
222 * @param x_msg_data Output parameter containing last user message from this procedure
223 * @rep:scope public
224 * @rep:lifecycle active
225 * @rep:displayname Update Salesreps API
226 */
227 PROCEDURE update_salesrep
228 (P_API_VERSION IN NUMBER,
229 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
230 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
231 P_SALESREP_ID IN JTF_RS_SALESREPS.SALESREP_ID%TYPE,
232 P_SALES_CREDIT_TYPE_ID IN JTF_RS_SALESREPS.SALES_CREDIT_TYPE_ID%TYPE,
233 P_NAME IN JTF_RS_SALESREPS.NAME%TYPE DEFAULT FND_API.G_MISS_CHAR,
234 P_STATUS IN JTF_RS_SALESREPS.STATUS%TYPE DEFAULT FND_API.G_MISS_CHAR,
235 P_START_DATE_ACTIVE IN JTF_RS_SALESREPS.START_DATE_ACTIVE%TYPE DEFAULT FND_API.G_MISS_DATE,
236 P_END_DATE_ACTIVE IN JTF_RS_SALESREPS.END_DATE_ACTIVE%TYPE DEFAULT FND_API.G_MISS_DATE,
237 P_GL_ID_REV IN JTF_RS_SALESREPS.GL_ID_REV%TYPE DEFAULT FND_API.G_MISS_NUM,
238 P_GL_ID_FREIGHT IN JTF_RS_SALESREPS.GL_ID_FREIGHT%TYPE DEFAULT FND_API.G_MISS_NUM,
239 P_GL_ID_REC IN JTF_RS_SALESREPS.GL_ID_REC%TYPE DEFAULT FND_API.G_MISS_NUM,
240 P_SET_OF_BOOKS_ID IN JTF_RS_SALESREPS.SET_OF_BOOKS_ID%TYPE DEFAULT FND_API.G_MISS_NUM,
241 P_SALESREP_NUMBER IN JTF_RS_SALESREPS.SALESREP_NUMBER%TYPE DEFAULT FND_API.G_MISS_CHAR,
242 P_EMAIL_ADDRESS IN JTF_RS_SALESREPS.EMAIL_ADDRESS%TYPE DEFAULT FND_API.G_MISS_CHAR,
243 P_WH_UPDATE_DATE IN JTF_RS_SALESREPS.WH_UPDATE_DATE%TYPE DEFAULT FND_API.G_MISS_DATE,
244 P_SALES_TAX_GEOCODE IN JTF_RS_SALESREPS.SALES_TAX_GEOCODE%TYPE DEFAULT FND_API.G_MISS_CHAR,
245 P_SALES_TAX_INSIDE_CITY_LIMITS IN JTF_RS_SALESREPS.SALES_TAX_INSIDE_CITY_LIMITS%TYPE DEFAULT FND_API.G_MISS_CHAR,
246 P_ORG_ID IN JTF_RS_SALESREPS.ORG_ID%TYPE,
247 P_OBJECT_VERSION_NUMBER IN OUT NOCOPY JTF_RS_SALESREPS.OBJECT_VERSION_NUMBER%TYPE,
248 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
249 X_MSG_COUNT OUT NOCOPY NUMBER,
250 X_MSG_DATA OUT NOCOPY VARCHAR2
251 );
252
253
254 END jtf_rs_salesreps_pub;