[Home] [Help]
PACKAGE BODY: APPS.AHL_STATUS_ORDER_RULES_PKG
Source
1 package body AHL_STATUS_ORDER_RULES_PKG as
2 /* $Header: AHLLSTSB.pls 115.1 2002/12/04 01:31:42 ssurapan noship $ */
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_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16 cursor C is select ROWID from AHL_STATUS_ORDER_RULES
17 where STATUS_ORDER_RULE_ID = X_STATUS_ORDER_RULE_ID ;
18 begin
19 insert into AHL_STATUS_ORDER_RULES (
20 STATUS_ORDER_RULE_ID,
21 LAST_UPDATE_DATE,
22 LAST_UPDATED_BY,
23 CREATION_DATE,
24 CREATED_BY,
25 LAST_UPDATE_LOGIN,
26 OBJECT_VERSION_NUMBER,
27 SYSTEM_STATUS_TYPE,
28 CURRENT_STATUS_CODE,
29 NEXT_STATUS_CODE
30 ) values
31 ( X_STATUS_ORDER_RULE_ID,
32 X_LAST_UPDATE_DATE,
33 X_LAST_UPDATED_BY,
34 X_CREATION_DATE,
35 X_CREATED_BY,
36 X_LAST_UPDATE_LOGIN,
37 X_OBJECT_VERSION_NUMBER,
38 X_SYSTEM_STATUS_TYPE,
39 X_CURRENT_STATUS_CODE,
40 X_NEXT_STATUS_CODE
41 );
42
43 open c;
44 fetch c into X_ROWID;
45 if (c%notfound) then
46 close c;
47 raise no_data_found;
48 end if;
49 close c;
50
51 end INSERT_ROW;
52
53 procedure UPDATE_ROW (
54 X_STATUS_ORDER_RULE_ID in NUMBER,
55 X_OBJECT_VERSION_NUMBER in NUMBER,
56 X_SYSTEM_STATUS_TYPE in VARCHAR2,
57 X_CURRENT_STATUS_CODE in VARCHAR2,
58 X_NEXT_STATUS_CODE in VARCHAR2,
59 X_LAST_UPDATE_DATE in DATE,
60 X_LAST_UPDATED_BY in NUMBER,
61 X_LAST_UPDATE_LOGIN in NUMBER
62 ) is
63 begin
64 update AHL_STATUS_ORDER_RULES set
65 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
66 SYSTEM_STATUS_TYPE = X_SYSTEM_STATUS_TYPE,
67 CURRENT_STATUS_CODE = X_CURRENT_STATUS_CODE,
68 NEXT_STATUS_CODE = X_NEXT_STATUS_CODE,
69 STATUS_ORDER_RULE_ID = X_STATUS_ORDER_RULE_ID,
70 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
71 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
72 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
73 where STATUS_ORDER_RULE_ID = X_STATUS_ORDER_RULE_ID;
74
75 if (sql%notfound) then
76 raise no_data_found;
77 end if;
78 end UPDATE_ROW;
79
80 procedure DELETE_ROW (
81 X_STATUS_ORDER_RULE_ID in NUMBER
82 ) is
83 begin
84 delete from AHL_STATUS_ORDER_RULES
85 where STATUS_ORDER_RULE_ID = X_STATUS_ORDER_RULE_ID;
86
87 if (sql%notfound) then
88 raise no_data_found;
89 end if;
90
91 end DELETE_ROW;
92
93 procedure LOAD_ROW(
94 X_STATUS_ORDER_RULE_ID in NUMBER,
95 X_SYSTEM_STATUS_TYPE in VARCHAR2,
96 X_CURRENT_STATUS_CODE in VARCHAR2,
97 X_NEXT_STATUS_CODE in VARCHAR2,
98 X_Owner VARCHAR2
99 ) is
100 l_user_id number := 0;
101 l_obj_verno number;
102 l_dummy_char varchar2(1);
103 l_row_id varchar2(100);
104 l_sts_id number;
105
106 cursor c_obj_verno is
107 select object_version_number
108 from AHL_STATUS_ORDER_RULES
109 where status_order_rule_id = X_STATUS_ORDER_RULE_ID;
110
111 cursor c_chk_sts_exists is
112 select 'x'
113 from AHL_STATUS_ORDER_RULES
114 where status_order_rule_id = X_STATUS_ORDER_RULE_ID;
115
116 cursor c_get_stsid is
117 select ahl_status_order_rules_s.nextval
118 from dual;
119
120 BEGIN
121
122 if X_OWNER = 'SEED' then
123 l_user_id := 1;
124 end if;
125
126 open c_chk_sts_exists;
127 fetch c_chk_sts_exists into l_dummy_char;
128 if c_chk_sts_exists%notfound
129 then
130 close c_chk_sts_exists;
131
132 if X_STATUS_ORDER_RULE_ID is null then
133 open c_get_stsid;
134 fetch c_get_stsid into l_sts_id;
135 close c_get_stsid;
136 else
137 l_sts_id := X_STATUS_ORDER_RULE_ID ;
138 end if ;
139
140 l_obj_verno := 1;
141
142 AHL_STATUS_ORDER_RULES_PKG.INSERT_ROW (
143 X_ROWID => l_row_id ,
144 X_STATUS_ORDER_RULE_ID => l_sts_id,
145 X_OBJECT_VERSION_NUMBER => l_obj_verno,
146 X_SYSTEM_STATUS_TYPE => X_SYSTEM_STATUS_TYPE,
147 X_CURRENT_STATUS_CODE => X_CURRENT_STATUS_CODE,
148 X_NEXT_STATUS_CODE => X_NEXT_STATUS_CODE,
149 X_CREATION_DATE => SYSDATE,
150 X_CREATED_BY => l_user_id,
151 X_LAST_UPDATE_DATE => SYSDATE,
152 X_LAST_UPDATED_BY => l_user_id,
153 X_LAST_UPDATE_LOGIN => 0
154 ) ;
155
156 else
157 close c_chk_sts_exists;
158 open c_obj_verno;
159 fetch c_obj_verno into l_obj_verno;
160 close c_obj_verno;
161
162 AHL_STATUS_ORDER_RULES_PKG.UPDATE_ROW(
163 X_STATUS_ORDER_RULE_ID => X_STATUS_ORDER_RULE_ID,
164 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1,
165 X_SYSTEM_STATUS_TYPE => X_SYSTEM_STATUS_TYPE,
166 X_CURRENT_STATUS_CODE => X_CURRENT_STATUS_CODE,
167 X_NEXT_STATUS_CODE => X_NEXT_STATUS_CODE,
168 X_LAST_UPDATE_DATE => SYSDATE,
169 X_LAST_UPDATED_BY => l_user_id,
170 X_LAST_UPDATE_LOGIN => 0
171 );
172 end if;
173
174 END LOAD_ROW ;
175
176 end AHL_STATUS_ORDER_RULES_PKG;