1 PACKAGE BODY MTL_STATUS_TRX_CONTROL_PKG AS
2 /* $Header: INVMSTCB.pls 120.1 2005/06/11 11:28:19 appldev $ */
3
4 PROCEDURE INSERT_ROW (
5 x_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6 ,p_STATUS_ID IN NUMBER
7 ,p_TRANSACTION_TYPE_ID IN NUMBER
8 ,p_IS_ALLOWED IN NUMBER
9 ,p_CREATION_DATE IN DATE
10 ,p_CREATED_BY IN NUMBER
11 ,p_LAST_UPDATED_BY IN NUMBER
12 ,p_LAST_UPDATE_DATE IN DATE
13 ,p_LAST_UPDATE_LOGIN IN NUMBER
14 ,p_PROGRAM_APPLICATION_ID IN NUMBER
15 ,p_PROGRAM_ID IN NUMBER
16 )IS
17 CURSOR C IS SELECT ROWID FROM MTL_STATUS_TRANSACTION_CONTROL
18 WHERE status_id = p_STATUS_ID
19 AND transaction_type_id = p_TRANSACTION_TYPE_ID;
20 BEGIN
21 INSERT INTO MTL_STATUS_TRANSACTION_CONTROL (
22 status_id
23 , transaction_type_id
24 , is_allowed
25 , creation_date
26 , created_by
27 , last_updated_by
28 , last_update_date
29 , last_update_login
30 , program_application_id
31 , program_id
32 ) values (
33 p_STATUS_ID
34 ,p_TRANSACTION_TYPE_ID
35 ,p_IS_ALLOWED
36 ,p_CREATION_DATE
37 ,p_CREATED_BY
38 ,p_LAST_UPDATED_BY
39 ,p_LAST_UPDATE_DATE
40 ,p_LAST_UPDATE_LOGIN
41 ,p_PROGRAM_APPLICATION_ID
42 ,p_PROGRAM_ID
43 );
44
45 OPEN C;
46 FETCH C INTO x_rowid;
47 IF (C%NOTFOUND) THEN
48 CLOSE C;
49 RAISE NO_DATA_FOUND;
50 END IF;
51 CLOSE C;
52 END INSERT_ROW;
53
54 PROCEDURE LOCK_ROW (
55 p_STATUS_ID IN NUMBER
56 ,p_TRANSACTION_TYPE_ID IN NUMBER
57 ,p_IS_ALLOWED IN NUMBER
58 )IS
59 CURSOR C IS SELECT
60 status_id
61 , transaction_type_id
62 , is_allowed
63 , creation_date
64 , created_by
65 , last_updated_by
66 , last_update_date
67 , last_update_login
68 , program_application_id
69 , program_id
70 FROM MTL_STATUS_TRANSACTION_CONTROL
71 WHERE status_id = p_STATUS_ID
72 AND transaction_type_id = p_TRANSACTION_TYPE_ID
73 FOR UPDATE OF status_id,transaction_type_id NOWAIT;
74
75 recinfo c%ROWTYPE;
76 BEGIN
77 OPEN c;
78 FETCH c INTO recinfo;
79 IF (c%notfound) THEN
80 CLOSE c;
81 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
82 app_exception.raise_exception;
83 END IF;
84 CLOSE c;
85 IF ( (recinfo.status_id = p_STATUS_ID)
86 AND (recinfo.transaction_type_id = p_TRANSACTION_TYPE_ID)
87 AND (recinfo.is_allowed = p_IS_ALLOWED)
88 )THEN
89 NULL;
90 ELSE
91 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
92 app_exception.raise_exception;
93 END IF;
94 END LOCK_ROW;
95
96
97 PROCEDURE UPDATE_ROW (
98 p_STATUS_ID IN NUMBER
99 ,p_TRANSACTION_TYPE_ID IN NUMBER
100 ,p_IS_ALLOWED IN NUMBER
101 ,p_LAST_UPDATED_BY IN NUMBER
102 ,p_LAST_UPDATE_DATE IN DATE
103 ,p_LAST_UPDATE_LOGIN IN NUMBER
104 ,p_PROGRAM_APPLICATION_ID IN NUMBER
105 ,p_PROGRAM_ID IN NUMBER
106 )IS
107 x_ROWID varchar2(18);
108 BEGIN
109 UPDATE MTL_STATUS_TRANSACTION_CONTROL SET
110 status_id = p_STATUS_ID
111 , transaction_type_id = p_TRANSACTION_TYPE_ID
112 , is_allowed = p_IS_ALLOWED
113 , last_updated_by = p_LAST_UPDATED_BY
114 , last_update_date = p_LAST_UPDATE_DATE
115 , last_update_login = p_LAST_UPDATE_LOGIN
116 , program_application_id = p_PROGRAM_APPLICATION_ID
117 , program_id = p_PROGRAM_ID
118 WHERE status_id = p_STATUS_ID
119 AND transaction_type_id = p_TRANSACTION_TYPE_ID;
120
121 IF (SQL%NOTFOUND) THEN
122 MTL_STATUS_TRX_CONTROL_PKG.INSERT_ROW(
123 x_ROWID
124 ,p_STATUS_ID
125 ,p_TRANSACTION_TYPE_ID
126 ,p_IS_ALLOWED
127 ,SYSDATE
128 ,FND_GLOBAL.USER_ID
129 ,p_LAST_UPDATED_BY
130 ,p_LAST_UPDATE_DATE
131 ,p_LAST_UPDATE_LOGIN
132 ,p_PROGRAM_APPLICATION_ID
133 ,p_PROGRAM_ID
134 );
135 END IF;
136 -- commit since form can't detect the changes in list
137 commit;
138 END UPDATE_ROW;
139
140 PROCEDURE INSERT_EXTRA_ROWS(P_STATUS_ID IN NUMBER)
141 IS
142 CURSOR c_excluded_trxs IS
143 SELECT mmt.transaction_type_id
144 From MTL_TRANSACTION_TYPES mmt
145 where mmt.status_control_flag = 1
146 and mmt.transaction_type_id not in (
147 SELECT transaction_type_id
148 FROM MTL_STATUS_TRANSACTION_CONTROL
149 WHERE status_id = P_STATUS_ID);
150 c_t_type c_excluded_trxs%ROWTYPE;
151
152 x_ROWID varchar2(18);
153 BEGIN
154 FOR c_t_type IN c_excluded_trxs loop
155 MTL_STATUS_TRX_CONTROL_PKG.INSERT_ROW(
156 x_ROWID
157 ,p_STATUS_ID
158 ,c_t_type.TRANSACTION_TYPE_ID
159 ,1
160 ,SYSDATE
161 ,FND_GLOBAL.USER_ID
162 ,FND_GLOBAL.USER_ID
163 ,SYSDATE
164 ,FND_GLOBAL.LOGIN_ID
165 ,null
166 ,null
167 );
168 end loop;
169 commit;
170 END INSERT_EXTRA_ROWS;
171
172 END MTL_STATUS_TRX_CONTROL_PKG;
[Home] [Help]