[Home] [Help]
PACKAGE BODY: APPS.EDW_SOURCE_INST_PKG
Source
1 PACKAGE BODY EDW_SOURCE_INST_PKG AS
2 /* $Header: EDWSRCB.pls 115.0 99/09/03 14:12:38 porting shi $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | EDWSRCIN.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | API for translation, loading, downloading data for |
13 REM | edw_source_instances |
14 REM |
15 REM |
16 REM | NOTES |
17 REM | |
18 REM | HISTORY |
19 REM | 08-04-99 arsantha creation
20 REM +=======================================================================+
21 */
22 --
23 G_PKG_NAME CONSTANT VARCHAR2(30):='EDW_SOURCE_INST_PKG';
24 --
25 --
26 --
27 Procedure Translate_edw_source_instances
28 ( p_api_version IN NUMBER
29 , p_commit IN VARCHAR2 := FND_API.G_FALSE
30 , p_src_inst_rec IN EDW_SOURCE_INST_PKG.src_inst_rec_type
31 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
32 , x_return_status OUT VARCHAR2
33 )
34 IS
35 l_user_id NUMBER;
36 l_login_id NUMBER;
37
38 BEGIN
39 IF p_owner = BIS_UTILITIES_PUB.G_SEED_OWNER THEN
40 l_user_id := BIS_UTILITIES_PUB.G_SEED_USER_ID;
41 ELSE
42 l_user_id := BIS_UTILITIES_PUB.G_CUSTOM_USER_ID;
43 END IF;
44
45 l_login_id := fnd_global.LOGIN_ID;
46
47
48 UPDATE edw_source_instances_tl
49 SET DESCRIPTION = p_src_inst_rec.description
50 ,NAME = p_src_inst_rec.name
51 , LAST_UPDATE_DATE = SYSDATE
52 , LAST_UPDATED_BY = l_user_id
53 , LAST_UPDATE_LOGIN = l_login_id
54 , SOURCE_LANG = userenv('LANG')
55 where instance_code = p_src_inst_rec.instance_code
56 and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
57
58 EXCEPTION
59 WHEN NO_DATA_FOUND THEN
60 x_return_status := FND_API.G_RET_STS_ERROR ;
61 when FND_API.G_EXC_ERROR then
62 x_return_status := FND_API.G_RET_STS_ERROR ;
63 when FND_API.G_EXC_UNEXPECTED_ERROR then
64 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
65 when others then
66 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
67
68
69 END Translate_Edw_source_instances ;
70 --
71 Procedure Load_Edw_source_instances
72 ( p_api_version IN NUMBER
73 , p_commit IN VARCHAR2 := FND_API.G_FALSE
74 , p_src_inst_rec IN EDW_SOURCE_INST_PKG.src_inst_rec_type
75 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
76 , x_return_status OUT VARCHAR2)
77 IS
78 l_user_id NUMBER;
79 l_login_id NUMBER;
80
81 BEGIN
82
83 IF p_owner = BIS_UTILITIES_PUB.G_SEED_OWNER THEN
84 l_user_id := BIS_UTILITIES_PUB.G_SEED_USER_ID;
85 ELSE
86 l_user_id := BIS_UTILITIES_PUB.G_CUSTOM_USER_ID;
87 END IF;
88
89 l_login_id := fnd_global.LOGIN_ID;
90
91
92 BEGIN
93
94 UPDATE edw_source_instances
95 SET warehouse_to_instance_link = p_src_inst_rec.LINK
96 , LAST_UPDATE_DATE = SYSDATE
97 , LAST_UPDATED_BY = l_user_id
98 , LAST_UPDATE_LOGIN = l_login_id
99 where instance_code = p_src_inst_rec.instance_code;
100
101 UPDATE edw_source_instances_tl
102 SET DESCRIPTION = p_src_inst_rec.description
103 ,NAME = p_src_inst_rec.name
104 , LAST_UPDATE_DATE = SYSDATE
105 , LAST_UPDATED_BY = l_user_id
106 , LAST_UPDATE_LOGIN = l_login_id
107 , SOURCE_LANG = userenv('LANG')
108 where instance_code = p_src_inst_rec.instance_code
109 and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
110
111 EXCEPTION
112 WHEN NO_DATA_FOUND OR FND_API.G_EXC_ERROR then
113
114 INSERT INTO EDW_SOURCE_INSTANCES(
115 INSTANCE_CODE, ENABLED_FLAG,
116 WAREHOUSE_TO_INSTANCE_LINK, CREATION_DATE,
117 CREATED_BY, LAST_UPDATE_DATE,
118 LAST_UPDATE_LOGIN, LAST_UPDATED_BY)
119 values
120 ( p_src_inst_rec.Instance_code, p_src_inst_rec.enabled_flag,
121 p_src_inst_rec.link, sysdate,
122 l_user_id, sysdate,
123 l_user_id, l_login_id);
124
125 INSERT INTO EDW_SOURCE_INSTANCES_TL(
126 INSTANCE_CODE, LANGUAGE,
127 TRANSLATED, SOURCE_LANG,
128 NAME, DESCRIPTION,
129 CREATION_DATE, CREATED_BY,
130 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, LAST_UPDATED_BY)
131 select e.instance_code, l.language_code,
132 'Y', userenv('LANG'),
133 p_src_inst_rec.name, p_src_inst_rec.description,
134 sysdate, l_user_id,
135 sysdate, l_login_id, l_user_id
136 from fnd_languages L,
137 edw_source_instances e
138 where l.installed_flag in ('I', 'B')
139 and e.instance_code = p_src_inst_rec.instance_code
140 and NOT EXISTS
141 (SELECT 'EXISTS' FROM edw_source_instances e, edw_source_instances_tl tl
142 WHERE tl.instance_code = e.instance_code
143 and tl.language = l.language_code);
144
145 WHEN OTHERS THEN
146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
147 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
148
149 END;
150
151 EXCEPTION
152 WHEN NO_DATA_FOUND THEN
153 x_return_status := FND_API.G_RET_STS_ERROR ;
154 when FND_API.G_EXC_ERROR then
155 x_return_status := FND_API.G_RET_STS_ERROR ;
156 when FND_API.G_EXC_UNEXPECTED_ERROR then
157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
158 when others then
159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
160
161 END Load_edw_source_instances ;
162 --
163 --
164 END EDW_SOURCE_INST_PKG;