1 PACKAGE BODY LNS_DISB_LINES_PKG AS
2 /* $Header: LNS_DSBLN_TBLH_B.pls 120.3 2011/10/25 20:36:04 scherkas ship $ */
3
4 /* Insert_Row procedure */
5 PROCEDURE Insert_Row(
6 X_DISB_LINE_ID IN OUT NOCOPY NUMBER
7 ,P_DISB_HEADER_ID IN NUMBER
8 ,P_DISB_LINE_NUMBER IN NUMBER
9 ,P_LINE_AMOUNT IN NUMBER
10 ,P_LINE_PERCENT IN NUMBER
11 ,P_PAYEE_PARTY_ID IN NUMBER
12 ,P_BANK_ACCOUNT_ID IN NUMBER
13 ,P_PAYMENT_METHOD_CODE IN VARCHAR2
14 ,P_STATUS IN VARCHAR2
15 ,P_REQUEST_DATE IN DATE
16 ,P_DISBURSEMENT_DATE IN DATE
17 ,P_OBJECT_VERSION_NUMBER IN NUMBER
18 ,P_CREATION_DATE IN DATE
19 ,P_CREATED_BY IN NUMBER
20 ,P_LAST_UPDATE_DATE IN DATE
21 ,P_LAST_UPDATED_BY IN NUMBER
22 ,P_LAST_UPDATE_LOGIN IN NUMBER
23 ,P_INVOICE_INTERFACE_ID IN NUMBER
24 ,P_INVOICE_ID IN NUMBER
25 ,P_PARTY_SITE_ID IN NUMBER DEFAULT NULL
26 ,P_INCOME_TAX_REGION IN VARCHAR2 DEFAULT NULL
27 ,P_TYPE_1099 IN VARCHAR2 DEFAULT NULL
28 ) IS
29 BEGIN
30 INSERT INTO LNS_DISB_LINES
31 (
32 DISB_LINE_ID
33 ,DISB_HEADER_ID
34 ,DISB_LINE_NUMBER
35 ,LINE_AMOUNT
36 ,LINE_PERCENT
37 ,PAYEE_PARTY_ID
38 ,BANK_ACCOUNT_ID
39 ,PAYMENT_METHOD_CODE
40 ,STATUS
41 ,REQUEST_DATE
42 ,DISBURSEMENT_DATE
43 ,OBJECT_VERSION_NUMBER
44 ,CREATION_DATE
45 ,CREATED_BY
46 ,LAST_UPDATE_DATE
47 ,LAST_UPDATED_BY
48 ,LAST_UPDATE_LOGIN
49 ,INVOICE_INTERFACE_ID
50 ,INVOICE_ID
51 ,PARTY_SITE_ID
52 ,INCOME_TAX_REGION
53 ,TYPE_1099
54 ) VALUES (
55 DECODE(X_DISB_LINE_ID, FND_API.G_MISS_NUM, LNS_DISB_LINES_S.NEXTVAL, NULL, LNS_DISB_LINES_S.NEXTVAL, X_DISB_LINE_ID)
56 ,DECODE(P_DISB_HEADER_ID, FND_API.G_MISS_NUM, NULL, P_DISB_HEADER_ID)
57 ,DECODE(P_DISB_LINE_NUMBER, FND_API.G_MISS_NUM, NULL, P_DISB_LINE_NUMBER)
58 ,DECODE(P_LINE_AMOUNT, FND_API.G_MISS_NUM, NULL, P_LINE_AMOUNT)
59 ,DECODE(P_LINE_PERCENT, FND_API.G_MISS_NUM, NULL, P_LINE_PERCENT)
60 ,DECODE(P_PAYEE_PARTY_ID, FND_API.G_MISS_NUM, NULL, P_PAYEE_PARTY_ID)
61 ,DECODE(P_BANK_ACCOUNT_ID, FND_API.G_MISS_NUM, NULL, P_BANK_ACCOUNT_ID)
62 ,DECODE(P_PAYMENT_METHOD_CODE, FND_API.G_MISS_CHAR, NULL, P_PAYMENT_METHOD_CODE)
63 ,DECODE(P_STATUS, FND_API.G_MISS_CHAR, NULL, P_STATUS)
64 ,DECODE(P_REQUEST_DATE, FND_API.G_MISS_DATE, NULL, P_REQUEST_DATE)
65 ,DECODE(P_DISBURSEMENT_DATE, FND_API.G_MISS_DATE, NULL, P_DISBURSEMENT_DATE)
66 ,DECODE(P_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, P_OBJECT_VERSION_NUMBER)
67 ,LNS_UTILITY_PUB.CREATION_DATE
68 ,LNS_UTILITY_PUB.CREATED_BY
69 ,LNS_UTILITY_PUB.LAST_UPDATE_DATE
70 ,LNS_UTILITY_PUB.LAST_UPDATED_BY
71 ,LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
72 ,DECODE(P_INVOICE_INTERFACE_ID, FND_API.G_MISS_NUM, NULL, P_INVOICE_INTERFACE_ID)
73 ,DECODE(P_INVOICE_ID, FND_API.G_MISS_NUM, NULL, P_INVOICE_ID)
74 ,DECODE(P_PARTY_SITE_ID, FND_API.G_MISS_NUM, NULL, P_PARTY_SITE_ID)
75 ,DECODE(P_INCOME_TAX_REGION, FND_API.G_MISS_CHAR, NULL, P_INCOME_TAX_REGION)
76 ,DECODE(P_TYPE_1099, FND_API.G_MISS_CHAR, NULL, P_TYPE_1099)
77 ) RETURNING
78 DISB_LINE_ID
79 INTO
80 X_DISB_LINE_ID;
81 END Insert_Row;
82
83 /* Update_Row procedure */
84 PROCEDURE Update_Row(
85 P_DISB_LINE_ID IN NUMBER
86 ,P_DISB_HEADER_ID IN NUMBER
87 ,P_DISB_LINE_NUMBER IN NUMBER
88 ,P_LINE_AMOUNT IN NUMBER
89 ,P_LINE_PERCENT IN NUMBER
90 ,P_PAYEE_PARTY_ID IN NUMBER
91 ,P_BANK_ACCOUNT_ID IN NUMBER
92 ,P_PAYMENT_METHOD_CODE IN VARCHAR2
93 ,P_STATUS IN VARCHAR2
94 ,P_REQUEST_DATE IN DATE
95 ,P_DISBURSEMENT_DATE IN DATE
96 ,P_OBJECT_VERSION_NUMBER IN NUMBER
97 ,P_LAST_UPDATE_DATE IN DATE
98 ,P_LAST_UPDATED_BY IN NUMBER
99 ,P_LAST_UPDATE_LOGIN IN NUMBER
100 ,P_INVOICE_INTERFACE_ID IN NUMBER
101 ,P_INVOICE_ID IN NUMBER
102 ,P_PARTY_SITE_ID IN NUMBER
103 ,P_INCOME_TAX_REGION IN VARCHAR2
104 ,P_TYPE_1099 IN VARCHAR2
105 ) IS
106 BEGIN
107 UPDATE LNS_DISB_LINES SET
108 DISB_HEADER_ID = DECODE(P_DISB_HEADER_ID, NULL, DISB_HEADER_ID, FND_API.G_MISS_NUM, NULL, P_DISB_HEADER_ID)
109 ,DISB_LINE_NUMBER = DECODE(P_DISB_LINE_NUMBER, NULL, DISB_LINE_NUMBER, FND_API.G_MISS_NUM, NULL, P_DISB_LINE_NUMBER)
110 ,LINE_AMOUNT = DECODE(P_LINE_AMOUNT, NULL, LINE_AMOUNT, FND_API.G_MISS_NUM, NULL, P_LINE_AMOUNT)
111 ,LINE_PERCENT = DECODE(P_LINE_PERCENT, NULL, LINE_PERCENT, FND_API.G_MISS_NUM, NULL, P_LINE_PERCENT)
112 ,PAYEE_PARTY_ID = DECODE(P_PAYEE_PARTY_ID, NULL, PAYEE_PARTY_ID, FND_API.G_MISS_NUM, NULL, P_PAYEE_PARTY_ID)
113 ,BANK_ACCOUNT_ID = DECODE(P_BANK_ACCOUNT_ID, NULL, BANK_ACCOUNT_ID, FND_API.G_MISS_NUM, NULL, P_BANK_ACCOUNT_ID)
114 ,PAYMENT_METHOD_CODE = DECODE(P_PAYMENT_METHOD_CODE, NULL, PAYMENT_METHOD_CODE, FND_API.G_MISS_CHAR, NULL, P_PAYMENT_METHOD_CODE)
115 ,STATUS = DECODE(P_STATUS, NULL, STATUS, FND_API.G_MISS_CHAR, NULL, P_STATUS)
116 ,REQUEST_DATE = DECODE(P_REQUEST_DATE, NULL, REQUEST_DATE, FND_API.G_MISS_DATE, NULL, P_REQUEST_DATE)
117 ,DISBURSEMENT_DATE = DECODE(P_DISBURSEMENT_DATE, NULL, DISBURSEMENT_DATE, FND_API.G_MISS_DATE, NULL, P_DISBURSEMENT_DATE)
118 ,OBJECT_VERSION_NUMBER = DECODE(P_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, P_OBJECT_VERSION_NUMBER)
119 ,LAST_UPDATE_DATE = LNS_UTILITY_PUB.LAST_UPDATE_DATE
120 ,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
121 ,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
122 ,INVOICE_INTERFACE_ID = DECODE(P_INVOICE_INTERFACE_ID, NULL, INVOICE_INTERFACE_ID, FND_API.G_MISS_NUM, NULL, P_INVOICE_INTERFACE_ID)
123 ,INVOICE_ID = DECODE(P_INVOICE_ID, NULL, INVOICE_ID, FND_API.G_MISS_NUM, NULL, P_INVOICE_ID)
124 ,PARTY_SITE_ID = DECODE(P_PARTY_SITE_ID, NULL, PARTY_SITE_ID, FND_API.G_MISS_NUM, NULL, P_PARTY_SITE_ID)
125 ,INCOME_TAX_REGION = DECODE(P_INCOME_TAX_REGION, NULL, INCOME_TAX_REGION, FND_API.G_MISS_CHAR, NULL, P_INCOME_TAX_REGION)
126 ,TYPE_1099 = DECODE(P_TYPE_1099, NULL, TYPE_1099, FND_API.G_MISS_CHAR, NULL, P_TYPE_1099)
127 WHERE DISB_LINE_ID = P_DISB_LINE_ID;
128
129 if (sql%notfound) then
130 raise no_data_found;
131 end if;
132 END Update_Row;
133
134 /* Delete_Row procedure */
135 PROCEDURE Delete_Row(P_DISB_LINE_ID IN NUMBER) IS
136 BEGIN
137 DELETE FROM LNS_DISB_LINES
138 WHERE DISB_LINE_ID = P_DISB_LINE_ID;
139
140 if (sql%notfound) then
141 raise no_data_found;
142 end if;
143 END Delete_Row;
144
145 /* Lock_Row procedure */
146 PROCEDURE Lock_Row(
147 P_DISB_LINE_ID IN NUMBER
148 ,P_DISB_HEADER_ID IN NUMBER
149 ,P_DISB_LINE_NUMBER IN NUMBER
150 ,P_LINE_AMOUNT IN NUMBER
151 ,P_LINE_PERCENT IN NUMBER
152 ,P_PAYEE_PARTY_ID IN NUMBER
153 ,P_BANK_ACCOUNT_ID IN NUMBER
154 ,P_PAYMENT_METHOD_CODE IN VARCHAR2
155 ,P_STATUS IN VARCHAR2
156 ,P_REQUEST_DATE IN DATE
157 ,P_DISBURSEMENT_DATE IN DATE
158 ,P_OBJECT_VERSION_NUMBER IN NUMBER
159 ,P_CREATION_DATE IN DATE
160 ,P_CREATED_BY IN NUMBER
161 ,P_LAST_UPDATE_DATE IN DATE
162 ,P_LAST_UPDATED_BY IN NUMBER
163 ,P_LAST_UPDATE_LOGIN IN NUMBER
164 ,P_INVOICE_INTERFACE_ID IN NUMBER
165 ,P_INVOICE_ID IN NUMBER
166 ,P_PARTY_SITE_ID IN NUMBER
167 ,P_INCOME_TAX_REGION IN VARCHAR2
168 ,P_TYPE_1099 IN VARCHAR2
169 ) IS
170 CURSOR C IS SELECT * FROM LNS_DISB_LINES
171 WHERE DISB_LINE_ID = P_DISB_LINE_ID
172 FOR UPDATE of DISB_LINE_ID NOWAIT;
173 Recinfo C%ROWTYPE;
174 BEGIN
175 OPEN C;
176 FETCH C INTO Recinfo;
177 IF (C%NOTFOUND) THEN
178 CLOSE C;
179 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
180 APP_EXCEPTION.Raise_Exception;
181 END IF;
182 CLOSE C;
183
184 IF (
185 (Recinfo.DISB_LINE_ID = P_DISB_LINE_ID)
186 AND ( (Recinfo.DISB_HEADER_ID = P_DISB_HEADER_ID)
187 OR ( (Recinfo.DISB_HEADER_ID IS NULL)
188 AND (P_DISB_HEADER_ID IS NULL)))
189 AND ( (Recinfo.DISB_LINE_NUMBER = P_DISB_LINE_NUMBER)
190 OR ( (Recinfo.DISB_LINE_NUMBER IS NULL)
191 AND (P_DISB_LINE_NUMBER IS NULL)))
192 AND ( (Recinfo.LINE_AMOUNT = P_LINE_AMOUNT)
193 OR ( (Recinfo.LINE_AMOUNT IS NULL)
194 AND (P_LINE_AMOUNT IS NULL)))
195 AND ( (Recinfo.LINE_PERCENT = P_LINE_PERCENT)
196 OR ( (Recinfo.LINE_PERCENT IS NULL)
197 AND (P_LINE_PERCENT IS NULL)))
198 AND ( (Recinfo.PAYEE_PARTY_ID = P_PAYEE_PARTY_ID)
199 OR ( (Recinfo.PAYEE_PARTY_ID IS NULL)
200 AND (P_PAYEE_PARTY_ID IS NULL)))
201 AND ( (Recinfo.BANK_ACCOUNT_ID = P_BANK_ACCOUNT_ID)
202 OR ( (Recinfo.BANK_ACCOUNT_ID IS NULL)
203 AND (P_BANK_ACCOUNT_ID IS NULL)))
204 AND ( (Recinfo.PAYMENT_METHOD_CODE = P_PAYMENT_METHOD_CODE)
205 OR ( (Recinfo.PAYMENT_METHOD_CODE IS NULL)
206 AND (P_PAYMENT_METHOD_CODE IS NULL)))
207 AND ( (Recinfo.STATUS = P_STATUS)
208 OR ( (Recinfo.STATUS IS NULL)
209 AND (P_STATUS IS NULL)))
210 AND ( (Recinfo.REQUEST_DATE = P_REQUEST_DATE)
211 OR ( (Recinfo.REQUEST_DATE IS NULL)
212 AND (P_REQUEST_DATE IS NULL)))
213 AND ( (Recinfo.DISBURSEMENT_DATE = P_DISBURSEMENT_DATE)
214 OR ( (Recinfo.DISBURSEMENT_DATE IS NULL)
215 AND (P_DISBURSEMENT_DATE IS NULL)))
216 AND ( (Recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER)
217 OR ( (Recinfo.OBJECT_VERSION_NUMBER IS NULL)
218 AND (P_OBJECT_VERSION_NUMBER IS NULL)))
219 AND ( (Recinfo.CREATION_DATE = P_CREATION_DATE)
220 OR ( (Recinfo.CREATION_DATE IS NULL)
221 AND (P_CREATION_DATE IS NULL)))
222 AND ( (Recinfo.CREATED_BY = P_CREATED_BY)
223 OR ( (Recinfo.CREATED_BY IS NULL)
224 AND (P_CREATED_BY IS NULL)))
225 AND ( (Recinfo.LAST_UPDATE_DATE = P_LAST_UPDATE_DATE)
226 OR ( (Recinfo.LAST_UPDATE_DATE IS NULL)
227 AND (P_LAST_UPDATE_DATE IS NULL)))
228 AND ( (Recinfo.LAST_UPDATED_BY = P_LAST_UPDATED_BY)
229 OR ( (Recinfo.LAST_UPDATED_BY IS NULL)
230 AND (P_LAST_UPDATED_BY IS NULL)))
231 AND ( (Recinfo.LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN)
232 OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
233 AND (P_LAST_UPDATE_LOGIN IS NULL)))
234 AND ( (Recinfo.INVOICE_INTERFACE_ID = P_INVOICE_INTERFACE_ID)
235 OR ( (Recinfo.INVOICE_INTERFACE_ID IS NULL)
236 AND (P_INVOICE_INTERFACE_ID IS NULL)))
237 AND ( (Recinfo.INVOICE_ID = P_INVOICE_ID)
238 OR ( (Recinfo.INVOICE_ID IS NULL)
239 AND (P_INVOICE_ID IS NULL)))
240 AND ( (Recinfo.PARTY_SITE_ID = P_PARTY_SITE_ID)
241 OR ( (Recinfo.PARTY_SITE_ID IS NULL)
242 AND (P_PARTY_SITE_ID IS NULL)))
243 AND ( (Recinfo.INCOME_TAX_REGION = P_INCOME_TAX_REGION)
244 OR ( (Recinfo.INCOME_TAX_REGION IS NULL)
245 AND (P_INCOME_TAX_REGION IS NULL)))
246 AND ( (Recinfo.TYPE_1099 = P_TYPE_1099)
247 OR ( (Recinfo.TYPE_1099 IS NULL)
248 AND (P_TYPE_1099 IS NULL)))
249 ) THEN
250 return;
251 ELSE
252 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
253 APP_EXCEPTION.Raise_Exception;
254 END IF;
255 END Lock_Row;
256 END;
257
[Home] [Help]