PER Organization Upload

➜ Back to Library
Description

PER Organization Upload creates and updates Oracle HRMS organizations from Excel, together with their classifications, organization information types and the HR Organization cost allocation flexfield, within your business group. It uses the standard Oracle HR organization API and works for all classifications, for example HR Organization, Company Cost Center, GRE/Legal Entity, Operating Unit and Inventory Organization.

When to use it

  • Mass-create new organizations, such as departments, with their classifications and information types in one pass.
  • Update organization header data (name, type, location, dates, internal address line) in bulk.
  • Enable or disable an organization classification across many organizations.
  • Maintain information type values in bulk, for example work schedules, costing information or operating unit and inventory organization settings.
  • Populate the HR Organization cost allocation flexfield segments for many organizations.
  • Copy a set of organizations from one environment to another.

Before you start

  • Access to the upload in Blitz Report, from a responsibility whose HR:Business Group profile is the business group you work in. Organizations are downloaded and created in that business group only.
  • The business group itself already exists. Business groups cannot be created with this upload.
  • Locations, organization types and the values used in the information types (for example work schedules) already exist.

Choose a template

TemplateUse it for
Tabular Layout (default)Mass uploads where data is prepared elsewhere and pasted in. The information type values are compact columns Org Information1 to Org Information20, with one Org Info Segment Labels column telling you which value goes in which position.
Form LayoutSmall, targeted changes typed directly into the sheet. Each Org Information column is preceded by its own label column (Org Info Label1, Org Info Label2 …), as in the Organization window.

Step 1 – Set the parameters

Open PER Organization Upload in Blitz Report, choose the template and set the parameters:

ParameterMeaning
Upload ModeCreate (default) opens an empty sheet for new organizations; Create, Update downloads existing organizations for editing and lets you add new ones.
Business GroupThe business group of your responsibility (required).
Organization TypeDownloads organizations of this type only, for example Department.
Organization NameDownloads the selected organization.
LocationDownloads organizations at this location only.
Organization StatusActive (default) downloads organizations effective today, Inactive those that are end-dated or not yet effective.
ClassificationDownloads organizations with this classification only, for example HR Organization.
Classification StatusActive downloads only enabled classifications, Inactive only disabled ones.
Information TypeDownloads rows of this information type only, for example Work Schedule.
Blitz Report run screen for PER Organization Upload with Upload Mode Create, Update, Business Group Vision Corporation and Organization Name Recruiting-West

Step 2 – Run to download the Excel file

Click Run. The Excel file downloads and opens with one row per organization, classification and information type, or an empty sheet in Create mode.

Excel file with the department Recruiting-West, its HR Organization classification and Work Schedule information type

Step 3 – Enter or change the organizations

Each row holds the organization header, one Classification with its Enabled flag and, optionally, one Org Information Type with its values. An organization with several classifications or information types has several rows with the same Organization Name and header values.

On a downloaded row change the Organization Name, Organization Type, Date From, Date To, Location or Internal Address Line, set Enabled to No or Yes to disable or enable the classification, or change the Org Information values. Business Group, Classification and Org Information Type are read-only on downloaded rows.

To create an organization, add a row with the Organization Name, Date From, Organization Type, Location and the Classification, for example HR Organization. Add further rows with the same Organization Name for more classifications or information types. Entering the name of an existing organization on a new row fills its header values, so you can add a classification or information type to an existing organization the same way.

For an information type, select the Classification first, then the Org Information Type, then enter the values in the positions shown in Org Info Segment Labels, for example (1) Schedule Table and (2) Schedule Name for Work Schedule. Each column offers the valid values of its position. Information types that allow several records can be entered on several rows for the same organization and classification.

The Cost Alloc Segment columns are the cost allocation flexfield of the HR Organization classification. Their headings take the segment prompts of your business group’s costing structure (for example Cost Center), and values are only processed on HR Organization rows.

Excel file with the internal address line of Recruiting-West changed, marked Update, and a new department Talent Acquisition-West with HR Organization classification and work schedule added, marked Create

Step 4 – Validate and Save

Click Validate and Save. This checks for missing required values and saves the file. Correct any rows it flags before uploading.

Both edited rows show status Valid after Validate and Save

Step 5 – Upload the file

In Blitz Report click Upload and select the saved file. This submits the upload request, which creates or updates each organization, classification and information type.

File Upload page with the saved Excel file selected for upload

Step 6 – Review the result report

When the request completes, a result report opens listing every uploaded row with its status and a message saying what was done, for example Organization updated, or Organization created. Classification created. Org Information created.

