[Home] [Help]
PACKAGE BODY: APPS.AP_APXINUPD_XMLP_PKG
Source
1 PACKAGE BODY AP_APXINUPD_XMLP_PKG AS
2 /* $Header: APXINUPDB.pls 120.0 2007/12/27 08:03:28 vjaganat noship $ */
3 FUNCTION GET_BASE_CURR_DATA RETURN BOOLEAN IS
4 BEGIN
5 RETURN (TRUE);
6 RETURN NULL;
7 EXCEPTION
8 WHEN OTHERS THEN
9 RETURN (FALSE);
10 END GET_BASE_CURR_DATA;
11
12 FUNCTION CUSTOM_INIT RETURN BOOLEAN IS
13 BEGIN
14 UPDATE
15 AP_DUPLICATE_VENDORS
16 SET
17 PROCESS_FLAG = 'S'
18 WHERE PROCESS_FLAG = 'N';
19 RETURN (TRUE);
20 RETURN NULL;
21 EXCEPTION
22 WHEN OTHERS THEN
23 RETURN (TRUE);
24 END CUSTOM_INIT;
25
26 FUNCTION GET_COVER_PAGE_VALUES RETURN BOOLEAN IS
27 BEGIN
28 RETURN (TRUE);
29 RETURN NULL;
30 EXCEPTION
31 WHEN OTHERS THEN
32 RETURN (FALSE);
33 END GET_COVER_PAGE_VALUES;
34
35 FUNCTION GET_NLS_STRINGS RETURN BOOLEAN IS
36 NLS_ALL AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
37 NLS_YES FND_LOOKUPS.MEANING%TYPE;
38 NLS_NO FND_LOOKUPS.MEANING%TYPE;
39 BEGIN
40 NLS_ALL := '';
41 NLS_YES := '';
42 NLS_NO := '';
43 SELECT
44 LY.MEANING,
45 LN.MEANING,
46 LA.DISPLAYED_FIELD
47 INTO NLS_YES,NLS_NO,NLS_ALL
48 FROM
49 FND_LOOKUPS LY,
50 FND_LOOKUPS LN,
51 AP_LOOKUP_CODES LA
52 WHERE LY.LOOKUP_TYPE = 'YES_NO'
53 AND LY.LOOKUP_CODE = 'Y'
54 AND LN.LOOKUP_TYPE = 'YES_NO'
55 AND LN.LOOKUP_CODE = 'N'
56 AND LA.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
57 AND LA.LOOKUP_CODE = 'ALL';
58 C_NLS_YES := NLS_YES;
59 C_NLS_NO := NLS_NO;
60 C_NLS_ALL := NLS_ALL;
61 FND_MESSAGE.SET_NAME('SQLAP'
62 ,'AP_APPRVL_NO_DATA');
63 C_NLS_NO_DATA_EXISTS := '*** ' || C_NLS_NO_DATA_EXISTS || ' ***';
64 FND_MESSAGE.SET_NAME('SQLAP'
65 ,'AP_ALL_END_OF_REPORT');
66 C_NLS_END_OF_REPORT := '*** ' || C_NLS_END_OF_REPORT || ' ***';
67 RETURN (TRUE);
68 RETURN NULL;
69 EXCEPTION
70 WHEN OTHERS THEN
71 RETURN (FALSE);
72 END GET_NLS_STRINGS;
73
74 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
75 BEGIN
76 DECLARE
77 INIT_FAILURE EXCEPTION;
78 BEGIN
79 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
80 SELECT
81 SUBSTR(ARGUMENT1,INSTR(ARGUMENT1,'"',1,1)+1,(INSTR(ARGUMENT1,'"',1,2)-INSTR(ARGUMENT1,'"',1,1) -1)),
82 SUBSTR(ARGUMENT2,INSTR(ARGUMENT2,'"',1,1)+1,INSTR(ARGUMENT2,'"',1,2)-INSTR(ARGUMENT2,'"',1,1) -1),
83 SUBSTR(ARGUMENT3,INSTR(ARGUMENT3,'"',1,1)+1,INSTR(ARGUMENT3,'"',1,2)-INSTR(ARGUMENT3,'"',1,1) -1),
84 SUBSTR(ARGUMENT4,INSTR(ARGUMENT4,'"',1,1)+1,INSTR(ARGUMENT4,'"',1,2)-INSTR(ARGUMENT4,'"',1,1) -1),
85 SUBSTR(ARGUMENT5,INSTR(ARGUMENT5,'"',1,1)+1,INSTR(ARGUMENT5,'"',1,2)-INSTR(ARGUMENT5,'"',1,1) -1),
86 SUBSTR(ARGUMENT6,INSTR(ARGUMENT6,'"',1,1)+1,INSTR(ARGUMENT6,'"',1,2)-INSTR(ARGUMENT6,'"',1,1) -1),
87 SUBSTR(ARGUMENT7,INSTR(ARGUMENT7,'"',1,1)+1,INSTR(ARGUMENT7,'"',1,2)-INSTR(ARGUMENT7,'"',1,1) -1)
88 into P_LAST_UPDATED_BY_T,P_SET_OF_BOOKS_ID_T,P_FA_INSTALLED_FLAG_T,P_PO_INSTALLED_FLAG_T,
89 P_PA_INSTALLED_FLAG_T,P_INV_INSTALLED_FLAG_T,P_PN_INSTALLED_FLAG_T
90 FROM FND_CONCURRENT_REQUESTS
91 WHERE REQUEST_ID = P_CONC_REQUEST_ID;
92
93 IF (P_DEBUG_SWITCH = 'Y') THEN
94 NULL;
95 END IF;
96 IF (P_CHV_INSTALLED_FLAG = 'I') THEN
97 P_CHV_INSTALLED_FLAG := 'Y';
98 END IF;
99 IF (P_MRP_INSTALLED_FLAG in ('I','S')) THEN
100 P_MRP_INSTALLED_FLAG := 'Y';
101 END IF;
102 IF (GET_COMPANY_NAME <> TRUE) THEN
103 RAISE INIT_FAILURE;
104 END IF;
105 IF (GET_NLS_STRINGS <> TRUE) THEN
106 RAISE INIT_FAILURE;
107 END IF;
108 IF (P_DEBUG_SWITCH = 'Y') THEN
109 NULL;
110 END IF;
111 IF (GET_BASE_CURR_DATA <> TRUE) THEN
112 RAISE INIT_FAILURE;
113 END IF;
114 IF (CUSTOM_INIT <> TRUE) THEN
115 RAISE INIT_FAILURE;
116 END IF;
117 IF (P_DEBUG_SWITCH = 'Y') THEN
118 NULL;
119 END IF;
120 IF (DUPLICATE_TAX_SITES <> TRUE) THEN
121 RAISE INIT_FAILURE;
122 END IF;
123 IF (DUPLICATE_SITES <> TRUE) THEN
124 RAISE INIT_FAILURE;
125 END IF;
126 IF (P_DEBUG_SWITCH = 'Y') THEN
127 NULL;
128 END IF;
129 RETURN (TRUE);
130 EXCEPTION
131 WHEN OTHERS THEN
132 RAISE_APPLICATION_ERROR(-20101
133 ,NULL);
134 END;
135 RETURN (TRUE);
136 END BEFOREREPORT;
137
138 FUNCTION AFTERREPORT RETURN BOOLEAN IS
139 BEGIN
140 BEGIN
141 AP_VENDOR_PARTY_MERGE_PKG.OTHER_PRODUCTS_VENDORMERGE;
142 MO_GLOBAL.SET_POLICY_CONTEXT('M'
143 ,NULL);
144 UPDATE
145 AP_DUPLICATE_VENDORS
146 SET
147 PROCESS_FLAG = 'Y'
148 WHERE PROCESS_FLAG = 'S';
149 UPDATE
150 AP_DUPLICATE_VENDORS
151 SET
152 PROCESS_FLAG = 'N'
153 WHERE PROCESS_FLAG = 'D';
154 IF (P_DEBUG_SWITCH = 'Y') THEN
155 NULL;
156 END IF;
157 EXCEPTION
158 WHEN OTHERS THEN
159 RAISE_APPLICATION_ERROR(-20101
160 ,NULL);
161 END;
162 RETURN (TRUE);
163 END AFTERREPORT;
164
165 FUNCTION GET_COMPANY_NAME RETURN BOOLEAN IS
166 BEGIN
167 RETURN (TRUE);
168 RETURN NULL;
169 EXCEPTION
170 WHEN OTHERS THEN
171 RETURN (FALSE);
172 END GET_COMPANY_NAME;
173
174 FUNCTION GET_FLEXDATA RETURN BOOLEAN IS
175 BEGIN
176 RETURN (TRUE);
177 RETURN NULL;
178 EXCEPTION
179 WHEN OTHERS THEN
180 RETURN (FALSE);
181 END GET_FLEXDATA;
182
183 FUNCTION CALCULATE_RUN_TIME RETURN BOOLEAN IS
184 END_DATE DATE;
185 START_DATE DATE;
186 BEGIN
187 END_DATE := SYSDATE;
188 START_DATE := C_REPORT_START_DATE;
189 C_REPORT_RUN_TIME := TO_CHAR(TO_DATE('01/01/0001'
190 ,'DD/MM/YYYY') + ((END_DATE - START_DATE))
191 ,'HH24:MI:SS');
192 RETURN (TRUE);
193 RETURN NULL;
194 EXCEPTION
195 WHEN OTHERS THEN
196 RAISE_APPLICATION_ERROR(-20101
197 ,NULL);
198 END CALCULATE_RUN_TIME;
199
200 FUNCTION C_VENDOR_SITE_IDFORMULA(C_ORG_ID IN NUMBER
201 ,C_KEEP_SITE_FLAG IN VARCHAR2
202 ,C_VENDOR_ID IN NUMBER
203 ,C_VENDOR_SITE_CODE IN VARCHAR2
204 ,C_OLD_VENDOR_SITE_ID IN NUMBER) RETURN NUMBER IS
205 BEGIN
206 DECLARE
207 L_VENDOR_SITE_ID NUMBER;
208 BEGIN
209 MO_GLOBAL.SET_POLICY_CONTEXT('S'
210 ,C_ORG_ID);
211 IF C_KEEP_SITE_FLAG = 'Y' THEN
212 SELECT
213 VENDOR_SITE_ID
214 INTO L_VENDOR_SITE_ID
215 FROM
216 AP_SUPPLIER_SITES_ALL
217 WHERE VENDOR_ID = C_VENDOR_ID
218 AND VENDOR_SITE_CODE = C_VENDOR_SITE_CODE
219 AND ORG_ID = C_ORG_ID;
220 RETURN (L_VENDOR_SITE_ID);
221 ELSE
222 RETURN (C_OLD_VENDOR_SITE_ID);
223 END IF;
224 EXCEPTION
225 WHEN OTHERS THEN
226 RETURN (C_OLD_VENDOR_SITE_ID);
227 END;
228 RETURN NULL;
229 END C_VENDOR_SITE_IDFORMULA;
230
231 FUNCTION C_DUPLICATE_YES_NOFORMULA(C_INVOICE_ID IN NUMBER
232 ,C_INVOICE_NUM IN VARCHAR2
233 ,C_VENDOR_ID IN NUMBER
234 ,C_VENDOR_SITE_ID IN NUMBER
235 ,C_PARTY_ID IN NUMBER
236 ,C_PARTY_SITE_ID IN NUMBER
237 ,C_PAID_INVOICES_FLAG IN VARCHAR2
238 ,C_DUP_VENDOR_ID IN NUMBER
239 ,C_DUP_VENDOR_SITE_ID IN NUMBER) RETURN VARCHAR2 IS
240 BEGIN
241 DECLARE
242 L_DUPLICATES VARCHAR2(1);
243 STATUS VARCHAR2(1);
244 INDUSTRY VARCHAR2(1);
245 L_STAGE VARCHAR2(500);
246 L_STATUS VARCHAR2(1);
247 L_MSG_COUNT NUMBER;
248 L_MSG_DATA VARCHAR2(500);
249 L_RETURN_STATUS VARCHAR2(1);
250 L_ERR_MSG VARCHAR2(500);
251 API_ERROR EXCEPTION;
252 CURSOR L_INVOICE_MERGED_CUR IS
253 SELECT
254 AI.INVOICE_ID INVOICE_ID
255 FROM
256 AP_INVOICES_ALL AI
257 WHERE AI.INVOICE_ID = C_INVOICE_ID
258 AND EXISTS (
259 SELECT
260 'Y'
261 FROM
262 AP_INVOICE_DISTRIBUTIONS_ALL AID
263 WHERE AID.POSTED_FLAG = 'Y'
264 AND AID.INVOICE_ID = AI.INVOICE_ID );
265 CURSOR L_PAYMENT_MERGED_CUR IS
266 SELECT
267 AC.CHECK_ID CHECK_ID
268 FROM
269 AP_CHECKS_ALL AC
270 WHERE EXISTS (
271 SELECT
272 'Y'
273 FROM
274 AP_INVOICE_PAYMENTS_ALL AIP
275 WHERE AIP.CHECK_ID = AC.CHECK_ID
276 AND AIP.POSTED_FLAG = 'Y'
277 AND AIP.INVOICE_ID = C_INVOICE_ID );
278 BEGIN
279 SELECT
280 'Y'
281 INTO L_DUPLICATES
282 FROM
283 DUAL
284 WHERE EXISTS (
285 SELECT
286 'Duplicates exist in ap_invoices'
287 FROM
288 AP_INVOICES_ALL
289 WHERE INVOICE_NUM = C_INVOICE_NUM
290 AND VENDOR_ID = C_VENDOR_ID
291 AND INVOICE_ID <> C_INVOICE_ID )
292 OR EXISTS (
293 SELECT
294 'Duplicates exist in ap_history_invoices'
295 FROM
296 AP_HISTORY_INVOICES_ALL
297 WHERE INVOICE_NUM = C_INVOICE_NUM
298 AND VENDOR_ID = C_VENDOR_ID
299 AND INVOICE_ID <> C_INVOICE_ID );
300 RETURN (C_NLS_YES);
301 EXCEPTION
302 WHEN NO_DATA_FOUND THEN
303 BEGIN
304 UPDATE
305 AP_INVOICES_ALL
306 SET
307 VENDOR_ID = C_VENDOR_ID
308 ,VENDOR_SITE_ID = C_VENDOR_SITE_ID
309 ,PARTY_ID = C_PARTY_ID
310 ,PARTY_SITE_ID = C_PARTY_SITE_ID
311 ,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
312 ,LAST_UPDATE_DATE = SYSDATE
313 WHERE INVOICE_ID = C_INVOICE_ID;
314 AP_DBI_PKG.MAINTAIN_DBI_SUMMARY(P_TABLE_NAME => 'AP_INVOICES'
315 ,P_OPERATION => 'U'
316 ,P_KEY_VALUE1 => C_INVOICE_ID
317 ,P_CALLING_SEQUENCE => 'Merge Vendors ');
318 IF (C_PAID_INVOICES_FLAG = 'Y') THEN
319 UPDATE
320 AP_CHECKS_ALL
321 SET
322 VENDOR_ID = C_VENDOR_ID
323 ,VENDOR_SITE_ID = C_VENDOR_SITE_ID
324 ,PARTY_ID = C_PARTY_ID
325 ,PARTY_SITE_ID = C_PARTY_SITE_ID
326 ,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
327 ,LAST_UPDATE_DATE = SYSDATE
328 WHERE VENDOR_ID = C_DUP_VENDOR_ID
329 AND CHECK_ID in (
330 SELECT
331 CHECK_ID
332 FROM
333 AP_INVOICE_PAYMENTS
334 WHERE INVOICE_ID = C_INVOICE_ID );
335 END IF;
336 IF P_PA_INSTALLED_FLAG_T = 'Y' THEN
337 PA_AP_INTEGRATION.UPD_PA_DETAILS_SUPPLIER_MERGE(P_OLD_VENDOR_ID => C_DUP_VENDOR_ID
338 ,P_NEW_VENDOR_ID => C_VENDOR_ID
339 ,P_PAID_INV_FLAG => C_PAID_INVOICES_FLAG
340 ,X_STAGE => L_STAGE
341 ,X_STATUS => L_STATUS);
342 IF (P_DEBUG_SWITCH = 'Y') THEN
343 NULL;
344 END IF;
345 END IF;
346 IF P_INV_INSTALLED_FLAG_T = 'Y' THEN
347 INV_VENDORMERGE_GRP.MERGE_VENDOR(P_API_VERSION => 1.0
348 ,P_INIT_MSG_LIST => 'F'
349 ,P_COMMIT => 'F'
350 ,P_VALIDATION_LEVEL => 100
351 ,P_RETURN_STATUS => L_RETURN_STATUS
352 ,P_MSG_COUNT => L_MSG_COUNT
353 ,P_MSG_DATA => L_MSG_DATA
354 ,P_VENDOR_ID => C_VENDOR_ID
355 ,P_DUP_VENDOR_ID => C_DUP_VENDOR_ID
356 ,P_VENDOR_SITE_ID => C_VENDOR_SITE_ID
357 ,P_DUP_VENDOR_SITE_ID => C_DUP_VENDOR_SITE_ID
358 ,P_PARTY_ID => NULL
359 ,P_DUP_PARTY_ID => NULL
360 ,P_PARTY_SITE_ID => NULL
361 ,P_DUP_PARTY_SITE_ID => NULL);
362 IF L_RETURN_STATUS <> 'S' THEN
363 IF L_MSG_COUNT > 0 THEN
364 FOR i IN 1 .. L_MSG_COUNT LOOP
365 L_MSG_DATA := FND_MSG_PUB.GET(-2
366 ,'T');
367 L_ERR_MSG := FND_MESSAGE.GET;
368 FND_FILE.PUT_LINE(1
369 ,L_ERR_MSG);
370 END LOOP;
371 END IF;
372 RAISE API_ERROR;
373 END IF;
374 END IF;
375 IF (P_PN_INSTALLED_FLAG_T = 'Y') THEN
376 PN_VENDORMERGE_GRP.MERGE_VENDOR(P_API_VERSION => 1.0
377 ,P_INIT_MSG_LIST => 'F'
378 ,P_COMMIT => 'F'
379 ,P_VALIDATION_LEVEL => 100
380 ,X_RETURN_STATUS => L_RETURN_STATUS
381 ,X_MSG_COUNT => L_MSG_COUNT
382 ,X_MSG_DATA => L_MSG_DATA
383 ,P_VENDOR_ID => C_VENDOR_ID
384 ,P_VENDOR_SITE_ID => C_VENDOR_SITE_ID
385 ,P_DUP_VENDOR_ID => C_DUP_VENDOR_ID
386 ,P_DUP_VENDOR_SITE_ID => C_DUP_VENDOR_SITE_ID
387 ,P_LAST_UPDATED_BY => P_LAST_UPDATED_BY_T);
388 IF L_RETURN_STATUS <> 'S' THEN
389 IF L_MSG_COUNT > 0 THEN
390 FOR i IN 1 .. L_MSG_COUNT LOOP
391 L_MSG_DATA := FND_MSG_PUB.GET(-2
392 ,'T');
393 L_ERR_MSG := FND_MESSAGE.GET;
394 FND_FILE.PUT_LINE(1
395 ,L_ERR_MSG);
396 END LOOP;
397 END IF;
398 RAISE API_ERROR;
399 END IF;
400 END IF;
401 RETURN (C_NLS_NO);
402 END;
403 END;
404 RETURN NULL;
405 EXCEPTION
406 WHEN OTHERS THEN
407 APP_EXCEPTION.RAISE_EXCEPTION;
408 RETURN NULL;
409 END C_DUPLICATE_YES_NOFORMULA;
410
411 FUNCTION C_DUP_INVOICEFORMULA(C_DUPLICATE_YES_NO IN VARCHAR2) RETURN NUMBER IS
412 BEGIN
413 BEGIN
414 IF C_DUPLICATE_YES_NO = C_NLS_YES THEN
415 RETURN (1);
416 ELSE
417 RETURN (0);
418 END IF;
419 END;
420 RETURN NULL;
421 END C_DUP_INVOICEFORMULA;
422
423 FUNCTION C_NON_DUP_INVOICEFORMULA(C_DUPLICATE_YES_NO IN VARCHAR2) RETURN NUMBER IS
424 BEGIN
425 BEGIN
426 IF C_DUPLICATE_YES_NO = C_NLS_NO THEN
427 RETURN (1);
428 ELSE
429 RETURN (0);
430 END IF;
431 END;
432 RETURN NULL;
433 END C_NON_DUP_INVOICEFORMULA;
434
435 FUNCTION C_PAID_AMOUNTFORMULA(C_DUPLICATE_YES_NO IN VARCHAR2
436 ,C_PAYMENT_STATUS_FLAG IN VARCHAR2
437 ,C_AMOUNT_PAID IN NUMBER
438 ,C_DISCOUNT_AMOUNT_TAKEN IN NUMBER) RETURN NUMBER IS
439 BEGIN
440 DECLARE
441 L_PAID_AMOUNT NUMBER;
442 BEGIN
443 IF C_DUPLICATE_YES_NO = C_NLS_YES THEN
444 RETURN (0);
445 ELSE
446 IF C_PAYMENT_STATUS_FLAG not in ('N','Y') THEN
447 L_PAID_AMOUNT := C_AMOUNT_PAID + C_DISCOUNT_AMOUNT_TAKEN;
448 RETURN (L_PAID_AMOUNT);
449 ELSIF C_PAYMENT_STATUS_FLAG = 'Y' THEN
450 RETURN (C_AMOUNT_PAID);
451 ELSE
452 RETURN (0);
453 END IF;
454 END IF;
455 END;
456 RETURN NULL;
457 END C_PAID_AMOUNTFORMULA;
458
459 FUNCTION C_CHANGED_AMOUNTFORMULA(C_DUPLICATE_YES_NO IN VARCHAR2
460 ,C_INVOICE_AMOUNT IN NUMBER) RETURN NUMBER IS
461 BEGIN
462 BEGIN
463 IF C_DUPLICATE_YES_NO = C_NLS_NO THEN
464 RETURN (C_INVOICE_AMOUNT);
465 ELSE
466 RETURN (0);
467 END IF;
468 END;
469 RETURN NULL;
470 END C_CHANGED_AMOUNTFORMULA;
471
472 FUNCTION C_PAID_INVOICEFORMULA(C_PAYMENT_STATUS_FLAG IN VARCHAR2) RETURN NUMBER IS
473 BEGIN
474 BEGIN
475 IF C_PAYMENT_STATUS_FLAG = 'Y' THEN
476 RETURN (1);
477 ELSE
478 RETURN (0);
479 END IF;
480 END;
481 RETURN NULL;
482 END C_PAID_INVOICEFORMULA;
483
484 FUNCTION C_UNPAID_INVOICEFORMULA(C_PAYMENT_STATUS_FLAG IN VARCHAR2) RETURN NUMBER IS
485 BEGIN
486 BEGIN
487 IF C_PAYMENT_STATUS_FLAG = 'N' THEN
488 RETURN (1);
489 ELSE
490 RETURN (0);
491 END IF;
492 END;
493 RETURN NULL;
494 END C_UNPAID_INVOICEFORMULA;
495
496 FUNCTION C_DUP_RECURRING_COUNTFORMULA(C_DUP_VENDOR_ID IN NUMBER
497 ,C_DUP_VENDOR_SITE_ID IN NUMBER
498 ,C_VENDOR_ID IN NUMBER
499 ,C_VENDOR_SITE_ID IN NUMBER) RETURN NUMBER IS
500 BEGIN
501 DECLARE
502 L_NUM_DUP_RECURRING NUMBER;
503 BEGIN
504 SELECT
505 COUNT(*)
506 INTO L_NUM_DUP_RECURRING
507 FROM
508 AP_RECURRING_PAYMENTS_ALL ARP1
509 WHERE VENDOR_ID = C_DUP_VENDOR_ID
510 AND VENDOR_SITE_ID = C_DUP_VENDOR_SITE_ID
511 AND EXISTS (
512 SELECT
513 'this would be a duplicate'
514 FROM
515 AP_RECURRING_PAYMENTS_ALL ARP2
516 WHERE ARP2.VENDOR_ID = C_VENDOR_ID
517 AND ARP2.VENDOR_SITE_ID = C_VENDOR_SITE_ID
518 AND ARP2.RECURRING_PAY_NUM = ARP1.RECURRING_PAY_NUM );
519 RETURN (L_NUM_DUP_RECURRING);
520 END;
521 RETURN NULL;
522 END C_DUP_RECURRING_COUNTFORMULA;
523
524 FUNCTION C_RECURRING_COUNTFORMULA(C_VENDOR_ID IN NUMBER
525 ,C_VENDOR_SITE_ID IN NUMBER
526 ,C_DUP_VENDOR_ID IN NUMBER
527 ,C_DUP_VENDOR_SITE_ID IN NUMBER) RETURN NUMBER IS
528 pragma autonomous_transaction;
529 BEGIN
530 DECLARE
531 L_NUM_RECURRING NUMBER;
532 BEGIN
533 UPDATE
534 AP_RECURRING_PAYMENTS arp1
535 SET
536 VENDOR_ID = C_VENDOR_ID
537 ,VENDOR_SITE_ID = C_VENDOR_SITE_ID
538 ,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
539 ,LAST_UPDATE_DATE = SYSDATE
540 WHERE VENDOR_ID = C_DUP_VENDOR_ID
541 AND VENDOR_SITE_ID = C_DUP_VENDOR_SITE_ID
542 AND NOT EXISTS (
543 SELECT
544 'this would be a duplicate'
545 FROM
546 AP_RECURRING_PAYMENTS ARP2
547 WHERE ARP2.VENDOR_ID = C_VENDOR_ID
548 AND ARP2.VENDOR_SITE_ID = C_VENDOR_SITE_ID
549 AND ARP2.RECURRING_PAY_NUM = ARP1.RECURRING_PAY_NUM );
550 L_NUM_RECURRING := SQL%ROWCOUNT;
551 commit;
552 RETURN (L_NUM_RECURRING);
553 END;
554 RETURN NULL;
555 END C_RECURRING_COUNTFORMULA;
556
557 FUNCTION C_UPDATE_DUP_VENDORFORMULA(C_DUP_INVOICE_COUNT IN NUMBER
558 ,C_DUP_RECURRING_COUNT IN NUMBER
559 ,C_KEEP_SITE_FLAG IN VARCHAR2
560 ,C_VENDOR_SITE_ID IN NUMBER
561 ,C_ENTRY_ID IN NUMBER
562 ,C_PAID_INVOICE_COUNT_RESET IN NUMBER
563 ,C_UNPAID_INVOICE_COUNT_RESET IN NUMBER
564 ,C_DUP_VENDOR_ID IN NUMBER
565 ,C_DUP_VENDOR_SITE_ID IN NUMBER
566 ,C_VENDOR_ID IN NUMBER
567 ,C_PARTY_SITE_ID IN NUMBER
568 ,C_DUP_PARTY_SITE_ID IN NUMBER) RETURN NUMBER IS
569 L_MERGING_PPSITE VARCHAR2(1);
570 L_PPSITES NUMBER;
571 STATUS VARCHAR2(1);
572 INDUSTRY VARCHAR2(1);
573 L_PROCESS AP_DUPLICATE_VENDORS_ALL.PROCESS%TYPE;
574 L_MSG_DATA VARCHAR2(2000);
575 L_MSG_COUNT NUMBER;
576 L_RETURN_STATUS VARCHAR2(1);
577 V_NEW_VENDOR_ID NUMBER;
578 V_OLD_VENDOR_ID NUMBER;
579 V_NEW_VENDOR_NAME AP_SUPPLIERS.VENDOR_NAME%TYPE;
580 V_OLD_VENDOR_NAME AP_SUPPLIERS.VENDOR_NAME%TYPE;
581 V_DUP_HIST_INVOICE VARCHAR2(50);
582 V_INV_NUM_LENGTH NUMBER;
583 V_OLD_VENDOR_NUM_LENGTH NUMBER;
584 V_NEW_VENDOR_NUM_LENGTH NUMBER;
585 V_OLD_VENDOR_NUM VARCHAR2(30);
586 V_NEW_VENDOR_NUM VARCHAR2(30);
587 V_OLD_COMB_LENGTH NUMBER;
588 V_NEW_COMB_LENGTH NUMBER;
589 V_DIFF NUMBER;
590 CURSOR C_DUP_HISTORY IS
591 SELECT
592 AHI1.INVOICE_NUM
593 FROM
594 AP_HISTORY_INVOICES_ALL AHI1
595 WHERE VENDOR_ID = V_OLD_VENDOR_ID
596 AND EXISTS (
597 SELECT
598 'The merge-to supplier already has this invoice num'
599 FROM
600 AP_HISTORY_INVOICES_ALL AHI2
601 WHERE AHI2.VENDOR_ID = V_NEW_VENDOR_ID
602 AND AHI2.INVOICE_NUM = AHI1.INVOICE_NUM );
603 pragma autonomous_transaction;
604
605 BEGIN
606 P_PRIMARY_PAY := 'N';
607 IF (C_DUP_INVOICE_COUNT + C_DUP_RECURRING_COUNT > 0) THEN
608 IF C_KEEP_SITE_FLAG = 'Y' THEN
609 UPDATE
610 AP_DUPLICATE_VENDORS_ALL
611 SET
612 VENDOR_SITE_ID = C_VENDOR_SITE_ID
613 ,KEEP_SITE_FLAG = 'N'
614 WHERE ENTRY_ID = C_ENTRY_ID;
615 END IF;
616 UPDATE
617 AP_DUPLICATE_VENDORS_ALL
618 SET
622 UPDATE
619 PROCESS_FLAG = 'D'
620 WHERE ENTRY_ID = C_ENTRY_ID;
621 ELSE
623 AP_DUPLICATE_VENDORS_ALL
624 SET
625 LAST_UPDATE_DATE = SYSDATE
626 ,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
627 ,NUMBER_PAID_INVOICES = NVL(NUMBER_PAID_INVOICES
628 ,0) + C_PAID_INVOICE_COUNT_RESET
629 ,NUMBER_UNPAID_INVOICES = NVL(NUMBER_UNPAID_INVOICES
630 ,0) + C_UNPAID_INVOICE_COUNT_RESET
631 WHERE ENTRY_ID = C_ENTRY_ID;
632 END IF;
633 IF P_PRIMARY_PAY = 'N' THEN
634 SELECT
635 NVL(PRIMARY_PAY_SITE_FLAG
636 ,'N')
637 INTO L_MERGING_PPSITE
638 FROM
639 AP_SUPPLIER_SITES_ALL
640 WHERE VENDOR_ID = C_DUP_VENDOR_ID
641 AND VENDOR_SITE_ID = C_DUP_VENDOR_SITE_ID;
642 IF ((L_MERGING_PPSITE = 'Y') AND (C_VENDOR_ID = C_DUP_VENDOR_ID)) THEN
643 UPDATE
644 AP_SUPPLIER_SITES_ALL
645 SET
646 PRIMARY_PAY_SITE_FLAG = 'Y'
647 WHERE VENDOR_ID = C_VENDOR_ID
648 AND VENDOR_SITE_ID = C_VENDOR_SITE_ID;
649 P_PRIMARY_PAY := 'Y';
650 END IF;
651 END IF;
652 IF NVL(C_KEEP_SITE_FLAG
653 ,'N') <> 'Y' THEN
654 UPDATE
655 AP_SUPPLIER_CONTACTS
656 SET
657 LAST_UPDATE_DATE = SYSDATE
658 ,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
659 ,ORG_PARTY_SITE_ID = C_PARTY_SITE_ID
660 WHERE ORG_PARTY_SITE_ID = C_DUP_PARTY_SITE_ID;
661 END IF;
662 V_NEW_VENDOR_ID := C_VENDOR_ID;
663 V_OLD_VENDOR_ID := C_DUP_VENDOR_ID;
664 SELECT
665 PV1.VENDOR_NAME,
666 PV1.SEGMENT1,
667 LENGTH(PV1.SEGMENT1),
668 PV2.VENDOR_NAME,
669 PV2.SEGMENT1,
670 LENGTH(PV2.SEGMENT1)
671 INTO V_OLD_VENDOR_NAME,V_OLD_VENDOR_NUM,V_OLD_VENDOR_NUM_LENGTH,
672 V_NEW_VENDOR_NAME,V_NEW_VENDOR_NUM,V_NEW_VENDOR_NUM_LENGTH
673 FROM
674 AP_SUPPLIERS PV1,
675 AP_SUPPLIERS PV2
676 WHERE PV1.VENDOR_ID = V_OLD_VENDOR_ID
677 AND PV2.VENDOR_ID = V_NEW_VENDOR_ID;
678 OPEN C_DUP_HISTORY;
679 LOOP
680 FETCH C_DUP_HISTORY
681 INTO V_DUP_HIST_INVOICE;
682 EXIT WHEN C_DUP_HISTORY%NOTFOUND;
683 V_INV_NUM_LENGTH := LENGTH(V_DUP_HIST_INVOICE);
684 V_OLD_COMB_LENGTH := V_INV_NUM_LENGTH + V_OLD_VENDOR_NUM_LENGTH + 1;
685 V_NEW_COMB_LENGTH := V_INV_NUM_LENGTH + V_NEW_VENDOR_NUM_LENGTH + 1;
686 IF V_INV_NUM_LENGTH = 50 THEN
687 NULL;
688 ELSE
689 IF V_OLD_COMB_LENGTH <= 50 THEN
690 UPDATE
691 AP_HISTORY_INVOICES_ALL
692 SET
693 INVOICE_NUM = V_DUP_HIST_INVOICE || '*' || V_OLD_VENDOR_NUM
694 WHERE VENDOR_ID = V_OLD_VENDOR_ID
695 AND INVOICE_NUM = V_DUP_HIST_INVOICE;
696 ELSE
697 V_DIFF := V_OLD_COMB_LENGTH - 50;
698 V_OLD_VENDOR_NUM := SUBSTR(V_OLD_VENDOR_NUM
699 ,1
700 ,V_OLD_VENDOR_NUM_LENGTH - V_DIFF);
701 UPDATE
702 AP_HISTORY_INVOICES_ALL
703 SET
704 INVOICE_NUM = V_DUP_HIST_INVOICE || '*' || V_OLD_VENDOR_NUM
705 WHERE VENDOR_ID = V_OLD_VENDOR_ID
706 AND INVOICE_NUM = V_DUP_HIST_INVOICE;
707 END IF;
708 IF V_OLD_COMB_LENGTH <= 50 THEN
709 UPDATE
710 AP_HISTORY_INVOICES_ALL
711 SET
712 INVOICE_NUM = V_DUP_HIST_INVOICE || '-' || V_NEW_VENDOR_NUM
713 WHERE VENDOR_ID = V_NEW_VENDOR_ID
714 AND INVOICE_NUM = V_DUP_HIST_INVOICE;
715 ELSE
716 V_DIFF := V_NEW_COMB_LENGTH - 50;
717 V_NEW_VENDOR_NUM := SUBSTR(V_NEW_VENDOR_NUM
718 ,1
719 ,V_NEW_VENDOR_NUM_LENGTH - V_DIFF);
720 UPDATE
721 AP_HISTORY_INVOICES_ALL
722 SET
723 INVOICE_NUM = V_DUP_HIST_INVOICE || '-' || V_NEW_VENDOR_NUM
724 WHERE VENDOR_ID = V_NEW_VENDOR_ID
725 AND INVOICE_NUM = V_DUP_HIST_INVOICE;
726 END IF;
727 END IF;
728 END LOOP;
729 UPDATE
730 AP_HISTORY_INVOICES_ALL ahi
731 SET
732 VENDOR_ID = V_NEW_VENDOR_ID
733 WHERE VENDOR_ID = V_OLD_VENDOR_ID
734 AND NOT EXISTS (
735 SELECT
736 'Invoice Num exists as an invoice'
737 FROM
738 AP_INVOICES_ALL AI
739 WHERE AI.INVOICE_NUM = AHI.INVOICE_NUM
740 AND AI.VENDOR_ID = V_NEW_VENDOR_ID );
741 IGI_VENDOR_MERGE_GRP.MERGE_VENDOR(P_API_VERSION => 1.0
742 ,P_INIT_MSG_LIST => NULL
743 ,P_COMMIT => NULL
744 ,P_VALIDATION_LEVEL => NULL
745 ,X_RETURN_STATUS => L_RETURN_STATUS
746 ,X_MSG_COUNT => L_MSG_COUNT
747 ,X_MSG_DATA => L_MSG_DATA
748 ,P_NEW_VENDOR_ID => C_VENDOR_ID
749 ,P_NEW_VENDOR_SITE_ID => C_VENDOR_SITE_ID
750 ,P_OLD_VENDOR_ID => C_DUP_VENDOR_ID
751 ,P_OLD_VENDOR_SITE_ID => C_DUP_VENDOR_SITE_ID);
752 commit;
753 RETURN NULL;
754 END C_UPDATE_DUP_VENDORFORMULA;
755
756 FUNCTION DUPLICATE_SITES RETURN BOOLEAN IS
757 L_NUMBER_OF_OTHER_SITES NUMBER;
758 NEW_VENDOR_SITE_ID NUMBER;
759 L_DESTINATION_VENDOR_TAX_SITES NUMBER := 0;
760 L_DUPLICATE_SITE_FLAG VARCHAR2(10);
761 L_DUPLICATE_TAX_SITES VARCHAR2(10);
765 L_ORG_ID NUMBER;
762 L_TARGET_PRIMARY_PAY_SITES NUMBER := 0;
763 L_DUP_PAY_SITE_FLAG VARCHAR2(1);
764 L_DUP_PRIMARY_PAY_SITES VARCHAR2(1);
766 L_VENDOR_SITE_REC AP_VENDOR_PUB_PKG.R_VENDOR_SITE_REC_TYPE;
767 L_RETURN_STATUS VARCHAR2(1);
768 L_MSG_COUNT NUMBER;
769 L_MSG_DATA VARCHAR2(500);
770 L_VENDOR_SITE_ID NUMBER;
771 L_PARTY_SITE_ID NUMBER;
772 L_LOCATION_ID NUMBER;
773 L_ERR_MSG VARCHAR2(500);
774 API_ERROR EXCEPTION;
775 BEGIN
776 FOR c1 IN (SELECT
777 DV.DUPLICATE_VENDOR_SITE_ID,
778 DV.KEEP_SITE_FLAG,
779 DV.VENDOR_ID,
780 DV.DUPLICATE_VENDOR_ID,
781 DV.ORG_ID,
782 VS.VENDOR_SITE_CODE,
783 VS.PARTY_SITE_ID
784 FROM
785 AP_DUPLICATE_VENDORS_ALL DV,
786 AP_SUPPLIER_SITES_ALL VS
787 WHERE PROCESS_FLAG = 'S'
788 AND VS.VENDOR_SITE_ID = DV.DUPLICATE_VENDOR_SITE_ID
789 AND VS.ORG_ID = DV.ORG_ID) LOOP
790 SELECT
791 COUNT(VENDOR_SITE_ID)
792 INTO L_NUMBER_OF_OTHER_SITES
793 FROM
794 AP_SUPPLIER_SITES_ALL
795 WHERE VENDOR_ID = C1.DUPLICATE_VENDOR_ID
796 AND VENDOR_SITE_ID <> C1.DUPLICATE_VENDOR_SITE_ID
797 AND NVL(INACTIVE_DATE
798 ,SYSDATE + 1) > sysdate;
799 IF L_NUMBER_OF_OTHER_SITES = 0 THEN
800 UPDATE
801 AP_SUPPLIERS
802 SET
803 END_DATE_ACTIVE = SYSDATE
804 ,LAST_UPDATE_DATE = SYSDATE
805 ,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
806 WHERE VENDOR_ID = C1.DUPLICATE_VENDOR_ID;
807 END IF;
808 IF C1.KEEP_SITE_FLAG = 'Y' THEN
809 SELECT
810 PO_VENDOR_SITES_S.NEXTVAL
811 INTO NEW_VENDOR_SITE_ID
812 FROM
813 SYS.DUAL;
814 SELECT
815 COUNT(*)
816 INTO L_TARGET_PRIMARY_PAY_SITES
817 FROM
818 AP_SUPPLIER_SITES_ALL PVS
819 WHERE PVS.VENDOR_ID = C1.VENDOR_ID
820 AND PVS.ORG_ID = C1.ORG_ID
821 AND NVL(PRIMARY_PAY_SITE_FLAG
822 ,'N') = 'Y'
823 AND NVL(INACTIVE_DATE
824 ,SYSDATE + 1) > sysdate;
825 SELECT
826 PVS.PRIMARY_PAY_SITE_FLAG
827 INTO L_DUP_PAY_SITE_FLAG
828 FROM
829 AP_SUPPLIER_SITES_ALL PVS
830 WHERE PVS.VENDOR_SITE_ID = C1.DUPLICATE_VENDOR_SITE_ID;
831 IF L_TARGET_PRIMARY_PAY_SITES > 0 AND L_DUP_PAY_SITE_FLAG = 'Y' THEN
832 L_DUP_PRIMARY_PAY_SITES := 'Y';
833 ELSE
834 L_DUP_PRIMARY_PAY_SITES := 'N';
835 END IF;
836 SELECT
837 COUNT(*)
838 INTO L_DESTINATION_VENDOR_TAX_SITES
839 FROM
840 AP_SUPPLIER_SITES_ALL PVS
841 WHERE PVS.VENDOR_ID = C1.VENDOR_ID
842 AND PVS.ORG_ID = C1.ORG_ID
843 AND NVL(TAX_REPORTING_SITE_FLAG
844 ,'N') = 'Y'
845 AND NVL(INACTIVE_DATE
846 ,SYSDATE + 1) > sysdate;
847 SELECT
848 PVS.TAX_REPORTING_SITE_FLAG
849 INTO L_DUPLICATE_SITE_FLAG
850 FROM
851 AP_SUPPLIER_SITES_ALL PVS
852 WHERE PVS.VENDOR_SITE_ID = C1.DUPLICATE_VENDOR_SITE_ID;
853 IF L_DESTINATION_VENDOR_TAX_SITES > 0 AND L_DUPLICATE_SITE_FLAG = 'Y' THEN
854 L_DUPLICATE_TAX_SITES := 'Y';
855 ELSE
856 L_DUPLICATE_TAX_SITES := 'N';
857 END IF;
858 SELECT
859 AREA_CODE,
860 PHONE,
861 CUSTOMER_NUM,
862 SHIP_TO_LOCATION_ID,
863 BILL_TO_LOCATION_ID,
864 SHIP_VIA_LOOKUP_CODE,
865 FREIGHT_TERMS_LOOKUP_CODE,
866 FOB_LOOKUP_CODE,
867 INACTIVE_DATE,
868 FAX,
869 FAX_AREA_CODE,
870 TELEX,
871 TERMS_DATE_BASIS,
872 DISTRIBUTION_SET_ID,
873 ACCTS_PAY_CODE_COMBINATION_ID,
874 PREPAY_CODE_COMBINATION_ID,
875 PAY_GROUP_LOOKUP_CODE,
876 PAYMENT_PRIORITY,
877 TERMS_ID,
878 INVOICE_AMOUNT_LIMIT,
879 PAY_DATE_BASIS_LOOKUP_CODE,
880 ALWAYS_TAKE_DISC_FLAG,
881 INVOICE_CURRENCY_CODE,
882 PAYMENT_CURRENCY_CODE,
883 VENDOR_SITE_ID,
884 SYSDATE,
885 P_LAST_UPDATED_BY_T,
886 C1.VENDOR_ID,
887 VENDOR_SITE_CODE,
888 VENDOR_SITE_CODE_ALT,
889 PURCHASING_SITE_FLAG,
890 RFQ_ONLY_SITE_FLAG,
891 PAY_SITE_FLAG,
892 ATTENTION_AR_FLAG,
893 HOLD_ALL_PAYMENTS_FLAG,
894 HOLD_FUTURE_PAYMENTS_FLAG,
895 HOLD_REASON,
896 HOLD_UNMATCHED_INVOICES_FLAG,
897 DECODE(L_DUPLICATE_TAX_SITES
898 ,'Y'
899 ,'N'
900 ,TAX_REPORTING_SITE_FLAG),
901 ATTRIBUTE_CATEGORY,
902 ATTRIBUTE1,
903 ATTRIBUTE2,
904 ATTRIBUTE3,
905 ATTRIBUTE4,
906 ATTRIBUTE5,
907 ATTRIBUTE6,
908 ATTRIBUTE7,
909 ATTRIBUTE8,
910 ATTRIBUTE9,
911 ATTRIBUTE10,
912 ATTRIBUTE11,
913 ATTRIBUTE12,
914 ATTRIBUTE13,
915 ATTRIBUTE14,
916 ATTRIBUTE15,
917 VALIDATION_NUMBER,
918 EXCLUDE_FREIGHT_FROM_DISCOUNT,
919 BANK_CHARGE_BEARER,
920 ORG_ID,
924 DEFAULT_PAY_SITE_ID,
921 CHECK_DIGITS,
922 ALLOW_AWT_FLAG,
923 AWT_GROUP_ID,
925 PAY_ON_CODE,
926 PAY_ON_RECEIPT_SUMMARY_CODE,
927 GLOBAL_ATTRIBUTE_CATEGORY,
928 GLOBAL_ATTRIBUTE1,
929 GLOBAL_ATTRIBUTE2,
930 GLOBAL_ATTRIBUTE3,
931 GLOBAL_ATTRIBUTE4,
932 GLOBAL_ATTRIBUTE5,
933 GLOBAL_ATTRIBUTE6,
934 GLOBAL_ATTRIBUTE7,
935 GLOBAL_ATTRIBUTE8,
936 GLOBAL_ATTRIBUTE9,
937 GLOBAL_ATTRIBUTE10,
938 GLOBAL_ATTRIBUTE11,
939 GLOBAL_ATTRIBUTE12,
940 GLOBAL_ATTRIBUTE13,
941 GLOBAL_ATTRIBUTE14,
942 GLOBAL_ATTRIBUTE15,
943 GLOBAL_ATTRIBUTE16,
944 GLOBAL_ATTRIBUTE17,
945 GLOBAL_ATTRIBUTE18,
946 GLOBAL_ATTRIBUTE19,
947 GLOBAL_ATTRIBUTE20,
948 TP_HEADER_ID,
949 ECE_TP_LOCATION_CODE,
950 PCARD_SITE_FLAG,
951 MATCH_OPTION,
952 COUNTRY_OF_ORIGIN_CODE,
953 FUTURE_DATED_PAYMENT_CCID,
954 CREATE_DEBIT_MEMO_FLAG,
955 SUPPLIER_NOTIF_METHOD,
956 EMAIL_ADDRESS,
957 DECODE(L_DUP_PRIMARY_PAY_SITES
958 ,'Y'
959 ,'N'
960 ,PRIMARY_PAY_SITE_FLAG),
961 SHIPPING_CONTROL,
962 SELLING_COMPANY_IDENTIFIER,
963 GAPLESS_INV_NUM_FLAG,
964 LOCATION_ID,
965 NULL,
966 DUNS_NUMBER,
967 ADDRESS_STYLE,
968 LANGUAGE,
969 PROVINCE,
970 COUNTRY,
971 ADDRESS_LINE1,
972 ADDRESS_LINE2,
973 ADDRESS_LINE3,
974 ADDRESS_LINE4,
975 ADDRESS_LINES_ALT,
976 COUNTY,
977 CITY,
978 STATE,
979 ZIP,
980 TOLERANCE_ID
981 INTO L_VENDOR_SITE_REC.AREA_CODE,L_VENDOR_SITE_REC.PHONE,L_VENDOR_SITE_REC.CUSTOMER_NUM,
982 L_VENDOR_SITE_REC.SHIP_TO_LOCATION_ID,L_VENDOR_SITE_REC.BILL_TO_LOCATION_ID,
983 L_VENDOR_SITE_REC.SHIP_VIA_LOOKUP_CODE,L_VENDOR_SITE_REC.FREIGHT_TERMS_LOOKUP_CODE,
984 L_VENDOR_SITE_REC.FOB_LOOKUP_CODE,L_VENDOR_SITE_REC.INACTIVE_DATE,L_VENDOR_SITE_REC.FAX,
985 L_VENDOR_SITE_REC.FAX_AREA_CODE,L_VENDOR_SITE_REC.TELEX,L_VENDOR_SITE_REC.TERMS_DATE_BASIS,
986 L_VENDOR_SITE_REC.DISTRIBUTION_SET_ID,L_VENDOR_SITE_REC.ACCTS_PAY_CODE_COMBINATION_ID,
987 L_VENDOR_SITE_REC.PREPAY_CODE_COMBINATION_ID,L_VENDOR_SITE_REC.PAY_GROUP_LOOKUP_CODE,
988 L_VENDOR_SITE_REC.PAYMENT_PRIORITY,L_VENDOR_SITE_REC.TERMS_ID,L_VENDOR_SITE_REC.INVOICE_AMOUNT_LIMIT,
989 L_VENDOR_SITE_REC.PAY_DATE_BASIS_LOOKUP_CODE,L_VENDOR_SITE_REC.ALWAYS_TAKE_DISC_FLAG,
990 L_VENDOR_SITE_REC.INVOICE_CURRENCY_CODE,L_VENDOR_SITE_REC.PAYMENT_CURRENCY_CODE,L_VENDOR_SITE_REC.VENDOR_SITE_ID,
991 L_VENDOR_SITE_REC.LAST_UPDATE_DATE,L_VENDOR_SITE_REC.LAST_UPDATED_BY,L_VENDOR_SITE_REC.VENDOR_ID,
992 L_VENDOR_SITE_REC.VENDOR_SITE_CODE,L_VENDOR_SITE_REC.VENDOR_SITE_CODE_ALT,L_VENDOR_SITE_REC.PURCHASING_SITE_FLAG,
993 L_VENDOR_SITE_REC.RFQ_ONLY_SITE_FLAG,L_VENDOR_SITE_REC.PAY_SITE_FLAG,L_VENDOR_SITE_REC.ATTENTION_AR_FLAG,
994 L_VENDOR_SITE_REC.HOLD_ALL_PAYMENTS_FLAG,L_VENDOR_SITE_REC.HOLD_FUTURE_PAYMENTS_FLAG,L_VENDOR_SITE_REC.HOLD_REASON,
995 L_VENDOR_SITE_REC.HOLD_UNMATCHED_INVOICES_FLAG,L_VENDOR_SITE_REC.TAX_REPORTING_SITE_FLAG,
996 L_VENDOR_SITE_REC.ATTRIBUTE_CATEGORY,L_VENDOR_SITE_REC.ATTRIBUTE1,L_VENDOR_SITE_REC.ATTRIBUTE2,
997 L_VENDOR_SITE_REC.ATTRIBUTE3,L_VENDOR_SITE_REC.ATTRIBUTE4,L_VENDOR_SITE_REC.ATTRIBUTE5,L_VENDOR_SITE_REC.ATTRIBUTE6,
998 L_VENDOR_SITE_REC.ATTRIBUTE7,L_VENDOR_SITE_REC.ATTRIBUTE8,L_VENDOR_SITE_REC.ATTRIBUTE9,L_VENDOR_SITE_REC.ATTRIBUTE10,
999 L_VENDOR_SITE_REC.ATTRIBUTE11,L_VENDOR_SITE_REC.ATTRIBUTE12,L_VENDOR_SITE_REC.ATTRIBUTE13,
1000 L_VENDOR_SITE_REC.ATTRIBUTE14,L_VENDOR_SITE_REC.ATTRIBUTE15,L_VENDOR_SITE_REC.VALIDATION_NUMBER,
1001 L_VENDOR_SITE_REC.EXCLUDE_FREIGHT_FROM_DISCOUNT,L_VENDOR_SITE_REC.BANK_CHARGE_BEARER,L_VENDOR_SITE_REC.ORG_ID,
1002 L_VENDOR_SITE_REC.CHECK_DIGITS,L_VENDOR_SITE_REC.ALLOW_AWT_FLAG,L_VENDOR_SITE_REC.AWT_GROUP_ID,
1003 L_VENDOR_SITE_REC.DEFAULT_PAY_SITE_ID,L_VENDOR_SITE_REC.PAY_ON_CODE,L_VENDOR_SITE_REC.PAY_ON_RECEIPT_SUMMARY_CODE,
1004 L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE_CATEGORY
1005 ,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE1,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE2,
1006 L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE3,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE4,
1007 L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE5,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE6,
1008 L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE7,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE8,
1009 L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE9,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE10,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE11,
1010 L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE12,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE13,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE14,
1011 L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE15,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE16,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE17,
1012 L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE18,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE19,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE20,
1013 L_VENDOR_SITE_REC.TP_HEADER_ID,L_VENDOR_SITE_REC.ECE_TP_LOCATION_CODE,L_VENDOR_SITE_REC.PCARD_SITE_FLAG,
1014 L_VENDOR_SITE_REC.MATCH_OPTION,L_VENDOR_SITE_REC.COUNTRY_OF_ORIGIN_CODE,L_VENDOR_SITE_REC.FUTURE_DATED_PAYMENT_CCID,
1015 L_VENDOR_SITE_REC.CREATE_DEBIT_MEMO_FLAG,L_VENDOR_SITE_REC.SUPPLIER_NOTIF_METHOD,L_VENDOR_SITE_REC.EMAIL_ADDRESS,
1016 L_VENDOR_SITE_REC.PRIMARY_PAY_SITE_FLAG,L_VENDOR_SITE_REC.SHIPPING_CONTROL,L_VENDOR_SITE_REC.SELLING_COMPANY_IDENTIFIER,
1017 L_VENDOR_SITE_REC.GAPLESS_INV_NUM_FLAG,L_VENDOR_SITE_REC.LOCATION_ID,L_VENDOR_SITE_REC.PARTY_SITE_ID,
1018 L_VENDOR_SITE_REC.DUNS_NUMBER,L_VENDOR_SITE_REC.ADDRESS_STYLE,L_VENDOR_SITE_REC.LANGUAGE,L_VENDOR_SITE_REC.PROVINCE,
1019 L_VENDOR_SITE_REC.COUNTRY,L_VENDOR_SITE_REC.ADDRESS_LINE1,L_VENDOR_SITE_REC.ADDRESS_LINE2,
1023 FROM
1020 L_VENDOR_SITE_REC.ADDRESS_LINE3,L_VENDOR_SITE_REC.ADDRESS_LINE4,L_VENDOR_SITE_REC.ADDRESS_LINES_ALT,
1021 L_VENDOR_SITE_REC.COUNTY,L_VENDOR_SITE_REC.CITY,L_VENDOR_SITE_REC.STATE,L_VENDOR_SITE_REC.ZIP,
1022 L_VENDOR_SITE_REC.TOLERANCE_ID
1024 AP_SUPPLIER_SITES_ALL
1025 WHERE VENDOR_ID = C1.DUPLICATE_VENDOR_ID
1026 AND VENDOR_SITE_ID = C1.DUPLICATE_VENDOR_SITE_ID
1027 AND NOT EXISTS (
1028 SELECT
1029 null
1030 FROM
1031 AP_SUPPLIER_SITES_ALL
1032 WHERE VENDOR_ID = C1.VENDOR_ID
1033 AND VENDOR_SITE_ID = C1.DUPLICATE_VENDOR_SITE_ID );
1034 AP_VENDOR_PUB_PKG.CREATE_VENDOR_SITE(P_API_VERSION => 1.0
1035 ,P_INIT_MSG_LIST => 'F'
1036 ,P_COMMIT => 'F'
1037 ,P_VALIDATION_LEVEL => 100
1038 ,X_RETURN_STATUS => L_RETURN_STATUS
1039 ,X_MSG_COUNT => L_MSG_COUNT
1040 ,X_MSG_DATA => L_MSG_DATA
1041 ,P_VENDOR_SITE_REC => L_VENDOR_SITE_REC
1042 ,X_VENDOR_SITE_ID => L_VENDOR_SITE_ID
1043 ,X_PARTY_SITE_ID => L_PARTY_SITE_ID
1044 ,X_LOCATION_ID => L_LOCATION_ID);
1045 IF L_RETURN_STATUS <> 'S' THEN
1046 IF L_MSG_COUNT > 0 THEN
1047 FOR i IN 1 .. L_MSG_COUNT LOOP
1048 L_MSG_DATA := FND_MSG_PUB.GET(-2
1049 ,'T');
1050 L_ERR_MSG := FND_MESSAGE.GET;
1051 FND_FILE.PUT_LINE(1
1052 ,L_ERR_MSG);
1053 END LOOP;
1054 END IF;
1055 RAISE API_ERROR;
1056 END IF;
1057 END IF;
1058 UPDATE
1059 AP_SUPPLIER_CONTACTS
1060 SET
1061 ORG_PARTY_SITE_ID = L_PARTY_SITE_ID
1062 WHERE ORG_PARTY_SITE_ID = C1.PARTY_SITE_ID;
1063 UPDATE
1064 AP_SUPPLIER_SITES_ALL
1065 SET
1066 INACTIVE_DATE = SYSDATE
1067 ,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
1068 ,LAST_UPDATE_DATE = SYSDATE
1069 WHERE VENDOR_SITE_ID = C1.DUPLICATE_VENDOR_SITE_ID;
1070 END LOOP;
1071 RETURN (TRUE);
1072 EXCEPTION
1073 WHEN OTHERS THEN
1074 APP_EXCEPTION.RAISE_EXCEPTION;
1075 RETURN (FALSE);
1076 END DUPLICATE_SITES;
1077
1078 FUNCTION C_VENDOR_SITE_ID_POFORMULA(C_ORG_ID_PO IN NUMBER
1079 ,C_KEEP_SITE_FLAG_PO IN VARCHAR2
1080 ,C_VENDOR_ID_PO IN NUMBER
1081 ,C_VENDOR_SITE_CODE_PO IN VARCHAR2
1082 ,C_OLD_VENDOR_SITE_ID_PO IN NUMBER) RETURN NUMBER IS
1083 BEGIN
1084 DECLARE
1085 L_VENDOR_SITE_ID NUMBER;
1086 BEGIN
1087 MO_GLOBAL.SET_POLICY_CONTEXT('S'
1088 ,C_ORG_ID_PO);
1089 IF C_KEEP_SITE_FLAG_PO = 'Y' THEN
1090 SELECT
1091 VENDOR_SITE_ID
1092 INTO L_VENDOR_SITE_ID
1093 FROM
1094 AP_SUPPLIER_SITES_ALL
1095 WHERE VENDOR_ID = C_VENDOR_ID_PO
1096 AND VENDOR_SITE_CODE = C_VENDOR_SITE_CODE_PO
1097 AND ORG_ID = C_ORG_ID_PO;
1098 RETURN (L_VENDOR_SITE_ID);
1099 ELSE
1100 RETURN (C_OLD_VENDOR_SITE_ID_PO);
1101 END IF;
1102 EXCEPTION
1103 WHEN OTHERS THEN
1104 RETURN (C_OLD_VENDOR_SITE_ID_PO);
1105 END;
1106 RETURN NULL;
1107 END C_VENDOR_SITE_ID_POFORMULA;
1108
1109 FUNCTION C_MODIFY_PO_TABLESFORMULA(C_DUP_VENDOR_ID_PO IN NUMBER
1110 ,C_KEEP_SITE_FLAG_PO IN VARCHAR2
1111 ,C_PARTY_SITE_ID_PO IN NUMBER
1112 ,C_DUP_PARTY_SITE_ID_PO IN NUMBER
1113 ,C_DUP_VENDOR_SITE_ID_PO IN NUMBER
1114 ,C_VENDOR_ID_PO IN NUMBER
1115 ,C_VENDOR_SITE_ID_PO IN NUMBER
1116 ,C_PO_COUNT IN NUMBER
1117 ,C_ENTRY_ID_PO IN NUMBER) RETURN NUMBER IS
1118 BEGIN
1119 DECLARE
1120 CURSOR MERGE_AUTOSRC_DOCS IS
1121 SELECT
1122 DISTINCT
1123 PAD.AUTOSOURCE_RULE_ID,
1124 PAD.SEQUENCE_NUM,
1125 PAD.DOCUMENT_LINE_ID
1126 FROM
1127 PO_AUTOSOURCE_DOCUMENTS PAD
1128 WHERE PAD.VENDOR_ID = C_DUP_VENDOR_ID_PO;
1129 L_MAX_SEQ_NUM NUMBER;
1130 L_NEW_SEQ_NUM NUMBER;
1131 L_SEQ_NUM NUMBER;
1132 L_RULE_ID NUMBER;
1133 L_DOC_LINE_ID NUMBER;
1134 STATUS VARCHAR2(1);
1135 INDUSTRY VARCHAR2(1);
1136 L_MERGING_PPSITE VARCHAR2(1);
1137 L_PPSITES NUMBER;
1138 L_RETURN_STATUS VARCHAR2(1);
1139 L_MSG_COUNT NUMBER;
1140 L_MSG_DATA VARCHAR2(500);
1141 API_ERROR EXCEPTION;
1142 L_ERR_MSG VARCHAR2(500);
1143 BEGIN
1144 P_PRIMARY_PAY := 'N';
1145 IF NVL(C_KEEP_SITE_FLAG_PO
1146 ,'N') <> 'Y' THEN
1147 UPDATE
1148 AP_SUPPLIER_CONTACTS
1149 SET
1150 LAST_UPDATE_DATE = SYSDATE
1151 ,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
1152 ,ORG_PARTY_SITE_ID = C_PARTY_SITE_ID_PO
1153 WHERE ORG_PARTY_SITE_ID = C_DUP_PARTY_SITE_ID_PO;
1154 END IF;
1155 EXECUTE IMMEDIATE
1156 'UPDATE ap_history_invoices_all ahi
1157 SET vendor_id = :C_VENDOR_ID_PO
1161 where ai.invoice_num = ahi.invoice_num
1158 WHERE vendor_id = :C_DUP_VENDOR_ID_PO
1159 and not exists (select null
1160 from ap_invoices ai
1162 and ai.vendor_id = :C_VENDOR_ID)';
1163 IF P_PO_INSTALLED_FLAG_T = 'Y' THEN
1164 PO_AP_MERGE_GRP.UPDATE_ORG_ASSIGNMENTS(P_API_VERSION => 1.0
1165 ,X_RETURN_STATUS => L_RETURN_STATUS
1166 ,P_FROM_VENDOR_ID => C_DUP_VENDOR_ID_PO
1167 ,P_FROM_SITE_ID => C_DUP_VENDOR_SITE_ID_PO
1168 ,P_TO_VENDOR_ID => C_VENDOR_ID_PO
1169 ,P_TO_SITE_ID => C_VENDOR_SITE_ID_PO);
1170 END IF;
1171 IF P_CHV_INSTALLED_FLAG = 'Y' THEN
1172 CHV_VENDORMERGE_GRP.MERGE_VENDOR(P_API_VERSION => 1.0
1173 ,P_INIT_MSG_LIST => 'F'
1174 ,P_COMMIT => 'F'
1175 ,P_VALIDATION_LEVEL => 100
1176 ,X_RETURN_STATUS => L_RETURN_STATUS
1177 ,X_MSG_COUNT => L_MSG_COUNT
1178 ,X_MSG_DATA => L_MSG_DATA
1179 ,P_VENDOR_ID => C_VENDOR_ID_PO
1180 ,P_VENDOR_SITE_ID => C_VENDOR_SITE_ID_PO
1181 ,P_DUP_VENDOR_ID => C_DUP_VENDOR_ID_PO
1182 ,P_DUP_VENDOR_SITE_ID => C_DUP_VENDOR_SITE_ID_PO);
1183 IF L_RETURN_STATUS <> 'S' THEN
1184 IF L_MSG_COUNT > 0 THEN
1185 FOR i IN 1 .. L_MSG_COUNT LOOP
1186 L_MSG_DATA := FND_MSG_PUB.GET(-2
1187 ,'T');
1188 L_ERR_MSG := FND_MESSAGE.GET;
1189 FND_FILE.PUT_LINE(1
1190 ,L_ERR_MSG);
1191 END LOOP;
1192 END IF;
1193 RAISE API_ERROR;
1194 END IF;
1195 END IF;
1196 IGI_VENDOR_MERGE_GRP.MERGE_VENDOR(P_API_VERSION => 1.0
1197 ,P_INIT_MSG_LIST => NULL
1198 ,P_COMMIT => NULL
1199 ,P_VALIDATION_LEVEL => NULL
1200 ,X_RETURN_STATUS => L_RETURN_STATUS
1201 ,X_MSG_COUNT => L_MSG_COUNT
1202 ,X_MSG_DATA => L_MSG_DATA
1203 ,P_NEW_VENDOR_ID => C_VENDOR_ID_PO
1204 ,P_NEW_VENDOR_SITE_ID => C_VENDOR_SITE_ID_PO
1205 ,P_OLD_VENDOR_ID => C_DUP_VENDOR_ID_PO
1206 ,P_OLD_VENDOR_SITE_ID => C_DUP_VENDOR_SITE_ID_PO);
1207 UPDATE
1208 AP_DUPLICATE_VENDORS_ALL
1209 SET
1210 LAST_UPDATE_DATE = SYSDATE
1211 ,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
1212 ,NUMBER_PO_HEADERS_CHANGED = NVL(NUMBER_PO_HEADERS_CHANGED
1213 ,0) + C_PO_COUNT
1214 WHERE ENTRY_ID = C_ENTRY_ID_PO;
1215 IF P_PRIMARY_PAY = 'N' THEN
1216 SELECT
1217 NVL(PRIMARY_PAY_SITE_FLAG
1218 ,'N')
1219 INTO L_MERGING_PPSITE
1220 FROM
1221 AP_SUPPLIER_SITES_ALL
1222 WHERE VENDOR_ID = C_DUP_VENDOR_ID_PO
1223 AND VENDOR_SITE_ID = C_DUP_VENDOR_SITE_ID_PO;
1227 SET
1224 IF ((L_MERGING_PPSITE = 'Y') AND (C_VENDOR_ID_PO = C_DUP_VENDOR_ID_PO)) THEN
1225 UPDATE
1226 AP_SUPPLIER_SITES_ALL
1228 PRIMARY_PAY_SITE_FLAG = 'Y'
1229 WHERE VENDOR_ID = C_VENDOR_ID_PO
1230 AND VENDOR_SITE_ID = C_VENDOR_SITE_ID_PO;
1231 P_PRIMARY_PAY := 'Y';
1232 END IF;
1233 END IF;
1234 END;
1235 RETURN NULL;
1236 EXCEPTION
1237 WHEN OTHERS THEN
1238 APP_EXCEPTION.RAISE_EXCEPTION;
1239 RETURN (NULL);
1240 END C_MODIFY_PO_TABLESFORMULA;
1241
1242 FUNCTION C_MODIFY_RFQ_VENDORSFORMULA(C_VENDOR_ID_PO IN NUMBER
1243 ,C_VENDOR_SITE_ID_PO IN NUMBER
1244 ,C_PO_HEADER_ID IN NUMBER) RETURN NUMBER IS
1245 BEGIN
1246 BEGIN
1247 UPDATE
1248 PO_HEADERS_ALL
1249 SET
1250 VENDOR_ID = C_VENDOR_ID_PO
1251 ,VENDOR_SITE_ID = C_VENDOR_SITE_ID_PO
1252 ,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
1253 ,LAST_UPDATE_DATE = SYSDATE
1254 WHERE PO_HEADER_ID = C_PO_HEADER_ID;
1255 IF P_PO_INSTALLED_FLAG_T = 'Y' THEN
1256 EXECUTE IMMEDIATE
1257 'UPDATE po_rfq_vendors
1258 SET vendor_id = :C_VENDOR_ID_PO,
1259 vendor_site_id = :C_VENDOR_SITE_ID_PO
1260 WHERE vendor_id = :C_DUP_VENDOR_ID_PO
1261 AND po_header_id = :C_PO_HEADER_ID
1262 AND not exists
1263 (select vendor_id
1264 from po_rfq_vendors
1265 where vendor_id = :C_VENDOR_ID_PO
1266 and vendor_site_id = :C_VENDOR_SITE_ID_PO
1267 and po_header_id = :C_PO_HEADER_ID)';
1268 EXECUTE IMMEDIATE
1269 'DELETE from po_rfq_vendors prv
1270 WHERE vendor_id = :C_DUP_VENDOR_ID_PO
1271 AND vendor_site_id = :C_DUP_VENDOR_SITE_ID_PO
1272 AND po_header_id = :C_PO_HEADER_ID';
1273 END IF;
1274 END;
1275 RETURN NULL;
1276 END C_MODIFY_RFQ_VENDORSFORMULA;
1277
1278 FUNCTION G_DUPLICATE_VENDORS_POGROUPFIL RETURN BOOLEAN IS
1279 BEGIN
1280 BEGIN
1281 RETURN (TRUE);
1282 END;
1283 RETURN (TRUE);
1284 END G_DUPLICATE_VENDORS_POGROUPFIL;
1285
1286 FUNCTION G_DUPLICATE_VENDORSGROUPFILTER RETURN BOOLEAN IS
1287 BEGIN
1288 BEGIN
1289 RETURN (TRUE);
1290 END;
1291 RETURN (TRUE);
1292 END G_DUPLICATE_VENDORSGROUPFILTER;
1293
1294 FUNCTION C_CURRENCY_CODEFORMULA(C_INVOICE_CURRENCY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
1295 BEGIN
1296 RETURN (C_INVOICE_CURRENCY_CODE);
1297 END C_CURRENCY_CODEFORMULA;
1298
1299 FUNCTION C_INVOICE_COUNTFORMULA(C_DUP_INVOICE_COUNT IN NUMBER
1300 ,C_NON_DUP_INVOICE_COUNT IN NUMBER
1301 ,C_DUP_RECURRING_COUNT IN NUMBER
1302 ,C_RECURRING_COUNT IN NUMBER) RETURN NUMBER IS
1303 BEGIN
1304 DECLARE
1305 L_INVOICE_COUNT NUMBER := 0;
1306 BEGIN
1307 L_INVOICE_COUNT := C_DUP_INVOICE_COUNT + C_NON_DUP_INVOICE_COUNT + C_DUP_RECURRING_COUNT + C_RECURRING_COUNT;
1308 RETURN (L_INVOICE_COUNT);
1309 END;
1310 RETURN NULL;
1311 END C_INVOICE_COUNTFORMULA;
1312
1313 FUNCTION DUPLICATE_TAX_SITES RETURN BOOLEAN IS
1314 L_DUP_INACTIVE_DATE DATE;
1315 L_DUP_TAX_FLAG VARCHAR2(1);
1316 L_COUNT NUMBER;
1317 BEGIN
1318 FOR c1 IN (SELECT
1319 VENDOR_ID,
1323 FROM
1320 DUPLICATE_VENDOR_ID,
1321 DUPLICATE_VENDOR_SITE_ID,
1322 ORG_ID
1324 AP_DUPLICATE_VENDORS
1325 WHERE PROCESS_FLAG = 'S') LOOP
1326 SELECT
1327 TAX_REPORTING_SITE_FLAG,
1328 INACTIVE_DATE
1329 INTO L_DUP_TAX_FLAG,L_DUP_INACTIVE_DATE
1330 FROM
1331 AP_SUPPLIER_SITES_ALL
1332 WHERE VENDOR_ID = C1.DUPLICATE_VENDOR_ID
1333 AND VENDOR_SITE_ID = C1.DUPLICATE_VENDOR_SITE_ID;
1334 IF L_DUP_TAX_FLAG = 'Y' AND L_DUP_INACTIVE_DATE IS NULL THEN
1335 SELECT
1336 count(*)
1337 INTO L_COUNT
1338 FROM
1339 AP_SUPPLIER_SITES_ALL
1340 WHERE TAX_REPORTING_SITE_FLAG = 'Y'
1341 AND VENDOR_ID = C1.VENDOR_ID
1342 AND ORG_ID = C1.ORG_ID
1343 AND INACTIVE_DATE is not null;
1344 IF L_COUNT > 0 THEN
1345 UPDATE
1346 AP_SUPPLIER_SITES_ALL
1347 SET
1348 TAX_REPORTING_SITE_FLAG = 'N'
1349 WHERE VENDOR_ID = C1.VENDOR_ID
1350 AND ORG_ID = C1.ORG_ID
1351 AND INACTIVE_DATE is not null;
1352 END IF;
1353 END IF;
1354 END LOOP;
1355 RETURN (TRUE);
1356 END DUPLICATE_TAX_SITES;
1357
1358 FUNCTION C_OPERATING_UNITFORMULA(C_ORG_ID IN NUMBER) RETURN CHAR IS
1359 BEGIN
1360 DECLARE
1361 L_OPERATING_UNIT VARCHAR2(4000);
1362 BEGIN
1363 L_OPERATING_UNIT := MO_GLOBAL.GET_OU_NAME(C_ORG_ID);
1364 RETURN (L_OPERATING_UNIT);
1365 EXCEPTION
1366 WHEN OTHERS THEN
1367 RETURN NULL;
1368 END;
1369 RETURN NULL;
1370 END C_OPERATING_UNITFORMULA;
1371
1372 FUNCTION C_OPERATING_UNITPOFORMULA(C_ORG_ID_PO IN NUMBER) RETURN CHAR IS
1373 BEGIN
1374 DECLARE
1375 L_OPERATING_UNIT VARCHAR2(4000);
1376 BEGIN
1377 L_OPERATING_UNIT := MO_GLOBAL.GET_OU_NAME(C_ORG_ID_PO);
1378 RETURN (L_OPERATING_UNIT);
1379 EXCEPTION
1380 WHEN OTHERS THEN
1381 RETURN NULL;
1382 END;
1383 RETURN NULL;
1384 END C_OPERATING_UNITPOFORMULA;
1385
1386 FUNCTION C_BASE_CURRENCY_CODE_P RETURN VARCHAR2 IS
1387 BEGIN
1388 RETURN C_BASE_CURRENCY_CODE;
1389 END C_BASE_CURRENCY_CODE_P;
1390
1391 FUNCTION C_BASE_PRECISION_P RETURN NUMBER IS
1392 BEGIN
1393 RETURN C_BASE_PRECISION;
1394 END C_BASE_PRECISION_P;
1395
1396 FUNCTION C_BASE_MIN_ACCT_UNIT_P RETURN NUMBER IS
1397 BEGIN
1398 RETURN C_BASE_MIN_ACCT_UNIT;
1399 END C_BASE_MIN_ACCT_UNIT_P;
1400
1401 FUNCTION C_BASE_DESCRIPTION_P RETURN VARCHAR2 IS
1402 BEGIN
1403 RETURN C_BASE_DESCRIPTION;
1404 END C_BASE_DESCRIPTION_P;
1405
1406 FUNCTION C_COMPANY_NAME_HEADER_P RETURN VARCHAR2 IS
1407 BEGIN
1408 RETURN C_COMPANY_NAME_HEADER;
1409 END C_COMPANY_NAME_HEADER_P;
1410
1411 FUNCTION C_REPORT_START_DATE_P RETURN DATE IS
1412 BEGIN
1413 RETURN C_REPORT_START_DATE;
1414 END C_REPORT_START_DATE_P;
1415
1416 FUNCTION C_NLS_YES_P RETURN VARCHAR2 IS
1417 BEGIN
1418 RETURN C_NLS_YES;
1419 END C_NLS_YES_P;
1420
1421 FUNCTION C_NLS_NO_P RETURN VARCHAR2 IS
1422 BEGIN
1423 RETURN C_NLS_NO;
1424 END C_NLS_NO_P;
1425
1426 FUNCTION C_NLS_ALL_P RETURN VARCHAR2 IS
1427 BEGIN
1428 RETURN C_NLS_ALL;
1429 END C_NLS_ALL_P;
1430
1431 FUNCTION C_NLS_NO_DATA_EXISTS_P RETURN VARCHAR2 IS
1432 BEGIN
1433 RETURN C_NLS_NO_DATA_EXISTS;
1434 END C_NLS_NO_DATA_EXISTS_P;
1435
1436 FUNCTION C_REPORT_RUN_TIME_P RETURN VARCHAR2 IS
1437 BEGIN
1438 RETURN C_REPORT_RUN_TIME;
1439 END C_REPORT_RUN_TIME_P;
1440
1441 FUNCTION C_CHART_OF_ACCOUNTS_ID_P RETURN NUMBER IS
1442 BEGIN
1443 RETURN C_CHART_OF_ACCOUNTS_ID;
1444 END C_CHART_OF_ACCOUNTS_ID_P;
1445
1446 FUNCTION C_NLS_END_OF_REPORT_P RETURN VARCHAR2 IS
1447 BEGIN
1448 RETURN C_NLS_END_OF_REPORT;
1449 END C_NLS_END_OF_REPORT_P;
1450
1451 END AP_APXINUPD_XMLP_PKG;
1452
1453