DBA Data[Home] [Help]

PACKAGE BODY: APPS.LNS_DISB_LINES_PKG

Source


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