1 PACKAGE BODY JL_VENDORMERGE_GRP AS
2 /* $Header: jlzzpsmb.pls 120.4 2006/04/11 21:12:46 dbetanco 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 Begin
46
47 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
48 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
49 END IF;
50
51 SAVEPOINT import_document_PVT;
52
53 -- Standard call to check for call compatibility
54 IF NOT FND_API.Compatible_API_Call(l_api_version,
55 p_api_version,
56 l_api_name,
57 G_PKG_NAME
58 ) THEN
59 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
60 END IF;
61
62 -- Initialize API message list if necessary.
63 -- Initialize message list if p_init_msg_list is set to TRUE.
64 IF FND_API.to_Boolean( p_init_msg_list) THEN
65 FND_MSG_PUB.initialize;
66 END IF;
67
68
69 p_return_status := FND_API.G_RET_STS_SUCCESS;
70
71 IF p_dup_vendor_id is not null and p_dup_vendor_site_id is null
72 and p_vendor_id is not null Then
73
74 SAVEPOINT jl_vendor_merge;
75
76 -- Update JL_ZZ_AP_SUPP_AWT_TYPES - vendor_id
77 Update JL_ZZ_AP_SUPP_AWT_TYPES
78 set vendor_id = p_vendor_id
79 where vendor_id = p_dup_vendor_id;
80
81 -- Update JL_BR_AP_COLLECTION_DOCS_ALL - vendor_id
82 Update JL_BR_AP_COLLECTION_DOCS_ALL
83 set vendor_id = p_vendor_id
84 where vendor_id = p_dup_vendor_id;
85
86 -- Update JL_BR_AP_CONSOLID_INVOICES_ALL - vendor_id
87 Update JL_BR_AP_CONSOLID_INVOICES_ALL
88 set vendor_id = p_vendor_id
89 where vendor_id = p_dup_vendor_id;
90
91 -- Update JG_ZZ_ENTITY_ASSOC
92 Update JG_ZZ_ENTITY_ASSOC
93 set associated_entity_id = p_vendor_id
94 where associated_entity_id = p_dup_vendor_id;
95
96 ELSif p_dup_vendor_id is not null and p_dup_vendor_site_id is not null
97 and p_vendor_id is not null and p_vendor_site_id is not null Then
98
99 -- Update JL_BR_AP_COLLECTION_DOCS_ALL - vendor_id
100 Update JL_BR_AP_COLLECTION_DOCS_ALL
101 set vendor_id = p_vendor_id,
102 vendor_site_id = p_vendor_site_id
103 where vendor_id = p_dup_vendor_id
104 and vendor_site_id = p_dup_vendor_site_id;
105
106 -- Update JL_BR_AP_CONSOLID_INVOICES_ALL - vendor_id
107 Update JL_BR_AP_CONSOLID_INVOICES_ALL
108 set vendor_id = p_vendor_id,
109 vendor_site_id = p_vendor_site_id
110 where vendor_id = p_dup_vendor_id
111 and vendor_site_id = p_dup_vendor_site_id;
112
113 END IF;
114
115 IF FND_API.To_Boolean( p_commit ) THEN
116 COMMIT WORK;
117 END IF;
118
119 -- Standard check of p_commit.
120 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
121 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'()-');
122 END IF;
123
124 EXCEPTION
125 WHEN NO_DATA_FOUND THEN
126 NULL;
127 WHEN OTHERS THEN
128 p_return_status := FND_API.G_RET_STS_ERROR ;
129 ROLLBACK TO jl_vendor_merge;
130 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
131 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
132 FND_MSG_PUB.Add;
133 /*---------------------------------------------------------+
134 | FND_MSG_PUB.Count_And_Get used to get the count of mesg.|
135 | in the message stack. If there is only one message in |
136 | the stack it retrieves this message |
137 +---------------------------------------------------------*/
138 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
139 p_count => p_msg_count,
140 p_data => p_msg_data
141 );
142 --
143 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
144 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
145 END IF;
146
147 END Merge_Vendor;
148
149 END JL_VENDORMERGE_GRP;
[Home] [Help]