[Home] [Help]
PACKAGE BODY: APPS.PSP_AUTO_SEGMENTS_PKG
Source
1 package body PSP_AUTO_SEGMENTS_PKG as
2 --$Header: PSPAUSGB.pls 115.9 2002/11/19 10:55:50 ddubey psp2376993.sql $
3 /* $HEADER$ */
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_SET_OF_BOOKS_ID in NUMBER,
7 X_BUSINESS_GROUP_ID in NUMBER,
8 X_SEGMENT_NUMBER in NUMBER,
9 X_MODE in VARCHAR2 default 'R'
10 ) is
11 cursor C is select ROWID from PSP_AUTO_SEGMENTS
12 where SET_OF_BOOKS_ID = X_SET_OF_BOOKS_ID and
13 BUSINESS_GROUP_ID =X_BUSINESS_GROUP_ID;
14 X_LAST_UPDATE_DATE DATE;
15 X_LAST_UPDATED_BY NUMBER;
16 X_LAST_UPDATE_LOGIN NUMBER;
17 begin
18 X_LAST_UPDATE_DATE := SYSDATE;
19 if(X_MODE = 'I') then
20 X_LAST_UPDATED_BY := 1;
21 X_LAST_UPDATE_LOGIN := 0;
22 elsif (X_MODE = 'R') then
23 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
24 if X_LAST_UPDATED_BY is NULL then
25 X_LAST_UPDATED_BY := -1;
26 end if;
27 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
28 if X_LAST_UPDATE_LOGIN is NULL then
29 X_LAST_UPDATE_LOGIN := -1;
30 end if;
31 else
32 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
33 app_exception.raise_exception;
34 end if;
35 insert into PSP_AUTO_SEGMENTS (
36 SET_OF_BOOKS_ID,
37 BUSINESS_GROUP_ID ,
38 SEGMENT_NUMBER,
39 CREATION_DATE,
40 CREATED_BY,
41 LAST_UPDATE_DATE,
42 LAST_UPDATED_BY,
43 LAST_UPDATE_LOGIN
44 ) values (
45 X_SET_OF_BOOKS_ID,
46 X_BUSINESS_GROUP_ID ,
47 X_SEGMENT_NUMBER,
48 X_LAST_UPDATE_DATE,
49 X_LAST_UPDATED_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
63 end INSERT_ROW;
64
65 procedure LOCK_ROW (
66 X_SET_OF_BOOKS_ID in NUMBER,
67 X_BUSINESS_GROUP_ID in NUMBER,
68 X_SEGMENT_NUMBER in NUMBER
69 ) is
70 cursor c1 is select
71 SEGMENT_NUMBER
72 from PSP_AUTO_SEGMENTS
73 where SET_OF_BOOKS_ID = X_SET_OF_BOOKS_ID
74 and BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID
75 for update of SET_OF_BOOKS_ID, BUSINESS_GROUP_ID nowait;
76 tlinfo c1%rowtype;
77
78 begin
79 open c1;
80 fetch c1 into tlinfo;
81 if (c1%notfound) then
82 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
83 app_exception.raise_exception;
84 close c1;
85 return;
86 end if;
87 close c1;
88
89 if ( ((tlinfo.SEGMENT_NUMBER = X_SEGMENT_NUMBER)
90 OR ((tlinfo.SEGMENT_NUMBER is null)
91 AND (X_SEGMENT_NUMBER is null)))
92 ) then
93 null;
94 else
95 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
96 app_exception.raise_exception;
97 end if;
98 return;
99 end LOCK_ROW;
100
101 procedure UPDATE_ROW (
102 X_SET_OF_BOOKS_ID in NUMBER,
103 X_BUSINESS_GROUP_ID in NUMBER,
104 X_SEGMENT_NUMBER in NUMBER,
105 X_MODE in VARCHAR2 default 'R'
106 ) is
107 X_LAST_UPDATE_DATE DATE;
108 X_LAST_UPDATED_BY NUMBER;
109 X_LAST_UPDATE_LOGIN NUMBER;
110 begin
111 X_LAST_UPDATE_DATE := SYSDATE;
112 if(X_MODE = 'I') then
113 X_LAST_UPDATED_BY := 1;
114 X_LAST_UPDATE_LOGIN := 0;
115 elsif (X_MODE = 'R') then
116 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
117 if X_LAST_UPDATED_BY is NULL then
118 X_LAST_UPDATED_BY := -1;
119 end if;
120 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
121 if X_LAST_UPDATE_LOGIN is NULL then
122 X_LAST_UPDATE_LOGIN := -1;
123 end if;
124 else
125 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
126 app_exception.raise_exception;
127 end if;
128 update PSP_AUTO_SEGMENTS set
129 SEGMENT_NUMBER = X_SEGMENT_NUMBER,
130 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
131 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
132 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
133 where SET_OF_BOOKS_ID = X_SET_OF_BOOKS_ID
134 ANd BUSINESS_GROUP_ID =X_BUSINESS_GROUP_ID
135 ;
136 if (sql%notfound) then
137 raise no_data_found;
138 end if;
139 end UPDATE_ROW;
140
141 procedure ADD_ROW (
142 X_ROWID in out NOCOPY VARCHAR2,
143 X_SET_OF_BOOKS_ID in NUMBER,
144 X_BUSINESS_GROUP_ID in NUMBER,
145 X_SEGMENT_NUMBER in NUMBER,
146 X_MODE in VARCHAR2 default 'R'
147 ) is
148 cursor c1 is select rowid from PSP_AUTO_SEGMENTS
149 where SET_OF_BOOKS_ID = X_SET_OF_BOOKS_ID
150 and BUSINESS_GROUP_ID=X_BUSINESS_GROUP_ID
151 ;
152 dummy c1%rowtype;
153 begin
154 open c1;
155 fetch c1 into dummy;
156 if (c1%notfound) then
157 close c1;
158 INSERT_ROW (
159 X_ROWID,
160 X_SET_OF_BOOKS_ID,
161 X_BUSINESS_GROUP_ID,
162 X_SEGMENT_NUMBER,
163 X_MODE);
164 return;
165 end if;
166 close c1;
167 UPDATE_ROW (
168 X_SET_OF_BOOKS_ID,
169 X_BUSINESS_GROUP_ID,
170 X_SEGMENT_NUMBER,
171 X_MODE);
172 end ADD_ROW;
173
174 procedure DELETE_ROW (
175 X_SET_OF_BOOKS_ID in NUMBER,
176 X_BUSINESS_GROUP_ID IN NUMBER
177 ) is
178 begin
179 delete from PSP_AUTO_SEGMENTS
180 where SET_OF_BOOKS_ID = X_SET_OF_BOOKS_ID
181 and BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID;
182 if (sql%notfound) then
183 raise no_data_found;
184 end if;
185 end DELETE_ROW;
186
187 end PSP_AUTO_SEGMENTS_PKG;