[Home] [Help]
PACKAGE BODY: APPS.AMS_DLG_IDENTIFY_ITEMS_PKG
Source
1 PACKAGE BODY AMS_DLG_IDENTIFY_ITEMS_PKG as
2 /* $Header: amstdiib.pls 115.0 2002/04/28 20:28:13 pkm ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_DLG_IDENTIFY_ITEMS_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_DLG_IDENTIFY_ITEMS_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstdiib.pls';
19
20 -- ========================================================
21 PROCEDURE Insert_Row(
22 px_item_id IN OUT NUMBER,
23 p_list_source_type_id NUMBER,
24 p_list_source_field_id NUMBER,
25 p_source_type_code VARCHAR2,
26 p_source_column_name VARCHAR2,
27 p_creation_date DATE,
28 p_created_by NUMBER,
29 p_last_update_date DATE,
30 p_last_updated_by NUMBER,
31 p_last_update_login NUMBER,
32 px_object_version_number IN OUT NUMBER,
33 p_rule_id NUMBER)
34
35 IS
36 x_rowid VARCHAR2(30);
37
38
39 BEGIN
40
41
42 px_object_version_number := 1;
43
44
45 INSERT INTO AMS_DLG_IDENTIFY_ITEMS(
46 item_id,
47 list_source_type_id,
48 list_source_field_id,
49 source_type_code,
50 source_column_name,
51 creation_date,
52 created_by,
53 last_update_date,
54 last_updated_by,
55 last_update_login,
56 object_version_number,
57 rule_id
58 ) VALUES (
59 DECODE( px_item_id, FND_API.g_miss_num, NULL, px_item_id),
60 DECODE( p_list_source_type_id, FND_API.g_miss_num, NULL, p_list_source_type_id),
61 DECODE( p_list_source_field_id, FND_API.g_miss_num, NULL, p_list_source_field_id),
62 DECODE( p_source_type_code, FND_API.g_miss_char, NULL, p_source_type_code),
63 DECODE( p_source_column_name, FND_API.g_miss_char, NULL, p_source_column_name),
64 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
65 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
66 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
67 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
68 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
69 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
70 DECODE( p_rule_id, FND_API.g_miss_num, NULL, p_rule_id));
71 END Insert_Row;
72
73 -- ========================================================
74 PROCEDURE Update_Row(
75 p_item_id NUMBER,
76 p_list_source_type_id NUMBER,
77 p_list_source_field_id NUMBER,
78 p_source_type_code VARCHAR2,
79 p_source_column_name VARCHAR2,
80 p_last_update_date DATE,
81 p_last_updated_by NUMBER,
82 p_last_update_login NUMBER,
83 p_object_version_number NUMBER,
84 p_rule_id NUMBER)
85
86 IS
87 BEGIN
88 Update AMS_DLG_IDENTIFY_ITEMS
89 SET
90 item_id = DECODE( p_item_id, FND_API.g_miss_num, item_id, p_item_id),
91 list_source_type_id = DECODE( p_list_source_type_id, FND_API.g_miss_num, list_source_type_id, p_list_source_type_id),
92 list_source_field_id = DECODE( p_list_source_field_id, FND_API.g_miss_num, list_source_field_id, p_list_source_field_id),
93 source_type_code = DECODE( p_source_type_code, FND_API.g_miss_char, source_type_code, p_source_type_code),
94 source_column_name = DECODE( p_source_column_name, FND_API.g_miss_char, source_column_name, p_source_column_name),
95 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
96 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
97 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
98 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number),
99 rule_id = DECODE( p_rule_id, FND_API.g_miss_num, rule_id, p_rule_id)
100 WHERE ITEM_ID = p_ITEM_ID
101 AND object_version_number = p_object_version_number;
102
103 IF (SQL%NOTFOUND) THEN
104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
105 END IF;
106 END Update_Row;
107
108 -- ========================================================
109 PROCEDURE Delete_Row(
110 p_ITEM_ID NUMBER)
111 IS
112 BEGIN
113 DELETE FROM AMS_DLG_IDENTIFY_ITEMS
114 WHERE ITEM_ID = p_ITEM_ID;
115 If (SQL%NOTFOUND) then
116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
117 End If;
118 END Delete_Row ;
119 -- ========================================================
120 PROCEDURE load_row (
121 p_item_id NUMBER,
122 p_list_source_type_id NUMBER,
123 p_list_source_field_id NUMBER,
124 p_source_type_code VARCHAR2,
125 p_source_column_name VARCHAR2,
126 p_rule_id NUMBER,
127 p_owner VARCHAR2
128 )
129 IS
130 l_user_id number := 0;
131 l_obj_verno number;
132 l_dummy_char varchar2(1);
133 l_item_id number;
134
135 cursor c_obj_verno is
136 select object_version_number
137 from ams_dlg_identify_items
138 where item_id = p_item_id;
139
140 cursor c_chk_identify_item_exists is
141 select 'x'
142 from ams_dlg_identify_items
143 where item_id = p_item_id;
144
145 BEGIN
146
147 if p_OWNER = 'SEED' then
148 l_user_id := 1;
149 end if;
150
151 l_obj_verno := 1;
152 l_item_id := p_item_id;
153
154 open c_chk_identify_item_exists;
155 fetch c_chk_identify_item_exists into l_dummy_char;
156
157 if (c_chk_identify_item_exists%notfound ) then
158
159 AMS_DLG_IDENTIFY_ITEMS_PKG.Insert_Row(
160 px_item_id => l_item_id,
161 p_list_source_type_id => p_list_source_type_id,
162 p_list_source_field_id => p_list_source_field_id,
163 p_source_type_code => p_source_type_code,
164 p_source_column_name => p_source_column_name,
165 p_creation_date => sysdate,
166 p_created_by => l_user_id,
167 p_last_update_date => sysdate,
168 p_last_updated_by => l_user_id,
169 p_last_update_login => l_user_id,
170 px_object_version_number => l_obj_verno,
171 p_rule_id => p_rule_id
172 );
173
174 else
175 open c_obj_verno;
176 fetch c_obj_verno into l_obj_verno;
177 close c_obj_verno;
178
179 AMS_DLG_IDENTIFY_ITEMS_PKG.Update_Row(
180 p_item_id => p_item_id,
181 p_list_source_type_id => p_list_source_type_id,
182 p_list_source_field_id => p_list_source_field_id,
183 p_source_type_code => p_source_type_code,
184 p_source_column_name => p_source_column_name,
185 p_last_update_date => sysdate,
186 p_last_updated_by => l_user_id,
187 p_last_update_login => l_user_id,
188 p_object_version_number => l_obj_verno,
189 p_rule_id => p_rule_id
190 );
191 end if;
192 close c_chk_identify_item_exists;
193
194 END load_row;
195
196 -- ========================================================
197
198 END AMS_DLG_IDENTIFY_ITEMS_PKG;