Payroll - Element Updation ( Basic/HRA etc)

STEP I  -- Insert into XYKA_SALARY_API

STEP II -- Call Procedure import_records

        -- Records will be populated into xyka_salary_proposal
       
STEP III  -- process_salary_proposal_api ( Main call to hr_maintain_proposal_api.insert_salary_proposal )

CREATE SEQUENCE XYKA_SALARY_PROPOSAL_IDS
  START WITH 1000
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;

CREATE TABLE XYKA_SALARY_PROPOSAL
(
  X_PROPOSAL_ID      NUMBER,
  X_EMPLOYEE_NUMBER  NUMBER,
  X_EFFECTIVE_DATE   DATE,
  X_NET_AMOUNT       NUMBER,
  X_PROCESS_FLAG     CHAR(1 BYTE),
  X_APPROVED_FLAG    CHAR(1 BYTE),
  X_REMARKS          VARCHAR2(240 BYTE),
  CREATION_DATE      DATE,
  CREATED_BY         NUMBER,
  LAST_UPDATE_DATE   DATE,
  LAST_UPDATED_BY    NUMBER,
  LAST_UPDATE_LOGIN  NUMBER
);


CREATE TABLE XYKA_SALARY_API
(
  X_EMPLOYEE_NUMBER  NUMBER,
  X_EFFECTIVE_DATE   DATE,
  X_NET_AMOUNT       NUMBER,
  X_TRANSFER_DATE    DATE,
  X_REMARKS          VARCHAR2(240 BYTE)
)

select * from XYKA_SALARY_PROPOSAL  

--drop table xyka.XYKA_SALARY_PROPOSAL 

commit

-------------------------------------------------

select ELEMENT_NAME , REPORTING_NAME , decode (PROCESSING_TYPE ,'R','REC','N','NREC') from PAY_ELEMENT_TYPES_F

select * from PAY_ELEMENT_TYPES_F 

