1 PACKAGE BODY JL_VENDORMERGE_GRP AS
2 /* $Header: jlzzpsmb.pls 120.4.12020000.2 2013/01/02 15:02:52 mbarrett ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'JL_VENDORMERGE_GRP_PKG';
5 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_MODULE_NAME CONSTANT VARCHAR2(250) := 'ZX.PLSQL.JL_VENDORMERGE_GRP_PKG.';
13
14 /********************************************************************
15 Prodeure: Merge_Vendor
16 Objective: This package is called from AP - Supplier Merge Process
17 Following procedure will update the vendor_id and
18 vendor_site_id in JL tables.
19 Parameters: p_vendor_id is supplier to
20 p_dup_vendor_id supplier from
21
22 *******************************************************************/
23
24 Procedure Merge_Vendor
25 (p_api_version IN NUMBER
26 ,p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
27 ,p_commit IN VARCHAR2 default FND_API.G_FALSE
28 ,p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
29 ,p_return_status OUT NOCOPY VARCHAR2
30 ,p_msg_count OUT NOCOPY NUMBER
31 ,p_msg_data OUT NOCOPY VARCHAR2
32 ,p_vendor_id IN NUMBER --> Represents Merge To Vendor
33 ,p_dup_vendor_id IN NUMBER --> Represents Merge From Vendor
34 ,p_vendor_site_id IN NUMBER --> Represents Merge To Vendor Site
35 ,p_dup_vendor_site_id IN NUMBER --> Represents Merge From Vendor Site
36 ,p_party_id IN NUMBER --> Represents Merge To Party
37 ,P_dup_party_id IN NUMBER --> Represents Merge From Party
38 ,p_party_site_id IN NUMBER --> Represents Merge To Party Site
39 ,p_dup_party_site_id IN NUMBER --> Represents Merge From Party Site
40 ) IS
41
42 l_api_name CONSTANT VARCHAR2(50) := 'Merge_Vendor';
43 l_api_version CONSTANT NUMBER := 1.0;
44
45 -- Bug 16036552 : Start
46 Cursor C_NewSuppNum Is
47 Select segment1
48 From ap_suppliers
49 Where vendor_id = p_vendor_id;
50
51 Cursor C_OldSuppNum Is
52 Select segment1
53 From ap_suppliers
54 Where vendor_id = p_dup_vendor_id;
55 -- Bug 16036552 : End
56
57 Begin
58
59 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
60 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
61 END IF;
62
63 SAVEPOINT import_document_PVT;
64
65 -- Standard call to check for call compatibility
66 IF NOT FND_API.Compatible_API_Call(l_api_version,
67 p_api_version,
68 l_api_name,
69 G_PKG_NAME
70 ) THEN
71 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
72 END IF;
73
74 -- Initialize API message list if necessary.
75 -- Initialize message list if p_init_msg_list is set to TRUE.
76 IF FND_API.to_Boolean( p_init_msg_list) THEN
77 FND_MSG_PUB.initialize;
78 END IF;
79
80
81 p_return_status := FND_API.G_RET_STS_SUCCESS;
82
83 IF p_dup_vendor_id is not null and p_dup_vendor_site_id is null
84 and p_vendor_id is not null Then
85
86 SAVEPOINT jl_vendor_merge;
87
88 -- Update JL_ZZ_AP_SUPP_AWT_TYPES - vendor_id
89 Update JL_ZZ_AP_SUPP_AWT_TYPES
90 set vendor_id = p_vendor_id
91 where vendor_id = p_dup_vendor_id;
92
93 -- Update JL_BR_AP_COLLECTION_DOCS_ALL - vendor_id
94 Update JL_BR_AP_COLLECTION_DOCS_ALL
95 set vendor_id = p_vendor_id
96 where vendor_id = p_dup_vendor_id;
97
98 -- Update JL_BR_AP_CONSOLID_INVOICES_ALL - vendor_id
99 Update JL_BR_AP_CONSOLID_INVOICES_ALL
100 set vendor_id = p_vendor_id
101 where vendor_id = p_dup_vendor_id;
102
103 -- Update JG_ZZ_ENTITY_ASSOC
104 Update JG_ZZ_ENTITY_ASSOC
105 set associated_entity_id = p_vendor_id
106 where associated_entity_id = p_dup_vendor_id;
107
108 ELSif p_dup_vendor_id is not null and p_dup_vendor_site_id is not null
109 and p_vendor_id is not null and p_vendor_site_id is not null Then
110
111 -- Update JL_BR_AP_COLLECTION_DOCS_ALL - vendor_id
112 Update JL_BR_AP_COLLECTION_DOCS_ALL
113 set vendor_id = p_vendor_id,
114 vendor_site_id = p_vendor_site_id
115 where vendor_id = p_dup_vendor_id
116 and vendor_site_id = p_dup_vendor_site_id;
117
118 -- Update JL_BR_AP_CONSOLID_INVOICES_ALL - vendor_id
119 Update JL_BR_AP_CONSOLID_INVOICES_ALL
120 set vendor_id = p_vendor_id,
121 vendor_site_id = p_vendor_site_id
122 where vendor_id = p_dup_vendor_id
123 and vendor_site_id = p_dup_vendor_site_id;
124
125 END IF;
126
127 -- Bug 16036552 : Start
128 If p_dup_vendor_id is not null and p_vendor_id is not null Then
129 For NewSuppNumRec in C_NewSuppNum Loop
130 For OldSuppNumRec in C_OldSuppNum Loop
131 Update ap_invoice_distributions_all
132 Set global_attribute2 = NewSuppNumRec.segment1
133 Where global_attribute2 = OldSuppNumRec.segment1
134 And global_attribute_category = 'JL.CO.APXINWKB.DISTRIBUTIONS';
135 End Loop;
136 End Loop;
137 End If;
138 -- Bug 16036552 : End
139
140 IF FND_API.To_Boolean( p_commit ) THEN
141 COMMIT WORK;
142 END IF;
143
144 -- Standard check of p_commit.
145 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
146 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'()-');
147 END IF;
148
149 EXCEPTION
150 WHEN NO_DATA_FOUND THEN
151 NULL;
152 WHEN OTHERS THEN
153 p_return_status := FND_API.G_RET_STS_ERROR ;
154 ROLLBACK TO jl_vendor_merge;
155 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
156 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
157 FND_MSG_PUB.Add;
158 /*---------------------------------------------------------+
159 | FND_MSG_PUB.Count_And_Get used to get the count of mesg.|
160 | in the message stack. If there is only one message in |
161 | the stack it retrieves this message |
162 +---------------------------------------------------------*/
163 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
164 p_count => p_msg_count,
165 p_data => p_msg_data
166 );
167 --
168 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
169 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
170 END IF;
171
172 END Merge_Vendor;
173
174 END JL_VENDORMERGE_GRP;
[Home] [Help]