1 PACKAGE BODY CSP_USG_TRANSACTION_TYPES_PKG as
2 /* $Header: csptuttb.pls 115.1 2003/10/03 19:03:28 sunarasi noship $ */
3 -- Start of Comments
4 -- Package name : CSP_USG_TRANSACTION_TYPES_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_USG_TRANSACTION_TYPES_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csptbrub.pls';
13
14 PROCEDURE Insert_Row(
15 px_USG_TRANSACTION_TYPE_ID IN OUT NOCOPY NUMBER
16 ,p_LAST_UPDATE_DATE DATE
17 ,p_LAST_UPDATED_BY NUMBER
18 ,p_CREATION_DATE DATE
19 ,p_CREATED_BY NUMBER
20 ,p_LAST_UPDATE_LOGIN NUMBER
21 ,p_FORECAST_RULE_ID NUMBER
22 ,p_TRANSACTION_TYPE_ID NUMBER)
23 IS
24 CURSOR C2 IS SELECT CSP_USG_TRANSACTION_TYPES_S1.nextval FROM sys.dual;
25 BEGIN
26 If px_USG_TRANSACTION_TYPE_ID IS NULL Then
27 OPEN C2;
28 FETCH C2 INTO px_USG_TRANSACTION_TYPE_ID;
29 CLOSE C2;
30 End If;
31 INSERT INTO CSP_USG_TRANSACTION_TYPES(
32 USG_TRANSACTION_TYPE_ID
33 ,LAST_UPDATE_DATE
34 ,LAST_UPDATED_BY
35 ,CREATION_DATE
36 ,CREATED_BY
37 ,LAST_UPDATE_LOGIN
38 ,FORECAST_RULE_ID
39 ,TRANSACTION_TYPE_ID
40 ) VALUES (
41 px_USG_TRANSACTION_TYPE_ID
42 ,p_LAST_UPDATE_DATE
43 ,p_LAST_UPDATED_BY
44 ,p_CREATION_DATE
45 ,p_CREATED_BY
46 ,p_LAST_UPDATE_LOGIN
47 ,p_FORECAST_RULE_ID
48 ,p_TRANSACTION_TYPE_ID);
49 END Insert_Row;
50
51 Procedure Update_Row(
52 p_USG_TRANSACTION_TYPE_ID NUMBER
53 ,p_LAST_UPDATE_DATE DATE
54 ,p_LAST_UPDATED_BY NUMBER
55 ,p_CREATION_DATE DATE
56 ,p_CREATED_BY NUMBER
57 ,p_LAST_UPDATE_LOGIN NUMBER
58 ,p_FORECAST_RULE_ID NUMBER
59 ,p_TRANSACTION_TYPE_ID NUMBER)
60 IS
61 BEGIN
62 Update CSP_USG_TRANSACTION_TYPES
63 SET
64 LAST_UPDATE_DATE = p_LAST_UPDATE_DATE
65 ,LAST_UPDATED_BY = p_LAST_UPDATED_BY
66 ,CREATION_DATE = p_CREATION_DATE
67 ,CREATED_BY = p_CREATED_BY
68 ,LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
69 ,FORECAST_RULE_ID = p_FORECAST_RULE_ID
70 ,TRANSACTION_TYPE_ID = p_TRANSACTION_TYPE_ID
71 where USG_TRANSACTION_TYPE_ID = p_USG_TRANSACTION_TYPE_ID;
72
73 If (SQL%NOTFOUND) then
74 RAISE NO_DATA_FOUND;
75 End If;
76 END Update_Row;
77
78 PROCEDURE Delete_Row(
79 p_USG_TRANSACTION_TYPE_ID NUMBER)
80 IS
81 BEGIN
82 DELETE FROM CSP_USG_TRANSACTION_TYPES
83 WHERE USG_TRANSACTION_TYPE_ID = p_USG_TRANSACTION_TYPE_ID;
84 If (SQL%NOTFOUND) then
85 RAISE NO_DATA_FOUND;
86 End If;
87 END Delete_Row;
88
89 Procedure Lock_Row(
90 p_USG_TRANSACTION_TYPE_ID NUMBER
91 ,p_LAST_UPDATE_DATE DATE
92 ,p_LAST_UPDATED_BY NUMBER
93 ,p_CREATION_DATE DATE
94 ,p_CREATED_BY NUMBER
95 ,p_LAST_UPDATE_LOGIN NUMBER
96 ,p_FORECAST_RULE_ID NUMBER
97 ,p_TRANSACTION_TYPE_ID NUMBER)
98 IS
99 CURSOR C IS
100 SELECT *
101 FROM CSP_USG_TRANSACTION_TYPES
102 WHERE USG_TRANSACTION_TYPE_ID = p_USG_TRANSACTION_TYPE_ID
103 FOR UPDATE of USG_TRANSACTION_TYPE_ID NOWAIT;
104 Recinfo C%ROWTYPE;
105 BEGIN
106 OPEN C;
107 FETCH C INTO Recinfo;
108 If (C%NOTFOUND) then
109 CLOSE C;
110 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
111 APP_EXCEPTION.RAISE_EXCEPTION;
112 End If;
113 CLOSE C;
114 if (
115 ( Recinfo.USG_TRANSACTION_TYPE_ID = p_USG_TRANSACTION_TYPE_ID)
116 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
117 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
118 AND ( p_LAST_UPDATE_DATE IS NULL )))
119 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
120 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
121 AND ( p_LAST_UPDATED_BY IS NULL )))
122 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
123 OR ( ( Recinfo.CREATION_DATE IS NULL )
124 AND ( p_CREATION_DATE IS NULL )))
125 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
126 OR ( ( Recinfo.CREATED_BY IS NULL )
127 AND ( p_CREATED_BY IS NULL )))
128 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
129 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
130 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
131 AND ( ( Recinfo.FORECAST_RULE_ID = p_FORECAST_RULE_ID)
132 OR ( ( Recinfo.FORECAST_RULE_ID IS NULL )
133 AND ( p_FORECAST_RULE_ID IS NULL )))
134 AND ( ( Recinfo.TRANSACTION_TYPE_ID = p_TRANSACTION_TYPE_ID)
135 OR ( ( Recinfo.TRANSACTION_TYPE_ID IS NULL )
136 AND ( p_TRANSACTION_TYPE_ID IS NULL )))
137 ) then
138 return;
139 else
140 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
141 APP_EXCEPTION.RAISE_EXCEPTION;
142 End If;
143 END Lock_Row;
144
145 End CSP_USG_TRANSACTION_TYPES_PKG;