CREATE OR REPLACE FORCE VIEW APPS.XYKA_SALARY_PROPOSAL_V
(
   SALARY_API_ROWID,
   PROPOSAL_ID,
   EMPLOYEE_NUMBER,
   EMPLOYEE_NAME,
   BUSINESS_GROUP_ID,
   GRADE,
   ASSIGNMENT_ID,
   ELEMENT_ENTRY_ID,
   DEPARTMENT,
   EFFECTIVE_DATE,
   PERIOD_NAME,
   NET_AMOUNT,
   REMARKS,
   APPROVED_FLAG,
   PROCESS_FLAG,
   CREATION_DATE,
   CREATED_BY,
   LAST_UPDATE_DATE,
   LAST_UPDATED_BY,
   LAST_UPDATE_LOGIN
)
AS
   SELECT   ROWID salary_api_rowid,
            xsp.x_proposal_id proposal_id,
            xsp.x_employee_number employee_number,
            (SELECT   last_name
               FROM   per_people_f
              WHERE   employee_number = TO_CHAR (xsp.x_employee_number)
                      AND ROWNUM = 1)
               employee_name,
            (SELECT   business_group_id
               FROM   per_people_f
              WHERE   employee_number = TO_CHAR (xsp.x_employee_number)
                      AND ROWNUM = 1)
               business_group_id,
            (SELECT   pg.NAME
               FROM   per_people_f ppf, per_assignments_f paf, per_grades pg
              WHERE   ppf.person_id = paf.person_id
                      AND paf.grade_id = pg.grade_id
                      AND xsp.x_effective_date BETWEEN ppf.effective_start_date
                                                   AND  ppf.effective_end_date
                      AND xsp.x_effective_date BETWEEN paf.effective_start_date
                                                   AND  paf.effective_end_date
                      AND (pg.date_to IS NULL
                           OR xsp.x_effective_date BETWEEN pg.date_from
                                                       AND  pg.date_to)
                      AND ppf.employee_number =
                            TO_CHAR (xsp.x_employee_number))
               grade,
            (SELECT   paf.assignment_id
               FROM   per_people_f ppf, per_assignments_f paf
              WHERE   ppf.person_id = paf.person_id
                      AND xsp.x_effective_date BETWEEN ppf.effective_start_date
                                                   AND  ppf.effective_end_date
                      AND xsp.x_effective_date BETWEEN paf.effective_start_date
                                                   AND  paf.effective_end_date
                      AND ppf.employee_number =
                            TO_CHAR (xsp.x_employee_number))
               assignment_id,
            (SELECT   element_entry_id
               FROM   pay_element_entries_f
              WHERE   assignment_id =
                         (SELECT   paf.assignment_id
                            FROM   per_people_f ppf, per_assignments_f paf
                           WHERE   ppf.person_id = paf.person_id
                                   AND xsp.x_effective_date BETWEEN ppf.effective_start_date
                                                                AND  ppf.effective_end_date
                                   AND xsp.x_effective_date BETWEEN paf.effective_start_date
                                                                AND  paf.effective_end_date
                                   AND ppf.employee_number =
                                         TO_CHAR (xsp.x_employee_number))
                      AND SYSDATE BETWEEN effective_start_date
                                      AND  effective_end_date
                      AND element_type_id =
                            (SELECT   element_type_id
                               FROM   pay_element_types_f_tl
                              WHERE   element_name = 'Basic'
                                      AND element_type_id = 92))  --change element as per requirement
               element_entry_id,
            (SELECT   NAME
               FROM   hr_all_organization_units_tl
              WHERE   organization_id =
                         (SELECT   organization_id
                            FROM   per_people_f ppf, per_assignments_f paf
                           WHERE   ppf.person_id = paf.person_id
                                   AND xsp.x_effective_date BETWEEN ppf.effective_start_date
                                                                AND  ppf.effective_end_date
                                   AND xsp.x_effective_date BETWEEN paf.effective_start_date
                                                                AND  paf.effective_end_date
                                   AND ppf.employee_number =
                                         TO_CHAR (xsp.x_employee_number)))
               department,
            xsp.x_effective_date effective_date,
            TO_CHAR (xsp.x_effective_date, 'MON-RR') period_name,
            xsp.x_net_amount net_amount,
            xsp.x_remarks remarks,
            xsp.x_approved_flag approved_flag,
            xsp.x_process_flag process_flag,
            xsp.creation_date,
            xsp.created_by,
            xsp.last_update_date,
            xsp.last_updated_by,
            xsp.last_update_login
     FROM   xyka_salary_proposal xsp;
    
---------------------------------------------------------------------

CREATE OR REPLACE PACKAGE APPS.xyka_salary_proposal_api
AS
--## ##################################################################
--#
--# Script        :   xyka_salary_api.sql
--# Author        :   Rushikesh Ranade
--# System        :   YK Almoayyed and Sons
--# Date          :   12 Jul 2012 11:54
--# Version       :   1.0
--# Description   :   Customized Package to process HR Salary APIs
--#
--# Modif Hist :
--# -------------
--#
--# Usage Instructions:
--# -------------------
--# The Objects has to be called from the Salary API Form and Salary
--# API Requests
--#
--## ##################################################################

   /* Package Objects Definition */
-- ---------------------------------------------------
-- Procedure validate the data given in the Salary API
-- table and populates the validated data into the
-- Salary API Main tables
-- populated based on the supplied parameters
-- the parameters are
-- p_period specifies the Period Name
-- p_user_id specifies the User ID
-- p_login_id specifies the User Login ID
-- l_status specifies the Process Status
-- L_message specifies the processing messages
-- ------------------
   PROCEDURE import_records (
      p_out1       OUT      VARCHAR2,
      p_out2       OUT      VARCHAR2,
      p_period     IN       VARCHAR2,
      p_user_id    IN       NUMBER,
      p_login_id   IN       NUMBER
   );

-- ------------------------------------------------
-- Procedure to process the Salary proposal API
-- based on the supplied parameters
-- p_period_name specifies the Period Name
-- ---------------
   PROCEDURE process_salary_proposal_api (p_period_name IN VARCHAR2);
END xyka_salary_proposal_api;

