[Home] [Help]
PACKAGE BODY: APPS.AMS_STATUS_ORDER_RULES_PKG
Source
1 package body AMS_STATUS_ORDER_RULES_PKG as
2 /* $Header: amslstsb.pls 115.4 2002/11/16 01:44:37 dbiswas ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_STATUS_ORDER_RULE_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_SYSTEM_STATUS_TYPE in VARCHAR2,
8 X_CURRENT_STATUS_CODE in VARCHAR2,
9 X_NEXT_STATUS_CODE in VARCHAR2,
10 X_SHOW_IN_LOV_FLAG in VARCHAR2,
11 X_THEME_APPROVAL_FLAG in VARCHAR2,
12 X_BUDGET_APPROVAL_FLAG in VARCHAR2,
13 X_CREATION_DATE in DATE,
14 X_CREATED_BY in NUMBER,
15 X_LAST_UPDATE_DATE in DATE,
16 X_LAST_UPDATED_BY in NUMBER,
17 X_LAST_UPDATE_LOGIN in NUMBER,
18 X_APPLICATION_ID in NUMBER DEFAULT '530'
19 ) is
20 cursor C is select ROWID from AMS_STATUS_ORDER_RULES
21 where STATUS_ORDER_RULE_ID = X_STATUS_ORDER_RULE_ID ;
22 begin
23 insert into AMS_STATUS_ORDER_RULES (
24 STATUS_ORDER_RULE_ID,
25 LAST_UPDATE_DATE,
26 LAST_UPDATED_BY,
27 CREATION_DATE,
28 CREATED_BY,
29 LAST_UPDATE_LOGIN,
30 OBJECT_VERSION_NUMBER,
31 SYSTEM_STATUS_TYPE,
32 CURRENT_STATUS_CODE,
33 NEXT_STATUS_CODE,
34 SHOW_IN_LOV_FLAG,
35 THEME_APPROVAL_FLAG,
36 BUDGET_APPROVAL_FLAG,
37 APPLICATION_ID
38 ) values
39 ( X_STATUS_ORDER_RULE_ID,
40 X_LAST_UPDATE_DATE,
41 X_LAST_UPDATED_BY,
42 X_CREATION_DATE,
43 X_CREATED_BY,
44 X_LAST_UPDATE_LOGIN,
45 X_OBJECT_VERSION_NUMBER,
46 X_SYSTEM_STATUS_TYPE,
47 X_CURRENT_STATUS_CODE,
48 X_NEXT_STATUS_CODE,
49 X_SHOW_IN_LOV_FLAG,
50 X_THEME_APPROVAL_FLAG,
51 X_BUDGET_APPROVAL_FLAG,
52 X_APPLICATION_ID
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
63 end INSERT_ROW;
64
65 procedure UPDATE_ROW (
66 X_STATUS_ORDER_RULE_ID in NUMBER,
67 X_OBJECT_VERSION_NUMBER in NUMBER,
68 X_SYSTEM_STATUS_TYPE in VARCHAR2,
69 X_CURRENT_STATUS_CODE in VARCHAR2,
70 X_NEXT_STATUS_CODE in VARCHAR2,
71 X_SHOW_IN_LOV_FLAG in VARCHAR2,
72 X_THEME_APPROVAL_FLAG in VARCHAR2,
73 X_BUDGET_APPROVAL_FLAG in VARCHAR2,
74 X_LAST_UPDATE_DATE in DATE,
75 X_LAST_UPDATED_BY in NUMBER,
76 X_LAST_UPDATE_LOGIN in NUMBER,
77 X_APPLICATION_ID in NUMBER DEFAULT '530'
78 ) is
79 begin
80 update AMS_STATUS_ORDER_RULES set
81 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
82 SYSTEM_STATUS_TYPE = X_SYSTEM_STATUS_TYPE,
83 CURRENT_STATUS_CODE = X_CURRENT_STATUS_CODE,
84 NEXT_STATUS_CODE = X_NEXT_STATUS_CODE,
85 SHOW_IN_LOV_FLAG = X_SHOW_IN_LOV_FLAG,
86 THEME_APPROVAL_FLAG = X_THEME_APPROVAL_FLAG,
87 BUDGET_APPROVAL_FLAG = X_BUDGET_APPROVAL_FLAG,
88 STATUS_ORDER_RULE_ID = X_STATUS_ORDER_RULE_ID,
89 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
90 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
91 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
92 APPLICATION_ID = X_APPLICATION_ID
93 where STATUS_ORDER_RULE_ID = X_STATUS_ORDER_RULE_ID;
94
95 if (sql%notfound) then
96 raise no_data_found;
97 end if;
98 end UPDATE_ROW;
99
100 procedure DELETE_ROW (
101 X_STATUS_ORDER_RULE_ID in NUMBER
102 ) is
103 begin
104 delete from AMS_STATUS_ORDER_RULES
105 where STATUS_ORDER_RULE_ID = X_STATUS_ORDER_RULE_ID;
106
107 if (sql%notfound) then
108 raise no_data_found;
109 end if;
110
111 end DELETE_ROW;
112
113 procedure LOAD_ROW(
114 X_STATUS_ORDER_RULE_ID in NUMBER,
115 X_SYSTEM_STATUS_TYPE in VARCHAR2,
116 X_CURRENT_STATUS_CODE in VARCHAR2,
117 X_NEXT_STATUS_CODE in VARCHAR2,
118 X_SHOW_IN_LOV_FLAG in VARCHAR2,
119 X_THEME_APPROVAL_FLAG in VARCHAR2,
120 X_BUDGET_APPROVAL_FLAG in VARCHAR2,
121 X_Owner VARCHAR2,
122 X_APPLICATION_ID in NUMBER DEFAULT '530'
123 ) is
124 l_user_id number := 0;
125 l_obj_verno number;
126 l_dummy_char varchar2(1);
127 l_row_id varchar2(100);
128 l_sts_id number;
129
130 cursor c_obj_verno is
131 select object_version_number
132 from AMS_STATUS_ORDER_RULES
133 where status_order_rule_id = X_STATUS_ORDER_RULE_ID;
134
135 cursor c_chk_sts_exists is
136 select 'x'
137 from AMS_STATUS_ORDER_RULES
138 where status_order_rule_id = X_STATUS_ORDER_RULE_ID;
139
140 cursor c_get_stsid is
141 select ams_status_order_rules_s.nextval
142 from dual;
143
144 BEGIN
145
146 if X_OWNER = 'SEED' then
147 l_user_id := 1;
148 end if;
149
150 open c_chk_sts_exists;
151 fetch c_chk_sts_exists into l_dummy_char;
152 if c_chk_sts_exists%notfound
153 then
154 close c_chk_sts_exists;
155
156 if X_STATUS_ORDER_RULE_ID is null then
157 open c_get_stsid;
158 fetch c_get_stsid into l_sts_id;
159 close c_get_stsid;
160 else
161 l_sts_id := X_STATUS_ORDER_RULE_ID ;
162 end if ;
163
164 l_obj_verno := 1;
165
166 AMS_STATUS_ORDER_RULES_PKG.INSERT_ROW (
167 X_ROWID => l_row_id ,
168 X_STATUS_ORDER_RULE_ID => l_sts_id,
169 X_OBJECT_VERSION_NUMBER => l_obj_verno,
170 X_SYSTEM_STATUS_TYPE => X_SYSTEM_STATUS_TYPE,
171 X_CURRENT_STATUS_CODE => X_CURRENT_STATUS_CODE,
172 X_NEXT_STATUS_CODE => X_NEXT_STATUS_CODE,
173 X_SHOW_IN_LOV_FLAG => X_SHOW_IN_LOV_FLAG,
174 X_THEME_APPROVAL_FLAG => X_THEME_APPROVAL_FLAG,
175 X_BUDGET_APPROVAL_FLAG => X_BUDGET_APPROVAL_FLAG,
176 X_CREATION_DATE => SYSDATE,
177 X_CREATED_BY => l_user_id,
178 X_LAST_UPDATE_DATE => SYSDATE,
179 X_LAST_UPDATED_BY => l_user_id,
180 X_LAST_UPDATE_LOGIN => 0,
181 X_APPLICATION_ID => X_APPLICATION_ID
182 ) ;
183
184 else
185 close c_chk_sts_exists;
186 open c_obj_verno;
187 fetch c_obj_verno into l_obj_verno;
188 close c_obj_verno;
189
190 AMS_STATUS_ORDER_RULES_PKG.UPDATE_ROW(
191 X_STATUS_ORDER_RULE_ID => X_STATUS_ORDER_RULE_ID,
192 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1,
193 X_SYSTEM_STATUS_TYPE => X_SYSTEM_STATUS_TYPE,
194 X_CURRENT_STATUS_CODE => X_CURRENT_STATUS_CODE,
195 X_NEXT_STATUS_CODE => X_NEXT_STATUS_CODE,
196 X_SHOW_IN_LOV_FLAG => X_SHOW_IN_LOV_FLAG,
197 X_THEME_APPROVAL_FLAG => X_THEME_APPROVAL_FLAG,
198 X_BUDGET_APPROVAL_FLAG => X_BUDGET_APPROVAL_FLAG,
199 X_LAST_UPDATE_DATE => SYSDATE,
200 X_LAST_UPDATED_BY => l_user_id,
201 X_LAST_UPDATE_LOGIN => 0,
202 X_APPLICATION_ID => X_APPLICATION_ID
203 );
204 end if;
205
206 END LOAD_ROW ;
207
208 end AMS_STATUS_ORDER_RULES_PKG;