ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 68712CDDE96249458C2D3F43B39AA5F5Y select null action_, null status_, null message_, null modified_columns_, x.control_group_id, x.control_rule_id, x.position_control_id, x.row_type_sort, haouv.name operating_unit, x.approval_group, x.group_description, x.enabled, x.object, x.rule_type, x.amount_limit, x.low_value, x.high_value, x.inactive_date, x.position, x.job, x.document_type, x.start_date, x.end_date, to_number(null) upload_row from ( select pcga.org_id, pcga.control_group_id, pcga.control_group_name approval_group, pcga.description group_description, xxen_util.yes(pcga.enabled_flag) enabled, pcr.control_rule_id, to_number(null) position_control_id, 1 row_type_sort, xxen_util.meaning(pcr.object_code,'CONTROLLED_OBJECT',201) object, xxen_util.meaning(pcr.rule_type_code,'CONTROL_TYPE',201) rule_type, pcr.amount_limit, xxen_po_upload.control_rule_value(pcr.control_rule_id,'LOW') low_value, xxen_po_upload.control_rule_value(pcr.control_rule_id,'HIGH') high_value, pcr.inactive_date, to_char(null) position, to_char(null) job, to_char(null) document_type, to_date(null) start_date, to_date(null) end_date from po_control_groups_all pcga, po_control_rules pcr where pcga.control_group_id=pcr.control_group_id union all select pcga.org_id, pcga.control_group_id, pcga.control_group_name approval_group, pcga.description group_description, xxen_util.yes(pcga.enabled_flag) enabled, to_number(null) control_rule_id, ppca.position_control_id, 2 row_type_sort, to_char(null) object, to_char(null) rule_type, to_number(null) amount_limit, to_char(null) low_value, to_char(null) high_value, to_date(null) inactive_date, (select pp.name from per_positions pp where ppca.position_id=pp.position_id) position, (select pjv.name from per_jobs_vl pjv where ppca.job_id=pjv.job_id) job, (select pcfa.control_function_name from po_control_functions_all pcfa where ppca.control_function_id=pcfa.control_function_id) document_type, ppca.start_date, ppca.end_date from po_control_groups_all pcga, po_position_controls_all ppca where pcga.control_group_id=ppca.control_group_id union all select pcga.org_id, pcga.control_group_id, pcga.control_group_name approval_group, pcga.description group_description, xxen_util.yes(pcga.enabled_flag) enabled, to_number(null) control_rule_id, to_number(null) position_control_id, 0 row_type_sort, to_char(null) object, to_char(null) rule_type, to_number(null) amount_limit, to_char(null) low_value, to_char(null) high_value, to_date(null) inactive_date, to_char(null) position, to_char(null) job, to_char(null) document_type, to_date(null) start_date, to_date(null) end_date from po_control_groups_all pcga where pcga.control_group_id not in (select pcr.control_group_id from po_control_rules pcr) and pcga.control_group_id not in (select ppca.control_group_id from po_position_controls_all ppca where ppca.control_group_id is not null) ) x, hr_all_organization_units_vl haouv where x.org_id=haouv.organization_id and x.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_approval_group US PO Approval Groups Upload Creates and updates the approval groups of the Approval Groups form, with their rules, and the assignments of approval groups to positions or jobs per document type of the Approval Assignments form. A row carries an approval group, optionally with one rule (Object, Type, Amount Limit, Low Value, High Value, Inactive Date), one assignment (Position or Job, Document Type, Start Date, End Date), or both. The group values are taken from the first row of each group. Low Value and High Value of an account, item or item category range hold the segment values separated by the flexfield delimiter, such as 01-000-0000-0000-000. A Location rule takes the location in Low Value. An operating unit that uses approval hierarchies (Use Approval Hierarchies in the Financials options) assigns positions, otherwise jobs. Rules and assignments are ended with Inactive Date and End Date, not deleted. 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 x.approval_group=:approval_group LOV custom select pcga.control_group_name value, haouv.name description from po_control_groups_all pcga, hr_all_organization_units_vl haouv where pcga.org_id=haouv.organization_id and (:$flex$.operating_unit is null or haouv.name=:$flex$.operating_unit) order by 1 US Approval Group 4 40 1=1 x.control_group_id in (select ppca.control_group_id from po_position_controls_all ppca, per_positions pp where ppca.position_id=pp.position_id and pp.name=:position) and (x.row_type_sort<>2 or x.position=:position) LOV custom select distinct pp.name value, null description from po_position_controls_all ppca, per_positions pp where ppca.position_id=pp.position_id order by 1 US Position 5 50 1=1 x.control_group_id in (select ppca.control_group_id from po_position_controls_all ppca, per_jobs_vl pjv where ppca.job_id=pjv.job_id and pjv.name=:job) and (x.row_type_sort<>2 or x.job=:job) LOV custom select distinct pjv.name value, null description from po_position_controls_all ppca, per_jobs_vl pjv where ppca.job_id=pjv.job_id order by 1 US Job 6 60 1=1 x.control_group_id in (select ppca.control_group_id from po_position_controls_all ppca, po_control_functions_all pcfa where ppca.control_function_id=pcfa.control_function_id and pcfa.control_function_name=:document_type) and (x.row_type_sort<>2 or x.document_type=:document_type) LOV custom select pcfa.control_function_name value, null description from po_control_functions_all pcfa where pcfa.enabled_flag='Y' order by 1 US Document Type 892949DDE0394472A3B0AD7817C2287F Approval Assignments Assignments of approval groups to positions or jobs. ENGINATICS 2 APPROVAL_GROUP 5 DOCUMENT_TYPE 7 END_DATE 4 JOB 1 OPERATING_UNIT 3 POSITION Y 6 START_DATE Upload Mode C,U S Site 8D2658BD140847049FE312D149DE69DE Approval Group Rules Approval groups and their rules. ENGINATICS 7 AMOUNT_LIMIT 2 APPROVAL_GROUP 4 ENABLED 3 GROUP_DESCRIPTION 9 HIGH_VALUE 10 INACTIVE_DATE 8 LOW_VALUE 5 OBJECT Y 1 OPERATING_UNIT 6 RULE_TYPE Upload Mode C,U S Site Document Type Job Position 0D91519A3CB64F3791A4568101C40225 Default Approval groups with their rules and assignments. ENGINATICS 7 AMOUNT_LIMIT 2 APPROVAL_GROUP 13 DOCUMENT_TYPE 4 ENABLED 15 END_DATE 3 GROUP_DESCRIPTION 9 HIGH_VALUE 10 INACTIVE_DATE 12 JOB 8 LOW_VALUE 5 OBJECT Y 1 OPERATING_UNIT 11 POSITION 6 RULE_TYPE 14 START_DATE Upload Mode C,U S Site 0D91519A3CB64F3791A4568101C40225 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 CONTROL_GROUP_ID Number number Y Y 6 CONTROL_RULE_ID Number number Y Y 7 POSITION_CONTROL_ID Number number Y Y 8 ROW_TYPE_SORT Number number Y Y 9 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 10 APPROVAL_GROUP Char varchar2 80 Y 11 GROUP_DESCRIPTION Char varchar2 240 12 ENABLED LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual xxen_util.meaning('Y','YES_NO',0) 13 OBJECT LOV custom varchar2 select plc.lookup_code id, plc.displayed_field value, plc.description from po_lookup_codes plc where plc.lookup_type='CONTROLLED_OBJECT' order by decode(plc.lookup_code,'DOCUMENT_TOTAL',1,'ACCOUNT_RANGE',2,'ITEM_CATEGORY_RANGE',3,'ITEM_RANGE',4,'LOCATION',5,6) 14 RULE_TYPE LOV custom varchar2 select plc.lookup_code id, plc.displayed_field value, plc.description from po_lookup_codes plc where plc.lookup_type='CONTROL_TYPE' order by decode(plc.lookup_code,'INCLUDE',1,'EXCLUDE',2,3) select xxen_util.meaning('INCLUDE','CONTROL_TYPE',201) from dual where :$column$.object is not null 15 AMOUNT_LIMIT Number number 16 LOW_VALUE LOV custom varchar2 2000 Y select hlat.location_code value, hlat.description from hr_operating_units hou, financials_system_params_all fspa, hr_locations_all hla, hr_locations_all_tl hlat where hou.name=:$column$.operating_unit and hou.organization_id=fspa.org_id and nvl(hla.business_group_id,fspa.business_group_id)=fspa.business_group_id and hla.location_id=hlat.location_id and hlat.language=userenv('lang') and sysdate<nvl(hla.inactive_date,sysdate+1) and :$column$.object=(select plc.displayed_field from po_lookup_codes plc where plc.lookup_type='CONTROLLED_OBJECT' and plc.lookup_code='LOCATION') order by hlat.location_code 17 HIGH_VALUE Char varchar2 2000 18 INACTIVE_DATE Date date 19 POSITION LOV custom varchar2 240 select ppvv.position_id id, ppvv.position_name value, ppvv.organization_name description from hr_operating_units hou, financials_system_params_all fspa, po_positions_val_v ppvv where hou.name=:$column$.operating_unit and hou.organization_id=fspa.org_id and fspa.business_group_id=ppvv.business_group_id order by ppvv.position_name 20 JOB LOV custom varchar2 700 select pjv.job_id id, pjv.name value, null description from hr_operating_units hou, financials_system_params_all fspa, per_jobs_vl pjv where hou.name=:$column$.operating_unit and hou.organization_id=fspa.org_id and fspa.business_group_id=pjv.business_group_id and sysdate between pjv.date_from and nvl(pjv.date_to,sysdate+1) order by pjv.name select pjv.name from hr_operating_units hou, financials_system_params_all fspa, po_positions_val_v ppvv, per_jobs_vl pjv where hou.name=:$column$.operating_unit and hou.organization_id=fspa.org_id and fspa.business_group_id=ppvv.business_group_id and ppvv.position_name=:$column$.position and ppvv.job_id=pjv.job_id 21 DOCUMENT_TYPE LOV custom varchar2 80 select pcfa.control_function_id id, pcfa.control_function_name value, null description from po_control_functions_all pcfa where pcfa.enabled_flag='Y' order by pcfa.control_function_name 22 START_DATE Date date select trunc(sysdate) from dual where :$column$.document_type is not null 23 END_DATE Date date 24 UPLOAD_ROW Number number Y U 1 p_org_id in Number OPERATING_UNIT U 2 p_approval_group in Char APPROVAL_GROUP U 3 p_group_description in Char Y GROUP_DESCRIPTION U 4 p_enabled in Char Y ENABLED U 5 p_object_code in Char Y OBJECT U 6 p_rule_type_code in Char Y RULE_TYPE U 7 p_amount_limit in Number Y AMOUNT_LIMIT U 8 p_low_value in Char Y LOW_VALUE U 9 p_high_value in Char Y HIGH_VALUE U 10 p_inactive_date in Date Y INACTIVE_DATE U 11 p_position_id in Number Y POSITION U 12 p_job_id in Number Y JOB U 13 p_control_function_id in Number Y DOCUMENT_TYPE U 14 p_start_date in Date Y START_DATE U 15 p_end_date in Date Y END_DATE U 16 p_control_group_id in Number Y CONTROL_GROUP_ID U 17 p_control_rule_id in Number Y CONTROL_RULE_ID U 18 p_position_control_id in Number Y POSITION_CONTROL_ID U 19 p_control_group_id_out out Number CONTROL_GROUP_ID U 20 p_control_rule_id_out out Number CONTROL_RULE_ID U 21 p_position_control_id_out out Number POSITION_CONTROL_ID U 22 status_ out Char STATUS_CODE_ U 23 message_ out Char MESSAGE_ Error SQL Order By upload_row, operating_unit, approval_group, row_type_sort, object, low_value, position, job, document_type Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, xu.control_group_id, xu.control_rule_id, xu.position_control_id, xu.row_type_sort, nvl(haouv.name,xu.operating_unit) operating_unit, nvl(pcga.control_group_name,xu.approval_group) approval_group, pcga.description group_description, xxen_util.yes(pcga.enabled_flag) enabled, xxen_util.meaning(pcr.object_code,'CONTROLLED_OBJECT',201) object, xxen_util.meaning(pcr.rule_type_code,'CONTROL_TYPE',201) rule_type, pcr.amount_limit, xxen_po_upload.control_rule_value(pcr.control_rule_id,'LOW') low_value, xxen_po_upload.control_rule_value(pcr.control_rule_id,'HIGH') high_value, pcr.inactive_date, (select pp.name from per_positions pp where ppca.position_id=pp.position_id) position, (select pjv.name from per_jobs_vl pjv where ppca.job_id=pjv.job_id) job, (select pcfa.control_function_name from po_control_functions_all pcfa where ppca.control_function_id=pcfa.control_function_id) document_type, ppca.start_date, ppca.end_date, xu.upload_row from xxen_po_approval_groups_1430_u xu, po_control_groups_all pcga, hr_all_organization_units_vl haouv, po_control_rules pcr, po_position_controls_all ppca where xu.status_code_=xxen_upload.status_success and xu.control_group_id=pcga.control_group_id(+) and pcga.org_id=haouv.organization_id(+) and xu.control_rule_id=pcr.control_rule_id(+) and xu.position_control_id=ppca.position_control_id(+)