1 PACKAGE BODY OE_PARTY_MERGE_PKG AS
2 /* $Header: OEXPMRGB.pls 120.0 2005/06/01 23:06:41 appldev noship $ */
3
4
5 G_PROC_NAME CONSTANT VARCHAR2(30) := 'OE_PARTY_MERGE_PKG';
6 G_USER_ID CONSTANT NUMBER(15) := FND_GLOBAL.USER_ID;
7 G_LOGIN_ID CONSTANT NUMBER(15) := FND_GLOBAL.LOGIN_ID;
8
9 PROCEDURE MERGE_ADJ_ATTRIBS_PARTY (
10 p_entity_name IN VARCHAR2,
11 p_from_id IN NUMBER,
12 x_to_id OUT NOCOPY 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 VARCHAR2)
19 IS
20
21 l_merge_reason_code VARCHAR2(30);
22 l_api_name VARCHAR2(30) := 'ADJ_ATTRIB_PARTY_MERGE';
23 l_count NUMBER(10) := 0;
24 RESOURCE_BUSY EXCEPTION;
25 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
26
27 BEGIN
28 -- arp_message.set_line('OE_PARTY_MERGE_PKG.MERGE_ADJ_ATTRIBS()+');
29
30 x_return_status := FND_API.G_RET_STS_SUCCESS;
31
32 select merge_reason_code
33 into l_merge_reason_code
34 from hz_merge_batch
35 where batch_id = p_batch_id;
36
37 if l_merge_reason_code = 'DUPLICATE' then
38 -- if reason code is duplicate then allow the party merge to happen without
39 -- any validations.
40 null;
41 else
42 -- if there are any validations to be done, include it in this section
43 null;
44 end if;
45
46 -- If the parent has NOT changed (ie.Parent getting transferred) then nothing
47 -- needs to be done. Set Merged To Id is same as Merged From Id and return
48
49 if p_from_fk_id = p_to_fk_id then
50 x_to_id := p_from_id;
51 return;
52 end if;
53
54 -- If the parent has changed(ie. Parent is getting merged) then transfer the
55 -- dependent record to the new parent. Before transferring check if a similar
56 -- dependent record exists on the new parent. If a duplicate exists then do
57 -- not transfer and return the id of the duplicate record as the Merged To Id
58 -- This check is not required for oe_price_adj_attribs
59
60 if p_from_fk_id <> p_to_fk_id then
61 -- obtain lock on records to be updated.
62 --arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
63 --arp_message.set_token( 'TABLE_NAME', 'OE_PRICE_ADJ_ATTRIBS', FALSE );
64
65 UPDATE /*+ index(S OE_PRICE_ADJ_ATTRIBS_N2) */ OE_PRICE_ADJ_ATTRIBS S
66 SET pricing_attr_value_from = to_char(p_to_fk_id),
67 last_update_date = hz_utility_v2pub.last_update_date,
68 last_updated_by = hz_utility_v2pub.user_id,
69 last_update_login = hz_utility_v2pub.last_update_login,
70 request_id = hz_utility_v2pub.request_id,
71 program_application_id = hz_utility_v2pub.program_application_id,
72 program_id = hz_utility_v2pub.program_id,
73 program_update_date = sysdate
74 WHERE pricing_attr_value_from = to_char(p_from_fk_id)
75 and (pricing_context = 'ASOPARTYINFO' AND pricing_attribute = 'QUALIFIER_ATTRIBUTE1'
76 OR pricing_context = 'CUSTOMER' AND pricing_attribute ='QUALIFIER_ATTRIBUTE16'
77 OR pricing_context = 'CUSTOMER_GROUP' AND pricing_attribute = 'QUALIFIER_ATTRIBUTE3'
78 OR pricing_context = 'PARTY' AND pricing_attribute
79 IN ('QUALIFIER_ATTRIBUTE1', 'QUALIFIER_ATTRIBUTE2')
80 );
81 l_count := sql%rowcount;
82
83 --arp_message.set_name('AR', 'AR_ROWS_UPDATED');
84 --arp_message.set_token('NUM_ROWS', to_char(l_count) );
85
86 end if;
87
88 EXCEPTION
89
90 when others then
91 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
92 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
93 FND_MSG_PUB.ADD;
94 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
95
96 END MERGE_ADJ_ATTRIBS_PARTY;
97
98 PROCEDURE MERGE_ADJ_ATTRIBS_PARTY_SITE (
99 p_entity_name IN VARCHAR2,
100 p_from_id IN NUMBER,
101 x_to_id OUT NOCOPY NUMBER,
102 p_from_fk_id IN NUMBER,
103 p_to_fk_id IN NUMBER,
104 p_parent_entity_name IN VARCHAR2,
105 p_batch_id IN NUMBER,
106 p_batch_party_id IN NUMBER,
107 x_return_status OUT NOCOPY VARCHAR2)
108 IS
109
110 l_merge_reason_code VARCHAR2(30);
111 l_api_name VARCHAR2(30) := 'ADJ_ATTRIB_PARTY_MERGE';
112 l_count NUMBER(10) := 0;
113 RESOURCE_BUSY EXCEPTION;
114 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
115
116 BEGIN
117 -- arp_message.set_line('OE_PARTY_MERGE_PKG.MERGE_ADJ_ATTRIBS()+');
118
119 x_return_status := FND_API.G_RET_STS_SUCCESS;
120
121 select merge_reason_code
122 into l_merge_reason_code
123 from hz_merge_batch
124 where batch_id = p_batch_id;
125
126 if l_merge_reason_code = 'DUPLICATE' then
127 -- if reason code is duplicate then allow the party merge to happen without
128 -- any validations.
129 null;
130 else
131 -- if there are any validations to be done, include it in this section
132 null;
133 end if;
134
135 -- If the parent has NOT changed (ie.Parent getting transferred) then nothing
136 -- needs to be done. Set Merged To Id is same as Merged From Id and return
137
138 if p_from_fk_id = p_to_fk_id then
139 x_to_id := p_from_id;
140 return;
141 end if;
142
143 -- If the parent has changed(ie. Parent is getting merged) then transfer the
144 -- dependent record to the new parent. Before transferring check if a similar
145 -- dependent record exists on the new parent. If a duplicate exists then do
146 -- not transfer and return the id of the duplicate record as the Merged To Id
147 -- This check is not required for oe_price_adj_attribs
148
149 if p_from_fk_id <> p_to_fk_id then
150 -- obtain lock on records to be updated.
151 --arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
152 --arp_message.set_token( 'TABLE_NAME', 'OE_PRICE_ADJ_ATTRIBS', FALSE );
153
154 UPDATE /*+ index(S OE_PRICE_ADJ_ATTRIBS_N2) */ OE_PRICE_ADJ_ATTRIBS S
155 SET pricing_attr_value_from = to_char(p_to_fk_id),
156 last_update_date = hz_utility_v2pub.last_update_date,
157 last_updated_by = hz_utility_v2pub.user_id,
158 last_update_login = hz_utility_v2pub.last_update_login,
159 request_id = hz_utility_v2pub.request_id,
160 program_application_id = hz_utility_v2pub.program_application_id,
161 program_id = hz_utility_v2pub.program_id,
162 program_update_date = sysdate
163 WHERE pricing_attr_value_from = to_char(p_from_fk_id)
164 AND (pricing_context = 'ASOPARTYINFO'
165 AND pricing_attribute IN ('QUALIFIER_ATTRIBUTE10','QUALIFIER_ATTRIBUTE11')
166 OR pricing_context = 'CUSTOMER'
167 AND pricing_attribute IN ('QUALIFIER_ATTRIBUTE17', 'QUALIFIER_ATTRIBUTE18')
168 );
169
170 l_count := sql%rowcount;
171
172 --arp_message.set_name('AR', 'AR_ROWS_UPDATED');
173 --arp_message.set_token('NUM_ROWS', to_char(l_count) );
174
175 end if;
176
177 EXCEPTION
178
179 when others then
180 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
181 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
182 FND_MSG_PUB.ADD;
183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
184
185 END MERGE_ADJ_ATTRIBS_PARTY_SITE;
186
187 END OE_PARTY_MERGE_PKG;
[Home] [Help]