ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN AF8673B1195D41FC9320658AAA270802Y select x.organization_code, x.collection_plan, x.plan_description, x.item, x.item_description, x.revision, x.lot_number, x.serial_number, x.subinventory, x.quantity, x.uom, x.job, x.po_number, x.supplier, x.customer, x.characteristic, x.element_name, x.element_datatype, x.element_uom, x.result, case when x.datatype=2 and regexp_like(x.result,'^[+-]?([0-9]+(\.[0-9]*)?|\.[0-9]+)$') then fnd_number.canonical_to_number(x.result) end result_number, x.result_date, x.transaction_date, x.entered_by, x.collection_id, x.occurrence, x.element_sequence from ( select mp.organization_code, qp.name collection_plan, qp.description plan_description, msiv.concatenated_segments item, msiv.description item_description, qr.revision, qr.lot_number, qr.serial_number, qr.subinventory, qr.quantity, qr.uom, (select we.wip_entity_name from wip_entities we where qr.wip_entity_id=we.wip_entity_id) job, (select pha.segment1 from po_headers_all pha where qr.po_header_id=pha.po_header_id) po_number, (select pv.vendor_name from po_vendors pv where qr.vendor_id=pv.vendor_id) supplier, (select hp.party_name from hz_cust_accounts hca, hz_parties hp where qr.customer_id=hca.cust_account_id and hca.party_id=hp.party_id) customer, nvl(qpc.prompt,qc.prompt) characteristic, qc.name element_name, xxen_util.meaning(to_char(qc.datatype),'QA_CHAR_DATATYPE',700) element_datatype, nvl(qpc.uom_code,qc.uom_code) element_uom, qc.datatype, case when qpc.result_column_name like 'CHARACTER%' then decode(to_number(substr(qpc.result_column_name,10)) ,1,qr.character1,2,qr.character2,3,qr.character3,4,qr.character4,5,qr.character5,6,qr.character6,7,qr.character7,8,qr.character8 ,9,qr.character9,10,qr.character10,11,qr.character11,12,qr.character12,13,qr.character13,14,qr.character14,15,qr.character15,16,qr.character16 ,17,qr.character17,18,qr.character18,19,qr.character19,20,qr.character20,21,qr.character21,22,qr.character22,23,qr.character23,24,qr.character24 ,25,qr.character25,26,qr.character26,27,qr.character27,28,qr.character28,29,qr.character29,30,qr.character30,31,qr.character31,32,qr.character32 ,33,qr.character33,34,qr.character34,35,qr.character35,36,qr.character36,37,qr.character37,38,qr.character38,39,qr.character39,40,qr.character40 ,41,qr.character41,42,qr.character42,43,qr.character43,44,qr.character44,45,qr.character45,46,qr.character46,47,qr.character47,48,qr.character48 ,49,qr.character49,50,qr.character50,51,qr.character51,52,qr.character52,53,qr.character53,54,qr.character54,55,qr.character55,56,qr.character56 ,57,qr.character57,58,qr.character58,59,qr.character59,60,qr.character60,61,qr.character61,62,qr.character62,63,qr.character63,64,qr.character64 ,65,qr.character65,66,qr.character66,67,qr.character67,68,qr.character68,69,qr.character69,70,qr.character70,71,qr.character71,72,qr.character72 ,73,qr.character73,74,qr.character74,75,qr.character75,76,qr.character76,77,qr.character77,78,qr.character78,79,qr.character79,80,qr.character80 ,81,qr.character81,82,qr.character82,83,qr.character83,84,qr.character84,85,qr.character85,86,qr.character86,87,qr.character87,88,qr.character88 ,89,qr.character89,90,qr.character90,91,qr.character91,92,qr.character92,93,qr.character93,94,qr.character94,95,qr.character95,96,qr.character96 ,97,qr.character97,98,qr.character98,99,qr.character99,100,qr.character100) when qpc.result_column_name like 'COMMENT%' then decode(to_number(substr(qpc.result_column_name,8)) ,1,qr.comment1,2,qr.comment2,3,qr.comment3,4,qr.comment4,5,qr.comment5) when qpc.result_column_name like 'SEQUENCE%' then decode(to_number(substr(qpc.result_column_name,9)) ,1,qr.sequence1,2,qr.sequence2,3,qr.sequence3,4,qr.sequence4,5,qr.sequence5,6,qr.sequence6,7,qr.sequence7,8,qr.sequence8 ,9,qr.sequence9,10,qr.sequence10,11,qr.sequence11,12,qr.sequence12,13,qr.sequence13,14,qr.sequence14,15,qr.sequence15) end result, xxen_util.client_time(qr.creation_date) result_date, xxen_util.client_time(qr.transaction_date) transaction_date, xxen_util.user_name(qr.created_by) entered_by, qr.collection_id, qr.occurrence, qpc.prompt_sequence element_sequence from qa_results qr, qa_plans qp, qa_plan_chars qpc, qa_chars qc, mtl_parameters mp, mtl_system_items_vl msiv where qr.plan_id=qp.plan_id and qr.plan_id=qpc.plan_id and qpc.char_id=qc.char_id and qpc.enabled_flag=1 and (qpc.result_column_name like 'CHARACTER%' or qpc.result_column_name like 'COMMENT%' or qpc.result_column_name like 'SEQUENCE%') and qr.organization_id=mp.organization_id(+) and qr.item_id=msiv.inventory_item_id(+) and qr.organization_id=msiv.organization_id(+) and qr.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and 1=1 ) x where 2=2 order by x.organization_code, x.collection_plan, x.collection_id, x.occurrence, x.element_sequence New report. Oracle Quality had no Blitz Report covering collection results, only the collection plan setup reports, and the Oracle standard Quality Results Report cannot serve the requirement: QLTRSLTR takes a single saved report criteria, which pins one collection plan per run, and its data template is a fixed wide layout of at most thirty element columns. This report returns results across every collection plan matching a name pattern, in long format with one row per result and characteristic, which keeps a single extract valid no matter how many different elements the matched plans carry. Results are read from qa_results and unpivoted through qa_plan_chars.result_column_name, which maps each enabled plan element to the generic column holding its value. The user entered elements are covered, being the plan's own characteristics plus its comment and sequence elements. The context elements are exposed as columns instead of characteristic rows, because those hold internal ids that need dereferencing per element and are more useful as filters. Inventory organization access is enforced in the main SQL through org_access_view, so a blank Organization parameter cannot return results from organizations the responsibility has no access to. The supplier lookup reads po_vendors rather than ap_suppliers because po_vendors exists on both releases, so the definition runs unchanged on EBS 11i and needs no 11i variant, and every other object it touches was confirmed present there. :collection_plan is not null or :collection_plan_like is not null or :result_date_from is not null or :item is not null or :lot_number is not null AR QA تقرير نتائج الجودة Application: الجودة Source: تقرير نتائج الجودة (XML) Short Name: QLTRSLTR_XML DB package: QA_QLTRSLTR_XMLP_PKG D QA Qualitätsergebnisse Application: Qualität Source: Qualitätsergebnisse (XML) Short Name: QLTRSLTR_XML DB package: QA_QLTRSLTR_XMLP_PKG F QA Etat Résultats qualité Application: Quality Source: Etat Résultats qualité (XML) Short Name: QLTRSLTR_XML DB package: QA_QLTRSLTR_XMLP_PKG US QA Quality Results One row per collection result and characteristic, so results from any number of collection plans stack in a single extract instead of one column per characteristic. Use Collection Plan like to pull a whole family of plans in one run, for example INC_MRC%. Oracle Quality stores every element value as a string in a generic column of qa_results, and the plan's element definition decides which column holds which characteristic. Result is therefore text. Result Number carries the numeric conversion for Number elements so the values can be charted or averaged, and Date elements arrive as YYYY/MM/DD. Only the elements an operator fills in are listed as characteristic rows. The context elements a plan collects, being item, revision, lot, serial, subinventory, quantity, job, purchase order, supplier and customer, are columns on every row instead, so they can be filtered and pivoted without appearing as results. Result Date is when the result was recorded. Exclude Empty Results defaults to Yes, so clear it to also list elements that are enabled on the plan but were left blank. ZHS QA 质量结果报表 Application: 质量管理系统 Source: 质量结果报表 (XML) Short Name: QLTRSLTR_XML DB package: QA_QLTRSLTR_XMLP_PKG Enginatics 1=1 2=2 1 10 1=1 mp.organization_code=:organization LOV INV Organization Code (including master) 0B22DF18E239D64AE0630100007FBD85 Y select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate order by ood.organization_code xxen_util.previous_parameter_value(:parameter_id) AR اسم التقرير D Auswertungsbezeichnung F Nom de l'état US Organization ZHS 报表名称 2 20 1=1 qp.name=:collection_plan LOV QA Collection Plan 52162B9B8C221F1DE0630100007FADC0 Y select qp.name value, qp.description from qa_plans qp, mtl_parameters mp where qp.organization_id=mp.organization_id(+) and (:$flex$.Organization is null or xxen_util.contains(:$flex$.Organization,mp.organization_code)='Y') and (qp.organization_id=0 or qp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)) order by qp.name US Collection Plan 3 30 1=1 qp.name like :collection_plan_like Char US Collection Plan like 4 40 1=1 qr.creation_date>=:result_date_from Date US Result Date From 5 50 1=1 qr.creation_date<:result_date_to+1 Date last_day(:$flex$.result_date_from) US Result Date To 6 60 1=1 qc.name=:collection_element LOV QA Collection Element 58F7B0E8A74E1DC4E0630100007F880E Y select distinct qc.name value, qc.prompt description from qa_plans qp, mtl_parameters mp, qa_plan_chars qpc, qa_chars qc where qp.organization_id=mp.organization_id(+) and qp.plan_id=qpc.plan_id and qpc.char_id=qc.char_id and (:$flex$.Organization is null or xxen_util.contains(:$flex$.Organization,mp.organization_code)='Y') and (:$flex$.Collection_Plan is null or xxen_util.contains(:$flex$.Collection_Plan,qp.name)='Y') and (qp.organization_id=0 or qp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)) order by qc.name US Collection Element 7 70 1=1 msiv.concatenated_segments=:item LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments US Item 8 80 1=1 qr.lot_number=:lot_number Char US Lot Number 9 90 2=2 x.result is not null LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y xxen_util.meaning('Y','YES_NO',0) US Exclude Empty Results