[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
The p_a_interface() logic will insert pay data to the PA_Transaction_Interface
table.Details to be inserted will be passed in as parameters to a function
called p_a_interface.Because the interface to Project Accounting will vary on
different installations,the p_a_interface logic will be stored in the
HXT_USER_EXITS package.HXT_USER_EXITS is designed to contain unique code,
specific to a customers needs.
Following is a list of parameters to the p_a_interface function and the source
of each argument:
i_hours_worked -- HXT_det_hours_worked_x.hours
i_rate -- per_pay_proposals.proposed_salary
(employees hourly rate)
or
hxt_det_hours_worked_x.hourly_rate
(timecard override hourly rate)
or
per_pay_proposals.proposed_salary *
(employees hourly rate)
hxt_det_hours_worked_x.rate_multiple
(manually entered multiple from timecard)
or
per_pay_proposals.proposed_salary *
(employees hourly rate)
hxt_pay_element_types_f_ddf_v.hxt_premium_amount
(multiple from pay element flex)
or
hxt_pay_element_types_f_ddf_v.hxt_premium_amount/
(daily amount from element flex)
hxt_det_hours_worked_x.hours
(hours worked)
or
per_pay_proposals.proposed_salary *
(employees hourly rate)
hxt_pay_element_types_f_ddf_v.hxt_premium_amount - 1.0
(non-ot premium multiple from pay element flex minus 1.0)
i_premium_amount -- hxt_pay_element_types_f_ddf_v.hxt_premium_amount
(premium amount element flex)
or
hxt_det_hours_worked_x.amount (timecard override amount)
i_trans_source -- hxt_pay_element_types_f_ddf_v.hxt_earning_category
||hxt_pay_element_types_f_ddf_v.hxt_premium_type
i_period_end -- per_time_periods.end_date
i_employee_number-- per_people_f.employee_number
--SIR162i_employment_cat -- fnd_common_lookups.meaning(lookup_type = 'EMP_CAT')
--SIR162i_emp_cat_code -- per_assignments_f.employment_category
i_oganization_name -- hr_organization_units.name
i_organization_id -- hr_organization_units.id
i_date_worked -- hxt_det_hours_worked_x.date_worked
i_effective_start_date -- hxt_det_hours_worked_x.effective_start_date
i_effective_end_date -- hxt_det_hours_worked_x.effective_end_date
i_hours_type -- hxt_det_hours_worked_x.element_name
i_salary_basis -- per_pay_proposals_v.pay_basis
i_time_detail_id -- hxt_det_hours_worked_x.id
i_hxt_earning_category -- hxt_pay_element_types_f_ddf_v.hxt_earning_category
i_retro_transaction -- TRUE if Retro Transaction
-- FALSE if Normal Transaction
i_standard_rate -- per_pay_proposals.proposed_salary
-- (employees unmodified base hourly rate)
i_project_id -- hxt_det_hours_worked_x.project_id
i_task_id -- hxt_det_hours_worked_x.task_id
i_segment1 -- pa_projects.segment1
i_task_number -- pa_tasks.task_number
i_project_name -- pa_projects.name
i_task_name -- pa_tasks.task_name
i_assignment_id -- per_assignments_f.assignment_id
i_cost_allocation_keyflex_id -- pay_cost_allocation_keyflex.cost_allocation_keyflex_id
i_job_definition_id -- per_job_definitions.job_definition_id
*******************************************************************************/
FUNCTION p_a_interface( i_hours_worked IN NUMBER
,i_rate IN NUMBER
,i_premium_amount IN NUMBER
,i_trans_source IN VARCHAR2
,i_period_end IN DATE
,i_employee_number IN VARCHAR2
,i_employment_cat IN VARCHAR2
,i_element_type_id IN NUMBER --SIR162
--SIR162 ,i_emp_cat_code IN VARCHAR2
,i_organization_name IN VARCHAR2
,i_organization_id IN NUMBER
,i_date_worked IN DATE
,i_effective_start_date IN DATE
,i_effective_end_date IN DATE
,i_hours_type IN VARCHAR2
,i_salary_basis IN VARCHAR2
,i_time_detail_id IN NUMBER
,i_hxt_earning_category IN VARCHAR2
,i_retro_transaction IN BOOLEAN
,i_standard_rate IN NUMBER
,i_project_id IN NUMBER
,i_task_id IN NUMBER
,i_segment1 IN VARCHAR2
,i_task_number IN VARCHAR2
,i_project_name IN VARCHAR2
,i_task_name IN VARCHAR2
,i_assignment_id IN NUMBER
,i_cost_allocation_keyflex_id IN NUMBER
,i_job_definition_id IN NUMBER
,o_location OUT NOCOPY VARCHAR2
,o_error_text OUT NOCOPY VARCHAR2
,o_system_text OUT NOCOPY VARCHAR2)
RETURN NUMBER IS
-- Define local variables for each coloumn to ease customizations
l_trans_source pa_transaction_interface.transaction_source%TYPE DEFAULT NULL;
SELECT eltv.hxt_expenditure_type
FROM hxt_pay_element_types_f_ddf_v eltv
WHERE eltv.element_type_id = i_element_type_id
AND i_date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date;
SELECT hoi.org_information10
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'Business Group Information'
AND hoi.organization_id = i_organization_id;
SELECT for_person_id
INTO l_resource_id
FROM hxt_timecards_x tim
WHERE tim.id = (SELECT det.tim_id
FROM hxt_det_hours_worked_x det
WHERE det.id = i_time_detail_id
);
INSERT INTO pa_transaction_interface_all
( TRANSACTION_SOURCE
,BATCH_NAME
,EXPENDITURE_ENDING_DATE
,EMPLOYEE_NUMBER
,ORGANIZATION_NAME
,EXPENDITURE_ITEM_DATE
,PROJECT_NUMBER
,TASK_NUMBER
,EXPENDITURE_TYPE
,NON_LABOR_RESOURCE
,NON_LABOR_RESOURCE_ORG_NAME
,QUANTITY
,RAW_COST
,EXPENDITURE_COMMENT
,TRANSACTION_STATUS_CODE
,TRANSACTION_REJECTION_CODE
,EXPENDITURE_ID
,ORIG_TRANSACTION_REFERENCE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,RAW_COST_RATE
,DENOM_CURRENCY_CODE
,DENOM_RAW_COST
,INTERFACE_ID
,UNMATCHED_NEGATIVE_TXN_FLAG
,EXPENDITURE_ITEM_ID
,ORG_ID
,DR_CODE_COMBINATION_ID
,CR_CODE_COMBINATION_ID
,CDL_SYSTEM_REFERENCE1
,CDL_SYSTEM_REFERENCE2
,CDL_SYSTEM_REFERENCE3
,GL_DATE)
VALUES( l_trans_source
,l_batch_name
-- need the NVL on the following line where
-- pa_misc.get_week_ending_date() might return NULL.
-- This would happen in Columbia,or anywhere else that ExpEndDates
-- not set up in ProjAccting.However,i_period_end will be WRONG for
-- any Payroll Type other than Weekly.SIR151
,nvl(l_expenditure_ending_date,i_period_end) --SIR151
,l_employee_number
,l_organization_name
,l_expenditure_item_date
,l_project_number
,l_task_number
,l_expenditure_type
,l_non_labor_resource
,l_non_labor_resource_org_name
,l_quantity
,l_raw_cost
,l_expenditure_comment
,l_transaction_status_code
,l_transaction_rejection_code
,l_expenditure_id
,l_orig_transaction_reference
,l_attribute_category
,l_attribute1
,l_attribute2
,l_attribute3
,l_attribute4
,l_attribute5
,l_attribute6
,l_attribute7
,l_attribute8
,l_attribute9
,l_attribute10
,l_raw_cost_rate
--Bug2177304 ,'USD'
,l_denom_currency_code
,l_raw_cost
,l_interface_id
,l_unmatched_negative_txn_flag
,l_expenditure_item_id
--Bug:5559930,ORG_ID
,l_operating_unit_id
,l_dr_code_combination_id
,l_cr_code_combination_id
,l_cdl_system_reference1
,l_cdl_system_reference2
,l_cdl_system_reference3
,l_gl_date);
HXT_UTIL.DEBUG('after insert to pa_transaction_interface.'); --HXT115