Search This Blog

Monday, December 13, 2010

Matrix Report in XML Publisher

This report is number of JVs created per month per source wise.

Query:
select je_source,to_char(date_created,'Mon-YY') Source_Month,count(je_header_id) je_count from gl_je_headerswhere to_char(date_created,'Q') = :p_quarter and to_char(date_created,'YYYY') = :p_year group
 by je_source,to_char(date_created,'Mon-YY')
RDF report:
    

Total Number of JVs Quarterly

Source Name
GSource_MonthE
Total
GJE_SOURCE
G If exists: JE_COUNT end; E
999E
Total
G If exists: 999 end; E


O              
G Close to JE_SOURCE - <?for-each-   group@section:G_SERVICE_NAME;./JE_SOURCE?><?sort:JE_SOURCE;'ascending';data-type='text'?>

 JE_SOURCE - <?JE_SOURCE?><?variable@incontext:JS;JE_SOURCE?>
       
 G close to Source_Month :
<?for-each-group@column:G_SERVICE_NAME;./SOURCE_MONTH?>
Source_Month - <?SOURCE_MONTH?>

E  - <?end for-each-group?>
G Near If Exists -  <?for-each-group@cell://G_SERVICE_NAME;./SOURCE_MONTH?
IF Exists –
<?if:count(current-group()[JE_SOURCE=$JS])?>

JE_COUNT - <?current-group()[JE_SOURCE=$JS]/JE_COUNT?> 
End; - <?end if?>

E  - <?end for-each-group?>
G in Total -  <?for-each-group@cell://G_SERVICE_NAME;./SOURCE_MONTH?

IF Exists in Total –
<?if:count(current-group())?>
999 in Total
<?sum(current-group()/JE_COUNT)?>
End if in Total:
<?end if?>
E in Total –
<?end for-each-group?>

999 in Total Column - <?sum(//G_SERVICE_NAME[JE_SOURCE=$JS]/JE_COUNT)?>
E in Total Column:
<?end for-each-group?>


      

     


Sunday, December 12, 2010

How to Use FND_STANDARD_DATE value set

Let’s take example of report – AP Invoices Paid in Time
Here GL_Date in Date parameter.
SELECT api.invoice_id, api.invoice_num, invoice_date, api.invoice_amount,
       api.vendor_name, api.vendor_number, apc.check_number, aps.due_date,
       apc.check_date
  FROM ap_invoices_v api,
       ap_payment_schedules_all aps,
       ap_invoice_payments_all aip,
       ap_checks_all apc
 WHERE api.invoice_id = aps.invoice_id
   AND aip.invoice_id = api.invoice_id
   AND aip.check_id = apc.check_id
   AND apc.check_date <= aps.due_date
   --and api.gl_date >= nvl(:p_gl_date,api.gl_date)
   AND api.gl_date >=
          NVL (TO_DATE (SUBSTR (:p_gl_date, 1, 10), 'YYYY/MM/DD'),
               api.gl_date)

That's it. Test the report through SRS window.

Stock Market in India Development of Stock Market in India 
 

Tuesday, December 7, 2010

HRMS APIs

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

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