DBA Data[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;