ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E777361CAD224C79E0530100007FBB44Y select nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, acct_dist.organization_code Org_Code, oap.period_name Period_Name, &segment_columns msiv.concatenated_segments Assembly_Number, msiv.description Assembly_Description, -- Revision for version 1.13 fcl.meaning item_type, -- Revision for version 1.9 &category_columns -- End revision for version 1.9 ml1.meaning Accounting_Line_Type, ml2.meaning Transaction_Type, -- Revision for version 1.18 acct_dist.transaction_id Transaction_Id, &p_show_wip_osp acct_dist.move_transaction_id Move_Transaction_Id, acct_dist.completion_transaction_id Completion_Transaction_Id, acct_dist.transaction_date Transaction_Date, acct_dist.creation_date Creation_Date, fu.user_name Created_By, acct_dist.transaction_reference, -- End revision for version 1.18 wac.class_code WIP_Class, ml3.meaning Class_Type, -- Revision for version 1.13 ml4.meaning WIP_Type, -- Revision for version 1.17 &p_show_project we.wip_entity_name WIP_Job, we.description Description, -- Revision for version 1.18 decode(ml4.lookup_code, 4, ml9.meaning, ml8.meaning) WIP_Status, -- Revision for version 1.15 acct_dist.operation_seq_num Operation_Seq_Number, acct_dist.resource_seq_num Resource_Operation_Seq, bd.department_code WIP_Department, -- Revision for version 1.18 regexp_replace(bd.description,'[^[:alnum:]'' '']', null) Department_Description, br.resource_code WIP_Resource, ml5.meaning Autocharge_Type, ml6.meaning Standard_Rate, -- End revision for version 1.18 &p_show_wip_osp2 -- End revision for version 1.17 -- Revision for version 1.18 acct_dist.actual_resource_rate, acct_dist.standard_resource_rate, acct_dist.rate_or_amount Resource_Cost, -- Revision for version 1.23 ml7.meaning Basis_Type, &p_show_wip_ovhds ca.activity Activity, -- End revision for version 1.18 -- Revision for version 1.11, 1.12 and 1.16 (select nvl(muomv.uom_code, br.unit_of_measure) from bom_resources br, mtl_units_of_measure_vl muomv where acct_dist.resource_id = br.resource_id and muomv.uom_code (+) = br.unit_of_measure ) UOM_Code, round(nvl(acct_dist.primary_quantity,0),3) Primary_Quantity, -- End revision for version 1.11 cce.cost_element Cost_Element, gl.currency_code Currency_Code, acct_dist.base_transaction_value Amount from wip_entities we, wip_accounting_classes wac, mtl_system_items_vl msiv, org_acct_periods oap, gl_code_combinations gcc, mfg_lookups ml1, -- CST_ACCOUNTING_LINE_TYPE mfg_lookups ml2, -- WIP_TRANSACTION_TYPE_SHORT mfg_lookups ml3, -- WIP_CLASS_TYPE -- Revision for version 1.13 mfg_lookups ml4, -- WIP_ENTITY -- Revision for version 1.10 fnd_common_lookups fcl, -- Revision for version 1.18 bom_resources br, -- Revision for version 1.20 -- bom_resources br_basis, bom_departments bd, cst_activities ca, cst_cost_elements cce, fnd_user fu, mfg_lookups ml5, -- BOM_AUTOCHARGE_TYPE mfg_lookups ml6, -- Standard Rate Flag mfg_lookups ml7, -- CST_BASIS mfg_lookups ml8, -- WIP Job Status mfg_lookups ml9, -- WIP Flow Schedule Status -- End revision for version 1.18 hr_organization_information hoi, hr_all_organization_units_vl haou, -- inv_organization_id hr_all_organization_units_vl haou2, -- operating unit gl_ledgers gl, -- Revision for version 1.18 &project_tables &wip_osp_tables &wip_ovhd_tables &wip_sla_tables (select mp.organization_code, mp.organization_id, wt.acct_period_id, wta.reference_account, nvl(wdj.primary_item_id, wfs.primary_item_id) primary_item_id, wta.accounting_line_type, wt.transaction_type, -- Revision for version 1.17 wt.transaction_id, wt.rcv_transaction_id, wt.move_transaction_id, wt.completion_transaction_id, wt.transaction_date, wt.creation_date, wt.created_by, -- Release 12.2 wt.reason_code, -- Revision for version 1.21 -- regexp_replace(wt.reference,'[^[:alnum:]'' '']', null) transaction_reference, decode(wt.transaction_type, 4, (select description -- Std cost update from cst_cost_updates ccu where ccu.cost_update_id = wt.cost_update_id), regexp_replace(wt.reference,'[^[:alnum:]'' '']', null) ) transaction_reference, -- End revision for version 1.21 -- End revision for version 1.17 nvl(wdj.class_code, wfs.class_code) class_code, nvl(wdj.wip_entity_id, wfs.wip_entity_id) wip_entity_id, -- Revision for version 1.18 nvl(wdj.status_type, wfs.status) status_type, wta.resource_id, wt.po_header_id, wt.po_line_id, wt.cost_update_id, -- Revision for version 1.17 -- Release 12.2 wt.resource_instance, -- Revision for version 1.15 wt.department_id, -- Revision for version 1.19 wt.project_id, -- Revision for version 1.17 wt.task_id, -- Revision for version 1.19 wt.pm_cost_collected, wt.operation_seq_num, wt.resource_seq_num, wt.autocharge_type, wt.standard_rate_flag, -- Release 12.2 wt.usage_rate_of_amount, wt.actual_resource_rate, wt.standard_resource_rate, wta.rate_or_amount, wta.basis_type, wta.currency_code, wta.currency_conversion_date, wta.currency_conversion_rate, wta.overhead_basis_factor, wta.basis_resource_id, wt.activity_id, -- End revision for version 1.17 wt.transaction_uom, wt.primary_uom, wta.primary_quantity, wta.cost_element_id, wta.base_transaction_value, wta.wip_sub_ledger_id from wip_transaction_accounts wta, wip_transactions wt, wip_discrete_jobs wdj, -- Revision for version 1.18 wip_flow_schedules wfs, mtl_parameters mp -- End revision for version 1.18 -- ======================================================== -- WIP Transaction, org and item joins -- ======================================================== where wt.transaction_id = wta.transaction_id -- Revision for version 1.18 and mp.organization_id = wta.organization_id and wdj.wip_entity_id (+) = wta.wip_entity_id and wdj.organization_id (+) = wta.organization_id and wfs.wip_entity_id (+) = wta.wip_entity_id and wfs.organization_id (+) = wta.organization_id -- Revision for version 1.21 and mp.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) -- End revision for version 1.18 -- Revision for version 1.17, do not show zero job and flow schedule close variances -- and (wt.transaction_type <> 6 and wta.base_transaction_value <> 0) and 2=2 -- p_org_code, p_trx_date_from, p_trx_date_to, p_wip_job, p_minimum_amount ) acct_dist -- ======================================================== -- WIP Transaction, org and item joins -- ======================================================== where msiv.organization_id = acct_dist.organization_id and msiv.inventory_item_id = acct_dist.primary_item_id -- Revision for version 1.15 and we.wip_entity_id = acct_dist.wip_entity_id and bd.department_id (+) = acct_dist.department_id and br.resource_id (+) = acct_dist.resource_id -- Revision for version 1.20 -- and br_basis.resource_id (+) = acct_dist.basis_resource_id and ca.activity_id (+) = acct_dist.activity_id and cce.cost_element_id (+) = acct_dist.cost_element_id and fu.user_id (+) = acct_dist.created_by and wac.class_code (+) = acct_dist.class_code -- Revision for version 1.2 and wac.organization_id (+) = acct_dist.organization_id -- ======================================================== -- Dynamic SQL joins -- ======================================================== &project_table_joins &osp_wip_table_joins &wip_ovhd_table_joins -- ======================================================== -- Inventory Org accounting period joins -- ======================================================== and oap.acct_period_id = acct_dist.acct_period_id and oap.organization_id = acct_dist.organization_id -- ======================================================== -- Version 1.3, added lookup values to see more detail -- ======================================================== and ml1.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' and ml1.lookup_code = acct_dist.accounting_line_type and ml2.lookup_type = 'WIP_TRANSACTION_TYPE_SHORT' and ml2.lookup_code = acct_dist.transaction_type and ml3.lookup_type = 'WIP_CLASS_TYPE' and ml3.lookup_code = wac.class_type -- Revision for version 1.13 and ml4.lookup_type = 'WIP_ENTITY' and ml4.lookup_code = we.entity_type -- Revision for version 1.18 and ml5.lookup_type (+) = 'BOM_AUTOCHARGE_TYPE' and ml5.lookup_code (+) = acct_dist.autocharge_type and ml6.lookup_type (+) = 'SYS_YES_NO' and ml6.lookup_code (+) = to_char(acct_dist.standard_rate_flag) and ml7.lookup_type (+) = 'CST_BASIS' and ml7.lookup_code (+) = acct_dist.basis_type and ml8.lookup_type (+) = 'WIP_JOB_STATUS' and ml8.lookup_code (+) = acct_dist.status_type and ml9.lookup_type (+) = 'WIP_FLOW_SCHEDULE_STATUS' and ml9.lookup_code (+) = acct_dist.status_type -- End revision for version 1.18 -- Version 1.10, added Item Type lookup values and fcl.lookup_code (+) = msiv.item_type and fcl.lookup_type (+) = 'ITEM_TYPE' -- End revision for version 1.10 -- ======================================================== -- using the base tables to avoid using -- org_organization_definitions and hr_operating_units -- ======================================================== and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = acct_dist.organization_id and hoi.organization_id = haou.organization_id -- this gets the organization name and haou2.organization_id = to_number(hoi.org_information3) -- this gets the operating unit id and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id and 1=1 -- p_assy_number, p_operating_unit, p_ledger -- ======================================================== -- Revision for version 1.17 and 1.18, SLA and Non-SLA joins. -- ======================================================== &wip_sla_table_joins &wip_non_sla_table_joins -- ========================================================== -- End revisions for version 1.17 and 1.18 order by -- Fix for version 1.10 -- 1,3,4,5,6,7,8,9,10,12,13 nvl(gl.short_name, gl.name), haou2.name, acct_dist.organization_code, oap.period_name, &segment_columns_grp msiv.concatenated_segments, ml1.meaning, -- Accounting_Line_Type ml2.meaning, -- Transaction_Type wac.class_code, -- WIP_Class -- Revision for version 1.13 ml4.meaning -- WIP Entity Type &order_by_project ,we.wip_entity_name ,br.resource_code &order_by_wip_osp &order_by_wip_ovhds ,msiv.organization_id (:Accounting_Line_Type is not null or :WIP_Transaction_Type is not null or :Minimum_Absolute_Amount is not null or :Resource_Code is not null or :Department is not null or :Class_Code is not null or :WIP_Job_or_Flow_Schedule is not null or :Assembly_Number is not null) and (:Organization_Code is not null) US CAC WIP Account Detail Report to get the WIP accounting distributions, in summary, by WIP job, resource, overhead and WIP cost update. With the Show SLA Accounting parameter you can choose to use the Release 12 Subledger Accounting (Create Accounting) account setups by selecting Yes. And if you have not modified your SLA accounting rules, select No to allow this report to run a bit faster. With parameters to limit the number of report columns, Show Project to display or not display the project number and name, Show WIP Job to display or not display the WIP job (WIP job, description and resource codes), Show WIP Outside Processing to display or not display the outside processing information (WIP OSP item number, supplier, purchase order, purchase order line and release) and Show Overheads to display or not display the overhead codes. For Discrete, Flow and Workorderless WIP (but not Repetitive Schedules). Note that both Flow and Workorderless show up as the WIP Type "Flow schedule". And WIP Standard Cost Update descriptions show up in the Transaction Reference column. Parameters: =========== Transaction Date From: enter the starting transaction date (mandatory). Transaction Date To: enter the ending transaction date (mandatory). Show SLA Accounting: enter Yes to use the Subledger Accounting rules for your accounting information (mandatory). If you choose No the report uses the pre-Create Accounting entries. Show Projects: display the project number and name. Enter Yes or No, use to control the reported columns. (mandatory). Show WIP Outside Processing: display the WIP OSP item number, supplier, purchase order, purchase order line and release. Enter Yes or No, use to control the reported columns (mandatory). Show WIP Overheads: display the earned WIP production overheads, including the Overhead Code and Resource Basis Amount. Enter Yes or No, use to control the reported columns (mandatory). Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional). Category Set 2: any item category you wish, typically the Inventory category set (optional). Accounting Line Type: enter the accounting purpose or line type to report (optional). WIP Transaction Type: enter the transaction type to report (optional). Minimum Absolute Amount: enter the minimum debit or credit to report (optional). To see all accounting entries, enter zero (0) and leave the other parameters blank. Resource Code: enter the resource codes to report (optional). Department: enter the routing department to report (optional). Class Code: enter the WIP class code to report (optional). WIP Job or Flow Schedule: enter the WIP Job or the Flow Schedule to report (optional). Organization Code: enter the inventory organization(s) you wish to report (optional). Assembly Number: enter the assembly number(s) you wish to report (optional). Operating Unit: enter the operating unit(s) you wish to report (optional). Ledger: enter the ledger(s) you wish to report (optional). /* +=============================================================================+ -- | Copyright 2009- 2024 Douglas Volz Consulting, Inc. -- | All rights reserved. -- | Permission to use this code is granted provided the original author is -- | acknowledged. No warranties, express or otherwise is included in this permission. -- +=============================================================================+ -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 06 Nov 2009 Douglas Volz Initial Coding -- | 1.21 25 Jan 2024 Douglas Volz Added Std Cost Update Description to Transaction Reference column. -- | Andy Haack Added organization security restriction by org_access_view oav. -- +=============================================================================+*/ Please specify at least one or more of the following: Accounting Line Type, WIP Transaction Type, Minimum Absolute Amount, Resource Code, Department, Class Code, WIP Job or Flow Schedule or Assembly Number, plus one or more Organization Codes. Hint: to show all detailed entries, enter zero (0) for the Minimum Absolute Amount, along with one or more Organization Codes. Enginatics &category_columns &order_by_project &order_by_wip_osp &order_by_wip_ovhds &osp_wip_table_joins &p_show_project &p_show_wip_osp &p_show_wip_osp2 &p_show_wip_ovhds &project_table_joins &project_tables &segment_columns &segment_columns_grp &wip_non_sla_table_joins &wip_osp_tables &wip_ovhd_table_joins &wip_ovhd_tables &wip_sla_table_joins &wip_sla_tables 1=1 2=2 1 -10 &segment_columns select 'gcc.'||lower(fifsv.application_column_name)||' "'||fifsv.form_left_prompt||'",' text from fnd_id_flex_segments_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and fifsv.id_flex_num=(select fifsv0.id_flex_num from fnd_id_flex_structures_vl fifsv0 where fifsv0.application_id=101 and fifsv0.id_flex_code='GL#' and fifsv0.id_flex_structure_name=:chart_of_accounts) order by fifsv.segment_num LOV GL Chart of Accounts (inv org related) 8E2FF36EDEC879D2E0530100007F1FF2 Y select fifsv.id_flex_structure_name value, fifsv.id_flex_structure_code description from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 and fifsv.id_flex_num in (select ood.chart_of_accounts_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate + 1) > sysdate) order by fifsv.id_flex_structure_name select fifsv.id_flex_structure_name from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 and fifsv.id_flex_num=(select ood.chart_of_accounts_id from org_organization_definitions ood where ood.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID')) Y US Chart of Accounts 2 &segment_columns_grp select 'gcc.'||lower(fifsv.application_column_name)||',' text from fnd_id_flex_segments_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and fifsv.id_flex_num=(select fifsv0.id_flex_num from fnd_id_flex_structures_vl fifsv0 where fifsv0.application_id=101 and fifsv0.id_flex_code='GL#' and fifsv0.id_flex_structure_name=:chart_of_accounts) order by fifsv.segment_num US Chart of Accounts 3 10 2=2 wta.transaction_date >= :p_trx_date_from and wt.transaction_date >= :p_trx_date_from Date select max(oap.period_start_date) from org_acct_periods oap, mtl_parameters mp where mp.organization_id = oap.organization_id and mp.organization_id <> mp.master_organization_id Y US Transaction Date From 4 20 2=2 wta.transaction_date < :p_trx_date_to + 1 and wt.transaction_date < :p_trx_date_to + 1 Date select max(oap.schedule_close_date) from org_acct_periods oap, mtl_parameters mp where mp.organization_id = oap.organization_id and mp.organization_id <> mp.master_organization_id Y US Transaction Date To 5 30 &wip_sla_tables xla_distribution_links xdl, xla_ae_headers ah, xla_ae_lines al, LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y Y Y US Show SLA Accounting 6 &wip_non_sla_table_joins and gcc.code_combination_id (+) = acct_dist.reference_account N US Show SLA Accounting 7 &wip_sla_table_joins and ah.ledger_id = gl.ledger_id and ah.application_id = al.application_id and ah.application_id = 707 and ah.ae_header_id = al.ae_header_id and al.ledger_id = ah.ledger_id and al.ae_header_id = xdl.ae_header_id and al.ae_line_num = xdl.ae_line_num and xdl.application_id = 707 and xdl.source_distribution_type = 'WIP_TRANSACTION_ACCOUNTS' and xdl.source_distribution_id_num_1 = acct_dist.wip_sub_ledger_id and gcc.code_combination_id (+) = al.code_combination_id Y US Show SLA Accounting 8 40 &p_show_project pp.segment1 Project_Number, pp.name Project_Name, -- Revision for version 1.19 pt.task_number Task_Number, pt.task_name Task_Name, pt.wbs_level WBS_Level, pt.description Task_Description, al.meaning Project_Cost_Collected, -- End revision for version 1.19 LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y N Y US Show Projects 9 &order_by_project ,pp.segment1 ,pp.name ,pt.task_number ,pt.task_name Y US Show Projects 10 &project_table_joins and pp.project_id (+) = acct_dist.project_id and pt.task_id (+) = acct_dist.task_id and al.lookup_code (+) = acct_dist.pm_cost_collected and al.lookup_type (+) = 'AR_PROCESS_STATUS' Y US Show Projects 11 &project_tables pa_projects_all pp, pa_tasks pt, ar_lookups al, Y US Show Projects 12 50 &p_show_wip_osp2 (select msiv.concatenated_segments from mtl_system_items_vl msiv where pol.item_id = msiv.inventory_item_id and msiv.organization_id = acct_dist.organization_id) Outside_Processing_Item, pv.vendor_name Supplier_Name, poh.segment1 PO_Number, pol.line_num PO_Line, (select pr.release_num from po_releases_all pr, rcv_transactions rt where rt.transaction_id = acct_dist.rcv_transaction_id and rt.po_release_id = pr.po_release_id) PO_Release, (select nvl(pll.price_override, pol.unit_price) from po_line_locations_all pll, rcv_transactions rt where rt.transaction_id = acct_dist.rcv_transaction_id and rt.po_line_location_id = pll.line_location_id) PO_Unit_Price, acct_dist.currency_code, acct_dist.currency_conversion_date, acct_dist.currency_conversion_rate, LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y N Y US Show WIP Outside Processing 13 &order_by_wip_osp ,poh.segment1 ,pol.line_num ,(select pr.release_num from po_releases_all pr, rcv_transactions rt where rt.transaction_id = acct_dist.rcv_transaction_id and rt.po_release_id = pr.po_release_id (+)) Y US Show WIP Outside Processing 14 &osp_wip_table_joins and poh.po_header_id (+) = acct_dist.po_header_id and pol.po_line_id (+) = acct_dist.po_line_id and pv.vendor_id (+) = poh.vendor_id Y US Show WIP Outside Processing 15 &p_show_wip_osp acct_dist.rcv_transaction_id Receiving_Transaction_Id, Y US Show WIP Outside Processing 16 &wip_osp_tables po_headers_all poh, po_lines_all pol, po_vendors pv, Y US Show WIP Outside Processing 17 60 &p_show_wip_ovhds acct_dist.overhead_basis_factor, br_basis.resource_code Basis_Resource_Code, LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y N Y US Show WIP Overheads 18 &order_by_wip_ovhds ,br_basis.resource_code Y US Show WIP Overheads 19 &wip_ovhd_table_joins and br_basis.resource_id (+) = acct_dist.basis_resource_id Y US Show WIP Overheads 20 &wip_ovhd_tables bom_resources br_basis, Y US Show WIP Overheads 21 70 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=11)) US Category Set 1 22 80 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=5)) US Category Set 2 23 90 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name xxen_util.previous_parameter_value(:parameter_id) US Category Set 3 24 100 1=1 ml1.meaning = :p_accounting_line_type LOV CST Accounting Line Type E7B8D5BA75FBA5ACE05369FB090593D7 Y select meaning value, description from mfg_lookups where lookup_type='CST_ACCOUNTING_LINE_TYPE' and enabled_flag='Y' order by meaning US Accounting Line Type 25 110 2=2 wt.transaction_type in (select ml.lookup_code from mfg_lookups ml where ml.lookup_type = 'WIP_TRANSACTION_TYPE' and ml.meaning = :p_wip_transaction_type) LOV WIP Transaction Type E77C7835DAA40406E0530100007F2B67 Y select meaning value, description from mfg_lookups where lookup_type='WIP_TRANSACTION_TYPE' and enabled_flag='Y' order by meaning US WIP Transaction Type 26 120 2=2 abs(wta.base_transaction_value) >= :p_min_absolute_amount Number US Minimum Absolute Amount 27 130 1=1 br.resource_code=:p_resource_code LOV BOM Resource Code (org code dependent) E7CCB27F39541272E0530100007F62B4 Y select br.resource_code value, br.description from bom_resources br where (:$flex$.organization_code is null or br.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y')) and nvl(br.disable_date,sysdate)>=sysdate order by br.resource_code US Resource Code 28 140 1=1 bd.department_code=:p_department_code LOV BOM Department (org code dependent) E7CCB27F39531272E0530100007F62B4 Y select bd.department_code value, nvl2(bd.department_class_code,bd.department_class_code||': ',null)||bd.description description from bom_departments bd where :$flex$.organization_code is null or bd.organization_id in (select mp.organization_id from mtl_parameters mp where mp.organization_code=:$flex$.organization_code) order by bd.department_code US Department 29 150 2=2 wdj.class_code = :p_class_code LOV WIP Class Code (org dependent) E7CCB27F39561272E0530100007F62B4 Y select distinct wac.class_code value, wac.description from mtl_parameters mp, wip_accounting_classes wac where (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y') and mp.organization_id=wac.organization_id order by wac.class_code US Class Code 30 160 2=2 (wt.organization_id, wt.wip_entity_id) in (select we.organization_id, we.wip_entity_id from wip_entities we where we.wip_entity_name=:p_wip_job_flow_schedule) LOV custom Y select we.wip_entity_name value, xxen_util.meaning(we.entity_type,'WIP_ENTITY',700)||' ('||ood.organization_code||')'||nvl2(we.description,': '||we.description,null) description from wip_entities we, org_organization_definitions ood where (:$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 (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y') and we.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate and we.entity_type in (1,3,4,5,8) --(Discrete job, Closed discrete job, Flow Schedule, Lot based job, Closed lot based job) order by we.creation_date desc US WIP Job or Flow Schedule 31 170 2=2 mp.organization_code = :p_org_code LOV INV Organization Code (excluding master) 91D3167A33531204E053BB6B63585EE3 Y select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id not in (select mp.organization_id from mtl_parameters mp where mp.organization_id=mp.master_organization_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 select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') US Organization Code 32 180 1=1 msiv.concatenated_segments = :p_item_number LOV INV Item Number (costing enabled only items) EC5CECA520510FC8E0530100007F5EB3 Y Y select distinct msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv, mtl_parameters mp where msiv.organization_id = mp.organization_id and (xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y' or (:$flex$.organization_code is null and mp.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 (msiv.costing_enabled_flag='Y' or mp.process_enabled_flag='Y') order by msiv.concatenated_segments US Assembly Number 33 190 1=1 haou2.name = :p_operating_unit LOV INV Operating Unit 9B9627743E84607DE053BB6B635805FB Y select haouv.name value, 'Operating Unit' description from hr_all_organization_units_vl haouv where haouv.organization_id in ( select to_number(hoi.org_information3) from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou where mp.organization_id<>mp.master_organization_id and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.organization_id=haou.organization_id and nvl(haou.date_to,sysdate)>=sysdate ) order by 1 US Operating Unit 34 200 1=1 gl.name = :p_ledger LOV GL Ledger (inv org related) 8E2FF36EDF3A79D2E0530100007F1FF2 Y select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, gl.name US Ledger