[Home] [Help]
PACKAGE BODY: APPS.ZX_TCM_BES_REGISTRATION_PVT
Source
1 PACKAGE BODY ZX_TCM_BES_REGISTRATION_PVT AS
2 /* $Header: zxcbesapib.pls 120.5 2005/10/04 01:05:47 dbetanco ship $ */
3
4
5 PROCEDURE UPDATE_SUPPLIERS(
6 P_PARTY_ID IN NUMBER,
7 P_AMOUNT_INCLUDES_TAX_FLAG IN VARCHAR2,
8 P_AP_TAX_ROUNDING_RULE IN VARCHAR2,
9 P_AUTO_TAX_CALC_FLAG IN VARCHAR2,
10 P_OFFSET_TAX_FLAG IN VARCHAR2,
11 P_VAT_CODE IN VARCHAR2,
12 P_VAT_REGISTRATION_NUM IN VARCHAR2,
13 P_WITHHOLDING_START_DATE IN DATE,
14 P_WITHHOLDING_STATUS_CODE IN VARCHAR2)
15 IS
16
17 BEGIN
18
19 UPDATE AP_SUPPLIERS
20 SET AMOUNT_INCLUDES_TAX_FLAG = P_AMOUNT_INCLUDES_TAX_FLAG,
21 AP_TAX_ROUNDING_RULE = P_AP_TAX_ROUNDING_RULE,
22 AUTO_TAX_CALC_FLAG = P_AUTO_TAX_CALC_FLAG,
23 OFFSET_TAX_FLAG = P_OFFSET_TAX_FLAG,
24 VAT_CODE = P_VAT_CODE,
25 VAT_REGISTRATION_NUM = P_VAT_REGISTRATION_NUM,
26 WITHHOLDING_START_DATE = P_WITHHOLDING_START_DATE,
27 WITHHOLDING_STATUS_LOOKUP_CODE = P_WITHHOLDING_STATUS_CODE
28 WHERE PARTY_ID = P_PARTY_ID;
29
30 END;
31
32
33 PROCEDURE UPDATE_SUPPLIER_SITES(
34 P_PARTY_SITE_ID IN NUMBER,
35 P_AMOUNT_INCLUDES_TAX_FLAG IN VARCHAR2,
36 P_AP_TAX_ROUNDING_RULE IN VARCHAR2,
37 P_AUTO_TAX_CALC_FLAG IN VARCHAR2,
38 P_OFFSET_TAX_FLAG IN VARCHAR2,
39 P_VAT_CODE IN VARCHAR2,
40 P_VAT_REGISTRATION_NUM IN VARCHAR2)
41 IS
42 BEGIN
43
44 UPDATE ap_supplier_sites_all
45 SET AMOUNT_INCLUDES_TAX_FLAG = P_AMOUNT_INCLUDES_TAX_FLAG,
46 AP_TAX_ROUNDING_RULE = P_AP_TAX_ROUNDING_RULE,
47 AUTO_TAX_CALC_FLAG = P_AUTO_TAX_CALC_FLAG,
48 OFFSET_TAX_FLAG = P_OFFSET_TAX_FLAG,
49 VAT_CODE = P_VAT_CODE,
50 VAT_REGISTRATION_NUM = P_VAT_REGISTRATION_NUM
51 WHERE PARTY_SITE_ID = P_PARTY_SITE_ID;
52
53 END;
54
55 FUNCTION PARTY_TAX_PROF_TRD_PARTY(
56 p_subscription_guid IN RAW,
57 p_event IN OUT NOCOPY wf_event_t) RETURN VARCHAR2
58 IS
59 l_party_id HZ_PARTIES.party_id%TYPE;
60
61 l_tax_code ZX_PARTY_TAX_PROFILE.TAX_CLASSIFICATION_CODE%TYPE;
62 l_tax_header_level_flag ZX_PARTY_TAX_PROFILE.ROUNDING_LEVEL_CODE%TYPE;
63 l_tax_rounding_rule ZX_PARTY_TAX_PROFILE.ROUNDING_RULE_CODE%TYPE;
64
65 l_ptp_id ZX_PARTY_TAX_PROFILE.PARTY_TAX_PROFILE_ID%TYPE;
66
67 l_status_code VARCHAR2(80);
68
69 BEGIN
70
71 l_party_id :=p_event.GetValueForParameter('PARTY_ID');
72 l_ptp_id :=NULL;
73 l_tax_code :=NULL;
74 l_tax_header_level_flag :=NULL;
75 l_tax_rounding_rule :=NULL;
76
77 l_party_id :=p_event.GetValueForParameter('PARTY_ID');
78 l_ptp_id :=NULL;
79
80 l_tax_code :=NULL;
81 l_tax_header_level_flag :=NULL;
82 l_tax_rounding_rule :=NULL;
83
84 -- Create PTP If can not be updated.
85 BEGIN
86 MERGE INTO ZX_PARTY_TAX_PROFILE PTP
87 USING
88 (SELECT
89 decode (party.party_type, 'ORGANIZATION', 'THIRD_PARTY', 'PERSON', 'THIRD_PARTY') PARTY_TYPE_CODE,
90 L_TAX_CODE TAX_CLASSIFICATION_CODE,
91 L_PARTY_ID PARTY_ID,
92 FND_GLOBAL.Login_ID PROGRAM_LOGIN_ID ,
93 L_TAX_HEADER_LEVEL_FLAG ROUNDING_LEVEL_CODE,
94 L_TAX_ROUNDING_RULE ROUNDING_RULE_CODE,
95 party.tax_reference TAX_REFERENCE,
96 SYSDATE CREATION_DATE,
97 FND_GLOBAL.User_ID CREATED_BY,
98 SYSDATE LAST_UPDATE_DATE,
99 FND_GLOBAL.User_ID LAST_UPDATED_BY,
100 FND_GLOBAL.Login_ID LAST_UPDATE_LOGIN
101 FROM HZ_PARTIES party
102 WHERE PARTY_ID = l_party_id) PTY
103 ON (PTY.PARTY_ID = PTP.PARTY_ID)
104 WHEN MATCHED THEN UPDATE SET
105 PTP.REP_REGISTRATION_NUMBER = PTY.TAX_REFERENCE,
106 PTP.LAST_UPDATE_DATE=PTY.LAST_UPDATE_DATE,
107 PTP.LAST_UPDATED_BY=PTY.LAST_UPDATED_BY,
108 PTP.LAST_UPDATE_LOGIN=PTY.LAST_UPDATE_LOGIN,
109 PTP.OBJECT_VERSION_NUMBER = PTP.OBJECT_VERSION_NUMBER +1
110 WHEN NOT MATCHED THEN
111 INSERT (
112 PARTY_TYPE_CODE,
113 TAX_CLASSIFICATION_CODE,
114 PARTY_TAX_PROFILE_ID,
115 PARTY_ID,
116 PROGRAM_LOGIN_ID,
117 ROUNDING_LEVEL_CODE,
118 ROUNDING_RULE_CODE,
119 REP_REGISTRATION_NUMBER,
120 CREATION_DATE,
121 CREATED_BY,
122 LAST_UPDATE_DATE,
123 LAST_UPDATED_BY,
124 LAST_UPDATE_LOGIN,
125 OBJECT_VERSION_NUMBER)
126 VALUES (
127 PTY.PARTY_TYPE_CODE,
128 PTY.TAX_CLASSIFICATION_CODE,
129 ZX_PARTY_TAX_PROFILE_S.NEXTVAL,
130 PTY.PARTY_ID,
131 PTY.PROGRAM_LOGIN_ID,
132 PTY.ROUNDING_LEVEL_CODE,
133 PTY.ROUNDING_RULE_CODE,
134 PTY.TAX_REFERENCE,
135 PTY.CREATION_DATE,
136 PTY.CREATED_BY,
137 PTY.LAST_UPDATE_DATE,
138 PTY.LAST_UPDATED_BY,
139 PTY.LAST_UPDATE_LOGIN,
140 1);
141
142 EXCEPTION WHEN OTHERS THEN
143 RETURN (FND_API.G_RET_STS_ERROR);
144 END;
145
146 COMMIT;
147
148 RETURN (FND_API.G_RET_STS_SUCCESS);
149
150
151 END PARTY_TAX_PROF_TRD_PARTY;
152
153 FUNCTION PARTY_TAX_PROF_TRD_PTY_SITE(
154 p_subscription_guid IN RAW,
155 p_event IN OUT NOCOPY wf_event_t) RETURN VARCHAR2
156 IS
157
158 l_party_id HZ_PARTIES.party_id%TYPE;
159 l_party_site_id HZ_PARTY_SITES.party_site_id%TYPE;
160
161 l_tax_code ZX_PARTY_TAX_PROFILE.TAX_CLASSIFICATION_CODE%TYPE;
162 l_tax_header_level_flag ZX_PARTY_TAX_PROFILE.ROUNDING_LEVEL_CODE%TYPE;
163 l_tax_rounding_rule ZX_PARTY_TAX_PROFILE.ROUNDING_RULE_CODE%TYPE;
164
165 l_ptp_id ZX_PARTY_TAX_PROFILE.PARTY_TAX_PROFILE_ID%TYPE;
166
167 BEGIN
168 l_party_site_id:=p_event.GetValueForParameter('PARTY_SITE_ID');
169
170 l_ptp_id :=NULL;
171
172 l_tax_code :=NULL;
173 l_tax_header_level_flag :=NULL;
174 l_tax_rounding_rule :=NULL;
175
176 -- Create PTP If can not be updated.
177 BEGIN
178 MERGE INTO ZX_PARTY_TAX_PROFILE PTP
179 USING
180 (SELECT
181 'THIRD_PARTY_SITE' PARTY_TYPE_CODE,
182 L_TAX_CODE TAX_CLASSIFICATION_CODE,
183 L_PARTY_SITE_ID PARTY_SITE_ID,
184 FND_GLOBAL.Login_ID PROGRAM_LOGIN_ID ,
185 L_TAX_HEADER_LEVEL_FLAG ROUNDING_LEVEL_CODE,
186 L_TAX_ROUNDING_RULE ROUNDING_RULE_CODE,
187 NULL TAX_REFERENCE,
188 SYSDATE CREATION_DATE,
189 FND_GLOBAL.User_ID CREATED_BY,
190 SYSDATE LAST_UPDATE_DATE,
191 FND_GLOBAL.User_ID LAST_UPDATED_BY,
192 FND_GLOBAL.Login_ID LAST_UPDATE_LOGIN
193 FROM HZ_PARTY_SITES party
194 WHERE PARTY_SITE_ID = l_party_site_id) PTY
195 ON (PTY.PARTY_SITE_ID = PTP.PARTY_ID)
196 WHEN MATCHED THEN UPDATE SET
197 PTP.REP_REGISTRATION_NUMBER = PTY.TAX_REFERENCE,
198 PTP.LAST_UPDATE_DATE=PTY.LAST_UPDATE_DATE,
199 PTP.LAST_UPDATED_BY=PTY.LAST_UPDATED_BY,
200 PTP.LAST_UPDATE_LOGIN=PTY.LAST_UPDATE_LOGIN,
201 PTP.OBJECT_VERSION_NUMBER = PTP.OBJECT_VERSION_NUMBER +1
202 WHEN NOT MATCHED THEN
203 INSERT (
204 PARTY_TYPE_CODE,
205 TAX_CLASSIFICATION_CODE,
206 PARTY_TAX_PROFILE_ID,
207 PARTY_ID,
208 PROGRAM_LOGIN_ID,
209 ROUNDING_LEVEL_CODE,
210 ROUNDING_RULE_CODE,
211 REP_REGISTRATION_NUMBER,
212 CREATION_DATE,
213 CREATED_BY,
214 LAST_UPDATE_DATE,
215 LAST_UPDATED_BY,
216 LAST_UPDATE_LOGIN,
217 OBJECT_VERSION_NUMBER)
218 VALUES (
219 PTY.PARTY_TYPE_CODE,
220 PTY.TAX_CLASSIFICATION_CODE,
221 ZX_PARTY_TAX_PROFILE_S.NEXTVAL,
222 PTY.PARTY_SITE_ID,
223 PTY.PROGRAM_LOGIN_ID,
224 PTY.ROUNDING_LEVEL_CODE,
225 PTY.ROUNDING_RULE_CODE,
226 PTY.TAX_REFERENCE,
227 PTY.CREATION_DATE,
228 PTY.CREATED_BY,
229 PTY.LAST_UPDATE_DATE,
230 PTY.LAST_UPDATED_BY,
231 PTY.LAST_UPDATE_LOGIN,
232 1);
233
234 EXCEPTION WHEN OTHERS THEN
235 RETURN (FND_API.G_RET_STS_ERROR);
236 END;
237
238 COMMIT;
239
240 RETURN (FND_API.G_RET_STS_SUCCESS);
241
242 END PARTY_TAX_PROF_TRD_PTY_SITE;
243
244 PROCEDURE SYNCH_PARTY_IMPORT_PTP(
245 P_REQUEST_ID IN NUMBER)
246 IS
247 -- Create PTP If can not be updated.
248 BEGIN
249
250 MERGE INTO ZX_PARTY_TAX_PROFILE PTP
251 USING
252 (SELECT
253 decode (party.party_type, 'ORGANIZATION', 'THIRD_PARTY', 'PERSON', 'THIRD_PARTY') PARTY_TYPE_CODE,
254 NULL TAX_CLASSIFICATION_CODE,
255 party.party_id PARTY_ID,
256 FND_GLOBAL.Login_ID PROGRAM_LOGIN_ID ,
257 NULL ROUNDING_LEVEL_CODE,
258 NULL ROUNDING_RULE_CODE,
259 party.tax_reference TAX_REFERENCE,
260 SYSDATE CREATION_DATE,
261 FND_GLOBAL.User_ID CREATED_BY,
262 SYSDATE LAST_UPDATE_DATE,
263 FND_GLOBAL.User_ID LAST_UPDATED_BY,
264 FND_GLOBAL.Login_ID LAST_UPDATE_LOGIN
265 FROM HZ_PARTIES party
266 WHERE party.request_id = p_request_id
267 AND (party.party_type ='ORGANIZATION' OR party.party_type ='PERSON')) PTY
268 ON (PTY.PARTY_ID = PTP.PARTY_ID)
269 WHEN MATCHED THEN UPDATE SET
270 PTP.REP_REGISTRATION_NUMBER = PTY.TAX_REFERENCE,
271 PTP.LAST_UPDATE_DATE=PTY.LAST_UPDATE_DATE,
272 PTP.LAST_UPDATED_BY=PTY.LAST_UPDATED_BY,
273 PTP.LAST_UPDATE_LOGIN=PTY.LAST_UPDATE_LOGIN,
274 PTP.OBJECT_VERSION_NUMBER = PTP.OBJECT_VERSION_NUMBER +1
275 WHEN NOT MATCHED THEN
276 INSERT (
277 PARTY_TYPE_CODE,
278 TAX_CLASSIFICATION_CODE,
279 PARTY_TAX_PROFILE_ID,
280 PARTY_ID,
281 PROGRAM_LOGIN_ID,
282 ROUNDING_LEVEL_CODE,
283 ROUNDING_RULE_CODE,
284 REP_REGISTRATION_NUMBER,
285 CREATION_DATE,
286 CREATED_BY,
287 LAST_UPDATE_DATE,
288 LAST_UPDATED_BY,
289 LAST_UPDATE_LOGIN,
290 OBJECT_VERSION_NUMBER)
291 VALUES (
292 PTY.PARTY_TYPE_CODE,
293 PTY.TAX_CLASSIFICATION_CODE,
294 ZX_PARTY_TAX_PROFILE_S.NEXTVAL,
295 PTY.PARTY_ID,
296 PTY.PROGRAM_LOGIN_ID,
297 PTY.ROUNDING_LEVEL_CODE,
298 PTY.ROUNDING_RULE_CODE,
299 PTY.TAX_REFERENCE,
300 PTY.CREATION_DATE,
301 PTY.CREATED_BY,
302 PTY.LAST_UPDATE_DATE,
303 PTY.LAST_UPDATED_BY,
304 PTY.LAST_UPDATE_LOGIN,
305 1);
306
307 -- Import Party Sites.
308 MERGE INTO ZX_PARTY_TAX_PROFILE PTP
309 USING
310 (SELECT 'THIRD_PARTY_SITE' PARTY_TYPE_CODE,
311 NULL TAX_CLASSIFICATION_CODE,
312 party.party_site_id PARTY_ID,
313 FND_GLOBAL.Login_ID PROGRAM_LOGIN_ID ,
314 NULL ROUNDING_LEVEL_CODE,
315 NULL ROUNDING_RULE_CODE,
316 NULL TAX_REFERENCE,
317 SYSDATE CREATION_DATE,
318 FND_GLOBAL.User_ID CREATED_BY,
319 SYSDATE LAST_UPDATE_DATE,
320 FND_GLOBAL.User_ID LAST_UPDATED_BY,
321 FND_GLOBAL.Login_ID LAST_UPDATE_LOGIN
322 FROM HZ_PARTY_SITES party
323 WHERE party.request_id = p_request_id ) PTY
324 ON (PTY.PARTY_ID = PTP.PARTY_ID)
325 WHEN MATCHED THEN UPDATE SET
326 PTP.LAST_UPDATE_DATE=PTY.LAST_UPDATE_DATE,
327 PTP.LAST_UPDATED_BY=PTY.LAST_UPDATED_BY,
328 PTP.LAST_UPDATE_LOGIN=PTY.LAST_UPDATE_LOGIN,
329 PTP.OBJECT_VERSION_NUMBER = PTP.OBJECT_VERSION_NUMBER +1
330 WHEN NOT MATCHED THEN
331 INSERT (
332 PARTY_TYPE_CODE,
333 TAX_CLASSIFICATION_CODE,
334 PARTY_TAX_PROFILE_ID,
335 PARTY_ID,
336 PROGRAM_LOGIN_ID,
337 ROUNDING_LEVEL_CODE,
338 ROUNDING_RULE_CODE,
339 REP_REGISTRATION_NUMBER,
340 CREATION_DATE,
341 CREATED_BY,
342 LAST_UPDATE_DATE,
343 LAST_UPDATED_BY,
344 LAST_UPDATE_LOGIN,
345 OBJECT_VERSION_NUMBER)
346 VALUES (
347 PTY.PARTY_TYPE_CODE,
348 PTY.TAX_CLASSIFICATION_CODE,
349 ZX_PARTY_TAX_PROFILE_S.NEXTVAL,
350 PTY.PARTY_ID,
351 PTY.PROGRAM_LOGIN_ID,
352 PTY.ROUNDING_LEVEL_CODE,
353 PTY.ROUNDING_RULE_CODE,
354 PTY.TAX_REFERENCE,
355 PTY.CREATION_DATE,
356 PTY.CREATED_BY,
357 PTY.LAST_UPDATE_DATE,
358 PTY.LAST_UPDATED_BY,
359 PTY.LAST_UPDATE_LOGIN,
360 1);
361
362 COMMIT;
363
364 EXCEPTION WHEN OTHERS THEN
365 ROLLBACK;
366
367 END SYNCH_PARTY_IMPORT_PTP;
368
369
370 PROCEDURE SYNCH_PTP_CODE_ASSIGMENT(
371 P_PARTY_ID IN NUMBER,
372 P_CLASS_CATEGORY IN VARCHAR2,
373 P_CLASS_CODE IN VARCHAR2,
374 x_return_status OUT NOCOPY VARCHAR2,
375 x_msg_count OUT NOCOPY NUMBER,
376 x_msg_data OUT NOCOPY VARCHAR2) IS
377
378 BEGIN
379 MERGE INTO HZ_CODE_ASSIGNMENTS HZ USING
380 (SELECT 'ZX_PARTY_TAX_PROFILE' OWNER_TABLE_NAME,
381 PARTY_TAX_PROFILE_ID OWNER_TABLE_ID,
382 'VENDOR TYPE' CLASS_CATEGORY,
383 OBJECT_VERSION_NUMBER
384 FROM ZX_PARTY_TAX_PROFILE
385 WHERE Party_id = p_party_id
386 AND PARTY_TYPE_CODE ='THIRD_PARTY' ) PTP
387 ON (HZ.OWNER_TABLE_ID = PTP.OWNER_TABLE_ID
388 AND HZ.OWNER_TABLE_NAME = PTP.OWNER_TABLE_NAME
389 AND HZ.CLASS_CATEGORY=PTP.CLASS_CATEGORY)
390 WHEN MATCHED THEN UPDATE SET
391 HZ.CLASS_CODE = P_CLASS_CODE,
392 HZ.LAST_UPDATE_DATE=SYSDATE,
393 HZ.LAST_UPDATED_BY= FND_GLOBAL.User_ID,
394 HZ.LAST_UPDATE_LOGIN=FND_GLOBAL.Login_ID,
395 HZ.OBJECT_VERSION_NUMBER = HZ.OBJECT_VERSION_NUMBER +1
396 WHEN NOT MATCHED THEN
397 INSERT (
398 CODE_ASSIGNMENT_ID,
399 OWNER_TABLE_NAME,
400 OWNER_TABLE_ID,
401 CLASS_CATEGORY,
402 CLASS_CODE,
403 PRIMARY_FLAG,
404 CONTENT_SOURCE_TYPE,
405 ACTUAL_CONTENT_SOURCE,
406 IMPORTANCE_RANKING,
407 START_DATE_ACTIVE,
408 END_DATE_ACTIVE,
409 CREATED_BY,
410 CREATION_DATE,
411 LAST_UPDATE_LOGIN,
412 LAST_UPDATE_DATE,
413 LAST_UPDATED_BY ,
414 STATUS ,
415 OBJECT_VERSION_NUMBER,
416 CREATED_BY_MODULE,
417 APPLICATION_ID,
418 RANK,
419 REQUEST_ID,
420 PROGRAM_APPLICATION_ID,
421 PROGRAM_ID
422 )
423 VALUES(
424 HZ_CODE_ASSIGNMENTS_S.nextval,
425 PTP.OWNER_TABLE_NAME ,
426 PTP.OWNER_TABLE_ID,
427 PTP.CLASS_CATEGORY ,
428 P_CLASS_CODE,
429 'N' ,
430 'USER_ENTERED',
431 'USER_ENTERED',
432 null,
433 sysdate,
434 null,
435 FND_GLOBAL.User_ID ,
436 SYSDATE ,
437 FND_GLOBAL.Login_ID,
438 SYSDATE ,
439 FND_GLOBAL.User_ID,
440 NULL ,
441 1,
442 'EBTAX',
443 235,
444 null ,
445 null,
446 fnd_global.PROG_APPL_ID ,
447 fnd_global.CONC_PROGRAM_ID);
448
449 END SYNCH_PTP_CODE_ASSIGMENT;
450
451 END ZX_TCM_BES_REGISTRATION_PVT;