--------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY APPS.xyka_salary_proposal_api
AS
-- ---------------------------------------------------
-- Procedure validate the data given in the Salary API
-- table and populates the validated data into the
-- Salary API Main tables
-- populated based on the supplied parameters
-- the parameters are
-- p_period specifies the Period Name
-- p_user_id specifies the User ID
-- p_login_id specifies the User Login ID
-- ------------------
   PROCEDURE import_records (
      p_out1       OUT      VARCHAR2,
      p_out2       OUT      VARCHAR2,
      p_period     IN       VARCHAR2,
      p_user_id    IN       NUMBER,
      p_login_id   IN       NUMBER
   )
   IS
      /* Local Variables */
      l_success_count   NUMBER         := 0;
      l_rec_count       NUMBER         := 0;
      l_status          VARCHAR2 (20);
      l_message         VARCHAR2 (240);

      CURSOR salary_api (c_period_name IN VARCHAR2)
      IS
         SELECT x_employee_number, x_effective_date, x_net_amount
           FROM xyka_salary_api
          WHERE TO_CHAR (x_effective_date, 'MON-RR') = c_period_name
            AND x_transfer_date IS NULL;
   BEGIN
      /* Transfers the records into the Salary API Tables */
      fnd_file.put_line
         (fnd_file.output,
             'YKA - Upload Salary Proposal for a Period (XYKASALP) Started on : '
          || TO_CHAR (SYSDATE, 'DD-MON-RRRR HH24:MI:SS')
         );
      fnd_file.put_line
         (fnd_file.output,
          '-----------------------------------------------------------------------------'
         );
      fnd_file.put_line (fnd_file.output, '         ');

      FOR r IN salary_api (p_period)
      LOOP
         fnd_file.put_line (fnd_file.output,
                               'Processing for Employee Number # '
                            || r.x_employee_number
                           );
         /* Re-Sets the Variables */
         l_status := NULL;
         l_message := NULL;
         l_rec_count := l_rec_count + 1;

         /* Start populating the Salary API Main table */
         BEGIN
            INSERT INTO xyka_salary_proposal
                        (x_proposal_id,
                         x_employee_number, x_effective_date,
                         x_net_amount, x_process_flag, x_approved_flag,
                         x_remarks, creation_date, created_by,
                         last_update_date, last_updated_by, last_update_login
                        )
                 VALUES (xyka_salary_proposal_ids.NEXTVAL,
                         r.x_employee_number, r.x_effective_date,
                         r.x_net_amount, 'N', 'N',
                         NULL, SYSDATE, p_user_id,
                         SYSDATE, p_user_id, p_login_id
                        );
         EXCEPTION
            WHEN OTHERS
            THEN
               l_status := 'ERROR';
               l_message := SQLERRM;
               fnd_file.put_line (fnd_file.output,
                                  ' *** Error Message *** : ' || SQLERRM
                                 );
         END;

         /* If any error occured while transferring the record */
         IF l_status = 'ERROR'
         THEN
            /* Updates the Base Table with the error message */
            UPDATE xyka_salary_api
               SET x_remarks = l_message
             WHERE x_employee_number = r.x_employee_number
               AND TO_CHAR (x_effective_date, 'MON-RR') = p_period;
         ELSIF l_status IS NULL
         THEN
            l_success_count := l_success_count + 1;

            /* If no processing errors occurred then, updates the Transfer Date */
            UPDATE xyka_salary_api
               SET x_remarks = 'TRANSFERRED',
                   x_transfer_date = SYSDATE
             WHERE x_employee_number = r.x_employee_number
               AND TO_CHAR (x_effective_date, 'MON-RR') = p_period;
         END IF;

         /* Commits the Changes */
         COMMIT;
      END LOOP;

      fnd_file.put_line (fnd_file.output, '         ');
      fnd_file.put_line (fnd_file.output,
                            'Total Records processed by the Upload program : '
                         || l_rec_count
                        );
      fnd_file.put_line (fnd_file.output,
                            'Total Records Uploaded by the Upload program : '
                         || l_success_count
                        );
      fnd_file.put_line (fnd_file.output, '         ');
      fnd_file.put_line
         (fnd_file.output,
             'YKA - Upload Salary Proposal for a Period (XYKASALP) Completed on : '
          || TO_CHAR (SYSDATE, 'DD-MON-RRRR HH24:MI:SS')
         );
      fnd_file.put_line
         (fnd_file.output,
          '-----------------------------------------------------------------------------'
         );
   END;

  -- ------------------------------------------------
