Queries for Costing in Payroll 2

   INSERT INTO fnd_sessions
        VALUES (USERENV ('SESSIONID'), TRUNC (SYSDATE));
      
        COMMIT


SELECT ELEMENT_LINK_ID,EFFECTIVE_START_DATE, EFFECTIVE_END_DATE,PAYROLL_ID, ORGANIZATION_ID,COST_ALLOCATION_KEYFLEX_ID, --Costing
ELEMENT_TYPE_ID,ELEMENT_NAME,
CLASSIFICATION_NAME,CLASSIFICATION_COSTABLE_FLAG,
BALANCING_KEYFLEX_ID, --Balancing
COSTABLE_TYPE,TRANSFER_TO_GL_FLAG
 FROM PAY_ELEMENT_LINKS_V
 WHERE
CLASSIFICATION_COSTABLE_FLAG = 'Y'
AND ELEMENT_NAME = 'Basic Salary'
AND PAYROLL_ID = 61

SELECT * FROM PAY_COST_ALLOCATION_KEYFLEX PCA --CONTAINS CONCATTED SEGMENTS, SEGMENT1, SEGMENT 2 ....
WHERE COST_ALLOCATION_KEYFLEX_ID = 1116 --from above step Cost_Allocation_keyflex_id


SELECT * FROM PAY_COST_ALLOCATION_KEYFLEX PCA --CONTAINS CONCATTED SEGMENTS, SEGMENT1, SEGMENT 2 ....
WHERE COST_ALLOCATION_KEYFLEX_ID = 4797 --from above step Balancing_keyflex_id

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



SELECT --ELEMENT_LINK_ID,EFFECTIVE_START_DATE, EFFECTIVE_END_DATE,PAYROLL_ID, ORGANIZATION_ID,PV.COST_ALLOCATION_KEYFLEX_ID, --Costing
--ELEMENT_TYPE_ID,
ELEMENT_NAME,
(SELECT ELEMENT_NAME FROM PAY_ELEMENT_TYPES_F_TL PAYTL
WHERE PAYTL.ELEMENT_TYPE_ID = PV.ELEMENT_TYPE_ID
AND LANGUAGE = 'AR')ARABIC_ELEMENT,
--CLASSIFICATION_NAME,--CLASSIFICATION_COSTABLE_FLAG,
PCOST.SEGMENT3 COST_ACCOUNT, PCOST.SEGMENT4 COST_SUBACCOUNT
,PBAL.SEGMENT3 BAL_ACCOUNT, PBAL.SEGMENT4 BAL_SUBACCOUNT
--BALANCING_KEYFLEX_ID, --Balancing
--COSTABLE_TYPE,TRANSFER_TO_GL_FLAG
 FROM PAY_ELEMENT_LINKS_V PV,
 PAY_COST_ALLOCATION_KEYFLEX PCOST,
 PAY_COST_ALLOCATION_KEYFLEX PBAL
 WHERE
CLASSIFICATION_COSTABLE_FLAG = 'Y'
--AND ELEMENT_NAME = 'Basic Salary'
AND PAYROLL_ID = 61
AND PV.COST_ALLOCATION_KEYFLEX_ID = PCOST.COST_ALLOCATION_KEYFLEX_ID
AND PV.BALANCING_KEYFLEX_ID = PBAL.COST_ALLOCATION_KEYFLEX_ID


SELECT HRV.NAME, HRV.ORGANIZATION_ID,
PCA.SEGMENT1 SECTOR,
PCA.SEGMENT2 DEPARTMENT,
PCA.SEGMENT5 PROGRAM
FROM
HR_ORGANIZATION_UNITS_V HRV,
PAY_COST_ALLOCATION_KEYFLEX PCA
WHERE
PCA.COST_ALLOCATION_KEYFLEX_ID = HRV.COST_ALLOCATION_KEYFLEX_ID
AND PCA.SEGMENT1 = '022000'--'022002'
--AND ORGANIZATION_ID = 108
ORDER BY 2

SELECT PAYROLL_ID, PAYROLL_NAME, PCA.SEGMENT1 COST_SECTOR, PCA.SEGMENT2 COST_DEPARTMENT,
PSUS.SEGMENT1 SUSPENSE_SECTOR,
PSUS.SEGMENT2 SUSPENSE_DEPT,
PSUS.SEGMENT3 SUSPENSE_ACC,
PSUS.SEGMENT4 SUSPENSE_SUB_ACC
FROM PAY_ALL_PAYROLLS_F PAPF,
PAY_COST_ALLOCATION_KEYFLEX PCA
,PAY_COST_ALLOCATION_KEYFLEX PSUS
WHERE
PCA.COST_ALLOCATION_KEYFLEX_ID = PAPF.COST_ALLOCATION_KEYFLEX_ID
AND PAYROLL_ID = 61
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PSUS.COST_ALLOCATION_KEYFLEX_ID = PAPF.SUSPENSE_ACCOUNT_KEYFLEX_ID



Query to get the Organization Information Attached as 'HR Organization'

SELECT HRV.ORGANIZATION_ID, HRV.NAME FROM
HR_ORGANIZATION_UNITS_V HRV
,HR_ORGANIZATION_INFORMATION_V HINFV
WHERE HRV.COST_ALLOCATION_KEYFLEX_ID IS NULL
AND HRV.DATE_TO IS NULL
AND HRV.ORGANIZATION_ID= HINFV.ORGANIZATION_ID
AND HINFV.ORG_INFORMATION1 = 'HR_ORG'


SELECT * FROM
HR_ORGANIZATION_INFORMATION_V
WHERE ORGANIZATION_ID = 108

SELECT HRV.ORGANIZATION_ID, HRV.NAME FROM
HR_ORGANIZATION_UNITS_V HRV
,HR_ORGANIZATION_INFORMATION_V HINFV
WHERE HRV.COST_ALLOCATION_KEYFLEX_ID IS NULL
AND HRV.DATE_TO IS NULL
AND HRV.ORGANIZATION_ID= HINFV.ORGANIZATION_ID
AND HINFV.ORG_INFORMATION1 = 'HR_ORG'
AND HRV.ORGANIZATION_ID IN
(SELECT DISTINCT PAAF.ORGANIZATION_ID,PERSON_ID FROM PER_ALL_ASSIGNMENTS_F PAAF
WHERE PAAF.PAYROLL_iD = 61
AND ORGANIZATION_ID = 1707
)

Query to get Contact / Relationship Information in Oracle HRMS R12

SELECT papf.person_id employee_id, papf.full_name employee_name,
papf.effective_start_date employee_start_date,
papf.effective_end_date employee_end_date,
papf_cont.full_name contact_name, hl.meaning contact_type,
pcr.date_start contact_start_date, pcr.date_end contact_end_date
FROM per_contact_relationships pcr,
per_all_people_f papf,
hr_lookups hl,
per_all_people_f papf_cont
WHERE 1 = 1
AND papf.person_id = pcr.person_id
AND pcr.contact_person_id = papf_cont.person_id
--AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
--AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
and trunc(sysdate) between papf.EFFECTIVE_START_DATE and papf.effective_end_date
and trunc(sysdate) between papf_cont.EFFECTIVE_START_DATE and papf_cont.effective_end_date
AND hl.lookup_type(+) = 'CONTACT'
AND hl.lookup_code(+) = pcr.contact_type
and papf.employee_number = '9213'