Queries to retrive data from tables for FLEXFLIEDS

Retrieving data from tables for  FLEXFLIEDS .....

1. IF IT IS BANK DETAILS THEN QUERY FOR THE
SYSTEM ADMIN>APPLICATION>FLEXFIELD>KEY>SEGMENTS
APPLICATION > PAYROLL
FLEXFIELD TITLE> Bank Details KeyFlexField
SEARCH FOR THE CODE (STRUCTURE)> AE_BANK_DETAILS (or ur respective KFF)

2.
CLICK SEGMENTS
SEE FOR WHICH SEGMENT WE NEED AND WHAT VALUE SET IT IS ATTACHED.

3.
OPEN THE VALUE SET AND SEE THE TABLE QUERY
IT IS READING FROM WHICH LOOKUP CODE

SELECT APPLICATION_ID, ID_FLEX_CODE, ID_FLEX_NUM,ID_FLEX_STRUCTURE_CODE
 FROM apps.fnd_id_flex_structures ffs
 WHERE
  ffs.application_id = 800
 AND  FFS.ID_FLEX_STRUCTURE_CODE LIKE 'FUJ%'--STRUCTURE CODE   EX: FUJ_Grade_Flexfield
 --AND  ffs.id_flex_code = 'PEA' 

  SELECT A.* --SEGMENT4, A.FLEX_VALUE-- A.FLEX_VALUE_SET_ID, B.PARENT_FLEX_VALUE_SET_ID
   FROM FND_FLEX_VALUES_VL A
   WHERE  A.FLEX_VALUE_SET_ID = 1013710
  AND A.PARENT_FLEX_VALUE_LOW = 510000 --FOR DEPENDENT VALUES
  
4. If in Per_all_people_f, per_all_assignments_f screen then
search the columns for the flexfields values like soft_coding_flexfield

SELECT PERSON_ID, SEGMENT1 EMPLOYER, SEGMENT2 CIVIL_REG, SEGMENT3 SOCIAL_SEC
FROM HR_SOFT_CODING_KEYFLEX SCK,
PER_ALL_ASSIGNMENTS_F PAAF
WHERE PAAF.SOFT_CODING_KEYFLEX_ID = SCK.SOFT_CODING_KEYFLEX_ID
AND SYSDATE BETWEEN PAAF.EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND PERSON_ID = 25584
                    
Others:...........................
  select * from FND_FLEX_VALUE_SETS
  where FLEX_VALUE_SET_ID = 1013710
 
  AND PARENT_FLEX_VALUE_SET_ID = 1013709

 FLEX_VALUE_SET_NAME = 'FUJ_Sub_Account'--VALUE SET NAME

 FLEX_VALUE_SET_ID = 1013708 --FOREIGN KEY
 
 SELECT * FROM PAY_COST_ALLOCATION_KEYFLEX PCA --CONTAINS CONCATTED SEGMENTS, SEGMENT1, SEGMENT 2 ....

 SELECT * FROM FND_FLEX_VALUES_VL FND1--CONTAINS THE SEGMENT VALUES DESCRIPTION, VALUE


 WHERE FND1.FLEX_VALUE = PCA.SEGMENT1
  



Example:

SELECT paf.ASSIGNMENT_NUMBER, ppf.FULL_NAME ,pet.element_name,PIV.NAME,
hr_general.decode_organization (PAF.organization_id) ORG,
pca.CONCATENATED_SEGMENTS,SEG1.DESCRIPTION SECTOR,SEG2.DESCRIPTION DEPT,SEG3.DESCRIPTION ACCOUNT,
SEG4.DESCRIPTION SUB_ACC,
SEG5.DESCRIPTION PROGRAM, SEG6.DESCRIPTION FUT1, SEG7.DESCRIPTION FUT2,
decode(pc.DEBIT_OR_CREDIT,'D',pc.COSTED_VALUE) Debit,
decode(pc.DEBIT_OR_CREDIT,'C',pc.COSTED_VALUE) Credit
 FROM
PAY_ASSIGNMENT_ACTIONS PAV,
 PAY_COSTS PC,
 PAY_COST_ALLOCATION_KEYFLEX pca,
 pay_payroll_actions PPA,
 pay_run_results prr,
pay_run_result_values prrv,
pay_element_types_f pet,
PAY_INPUT_VALUES PIV,
per_ALL_people_f ppf,
per_ALL_assignments_f paf,
FND_FLEX_VALUES_VL SEG1,
FND_FLEX_VALUES_VL SEG2,
FND_FLEX_VALUES_VL SEG3,
FND_FLEX_VALUES_VL SEG4,
FND_FLEX_VALUES_VL SEG5,
FND_FLEX_VALUES_VL SEG6,
FND_FLEX_VALUES_VL SEG7
WHERE
PC.ASSIGNMENT_ACTION_ID =  PAV.ASSIGNMENT_ACTION_ID
AND SEG1.FLEX_VALUE_SET_ID = 1013707
AND SEG2.FLEX_VALUE_SET_ID = 1013708
AND SEG3.FLEX_VALUE_SET_ID = 1013709
AND SEG4.FLEX_VALUE_SET_ID = 1013710
AND SEG4.PARENT_FLEX_VALUE_LOW = 510000
AND SEG5.FLEX_VALUE_SET_ID = 1013711
AND SEG6.FLEX_VALUE_SET_ID = 1013712
AND SEG7.FLEX_VALUE_SET_ID = 1013713
AND SEG1.FLEX_VALUE = PCA.SEGMENT1
AND SEG2.FLEX_VALUE = PCA.SEGMENT2
AND SEG3.FLEX_VALUE = PCA.SEGMENT3
AND SEG4.FLEX_VALUE = PCA.SEGMENT4
AND SEG5.FLEX_VALUE = PCA.SEGMENT5
AND SEG6.FLEX_VALUE = PCA.SEGMENT6
AND SEG7.FLEX_VALUE = PCA.SEGMENT7
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRRV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
--AND PPA.PAYROLL_ACTION_ID = 839311
AND PPA.EFFECTIVE_DATE BETWEEN :FROM_DATE AND :TO_DATE
AND PPA.PAYROLL_ACTION_ID = PAV.PAYROLL_ACTION_ID
and pc.COST_ALLOCATION_KEYFLEX_ID=pca.COST_ALLOCATION_KEYFLEX_ID
and prr.RUN_RESULT_ID=prrv.RUN_RESULT_ID
and pc.RUN_RESULT_ID=prrv.RUN_RESULT_ID
and pc.INPUT_VALUE_ID=prrv.INPUT_VALUE_ID
and pet.ELEMENT_TYPE_ID=prr.ELEMENT_TYPE_ID
AND ppf.PERSON_ID=paf.PERSON_ID
and paf.ASSIGNMENT_ID=pav.ASSIGNMENT_ID
and paf.PRIMARY_FLAG='Y'
and ppf.EFFECTIVE_END_DATE = (select max(effective_end_date) from per_people_f where person_id=ppf.PERSON_ID)
and paf.EFFECTIVE_END_DATE = (select max(effective_end_date) from per_assignments_f where assignment_id=paf.ASSIGNMENT_ID)
--AND CONCATENATED_SEGMENTS IS NULL
ORDER BY paf.ASSIGNMENT_NUMBER