[Home] [Help]
PACKAGE BODY: APPS.GMD_GME_INT
Source
1 PACKAGE BODY GMD_GME_INT AS
2 /* $Header: GMDGMEIB.pls 120.4.12010000.2 2008/11/13 20:02:50 asatpute 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
50
51 Cursor C1(x_routing_id number,x_routing_step_id number , x_recipe_id number) is
52 SELECT C.OWNER_ORGANIZATION_ID, C.ROUTING_ID,
53 C.RECIPE_ID, C.FORMULA_ID,
54 C.ROUTING_ID
55 --MATL.ITEM_ID,
56 --MATL.FORMULALINE_ID
57 ,FM.OPRN_ID
58 ,FM.ROUTINGSTEP_ID
59 FROM GMD_RECIPES_B C,
60 FM_FORM_MST H,
61 fm_rout_dtl FM
62 --,FM_MATL_DTL MATL
63 WHERE
64 C.ROUTING_ID= x_routing_id AND
65 C.RECIPE_ID = x_recipe_id AND
66 FM.ROUTINGSTEP_ID= x_routing_step_id and
67 H.formula_id = C.Formula_id
68 AND FM.routing_id = C.routing_ID
69 --AND c.FORMULA_ID = MATL.FORMULA_ID
70 --AND h.FORMULA_ID = MATL.FORMULA_ID
71 ;
72
73 Cursor C2 (p_recipe_id number) is
74 select distinct md.inventory_item_id,md.revision ----RLNAGARA B5389806 Added revision
75 ,md.formulaline_id ----SMALLURU B6379386 Added formulaline_id
76 from fm_matl_dtl md, gmd_Recipes_b r
77 where r.recipe_id = p_recipe_id
78 and r.formula_id = md.formula_id
79 and md.line_type <> 2; -- no byproducts
80
81
82 BEGIN
83
84 --Bug 4523278. If profile GMD:Batch step sample required is set to No
85 --set p_resultout to 'F'(No Sample Required) and RETURN.
86 IF NVL(FND_PROFILE.VALUE('GMD_BATCH_STEP_SMPL_REQD'), 'Y') = 'N' THEN
87 p_resultout := 'F';
88 RETURN;
89 END IF;
90
91 IF (l_debug = 'Y') THEN
92 gmd_debug.log_initialize('GMDGMEInt');
93 END IF;
94
95 IF (l_debug = 'Y') THEN
96 gmd_debug.put_line('recipe ID ' || p_recipeid);
97 gmd_debug.put_line('routing ID ' || p_routingid);
98 gmd_debug.put_line('routing step ID ' || p_routingstepid);
99 END IF;
100
101 /* For a given recipe id, routing id, routingstepid combination */
102 OPEN C1(p_routingid,p_routingstepid,p_recipeid);
103 wf_log_pkg.string(6, 'Dummy','Before Fetching the values.');
104 Fetch C1 into L_ORGANIZATION_ID,L_ROUTING_ID,L_RECIPE_ID,
105 L_FORMULA_ID,L_ROUTING_ID,
106 --L_ITEM_ID,L_FORMULALINE_ID,
107 L_OPRN_ID,
108 l_routingstepid ;
109 CLOSE C1;
110
111 l_wip_spec.organization_id := p_organization_id;
112 l_wip_spec.batch_id := NULL;
113 l_wip_spec.recipe_id := L_recipe_id;
114 l_wip_spec.formula_id := L_formula_id;
115 l_wip_spec.formulaline_id := L_formulaline_id;
116 l_wip_spec.routing_id := L_routing_id;
117 l_wip_spec.step_id := l_routingstepid;
118 l_wip_spec.step_no := NULL;
119 l_wip_spec.oprn_id := L_oprn_id;
120 l_wip_spec.charge := NULL;
121 l_wip_spec.date_effective := SYSDATE;
122 l_wip_spec.exact_match := 'N';
123
124 /* Bug No.7032231 - Commented the following code as it is not supporting to get spec details */
125
126 /* IF (l_wip_spec.step_no IS NOT NULL or l_wip_spec.step_id IS NOT NULL) THEN
127 l_wip_spec.find_spec_with_step := 'Y';
128 ELSE
129 l_wip_spec.find_spec_with_step := 'N';
130 END IF; */
131
132
133 open C2(p_recipeid) ;
134 LOOP
135
136 Fetch C2 into l_inventory_item_id,l_revision ,l_formulaline_id;
137 exit when C2%notfound ;
138
139 l_wip_spec.inventory_item_id := l_inventory_item_id;
140 l_wip_spec.revision := l_revision; --RLNAGARA B5389806 Added revision
141 l_wip_spec.formulaline_id := l_formulaline_id; --Bug#6379386
142 l_step_id := l_routingstepid;
143
144 IF (l_debug = 'Y') THEN
145 gmd_debug.put_line('Checking if WIP Spec exists ');
146 END IF;
147
148 IF GMD_SPEC_MATCH_GRP.FIND_WIP_SPEC(
149 p_wip_spec_rec => l_wip_spec,
150 x_spec_id => l_spec_id,
151 x_spec_vr_id => l_spec_vr_id,
152 x_return_status => l_return_status,
153 x_message_data => l_msg_data) THEN
154
155 IF (l_debug = 'Y') THEN
156 gmd_debug.put_line('WIP Spec exists ');
157 END IF;
158
159 p_resultout:='S';
160 EXIT;
161 else
162 p_resultout:='F';
163 END IF;
164
165 END LOOP ;
166 CLOSE C2;
167
168
169 IF l_step_id is NULL THEN
170 p_resultout:='F';
171 end if ;
172
173 IF (l_debug = 'Y') THEN
174 gmd_debug.put_line('p_resultout ' || p_resultout);
175 END IF;
176
177
178 EXCEPTION
179 WHEN OTHERS THEN
180 p_resultout:='F';
181 GMD_API_PUB.Log_Message('GMD_GME_INT',
182 'PACKAGE','QM-GME QC status integration','ERROR', SUBSTR(SQLERRM,1,100));
183
184 raise;
185
186 END CHECK_QC;
187
188 END GMD_GME_INT ;