[Home] [Help]
PACKAGE BODY: APPS.GMD_QUALITY_PARAMETERS_GRP
Source
1 PACKAGE BODY GMD_QUALITY_PARAMETERS_GRP AS
2 /* $Header: GMDGQLPB.pls 120.3.12000000.2 2007/02/07 08:46:00 srakrish ship $ */
3 -- Start of comments
4 --+============================================================================+
5 --| Copyright (c) 2005 Oracle Corporation |
6 --| Redwood Shores, CA, USA |
7 --| All rights reserved. |
8 --+============================================================================+
9 --| File Name : GMDGQLPB.pls |
10 --| Package Name : GMD_QUALITY_PARAMETERS_GRP |
11 --| Type : Group |
12 --| |
13 --| Notes |
14 --| This package contains group layer APIs for retrieving quality parameters |
15 --| |
16 --| HISTORY |
17 --| Saikiran Vankadari 14-Feb-2005 Created as part of Convergence. |
18 --| |
19 --| Srakrish bug 5570258 20-Nov-2006 Created the funtcion sort_by_orgn_code |
20 --+============================================================================+
21 -- End of comments
22
23
24 --Start of comments
25 --+========================================================================+
26 --| API Name : get_quality_parameters |
27 --| TYPE : Group |
28 --| Notes : This procedure retrieves quality parameters of a |
29 --| particular organization. |
30 --| |
31 --| Parameters : |
32 --| p_organization_id Organization ID as input parameter |
33 --| x_quality_parameters Out Parameter. This record variable contains |
34 --| values of all parameters of an organization |
35 --| x_return_status E in case of an exception. Otherwise,S |
36 --| x_orgn_found TRUE if the parameters are present for an |
37 --| organization. Otherwise, FALSE. |
38 --| |
39 --| HISTORY |
40 --| Saikiran Vankadari 14-Feb-2005 Created. |
41 --| |
42 --+========================================================================+
43 -- End of comments
44
45
46
47
48 PROCEDURE get_quality_parameters
49 (p_organization_id IN NUMBER,
50 x_quality_parameters OUT NOCOPY GMD_QUALITY_CONFIG%ROWTYPE,
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_orgn_found OUT NOCOPY BOOLEAN
53 )
54 IS
55
56 CURSOR cr_get_process_parameters (p_orgn_id NUMBER) IS
57 SELECT * FROM GMD_QUALITY_CONFIG
58 WHERE organization_id = p_orgn_id;
59
60 BEGIN
61 x_return_status := FND_API.G_RET_STS_SUCCESS;
62 x_orgn_found := FALSE;
63
64 OPEN cr_get_process_parameters (p_organization_id);
65
66 FETCH cr_get_process_parameters INTO x_quality_parameters;
67 IF cr_get_process_parameters%FOUND THEN
68 x_orgn_found := TRUE;
69 ELSE
70 x_quality_parameters.exact_spec_match_ind := 'N';
71 x_quality_parameters.include_optional_test_rslt_ind := 'Y';
72 x_quality_parameters.spec_version_control_ind := 'N';
73 END IF;
74 CLOSE cr_get_process_parameters;
75
76 EXCEPTION
77
78 WHEN OTHERS THEN
79 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
80
81 END get_quality_parameters;
82
83
84 --Start of comments
85 --+========================================================================+
86 --| API Name : get_next_sample_no |
87 --| TYPE : Group |
88 --| Notes : This function returns the next sample number for a |
89 --| particular organization. |
90 --| |
91 --| Parameters : |
92 --| p_organization_id Organization ID as input parameter |
93 --| |
94 --| HISTORY |
95 --| Saikiran Vankadari 14-Feb-2005 Created. |
96 --| |
97 --+========================================================================+
98 -- End of comments
99
100
101 FUNCTION get_next_sample_no
102 (p_organization_id IN NUMBER
103 )
104 RETURN VARCHAR2
105 IS
106 PRAGMA AUTONOMOUS_TRANSACTION;
107 l_sample_no VARCHAR2(10);
108 l_last_assigned NUMBER(10);
109 CURSOR cr_get_sample_seq IS
110 SELECT
111 sample_last_assigned
112 FROM
113 gmd_quality_config
114 WHERE
115 organization_id = p_organization_id;
116
117 BEGIN
118
119 -- Update gmd_quality_config
120 UPDATE gmd_quality_config
121 SET
122 sample_last_assigned = sample_last_assigned + 1
123 WHERE
124 organization_id = p_organization_id;
125
126 -- Now get sample no
127 OPEN cr_get_sample_seq;
128
129 FETCH cr_get_sample_seq INTO
130 l_last_assigned;
131
132 CLOSE cr_get_sample_seq;
133
134 l_sample_no := TO_CHAR(l_last_assigned);
135
136 commit;
137 RETURN l_sample_no;
138
139 END get_next_sample_no;
140
141
142 --Start of comments
143 --+========================================================================+
144 --| API Name : get_next_ss_no |
145 --| TYPE : Group |
146 --| Notes : This function returns the next stability study number for|
147 --| a particular organization. |
148 --| |
149 --| Parameters : |
150 --| p_organization_id Organization ID as input parameter |
151 --| |
152 --| HISTORY |
153 --| Saikiran Vankadari 14-Feb-2005 Created. |
154 --| |
155 --+========================================================================+
156 -- End of comments
157
158
159 FUNCTION get_next_ss_no
160 (p_organization_id IN NUMBER
161 )
162 RETURN VARCHAR2
163 IS
164 PRAGMA AUTONOMOUS_TRANSACTION;
165 l_ss_no VARCHAR2(10);
166 l_last_assigned NUMBER(10);
167 CURSOR cr_get_ss_seq IS
168 SELECT
169 ss_last_assigned
170 FROM
171 gmd_quality_config
172 WHERE
173 organization_id = p_organization_id;
174
175 BEGIN
176
177 -- Update gmd_quality_config
178 UPDATE gmd_quality_config
179 SET
180 ss_last_assigned = ss_last_assigned + 1
181 WHERE
182 organization_id = p_organization_id;
183
184 -- Now get ss no
185 OPEN cr_get_ss_seq;
186
187 FETCH cr_get_ss_seq INTO
188 l_last_assigned;
189 CLOSE cr_get_ss_seq;
190
191 l_ss_no := TO_CHAR(l_last_assigned);
192
193 commit;
194 RETURN l_ss_no;
195
196 END get_next_ss_no;
197
198 --Start of comments
199 --+========================================================================+
200 --| API Name : sort_by_orgn_code |
201 --| TYPE : Group |
202 --| Notes : This function is returns teh value based on which the |
203 --| organizations need to be sorted |
204 --| Parameters : |
205 --| p_organization_id Organization ID as input parameter |
206 --| |
207 --| HISTORY |
208 --| srakrish 20-Nov-2006 Created. |
209 --| |
210 --+========================================================================+
211 -- End of comments
212
213 FUNCTION sort_by_orgn_code(p_organization_id in number)
214 return varchar2 is
215
216 l_organization_code varchar2(20);
217
218 cursor cr_get_orgn_code(p_organization_id Number) IS
219 select organization_code
220 from mtl_parameters
221 where organization_id = p_organization_id;
222
223 begin
224
225 open cr_get_orgn_code(p_organization_id);
226 fetch cr_get_orgn_code INTO l_organization_code;
227 close cr_get_orgn_code;
228
229 return l_organization_code;
230
231 end sort_by_orgn_code;
232
233 END gmd_quality_parameters_grp;