1 PACKAGE BODY PON_PROJECTS_INTEGRATION_GRP AS
2 --$Header: PONGPRJB.pls 120.0 2005/11/08 14:51 smhanda noship $
3
4 -- module name for logging message
5 g_module_prefix CONSTANT VARCHAR2(40) := 'pon.plsql.PON_PROJECTS_INTEGRATION_GRP.';
6
7 -- Read the profile option that enables/disables the debug log
8 g_fnd_debug CONSTANT VARCHAR2(1) :=
9 NVL (FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
10
11
12 --checks if passed in project_id being used in any negotiation
13 --This API is called by Oracle Projects before deleting any project in Oracle Projects
14 PROCEDURE CHECK_DELETE_PROJECT_OK(
15 p_api_version IN NUMBER,
16 p_init_msg_list IN VARCHAR2,
17 p_project_id IN NUMBER,
18 x_return_status OUT NOCOPY VARCHAR2,
19 x_msg_count OUT NOCOPY NUMBER,
20 x_msg_data OUT NOCOPY VARCHAR2
21 ) IS
22 -- Remember to change the l_api_version for change in the API
23 --
24 l_api_version CONSTANT NUMBER := 1.0;
25
26
27 --
28 -- define local variables
29 --
30 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_DELETE_PROJECT_OK';
31 l_delete_ok varchar2(1);
32
33 BEGIN
34 IF g_fnd_debug = 'Y' then
35 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
36 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
37 MODULE =>g_module_prefix||l_api_name,
38 MESSAGE =>'100: Start' ||l_api_name);
39 END IF;
40 End if;
41
42
43 --
44 -- Standard call to check for call compatibility
45 --
46 IF NOT FND_API.COMPATIBLE_API_CALL ( l_api_version,
47 p_api_version,
48 l_api_name,
49 'PON_PROJECTS_INTEGRATION_GRP' )
50 THEN
51 RAISE FND_API.G_EXC_ERROR;
52 END IF;
53
54 --
55 -- Initialize message list if p_init_msg_list is set to TRUE
56 -- We initialize the list by default. User should pass proper
57 -- value to p_init_msg_list in case this initialization is not
58 -- wanted
59 --
60 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
61 FND_MSG_PUB.INITIALIZE;
62 END IF;
63
64 --
65 -- Initialize API to return the status as success initially
66 x_return_status := FND_API.G_RET_STS_SUCCESS;
67 l_delete_ok := 'Y';
68
69 -- The below query looks for project_id reference at aution header, line
70 -- and payment level
71 SELECT 'N'
72 INTO l_delete_ok
73 FROM dual
74 WHERE EXISTS
75 (SELECT '1'
76 FROM pon_auction_headers_all
77 WHERE project_id = p_project_id
78 ) OR
79 EXISTS
80 (SELECT '1'
81 FROM pon_auction_item_prices_all
82 WHERE project_id = p_project_id
83 ) OR
84 EXISTS
85 (SELECT '1'
86 FROM pon_auc_payments_shipments
87 WHERE project_id = p_project_id
88 );
89
90
91 -- if control reaches here , that means passed in project_id was found in
92 -- some table and should not be deleted
93 IF ( l_delete_ok = 'N')
94 THEN
95 x_return_status := FND_API.G_RET_STS_ERROR;
96
97 -- add message in queue
98 FND_MESSAGE.set_name('PON', 'PON_PROJECT_USED_NO_DELETE');
99 FND_MSG_PUB.Add;
100 x_msg_data := 'PON_PROJECT_USED_NO_DELETE';
101
102 IF g_fnd_debug = 'Y' then
103 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
104 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
105 MODULE =>g_module_prefix||l_api_name,
106 MESSAGE =>'300: The Project Id refrenced in Sourcing');
107 END IF;
108 End IF;
109 END IF;
110 IF g_fnd_debug = 'Y' then
111 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
112 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_STATEMENT,
113 MODULE =>g_module_prefix||l_api_name,
114 MESSAGE =>'400: End' ||l_api_name);
115 END IF;
116 End IF;
117 EXCEPTION
118 WHEN NO_DATA_FOUND THEN
119 x_return_status := FND_API.G_RET_STS_SUCCESS;
120 IF g_fnd_debug = 'Y' then
121 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
122 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
123 MODULE =>g_module_prefix||l_api_name,
124 MESSAGE =>'450:Exception when no data found ');
125 END IF;
126 END IF;
127
128
129 WHEN FND_API.G_EXC_ERROR then
130 x_return_status := FND_API.G_RET_STS_ERROR;
131 FND_MSG_PUB.Count_And_Get
132 (p_count => x_msg_count,
133 p_data => x_msg_data );
134
135
136 IF g_fnd_debug = 'Y' then
137 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
138 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
139 MODULE =>g_module_prefix||l_api_name,
140 MESSAGE =>'470: expected error ');
141 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
142 MODULE =>g_module_prefix||l_api_name,
143 MESSAGE =>'470:errors '||FND_MSG_PUB.Get(p_msg_index=>nvl(x_msg_count,1),p_encoded =>'F' ));
144 END IF;
145 END IF;
146
147
148 WHEN OTHERS THEN
149 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
150 IF FND_MSG_PUB.Check_Msg_Level
151 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
152 THEN
153 FND_MSG_PUB.Add_Exc_Msg
154 (p_pkg_name => 'PON_PROJECTS_INTEGRATION_GRP',
155 p_procedure_name => l_api_name);
156 END IF;
157
158 FND_MSG_PUB.Count_And_Get
159 (p_count => x_msg_count,
160 p_data => x_msg_data );
161
162
163 IF g_fnd_debug = 'Y' then
164 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
165 FND_LOG.string(LOG_LEVEL=>FND_LOG.LEVEL_EXCEPTION,
166 MODULE =>g_module_prefix||l_api_name,
167 MESSAGE =>'500:Exception UnExpected error '||sqlcode||':'||sqlerrm);
168 END IF;
169 END IF;
170
171 END CHECK_DELETE_PROJECT_OK;
172
173 END PON_PROJECTS_INTEGRATION_GRP;
[Home] [Help]