[Home] [Help]
PACKAGE BODY: APPS.GMD_QC_ERES_CHANGE_STATUS_PVT
Source
1 package body GMD_QC_ERES_CHANGE_STATUS_PVT as
2 /* $Header: GMDVEREB.pls 120.1 2006/02/22 15:04:06 plowe noship $ */
3
4 PROCEDURE set_spec_status(p_spec_id IN NUMBER,
5 p_from_status IN VARCHAR2,
6 p_to_status IN VARCHAR2,
7 p_signature_status IN VARCHAR2) IS
8 l_signature_status VARCHAR2(40);
9 l_pending_status VARCHAR2(40);
10 l_rework_status VARCHAR2(40);
11 BEGIN
12 IF p_signature_status IS NOT NULL THEN
13 l_signature_status := p_signature_status;
14 ELSE
15 l_signature_status := EDR_PSIG_PAGE_FLOW.signature_status;
16 END IF;
17 IF l_signature_status = 'SUCCESS' THEN
18 UPDATE gmd_specifications_b
19 SET spec_status = p_to_status
20 WHERE spec_id = p_spec_id;
21 ELSIF l_signature_status = 'PENDING' THEN
22 l_pending_status := GMD_QC_STATUS_NEXT_PVT.get_pending_status(p_from_status => p_from_status
23 ,p_to_status => p_to_status
24 ,p_entity_type => 'S');
25 IF l_pending_status IS NOT NULL THEN
26 UPDATE gmd_specifications_b
27 SET spec_status = l_pending_status
28 WHERE spec_id = p_spec_id;
29 END IF;
30 ELSIF l_signature_status = 'REJECTED' THEN
31 l_rework_status := GMD_QC_STATUS_NEXT_PVT.get_rework_status(p_from_status => p_from_status
32 ,p_to_status => p_to_status
33 ,p_entity_type => 'S');
34 IF l_rework_status IS NOT NULL THEN
35 UPDATE gmd_specifications_b
36 SET spec_status = l_rework_status
37 WHERE spec_id = p_spec_id;
38 END IF;
39 END IF;
40
41 END set_spec_status;
42
43 PROCEDURE set_spec_vr_status(p_spec_vr_id IN NUMBER,
44 p_entity_type IN VARCHAR2,
45 p_from_status IN VARCHAR2,
46 p_to_status IN VARCHAR2,
47 p_signature_status IN VARCHAR2) IS
48 l_signature_status VARCHAR2(40);
49 l_pending_status VARCHAR2(40);
50 l_rework_status VARCHAR2(40);
51 BEGIN
52 IF p_signature_status IS NOT NULL THEN
53 l_signature_status := p_signature_status;
54 ELSE
55 l_signature_status := EDR_PSIG_PAGE_FLOW.signature_status;
56 END IF;
57 IF l_signature_status = 'SUCCESS' THEN
58 update_vr_status(p_entity_type,
59 p_spec_vr_id,
60 p_to_status);
61 ELSIF l_signature_status = 'PENDING' THEN
62 l_pending_status := GMD_QC_STATUS_NEXT_PVT.get_pending_status(p_from_status => p_from_status
63 ,p_to_status => p_to_status
64 ,p_entity_type => 'S');
65 IF l_pending_status IS NOT NULL THEN
66 update_vr_status(p_entity_type,
67 p_spec_vr_id,
68 l_pending_status);
69 END IF;
70 ELSIF l_signature_status = 'REJECTED' THEN
71 l_rework_status := GMD_QC_STATUS_NEXT_PVT.get_rework_status(p_from_status => p_from_status
72 ,p_to_status => p_to_status
73 ,p_entity_type => 'S');
74 IF l_rework_status IS NOT NULL THEN
75 update_vr_status(p_entity_type,
76 p_spec_vr_id,
77 l_rework_status);
78 END IF;
79 END IF;
80 END set_spec_vr_status;
81
82 PROCEDURE update_vr_status(pentity_type IN VARCHAR2,
83 pspec_vr_id IN NUMBER,
84 p_to_status IN NUMBER) IS
85 BEGIN
86
87 IF (pentity_type = 'I') THEN
88 UPDATE gmd_inventory_spec_vrs
89 SET spec_vr_status = p_to_status
90 WHERE spec_vr_id = pspec_vr_id;
91 ELSIF(pentity_type = 'W') THEN
92 UPDATE gmd_wip_spec_vrs
93 SET spec_vr_status = p_to_status
94 WHERE spec_vr_id = pspec_vr_id;
95 ELSIF(pentity_type = 'C') THEN
96 UPDATE gmd_customer_spec_vrs
97 SET spec_vr_status = p_to_status
98 WHERE spec_vr_id = pspec_vr_id;
99 ELSIF(pentity_type = 'S') THEN
100 UPDATE gmd_supplier_spec_vrs
101 SET spec_vr_status = p_to_status
102 WHERE spec_vr_id = pspec_vr_id;
103 ELSIF(pentity_type = 'M') THEN
104 UPDATE gmd_monitoring_spec_vrs
105 SET spec_vr_status = p_to_status
106 WHERE spec_vr_id = pspec_vr_id;
107 END IF;
108
109 END update_vr_status ;
110
111 FUNCTION chek_spec_validity_eres (p_spec_id IN NUMBER,
112 p_to_status IN VARCHAR2,
113 p_event IN VARCHAR2)
114 RETURN BOOLEAN IS
115 CURSOR Cur_get_validity IS
116 -- bug 4924550 sql id 14692703
117 /* SELECT spec_vr_id,spec_type
118 FROM gmd_all_spec_vrs
119 WHERE spec_id = p_spec_id
120 AND spec_vr_status < p_to_status; */
121 select spec_vr_id, 'I' spec_type
122 from GMD_INVENTORY_SPEC_VRS
123 where spec_id = p_spec_id
124 and spec_vr_status < p_to_status
125 UNION
126 select spec_vr_id,'W' spec_type
127 from GMD_WIP_SPEC_VRS
128 where spec_id = p_spec_id
129 and spec_vr_status < p_to_status
130 UNION
131 select spec_vr_id,'C' spec_type
132 from GMD_CUSTOMER_SPEC_VRS
133 where spec_id = p_spec_id
134 and spec_vr_status < p_to_status
135 UNION
136 select spec_vr_id,'S' spec_type
137 from GMD_SUPPLIER_SPEC_VRS
138 where spec_id = p_spec_id
139 and spec_vr_status < p_to_status
140 UNION
141 select spec_vr_id, rule_type spec_type -- R or L
142 from GMD_MONITORING_SPEC_VRS
143 where spec_id = p_spec_id
144 and spec_vr_status < p_to_status
145 UNION
146 select spec_vr_id, 'T' spec_type
147 from GMD_STABILITY_SPEC_VRS
148 where spec_id = p_spec_id
149 and spec_vr_status < p_to_status;
150
151 l_spec_validity_rule_id NUMBER;
152 l_status BOOLEAN;
153 l_spec_type VARCHAR2(2);
154 l_event_name VARCHAR2(40);
155 PRAGMA AUTONOMOUS_TRANSACTION;
156 BEGIN
157
158 OPEN Cur_get_validity;
159 FETCH Cur_get_validity INTO l_spec_validity_rule_id,l_spec_type;
160 WHILE Cur_get_validity%FOUND LOOP
161 update_vr_status(pentity_type => l_spec_type,
162 pspec_vr_id => l_spec_validity_rule_id,
163 p_to_status => p_to_status);
164 SELECT DECODE(l_spec_type,'I','oracle.apps.gmd.qm.spec.vr.inv',
165 'W','oracle.apps.gmd.qm.spec.vr.wip',
166 'C','oracle.apps.gmd.qm.spec.vr.cus',
167 'S','oracle.apps.gmd.qm.spec.vr.sup',
168 'R','oracle.apps.gmd.qm.spec.vr.mon',
169 'L','oracle.apps.gmd.qm.spec.vr.mon')
170 INTO l_event_name
171 FROM sys.dual;
172 EDR_STANDARD.psig_required (p_event => l_event_name
173 ,p_event_key => l_spec_validity_rule_id
174 ,p_status => l_status);
175
176
177 IF l_status THEN
178 ROLLBACK;
179 CLOSE Cur_get_validity;
180 RETURN TRUE;
181 END IF;
182 FETCH Cur_get_validity INTO l_spec_validity_rule_id,l_spec_type;
183 END LOOP;
184 ROLLBACK;
185 CLOSE Cur_get_validity;
186 RETURN FALSE;
187 END chek_spec_validity_eres;
188
189
190 FUNCTION esig_required (p_event IN VARCHAR2,
191 p_event_key IN VARCHAR2,
192 p_to_status IN VARCHAR2)
193 RETURN BOOLEAN IS
194 l_status BOOLEAN;
195 -- PRAGMA AUTONOMOUS_TRANSACTION;
196 BEGIN
197 /* Lets first update the status of the entity to to status */
198 IF p_event = 'oracle.apps.gmd.qm.spec.vr.inv' THEN
199 UPDATE gmd_inventory_spec_vrs
200 SET spec_vr_status = p_to_status
201 WHERE spec_vr_id = p_event_key
202 AND spec_vr_status <> p_to_status;
203 ELSIF p_event = 'oracle.apps.gmd.qm.spec.vr.wip' THEN
204 UPDATE gmd_wip_spec_vrs
205 SET spec_vr_status = p_to_status
206 WHERE spec_vr_id = p_event_key
207 AND spec_vr_status <> p_to_status;
208 ELSIF p_event = 'oracle.apps.gmd.qm.spec.vr.cus' THEN
209 UPDATE gmd_customer_spec_vrs
210 SET spec_vr_status = p_to_status
211 WHERE spec_vr_id = p_event_key
212 AND spec_vr_status <> p_to_status;
213 ELSIF p_event = 'oracle.apps.gmd.qm.spec.vr.sup' THEN
214 UPDATE gmd_supplier_spec_vrs
215 SET spec_vr_status = p_to_status
216 WHERE spec_vr_id = p_event_key
217 AND spec_vr_status <> p_to_status;
218 ELSIF p_event = 'oracle.apps.gmd.qm.spec' THEN
219 UPDATE gmd_specifications_b
220 SET spec_status = p_to_status
221 WHERE spec_id = p_event_key
222 AND spec_status <> p_to_status;
223 END IF;
224
225
226 GMA_STANDARD.psig_required (p_event => p_event
227 ,p_event_key => p_event_key
228 ,p_status => l_status);
229 --ROLLBACK;
230 IF l_status THEN
231 RETURN TRUE;
232 ELSE
233 RETURN FALSE;
234 END IF;
235 END esig_required;
236
237 end GMD_QC_ERES_CHANGE_STATUS_PVT;