[Home] [Help]
PACKAGE BODY: APPS.PA_STATUS_LISTS_PKG
Source
1 package body PA_STATUS_LISTS_PKG as
2 /* $Header: PACISLTB.pls 120.0 2005/05/30 17:22:42 appldev noship $ */
3 procedure INSERT_ROW (
4 X_RECORD_VERSION_NUMBER in NUMBER,
5 X_STATUS_LIST_ID in NUMBER,
6 X_STATUS_TYPE in VARCHAR2,
7 X_NAME in VARCHAR2,
8 X_START_DATE_ACTIVE in DATE,
9 X_END_DATE_ACTIVE in DATE,
10 X_DESCRIPTION in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17 cursor C is select ROWID from PA_STATUS_LISTS
18 where STATUS_LIST_ID = X_STATUS_LIST_ID
19 ;
20 l_rowid rowid;
21 begin
22 insert into PA_STATUS_LISTS (
23 LAST_UPDATE_DATE,
24 LAST_UPDATED_BY,
25 LAST_UPDATE_LOGIN,
26 CREATED_BY,
27 STATUS_LIST_ID,
28 STATUS_TYPE,
29 NAME,
30 RECORD_VERSION_NUMBER,
31 START_DATE_ACTIVE,
32 END_DATE_ACTIVE,
33 DESCRIPTION,
34 CREATION_DATE
35 ) values (
36 sysdate,
37 fnd_global.user_id,
38 fnd_global.user_id,
39 fnd_global.user_id,
40 X_STATUS_LIST_ID,
41 X_STATUS_TYPE,
42 X_NAME,
43 X_RECORD_VERSION_NUMBER,
44 X_START_DATE_ACTIVE,
45 X_END_DATE_ACTIVE,
46 X_DESCRIPTION,
47 sysdate);
48
49 open c;
50 fetch c into l_rowid;
51 if (c%notfound) then
52 close c;
53 raise no_data_found;
54 end if;
55 close c;
56
57 end INSERT_ROW;
58
59 PROCEDURE LOCK_ROW (
60 X_STATUS_LIST_ID in NUMBER,
61 X_RECORD_VERSION_NUMBER in NUMBER
62 ) IS
63 Resource_Busy EXCEPTION;
64 Invalid_Rec_Change EXCEPTION;
65 PRAGMA exception_init(Resource_Busy,-00054);
66 l_rec_ver_no NUMBER;
67 g_module_name VARCHAR2(100) := 'pa.plsql.PA_STATUS_LISTS';
68 l_debug_mode VARCHAR2(1);
69
70 l_debug_level2 CONSTANT NUMBER := 2;
71 l_debug_level3 CONSTANT NUMBER := 3;
72 l_debug_level4 CONSTANT NUMBER := 4;
73 l_debug_level5 CONSTANT NUMBER := 5;
74 BEGIN
75
76 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
77
78 IF l_debug_mode = 'Y' THEN
79 pa_debug.set_curr_function( p_function => 'STATUS_LISTS',
80 p_debug_mode => l_debug_mode );
81 END IF;
82
83
84 IF l_debug_mode = 'Y' THEN
85 pa_debug.g_err_stage:= 'Validating input parameters';
86 pa_debug.write(g_module_name,pa_debug.g_err_stage,
87 l_debug_level3);
88 END IF;
89
90 IF l_debug_mode = 'Y' THEN
91 pa_debug.g_err_stage:= 'X_STATUS_LIST_ID = '|| X_STATUS_LIST_ID;
92 pa_debug.write(g_module_name,pa_debug.g_err_stage,
93 l_debug_level5);
94 pa_debug.g_err_stage:= 'X_RECORD_VERSION_NUMBER = '|| X_RECORD_VERSION_NUMBER;
95 pa_debug.write(g_module_name,pa_debug.g_err_stage,
96 l_debug_level5);
97
98 END IF;
99 IF l_debug_mode = 'Y' THEN
100 pa_debug.g_err_stage:= 'in lock row method,ABOUT TO EXECUTE QUERY';
101 pa_debug.write(g_module_name,pa_debug.g_err_stage,
102 l_debug_level3);
103 pa_debug.reset_curr_function;
104 END IF;
105 select record_version_number into l_rec_ver_no
106 from pa_status_lists
107 where status_list_id = X_STATUS_LIST_ID
108 for update nowait;
109 if(X_RECORD_VERSION_NUMBER <> l_rec_ver_no) then
110 raise Invalid_Rec_Change;
111 end if;
112 IF l_debug_mode = 'Y' THEN
113 pa_debug.g_err_stage:= 'in lock row method,query executed';
114 pa_debug.write(g_module_name,pa_debug.g_err_stage,
115 l_debug_level3);
116 pa_debug.reset_curr_function;
117 END IF;
118 EXCEPTION
119 when NO_DATA_FOUND then
120 PA_UTILS.ADD_MESSAGE
121 (p_app_short_name => 'FND',
122 p_msg_name => 'FND_RECORD_DELETED_ERROR');
123 rollback to sp;
124 when Invalid_Rec_Change then
125 PA_UTILS.ADD_MESSAGE
126 (p_app_short_name => 'FND',
127 p_msg_name => 'FND_RECORD_CHANGED_ERROR');
128 rollback to sp;
129 when Resource_Busy then
130 PA_UTILS.ADD_MESSAGE
131 (p_app_short_name => 'FND',
132 p_msg_name => 'FND_LOCK_RECORD_ERROR');
133 rollback to sp;
134 END LOCK_ROW;
135
136 procedure UPDATE_ROW (
137 X_STATUS_LIST_ID in NUMBER,
138 X_RECORD_VERSION_NUMBER in NUMBER,
139 X_STATUS_TYPE in VARCHAR2,
140 X_NAME in VARCHAR2,
141 X_START_DATE_ACTIVE in DATE,
142 X_END_DATE_ACTIVE in DATE,
143 X_DESCRIPTION in VARCHAR2,
144 X_LAST_UPDATE_DATE in DATE,
145 X_LAST_UPDATED_BY in NUMBER,
146 X_LAST_UPDATE_LOGIN in NUMBER
147 ) is
148 begin
149 update PA_STATUS_LISTS set
150 STATUS_TYPE = X_STATUS_TYPE,
151 NAME = X_NAME,
152 RECORD_VERSION_NUMBER = X_RECORD_VERSION_NUMBER+1,
153 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
154 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
155 DESCRIPTION = X_DESCRIPTION,
156 STATUS_LIST_ID = X_STATUS_LIST_ID,
157 LAST_UPDATE_DATE = sysdate,
158 LAST_UPDATED_BY = fnd_global.user_id,
159 LAST_UPDATE_LOGIN = fnd_global.user_id
160 where STATUS_LIST_ID = X_STATUS_LIST_ID;
161
162 if (sql%notfound) then
163 raise no_data_found;
164 end if;
165 end UPDATE_ROW;
166
167 procedure DELETE_ROW (
168 X_STATUS_LIST_ID in NUMBER,
169 X_RECORD_VERSION_NUMBER in NUMBER
170 ) is
171 begin
172 delete from PA_STATUS_LISTS
173 where STATUS_LIST_ID = X_STATUS_LIST_ID;
174
175 if (sql%notfound) then
176 raise no_data_found;
177 end if;
178
179 end DELETE_ROW;
180
181
182 end PA_STATUS_LISTS_PKG;