-- Procedure to process the Salary proposal API
-- based on the supplied parameters
-- p_period_name specifies the Period Name
-- ---------------
   PROCEDURE process_salary_proposal_api (p_period_name IN VARCHAR2)
   IS
      CURSOR salary_api (c_period_name IN VARCHAR2)
      IS
         SELECT proposal_id, employee_number, net_amount, effective_date,
                business_group_id, assignment_id, element_entry_id
           FROM xyka_salary_proposal_v
          WHERE TO_CHAR (effective_date, 'MON-RR') = c_period_name
            AND approved_flag = 'Y'
            AND process_flag = 'N';

      /* Local Variables */
      l_row_id                       VARCHAR2 (30);
      l_pay_proposal_id              NUMBER;
      l_element_entry_id             NUMBER;
      l_ovn                          NUMBER;
      l_inv_next_sal_date_warning    BOOLEAN;
      l_inv_next_perf_date_warning   BOOLEAN;
      l_proposed_salary_warning      BOOLEAN;
      l_approved_warning             BOOLEAN;
      l_payroll_warning              BOOLEAN;
      l_dummy                        BOOLEAN;
      l_approved                     VARCHAR2 (1);
      l_status                       VARCHAR2 (20);
      l_message                      VARCHAR2 (240);
   BEGIN
      FOR r IN salary_api (p_period_name)
      LOOP
         l_status := NULL;
         l_message := NULL;
     l_element_entry_id:=NULL;

         BEGIN
            /* Start processing the API */
            hr_maintain_proposal_api.insert_salary_proposal
                 (p_pay_proposal_id                => l_pay_proposal_id,
                  p_assignment_id                  => r.assignment_id,
                  p_business_group_id              => r.business_group_id,
                  p_change_date                    => r.effective_date,
                  p_comments                       => NULL,
                  p_next_sal_review_date           => NULL,
                  p_proposal_reason                => NULL,
                  p_proposed_salary_n              => r.net_amount,
                  p_forced_ranking                 => NULL,
                  p_performance_review_id          => NULL,
                  p_attribute_category             => NULL,
                  p_attribute1                     => NULL,
                  p_attribute2                     => NULL,
                  p_attribute3                     => NULL,
                  p_attribute4                     => NULL,
                  p_attribute5                     => NULL,
                  p_attribute6                     => NULL,
                  p_attribute7                     => NULL,
                  p_attribute8                     => NULL,
                  p_attribute9                     => NULL,
                  p_attribute10                    => NULL,
                  p_attribute11                    => NULL,
                  p_attribute12                    => NULL,
                  p_attribute13                    => NULL,
                  p_attribute14                    => NULL,
                  p_attribute15                    => NULL,
                  p_attribute16                    => NULL,
                  p_attribute17                    => NULL,
                  p_attribute18                    => NULL,
                  p_attribute19                    => NULL,
                  p_attribute20                    => NULL,
                  p_object_version_number          => l_ovn,
                  p_multiple_components            => 'N',
                  p_approved                       => 'Y',
                  p_validate                       => FALSE,
                  p_element_entry_id               => r.element_entry_id,
                  p_inv_next_sal_date_warning      => l_inv_next_sal_date_warning,
                  p_proposed_salary_warning        => l_proposed_salary_warning,
                  p_approved_warning               => l_approved_warning,
                  p_payroll_warning                => l_payroll_warning
                 );
            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               /* Catches the Exceptions */
               l_message := SQLERRM;
               l_status := 'ERROR';
         END;

         /* If any error occured then */
         IF l_status IS NOT NULL
         THEN
            UPDATE xyka_salary_proposal
               SET x_remarks = l_message,
                   x_approved_flag = 'N',
                   x_process_flag = 'N'
             WHERE x_proposal_id = r.proposal_id;

            COMMIT;
         /* If the proposal is processed then */
         ELSIF l_status IS NULL
         THEN
            UPDATE xyka_salary_proposal
               SET x_remarks = 'PROCESSED',
                   x_process_flag = 'Y'
             WHERE x_proposal_id = r.proposal_id;

            COMMIT;
         END IF;
      END LOOP;
   END process_salary_proposal_api;