Result report showing Recruiting-West updated and Talent Acquisition-West created with its classification and information type, both with status Success

What’s produced

  • Organizations created or updated with their classifications, information types and cost allocation segments.
  • A result report listing every row with a status (success, warning or error) and a message.

Common questions

Can I create a business group with this upload?
No. Create business groups in the Organization window. The upload works within the business group of your responsibility.

Can I delete an organization or a classification?
No. Set Enabled to No to disable a classification, or enter a Date To to end-date the organization.

How do I add a classification to an existing organization?
Add a row with the organization’s name and the new Classification. The header values are filled from the existing organization.

Why did I get a warning that an information type cannot be updated?
Business Group Information, Canada Employer Identification and the FPT bank and branch information types can be created with the upload but not changed afterwards. Change them in the Organization window.

An information value refers to another record created in the same file. What do I do?
Upload in two passes: first create the information type without that value, then download again and set the value.

Why is the Company Cost Center first value filled automatically?
As in the Organization window, it is built from the values you enter in positions 2 to 5 (the company and cost center value sets and values).

Troubleshooting

MessageCauseWhat to do
Business Group classification cannot be created via this upload. Use the Define Business Group form.A row adds the Business Group classification to an organization.Create business groups in the Organization window.
Information type … cannot be updated by this upload.A change to Business Group Information, Canada Employer Identification or an FPT bank or branch information type (warning; the row is not changed).Make the change in the Organization window.
Org DFF error: …An organization descriptive flexfield value is not valid.Pick valid values for the chosen context.
Org Information DFF error: …An information type value is missing or not valid for its position.Check Org Info Segment Labels and pick the values from the column lists.
Organization id … not found.
Classification Org Information ID … not found.
Org Information ID … not found.		A downloaded row refers to an organization or record that no longer exists.Download again and reapply your changes.
select
null action_,
null status_,
null message_,
null modified_columns_,
to_number(null) organization_id_out,
to_number(null) class_org_info_id_out,
to_number(null) org_info_id_out,
haou.organization_id,
hoi_class.org_information_id class_org_info_id,
hoi.org_information_id org_info_id,
pbg.name business_group,
haout.name organization_name,
xxen_util.meaning(haou.type,'ORG_TYPE',3) organization_type,
haou.date_from,
haou.date_to,
hl.location_code location,
xxen_util.meaning(haou.internal_external_flag,'INTL_EXTL',3) internal_external,
haou.internal_address_line,
xxen_util.display_flexfield_context(800,'PER_ORGANIZATION_UNITS',haou.attribute_category) org_dff_context,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE1',haou.rowid,haou.attribute1) org_attribute1,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE2',haou.rowid,haou.attribute2) org_attribute2,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE3',haou.rowid,haou.attribute3) org_attribute3,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE4',haou.rowid,haou.attribute4) org_attribute4,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE5',haou.rowid,haou.attribute5) org_attribute5,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE6',haou.rowid,haou.attribute6) org_attribute6,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE7',haou.rowid,haou.attribute7) org_attribute7,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE8',haou.rowid,haou.attribute8) org_attribute8,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE9',haou.rowid,haou.attribute9) org_attribute9,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE10',haou.rowid,haou.attribute10) org_attribute10,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE11',haou.rowid,haou.attribute11) org_attribute11,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE12',haou.rowid,haou.attribute12) org_attribute12,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE13',haou.rowid,haou.attribute13) org_attribute13,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE14',haou.rowid,haou.attribute14) org_attribute14,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE15',haou.rowid,haou.attribute15) org_attribute15,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE16',haou.rowid,haou.attribute16) org_attribute16,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE17',haou.rowid,haou.attribute17) org_attribute17,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE18',haou.rowid,haou.attribute18) org_attribute18,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE19',haou.rowid,haou.attribute19) org_attribute19,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE20',haou.rowid,haou.attribute20) org_attribute20,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE21',haou.rowid,haou.attribute21) org_attribute21,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE22',haou.rowid,haou.attribute22) org_attribute22,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE23',haou.rowid,haou.attribute23) org_attribute23,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE24',haou.rowid,haou.attribute24) org_attribute24,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE25',haou.rowid,haou.attribute25) org_attribute25,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE26',haou.rowid,haou.attribute26) org_attribute26,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE27',haou.rowid,haou.attribute27) org_attribute27,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE28',haou.rowid,haou.attribute28) org_attribute28,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE29',haou.rowid,haou.attribute29) org_attribute29,
xxen_util.display_flexfield_value(800,'PER_ORGANIZATION_UNITS',haou.attribute_category,'ATTRIBUTE30',haou.rowid,haou.attribute30) org_attribute30,
xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3) classification,
xxen_util.meaning(hoi_class.org_information2,'YES_NO',0) enabled,
hoi.displayed_org_information_type org_information_type,
nvl(hoi.classification_has_mandatory,'N') classification_has_mandatory,
nvl(hoi.mandatory_flag,'N') info_type_mandatory,
xxen_per_upload.org_info_segment_labels(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,pbg.legislation_code) org_info_segment_labels,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION1',pbg.legislation_code) org_info_label1,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION1',hoi.hoi_rowid,hoi.org_information1,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information1,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION2',pbg.legislation_code) org_info_label2,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION2',hoi.hoi_rowid,hoi.org_information2,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information2,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION3',pbg.legislation_code) org_info_label3,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION3',hoi.hoi_rowid,hoi.org_information3,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information3,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION4',pbg.legislation_code) org_info_label4,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION4',hoi.hoi_rowid,hoi.org_information4,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information4,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION5',pbg.legislation_code) org_info_label5,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION5',hoi.hoi_rowid,hoi.org_information5,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information5,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION6',pbg.legislation_code) org_info_label6,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION6',hoi.hoi_rowid,hoi.org_information6,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information6,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION7',pbg.legislation_code) org_info_label7,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION7',hoi.hoi_rowid,hoi.org_information7,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information7,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION8',pbg.legislation_code) org_info_label8,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION8',hoi.hoi_rowid,hoi.org_information8,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information8,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION9',pbg.legislation_code) org_info_label9,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION9',hoi.hoi_rowid,hoi.org_information9,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information9,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION10',pbg.legislation_code) org_info_label10,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION10',hoi.hoi_rowid,hoi.org_information10,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information10,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION11',pbg.legislation_code) org_info_label11,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION11',hoi.hoi_rowid,hoi.org_information11,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information11,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION12',pbg.legislation_code) org_info_label12,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION12',hoi.hoi_rowid,hoi.org_information12,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information12,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION13',pbg.legislation_code) org_info_label13,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION13',hoi.hoi_rowid,hoi.org_information13,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information13,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION14',pbg.legislation_code) org_info_label14,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION14',hoi.hoi_rowid,hoi.org_information14,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information14,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION15',pbg.legislation_code) org_info_label15,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION15',hoi.hoi_rowid,hoi.org_information15,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information15,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION16',pbg.legislation_code) org_info_label16,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION16',hoi.hoi_rowid,hoi.org_information16,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information16,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION17',pbg.legislation_code) org_info_label17,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION17',hoi.hoi_rowid,hoi.org_information17,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information17,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION18',pbg.legislation_code) org_info_label18,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION18',hoi.hoi_rowid,hoi.org_information18,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information18,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION19',pbg.legislation_code) org_info_label19,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION19',hoi.hoi_rowid,hoi.org_information19,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information19,
xxen_per_upload.org_info_segment_label(xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3),hoi.displayed_org_information_type,'ORG_INFORMATION20',pbg.legislation_code) org_info_label20,
xxen_per_upload.display_org_info_value(hoi.org_information_context,'ORG_INFORMATION20',hoi.hoi_rowid,hoi.org_information20,pbg.business_group_id,haou.organization_id,hoi.org_information_id) org_information20,
pcak.segment1 cost_alloc_segment1,
pcak.segment2 cost_alloc_segment2,
pcak.segment3 cost_alloc_segment3,
pcak.segment4 cost_alloc_segment4,
pcak.segment5 cost_alloc_segment5,
pcak.segment6 cost_alloc_segment6,
pcak.segment7 cost_alloc_segment7,
pcak.segment8 cost_alloc_segment8,
pcak.segment9 cost_alloc_segment9,
pcak.segment10 cost_alloc_segment10,
pcak.segment11 cost_alloc_segment11,
pcak.segment12 cost_alloc_segment12,
pcak.segment13 cost_alloc_segment13,
pcak.segment14 cost_alloc_segment14,
pcak.segment15 cost_alloc_segment15,
pcak.segment16 cost_alloc_segment16,
pcak.segment17 cost_alloc_segment17,
pcak.segment18 cost_alloc_segment18,
pcak.segment19 cost_alloc_segment19,
pcak.segment20 cost_alloc_segment20,
pcak.segment21 cost_alloc_segment21,
pcak.segment22 cost_alloc_segment22,
pcak.segment23 cost_alloc_segment23,
pcak.segment24 cost_alloc_segment24,
pcak.segment25 cost_alloc_segment25,
pcak.segment26 cost_alloc_segment26,
pcak.segment27 cost_alloc_segment27,
pcak.segment28 cost_alloc_segment28,
pcak.segment29 cost_alloc_segment29,
pcak.segment30 cost_alloc_segment30,
0 upload_row
from
hr_all_organization_units haou,
hr_all_organization_units_tl haout,
per_business_groups pbg,
hr_locations_all hl,
pay_cost_allocation_keyflex pcak,
hr_organization_information hoi_class,
(select
hoi.org_information_id,
hoi.organization_id,
hoi.rowid hoi_rowid,
hoi.org_information_context,
hoi.org_information1,
hoi.org_information2,
hoi.org_information3,
hoi.org_information4,
hoi.org_information5,
hoi.org_information6,
hoi.org_information7,
hoi.org_information8,
hoi.org_information9,
hoi.org_information10,
hoi.org_information11,
hoi.org_information12,
hoi.org_information13,
hoi.org_information14,
hoi.org_information15,
hoi.org_information16,
hoi.org_information17,
hoi.org_information18,
hoi.org_information19,
hoi.org_information20,
hic.org_classification,
nvl(hic.mandatory_flag,'N') mandatory_flag,
hitl.displayed_org_information_type,
nvl(max(hic.mandatory_flag) over (partition by hoi.organization_id,hic.org_classification),'N') classification_has_mandatory,
first_value(xxen_util.meaning(hic.org_classification,'ORG_CLASS',3)) over (partition by hoi.organization_id,hoi.org_information_context order by hoi2.org_information2 desc,nvl(hic.mandatory_flag,'N') desc,xxen_util.meaning(hic.org_classification,'ORG_CLASS',3)) first_classification_name
from
hr_organization_information hoi,
hr_org_info_types_by_class hic,
hr_org_information_types hit,
hr_org_information_types_tl hitl,
hr_organization_information hoi2,
fnd_descr_flex_contexts fdfcx
where
hoi.org_information_context<>'CLASS' and
hoi.org_information_context=hit.org_information_type and
(hit.legislation_code=:p_legislation_code or hit.legislation_code is null) and
hit.navigation_method in ('GS','GM') and
hoi.org_information_context=hic.org_information_type and
nvl(hic.enabled_flag,'Y')='Y' and
hoi2.organization_id=hoi.organization_id and
hoi2.org_information_context='CLASS' and
hoi2.org_information1=hic.org_classification and
hit.org_information_type=hitl.org_information_type and
hitl.language=userenv('lang') and
fdfcx.application_id=800 and
fdfcx.descriptive_flexfield_name='Org Developer DF' and
fdfcx.descriptive_flex_context_code=hit.org_information_type and
fdfcx.enabled_flag='Y'
) hoi
where
1=1 and
:p_upload_mode like '%' || xxen_upload.action_update and
pbg.business_group_id=:p_business_group and
pbg.organization_id=haou.business_group_id and
haou.organization_id=haout.organization_id and
haout.language=userenv('lang') and
haou.location_id=hl.location_id(+) and
case when hoi.org_classification = 'HR_ORG' then haou.cost_allocation_keyflex_id end=pcak.cost_allocation_keyflex_id(+) and
haou.organization_id=hoi_class.organization_id(+) and
hoi_class.org_information_context(+)='CLASS' and
haou.organization_id=hoi.organization_id(+) and
hoi_class.org_information1=hoi.org_classification(+) and
xxen_util.meaning(hoi_class.org_information1,'ORG_CLASS',3)=hoi.first_classification_name(+)
Parameter NameSQL textValidation
Upload Mode
:p_upload_mode like '%' || xxen_upload.action_update
LOV
Business Group
 
LOV
Organization Type
haou.type=:p_organization_type
LOV
Organization Name
haout.name=:p_organization_name
LOV
Location
haou.location_id=:p_location
LOV
Organization Status
trunc(sysdate) between haou.date_from and nvl(haou.date_to,trunc(sysdate))
LOV
Classification
hoi_class.org_information1=:p_classification
LOV
Classification Status
decode(:p_class_status,'A','Y','I','N')=hoi_class.org_information2
LOV
Information Type
hoi.org_information_context=:p_information_type
LOV
Download
Blitz Report™