Below are use full API in HRMS most of time these API are used to migrate external data from legacy system to oracle apps Updating the Per_periods_of_service table using hr_ex_employee_api.update_term_details_emp Terminate employee hr_ex_employee_api.actual_termination_emp Applying the final process in the shared instance using hr_ex_employee_api.final_process_emp Re – Hire Employee hr_employee_api.re_hire_ex_employee Update Existing Person in system hr_person_api.update_us_person Hire New Emp hr_employee_api.create_us_employee Costing pay_cost_allocation_api.create_cost_allocation Load Assignment hr_assignment_api.update_us_emp_asg Load Update Assign Criteria hr_assignment_api.update_emp_asg_criteria Note : Contact Person Id Is To Be Passed hr_contact_rel_api.create_contact Note : Contact Person Id is passed as Null(default of API) hr_contact_rel_api.create_contact Load Phones hr_phone_api.create_phone hr_person_address_api.update_person_address Load Addresses hr_person_address_api.update_person_address hr_person_address_api.create_person_address Load Payment Methods hr_personal_pay_method_api.create_us_personal_pay_method Element Loading py_element_entry_api.create_element_entry py_element_entry_api.update_element_entry Load Salaries hr_upload_proposal_api.upload_salary_proposal Salary Proposal hr_maintain_proposal_api.approve_salary_proposal State tax rules pay_state_tax_rule_api.create_state_tax_rule pay_state_tax_rule_api.update_state_tax_rule County Tax Rules pay_county_tax_rule_api.create_county_tax_rule pay_county_tax_rule_api.update_county_tax_rule City Tax Rules pay_city_tax_rule_api.create_city_tax_rule pay_city_tax_rule_api.update_city_tax_rule Schools and Colleges per_esa_upd.upd per_esa_ins.ins Performance Reviews hr_perf_review_api.create_perf_review hr_perf_review_api.update_perf_review State Information Taxes hr_sit_api.update_sit hr_sit_api.create_sit Qualifications per_qualifications_api.create_qualification per_qualifications_api.update_qualification Locations hr_location_api.update_location hr_location_api.create_location Organization hr_organization_api.update_organization hr_organization_api.create_org_classification If any Change in Organization information. if information2 = 'Y' then hr_organization_api.enable_org_classification If any Change in Organization information. if information2 = 'N' then hr_organization_api.disable_org_classification If Organization does not exist in instance hr_organization_api.create_organization api Jobs hr_job_api.update_job hr_job_api.create_job Positions hr_position_api.update_position hr_position_api.create_position |
Tuesday, December 7, 2010
HRMS APIs
How to load monthly timesheet details
PROCEDURE xx_element_creation_proc (
p_errbuf OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY NUMBER,
p_effective_start_date IN VARCHAR2,
p_effective_end_date IN VARCHAR2
)
AS
CURSOR cur_asg_emp (
p_effective_start_date IN DATE,
p_effective_end_date IN DATE
)
IS
SELECT DISTINCT emp_no
FROM xx_time_sheet
WHERE work_date BETWEEN TO_DATE (p_effective_start_date)
AND TO_DATE (p_effective_end_date)
AND last_run_date IS NULL;
--Declare Variables
lv_proceed VARCHAR2 (1) := 'Y';
ln_ot NUMBER := 0;
ln_sot NUMBER := 0;
ln_tardi NUMBER := 0;
ln_flexi NUMBER := 0;
lv_error_message VARCHAR2 (300) := NULL;
ln_group_code NUMBER;
ln_assignment_id NUMBER;
ln_tardi_time NUMBER;
ln_tardi_hrs NUMBER;
ln_ot_hrs NUMBER;
ln_sot_hrs NUMBER;
ln_input_value_ot NUMBER;
ln_input_value_sot NUMBER;
ln_input_value_tar NUMBER;
ln_ot_link_id NUMBER;
ld_effective_start_date DATE;
ld_effective_end_date DATE;
ln_element_entry_id NUMBER;
ln_object_version_number NUMBER;
lb_create_warning BOOLEAN;
ln_sot_link_id NUMBER;
ln_tardi_link_id NUMBER;
ln_bg_id NUMBER
:= fnd_profile.VALUE ('Business_Group_ID');
ld_effective_date DATE;
lv_ot_eligibility VARCHAR2 (4);
ld_doj DATE;
BEGIN
SELECT TRUNC (TO_DATE (p_effective_end_date), 'Month')
INTO ld_effective_date
FROM DUAL;
FOR cur_rec IN cur_asg_emp (p_effective_start_date, p_effective_end_date)
LOOP
--Reset Variables
lv_proceed := 'Y';
ln_ot := 0;
ln_sot := 0;
ln_tardi := 0;
ln_flexi := 0;
lv_error_message := NULL;
ln_group_code := NULL;
ln_assignment_id := NULL;
ln_tardi_time := 0;
ln_tardi_hrs := 0;
ln_ot_hrs := 0;
ln_sot_hrs := 0;
ln_input_value_ot := NULL;
ln_input_value_sot := NULL;
ln_input_value_tar := NULL;
ln_ot_link_id := NULL;
ld_effective_start_date := NULL;
ld_effective_end_date := NULL;
ln_element_entry_id := NULL;
ln_object_version_number := NULL;
lb_create_warning := NULL;
ln_sot_link_id := NULL;
ln_tardi_link_id := NULL;
lv_ot_eligibility := NULL;
ld_doj := NULL;
-----Calculate Timesheet Data
BEGIN
SELECT ROUND
(xx_seconds_to_hrs
(SUM (xx_hours_in_seconds (xxm.over_time)
)
),
2
) over_time,
ROUND
(xx_seconds_to_hrs
(SUM (xx_hours_in_seconds (xxm.special_over_time)
)
),
2
) special_over_time,
ROUND
(xx_seconds_to_hrs
(SUM (xx_hours_in_seconds (xxm.tardiness_time)
)
),
2
) tardiness_time,
ROUND
(xx_seconds_to_hrs
(SUM (xx_hours_in_seconds (xxm.flex_hours)
)
),
2
) flexi_hours
INTO ln_ot,
ln_sot,
ln_tardi,
ln_flexi
FROM xx_time_sheet xxm
WHERE emp_no = cur_rec.emp_no
AND work_date BETWEEN TO_DATE (p_effective_start_date)
AND TO_DATE (p_effective_end_date)
GROUP BY emp_no;
EXCEPTION
WHEN OTHERS
THEN
lv_error_message := 'Timesheet Data Issue. Check the data';
lv_proceed := 'N';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
END;
--Decode for group id
BEGIN
SELECT NVL (DECODE (paaf.people_group_id,
61, 3,
62, 2,
63, 1,
paaf.people_group_id
),
0
) "group_code",
paaf.assignment_id, paaf.ass_attribute1 ot_eligibility,
papf.original_date_of_hire
INTO ln_group_code,
ln_assignment_id, lv_ot_eligibility,
ld_doj
FROM per_all_people_f papf, per_all_assignments_f paaf
WHERE paaf.person_id = papf.person_id
AND p_effective_end_date BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND p_effective_end_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND papf.employee_number = cur_rec.emp_no;
IF ln_group_code = 0
THEN
lv_error_message := 'Please enter people group for the employee';
lv_proceed := 'N';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
END IF;
IF ld_doj > ld_effective_date
THEN
ld_effective_date := ld_doj;
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_message :=
'Error in getting people group. Check the data';
lv_proceed := 'N';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
END;
IF ln_group_code = 2 AND NVL (lv_ot_eligibility, 'N') = 'N'
THEN
ln_tardi_time := (ln_tardi + ln_flexi) - (ln_ot + ln_sot);
lv_proceed := 'N';
IF ln_tardi_time > 0
THEN
lv_proceed := 'Y';
ln_tardi_hrs := ln_tardi_time;
END IF;
ELSIF ln_group_code = 2 AND NVL (lv_ot_eligibility, 'N') = 'Y'
THEN
IF ln_ot >= ln_flexi
THEN
ln_ot_hrs := ln_ot - ln_flexi;
ln_sot_hrs := ln_sot;
ln_tardi_hrs := ln_tardi;
ELSIF ln_ot < ln_flexi
THEN
IF (ln_ot + ln_sot) >= ln_flexi
THEN
ln_ot_hrs := 0;
ln_sot_hrs := (ln_ot + ln_sot) - ln_flexi;
ln_tardi_hrs := ln_tardi;
ELSE
ln_ot_hrs := 0;
ln_sot_hrs := 0;
ln_tardi_hrs := (ln_tardi + ln_flexi) - (ln_ot + ln_sot);
END IF;
END IF;
ELSIF ln_group_code = 1
THEN
lv_error_message := 'No Element Creation for Managers';
lv_proceed := 'N';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
ELSE
ln_ot_hrs := ln_ot;
ln_sot_hrs := ln_sot;
ln_tardi_hrs := ln_tardi;
END IF;
BEGIN
SELECT input_value_id
INTO ln_input_value_ot
FROM pay_input_values_f pi, pay_element_types_f pe
WHERE pi.NAME = 'Total Hours'
AND pi.element_type_id = pe.element_type_id
AND pe.element_name = 'Overtime';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
lv_error_message := 'More than one Hours input is defined';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
lv_proceed := 'N';
WHEN OTHERS
THEN
lv_error_message := 'Error in getting Hours Input ID';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
lv_proceed := 'N';
END;
BEGIN
SELECT input_value_id
INTO ln_input_value_sot
FROM pay_input_values_f pi, pay_element_types_f pe
WHERE pi.NAME = 'Total Hours'
AND pi.element_type_id = pe.element_type_id
AND pe.element_name = 'Special Overtime';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
lv_error_message := 'More than one Hours input is defined';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
lv_proceed := 'N';
WHEN OTHERS
THEN
lv_error_message := 'Error in getting Hours Input ID';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
lv_proceed := 'N';
END;
BEGIN
SELECT input_value_id
INTO ln_input_value_tar
FROM pay_input_values_f pi, pay_element_types_f pe
WHERE pi.NAME = 'Hours'
AND pi.element_type_id = pe.element_type_id
AND pe.element_name = 'Tardiness';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
lv_error_message := 'More than one Hours input is defined';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
lv_proceed := 'N';
WHEN OTHERS
THEN
lv_error_message := 'Error in getting Hours Input ID';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
lv_proceed := 'N';
END;
IF ln_ot_hrs > 0 AND lv_proceed = 'Y'
THEN --Create Element entry for Over Time.
BEGIN
SELECT pelf.element_link_id
INTO ln_ot_link_id
FROM pay_element_types_f petf, pay_element_links_f pelf
WHERE petf.element_type_id = pelf.element_type_id
AND petf.element_name = 'Overtime';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
lv_error_message :=
'More than one Overtime element is defined';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
--lv_proceed = 'N' ;
NULL;
WHEN OTHERS
THEN
lv_error_message := 'Error in getting OT link ID';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
-- lv_proceed = 'N' ;
NULL;
END;
--Call element creation API
BEGIN
pay_element_entry_api.create_element_entry
(p_effective_date => ld_effective_date,
p_business_group_id => ln_bg_id,
p_assignment_id => ln_assignment_id
--p_assignment_id
,
p_element_link_id => ln_ot_link_id,
p_entry_type => 'E' --For Entry
,
p_input_value_id1 => ln_input_value_ot
--Input Value ID for Hours
,
p_entry_value1 => ln_ot_hrs,
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_element_entry_id => ln_element_entry_id,
p_object_version_number => ln_object_version_number,
p_create_warning => lb_create_warning
);
UPDATE xx_time_sheet
SET last_run_date = SYSDATE
WHERE emp_no = cur_rec.emp_no;
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no
|| ' ~ '
|| 'Overtime Element Created ~ '
|| ln_ot_hrs
);
EXCEPTION
WHEN OTHERS
THEN
lv_error_message := 'Error in OT element creation - API Error';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no
|| ' ~ '
|| lv_error_message
|| ' ~ '
|| SUBSTR (SQLERRM, 1, 90)
);
END;
END IF;
IF ln_sot_hrs > 0 AND lv_proceed = 'Y'
THEN --Create element entry for Special Over Time.
BEGIN
SELECT pelf.element_link_id
INTO ln_sot_link_id
FROM pay_element_types_f petf, pay_element_links_f pelf
WHERE petf.element_type_id = pelf.element_type_id
AND petf.element_name = 'Special Overtime';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
lv_error_message :=
'More than one Special Overtime element is defined';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
--lv_proceed = 'N' ;
NULL;
WHEN OTHERS
THEN
lv_error_message := 'Error in getting SOT link ID';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
-- lv_proceed = 'N' ;
NULL;
END;
BEGIN
pay_element_entry_api.create_element_entry
(p_effective_date => ld_effective_date,
p_business_group_id => ln_bg_id,
p_assignment_id => ln_assignment_id
--p_assignment_id
,
p_element_link_id => ln_sot_link_id,
p_entry_type => 'E' --For entry
,
p_input_value_id1 => ln_input_value_sot
--Input Value ID for Hours
,
p_entry_value1 => ln_sot_hrs,
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_element_entry_id => ln_element_entry_id,
p_object_version_number => ln_object_version_number,
p_create_warning => lb_create_warning
);
UPDATE xx_time_sheet
SET last_run_date = SYSDATE
WHERE emp_no = cur_rec.emp_no;
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no
|| ' ~ '
|| 'Special Overtime Element Created ~ '
|| ln_sot_hrs
);
EXCEPTION
WHEN OTHERS
THEN
lv_error_message :=
'Error in SOT element creation - API Error';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no
|| ' ~ '
|| lv_error_message
|| ' ~ '
|| SUBSTR (SQLERRM, 1, 90)
);
END;
END IF;
IF ln_tardi_hrs > 0 AND lv_proceed = 'Y'
THEN --Create element entry for Special Over Time.
BEGIN
SELECT pelf.element_link_id
INTO ln_tardi_link_id
FROM pay_element_types_f petf, pay_element_links_f pelf
WHERE petf.element_type_id = pelf.element_type_id
AND petf.element_name = 'Tardiness';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
lv_error_message :=
'More than one Tardiness element is defined';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
--lv_proceed = 'N' ;
NULL;
WHEN OTHERS
THEN
lv_error_message := 'Error in getting OT link ID';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
-- lv_proceed = 'N' ;
NULL;
END;
BEGIN
pay_element_entry_api.create_element_entry
(p_effective_date => ld_effective_date,
p_business_group_id => ln_bg_id,
p_assignment_id => ln_assignment_id
--p_assignment_id
,
p_element_link_id => ln_tardi_link_id,
p_entry_type => 'E' --For Entry
,
p_input_value_id1 => ln_input_value_tar
--Input Value ID for Hours
,
p_entry_value1 => ln_tardi_hrs,
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_element_entry_id => ln_element_entry_id,
p_object_version_number => ln_object_version_number,
p_create_warning => lb_create_warning
);
UPDATE xx_time_sheet
SET last_run_date = SYSDATE
WHERE emp_no = cur_rec.emp_no;
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no
|| ' ~ '
|| 'Tardiness Element Created ~ '
|| ln_tardi_hrs
);
EXCEPTION
WHEN OTHERS
THEN
lv_error_message :=
'Error in Tardi element creation - API Error';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no
|| ' ~ '
|| lv_error_message
|| ' ~ '
|| SUBSTR (SQLERRM, 1, 90)
);
END;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Fatal Error' || ' ~ ' || SUBSTR (SQLERRM, 1, 60)
);
p_retcode := 3;
END xx_element_creation_proc;
-----------------------------------------------------------------------------------------------------------------------------------
--Element Creation - End
p_errbuf OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY NUMBER,
p_effective_start_date IN VARCHAR2,
p_effective_end_date IN VARCHAR2
)
AS
CURSOR cur_asg_emp (
p_effective_start_date IN DATE,
p_effective_end_date IN DATE
)
IS
SELECT DISTINCT emp_no
FROM xx_time_sheet
WHERE work_date BETWEEN TO_DATE (p_effective_start_date)
AND TO_DATE (p_effective_end_date)
AND last_run_date IS NULL;
--Declare Variables
lv_proceed VARCHAR2 (1) := 'Y';
ln_ot NUMBER := 0;
ln_sot NUMBER := 0;
ln_tardi NUMBER := 0;
ln_flexi NUMBER := 0;
lv_error_message VARCHAR2 (300) := NULL;
ln_group_code NUMBER;
ln_assignment_id NUMBER;
ln_tardi_time NUMBER;
ln_tardi_hrs NUMBER;
ln_ot_hrs NUMBER;
ln_sot_hrs NUMBER;
ln_input_value_ot NUMBER;
ln_input_value_sot NUMBER;
ln_input_value_tar NUMBER;
ln_ot_link_id NUMBER;
ld_effective_start_date DATE;
ld_effective_end_date DATE;
ln_element_entry_id NUMBER;
ln_object_version_number NUMBER;
lb_create_warning BOOLEAN;
ln_sot_link_id NUMBER;
ln_tardi_link_id NUMBER;
ln_bg_id NUMBER
:= fnd_profile.VALUE ('Business_Group_ID');
ld_effective_date DATE;
lv_ot_eligibility VARCHAR2 (4);
ld_doj DATE;
BEGIN
SELECT TRUNC (TO_DATE (p_effective_end_date), 'Month')
INTO ld_effective_date
FROM DUAL;
FOR cur_rec IN cur_asg_emp (p_effective_start_date, p_effective_end_date)
LOOP
--Reset Variables
lv_proceed := 'Y';
ln_ot := 0;
ln_sot := 0;
ln_tardi := 0;
ln_flexi := 0;
lv_error_message := NULL;
ln_group_code := NULL;
ln_assignment_id := NULL;
ln_tardi_time := 0;
ln_tardi_hrs := 0;
ln_ot_hrs := 0;
ln_sot_hrs := 0;
ln_input_value_ot := NULL;
ln_input_value_sot := NULL;
ln_input_value_tar := NULL;
ln_ot_link_id := NULL;
ld_effective_start_date := NULL;
ld_effective_end_date := NULL;
ln_element_entry_id := NULL;
ln_object_version_number := NULL;
lb_create_warning := NULL;
ln_sot_link_id := NULL;
ln_tardi_link_id := NULL;
lv_ot_eligibility := NULL;
ld_doj := NULL;
-----Calculate Timesheet Data
BEGIN
SELECT ROUND
(xx_seconds_to_hrs
(SUM (xx_hours_in_seconds (xxm.over_time)
)
),
2
) over_time,
ROUND
(xx_seconds_to_hrs
(SUM (xx_hours_in_seconds (xxm.special_over_time)
)
),
2
) special_over_time,
ROUND
(xx_seconds_to_hrs
(SUM (xx_hours_in_seconds (xxm.tardiness_time)
)
),
2
) tardiness_time,
ROUND
(xx_seconds_to_hrs
(SUM (xx_hours_in_seconds (xxm.flex_hours)
)
),
2
) flexi_hours
INTO ln_ot,
ln_sot,
ln_tardi,
ln_flexi
FROM xx_time_sheet xxm
WHERE emp_no = cur_rec.emp_no
AND work_date BETWEEN TO_DATE (p_effective_start_date)
AND TO_DATE (p_effective_end_date)
GROUP BY emp_no;
EXCEPTION
WHEN OTHERS
THEN
lv_error_message := 'Timesheet Data Issue. Check the data';
lv_proceed := 'N';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
END;
--Decode for group id
BEGIN
SELECT NVL (DECODE (paaf.people_group_id,
61, 3,
62, 2,
63, 1,
paaf.people_group_id
),
0
) "group_code",
paaf.assignment_id, paaf.ass_attribute1 ot_eligibility,
papf.original_date_of_hire
INTO ln_group_code,
ln_assignment_id, lv_ot_eligibility,
ld_doj
FROM per_all_people_f papf, per_all_assignments_f paaf
WHERE paaf.person_id = papf.person_id
AND p_effective_end_date BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND p_effective_end_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND papf.employee_number = cur_rec.emp_no;
IF ln_group_code = 0
THEN
lv_error_message := 'Please enter people group for the employee';
lv_proceed := 'N';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
END IF;
IF ld_doj > ld_effective_date
THEN
ld_effective_date := ld_doj;
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_message :=
'Error in getting people group. Check the data';
lv_proceed := 'N';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
END;
IF ln_group_code = 2 AND NVL (lv_ot_eligibility, 'N') = 'N'
THEN
ln_tardi_time := (ln_tardi + ln_flexi) - (ln_ot + ln_sot);
lv_proceed := 'N';
IF ln_tardi_time > 0
THEN
lv_proceed := 'Y';
ln_tardi_hrs := ln_tardi_time;
END IF;
ELSIF ln_group_code = 2 AND NVL (lv_ot_eligibility, 'N') = 'Y'
THEN
IF ln_ot >= ln_flexi
THEN
ln_ot_hrs := ln_ot - ln_flexi;
ln_sot_hrs := ln_sot;
ln_tardi_hrs := ln_tardi;
ELSIF ln_ot < ln_flexi
THEN
IF (ln_ot + ln_sot) >= ln_flexi
THEN
ln_ot_hrs := 0;
ln_sot_hrs := (ln_ot + ln_sot) - ln_flexi;
ln_tardi_hrs := ln_tardi;
ELSE
ln_ot_hrs := 0;
ln_sot_hrs := 0;
ln_tardi_hrs := (ln_tardi + ln_flexi) - (ln_ot + ln_sot);
END IF;
END IF;
ELSIF ln_group_code = 1
THEN
lv_error_message := 'No Element Creation for Managers';
lv_proceed := 'N';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
ELSE
ln_ot_hrs := ln_ot;
ln_sot_hrs := ln_sot;
ln_tardi_hrs := ln_tardi;
END IF;
BEGIN
SELECT input_value_id
INTO ln_input_value_ot
FROM pay_input_values_f pi, pay_element_types_f pe
WHERE pi.NAME = 'Total Hours'
AND pi.element_type_id = pe.element_type_id
AND pe.element_name = 'Overtime';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
lv_error_message := 'More than one Hours input is defined';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
lv_proceed := 'N';
WHEN OTHERS
THEN
lv_error_message := 'Error in getting Hours Input ID';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
lv_proceed := 'N';
END;
BEGIN
SELECT input_value_id
INTO ln_input_value_sot
FROM pay_input_values_f pi, pay_element_types_f pe
WHERE pi.NAME = 'Total Hours'
AND pi.element_type_id = pe.element_type_id
AND pe.element_name = 'Special Overtime';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
lv_error_message := 'More than one Hours input is defined';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
lv_proceed := 'N';
WHEN OTHERS
THEN
lv_error_message := 'Error in getting Hours Input ID';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
lv_proceed := 'N';
END;
BEGIN
SELECT input_value_id
INTO ln_input_value_tar
FROM pay_input_values_f pi, pay_element_types_f pe
WHERE pi.NAME = 'Hours'
AND pi.element_type_id = pe.element_type_id
AND pe.element_name = 'Tardiness';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
lv_error_message := 'More than one Hours input is defined';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
lv_proceed := 'N';
WHEN OTHERS
THEN
lv_error_message := 'Error in getting Hours Input ID';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
lv_proceed := 'N';
END;
IF ln_ot_hrs > 0 AND lv_proceed = 'Y'
THEN --Create Element entry for Over Time.
BEGIN
SELECT pelf.element_link_id
INTO ln_ot_link_id
FROM pay_element_types_f petf, pay_element_links_f pelf
WHERE petf.element_type_id = pelf.element_type_id
AND petf.element_name = 'Overtime';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
lv_error_message :=
'More than one Overtime element is defined';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
--lv_proceed = 'N' ;
NULL;
WHEN OTHERS
THEN
lv_error_message := 'Error in getting OT link ID';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
-- lv_proceed = 'N' ;
NULL;
END;
--Call element creation API
BEGIN
pay_element_entry_api.create_element_entry
(p_effective_date => ld_effective_date,
p_business_group_id => ln_bg_id,
p_assignment_id => ln_assignment_id
--p_assignment_id
,
p_element_link_id => ln_ot_link_id,
p_entry_type => 'E' --For Entry
,
p_input_value_id1 => ln_input_value_ot
--Input Value ID for Hours
,
p_entry_value1 => ln_ot_hrs,
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_element_entry_id => ln_element_entry_id,
p_object_version_number => ln_object_version_number,
p_create_warning => lb_create_warning
);
UPDATE xx_time_sheet
SET last_run_date = SYSDATE
WHERE emp_no = cur_rec.emp_no;
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no
|| ' ~ '
|| 'Overtime Element Created ~ '
|| ln_ot_hrs
);
EXCEPTION
WHEN OTHERS
THEN
lv_error_message := 'Error in OT element creation - API Error';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no
|| ' ~ '
|| lv_error_message
|| ' ~ '
|| SUBSTR (SQLERRM, 1, 90)
);
END;
END IF;
IF ln_sot_hrs > 0 AND lv_proceed = 'Y'
THEN --Create element entry for Special Over Time.
BEGIN
SELECT pelf.element_link_id
INTO ln_sot_link_id
FROM pay_element_types_f petf, pay_element_links_f pelf
WHERE petf.element_type_id = pelf.element_type_id
AND petf.element_name = 'Special Overtime';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
lv_error_message :=
'More than one Special Overtime element is defined';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
--lv_proceed = 'N' ;
NULL;
WHEN OTHERS
THEN
lv_error_message := 'Error in getting SOT link ID';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
-- lv_proceed = 'N' ;
NULL;
END;
BEGIN
pay_element_entry_api.create_element_entry
(p_effective_date => ld_effective_date,
p_business_group_id => ln_bg_id,
p_assignment_id => ln_assignment_id
--p_assignment_id
,
p_element_link_id => ln_sot_link_id,
p_entry_type => 'E' --For entry
,
p_input_value_id1 => ln_input_value_sot
--Input Value ID for Hours
,
p_entry_value1 => ln_sot_hrs,
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_element_entry_id => ln_element_entry_id,
p_object_version_number => ln_object_version_number,
p_create_warning => lb_create_warning
);
UPDATE xx_time_sheet
SET last_run_date = SYSDATE
WHERE emp_no = cur_rec.emp_no;
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no
|| ' ~ '
|| 'Special Overtime Element Created ~ '
|| ln_sot_hrs
);
EXCEPTION
WHEN OTHERS
THEN
lv_error_message :=
'Error in SOT element creation - API Error';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no
|| ' ~ '
|| lv_error_message
|| ' ~ '
|| SUBSTR (SQLERRM, 1, 90)
);
END;
END IF;
IF ln_tardi_hrs > 0 AND lv_proceed = 'Y'
THEN --Create element entry for Special Over Time.
BEGIN
SELECT pelf.element_link_id
INTO ln_tardi_link_id
FROM pay_element_types_f petf, pay_element_links_f pelf
WHERE petf.element_type_id = pelf.element_type_id
AND petf.element_name = 'Tardiness';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
lv_error_message :=
'More than one Tardiness element is defined';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
--lv_proceed = 'N' ;
NULL;
WHEN OTHERS
THEN
lv_error_message := 'Error in getting OT link ID';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
-- lv_proceed = 'N' ;
NULL;
END;
BEGIN
pay_element_entry_api.create_element_entry
(p_effective_date => ld_effective_date,
p_business_group_id => ln_bg_id,
p_assignment_id => ln_assignment_id
--p_assignment_id
,
p_element_link_id => ln_tardi_link_id,
p_entry_type => 'E' --For Entry
,
p_input_value_id1 => ln_input_value_tar
--Input Value ID for Hours
,
p_entry_value1 => ln_tardi_hrs,
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_element_entry_id => ln_element_entry_id,
p_object_version_number => ln_object_version_number,
p_create_warning => lb_create_warning
);
UPDATE xx_time_sheet
SET last_run_date = SYSDATE
WHERE emp_no = cur_rec.emp_no;
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no
|| ' ~ '
|| 'Tardiness Element Created ~ '
|| ln_tardi_hrs
);
EXCEPTION
WHEN OTHERS
THEN
lv_error_message :=
'Error in Tardi element creation - API Error';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no
|| ' ~ '
|| lv_error_message
|| ' ~ '
|| SUBSTR (SQLERRM, 1, 90)
);
END;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Fatal Error' || ' ~ ' || SUBSTR (SQLERRM, 1, 60)
);
p_retcode := 3;
END xx_element_creation_proc;
-----------------------------------------------------------------------------------------------------------------------------------
--Element Creation - End
Trip Creation API
Procedure to create Trip in Oracle WMS.
CREATE OR REPLACE PROCEDURE xx_om_autocreate_trip (
p_master_trip IN VARCHAR2,
p_trip_no OUT VARCHAR2,
p_trip_name OUT VARCHAR2,
p_err_msg OUT VARCHAR2
)
IS
lv_return_status VARCHAR2 (1);
ln_msg_count NUMBER;
lv_msg_data VARCHAR2 (400);
ln_trip_id VARCHAR2 (20);
lv_trip_name VARCHAR2 (30);
l_line_tbl wsh_util_core.id_tab_type;
l_del_rows_tbl wsh_util_core.id_tab_type;
ln_line_no NUMBER := 0;
l_msg_index_out NUMBER;
CURSOR cur_trip
IS
SELECT DISTINCT wdv.delivery_detail_id
FROM oe_order_headers_v oeh,
oe_order_lines_v ool,
xx_om_mtrip_mastertrip_v xxo,
wsh_deliverables_v wdv,
xx_om_mtrip_lines_v xxl
WHERE oeh.header_id = ool.header_id
AND oeh.ship_from = xxo.warehouse_code
AND oeh.shipping_method_code = xxo.shipping_method_code
AND TRIM (ool.schedule_ship_date)
BETWEEN NVL (xxo.ss_date_from,
TRIM (ool.schedule_ship_date)
)
AND NVL (xxo.ss_date_to,
TRIM (ool.schedule_ship_date)
)
AND UPPER (ool.flow_status_code) = 'AWAITING_SHIPPING'
AND wdv.source_header_id = ool.header_id
AND wdv.source_line_id = ool.line_id
AND xxo.master_trip_no = p_master_trip
AND TO_CHAR (xxl.sales_order) = TO_CHAR (oeh.order_number)
AND NOT EXISTS (
SELECT sales_order
FROM xx_om_mtrip_lines_v xxo
WHERE xxo.sales_order = oeh.order_number
AND xxo.add_to_trip = 'Y')
AND NOT EXISTS (
SELECT 1
FROM oe_holds_history_v ohh
WHERE ohh.header_id = oeh.header_id
AND UPPER (hold_name) <> 'HOLD ORDER - WAREHOUSE'
AND released_flag = 'N')
ORDER BY 1;
BEGIN
FOR i IN cur_trip
LOOP
ln_line_no := NVL (ln_line_no, 0) + 1;
l_line_tbl (ln_line_no) := i.delivery_detail_id;
DBMS_OUTPUT.put_line (l_line_tbl (ln_line_no));
END LOOP;
wsh_delivery_details_pub.autocreate_del_trip
(p_api_version_number => 1.0 ---IN NUMBER
,
p_init_msg_list => fnd_api.g_false
---IN VARCHAR2 DEFAULT Fnd_Api.G_FALSE
,
p_commit => fnd_api.g_false
--IN VARCHAR2 DEFAULT Fnd_Api.G_FALSE
,
x_return_status => lv_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lv_msg_data,
p_line_rows => l_line_tbl,
x_del_rows => l_del_rows_tbl,
x_trip_id => ln_trip_id,
x_trip_name => lv_trip_name
);
COMMIT;
IF NVL (ln_msg_count, 0) > 0
THEN
FOR j IN 1 .. ln_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => j,
p_encoded => 'F',
p_data => lv_msg_data,
p_msg_index_out => l_msg_index_out
);
END LOOP;
END IF;
IF NVL (lv_return_status, 'X') = 'S'
THEN
p_trip_name := lv_trip_name;
p_trip_no := ln_trip_id;
UPDATE xx_om_confirm_trip_v
SET trip_created_flag = 'Y',
oracle_trip_id = ln_trip_id,
oracle_trip_name = lv_trip_name
WHERE master_trip_no = p_master_trip;
ELSIF NVL (lv_return_status, 'X') = 'F'
THEN
p_err_msg := lv_msg_data;
END IF;
COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE xx_om_autocreate_trip (
p_master_trip IN VARCHAR2,
p_trip_no OUT VARCHAR2,
p_trip_name OUT VARCHAR2,
p_err_msg OUT VARCHAR2
)
IS
lv_return_status VARCHAR2 (1);
ln_msg_count NUMBER;
lv_msg_data VARCHAR2 (400);
ln_trip_id VARCHAR2 (20);
lv_trip_name VARCHAR2 (30);
l_line_tbl wsh_util_core.id_tab_type;
l_del_rows_tbl wsh_util_core.id_tab_type;
ln_line_no NUMBER := 0;
l_msg_index_out NUMBER;
CURSOR cur_trip
IS
SELECT DISTINCT wdv.delivery_detail_id
FROM oe_order_headers_v oeh,
oe_order_lines_v ool,
xx_om_mtrip_mastertrip_v xxo,
wsh_deliverables_v wdv,
xx_om_mtrip_lines_v xxl
WHERE oeh.header_id = ool.header_id
AND oeh.ship_from = xxo.warehouse_code
AND oeh.shipping_method_code = xxo.shipping_method_code
AND TRIM (ool.schedule_ship_date)
BETWEEN NVL (xxo.ss_date_from,
TRIM (ool.schedule_ship_date)
)
AND NVL (xxo.ss_date_to,
TRIM (ool.schedule_ship_date)
)
AND UPPER (ool.flow_status_code) = 'AWAITING_SHIPPING'
AND wdv.source_header_id = ool.header_id
AND wdv.source_line_id = ool.line_id
AND xxo.master_trip_no = p_master_trip
AND TO_CHAR (xxl.sales_order) = TO_CHAR (oeh.order_number)
AND NOT EXISTS (
SELECT sales_order
FROM xx_om_mtrip_lines_v xxo
WHERE xxo.sales_order = oeh.order_number
AND xxo.add_to_trip = 'Y')
AND NOT EXISTS (
SELECT 1
FROM oe_holds_history_v ohh
WHERE ohh.header_id = oeh.header_id
AND UPPER (hold_name) <> 'HOLD ORDER - WAREHOUSE'
AND released_flag = 'N')
ORDER BY 1;
BEGIN
FOR i IN cur_trip
LOOP
ln_line_no := NVL (ln_line_no, 0) + 1;
l_line_tbl (ln_line_no) := i.delivery_detail_id;
DBMS_OUTPUT.put_line (l_line_tbl (ln_line_no));
END LOOP;
wsh_delivery_details_pub.autocreate_del_trip
(p_api_version_number => 1.0 ---IN NUMBER
,
p_init_msg_list => fnd_api.g_false
---IN VARCHAR2 DEFAULT Fnd_Api.G_FALSE
,
p_commit => fnd_api.g_false
--IN VARCHAR2 DEFAULT Fnd_Api.G_FALSE
,
x_return_status => lv_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lv_msg_data,
p_line_rows => l_line_tbl,
x_del_rows => l_del_rows_tbl,
x_trip_id => ln_trip_id,
x_trip_name => lv_trip_name
);
COMMIT;
IF NVL (ln_msg_count, 0) > 0
THEN
FOR j IN 1 .. ln_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => j,
p_encoded => 'F',
p_data => lv_msg_data,
p_msg_index_out => l_msg_index_out
);
END LOOP;
END IF;
IF NVL (lv_return_status, 'X') = 'S'
THEN
p_trip_name := lv_trip_name;
p_trip_no := ln_trip_id;
UPDATE xx_om_confirm_trip_v
SET trip_created_flag = 'Y',
oracle_trip_id = ln_trip_id,
oracle_trip_name = lv_trip_name
WHERE master_trip_no = p_master_trip;
ELSIF NVL (lv_return_status, 'X') = 'F'
THEN
p_err_msg := lv_msg_data;
END IF;
COMMIT;
END;
/
Monday, December 6, 2010
Register a Report with Oracle Applications R12
To register a Report with Oracle Applications in order to use it in Apps.
1. Develop the report (.rdf) file as per client requirement by using reports 10g.
2. move the .rdf file from local machine to server into the respective path CUS_TOP\12.0.0\Reports\US\ .rdf PO_TOP\12.0.0\Reports\US\ .rdf1. Connect to the application and select system administrator create Executable
2. Executable Name (Any User defined Name)
3. Application Name(Where the .rdf file located)
4. Execution Method (Oracle Reports for reports)
5. Execution File (.rdf file Name)
6. Create Concurrent program and attach 1)Executable
7. Parameters
8. Incompatible Programs
9. create Request Group attach Concurrent Program
10. Create responsibility attach
1. Request Group
2. Menu
3. Data Group
11. Create User Attach Responsibility to the User
12. User will submit the request from SRS (Standard Request Submission) Window SELECT user_id UserID, user_name UserName, creation_date Cdate FROM fnd_user
Executable = System Administrator =>
Concurrent = > PRogram =>Executable Concurrent Program = => Concurrent = >
Program => Define Request Group = => Security = >Responsibility => Request responsibility = =>
Security = >responsibility => define user = =>Security =>User =>Define SRS Window =
View=>Requests=>Submit New Request => OK =>Select Program Name =>
Click Submit Button Press NO =>Select find Button =>13. Click View Log button to open the log file.
14. Click View output button to open the output file.
1. Develop the report (.rdf) file as per client requirement by using reports 10g.
2. move the .rdf file from local machine to server into the respective path CUS_TOP\12.0.0\Reports\US\ .rdf PO_TOP\12.0.0\Reports\US\ .rdf1. Connect to the application and select system administrator create Executable
2. Executable Name (Any User defined Name)
3. Application Name(Where the .rdf file located)
4. Execution Method (Oracle Reports for reports)
5. Execution File (.rdf file Name)
6. Create Concurrent program and attach 1)Executable
7. Parameters
8. Incompatible Programs
9. create Request Group attach Concurrent Program
10. Create responsibility attach
1. Request Group
2. Menu
3. Data Group
11. Create User Attach Responsibility to the User
12. User will submit the request from SRS (Standard Request Submission) Window SELECT user_id UserID, user_name UserName, creation_date Cdate FROM fnd_user
Executable = System Administrator =>
Concurrent = > PRogram =>Executable Concurrent Program = => Concurrent = >
Program => Define Request Group = => Security = >Responsibility => Request responsibility = =>
Security = >responsibility => define user = =>Security =>User =>Define SRS Window =
View=>Requests=>Submit New Request => OK =>Select Program Name =>
Click Submit Button Press NO =>Select find Button =>13. Click View Log button to open the log file.
14. Click View output button to open the output file.
Sunday, December 5, 2010
Check Printing in Oracle R12
Login to XML Publisher Administrator, download below template and start customizing report. IBYDC_STD1.rtf file is the initial starting point for Check printing report customization in R12
Template = Standard Check Format
Data Definition = Oracle Payments Funds Disbursement Payment Instruction Extract 1.0
Code = IBY_FD_INSTRUCTION_1_0
File = IBYDC_STD1.rtf
There are always lot of challenges to design check printing report as per clients requirement. I tried to cover most of them below:
1) Below link specifies all steps required for MICR Font Setup on our desktop and application server
http://www.centroid.com/Blog/oracle/check-printing-using-micr-fonts-in-r12/
For more details, refer to Metalink Note- 312353.1. This document describes how to set up the MICR font used in check printing.
2) Perform below steps to add signature in your RTF Template
a) Insert Picture tag in your template and click on Tab 'Web'
b) Insert url in that tab (Example given in separate article)
c) Finally copy your signature file Sample_Sig.gif in $OA_MEDIA top at Application server
You can refer separate article on this RTF Template- Signature Printing
3) In order to fix total number of rows on each page and then do pagebreak, go thru below link
http://apps2fusion.com/apps/apps/155-xml-publisher-developing-reports-printed-on-pre-printed-stationary
There are always lot of challenges to design check printing report as per clients requirement. I tried to cover most of them below:
1) Below link specifies all steps required for MICR Font Setup on our desktop and application server
http://www.centroid.com/Blog/oracle/check-printing-using-micr-fonts-in-r12/
For more details, refer to Metalink Note- 312353.1. This document describes how to set up the MICR font used in check printing.
2) Perform below steps to add signature in your RTF Template
a) Insert Picture tag in your template and click on Tab 'Web'
b) Insert url in that tab (Example given in separate article)
c) Finally copy your signature file Sample_Sig.gif in $OA_MEDIA top at Application server
You can refer separate article on this RTF Template- Signature Printing
3) In order to fix total number of rows on each page and then do pagebreak, go thru below link
http://apps2fusion.com/apps/apps/155-xml-publisher-developing-reports-printed-on-pre-printed-stationary
Set Environment for Oracle Forms development
1. Copy TEMPLATE.fmb , APPSTAND.fmb from AU_TOP/forms/US to C:\DevSuiteHome_1\forms directory copy all .pll files from /Applvis/visappl/au/12.0.0/resource to C:\DevSuiteHome_1\forms using ftp
2. Open Template.fmb in Oracle Forms Builder 10g. It should open without giving any error
3. Now environment is set to do further development
Resolving records stuck in the Receiving Transactions Interface
Reprocessing Errored Rows
-------------------------
On occassion it may be necessary to reprocess an errored or stuck record in the interface. The following steps can be used to reprocess a stuck record.
Navigate to the Transaction Status Summary screen in the application from either Receiving or Inventory responsibility (Nav > Receiving > Transaction Status Summary). Use the Find form to query up the transaction in question and then set focus to the transaction.
You then select the Delete function from the menu by pressing on the red x icon button to delete the record.
You can then reattempt to receive the record. If it fails again you must engage Oracle Support Services for further disposition.
Technical Details:
-----------------
When a row has been succesfully processed it will be deleted from the table. When a row has been processed for an inventory item, the rcv_transactions stable will be updated and a row written to the mtl_transactions_interface table, waiting to be picked up by the Inventory Transaction Processor. Once this record has been processed succesfully, the on hand quantity level will be updated.
Receiving statuses and errors can subsequently be viewed in the Receiving Transaction Status Summary form in Purchasing / Inventory. On occassion the records cannot be viewed in this form and they have to be queried using sqlplus.
To query stuck rows, run the following sql statement which will yield errored records for the past 7 days (if you want to change the time
period just change the sysdate - 7 criteria to a larger duration for example for the past 30 days it would be: "and transaction_date >= sysdate - 30")
Here is the query:
SQL> select interface_transaction_id, processing_status_code, processing_mode_code, transaction_status_code, transaction_type,
transaction_date
from rcv_transactions_interface
where (processing_status_code = 'ERROR' or transaction_status_code = 'ERROR') and transaction_date >= sysdate - 7;
The processing_mode_code determines whether the record was received in Online, Immediate or Batch mode. This is the column that would
need to be updated to 'BATCH' to reprocess an online record.
Troubleshooting
----------------
If the receipt has gone through okay but the stock levels have not been updated then it is worth checking if the inventory transaction manager is running and if there are any records pending or in error in the inventory transactions interface. These can be queried via the Inventory Transactions Interface Inquiry screen.
If the Purchase Order shows that the shipment has been received and delivered and inventory levels show the stock levels updated to reflect the delivery, then the records can be deleted using the Transaction Status Summary form (Ref Note 303544.1 - How To Remove Pending and Error Transactions from the Transaction Status Summary) by selecting the record and then picking the Delete red X icon on the toolbar in the menu. In this case there is no need to reprocess or re-enter these transactions.
Note: If a transaction is accessible via the Transaction Status Summary form and is in error it can be deleted.
Generic script to delete RTI records which are not appearing in Transaction Status Summary form for deletion. Please ensure the scripts are ran on TEST instance first and tested for data
correctness thoroughly. After the scripts are ran, please check the data and only the correct records are updated before committing. If all goes well, the script can be promoted to the PRODUCTION instance.
--back up date in rti, rli, mtlt, rsi, msnt
create table rti_bak as select * from rcv_transactions_interface
where interface_transaction_id in (&interface_transaction_ids);
create table rli_bak as select * from rcv_lots_interface
where interface_transaction_id in (select interface_transaction_id from rti_bak);
create table mtlt_bak as
select * from mtl_transaction_lots_temp
where product_code = 'RCV'
and product_transaction_id in (select interface_transaction_id from rti_bak);
create table rsi_bak as
select * from rcv_serials_interface where interface_transaction_id in (select interface_transaction_id from rti_bak);
create table msnt_bak as select * from mtl_serial_numbers_temp
where product_code = 'RCV' and product_transaction_id in (select interface_transaction_id
from rti_bak);
--delete data in rti, rli, mtlt, rsi, msnt
delete rcv_transactions_interface where interface_transaction_id in (select interface_transaction_id from rti_bak);
delete rcv_lots_interface
where interface_transaction_id in (select interface_transaction_id from rti_bak);
delete mtl_transaction_lots_temp
where product_code = 'RCV' and product_transaction_id in (select interface_transaction_id
from rti_bak);
delete rcv_serials_interface
where interface_transaction_id in (select interface_transaction_id from rti_bak);
delete mtl_serial_numbers_temp
where product_code = 'RCV'
and product_transaction_id in (select interface_transaction_id from rti_bak);
References :
Resolving records stuck in the Receiving Transactions Interface
Doc ID 50903.1
Transaction Status Summary: How To Remove Pending and Error Receiving Transactions [ID 303544.1]
-------------------------
On occassion it may be necessary to reprocess an errored or stuck record in the interface. The following steps can be used to reprocess a stuck record.
Navigate to the Transaction Status Summary screen in the application from either Receiving or Inventory responsibility (Nav > Receiving > Transaction Status Summary). Use the Find form to query up the transaction in question and then set focus to the transaction.
You then select the Delete function from the menu by pressing on the red x icon button to delete the record.
You can then reattempt to receive the record. If it fails again you must engage Oracle Support Services for further disposition.
Technical Details:
-----------------
When a row has been succesfully processed it will be deleted from the table. When a row has been processed for an inventory item, the rcv_transactions stable will be updated and a row written to the mtl_transactions_interface table, waiting to be picked up by the Inventory Transaction Processor. Once this record has been processed succesfully, the on hand quantity level will be updated.
Receiving statuses and errors can subsequently be viewed in the Receiving Transaction Status Summary form in Purchasing / Inventory. On occassion the records cannot be viewed in this form and they have to be queried using sqlplus.
To query stuck rows, run the following sql statement which will yield errored records for the past 7 days (if you want to change the time
period just change the sysdate - 7 criteria to a larger duration for example for the past 30 days it would be: "and transaction_date >= sysdate - 30")
Here is the query:
SQL> select interface_transaction_id, processing_status_code, processing_mode_code, transaction_status_code, transaction_type,
transaction_date
from rcv_transactions_interface
where (processing_status_code = 'ERROR' or transaction_status_code = 'ERROR') and transaction_date >= sysdate - 7;
The processing_mode_code determines whether the record was received in Online, Immediate or Batch mode. This is the column that would
need to be updated to 'BATCH' to reprocess an online record.
Troubleshooting
----------------
If the receipt has gone through okay but the stock levels have not been updated then it is worth checking if the inventory transaction manager is running and if there are any records pending or in error in the inventory transactions interface. These can be queried via the Inventory Transactions Interface Inquiry screen.
If the Purchase Order shows that the shipment has been received and delivered and inventory levels show the stock levels updated to reflect the delivery, then the records can be deleted using the Transaction Status Summary form (Ref Note 303544.1 - How To Remove Pending and Error Transactions from the Transaction Status Summary) by selecting the record and then picking the Delete red X icon on the toolbar in the menu. In this case there is no need to reprocess or re-enter these transactions.
Note: If a transaction is accessible via the Transaction Status Summary form and is in error it can be deleted.
Generic script to delete RTI records which are not appearing in Transaction Status Summary form for deletion. Please ensure the scripts are ran on TEST instance first and tested for data
correctness thoroughly. After the scripts are ran, please check the data and only the correct records are updated before committing. If all goes well, the script can be promoted to the PRODUCTION instance.
--back up date in rti, rli, mtlt, rsi, msnt
create table rti_bak as select * from rcv_transactions_interface
where interface_transaction_id in (&interface_transaction_ids);
create table rli_bak as select * from rcv_lots_interface
where interface_transaction_id in (select interface_transaction_id from rti_bak);
create table mtlt_bak as
select * from mtl_transaction_lots_temp
where product_code = 'RCV'
and product_transaction_id in (select interface_transaction_id from rti_bak);
create table rsi_bak as
select * from rcv_serials_interface where interface_transaction_id in (select interface_transaction_id from rti_bak);
create table msnt_bak as select * from mtl_serial_numbers_temp
where product_code = 'RCV' and product_transaction_id in (select interface_transaction_id
from rti_bak);
--delete data in rti, rli, mtlt, rsi, msnt
delete rcv_transactions_interface where interface_transaction_id in (select interface_transaction_id from rti_bak);
delete rcv_lots_interface
where interface_transaction_id in (select interface_transaction_id from rti_bak);
delete mtl_transaction_lots_temp
where product_code = 'RCV' and product_transaction_id in (select interface_transaction_id
from rti_bak);
delete rcv_serials_interface
where interface_transaction_id in (select interface_transaction_id from rti_bak);
delete mtl_serial_numbers_temp
where product_code = 'RCV'
and product_transaction_id in (select interface_transaction_id from rti_bak);
References :
Resolving records stuck in the Receiving Transactions Interface
Doc ID 50903.1
Transaction Status Summary: How To Remove Pending and Error Receiving Transactions [ID 303544.1]
Subscribe to:
Posts (Atom)