1 PACKAGE BODY LNS_DISB_HISTORIES_H_PKG AS
2 /* $Header: LNS_DSBHY_TBLH_B.pls 120.1 2005/07/01 08:31:41 scherkas noship $ */
3
4 /* Insert_Row procedure */
5 PROCEDURE Insert_Row(
6 X_DISB_HISTORY_ID IN OUT NOCOPY NUMBER
7 ,P_DISB_LINE_ID IN NUMBER
8 ,P_ATTEMPT_NUMBER IN NUMBER
9 ,P_BANK_ACCOUNT_ID IN NUMBER
10 ,P_PAYMENT_METHOD_CODE IN VARCHAR2
11 ,P_REQUEST_DATE IN DATE
12 ,P_PAYMENT_REQUEST_DATE IN DATE
13 ,P_STATUS IN VARCHAR2
14 ,P_MESSAGES IN VARCHAR2
15 ,P_OBJECT_VERSION_NUMBER IN NUMBER
16 ,P_CREATION_DATE IN DATE
17 ,P_CREATED_BY IN NUMBER
18 ,P_LAST_UPDATE_DATE IN DATE
19 ,P_LAST_UPDATED_BY IN NUMBER
20 ,P_LAST_UPDATE_LOGIN IN NUMBER
21 ) IS
22 BEGIN
23 INSERT INTO LNS_DISB_HISTORIES_H
24 (
25 DISB_HISTORY_ID
26 ,DISB_LINE_ID
27 ,ATTEMPT_NUMBER
28 ,BANK_ACCOUNT_ID
29 ,PAYMENT_METHOD_CODE
30 ,REQUEST_DATE
31 ,PAYMENT_REQUEST_DATE
32 ,STATUS
33 ,MESSAGES
34 ,OBJECT_VERSION_NUMBER
35 ,CREATION_DATE
36 ,CREATED_BY
37 ,LAST_UPDATE_DATE
38 ,LAST_UPDATED_BY
39 ,LAST_UPDATE_LOGIN
40 ) VALUES (
41 DECODE(X_DISB_HISTORY_ID, FND_API.G_MISS_NUM, LNS_DISB_HISTORIES_S.NEXTVAL, NULL, LNS_DISB_HISTORIES_S.NEXTVAL, X_DISB_HISTORY_ID)
42 ,DECODE(P_DISB_LINE_ID, FND_API.G_MISS_NUM, NULL, P_DISB_LINE_ID)
43 ,DECODE(P_ATTEMPT_NUMBER, FND_API.G_MISS_NUM, NULL, P_ATTEMPT_NUMBER)
44 ,DECODE(P_BANK_ACCOUNT_ID, FND_API.G_MISS_NUM, NULL, P_BANK_ACCOUNT_ID)
45 ,DECODE(P_PAYMENT_METHOD_CODE, FND_API.G_MISS_CHAR, NULL, P_PAYMENT_METHOD_CODE)
46 ,DECODE(P_REQUEST_DATE, FND_API.G_MISS_DATE, NULL, P_REQUEST_DATE)
47 ,DECODE(P_PAYMENT_REQUEST_DATE, FND_API.G_MISS_DATE, NULL, P_PAYMENT_REQUEST_DATE)
48 ,DECODE(P_STATUS, FND_API.G_MISS_CHAR, NULL, P_STATUS)
49 ,DECODE(P_MESSAGES, FND_API.G_MISS_CHAR, NULL, P_MESSAGES)
50 ,DECODE(P_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, P_OBJECT_VERSION_NUMBER)
51 ,LNS_UTILITY_PUB.CREATION_DATE
52 ,LNS_UTILITY_PUB.CREATED_BY
53 ,LNS_UTILITY_PUB.LAST_UPDATE_DATE
54 ,LNS_UTILITY_PUB.LAST_UPDATED_BY
55 ,LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
56 ) RETURNING
57 DISB_HISTORY_ID
58 INTO
59 X_DISB_HISTORY_ID;
60 END Insert_Row;
61
62 /* Update_Row procedure */
63 PROCEDURE Update_Row(
64 P_DISB_HISTORY_ID IN NUMBER
65 ,P_DISB_LINE_ID IN NUMBER
66 ,P_ATTEMPT_NUMBER IN NUMBER
67 ,P_BANK_ACCOUNT_ID IN NUMBER
68 ,P_PAYMENT_METHOD_CODE IN VARCHAR2
69 ,P_REQUEST_DATE IN DATE
70 ,P_PAYMENT_REQUEST_DATE IN DATE
71 ,P_STATUS IN VARCHAR2
72 ,P_MESSAGES IN VARCHAR2
73 ,P_OBJECT_VERSION_NUMBER IN NUMBER
74 ,P_LAST_UPDATE_DATE IN DATE
75 ,P_LAST_UPDATED_BY IN NUMBER
76 ,P_LAST_UPDATE_LOGIN IN NUMBER
77 ) IS
78 BEGIN
79 UPDATE LNS_DISB_HISTORIES_H SET
80 DISB_LINE_ID = DECODE(P_DISB_LINE_ID, NULL, DISB_LINE_ID, FND_API.G_MISS_NUM, NULL, P_DISB_LINE_ID)
81 ,ATTEMPT_NUMBER = DECODE(P_ATTEMPT_NUMBER, NULL, ATTEMPT_NUMBER, FND_API.G_MISS_NUM, NULL, P_ATTEMPT_NUMBER)
82 ,BANK_ACCOUNT_ID = DECODE(P_BANK_ACCOUNT_ID, NULL, BANK_ACCOUNT_ID, FND_API.G_MISS_NUM, NULL, P_BANK_ACCOUNT_ID)
83 ,PAYMENT_METHOD_CODE = DECODE(P_PAYMENT_METHOD_CODE, NULL, PAYMENT_METHOD_CODE, FND_API.G_MISS_CHAR, NULL, P_PAYMENT_METHOD_CODE)
84 ,REQUEST_DATE = DECODE(P_REQUEST_DATE, NULL, REQUEST_DATE, FND_API.G_MISS_DATE, NULL, P_REQUEST_DATE)
85 ,PAYMENT_REQUEST_DATE = DECODE(P_PAYMENT_REQUEST_DATE, NULL, PAYMENT_REQUEST_DATE, FND_API.G_MISS_DATE, NULL, P_PAYMENT_REQUEST_DATE)
86 ,STATUS = DECODE(P_STATUS, NULL, STATUS, FND_API.G_MISS_CHAR, NULL, P_STATUS)
87 ,MESSAGES = DECODE(P_MESSAGES, NULL, MESSAGES, FND_API.G_MISS_CHAR, NULL, P_MESSAGES)
88 ,OBJECT_VERSION_NUMBER = DECODE(P_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, P_OBJECT_VERSION_NUMBER)
89 ,LAST_UPDATE_DATE = LNS_UTILITY_PUB.LAST_UPDATE_DATE
90 ,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
91 ,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
92 WHERE DISB_HISTORY_ID = P_DISB_HISTORY_ID;
93
94 if (sql%notfound) then
95 raise no_data_found;
96 end if;
97 END Update_Row;
98
99 /* Delete_Row procedure */
100 PROCEDURE Delete_Row(P_DISB_HISTORY_ID IN NUMBER) IS
101 BEGIN
102 DELETE FROM LNS_DISB_HISTORIES_H
103 WHERE DISB_HISTORY_ID = P_DISB_HISTORY_ID;
104
105 if (sql%notfound) then
106 raise no_data_found;
107 end if;
108 END Delete_Row;
109
110 /* Lock_Row procedure */
111 PROCEDURE Lock_Row(
112 P_DISB_HISTORY_ID IN NUMBER
113 ,P_DISB_LINE_ID IN NUMBER
114 ,P_ATTEMPT_NUMBER IN NUMBER
115 ,P_BANK_ACCOUNT_ID IN NUMBER
116 ,P_PAYMENT_METHOD_CODE IN VARCHAR2
117 ,P_REQUEST_DATE IN DATE
118 ,P_PAYMENT_REQUEST_DATE IN DATE
119 ,P_STATUS IN VARCHAR2
120 ,P_MESSAGES IN VARCHAR2
121 ,P_OBJECT_VERSION_NUMBER IN NUMBER
122 ,P_CREATION_DATE IN DATE
123 ,P_CREATED_BY IN NUMBER
124 ,P_LAST_UPDATE_DATE IN DATE
125 ,P_LAST_UPDATED_BY IN NUMBER
126 ,P_LAST_UPDATE_LOGIN IN NUMBER
127 ) IS
128 CURSOR C IS SELECT * FROM LNS_DISB_HISTORIES_H
129 WHERE DISB_HISTORY_ID = P_DISB_HISTORY_ID
130 FOR UPDATE of DISB_HISTORY_ID NOWAIT;
131 Recinfo C%ROWTYPE;
132 BEGIN
133 OPEN C;
134 FETCH C INTO Recinfo;
135 IF (C%NOTFOUND) THEN
136 CLOSE C;
137 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
138 APP_EXCEPTION.Raise_Exception;
139 END IF;
140 CLOSE C;
141
142 IF (
143 (Recinfo.DISB_HISTORY_ID = P_DISB_HISTORY_ID)
144 AND ( (Recinfo.DISB_LINE_ID = P_DISB_LINE_ID)
145 OR ( (Recinfo.DISB_LINE_ID IS NULL)
146 AND (P_DISB_LINE_ID IS NULL)))
147 AND ( (Recinfo.ATTEMPT_NUMBER = P_ATTEMPT_NUMBER)
148 OR ( (Recinfo.ATTEMPT_NUMBER IS NULL)
149 AND (P_ATTEMPT_NUMBER IS NULL)))
150 AND ( (Recinfo.BANK_ACCOUNT_ID = P_BANK_ACCOUNT_ID)
151 OR ( (Recinfo.BANK_ACCOUNT_ID IS NULL)
152 AND (P_BANK_ACCOUNT_ID IS NULL)))
153 AND ( (Recinfo.PAYMENT_METHOD_CODE = P_PAYMENT_METHOD_CODE)
154 OR ( (Recinfo.PAYMENT_METHOD_CODE IS NULL)
155 AND (P_PAYMENT_METHOD_CODE IS NULL)))
156 AND ( (Recinfo.REQUEST_DATE = P_REQUEST_DATE)
157 OR ( (Recinfo.REQUEST_DATE IS NULL)
158 AND (P_REQUEST_DATE IS NULL)))
159 AND ( (Recinfo.PAYMENT_REQUEST_DATE = P_PAYMENT_REQUEST_DATE)
160 OR ( (Recinfo.PAYMENT_REQUEST_DATE IS NULL)
161 AND (P_PAYMENT_REQUEST_DATE IS NULL)))
162 AND ( (Recinfo.STATUS = P_STATUS)
163 OR ( (Recinfo.STATUS IS NULL)
164 AND (P_STATUS IS NULL)))
165 AND ( (Recinfo.MESSAGES = P_MESSAGES)
166 OR ( (Recinfo.MESSAGES IS NULL)
167 AND (P_MESSAGES IS NULL)))
168 AND ( (Recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER)
169 OR ( (Recinfo.OBJECT_VERSION_NUMBER IS NULL)
170 AND (P_OBJECT_VERSION_NUMBER IS NULL)))
171 AND ( (Recinfo.CREATION_DATE = P_CREATION_DATE)
172 OR ( (Recinfo.CREATION_DATE IS NULL)
173 AND (P_CREATION_DATE IS NULL)))
174 AND ( (Recinfo.CREATED_BY = P_CREATED_BY)
175 OR ( (Recinfo.CREATED_BY IS NULL)
176 AND (P_CREATED_BY IS NULL)))
177 AND ( (Recinfo.LAST_UPDATE_DATE = P_LAST_UPDATE_DATE)
178 OR ( (Recinfo.LAST_UPDATE_DATE IS NULL)
179 AND (P_LAST_UPDATE_DATE IS NULL)))
180 AND ( (Recinfo.LAST_UPDATED_BY = P_LAST_UPDATED_BY)
181 OR ( (Recinfo.LAST_UPDATED_BY IS NULL)
182 AND (P_LAST_UPDATED_BY IS NULL)))
183 AND ( (Recinfo.LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN)
184 OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
185 AND (P_LAST_UPDATE_LOGIN IS NULL)))
186 ) THEN
187 return;
188 ELSE
189 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
190 APP_EXCEPTION.Raise_Exception;
191 END IF;
192 END Lock_Row;
193 END;
194
[Home] [Help]