[Home] [Help]
Skip to content
PACKAGE BODY: APPS.GMD_GME_INT
Source
1 PACKAGE BODY GMD_GME_INT AS
2 /* $Header: GMDGMEIB.pls 120.6.12020000.2 2012/07/17 10:23:37 mtou ship $ */
3
4 -- l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 --Bug 3222090, magupta removed call to FND_PROFILE.VALUE('AFLOG_ENABLED')
6 --forward decl.
7 function set_debug_flag return varchar2;
8 --l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
9 l_debug VARCHAR2(1) := set_debug_flag;
10
11 FUNCTION set_debug_flag RETURN VARCHAR2 IS
12 l_debug VARCHAR2(1):= 'N';
13 BEGIN
14 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
15 l_debug := 'Y';
16 END IF;
17 RETURN l_debug;
18 END set_debug_flag;
19
20 PROCEDURE check_qc(
21 p_recipeid IN NUMBER,
22 p_routingid IN NUMBER,
23 p_routingstepid IN NUMBER,
24 p_organization_id IN NUMBER DEFAULT NULL,
25 p_resultout OUT NOCOPY VARCHAR2)
26
27 IS
28
29 L_INVENTORY_ITEM_ID NUMBER;
30 L_ORGANIZATION_ID NUMBER;
31 l_revision VARCHAR2(3); --RLNAGARA B5389806 Added revision.
32 L_RECIPE_ID NUMBER;
33 L_FORMULA_ID NUMBER;
34 L_FORMULALINE_ID NUMBER;
35 L_ROUTING_ID NUMBER ;
36 L_STEP_ID NUMBER;
37 L_STEP_NO NUMBER ;
38 L_OPRN_ID NUMBER ;
39 L_EXACT_MATCH VARCHAR2(1);
40 L_SPEC_ID NUMBER ;
41 L_SPEC_VR_ID NUMBER ;
42 L_RETURN_STATUS VARCHAR2(100) ;
43 L_Msg_DATA VARCHAR2(2000) ;
44 l_wip_spec GMD_SPEC_MATCH_GRP.WIP_SPEC_REC_TYPE;
45 l_log varchar2(4000);
46 dummy number;
47 l_routingstepid number;
48 l_last_update_by NUMBER ;
49 l_control_batch_step_ind VARCHAR2(1); -- Bug No.14033362
50
51
52 Cursor C1(x_routing_id number,x_routing_step_id number , x_recipe_id number) is
53 SELECT C.OWNER_ORGANIZATION_ID, C.ROUTING_ID,
54 C.RECIPE_ID, C.FORMULA_ID,
55 C.ROUTING_ID
56 --MATL.ITEM_ID,
57 --MATL.FORMULALINE_ID
58 ,FM.OPRN_ID
59 ,FM.ROUTINGSTEP_ID
60 FROM GMD_RECIPES_B C,
61 FM_FORM_MST H,
62 fm_rout_dtl FM
63 --,FM_MATL_DTL MATL
64 WHERE
65 C.ROUTING_ID= x_routing_id AND
66 C.RECIPE_ID = x_recipe_id AND
67 FM.ROUTINGSTEP_ID= x_routing_step_id and
68 H.formula_id = C.Formula_id
69 AND FM.routing_id = C.routing_ID
70 --AND c.FORMULA_ID = MATL.FORMULA_ID
71 --AND h.FORMULA_ID = MATL.FORMULA_ID
72 ;
73
74 Cursor C2 (p_recipe_id number) is
75 select distinct md.inventory_item_id,md.revision ----RLNAGARA B5389806 Added revision
76 ,md.formulaline_id ----SMALLURU B6379386 Added formulaline_id
77 from fm_matl_dtl md, gmd_Recipes_b r
78 where r.recipe_id = p_recipe_id
79 and r.formula_id = md.formula_id
80 and md.line_type <> 2; -- no byproducts
81
82 -- PLOWE 10-May-2012 Bug No.14033362
83 CURSOR get_batch_step_status(x_spec_vr_id_in number) IS
84 SELECT nvl(control_batch_step_ind,'N') FROM gmd_wip_spec_vrs
85 WHERE spec_vr_id = x_spec_vr_id_in;
86
87 BEGIN
88
89 IF (l_debug = 'Y') THEN
90 gmd_debug.log_initialize('GMDGMEInt');
91 gmd_debug.put_line('Entering GMD_GME_INT.check_qc') ;
92 END IF;
93
94 -- p_resultout := 'F' = No sample Required
95 -- p_resultout := 'S' = Sample Required
96
97 /* COMMENTING THIS OUT AS IT CONTRADICTS THE ORIGINAL DESIGN AS PER ELAINE WAN - PLOWE 10-May-2012 Bug No.14033362
98
99 --Bug 4523278. If profile GMD:Batch step sample required is set to No
100 --set p_resultout to 'F'(No Sample Required) and RETURN.
101 IF NVL(FND_PROFILE.VALUE('GMD_BATCH_STEP_SMPL_REQD'), 'Y') = 'N' THEN
102 p_resultout := 'F';
103 RETURN;
104 END IF;
105 */
106
107
108
109
110 IF (l_debug = 'Y') THEN
111 gmd_debug.put_line('recipe ID ' || p_recipeid);
112 gmd_debug.put_line('routing ID ' || p_routingid);
113 gmd_debug.put_line('routing step ID ' || p_routingstepid);
114 gmd_debug.put_line('organization_id ' || p_organization_id);
115 END IF;
116
117 /* For a given recipe id, routing id, routingstepid combination */
118 OPEN C1(p_routingid,p_routingstepid,p_recipeid);
119 wf_log_pkg.string(6, 'Dummy','Before Fetching the values.');
120 Fetch C1 into L_ORGANIZATION_ID,L_ROUTING_ID,L_RECIPE_ID,
121 L_FORMULA_ID,L_ROUTING_ID,
122 --L_ITEM_ID,L_FORMULALINE_ID,
123 L_OPRN_ID,
124 l_routingstepid ;
125 CLOSE C1;
126
127 l_wip_spec.organization_id := p_organization_id;
128 l_wip_spec.batch_id := NULL;
129 l_wip_spec.recipe_id := L_recipe_id;
130 l_wip_spec.formula_id := L_formula_id;
131 l_wip_spec.formulaline_id := L_formulaline_id;
132 l_wip_spec.routing_id := L_routing_id;
133 l_wip_spec.step_id := l_routingstepid;
134 l_wip_spec.step_no := NULL;
135 l_wip_spec.oprn_id := L_oprn_id;
136 l_wip_spec.charge := NULL;
137 l_wip_spec.date_effective := SYSDATE;
138 l_wip_spec.exact_match := 'N';
139
140 /* Bug No.7032231 - Commented the following code as it is not supporting to get spec details */
141
142 /* IF (l_wip_spec.step_no IS NOT NULL or l_wip_spec.step_id IS NOT NULL) THEN
143 l_wip_spec.find_spec_with_step := 'Y';
144 ELSE
145 l_wip_spec.find_spec_with_step := 'N';
146 END IF; */
147
148
149 open C2(p_recipeid) ;
150 LOOP
151
152 Fetch C2 into l_inventory_item_id,l_revision ,l_formulaline_id;
153 exit when C2%notfound ;
154
155 l_wip_spec.inventory_item_id := l_inventory_item_id;
156 l_wip_spec.revision := l_revision; --RLNAGARA B5389806 Added revision
157 l_wip_spec.formulaline_id := l_formulaline_id; --Bug#6379386
158 l_step_id := l_routingstepid;
159
160 IF (l_debug = 'Y') THEN
161 gmd_debug.put_line('Checking if WIP Spec exists ');
162 END IF;
163
164 IF GMD_SPEC_MATCH_GRP.FIND_WIP_SPEC(
165 p_wip_spec_rec => l_wip_spec,
166 x_spec_id => l_spec_id,
167 x_spec_vr_id => l_spec_vr_id,
168 x_return_status => l_return_status,
169 x_message_data => l_msg_data) THEN
170
171 IF (l_debug = 'Y') THEN
172 gmd_debug.put_line('WIP Spec exists ');
173 END IF;
174 -- changes start for Bug No.14033362
175
176 OPEN get_batch_step_status(l_spec_vr_id);
177 FETCH get_batch_step_status
178 into l_control_batch_step_ind ;
179 CLOSE get_batch_step_status;
180 IF (l_debug = 'Y') THEN
181 gmd_debug.put_line('l_control_batch_step_ind ' || l_control_batch_step_ind);
182 END IF;
183
184 IF l_control_batch_step_ind = 'Y' then
185 IF (l_debug = 'Y') THEN
186 gmd_debug.put_line('l_control_batch_step_ind = Y so p_resultout = S');
187 END IF;
188
189 p_resultout:='S';
190 EXIT;
191
192 ELSE
193
194 IF NVL(FND_PROFILE.VALUE('GMD_BATCH_STEP_SMPL_REQD'), 'N') = 'Y' THEN
195 IF (l_debug = 'Y') THEN
196 gmd_debug.put_line('GMD_BATCH_STEP_SMPL_REQD = Y so p_resultout = S');
197 END IF;
198
199 p_resultout := 'S';
200 EXIT ;
201 ELSE
202 IF (l_debug = 'Y') THEN
203 gmd_debug.put_line('GMD_BATCH_STEP_SMPL_REQD = N so p_resultout = F');
204 END IF;
205
206 p_resultout := 'F';
207 END IF; -- IF NVL(FND_PROFILE.VALUE('GMD_BATCH_STEP_SMPL_REQD'), 'N') = 'Y' THEN
208
209
210 END IF; -- IF l_control_batch_step_ind = 'Y' then
211
212
213 ELSE -- IF GMD_SPEC_MATCH_GRP.FIND_WIP_SPEC(
214 IF (l_debug = 'Y') THEN
215 gmd_debug.put_line('WIP Spec does NOT exists ');
216 gmd_debug.put_line('p_resultout = F');
217 END IF;
218
219 p_resultout:='F';
220
221 END IF; -- IF GMD_SPEC_MATCH_GRP.FIND_WIP_SPEC(
222
223 -- changes end for Bug No.14033362
224
225 END LOOP ; -- open C2(p_recipeid) ;
226
227 CLOSE C2;
228
229
230 IF l_step_id is NULL THEN
231 p_resultout:='F';
232 end if ;
233
234 IF (l_debug = 'Y') THEN
235 gmd_debug.put_line('p_resultout ' || p_resultout);
236 END IF;
237
238
239 EXCEPTION
240 WHEN OTHERS THEN
241 p_resultout:='F';
242 GMD_API_PUB.Log_Message('GMD_GME_INT',
243 'PACKAGE','QM-GME QC status integration','ERROR', SUBSTR(SQLERRM,1,100));
244
245 raise;
246
247 END CHECK_QC;
248
249 END GMD_GME_INT ;