Queries for costing in Payroll

SELECT paf.ASSIGNMENT_NUMBER, ppf.FULL_NAME ,pet.element_name,PIV.NAME,
pca.CONCATENATED_SEGMENTS,
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
WHERE
PC.ASSIGNMENT_ACTION_ID =  PAV.ASSIGNMENT_ACTION_ID
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.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)
ORDER BY paf.ASSIGNMENT_NUMBER


5683 YOUSEF K. BALKIS 72.00   

SELECT * FROM PAY_COSTS
 WHERE ASSIGNMENT_ACTION_ID =4363428-- &assignment_action_id;

 SELECT * FROM
 PAY_ASSIGNMENT_ACTIONS
 WHERE PAYROLL_ACTION_ID = 837315
 AND ASSIGNMENT_ID = 8231

 SELECT * FROM PAY_PAYROLL_ACTIONS
 WHERE ACTION_TYPE = 'R'
 ORDER BY EFFECTIVE_DATE DESC

 AND PAYROLL_ID =

 SELECT PAYROLL_NAME, PAYROLL_iD FROM PAY_ALL_PAYROLLS_F

  PAY_PAYROLLS_V2
 
  SELECT * FROM PER_ALL_PEOPLE_F
  WHERE EMPLOYEE_NUMBER IN ('8158','4434','8932')
 
  SELECT ASSIGNMENT_ID
  FROM PER_ALL_ASSIGNMENTS_F
  WHERE PERSON_ID IN (SELECT PERSON_ID
  FROM PER_ALL_PEOPLE_F
  WHERE FULL_NAME LIKE 'LIJO%')
 
INSERT into FND_SESSIONS
values (userenv('sessionid'),SYSDATE);
 
8108

Cost Allocation Key Flexfield: FUJ_Cost_Allocation_Flexfield
The costing process picks up 'run results'. The specific 'payroll actions' that will be picked up by the costing process are in PAY_ACTION_CLASSIFICATION table and have a CLASSIFICATION_NAME of 'COSTED".

So the process will create an assignment_action for every assignment that has a run result from one of the 'costed' classifications.

SELECT * FROM PAY_COSTS WHERE ASSIGNMENT_ACTION_ID = &assignment_action_id;

The process then populates the PAY_COSTS table with the run result value (costed value). The actual cost code is stored on the PAY_COST_ALLOCATION_KEYFLEX table in the 'concatenated_segments' column, this is referenced using the 'cost_allocation_keyflex_id' from PAY_COSTS.


When the Payroll costing results are transferred to Oracle General Ledger only the mapped segments and the costing amounts are transferred. The data is transferred from the PAY_COSTS table into the GL_INTERFACE table by running the Oracle Payroll process Transfer to GL. After the transfer from Payroll is complete, the batch will need to be imported into GL. This is done by running the Oracle General Ledger standard GL Import process .

GL interface table checking:

SELECT gl.*
FROM pay_payroll_actions ppa,
    pay_assignment_actions paa,
    pay_costs pcv,
    gl_interface gl
WHERE ppa.payroll_action_id = :costing_payroll_action
AND   ppa.action_type='C'
AND pcv.transfer_to_gl_flag='Y'
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id=pcv.assignment_action_id
AND gl.user_je_source_name='Payroll'
AND gl.reference22=TO_CHAR(pcv.cost_allocation_keyflex_id));


Verify the data should be imported into gl_interfaces
Do a quick check

<pre>select * FROM gl_interface
WHERE set_of_books_id =1003 --(set_of_books_id)
AND GROUP_ID =123   -enter_group_id