CREATE OR REPLACE PACKAGE BODY Xxhr_utpa_training_post_pk AS
PROCEDURE Utpa_tp_data_internal (
Errbuf OUT VARCHAR2,
Retcode OUT NUMBER,
P_in_date IN VARCHAR2,
WriteToFile IN BOOLEAN) IS
/* Version control headers and comments */
/* Declarations */
/* Main cursor */CURSOR C IS SELECT
Papf.person_id Person_id,
Papf.last_name Last_name,
Papf.first_name First_name,
Papf.middle_names Middle_names,
Papf.employee_number Employee_number,
Papf.effective_start_date Effective_start_date,
Papf.effective_end_date Effective_end_date,
Papf.original_date_of_hire Original_date_of_hire,
Papf.work_telephone Work_telephone,
Papf.date_of_birth Date_of_birth,
Papf.email_address Email_address,
Papf.attribute19 Bldg_code,
(
SELECT Ffv_2.description
FROM Fnd_flex_value_sets Ffvs_2,
Fnd_flex_values_vl Ffv_2
WHERE Ffvs_2.flex_value_set_id = Ffv_2.flex_value_set_id
AND Ffvs_2.flex_value_set_name = 'UTPA HR BUILDING'
AND Ffv_2.flex_value = Papf.attribute19
) Bldg_name,
Ppg.segment2 Segment2,
Substr(Hapf.name, 10, 100) Job_title,
Substr(Hapf.name, 1, 8) Job_code,
Haout.name Emp_dept,
Haout.organization_id Org_id
FROM Per_all_people_f Papf,
Per_person_types Ppt,
Per_person_type_usages_f Pptuf,
Per_all_assignments_f Paaf,
Hr_all_organization_units_tl Haout,
Hr_all_positions_f Hapf,
Pay_people_groups Ppg
WHERE Papf.person_id = Pptuf.person_id
AND Pptuf.person_type_id = Ppt.person_type_id
AND Paaf.person_id = Papf.person_id
AND Hapf.position_id = Paaf.position_id
AND Ppt.system_person_type IN ('EMP')
AND Ppg.segment2 IN ('F', 'FO', 'M', 'R', 'S') /* As per MD50 */
AND Paaf.organization_id = Haout.organization_id
AND Paaf.assignment_number IS NOT NULL /* Fix for Von Ende problem. See below. */
AND Ppg.people_group_id = Paaf.people_group_id
-- Only employees with a *valid* assignment for the entire month should be included in the output file.
-- this eliminates employees whose assignment_number is NULL...
AND Hapf.effective_start_date <= Trunc(Ld_in_date,'MONTH')
AND Hapf.effective_end_date >= Last_day(Ld_in_date)
/* Working days are inclusive,
so 1st day of month or before,
to last day of month or after
means that they work the complete
month, as per MD50 */
AND Papf.effective_start_date BETWEEN Pptuf.effective_start_date AND Pptuf.effective_end_date
AND Papf.effective_end_date = (SELECT MAX(Papf_2.effective_end_date)
FROM Per_all_people_f Papf_2
WHERE Papf_2.person_id = Papf.person_id
AND Papf_2.effective_start_date <= Paaf.effective_end_date
AND Papf_2.effective_end_date >= Paaf.effective_start_date)
AND Paaf.effective_start_date BETWEEN Papf.effective_start_date AND Papf.effective_end_date
AND Paaf.effective_end_date = (SELECT MIN(Paaf_2.effective_end_date)
FROM Per_all_assignments_f Paaf_2,
Per_assignment_status_types Past_2
WHERE Paaf_2.assignment_id = Paaf.assignment_id
AND Paaf_2.assignment_status_type_id = Past_2.assignment_status_type_id
AND Past_2.user_status = 'Active Assignment'
AND Paaf_2.effective_start_date <= Trunc(Ld_in_date,'MONTH')
AND Paaf_2.effective_end_date >= Trunc(Ld_in_date,'MONTH'))
ORDER BY Employee_number;
BEGIN
/* Set up default error string in case program aborts */
/* Write log file header */ Fnd_File.put_line (Fnd_file.log,
' Request Id :' || Fnd_Global.conc_request_id ||
' Date :' || TO_CHAR (SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
Fnd_File.put_line (Fnd_file.log,' ');
Fnd_File.put_line (Fnd_file.log,
' UTPA: HR Training Post Outbound ');
Fnd_File.put_line (Fnd_file.log, ' ');
Fnd_File.put_line (Fnd_file.log,
' Executable : $Id: tpextract.pkb,v 1.31 2008/03/20 00:37:51 apps Exp apps $');
Fnd_File.put_line (Fnd_file.log,
' Effective Date : ' || TO_CHAR (ld_in_date, 'DD-MON-YYYY'));
Fnd_File.put_line (Fnd_file.log, ' ');
Fnd_File.put_line (Fnd_file.log,
' Outbound File Path : ' || Lc_file_path);
Fnd_File.put_line (Fnd_file.log,
' Outbound File Name : ' || Lc_file_name);
Fnd_File.put_line (Fnd_file.log, ' ');
/* Create output file like $XXHR/DATA/OUTBOUND/UTPA_TPOST_20080124_133851_emptrain.txt */ BEGIN
L_utl_file := Utl_file.fopen(Lc_file_path, Lc_file_name, 'W', 32767);
EXCEPTION
-- not really sure why we trap these just to raise the same error!
-- surely simpler just to let the caller handle it ...
WHEN Utl_file.invalid_path THEN
Errbuf := 'invalid path - '||Lc_file_path;
Fnd_file.put_line(Fnd_file.log, Errbuf);
RAISE Ex_exit_from_proc;
WHEN Utl_file.access_denied THEN
Errbuf := 'access denied - '||Lc_file_path||'/'||Lc_file_name;
Fnd_file.put_line(Fnd_file.log, Errbuf);
RAISE Ex_exit_from_proc;
WHEN Utl_file.write_error THEN
Errbuf := 'no write permission - '||Lc_file_path||'/'||Lc_file_name;
Fnd_file.put_line(Fnd_file.log, Errbuf);
RAISE Ex_exit_from_proc;
WHEN OTHERS THEN
Errbuf := 'fopen('''
||Lc_file_path||''','''
||Lc_file_name||''', ''W'', 32767) fails - '
||SQLERRM;
Fnd_file.put_line(Fnd_file.log, Errbuf);
RAISE Ex_exit_from_proc;
END;
FOR X IN C LOOP /// Main loop over all records
/* Clean up 'end date' string */
/* Get phone numbers from another query */
/* Get division via a call to another package (from consultants) */
/* Everything else came from main select. We can now output the record */
END LOOP;
/* Exception handling - trap file errors, record a relevant error message */
END Utpa_tp_data_internal;
/* External procedure - primary interface to this code that is called from Concurrent Programs */
END Xxhr_utpa_training_post_pk;