[Home] [Help]
PACKAGE BODY: APPS.JTF_LOC_POSTAL_CODES_PKG
Source
1 package body JTF_LOC_POSTAL_CODES_PKG as
2 /* $Header: jtfllopb.pls 120.2 2005/08/18 23:07:51 stopiwal ship $ */
3 procedure INSERT_ROW (
4 X_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
5 X_LOCATION_POSTAL_CODE_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_ORIG_SYSTEM_REF in VARCHAR2,
8 X_ORIG_SYSTEM_ID in NUMBER,
9 X_LOCATION_AREA_ID in NUMBER,
10 X_START_DATE_ACTIVE in DATE,
11 X_END_DATE_ACTIVE in DATE,
12 X_POSTAL_CODE_START in VARCHAR2,
13 X_POSTAL_CODE_END in VARCHAR2,
14 X_CREATION_DATE in DATE,
15 X_CREATED_BY in NUMBER,
16 X_LAST_UPDATE_DATE in DATE,
17 X_LAST_UPDATED_BY in NUMBER,
18 X_LAST_UPDATE_LOGIN in NUMBER) is
19 cursor c is select ROWID from JTF_LOC_POSTAL_CODES
20 where LOCATION_POSTAL_CODE_ID = X_LOCATION_POSTAL_CODE_ID
21 ;
22 begin
23 insert into JTF_LOC_POSTAL_CODES (
24 LOCATION_POSTAL_CODE_ID,
25 OBJECT_VERSION_NUMBER,
26 ORIG_SYSTEM_REF,
27 ORIG_SYSTEM_ID,
28 LOCATION_AREA_ID,
29 START_DATE_ACTIVE,
30 END_DATE_ACTIVE,
31 POSTAL_CODE_START,
32 POSTAL_CODE_END,
33 CREATION_DATE,
34 CREATED_BY,
35 LAST_UPDATE_DATE,
36 LAST_UPDATED_BY,
37 LAST_UPDATE_LOGIN
38 ) values (
39 X_LOCATION_POSTAL_CODE_ID,
40 X_OBJECT_VERSION_NUMBER,
41 X_ORIG_SYSTEM_REF,
42 X_ORIG_SYSTEM_ID,
43 X_LOCATION_AREA_ID,
44 X_START_DATE_ACTIVE,
45 X_END_DATE_ACTIVE,
46 X_POSTAL_CODE_START,
47 X_POSTAL_CODE_END,
48 X_CREATION_DATE,
49 X_CREATED_BY,
50 X_LAST_UPDATE_DATE,
51 X_LAST_UPDATED_BY,
52 X_LAST_UPDATE_LOGIN
53 );
54
55 open c;
56 fetch c into X_ROWID;
57 if (c%notfound) then
58 close c;
59 raise no_data_found;
60 end if;
61 close c;
62 end INSERT_ROW;
63
64 procedure UPDATE_ROW (
65 X_LOCATION_POSTAL_CODE_ID in NUMBER,
66 X_OBJECT_VERSION_NUMBER in NUMBER,
67 X_ORIG_SYSTEM_REF in VARCHAR2,
68 X_ORIG_SYSTEM_ID in NUMBER,
69 X_LOCATION_AREA_ID in NUMBER,
70 X_START_DATE_ACTIVE in DATE,
71 X_END_DATE_ACTIVE in DATE,
72 X_POSTAL_CODE_START in VARCHAR2,
73 X_POSTAL_CODE_END in VARCHAR2,
74 X_LAST_UPDATE_DATE in DATE,
75 X_LAST_UPDATED_BY in NUMBER,
76 X_LAST_UPDATE_LOGIN in NUMBER
77 ) is
78 begin
79 update JTF_LOC_POSTAL_CODES set
80 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
81 ORIG_SYSTEM_REF = X_ORIG_SYSTEM_REF,
82 ORIG_SYSTEM_ID = X_ORIG_SYSTEM_ID,
83 LOCATION_AREA_ID = X_LOCATION_AREA_ID,
84 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
85 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
86 POSTAL_CODE_START = X_POSTAL_CODE_START,
87 POSTAL_CODE_END = X_POSTAL_CODE_END,
88 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
89 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
90 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
91 where LOCATION_POSTAL_CODE_ID = X_LOCATION_POSTAL_CODE_ID;
92
93 if (sql%notfound) then
94 raise no_data_found;
95 end if;
96 end UPDATE_ROW;
97
98 procedure DELETE_ROW (
99 X_LOCATION_POSTAL_CODE_ID in NUMBER
100 ) is
101 begin
102 delete from JTF_LOC_POSTAL_CODES
103 where LOCATION_POSTAL_CODE_ID = X_LOCATION_POSTAL_CODE_ID;
104
105 if (sql%notfound) then
106 raise no_data_found;
107 end if;
108 end DELETE_ROW;
109
110 procedure LOAD_ROW(
111 X_LOCATION_POSTAL_CODE_ID in NUMBER,
112 X_ORIG_SYSTEM_REF in VARCHAR2,
113 X_ORIG_SYSTEM_ID in NUMBER,
114 X_LOCATION_AREA_ID in NUMBER,
115 X_START_DATE_ACTIVE in DATE,
116 X_END_DATE_ACTIVE in DATE,
117 X_POSTAL_CODE_START in VARCHAR2,
118 X_POSTAL_CODE_END in VARCHAR2,
119 X_OWNER in VARCHAR2
120 ) is
121
122 l_user_id number := 0;
123 l_obj_verno number;
124 l_dummy_char varchar2(1);
125 l_row_id varchar2(100);
126 l_code_id number;
127
128 cursor c_obj_verno is
129 select object_version_number
130 from JTF_LOC_POSTAL_CODES
131 where LOCATION_POSTAL_CODE_ID = X_LOCATION_POSTAL_CODE_ID;
132
133 cursor c_chk_code_exists is
134 select 'x'
135 from JTF_LOC_POSTAL_CODES
136 where LOCATION_POSTAL_CODE_ID = X_LOCATION_POSTAL_CODE_ID;
137
138 cursor c_get_codeid is
139 select JTF_LOC_POSTAL_CODES_S.nextval
140 from dual;
141
142 BEGIN
143
144 if X_OWNER = 'SEED' then
145 l_user_id := 1;
146 end if;
147
148 open c_chk_code_exists;
149 fetch c_chk_code_exists into l_dummy_char;
150 if c_chk_code_exists%notfound
151 then
152 close c_chk_code_exists;
153 if X_LOCATION_POSTAL_CODE_ID is null
154 then
155 open c_get_codeid;
156 fetch c_get_codeid into l_code_id;
157 close c_get_codeid;
158 else
159 l_code_id := X_LOCATION_POSTAL_CODE_ID;
160 end if;
161 l_obj_verno := 1;
162 JTF_LOC_POSTAL_CODES_PKG.INSERT_ROW(
163 X_ROWID => l_row_id,
164 X_LOCATION_POSTAL_CODE_ID => l_code_id,
165 X_OBJECT_VERSION_NUMBER => l_obj_verno,
166 X_ORIG_SYSTEM_REF => X_ORIG_SYSTEM_REF,
167 X_ORIG_SYSTEM_ID => X_ORIG_SYSTEM_ID,
168 X_LOCATION_AREA_ID => X_LOCATION_AREA_ID,
169 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
170 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
171 X_POSTAL_CODE_START => X_POSTAL_CODE_START,
172 X_POSTAL_CODE_END => X_POSTAL_CODE_END,
173 X_CREATION_DATE => SYSDATE,
174 X_CREATED_BY => l_user_id,
175 X_LAST_UPDATE_DATE => SYSDATE,
176 X_LAST_UPDATED_BY => l_user_id,
177 X_LAST_UPDATE_LOGIN => 0
178 );
179 else
180 close c_chk_code_exists;
181 open c_obj_verno;
182 fetch c_obj_verno into l_obj_verno;
183 close c_obj_verno;
184 JTF_LOC_POSTAL_CODES_PKG.UPDATE_ROW(
185 X_LOCATION_POSTAL_CODE_ID => X_LOCATION_POSTAL_CODE_ID,
186 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1,
187 X_ORIG_SYSTEM_REF => X_ORIG_SYSTEM_REF,
188 X_ORIG_SYSTEM_ID => X_ORIG_SYSTEM_ID,
189 X_LOCATION_AREA_ID => X_LOCATION_AREA_ID,
190 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
191 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
192 X_POSTAL_CODE_START => X_POSTAL_CODE_START,
193 X_POSTAL_CODE_END => X_POSTAL_CODE_END,
194 X_LAST_UPDATE_DATE => SYSDATE,
195 X_LAST_UPDATED_BY => l_user_id,
196 X_LAST_UPDATE_LOGIN => 0
197 );
198 end if;
199 END LOAD_ROW;
200
201 end JTF_LOC_POSTAL_CODES_PKG;