[Home] [Help]
PACKAGE BODY: APPS.MRP_VENDORMERGE_GRP
Source
1 PACKAGE BODY MRP_VendorMerge_GRP AS
2 /* $Header: MRPGVDRB.pls 120.1 2005/09/06 13:52:38 ichoudhu noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='MRP_VendorMerge_GRP';
5
6 Procedure Merge_Vendor( p_api_version IN NUMBER,
7 p_init_msg_list IN VARCHAR2 default
8 FND_API.G_FALSE,
9 p_commit IN VARCHAR2 default
10 FND_API.G_FALSE,
11 p_validation_level IN NUMBER :=
12 FND_API.G_VALID_LEVEL_FULL,
13 x_return_status OUT NOCOPY VARCHAR2,
14 x_msg_count OUT NOCOPY NUMBER,
15 x_msg_data OUT NOCOPY VARCHAR2,
16 p_vendor_id IN NUMBER,
17 p_vendor_site_id IN NUMBER,
18 p_dup_vendor_id IN NUMBER,
19 p_dup_vendor_site_id IN NUMBER )
20
21 IS
22
23 l_api_name CONSTANT VARCHAR2(30) := 'Merge_Vendor';
24 l_api_version CONSTANT NUMBER := 1.0;
25 l_row_count NUMBER;
26 TYPE t_sr_receipt_id IS TABLE OF mrp_sr_receipt_org.sr_receipt_id%TYPE;
27 l_sr_receipt_id t_sr_receipt_id;
28 l_sourcing_rule_name mrp_sourcing_rules.sourcing_rule_name%TYPE;
29
30 CURSOR c1(receipt_id NUMBER) IS
31 SELECT SUM(allocation_percent) , rank
32 FROM mrp_sr_source_org
33 WHERE sr_receipt_id = receipt_id
34 GROUP BY rank
35 HAVING sum(allocation_percent) <> 100;
36 BEGIN
37
38 -- Initialize API return status to success
39 x_return_status := FND_API.G_RET_STS_SUCCESS;
40
41
42 -- Check for call compatibility.
43 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
44 p_api_version ,
45 l_api_name ,
46 G_PKG_NAME )
47 THEN
48 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
49 END IF;
50
51 -- Initialize API message list if necessary.
52 -- Initialize message list if p_init_msg_list is set to TRUE.
53 IF FND_API.to_Boolean( p_init_msg_list) THEN
54 FND_MSG_PUB.initialize;
55 END IF;
56
57 UPDATE mrp_sr_source_org mrp1
58 SET mrp1.vendor_id = p_vendor_id,
59 mrp1.vendor_site_id = p_vendor_site_id
60 WHERE mrp1.vendor_id = p_dup_vendor_id and
61 mrp1.vendor_site_id = p_dup_vendor_site_id
62 AND not exists
63 (select mrp2.vendor_id
64 from mrp_sr_source_org mrp2
65 where mrp2.vendor_id = p_vendor_id and
66 mrp2.vendor_site_id = p_vendor_site_id and
67 mrp2.sr_receipt_id = mrp1.sr_receipt_id) ;
68
69 UPDATE mrp_sr_source_org mrp1
70 SET mrp1.vendor_id = p_vendor_id
71 WHERE mrp1.vendor_id = p_dup_vendor_id
72 AND mrp1.vendor_site_id is null
73 AND not exists
74 (select mrp2.vendor_id
75 from mrp_sr_source_org mrp2
76 where mrp2.vendor_id = p_vendor_id
77 and mrp2.vendor_site_id is null
78 and mrp2.sr_receipt_id = mrp1.sr_receipt_id) ;
79
80
81 UPDATE mrp_sr_source_org mrp1
82 SET mrp1.allocation_percent
83 = (SELECT sum (mrp3.allocation_percent)
84 FROM mrp_sr_source_org mrp3
85 WHERE mrp3.sr_receipt_id = mrp1.sr_receipt_id
86 AND mrp3.rank = mrp1.rank
87 AND mrp3.vendor_id IN
88 (p_vendor_id, p_dup_vendor_id)
89 AND mrp3.vendor_site_id IN
90 (p_vendor_site_id, p_dup_vendor_site_id ))
91 WHERE mrp1.vendor_id = p_vendor_id
92 AND mrp1.vendor_site_id = p_vendor_site_id
93 AND exists
94 ( select mrp2.vendor_id
95 from mrp_sr_source_org mrp2
96 where mrp2.vendor_id = p_dup_vendor_id
97 and mrp2.vendor_site_id = p_dup_vendor_site_id
98 and mrp2.rank = mrp1.rank
99 and mrp2.sr_receipt_id = mrp1.sr_receipt_id) ;
100
101
102 DELETE from mrp_sr_source_org mrp1
103 WHERE vendor_id = p_dup_vendor_id
104 AND vendor_site_id = p_dup_vendor_site_id
105 RETURNING sr_receipt_id
106 BULK COLLECT INTO l_sr_receipt_id;
107
108 IF SQL%FOUND THEN
109 l_row_count := SQL%ROWCOUNT;
110 ELSE
111 l_row_count := 0;
112 END IF;
113
114 -- Prepare message name
115 FND_MESSAGE.SET_NAME('MRP','MRP_SR_SOURCE_ORG_DELETED');
116 FND_MESSAGE.SET_TOKEN('ROWS_DELETED',l_row_count);
117 -- Add message to API message list.
118 FND_MSG_PUB.Add;
119
120 IF (l_row_count > 0) THEN
121 FOR j IN l_sr_receipt_id.FIRST..l_sr_receipt_id.LAST LOOP
122 FOR c1rec in c1(l_sr_receipt_id(j)) LOOP
123 IF c1%FOUND THEN
124 UPDATE mrp_sourcing_rules
125 SET planning_active = 2
126 WHERE sourcing_rule_id = ( SELECT sourcing_rule_id
127 FROM mrp_sr_receipt_org
128 WHERE sr_receipt_id = l_sr_receipt_id(j))
129 AND planning_active = 1
130 RETURNING sourcing_rule_name INTO l_sourcing_rule_name;
131
132 IF (SQL%ROWCOUNT > 0) THEN
133 FND_MESSAGE.SET_NAME('MRP','MRP_INVALID_STATUS');
134 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_sourcing_rule_name);
135 FND_MSG_PUB.Add;
136 END IF;
137 END IF;
138 END LOOP;
139 END LOOP;
140 END IF;
141
142
143 -- Get message count and if 1, return message data.
144 FND_MSG_PUB.Count_And_Get
145 ( p_count => x_msg_count,
146 p_data => x_msg_data
147 );
148
149 EXCEPTION
150
151 WHEN OTHERS THEN
152 dbms_output.put_line(sqlerrm);
153 ROLLBACK ;
154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
155 FND_MSG_PUB.Count_And_Get
156 ( p_count => x_msg_count,
157 p_data => x_msg_data
158 );
159
160 END Merge_Vendor;
161
162 END MRP_VendorMerge_GRP;
163