END xyka_salary_proposal_api;
/
------------------ Execute --------------
INSERT INTO XYKA_SALARY_API (X_EMPLOYEE_NUMBER,X_EFFECTIVE_DATE,X_NET_AMOUNT,X_TRANSFER_DATE,X_REMARKS)VALUES(14699,TO_DATE (SYSDATE - 14, 'DD-MM-YY'),1200,NULL,'5% salary hike JUL-12');

 select to_date(sysdate-14,'DD-MM-YY') from dual

 commit 


 select * from XYKA_SALARY_API

 delete from XYKA_SALARY_API
 --edit XYKA_SALARY_API

 SELECT x_employee_number, x_effective_date, x_net_amount
           FROM xyka_salary_api
          WHERE TO_CHAR (x_effective_date, 'MON-RR') = :c_period_name
            AND x_transfer_date IS NULL
           
select * from fnd_user where user_name = '99999'
                
------------------ Procedure 1 -----------------------                 

DECLARE
   p_out1       VARCHAR2 (240);
   p_out2       VARCHAR2 (240);
   p_period     VARCHAR2 (10) := 'JUL-12';
   p_user_id    number := 1295;
   p_login_id   number := 1295;
BEGIN
   xyka_salary_proposal_api.import_records (p_out1,
                                            p_out2,
                                            p_period,
                                            p_user_id,
                                            p_login_id);
END;           

commit

-- Record gets added into

select * from xyka_salary_proposal

delete from xyka_salary_proposal  where X_APPROVED_FLAG = 'Y'

-- on success x_remark gets updated to TRANSFERRED  and trnsfer_date to SYSDATE

select * from xyka_salary_api

UPDATE   xyka_salary_proposal
   SET   X_APPROVED_FLAG = 'Y' ,
         X_EFFECTIVE_DATE = TO_DATE ('01-07-2012', 'DD-MM-YYYY'),
         LAST_UPDATE_DATE = TO_DATE ('01-07-2012', 'DD-MM-YYYY'),
         CREATION_DATE = TO_DATE ('01-07-2012', 'DD-MM-YYYY')
 --WHERE   X_EMPLOYEE_NUMBER = 14699

commit ;

select * from xyka_salary_proposal

--delete from xyka_salary_proposal where X_REMARKS  like 'ORA-20001: This date already exists. Please enter a new date.'


SELECT proposal_id, employee_number, net_amount, effective_date,
                business_group_id, assignment_id, element_entry_id
           FROM xyka_salary_proposal_v
          WHERE TO_CHAR (effective_date, 'MON-RR') = :c_period_name
            AND approved_flag = 'Y'
            AND process_flag = 'N';
           
           
-- call process_salary_proposal_api

DECLARE
   p_period_name   VARCHAR2 (10) := 'JUL-22';
BEGIN
   xyka_salary_proposal_api.process_salary_proposal_api (p_period_name);
END;

COMMIT

/* If any error occured then */
        x_approved_flag = 'N',
        x_process_flag = 'N'
       
ORA-20001: The effective date for all DateTracked operations must not include a time component.

/* If the proposal is processed then */
        x_remarks = 'PROCESSED',
        x_process_flag = 'Y'

select * from xyka_salary_proposal

select * from XYKA_SALARY_PROPOSAL_V

PO - Action History

