ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 66C64BCDB9B345EABA80FFDA0AD58D79Y select null action_, null status_, null message_, null modified_columns_, prea.account_rule_id, haouv.name operating_unit, xxen_util.meaning(prea.rule_type,'ACCOUNT_RULE_TYPE',201) account_rule_type, mcv.category_concat_segs account_rule_value, mcv.description category_description, fifsv.segment_name, prea.segment_value, xxen_util.segment_description(prea.segment_value,prea.segment_num,fifsv.id_flex_num) segment_value_description, null delete_rule, to_number(null) upload_row from po_rule_expense_accounts_all prea, hr_all_organization_units_vl haouv, financials_system_params_all fspa, gl_ledgers gl, fnd_id_flex_segments_vl fifsv, mtl_categories_v mcv where prea.org_id=haouv.organization_id and prea.org_id=fspa.org_id and fspa.set_of_books_id=gl.ledger_id and fifsv.application_id=101 and fifsv.id_flex_code='GL#' and gl.chart_of_accounts_id=fifsv.id_flex_num and prea.segment_num=fifsv.application_column_name and prea.rule_value_id=mcv.category_id(+) and prea.org_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) and 1=1 Initial version. U A xxen_po_upload.upload_expense_account_rule US PO Expense Account Rules Upload Creates, updates and deletes the expense account rules of the Purchasing setup page Expense Account Rules. A rule overrides one segment of the expense account that the PO and requisition Account Generators build, with a fixed segment value for all items of a purchasing category. There is one rule per operating unit, category and segment. Enginatics R12 only Upload 1=1 1 10 1=1 :p_upload_mode like '%'||xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create||','||xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C Y US Upload Mode 2 20 1=1 haouv.name=:operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 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 xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name US Operating Unit 3 30 1=1 mcv.category_concat_segs=:category LOV custom select mcv.category_concat_segs value, mcv.description from mtl_default_category_sets mdcs, mtl_category_sets_b mcsb, mtl_categories_v mcv where mdcs.functional_area_id=2 and mdcs.category_set_id=mcsb.category_set_id and mcsb.structure_id=mcv.structure_id and mcv.enabled_flag='Y' and nvl(mcv.disable_date,sysdate+1)>sysdate and sysdate between nvl(mcv.start_date_active,sysdate) and nvl(mcv.end_date_active,sysdate) and (mcsb.validate_flag='Y' and mcv.category_id in (select mcsvc.category_id from mtl_category_set_valid_cats mcsvc where mcsb.category_set_id=mcsvc.category_set_id) or mcsb.validate_flag<>'Y') order by mcv.category_concat_segs US Category 4 40 1=1 fifsv.segment_name=:segment_name LOV custom select distinct fifsv.segment_name value, null description 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 in (select gl.chart_of_accounts_id from hr_operating_units hou, financials_system_params_all fspa, gl_ledgers gl where (:$flex$.operating_unit is null or hou.name=:$flex$.operating_unit) and hou.organization_id=fspa.org_id and fspa.set_of_books_id=gl.ledger_id) order by 1 US Segment Name DB3F6327DAEA430887D65EEBF8C06798 Create Rules Opens an empty sheet to create new expense account rules. ENGINATICS 2 ACCOUNT_RULE_TYPE 3 ACCOUNT_RULE_VALUE 4 CATEGORY_DESCRIPTION 1 OPERATING_UNIT 5 SEGMENT_NAME Y 6 SEGMENT_VALUE 7 SEGMENT_VALUE_DESCRIPTION Upload Mode C Y S Site Category Segment Name 3328B78655F64EC9A634146CF581F8F9 Default Creates, updates and deletes expense account rules. ENGINATICS 2 ACCOUNT_RULE_TYPE 3 ACCOUNT_RULE_VALUE 4 CATEGORY_DESCRIPTION 8 DELETE_RULE 1 OPERATING_UNIT 5 SEGMENT_NAME Y 6 SEGMENT_VALUE 7 SEGMENT_VALUE_DESCRIPTION Upload Mode C,U S Site 3328B78655F64EC9A634146CF581F8F9 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 ACCOUNT_RULE_ID Number number Y Y 6 OPERATING_UNIT LOV varchar2 240 HR Operating Unit for Upload F263DDA3FBF60F8FE0530100007F75CD select hou.organization_id id, hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.operating_unit is null or hou.name=:$flex$.operating_unit) 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 :$flex$.operating_unit Y 7 ACCOUNT_RULE_TYPE LOV custom varchar2 select flvv.lookup_code id, flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='ACCOUNT_RULE_TYPE' and flvv.view_application_id=201 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by flvv.meaning xxen_util.meaning('ITEM CATEGORY','ACCOUNT_RULE_TYPE',201) Y 8 ACCOUNT_RULE_VALUE LOV custom varchar2 820 select mcv.category_id id, mcv.category_concat_segs value, mcv.description from mtl_default_category_sets mdcs, mtl_category_sets_b mcsb, mtl_categories_v mcv where mdcs.functional_area_id=2 and mdcs.category_set_id=mcsb.category_set_id and mcsb.structure_id=mcv.structure_id and mcv.enabled_flag='Y' and nvl(mcv.disable_date,sysdate+1)>sysdate and sysdate between nvl(mcv.start_date_active,sysdate) and nvl(mcv.end_date_active,sysdate) and (mcsb.validate_flag='Y' and mcv.category_id in (select mcsvc.category_id from mtl_category_set_valid_cats mcsvc where mcsb.category_set_id=mcsvc.category_set_id) or mcsb.validate_flag<>'Y') order by mcv.category_concat_segs Y 9 CATEGORY_DESCRIPTION Char varchar2 240 select mcv.description from mtl_default_category_sets mdcs, mtl_category_sets_b mcsb, mtl_categories_v mcv where mdcs.functional_area_id=2 and mdcs.category_set_id=mcsb.category_set_id and mcsb.structure_id=mcv.structure_id and mcv.category_concat_segs=:$column$.account_rule_value Y 10 SEGMENT_NAME LOV custom varchar2 30 select fifsv.application_column_name id, fifsv.segment_name value, fifsv.description from hr_operating_units hou, financials_system_params_all fspa, gl_ledgers gl, fnd_id_flex_segments_vl fifsv where hou.name=:$column$.operating_unit and hou.organization_id=fspa.org_id and fspa.set_of_books_id=gl.ledger_id and fifsv.application_id=101 and fifsv.id_flex_code='GL#' and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.enabled_flag='Y' order by fifsv.segment_num Y 11 SEGMENT_VALUE LOV custom varchar2 150 select ffvv.flex_value value, ffvv.description from hr_operating_units hou, financials_system_params_all fspa, gl_ledgers gl, fnd_id_flex_segments_vl fifsv, fnd_flex_values_vl ffvv where hou.name=:$column$.operating_unit and hou.organization_id=fspa.org_id and fspa.set_of_books_id=gl.ledger_id and fifsv.application_id=101 and fifsv.id_flex_code='GL#' and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.enabled_flag='Y' and fifsv.segment_name=:$column$.segment_name and fifsv.flex_value_set_id=ffvv.flex_value_set_id and ffvv.enabled_flag='Y' and ffvv.summary_flag='N' and nvl(ffvv.start_date_active,sysdate)<=sysdate and nvl(ffvv.end_date_active,sysdate)>=sysdate order by ffvv.flex_value Y 12 SEGMENT_VALUE_DESCRIPTION Char varchar2 240 select xxen_util.segment_description(:$column$.segment_value,fifsv.application_column_name,fifsv.id_flex_num) from hr_operating_units hou, financials_system_params_all fspa, gl_ledgers gl, fnd_id_flex_segments_vl fifsv where hou.name=:$column$.operating_unit and hou.organization_id=fspa.org_id and fspa.set_of_books_id=gl.ledger_id and fifsv.application_id=101 and fifsv.id_flex_code='GL#' and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.enabled_flag='Y' and fifsv.segment_name=:$column$.segment_name Y 13 DELETE_RULE LOV custom varchar2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$column$.account_rule_id is not null 14 UPLOAD_ROW Number number Y U 1 p_org_id in Number OPERATING_UNIT U 2 p_rule_type in Char ACCOUNT_RULE_TYPE U 3 p_category_id in Number ACCOUNT_RULE_VALUE U 4 p_segment_num in Char SEGMENT_NAME U 5 p_segment_value in Char SEGMENT_VALUE U 6 p_delete_rule in Char Y DELETE_RULE U 7 p_account_rule_id in Number Y ACCOUNT_RULE_ID U 8 p_account_rule_id_out out Number ACCOUNT_RULE_ID U 9 status_ out Char STATUS_CODE_ U 10 message_ out Char MESSAGE_ Error SQL Order By upload_row, operating_unit, account_rule_value, segment_name Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, nvl(prea.account_rule_id,xu.account_rule_id) account_rule_id, nvl(prea.operating_unit,xu.operating_unit) operating_unit, nvl(prea.account_rule_type,xu.account_rule_type) account_rule_type, nvl(prea.account_rule_value,xu.account_rule_value) account_rule_value, nvl(prea.category_description,xu.category_description) category_description, nvl(prea.segment_name,xu.segment_name) segment_name, nvl(prea.segment_value,xu.segment_value) segment_value, nvl(prea.segment_value_description,xu.segment_value_description) segment_value_description, xu.delete_rule, xu.upload_row from xxen_po_expense_account_9976_u xu, ( select prea.account_rule_id, haouv.name operating_unit, xxen_util.meaning(prea.rule_type,'ACCOUNT_RULE_TYPE',201) account_rule_type, mcv.category_concat_segs account_rule_value, mcv.description category_description, fifsv.segment_name, prea.segment_value, xxen_util.segment_description(prea.segment_value,prea.segment_num,fifsv.id_flex_num) segment_value_description from po_rule_expense_accounts_all prea, hr_all_organization_units_vl haouv, financials_system_params_all fspa, gl_ledgers gl, fnd_id_flex_segments_vl fifsv, mtl_categories_v mcv where prea.org_id=haouv.organization_id and prea.org_id=fspa.org_id and fspa.set_of_books_id=gl.ledger_id and fifsv.application_id=101 and fifsv.id_flex_code='GL#' and gl.chart_of_accounts_id=fifsv.id_flex_num and prea.segment_num=fifsv.application_column_name and prea.rule_value_id=mcv.category_id(+) ) prea where xu.status_code_=xxen_upload.status_success and xu.account_rule_id=prea.account_rule_id(+)