[Home] [Help]
PACKAGE BODY: APPS.ARP_CMERGE_ARTAX
Source
1 PACKAGE BODY ARP_CMERGE_ARTAX as
2 /* $Header: ARPLTAXB.pls 120.4 2005/10/30 04:24:46 appldev ship $ */
3
4 /*---------------------------- PRIVATE VARIABLES ----------------------------*/
5 g_count NUMBER := 0;
6
7
8 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
9 PROCEDURE ra_te (
10 req_id NUMBER,
11 set_num NUMBER,
12 process_mode VARCHAR2) IS
13
14 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
15 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
16 INDEX BY BINARY_INTEGER;
17 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
18
19 TYPE TAX_EXEMPTION_ID_LIST_TYPE IS TABLE OF
20 RA_TAX_EXEMPTIONS.TAX_EXEMPTION_ID%TYPE
21 INDEX BY BINARY_INTEGER;
22 PRIMARY_KEY_ID_LIST TAX_EXEMPTION_ID_LIST_TYPE;
23
24 TYPE customer_id_LIST_TYPE IS TABLE OF
25 RA_TAX_EXEMPTIONS.customer_id%TYPE
26 INDEX BY BINARY_INTEGER;
27 NUM_COL1_ORIG_LIST customer_id_LIST_TYPE;
28 NUM_COL1_NEW_LIST customer_id_LIST_TYPE;
29
30 TYPE site_use_id_LIST_TYPE IS TABLE OF
31 RA_TAX_EXEMPTIONS.site_use_id%TYPE
32 INDEX BY BINARY_INTEGER;
33 NUM_COL2_ORIG_LIST site_use_id_LIST_TYPE;
34 NUM_COL2_NEW_LIST site_use_id_LIST_TYPE;
35
36 l_profile_val VARCHAR2(30);
37 CURSOR merged_records IS
38 SELECT distinct CUSTOMER_MERGE_HEADER_ID
39 ,TAX_EXEMPTION_ID
40 ,yt.customer_id
41 ,site_use_id
42 FROM RA_TAX_EXEMPTIONS yt, ra_customer_merges m
43 WHERE ( yt.customer_id = m.DUPLICATE_ID AND
44 nvl(yt.site_use_id,m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID)
45 AND m.process_flag = 'N'
46 AND m.request_id = req_id
47 AND m.set_number = set_num;
48 l_last_fetch BOOLEAN := FALSE;
49 l_count NUMBER;
50 BEGIN
51 IF process_mode='LOCK' THEN
52 NULL;
53 ELSE
54 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
55 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','RA_TAX_EXEMPTIONS',FALSE);
56 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
57 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
58
59 open merged_records;
60 LOOP
61 FETCH merged_records BULK COLLECT INTO
62 MERGE_HEADER_ID_LIST
63 , PRIMARY_KEY_ID_LIST
64 , NUM_COL1_ORIG_LIST
65 , NUM_COL2_ORIG_LIST
66 LIMIT ARP_CMERGE.max_array_size;/*Additional changes for 2447449*/
67 IF merged_records%NOTFOUND THEN
68 l_last_fetch := TRUE;
69 END IF;
70 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
71 exit;
72 END IF;
73 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
74 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
75 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
76 END LOOP;
77 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
78 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
79 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
80 MERGE_LOG_ID,
81 TABLE_NAME,
82 MERGE_HEADER_ID,
83 PRIMARY_KEY_ID,
84 NUM_COL1_ORIG,
85 NUM_COL1_NEW,
86 NUM_COL2_ORIG,
87 NUM_COL2_NEW,
88 ACTION_FLAG,
89 REQUEST_ID,
90 CREATED_BY,
91 CREATION_DATE,
92 LAST_UPDATE_LOGIN,
93 LAST_UPDATE_DATE,
94 LAST_UPDATED_BY
95 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
96 'RA_TAX_EXEMPTIONS',
97 MERGE_HEADER_ID_LIST(I),
98 PRIMARY_KEY_ID_LIST(I),
99 NUM_COL1_ORIG_LIST(I),
100 NUM_COL1_NEW_LIST(I),
101 NUM_COL2_ORIG_LIST(I),
102 NUM_COL2_NEW_LIST(I),
103 'U',
104 req_id,
105 hz_utility_pub.CREATED_BY,
106 hz_utility_pub.CREATION_DATE,
107 hz_utility_pub.LAST_UPDATE_LOGIN,
108 hz_utility_pub.LAST_UPDATE_DATE,
109 hz_utility_pub.LAST_UPDATED_BY
110 );
111
112 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
113 UPDATE RA_TAX_EXEMPTIONS yt SET
114 customer_id=NUM_COL1_NEW_LIST(I)
115 ,site_use_id=NUM_COL2_NEW_LIST(I)
116 , LAST_UPDATE_DATE=SYSDATE
117 , REQUEST_ID=req_id
118 , PROGRAM_UPDATE_DATE=SYSDATE
119 WHERE TAX_EXEMPTION_ID=PRIMARY_KEY_ID_LIST(I)
120 ;
121 l_count := l_count + SQL%ROWCOUNT;
122 IF l_last_fetch THEN
123 EXIT;
124 END IF;
125 END LOOP;
126
127 arp_message.set_name('AR','AR_ROWS_UPDATED');
128 arp_message.set_token('NUM_ROWS',to_char(l_count));
129 END IF;
130 EXCEPTION
131 WHEN OTHERS THEN
132 arp_message.set_line( 'ra_te');
133 RAISE;
134 END ra_te;
135
136
137
138 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
139 PROCEDURE merge (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
140 BEGIN
141
142 arp_message.set_line( 'ARP_CMERGE_ARTAX.MERGE()+' );
143
144 ra_te( req_id, set_num, process_mode );
145
146 arp_message.set_line( 'ARP_CMERGE_ARTAX.MERGE()-' );
147
148 END merge;
149
150 end ARP_CMERGE_ARTAX;