********** PO - Action History **********

  SELECT   pah.object_id,
           pah.object_type_code,
           pah.object_sub_type_code,
           pah.sequence_num,
           pah.last_update_date,
           pah.last_updated_by,
           pah.creation_date,
           pah.created_by,
           pah.action_code,
           plc1.displayed_field Action,
           pah.action_date,
           pah.employee_id,
           hr.full_name Employee,
           pah.approval_path_id,
           pah.note,
           pah.object_revision_num,
           pah.offline_code,
           pah.last_update_login,
           pah.request_id,
           pah.program_application_id,
           pah.program_id,
           pah.program_update_date,
           pah.program_date,
           pah.approval_group_id
    FROM   po_action_history pah,
           po_lookup_codes plc1,
           po_lookup_codes plc2,
           per_all_people_f hr
   WHERE       pah.object_id = 176977  -- po_header_id
           AND pah.object_type_code <> 'RELEASE'
           AND pah.object_sub_type_code = 'STANDARD'
           AND hr.person_id = pah.employee_id
           AND pah.action_date BETWEEN hr.effective_start_date
                                   AND  hr.effective_end_date
           AND plc1.lookup_code(+) = pah.action_code
           AND plc1.lookup_type(+) = 'APPROVER ACTIONS'
           AND plc2.lookup_code(+) = pah.action_code
           AND plc2.lookup_type(+) = 'CONTROL ACTIONS'
ORDER BY   pah.sequence_num


********* PO - Approval Hierarchy *******
SELECT   det.lev "Level",
         det.subordinate_POSITION_ID "Subordinate Position Id",
         det.NAME "Name",
         DECODE (
            (SELECT   COUNT ( * ) cnt
               FROM   PER_ALL_ASSIGNMENTS_F paa
              WHERE   paa.position_id = det.subordinate_POSITION_ID
                      AND SYSDATE BETWEEN paa.effective_start_date
                                      AND  NVL (paa.effective_end_date,
                                                SYSDATE)),
            1,
            (SELECT   full_name
               FROM   PER_ALL_ASSIGNMENTS_F paa, per_all_people_f pp
              WHERE   paa.position_id = det.subordinate_POSITION_ID
                      AND SYSDATE BETWEEN paa.effective_start_date
                                      AND  NVL (paa.effective_end_date,
                                                SYSDATE)
                      AND SYSDATE BETWEEN pp.effective_start_date
                                      AND  NVL (pp.effective_end_date,
                                                SYSDATE)
                      AND paa.PERSON_ID = pp.person_id),
            'Multiple Employees attached to this Position'
         )
            Employee_Name
  FROM   (SELECT   ps.lev, PS.subordinate_POSITION_ID, pos.NAME
            FROM   (    SELECT   LEVEL lev, PS.subordinate_POSITION_ID
                          FROM   per_pos_structure_elements PS
                    START WITH   PS.POS_STRUCTURE_VERSION_ID = NVL (62, 0)
                                 AND PS.SUBORDINATE_POSITION_ID = 4087
                    CONNECT BY   PRIOR PS.PARENT_POSITION_ID =
                                    PS.SUBORDINATE_POSITION_ID
                                 AND PS.POS_STRUCTURE_VERSION_ID =
                                       NVL (62, 0)) ps,
                   per_positions POS
           WHERE   ps.subordinate_POSITION_ID = pos.POSITION_ID) det

********* PO - AP *********

SELECT   ih.invoice_num "Invoice Num",
         pla.line_num "PO Line Num",
         plla.shipment_num "PO Shipment Num",
         plla.quantity_billed "Shipment Qty Billed"
  FROM   ap_invoices ih,
         ap_invoice_distributions id,
         po_headers ph,
         po_distributions pd,
         po_lines pla,
         po_line_locations plla
 WHERE       ih.invoice_id(+) = id.invoice_id
         AND id.po_distribution_id(+) = pd.po_distribution_id
         AND ph.po_header_id = pd.po_header_id
         AND ph.po_header_id = pla.po_header_id
         AND pla.po_line_id = plla.po_line_id
         AND plla.line_location_id = pd.line_location_id
         AND ih.invoice_num IS NOT NULL
         AND NVL (plla.po_release_id, 0) = 0
         AND ph.po_header_id = 176977