1 PACKAGE BODY QP_PARTY_MERGE_PKG AS
2 /* $Header: QPXPMRGB.pls 120.1.12010000.2 2009/04/24 11:20:37 smbalara ship $ */
3
4 /***********************************************************************
5 Procedure to Merge those qualifier_attr_value's in QP_QUALIFIERS which
6 reference Party_Id or Party_Site_Id. To be called by TCA when Parties
7 or Party Sites are merged.
8 ***********************************************************************/
9
10 Procedure Merge_Qualifiers(p_entity_name IN VARCHAR2,
11 p_from_id IN NUMBER,
12 p_to_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
13 p_from_fk_id IN NUMBER,
14 p_to_fk_id IN NUMBER,
15 p_parent_entity_name IN VARCHAR2,
16 p_batch_id IN NUMBER,
17 p_batch_party_id IN NUMBER,
18 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
19 IS
20 l_temp_date DATE;
21 BEGIN
22
23 x_return_status := FND_API.G_RET_STS_SUCCESS;
24
25 l_temp_date := trunc(sysdate);
26
27 --If Parent getting transferred, then do nothing. Set Merged To Id
28 --to the same as Merged From Id and Return
29
30 IF p_from_fk_id = p_to_fk_id
31 THEN
32 p_to_id := p_from_id;
33 RETURN;
34 END IF;
35
36
37 --If Parent has changed (Parent is getting Merged), then transfer the
38 --dependent record to the new parent. Before transferring check if a
39 --similar record exists on the new parent. If a duplicate exists then
40 --do not transfer and return the id of the duplicate record as the
41 --Merged To Id.
42
43 IF p_from_fk_id <> p_to_fk_id
44 THEN
45
46 /*Party_Id references are merged when:
47 When qualifier_context = 'ASOPARTYINFO' AND
48 qualifier_attribute = 'QUALIFIER_ATTRIBUTE1' --Customer Party
49 OR
50 qualifier_context = 'CUSTOMER' AND
51 qualifier_attribute = 'QUALIFIER_ATTRIBUTE16' --Party Id
52 OR
53 qualifier_context = 'CUSTOMER_GROUP' AND
54 qualifier_attribute = 'QUALIFIER_ATTRIBUTE3' --Buying Groups
55 OR
56 qualifier_context = 'PARTY' AND
57 qualifier_attribute = 'QUALIFIER_ATTRIBUTE1' --Supplier
58 OR
59 qualifier_context = 'PARTY' AND
60 qualifier_attribute = 'QUALIFIER_ATTRIBUTE2' --Buyer
61 */
62
63 /*Party_Site_Id references are merged when:
64 When qualifier_context = 'ASOPARTYINFO' AND
65 qualifier_attribute = 'QUALIFIER_ATTRIBUTE10' --Ship To Party Site
66 OR
67 qualifier_context = 'ASOPARTYINFO' AND
68 qualifier_attribute = 'QUALIFIER_ATTRIBUTE11' --Bill To Party Site
69 OR
70 a.qualifier_context = 'CUSTOMER' AND
71 a.qualifier_attribute = 'QUALIFIER_ATTRIBUTE17' --Ship To Party Site
72 OR
73 a.qualifier_context = 'CUSTOMER' AND
74 a.qualifier_attribute = 'QUALIFIER_ATTRIBUTE18' --Invoice To Party Site
75 */
76 BEGIN
77 SELECT a.qualifier_id
78 INTO p_to_id
79 FROM qp_qualifiers a
80 WHERE a.qualifier_attr_value = to_char(p_to_fk_id)
81 AND trunc(l_temp_date) between nvl(trunc(start_date_active), trunc(l_temp_date)) and
82 nvl(trunc(end_date_active), trunc(l_temp_date))
83 AND (a.qualifier_context,
84 a.qualifier_attribute,
85 nvl(a.list_header_id, -1),
86 nvl(a.list_line_id, -1),
87 nvl(qualifier_rule_id, -1),
88 a.qualifier_grouping_no) IN
89 (SELECT b.qualifier_context, b.qualifier_attribute,
90 nvl(b.list_header_id, -1),
91 nvl(b.list_line_id, -1),
92 nvl(qualifier_rule_id, -1),
93 b.qualifier_grouping_no
94 FROM qp_qualifiers b
95 WHERE b.qualifier_id = p_from_id
96 AND b.qualifier_id <> a.qualifier_id)
97 AND rownum = 1;
98
99 EXCEPTION
100 WHEN NO_DATA_FOUND THEN
101 p_to_id := NULL;
102 END;
103
104 IF p_to_id IS NULL THEN /* Duplicate Does Not Exist. Therefore Transfer*/
105
106 UPDATE qp_qualifiers
107 SET qualifier_attr_value = to_char(p_to_fk_id),
108 last_update_date = hz_utility_pub.last_update_date,
109 last_updated_by = hz_utility_pub.user_id,
110 last_update_login = hz_utility_pub.last_update_login,
111 request_id = hz_utility_pub.request_id,
112 program_application_id = hz_utility_pub.program_application_id,
113 program_id = hz_utility_pub.program_id,
114 program_update_date = sysdate
115 WHERE qualifier_id = p_from_id;
116
117 RETURN;
118
119 END IF; --If p_to_id is null
120
121
122 IF p_to_id IS NOT NULL THEN /* Duplicate Exists. Therefore Merge and set
123 the status of the entity as Merged. The
124 Merged_To Id is the duplicate found on the
125 new parent */
126 -- Start for bug 8399342 / 8210994 - deleting instead of end dating duplicate qualifiers
127 /* UPDATE qp_qualifiers
128 SET qualifier_attr_value = to_char(p_to_fk_id),
129 end_date_active = sysdate,
130 last_update_date = hz_utility_pub.last_update_date,
131 last_updated_by = hz_utility_pub.user_id,
132 last_update_login = hz_utility_pub.last_update_login,
133 request_id = hz_utility_pub.request_id,
134 program_application_id = hz_utility_pub.program_application_id,
135 program_id = hz_utility_pub.program_id,
136 program_update_date = sysdate
137 WHERE qualifier_id = p_from_id;
138 */
139 DELETE qp_qualifiers
140 WHERE qualifier_id = p_from_id;
141 -- End for bug 8399342 / 8210994 - deleting instead of end dating duplicate qualifiers
142 RETURN;
143
144 END IF; --If p_to_id is not null
145
146
147 END IF; --If p_from_fk_id and p_to_fk_id are different
148
149 EXCEPTION
150 WHEN OTHERS THEN
151 FND_MESSAGE.SET_NAME('AR','HZ_API_OTHERS_EXCEP');
152 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
153 FND_MSG_PUB.ADD;
154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
155
156 END Merge_Qualifiers;
157
158 END QP_PARTY_MERGE_PKG;
[Home] [Help]