Search for:
Jan 6 2023
Configuration – Extract Table based value set information

Use below query to extract table based value set information from backend tables:

SELECT ffvs.flex_value_set_name
      ,ffvs.description vale_set_desc
      ,ffvs.validation_type
      ,ffvs.format_type
      ,ffvs.maximum_size
      ,ffvs.number_precision
      ,ffvs.alphanumeric_allowed_flag
      ,ffvs.uppercase_only_flag
      ,ffvt.value_column_name	  
      ,ffvt.value_column_type
      ,ffvt.value_column_size
      ,ffvt.meaning_column_name
      ,ffvt.meaning_column_type
      ,ffvt.meaning_column_size
      ,ffvt.id_column_name
      ,ffvt.id_column_type
      ,ffvt.id_column_size
      ,ffvt.enabled_column_name
      ,ffvt.start_date_column_name
      ,ffvt.end_date_column_name
      ,ffvt.summary_column_name
      ,ffvt.application_table_name
      ,ffvt.additional_where_clause
      ,ffvt.additional_quickpick_columns
  FROM fnd_flex_value_sets ffvs
      ,fnd_flex_validation_tables ffvt
 WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id
   AND ffvs.flex_value_set_name LIKE 'XX%'
Mandeep Gupta 1 Comments
Oct 6 2022
BIP – Query to extract DOR attachment name
select papf.person_number 
      ,hdor.document_code
      ,hdor.document_name
      ,hdor.document_number
      ,fdt.file_name
      ,fdt.dm_version_number document_id
      ,fdt.dm_document_id UCM_file
 from fnd_attached_documents fad, hr_documents_of_record hdor, fnd_documents_tl fdt
   ,per_all_people_f papf
   ,hr_document_types_tl hdtt
 where ENTITY_NAME = 'HR_DOCUMENTS_OF_RECORD'
  and hdor.documents_of_record_id = fad.PK1_VALUE
  and fad.document_id = fdt.document_id
  and fdt.language = 'US'
  and hdor.person_id = papf.person_id
  and TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
  and hdor.document_type_id = hdtt.document_type_id
  and hdtt.language = 'US'
  and hdtt.DOCUMENT_TYPE = 'Test Doc'
Mandeep Gupta 0 Comments
Sep 8 2022
HDL – Assignment Working Hour Pattern

Oracle has provided a new feature where working hours for each day can be stored against assignment working hours.

The data is stored in PER_WORKING_HOUR_PATTERNS_F table.

Use below query to extract the data:

SELECT papf.person_number, pwhpf.* 
  FROM PER_WORKING_HOUR_PATTERNS_F pwhpf
      ,PER_ALL_ASSIGNMENTS_M paam
	  ,PER_ALL_PEOPLE_F papf
 WHERE pwhpf.OBJECT_ID = paam.ASSIGNMENT_ID
   AND paam.PERSON_ID = papf.PERSON_ID
   AND papf.person_number = '10011'
   AND paam.assignment_type = 'E'
   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
   AND paam.effective_start_date BETWEEN pwhpf.effective_start_date AND pwhpf.effective_end_date

Query to extract data in HDL format:

SELECT
'METADATA|WorkingHourPattern|WorkingHourPatternId|EffectiveStartDate|EffectiveEndDate|Object|ObjectId|AssignmentNumber|PersonId|ReplaceFirstEffectiveStartDate' as DATA_ROW, 1 ORDERBY From dual
UNION
SELECT 'MERGE|WorkingHourPattern|'
|| pwhpf.WORKING_HOUR_PATTERN_ID
|| '|'
|| TO_CHAR(pwhpf.EFFECTIVE_START_DATE,'YYYY/MM/DD')
|| '|'
|| TO_CHAR(pwhpf.EFFECTIVE_END_DATE,'YYYY/MM/DD')
|| '|ASSIGNMENT|'
|| pwhpf.object_id
|| '|'
|| paam.assignment_number
|| '|'
|| paam.person_id
|| '|'
|| 'Y'
 as DATA_ROW, 2 ORDERBY
FROM PER_WORKING_HOUR_PATTERNS_F pwhpf
    ,PER_ALL_ASSIGNMENTS_M paam
WHERE paam.assignment_id = pwhpf.object_id
  AND paam.assignment_type = 'E'
  AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
  AND paam.effective_start_date BETWEEN pwhpf.effective_start_date AND pwhpf.effective_end_date;
Mandeep Gupta 0 Comments