1 PACKAGE BODY XDP_MERGE AS
2 /* $Header: XDPPMRGB.pls 120.1 2005/06/09 00:24:59 appldev $ */
3 --
4 -- Start of Comments
5 -- Package name : XDP_MERGE
6 -- Purpose : Merges duplicate parties in SFM tables. The
7 -- SFM tables that need to be considered for
8 -- Party Merge are:
9 -- XDP_ORDER_HEADERS
10 -- Columns : Customer_Id
11 --
12 -- The Customer Id column is populated through the ProcessOrder API
13 -- without any validation. The Flow Through Manager UI further displays it
14 -- along with customer name. Customer Name is also populated in the same fashion.
15 -- The data stored in these two columns is displayed as it is in the UI.
16 -- There no cross validation for the account number and customer as well.
17 --
18 -- Since these parameters are exposed throught the public API 'Process Order' and also they
19 -- displayed in the UI it necessary to write a party merge routine in order to keep the data
20 -- in sync in case of party merge incidents.
21 --
22 --
23 --
24 -- History
25 -- MM-DD-YYYY NAME MODIFICATIONS
26 -- 05-05-2003 spusegao Created.
27
28 G_PROC_NAME CONSTANT VARCHAR2(30) := 'XDP_MERGE';
29 G_USER_ID CONSTANT NUMBER(15) := FND_GLOBAL.USER_ID;
30 G_LOGIN_ID CONSTANT NUMBER(15) := FND_GLOBAL.LOGIN_ID;
31
32 TYPE ROWID_TBL IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
33
34 -- The following procedure merges XDP_ORDER_HEADERS columns:
35 -- CUSTOMER_ID
36 -- The above columns are FKs to HZ_PARTIES.PARTY_ID
37
38 PROCEDURE MERGE_CUSTOMER_ID (
39 p_entity_name IN VARCHAR2,
40 p_from_id IN NUMBER,
41 x_to_id OUT NOCOPY NUMBER,
42 p_from_fk_id IN NUMBER,
43 p_to_fk_id IN NUMBER,
44 p_parent_entity_name IN VARCHAR2,
45 p_batch_id IN NUMBER,
46 p_batch_party_id IN NUMBER,
47 x_return_status OUT NOCOPY VARCHAR2)
48 IS
49 -- cursor fetches all the records that need to be merged.
50 CURSOR c1 IS
51 SELECT rowid
52 FROM xdp_order_headers
53 WHERE p_from_fk_id IN (customer_id)
54 FOR UPDATE NOWAIT;
55
56 l_rowid_tbl ROWID_TBL;
57 l_customer_name VARCHAR2(360);
58 l_merge_reason_code VARCHAR2(30);
59 l_api_name VARCHAR2(30) := 'MERGE_CUSTOMER_ID';
60 l_count NUMBER(10) := 0;
61
62 RESOURCE_BUSY EXCEPTION;
63 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
64
65 BEGIN
66 arp_message.set_line('CS_SR_PARTY_MERGE_PKG.MERGE_CUSTOMER_ID()+');
67
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69
70 SELECT merge_reason_code
71 INTO l_merge_reason_code
72 FROM hz_merge_batch
73 WHERE batch_id = p_batch_id;
74
75 IF l_merge_reason_code = 'DUPLICATE' then
76 -- if reason code is duplicate then allow the party merge to happen without
77 -- any validations.
78 NULL;
79 ELSE
80 -- if there are any validations to be done, include it in this section
81 NULL;
82 END IF;
83
84 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
85 -- needs to be done. Set Merged To Id is same as Merged From Id and return
86
87 IF p_from_fk_id = p_to_fk_id then
88 x_to_id := p_from_id;
89 RETURN;
90 END IF;
91
92 -- If the parent has changed(ie. Parent is getting merged) then transfer the
93 -- dependent record to the new parent. Before transferring check if a similar
94 -- dependent record exists on the new parent. If a duplicate exists then do
95 -- not transfer and return the id of the duplicate record as the Merged To Id
96
97 -- In the case of XDP_ORDER_HEADERS table, if party id 1000 gets merged to party
98 -- id 2000 then, we have to update all records with customer_id = 1000 to 2000
99
100 IF p_from_fk_id <> p_to_fk_id THEN
101 BEGIN
102 -- obtain lock on records to be updated.
103 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
104 arp_message.set_token('TABLE_NAME', 'XDP_ORDER_HEADERS', FALSE);
105
106 OPEN c1;
107 FETCH c1 bulk collect INTO l_rowid_tbl;
108 CLOSE c1;
109
110 -- if no records were found to be updated then stop and return to calling prg.
111 IF l_rowid_tbl.count = 0 THEN
112 RETURN;
113 ELSE
114 SELECT party_name
115 INTO l_customer_name
116 FROM hz_parties
117 WHERE party_id = p_to_fk_id ;
118 END IF;
119
120 FORALL i IN 1..l_rowid_tbl.COUNT
121
122 UPDATE xdp_order_headers
123 SET customer_id = DECODE(customer_id, p_from_fk_id, p_to_fk_id, customer_id),
124 customer_name = DECODE(customer_id,p_from_fk_id,substr(l_customer_name,1,80),customer_name),
125 last_update_date = SYSDATE,
126 last_updated_by = G_USER_ID,
127 last_update_login = G_LOGIN_ID
128 WHERE rowid = l_rowid_tbl(i);
129
130 l_count := SQL%ROWCOUNT;
131
132 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
133 arp_message.set_token('NUM_ROWS', to_char(l_count) );
134
135 EXCEPTION
136 WHEN resource_busy THEN
137 arp_message.set_line(g_proc_name || '.' || l_api_name ||
138 '; Could not obtain lock for records in table ' ||
139 'XDP_ORDER_HEADERS for customer_id = ' || p_from_fk_id );
140 x_return_status := FND_API.G_RET_STS_ERROR;
141 RAISE;
142
143 WHEN others THEN
144 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
145 x_return_status := FND_API.G_RET_STS_ERROR;
146 RAISE;
147 END;
148 END IF; -- if p_from_fk_id <> p_to_fk_id
149
150 END MERGE_CUSTOMER_ID;
151
152 END XDP_MERGE ;
[Home] [Help]