[Home] [Help]
PACKAGE BODY: APPS.GMO_DISP_CON_PKG
Source
1 PACKAGE BODY GMO_DISP_CON_PKG AS
2 /* $Header: GMOVDCPB.pls 120.8 2006/03/28 02:30 srpuri noship $ */
3 FUNCTION GET_HAZARD_CLASS_NAME(P_INVENTORY_ITEM_ID NUMBER,
4 P_ORGANIZATION_ID NUMBER)
5 RETURN VARCHAR2
6 IS
7 l_phc_hazard_class_name VARCHAR2(40);
8 CURSOR C_GET_PHC_CLASS IS
9 SELECT PHC.HAZARD_CLASS
10 FROM PO_HAZARD_CLASSES PHC, MTL_SYSTEM_ITEMS_VL MSI
11 WHERE MSI.INVENTORY_ITEM_ID= P_INVENTORY_ITEM_ID
12 and MSI.ORGANIZATION_ID = P_ORGANIZATION_ID
13 and MSI.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID (+)
14 and SYSDATE < (NVL(PHC.INACTIVE_DATE,sysdate));
15 BEGIN
16 OPEN C_GET_PHC_CLASS;
17 FETCH C_GET_PHC_CLASS INTO l_phc_hazard_class_name;
18 CLOSE C_GET_PHC_CLASS;
19 RETURN l_phc_hazard_class_name;
20 END GET_HAZARD_CLASS_NAME ;
21
22 PROCEDURE GENERATE_DISPDPCH_XML(ERRBUF OUT NOCOPY VARCHAR2,
23 RETCODE OUT NOCOPY VARCHAR2,
24 Plant IN NUMBER,
25 SubInventory IN VARCHAR2,
26 Batch IN NUMBER,
27 FromDate IN VARCHAR2,
28 ToDate IN VARCHAR2)
29 IS
30 TYPE refcur IS REF CURSOR;
31 l_refcur refcur;
32 l_result XMLType;
33 l_final_clob CLOB ;
34 l_len number;
35 l_xml_data varchar2(10);
36 l_limit number;
37 l_argument_string long ;
38 l_from_date varchar2(15);
39 l_to_date varchar2(15);
40 l_org_name VARCHAR2(4000);
41 l_org_code VARCHAR2(240);
42 CURSOR GET_ORG_NAME IS
43 SELECT hou.name ORGANIZATION_NAME , mp.organization_code ORGANIZATION_CODE
44 FROM hr_all_organization_units hou,
45 mtl_parameters mp
46 WHERE hou.organization_id = Plant
47 AND NVL(hou.date_to, SYSDATE+1) >= SYSDATE
48 and mp.organization_id = hou.organization_id;
49
50 BEGIN
51 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CP : GENERATE_XML(DISPDPCH) : START ');
52 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Input Parameter - Plant :'||Plant||' and Sub Inventory Code:'||SubInventory||' and Batch :'||Batch||' From Date :'||FromDate||' To Date :'||ToDate);
53
54 l_from_date := null;
55 l_to_date := null;
56
57 IF(FromDate IS NOT NULL) THEN
58 l_from_date := substr(FromDate,0,10);
59 END IF;
60
61 IF(ToDate IS NOT NULL) THEN
62 l_to_date := substr(ToDate,0,10);
63 END IF;
64 OPEN GET_ORG_NAME;
65 FETCH GET_ORG_NAME into l_org_name, l_org_code;
66 CLOSE GET_ORG_NAME;
67
68 l_argument_string := 'select XMLELEMENT("DispenseDispatch", XMLCONCAT(XMLSEQUENCETYPE(XMLTYPE(''<BatchNo>''||:x ||''</BatchNo>''),
69 XMLTYPE(''<PlantCode>'' || :y||''</PlantCode>''),
70 XMLTYPE(''<Subinventory_Code>'' || :z||''</Subinventory_Code>''),
71 XMLTYPE(''<FromDate>'' ||:xx||''</FromDate>''),
72 XMLTYPE(''<ToDate>''|| :yy ||''</ToDate>'')
73 )),
74 XMLAGG(XMLELEMENT("DispatchDetails",
75 XMLFOREST(:orgName as OrganizationName,
76 RES.subinventory_code as Location ,
77 RES.INVENTORY_ITEM_ID as ITEM_ID,
78 MSI.CONCATENATED_SEGMENTS as ITEM ,
79 MSI.description as Description ,
80 flYesNo.meaning AS HAZARDOUS_MATERIAL_FLAG,
81 decode(nvl(MSI.HAZARD_CLASS_ID,-1),-1,null,
82 GMO_DISP_CON_PKG.GET_HAZARD_CLASS_NAME (GMDL.ORGANIZATION_ID,
83 GMDL.INVENTORY_ITEM_ID)) AS HAZARD_CLASS,
84 RES.reservation_id as RESERVATION_ID ,
85 RES.LOT_NUMBER as LOT,
86 GBH.BATCH_NO as BatchNo,
87 GBSI.OPERATION as Operation,
88 GMO_DISPENSE_PVT.GET_PENDING_DISPENSE_QTY(RES.reservation_id,RES.inventory_item_id,
89 RES.ORGANIZATION_ID,conf.recipe_id,GMDL.MATERIAL_DETAIL_ID,res.primary_uom_code,
90 res.primary_reservation_quantity,GMDL.plan_qty,GMDL.dtl_um,
91 RES.LOT_NUMBER) REQUIREDQUANTITY,
92 conf.DISPENSE_UOM UOM ,
93 GBSI.BATCHSTEP_NO as BatchStepNo,
94 RES.primary_uom_code as PRIMARY_UOM ,
95 FND_DATE.DATE_TO_DISPLAYDT(GMDL.MATERIAL_REQUIREMENT_DATE, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) as REQUIREMENT_DATE)
96 ) order by GMDL.MATERIAL_REQUIREMENT_DATE desc
97 )
98 )
99 FROM
100 GME_BATCH_HEADER GBH,
101 GME_MATERIAL_DETAILS GMDL,
102 MTL_RESERVATIONS RES,
103 MTL_SYSTEM_ITEMS_VL MSI,
104 gmo_dispense_config conf,
105 gmo_dispense_config_inst conf_inst,
106 FND_LOOKUPS flYesNo,
107 (SELECT GBSI.MATERIAL_DETAIL_ID, GBSI.BATCHSTEP_ID,
108 GBS.BATCHSTEP_NO , GMO.OPRN_NO OPERATION
109 FROM
110 GME_BATCH_STEPS GBS,
111 GME_BATCH_STEP_ITEMS GBSI,
112 GMD_OPERATIONS GMO
113 WHERE GBS.BATCHSTEP_ID = GBSI.BATCHSTEP_ID
114 AND GBS.OPRN_ID = GMO.OPRN_ID) GBSI
115 WHERE RES.DEMAND_SOURCE_TYPE_ID=5
116 AND RES.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
117 AND RES.ORGANIZATION_ID = MSI.ORGANIZATION_ID
118 AND GBH.BATCH_ID = GMDL.BATCH_ID
119 AND GBH.BATCH_STATUS in (1,2)
120 AND RES.DEMAND_SOURCE_HEADER_ID = GBH.BATCH_ID
121 AND RES.DEMAND_SOURCE_LINE_ID = GMDL.MATERIAL_DETAIL_ID
122 AND conf_inst.ENTITY_KEY = GMDL.MATERIAL_DETAIL_ID
123 AND conf_inst.ENTITY_NAME = ''MATERIAL_DETAILS_ID''
124 AND conf_inst.DISPENSE_CONFIG_ID = conf.CONFIG_ID
125 AND GMDL.MATERIAL_DETAIL_ID = GBSI.MATERIAL_DETAIL_ID(+)
126 and flYesNo.LOOKUP_CODE = nvl(MSI.HAZARDOUS_MATERIAL_FLAG,''N'')
127 and flYesNo.LOOKUP_TYPE = ''GMO_YES_NO''
128 and gmdl.line_type = -1
129 and gme_api_grp.IS_RESERVATION_FULLY_SPECIFIED(res.reservation_id) = 1
130 and gmo_dispense_pvt.is_dispense_required(res.reservation_id, msi.inventory_item_id,
131 gbh.organization_id, null, GMDL.MATERIAL_DETAIL_ID, res.primary_reservation_quantity,
132 res.primary_uom_code,gmdl.plan_qty,gmdl.dtl_um, res.lot_number ) = ''T''
133 AND GBH.organization_id =:a ';
134
135 -- Add 1=1 conditions if parameters are missing so that USING clause
136 -- always has static number of parameters to bind.
137 IF (Batch IS NOT NULL) THEN
138 l_argument_string := l_argument_string || ' and GMDL.batch_id = :b ';
139 ELSE
140 l_argument_string := l_argument_string || ' and 1 = :b ';
141 END IF;
142 IF(SubInventory IS NOT NULL) THEN
143 l_argument_string := l_argument_string || ' and RES.subinventory_code = :c ';
144 ELSE
145 l_argument_string := l_argument_string || ' and 1 = :c ';
146 END IF;
147 IF(l_from_date IS NOT NULL) THEN
148 l_argument_string := l_argument_string || ' and TRUNC(RES.requirement_date) >=TO_DATE( :d , ''YYYY/MM/DD'')';
149 ELSE
150 l_argument_string := l_argument_string || ' and 1 = :d ';
151 END IF;
152 IF(l_to_date IS NOT NULL) THEN
153 l_argument_string := l_argument_string || ' and TRUNC(RES.requirement_date) <=TO_DATE( :e , ''YYYY/MM/DD'')';
154 ELSE
155 l_argument_string := l_argument_string || ' and 1 = :e ';
156 END IF;
157
158 FND_FILE.put_line(FND_FILE.LOG,'SQL Query'||l_argument_string);
159
160 -- execute the Query
161 -- instead of execute immediate, use cursor first and then fetch into result.
162 open l_refcur FOR l_argument_string USING Batch, l_org_code, SubInventory, l_from_date, l_to_date,l_org_name,Plant, nvl(Batch,1), nvl(SubInventory,1), nvl(l_from_date, 1), nvl(l_to_date,1);
163 fetch l_refcur INTO l_result;
164 close l_refcur;
165
166 --EXECUTE IMMEDIATE l_argument_string USING l_var_list ;
167
168 -- write the XML into out file
169 l_limit:= 1;
170 l_final_clob := l_result.getClobVal();
171 l_len := DBMS_LOB.GETLENGTH (l_final_clob);
172 FND_FILE.PUT(FND_FILE.LOG, 'Size'||l_len);
173 LOOP
174 IF l_len > l_limit THEN
175 l_xml_data := DBMS_LOB.SUBSTR (l_final_clob,10,l_limit);
176 FND_FILE.PUT(FND_FILE.OUTPUT,l_xml_data);
177 FND_FILE.PUT(FND_FILE.LOG,l_xml_data);
178 l_xml_data := NULL;
179 l_limit:= l_limit + 10;
180 ELSE
181 l_xml_data := DBMS_LOB.SUBSTR (l_final_clob,10,l_limit);
182 FND_FILE.PUT(FND_FILE.OUTPUT, l_xml_data);
183 FND_FILE.PUT(FND_FILE.LOG,l_xml_data);
184 l_xml_data := NULL;
185 EXIT;
186 END IF;
187 END LOOP;
188 fnd_file.put_line(FND_FILE.LOG, 'CP : GENERATE_XML(DISPDPCH) : FINISH ');
189 EXCEPTION
190 WHEN OTHERS THEN
191 fnd_file.put_line(fnd_file.log,SQLERRM);
192 fnd_file.new_line(fnd_file.log,2);
193 fnd_file.put_line(fnd_file.log, fnd_message.get_string('GMO', 'GMO_DISPENSE_DISPATCH_XML_ERR') );
194 END GENERATE_DISPDPCH_XML;
195
196
197 --Generate Dispense History XML
198 PROCEDURE GENERATE_DISPHIST_XML(ERRBUF OUT NOCOPY VARCHAR2,
199 RETCODE OUT NOCOPY VARCHAR2,
200 Plant IN NUMBER,
201 SubInventory IN VARCHAR2,
202 Batch IN NUMBER,
203 FromDate IN VARCHAR2,
204 ToDate IN VARCHAR2,
205 OperatorID IN VARCHAR2)
206 IS
207 TYPE refcur IS REF CURSOR;
208 l_refcur refcur;
209 l_result XMLType;
210 l_final_clob CLOB ;
211 l_len number;
212 l_xml_data varchar2(10);
213 l_limit number;
214 l_argument_string long ;
215 l_from_date varchar2(15);
216 l_to_date varchar2(15);
217 l_org_name VARCHAR2(4000);
218 l_org_code VARCHAR2(240);
219 CURSOR GET_ORG_NAME IS
220 SELECT hou.name ORGANIZATION_NAME , mp.organization_code ORGANIZATION_CODE
221 FROM hr_all_organization_units hou,
222 mtl_parameters mp
223 WHERE hou.organization_id = Plant
224 AND NVL(hou.date_to, SYSDATE+1) >= SYSDATE
225 and mp.organization_id = hou.organization_id;
226
227 BEGIN
228 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CP : GENERATE_XML(DISHIST) : START ');
229 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Input Parameter - Plant :'||Plant||' and Sub Inventory Code :'||SubInventory||' and Batch :'||Batch||' From Date :'||FromDate||' To Date :'||ToDate||' Operation :'||OperatorID);
230 l_from_date := null;
231 l_to_date := null;
232
233 IF(FromDate IS NOT NULL) THEN
234 l_from_date := substr(FromDate,0,10);
235 END IF;
236 IF(ToDate IS NOT NULL) THEN
237 l_to_date := substr(ToDate,0,10);
238 END IF;
239 OPEN GET_ORG_NAME;
240 FETCH GET_ORG_NAME into l_org_name, l_org_code;
241 CLOSE GET_ORG_NAME;
242
243 l_argument_string := 'select XMLELEMENT("DispenseHistory", XMLCONCAT(XMLSEQUENCETYPE(XMLTYPE(''<BatchNo>''||:x||''</BatchNo>''),
244 XMLTYPE(''<PlantCode>''||:y||''</PlantCode>''),
245 XMLTYPE(''<Subinventory_Code>''||:z||''</Subinventory_Code>''),
246 XMLTYPE(''<FromDate>''||:xx||''</FromDate>''),
247 XMLTYPE(''<ToDate>''||:yy||''</ToDate>''),
248 XMLTYPE(''<Operator>''||:zz||''</Operator>'')
249 )),
250 XMLAGG(XMLELEMENT("DispenseHistoryDetails",
251 XMLFOREST(:orgName as OrganizationName ,
252 GMDL.subinventory_code as Location ,
253 GMDL.inventory_item_id as Item_id ,
254 MSI.CONCATENATED_SEGMENTS as Item ,
255 MSI.description as Description ,
256 GMDL.dispense_number DISPENSE_NO ,
257 GMDL.lot_number as Lot,
258 gbh.batch_no as BatchNo,
259 gbsi.batchstep_no as BatchStepNo ,
260 GMD.LINE_NO as LINE_NO ,
261 gmo_dispense_pvt.get_net_disp_dispensed_qty(GMDL.dispense_id) as NetDispensedQuantity ,
262 FND_DATE.DATE_TO_DISPLAYDT(GMDL.dispensed_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) as DispensedDate ,
263 GMDL.required_qty as RequiredQuantity ,
264 lkup.meaning as DispensingMode,
265 GMDL.dispense_uom DispensedUOM ,
266 GMDL.erecord_id ErecordID,
267 gbsi.oprn_no as Operation,
268 (select nvl(sum(nvl(undispensed_qty,0)),0) from gmo_material_undispenses where dispense_id = GMDL.dispense_id and GMDL.material_status <> ''REVDISPONLY'') as reverse_dispensed_qty ,
269 (select nvl(sum(material_loss),0) from gmo_material_undispenses where dispense_id = GMDL.dispense_id and GMDL.material_status <> ''REVDISPONLY'') as Material_loss,
270 GMO_UTILITIES.GET_USER_DISPLAY_NAME(GMDL.created_by) Operator)
271 ) order by GMDL.dispensed_date desc
272 )
273 )
274 from
275 GMO_MATERIAL_DISPENSES GMDL,
276 GME_BATCH_HEADER gbh ,
277 MTL_SYSTEM_ITEMS_VL MSI,
278 Gme_Material_details GMD,
279 fnd_lookups lkup,
280 (SELECT GBS.BATCHSTEP_ID,
281 GBS.BATCHSTEP_NO ,
282 GMDOP.OPRN_NO ,
283 GBSI.MATERIAL_DETAIL_ID
284 FROM
285 GME_BATCH_STEPS GBS,
286 GME_BATCH_STEP_ITEMS GBSI,
287 GMD_OPERATIONS GMDOP
288 WHERE GBS.BATCHSTEP_ID = GBSI.BATCHSTEP_ID
289 AND GBS.OPRN_ID = GMDOP.OPRN_ID) GBSI
290 where gbh.batch_id = GMDL.batch_id
291 and gbh.organization_id = GMDL.organization_id
292 and GMDL.batch_step_id = GBSI.BATCHSTEP_ID(+)
293 and GMDL.material_detail_id = gbsi.material_detail_id (+)
294 and MSI.inventory_item_id = GMDL.inventory_item_id
295 and MSI.organization_id = GMDL.organization_id
296 and gmd.MATERIAL_DETAIL_ID = GMDL.MATERIAL_DETAIL_ID
297 and lkup.lookup_type = ''GMO_DISPENSE_MODE''
298 and lkup.lookup_code = GMDL.dispensing_mode
299 AND gbh.organization_id =:a';
300
301 -- Add 1=1 conditions if parameters are missing so that USING clause
302 -- always has static number of parameters to bind.
303
304 IF (Batch IS NOT NULL) THEN
305 l_argument_string := l_argument_string || ' and GMDL.batch_id =:b';
306 ELSE
307 l_argument_string := l_argument_string || ' and 1 =:b';
308 END IF;
309 IF(SubInventory IS NOT NULL ) THEN
310 l_argument_string := l_argument_string || ' and GMDL.subinventory_code =:c';
311 ELSE
312 l_argument_string := l_argument_string || ' and 1 =:c';
313 END IF;
314 IF(l_from_date IS NOT NULL) THEN
315 l_argument_string := l_argument_string || ' and TRUNC(GMDL.dispensed_date) >=TO_DATE( :d , ''YYYY/MM/DD'')';
316 else
317 l_argument_string := l_argument_string || ' and 1 = :d ';
318 END IF;
319 IF(l_to_date IS NOT NULL) THEN
320 l_argument_string := l_argument_string || ' and TRUNC(GMDL.dispensed_date) <=TO_DATE( :e , ''YYYY/MM/DD'')';
321 else
322 l_argument_string := l_argument_string || ' and 1 = :e ';
323 END IF;
324 IF(OperatorID IS NOT NULL) THEN
325 l_argument_string := l_argument_string || ' and GMDL.created_by = :f';
326 ELSE
327 l_argument_string := l_argument_string || ' and 1 = :f';
328 END IF;
329
330
331 FND_FILE.put_line(FND_FILE.LOG,'SQL Query'||l_argument_string);
332
333 -- execute the Query
334 -- EXECUTE IMMEDIATE l_argument_string INTO l_result;
335 -- execute the Query
336 -- instead of execute immediate, use cursor first and then fetch into result.
337
338 open l_refcur FOR l_argument_string USING Batch, l_org_code, SubInventory, l_from_date, l_to_date,OperatorID,l_org_name,Plant, nvl(Batch,1), nvl(SubInventory,1), nvl(l_from_date, 1), nvl(l_to_date,1),nvl(OperatorID,1);
339 fetch l_refcur INTO l_result;
340 close l_refcur;
341
342
343 -- write the XML into out file
344 l_limit:= 1;
345 l_final_clob := l_result.getClobVal();
346 l_len := DBMS_LOB.GETLENGTH (l_final_clob);
347 FND_FILE.PUT(FND_FILE.LOG, 'Size'||l_len);
348 LOOP
349 IF l_len > l_limit THEN
350 l_xml_data := DBMS_LOB.SUBSTR (l_final_clob,10,l_limit);
351 FND_FILE.PUT(FND_FILE.OUTPUT,l_xml_data);
352 FND_FILE.PUT(FND_FILE.LOG,l_xml_data);
353 l_xml_data := NULL;
354 l_limit:= l_limit + 10;
355 ELSE
356 l_xml_data := DBMS_LOB.SUBSTR (l_final_clob,10,l_limit);
357 FND_FILE.PUT(FND_FILE.OUTPUT, l_xml_data);
358 FND_FILE.PUT(FND_FILE.LOG,l_xml_data);
359 l_xml_data := NULL;
360 EXIT;
361 END IF;
362 END LOOP;
363 fnd_file.put_line(FND_FILE.LOG, 'CP : GENERATE_XML(DISHIST) : FINISH ');
364 EXCEPTION
365 WHEN OTHERS THEN
366 fnd_file.put_line(fnd_file.log,SQLERRM);
367 fnd_file.new_line(fnd_file.log,2);
368 fnd_file.put_line(fnd_file.log, fnd_message.get_string('GMO', 'GMO_DISPENSE_HISTORY_XML_ERR') );
369 RETURN;
370 END GENERATE_DISPHIST_XML;
371 END GMO_DISP_CON_PKG;