ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 2615C07E38B0BD52E0634B05890A18DFY --------------------------- --WIP Transaction Accounts--- --------------------------- select 'WIP' source, nvl(gl.short_name, gl.name) ledger, hou.name operating_unit, ood.organization_code, trunc(wta.transaction_date) transaction_date, to_char(xah.period_name) period, we.wip_entity_name wip_job_number, xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) job_status, wdj.net_quantity wo_quantity, msiv.concatenated_segments assembly_item_number, msiv.concatenated_segments item_number, case when :p_replace_colon_dot = 'Y' then replace( xxen_util.concatenated_segments(msiv.expense_account),'-','.') else xxen_util.concatenated_segments(msiv.expense_account) end item_expense_account, case when :p_replace_colon_dot = 'Y' then replace( xxen_util.concatenated_segments(msiv.cost_of_sales_account),'-','.') else xxen_util.concatenated_segments(msiv.cost_of_sales_account) end item_cogs_account, case when :p_replace_colon_dot = 'Y' then replace( xxen_util.concatenated_segments(msiv.sales_Account),'-','.') else xxen_util.concatenated_segments(msiv.sales_Account) end item_sales_account, msiv.description item_description, &category_columns decode(msiv.planning_make_buy_code, 2, 'Buy', 1, 'Make')"Make/Buy", msiv.item_type, null subinventory, null locator, wt.operation_seq_num operation_sequence, bd.department_code department, wt.resource_seq_num resource_seq, br.resource_code, br.description resource_description, xxen_util.meaning(br.cost_code_type,'CST_COST_CODE_TYPE',700) cost_element, &cst_segment_columns &sla_segment_columns br.unit_of_measure transaction_uom, xxen_util.meaning(wta.basis_type,'CST_BASIS',700) basis_type, wta.primary_quantity transaction_qty, wta.rate_or_amount unit_cost, wta.base_transaction_value transaction_value, gl.currency_code currency, xxen_util.meaning(wt.transaction_type,'WIP_TRANSACTION_TYPE',700) transaction_type_name, xal.accounting_class_code accounting_class, xxen_util.meaning(wta.accounting_line_type,'CST_ACCOUNTING_LINE_TYPE',700) accounting_line_type, xal.accounted_dr, xal.accounted_cr, nvl(xal.accounted_dr, 0)-nvl(xal.accounted_cr, 0)account_net, wta.activity_id, xxen_util.meaning(we.entity_type,'WIP_ENTITY',700) wip_entity_type, xxen_util.meaning(wdj.job_type,'WIP_DISCRETE_JOB',700) job_type, xxen_util.meaning(wdj.wip_supply_type,'WIP_SUPPLY',700) supply_type, null po_number, wta.wip_entity_id, wta.transaction_id, xte.entity_code, xah.ae_header_id xla_header_id, xal.ae_line_num xla_line_number from gl_ledgers gl, org_organization_definitions ood, wip_transaction_accounts wta, wip_transactions wt, wip_entities we, wip_discrete_jobs wdj, gl_code_combinations_kfv gcc1, gl_code_combinations_kfv gcc2, xla_transaction_entities_upg xte, org_acct_periods oap, xla_events xe, xla_ae_headers xah, xla_ae_lines xal, xla_distribution_links xdl, hr_operating_units hou, mtl_system_items_vl msiv, wip_repetitive_items wri, bom_departments bd, bom_resources br where 1=1 and 3=3 and wta.organization_id=ood.organization_id and ood.set_of_books_id=gl.ledger_id and ood.operating_unit=hou.organization_id and xte.source_id_int_1=wta.transaction_id and xte.security_id_int_1=wta.organization_id and xe.entity_id=xte.entity_id and xe.event_id=xah.event_id and xah.ae_header_id=xal.ae_header_id and xdl.event_id=xe.event_id and xdl.ae_header_id=xah.ae_header_id and xdl.ae_line_num=xal.ae_line_num and wta.wip_sub_ledger_id=xdl.source_distribution_id_num_1 and xal.code_combination_id=gcc2.code_combination_id and xte.ledger_id=xah.ledger_id and wta.base_transaction_value<>0 and hou.set_of_books_id=xte.ledger_id and hou.organization_id=xte.security_id_int_2 and wta.organization_id=ood.organization_id and wta.accounting_line_type<>15 and wta.transaction_id=wt.transaction_id and wta.wip_entity_id=we.wip_entity_id and wta.wip_entity_id=wdj.wip_entity_id and wta.organization_id(+)=wdj.organization_id and wta.transaction_date >= oap.period_start_date and wta.transaction_date < oap.schedule_close_date + 1 and oap.organization_id= ood.organization_id and wta.reference_account=gcc1.code_combination_id and we.organization_id(+)=msiv.organization_id and we.primary_item_id(+)=msiv.inventory_item_id and wt.organization_id=wri.organization_id(+) and wt.wip_entity_id=wri.wip_entity_id(+) and wt.line_id=wri.line_id(+) and wt.department_id=bd.department_id(+) and br.resource_id(+)=wta.resource_id and :p_source in ('Both','WIP') union --------------------------- --Material Distirbutions--- --------------------------- select 'Material' source, nvl(gl.short_name, gl.name) ledger, hou.name operating_unit, ood.organization_code, trunc(mta.transaction_date) transaction_date, to_char(xah.period_name) period, we.wip_entity_name wip_job_number, xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) job_status, wdj.net_quantity wo_quantity, (select msiv_assembly.concatenated_segments from mtl_system_items_vl msiv_assembly where wdj.primary_item_id=msiv_assembly.inventory_item_id and wdj.organization_id=msiv_assembly.organization_id) assembly_item_number, msiv.concatenated_segments item_number, case when :p_replace_colon_dot = 'Y' then replace( xxen_util.concatenated_segments(msiv.expense_account),'-','.') else xxen_util.concatenated_segments(msiv.expense_account) end item_expense_account, case when :p_replace_colon_dot = 'Y' then replace( xxen_util.concatenated_segments(msiv.cost_of_sales_account),'-','.') else xxen_util.concatenated_segments(msiv.cost_of_sales_account) end item_cogs_account, case when :p_replace_colon_dot = 'Y' then replace( xxen_util.concatenated_segments(msiv.sales_Account),'-','.') else xxen_util.concatenated_segments(msiv.sales_Account) end item_sales_account, msiv.description item_description, &category_columns decode(msiv.planning_make_buy_code, 2, 'Buy', 1, 'Make')"Make/Buy", msiv.item_type, mmt.subinventory_code subinventory, mil.concatenated_segments locator, mmt.operation_seq_num operation_sequence, null department, null resource_seq, null resource_code, null resource_description, cce.cost_element, &cst_segment_columns &sla_segment_columns mmt.transaction_uom, xxen_util.meaning(mta.basis_type,'CST_BASIS',700) basis_type, mmt.transaction_quantity transaction_qty, case when mta.primary_quantity <> 0 then (mta.base_transaction_value / mta.primary_quantity) else 0 end unit_cost, mta.base_transaction_value transaction_value, mmt.currency_code currency, mtt.transaction_type_name, xal.accounting_class_code accounting_class, xxen_util.meaning(mta.accounting_line_type,'CST_ACCOUNTING_LINE_TYPE',700) accounting_line_type, xal.accounted_dr, xal.accounted_cr, nvl(xal.accounted_dr, 0)-nvl(xal.accounted_cr, 0)account_net, null activity_id, xxen_util.meaning(we.entity_type,'WIP_ENTITY',700) wip_entity_type, null job_type, null supply_type, null po_number, wdj.wip_entity_id, mmt.transaction_id, xte.entity_code, xah.ae_header_id xla_header_id, xal.ae_line_num xla_line_number from mtl_material_transactions mmt, mtl_transaction_accounts mta, mtl_system_items_vl msiv, gl_ledgers gl, org_organization_definitions ood, mtl_item_locations_kfv mil, mtl_transaction_types mtt, wip_discrete_jobs wdj, wip_entities we, cst_cost_elements cce, gl_code_combinations_kfv gcc1, gl_code_combinations_kfv gcc2, org_acct_periods oap, xla_transaction_entities_upg xte, xla_events xe, xla_ae_headers xah, xla_ae_lines xal, xla_distribution_links xdl, hr_operating_units hou where 2=2 and 3=3 and mmt.transaction_id=mta.transaction_id and mmt.inventory_item_id=mta.inventory_item_id and mta.inventory_item_id=msiv.inventory_item_id and mmt.inventory_item_id=msiv.inventory_item_id and mmt.organization_id=ood.organization_id and msiv.organization_id=mmt.organization_id and msiv.organization_id=ood.organization_id and ood.set_of_books_id=gl.ledger_id and ood.operating_unit=hou.organization_id and mmt.transaction_source_id=wdj.wip_entity_id(+) and wdj.wip_entity_id=we.wip_entity_id and mmt.organization_id=wdj.organization_id(+) and mil.inventory_location_id(+)=mmt.locator_id and mil.organization_id(+)=mmt.organization_id and mil.subinventory_code(+)=mmt.subinventory_code and mmt.transaction_type_id=mtt.transaction_type_id and mta.cost_element_id=cce.cost_element_id(+) and mta.reference_account=gcc1.code_combination_id(+) and mmt.organization_id=nvl(mmt.owning_organization_id, mmt.organization_id) and xte.source_id_int_1=mta.transaction_id and xte.security_id_int_1=mta.organization_id and xe.entity_id=xte.entity_id and xe.event_id=xah.event_id and xah.ae_header_id=xal.ae_header_id and xdl.event_id=xe.event_id and xdl.ae_header_id=xah.ae_header_id and xdl.ae_line_num=xal.ae_line_num and mta.inv_sub_ledger_id=xdl.source_distribution_id_num_1 and xal.code_combination_id=gcc2.code_combination_id and xte.ledger_id=xah.ledger_id and mta.base_transaction_value<> 0 and hou.set_of_books_id=xte.ledger_id and hou.organization_id=xte.security_id_int_2 and mta.transaction_source_type_id=5 and --'Purchase order' =1, Internal Requistion = 7 mmt.transaction_date >= oap.period_start_date and mmt.transaction_date < oap.schedule_close_date + 1 and oap.organization_id= ood.organization_id and :p_source in ('Both','Material') #,##0.00000;[Red]-#,##0.00000 US WIP Value Summary Report to get the WIP and Material accounting distributions, by WIP job,period, Org . It displays detailed information from both sources WIP and Material Transactions Parameters: =========== Period: To pull the transactions based on specific inventory period ( transaction_date).(mandatory) Source: enter the source "WIP","Material" or "Both" you wish to report (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). Category Set 3: any item category you wish, typically the Inventory category set (optional). Replace Colon with Dot: This is to give flexibility to replace concatenated segments '-' with '.' . If Yes, it wil always replace (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 (mandatory). Operating Unit: enter the operating unit(s) you wish to report (optional). Ledger: enter the ledger(s) you wish to report (optional). Enginatics &category_columns &cst_segment_columns &sla_segment_columns 1=1 2=2 3=3 :p_replace_colon_dot :p_source 1 -10 &cst_segment_columns select case when :p_replace_colon_dot='Y' then 'replace(gcc1.concatenated_segments, ''-'', ''.'')'||' "CST Account Combination",' else 'gcc1.concatenated_segments "CST Account Combination",' end text from dual union all select y.* from ( select 'gcc1.'||lower(fifsv.application_column_name)||' "CST '||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||'",'||chr(10)|| 'xxen_util.segment_description(gcc1.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', gcc1.chart_of_accounts_id'|| ( select ', gcc1.'||lower(fifs.application_column_name) from fnd_id_flex_segments fifs where fifsv.application_id=fifs.application_id and fifsv.id_flex_code=fifs.id_flex_code and fifsv.id_flex_num=fifs.id_flex_num and fifsv.enabled_flag=fifs.enabled_flag and fifs.flex_value_set_id=(select ffvs.parent_flex_value_set_id from fnd_flex_value_sets ffvs where fifsv.flex_value_set_id=ffvs.flex_value_set_id) )||')'||' "'||substrb(fifsv.form_left_prompt,1,xxen_report.max_column_length-5)||' desc",' text from (select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' 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 ) y 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')) US Chart of Accounts 2 &sla_segment_columns select case when :p_replace_colon_dot='Y' then 'replace(gcc2.concatenated_segments, ''-'', ''.'')'||' "SLA Account Combination",' else 'gcc2.concatenated_segments "SLA Account Combination",' end text from dual union all select y.* from ( select 'gcc2.'||lower(fifsv.application_column_name)||' "SLA '||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||'",'||chr(10)|| 'xxen_util.segment_description(gcc2.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', gcc2.chart_of_accounts_id'|| ( select ', gcc2.'||lower(fifs.application_column_name) from fnd_id_flex_segments fifs where fifsv.application_id=fifs.application_id and fifsv.id_flex_code=fifs.id_flex_code and fifsv.id_flex_num=fifs.id_flex_num and fifsv.enabled_flag=fifs.enabled_flag and fifs.flex_value_set_id=(select ffvs.parent_flex_value_set_id from fnd_flex_value_sets ffvs where fifsv.flex_value_set_id=ffvs.flex_value_set_id) )||')'||' "'||substrb(fifsv.form_left_prompt,1,xxen_report.max_column_length-5)||' desc",' text from (select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' 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 ) y US Chart of Accounts 3 10 3=3 oap.period_name = :p_period_name LOV INV Period 8E2FF36EDF2279D2E0530100007F1FF2 Y select distinct oap.period_name value, max(oap.period_year||'-'||oap.period_num||', '||xxen_util.meaning(nvl2(oap.period_close_date,decode(oap.open_flag,'P',2,'N',decode(oap.summarized_flag,'N',65,66),4),3),'MTL_ACCT_PERIOD_STATUS',700)||' ('||oap.period_start_date||' - '||oap.schedule_close_date||')') over (partition by oap.period_name) description, max(oap.period_start_date) over (partition by oap.period_name) period_start_date, max(oap.effective_period_num) over (partition by oap.period_name) effective_period_num from gl_ledgers gl, org_organization_definitions ood, mtl_parameters mp, (select oap.period_year*10000+oap.period_num effective_period_num, oap.* from org_acct_periods oap) oap where oap.period_start_date<=sysdate and (:$flex$.ledger is null or 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 ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and gl.period_set_name=oap.period_set_name and gl.ledger_id=ood.set_of_books_id and ood.organization_id=oap.organization_id order by period_start_date desc, effective_period_num desc, oap.period_name select distinct oap.period_name value from org_acct_periods oap, (select max(oap.schedule_close_date) default_date from org_acct_periods oap, org_organization_definitions ood where ood.organization_id = oap.organization_id and nvl(ood.disable_date, sysdate + 1) > sysdate) max where oap.schedule_close_date = max.default_date and rownum = 1 Y US Period Name 4 25 3=3 ood.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') Y US Organization Code 5 27 :p_source LOV custom Y select 'Both' value, 'Both' description from dual union select 'WIP' value, 'WIP' description from dual union select 'Material' value, 'Material' description from dual Both Y US Source 6 30 &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 7 40 &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 8 50 &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 9 60 1=1 (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) LOV WIP Job 8E2FF36EDEC379D2E0530100007F1FF2 Y 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 and 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) 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,5,8) --(Discrete job, Closed discrete job, Lot based job, Closed lot based job) order by we.creation_date desc US WIP Job 10 2=2 (mmt.organization_id, mmt.transaction_source_id) in (select we.organization_id, we.wip_entity_id from wip_entities we where we.wip_entity_name=:p_wip_job) US WIP Job 11 70 3=3 hou.name = :p_operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) order by hou.name US Operating Unit 12 80 3=3 gl.name = :p_ledger LOV GL Ledger 8E2FF36EDEB879D2E0530100007F1FF2 Y select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) 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, decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name US Ledger 13 90 :p_replace_colon_dot LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y US Replace Colon with Dot