[Home] [Help]
PACKAGE BODY: APPS.PSA_MFAR_ADJUSTMENT_COVER_PKG
Source
1 PACKAGE BODY PSA_MFAR_ADJUSTMENT_COVER_PKG AS
2 /* $Header: PSAMFAHB.pls 115.2 2000/10/09 18:44:27 pkm ship $ */
3
4 PROCEDURE INSERT_ROW ( X_ROWID IN OUT VARCHAR2,
5 X_ADJUSTMENT_ID IN NUMBER,
6 X_CUST_TRX_LINE_GL_DIST_ID IN NUMBER,
7 X_MF_ADJUSTMENT_CCID IN NUMBER,
8 X_AMOUNT IN NUMBER,
9 X_PERCENT IN NUMBER,
10 X_PREV_CUST_TRX_LINE_ID IN NUMBER,
11 X_COMMENTS IN VARCHAR2,
12 X_MODE IN VARCHAR2 DEFAULT 'R' ) IS
13
14 Cursor C Is Select ROWID
15 From PSA_MF_ADJ_DIST_ALL
16 Where ADJUSTMENT_ID = X_ADJUSTMENT_ID
17 And CUST_TRX_LINE_GL_DIST_ID = X_CUST_TRX_LINE_GL_DIST_ID;
18
19 X_LAST_UPDATE_DATE DATE;
20 X_LAST_UPDATED_BY NUMBER;
21 X_LAST_UPDATE_LOGIN NUMBER;
22
23 BEGIN
24
25 X_LAST_UPDATE_DATE := SYSDATE;
26
27 if(X_MODE = 'I') then
28 X_LAST_UPDATED_BY := 1;
29 X_LAST_UPDATE_LOGIN := 0;
30 elsif (X_MODE = 'R') then
31 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
32 if X_LAST_UPDATED_BY is NULL then
33 X_LAST_UPDATED_BY := -1;
34 end if;
35 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
36 if X_LAST_UPDATE_LOGIN is NULL then
37 X_LAST_UPDATE_LOGIN := -1;
38 end if;
39 else
40 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
41 app_exception.raise_exception;
42 end if;
43
44 Insert into PSA_MF_ADJ_DIST_ALL( ADJUSTMENT_ID,
45 CUST_TRX_LINE_GL_DIST_ID,
46 MF_ADJUSTMENT_CCID,
47 AMOUNT,
48 PERCENT,
49 PREV_CUST_TRX_LINE_ID,
50 COMMENTS,
51 CREATION_DATE,
52 CREATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATED_BY,
55 LAST_UPDATE_LOGIN ) Values
56 ( X_ADJUSTMENT_ID,
57 X_CUST_TRX_LINE_GL_DIST_ID,
58 X_MF_ADJUSTMENT_CCID,
59 X_AMOUNT,
60 X_PERCENT,
61 X_PREV_CUST_TRX_LINE_ID,
62 X_COMMENTS,
63 X_LAST_UPDATE_DATE,
64 X_LAST_UPDATED_BY,
65 X_LAST_UPDATE_DATE,
66 X_LAST_UPDATED_BY,
67 X_LAST_UPDATE_LOGIN );
68
69 open c;
70 fetch c into X_ROWID;
71 if (c%notfound) then
72 close c;
73 raise no_data_found;
74 end if;
75 close c;
76
77 END INSERT_ROW;
78
79 PROCEDURE LOCK_ROW( X_ADJUSTMENT_ID IN NUMBER,
80 X_CUST_TRX_LINE_GL_DIST_ID IN NUMBER,
81 X_MF_ADJUSTMENT_CCID IN NUMBER,
82 X_AMOUNT IN NUMBER,
83 X_PERCENT IN NUMBER,
84 X_PREV_CUST_TRX_LINE_ID IN NUMBER,
85 X_COMMENTS IN VARCHAR2 ) IS
86
87 CURSOR c1 IS Select MF_ADJUSTMENT_CCID,
88 AMOUNT,
89 PERCENT,
90 PREV_CUST_TRX_LINE_ID,
91 COMMENTS
92 From PSA_MF_ADJ_DIST_ALL
93 Where ADJUSTMENT_ID = X_ADJUSTMENT_ID
94 And CUST_TRX_LINE_GL_DIST_ID = X_CUST_TRX_LINE_GL_DIST_ID
95 FOR UPDATE OF ADJUSTMENT_ID NOWAIT;
96
97 tlinfo c1%rowtype;
98
99 BEGIN
100 open c1;
101 fetch c1 into tlinfo;
102 if (c1%notfound) then
103 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
104 app_exception.raise_exception;
105 close c1;
106 return;
107 end if;
108 close c1;
109
110 if ( (tlinfo.MF_ADJUSTMENT_CCID = X_MF_ADJUSTMENT_CCID)
111 AND (tlinfo.AMOUNT = X_AMOUNT)
112 AND (tlinfo.PERCENT = X_PERCENT)
113 AND ((tlinfo.PREV_CUST_TRX_LINE_ID = X_PREV_CUST_TRX_LINE_ID)
114 OR ((tlinfo.PREV_CUST_TRX_LINE_ID is null)
115 AND (X_PREV_CUST_TRX_LINE_ID is null)))
116 AND ((tlinfo.COMMENTS = X_COMMENTS)
117 OR ((tlinfo.COMMENTS is null)
118 AND (X_COMMENTS is null)))
119 ) then
120 null;
121 else
122 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
123 app_exception.raise_exception;
124 end if;
125 return;
126 END LOCK_ROW;
127
128 PROCEDURE UPDATE_ROW ( X_ADJUSTMENT_ID IN NUMBER,
129 X_CUST_TRX_LINE_GL_DIST_ID IN NUMBER,
130 X_MF_ADJUSTMENT_CCID IN NUMBER,
131 X_AMOUNT IN NUMBER,
132 X_PERCENT IN NUMBER,
133 X_PREV_CUST_TRX_LINE_ID IN NUMBER,
134 X_COMMENTS IN VARCHAR2,
135 X_MODE IN VARCHAR2 DEFAULT 'R' ) is
136
137 X_LAST_UPDATE_DATE DATE;
138 X_LAST_UPDATED_BY NUMBER;
139 X_LAST_UPDATE_LOGIN NUMBER;
140
141 BEGIN
142 X_LAST_UPDATE_DATE := SYSDATE;
143 if(X_MODE = 'I') then
144 X_LAST_UPDATED_BY := 1;
145 X_LAST_UPDATE_LOGIN := 0;
146 elsif (X_MODE = 'R') then
147 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
148 if X_LAST_UPDATED_BY is NULL then
149 X_LAST_UPDATED_BY := -1;
150 end if;
151 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
152 if X_LAST_UPDATE_LOGIN is NULL then
153 X_LAST_UPDATE_LOGIN := -1;
154 end if;
155 else
156 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
157 app_exception.raise_exception;
158 end if;
159
160 UPDATE PSA_MF_ADJ_DIST_ALL set
161 MF_ADJUSTMENT_CCID = X_MF_ADJUSTMENT_CCID,
162 AMOUNT = X_AMOUNT,
163 PERCENT = X_PERCENT,
164 PREV_CUST_TRX_LINE_ID = X_PREV_CUST_TRX_LINE_ID,
165 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
166 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
167 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
168 COMMENTS = X_COMMENTS
169 WHERE ADJUSTMENT_ID = X_ADJUSTMENT_ID
170 and CUST_TRX_LINE_GL_DIST_ID = X_CUST_TRX_LINE_GL_DIST_ID;
171
172 if (sql%notfound) then
173 raise no_data_found;
174 end if;
175 END UPDATE_ROW;
176
177 PROCEDURE ADD_ROW ( X_ROWID IN OUT VARCHAR2,
178 X_ADJUSTMENT_ID IN NUMBER,
179 X_CUST_TRX_LINE_GL_DIST_ID IN NUMBER,
180 X_MF_ADJUSTMENT_CCID IN NUMBER,
181 X_AMOUNT IN NUMBER,
182 X_PERCENT IN NUMBER,
183 X_PREV_CUST_TRX_LINE_ID IN NUMBER,
184 X_COMMENTS IN VARCHAR2,
185 X_MODE IN VARCHAR2 default 'R') IS
186
187 cursor c1 is select rowid
188 from PSA_MF_ADJ_DIST_ALL
189 where ADJUSTMENT_ID = X_ADJUSTMENT_ID
190 and CUST_TRX_LINE_GL_DIST_ID = X_CUST_TRX_LINE_GL_DIST_ID;
191
192 dummy c1%rowtype;
193 begin
194 open c1;
195 fetch c1 into dummy;
196 if (c1%notfound) then
197 close c1;
198 INSERT_ROW ( X_ROWID,
199 X_ADJUSTMENT_ID,
200 X_CUST_TRX_LINE_GL_DIST_ID,
201 X_MF_ADJUSTMENT_CCID,
202 X_AMOUNT,
203 X_PERCENT,
204 X_PREV_CUST_TRX_LINE_ID,
205 X_COMMENTS,
206 X_MODE );
207 return;
208 end if;
209 close c1;
210
211 UPDATE_ROW ( X_ADJUSTMENT_ID,
212 X_CUST_TRX_LINE_GL_DIST_ID,
213 X_MF_ADJUSTMENT_CCID,
214 X_AMOUNT,
215 X_PERCENT,
216 X_PREV_CUST_TRX_LINE_ID,
217 X_COMMENTS,
218 X_MODE );
219 END ADD_ROW;
220
221 PROCEDURE DELETE_ROW ( X_ADJUSTMENT_ID IN NUMBER,
222 X_CUST_TRX_LINE_GL_DIST_ID IN NUMBER ) IS
223 BEGIN
224
225 DELETE FROM PSA_MF_ADJ_DIST_ALL
226 WHERE ADJUSTMENT_ID = X_ADJUSTMENT_ID
227 AND CUST_TRX_LINE_GL_DIST_ID = X_CUST_TRX_LINE_GL_DIST_ID;
228
229 if (sql%notfound) then
230 raise no_data_found;
231 end if;
232 END DELETE_ROW;
233
234 END PSA_MFAR_ADJUSTMENT_COVER_PKG;