DBA Data[Home] [Help]

APPS.PAY_IN_24QC_ARCHIVE SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 36

      pay_in_utils.set_location(g_debug,'Number of assignment actions deleted : '|| g_count_sal_delete ,4);
Line: 37

      FOR i IN 1..g_count_sal_delete -1
      LOOP
         pay_in_utils.trace('Last Action Context ID is : ', g_sal_data_rec_del(i).last_action_context_id );
Line: 62

      pay_in_utils.trace('Number of assignment actions updated : ', g_count_sal_update );
Line: 63

      FOR i IN 1..g_count_sal_update -1
      LOOP
         pay_in_utils.trace('Last Action Context ID is : ', g_sal_data_rec_upd(i).last_action_context_id );
Line: 79

      pay_in_utils.set_location(g_debug,'Number of element entries deleted : '|| g_count_ee_delete ,4);
Line: 80

      FOR i IN 1..g_count_ee_delete -1
      LOOP
         pay_in_utils.trace('Last Action Context ID is : ', g_ee_data_rec_del(i).last_action_context_id );
Line: 105

      pay_in_utils.trace('Number of element entries updated : ', g_count_ee_update );
Line: 106

      FOR i IN 1..g_count_ee_update -1
      LOOP
         pay_in_utils.trace('Last Action Context ID is : ', g_ee_data_rec_upd(i).last_action_context_id );
Line: 151

      pay_in_utils.trace('Number of updated challans  is : ', g_count_challan_upd);
Line: 264

       SELECT 1
         FROM pay_action_information
        WHERE action_information_category = 'IN_24QC_PERSON'
          AND action_context_type         = 'AAP'
          AND source_id                   = p_source_id
          AND action_information10        = p_mode
          AND action_context_id IN
                                (SELECT assignment_action_id
                                   FROM pay_assignment_actions
                                  WHERE payroll_action_id  = g_payroll_action_id
                                );
Line: 279

       SELECT 1
         FROM pay_action_information
        WHERE action_information_category = 'IN_24QC_DEDUCTEE'
          AND action_context_type         = 'AAP'
          AND source_id                   = p_source_id
          AND action_information15        = p_mode
          AND action_context_id IN
                                (SELECT assignment_action_id
                                   FROM pay_assignment_actions
                                  WHERE payroll_action_id  = g_payroll_action_id
                                );
Line: 293

       SELECT 1
         FROM pay_action_information
        WHERE action_information_category = 'IN_24QC_ORG'
          AND action_context_type         = 'PA'
          AND action_context_id           = g_payroll_action_id
          AND action_information1         = g_gre_id;
Line: 302

       SELECT 1
         FROM pay_action_information
        WHERE action_information_category = 'IN_24QC_CHALLAN'
          AND action_context_type         = 'PA'
          AND action_context_id           = g_payroll_action_id
          AND action_information1         = p_act_information1
          AND action_information3         = g_gre_id;
Line: 375

    SELECT SUBSTR(legislative_parameters,
                   INSTR(legislative_parameters,p_token)+(LENGTH(p_token)+1),
                    INSTR(legislative_parameters,' ',
                           INSTR(legislative_parameters,p_token))
                     - (INSTR(legislative_parameters,p_token)+LENGTH(p_token)))
           ,business_group_id
      FROM  pay_payroll_actions
     WHERE  payroll_action_id = p_pact_id;
Line: 440

   SELECT 1
    FROM pay_action_information pai
        ,pay_payroll_actions    ppa
   WHERE pai.action_information_category = 'IN_24QC_ORG'
     AND pai.action_context_type         = 'PA'
     AND pai.action_information1         = g_gre_id
     AND pai.action_information3         = g_year||g_quarter
     AND pai.action_information30        = g_24qc_reference
     AND pai.action_context_id           = ppa.payroll_action_id
     AND ppa.action_type                 = 'X'
     AND ppa.action_status               = 'C'
     AND ppa.payroll_action_id  <> p_payroll_action_id;
Line: 549

    SELECT NVL(pos.actual_termination_date,(fnd_date.string_to_date('31-12-4712','DD-MM-YYYY')))
      FROM   per_all_assignments_f  asg
            ,per_periods_of_service pos
     WHERE asg.person_id         = pos.person_id
       AND asg.assignment_id     = p_assignment_id
       AND asg.business_group_id = pos.business_group_id
       AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
       AND NVL(pos.actual_termination_date,(TO_DATE('31-12-4712','DD-MM-YYYY')))
             BETWEEN asg.effective_start_date AND asg.effective_end_date
     ORDER BY 1 DESC;
Line: 644

  FUNCTION update_challans(p_challan_number    IN VARCHAR2)
  RETURN VARCHAR2
  IS
    CURSOR c_24q_ee_sum(p_challan_number       VARCHAR2
                       ,p_24q_pay_action_id   NUMBER
                       )
    IS
        SELECT SUM(NVL(pai.action_information9,0)) amount_deposited
          FROM pay_action_information  pai
              ,pay_assignment_actions  paa
         WHERE pai.action_information_category = 'IN_24Q_DEDUCTEE'
           AND paa.assignment_action_id        = pai.action_context_id
           AND pai.action_information1         = p_challan_number
           AND paa.payroll_action_id           = p_24q_pay_action_id;
Line: 666

     l_procedure := g_package ||'.update_challans';
Line: 692

     pay_in_utils.set_location(g_debug,'Leaving: '|| g_package||'.update_challans',1);
Line: 698

  END update_challans;
Line: 722

   SELECT pai.action_information1
         ,MAX(pai.action_context_id)
     FROM pay_action_information pai
         ,pay_action_interlocks locks
         ,pay_assignment_actions paa
         ,pay_assignment_actions paa_24qc
    WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
      AND locks.locking_action_id         = pai.action_context_id
      AND locks.locked_action_id          = paa.assignment_action_id
      AND paa.payroll_action_id           = g_24q_payroll_act_id
      AND paa.assignment_id               = pai.assignment_id
      AND pai.action_information2         = g_year||g_quarter
      AND paa_24qc.assignment_id          = pai.assignment_id
      AND paa_24qc.assignment_action_id   = pai.action_context_id
      AND pai.action_information15        = 'A'
      AND pai.source_id                   = p_element_entry_id
      AND paa_24qc.payroll_action_id IN(
                                        SELECT org_information3
                                          FROM hr_organization_information
                                         WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                           AND organization_id  = g_gre_id
                                           AND org_information1 = g_year
                                           AND org_information2 = g_quarter
                                           AND org_information5 = 'A'
                                           AND org_information6 = 'C'
                                        )
      GROUP BY pai.action_information1;
Line: 795

    CURSOR c_delete_mode_entries(p_payroll_action_id        NUMBER)
    IS
       SELECT pai.source_id           element_entry_id
             ,pai.assignment_id       assignment_id
             ,pai.action_context_id   last_action_context_id
         FROM pay_action_information pai
             ,pay_assignment_actions paa
        WHERE pai.action_information_category LIKE 'IN_24Q%_DEDUCTEE'
          AND pai.action_context_id = paa.assignment_action_id
          AND(
                paa.payroll_action_id IN (
                                           SELECT org_information3
                                             FROM hr_organization_information
                                            WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                              AND organization_id  = g_gre_id
                                              AND org_information1 = g_year
                                              AND org_information2 = g_quarter
                                              AND org_information5 = 'A'
                                          )
             )
          AND pai.action_information3 = g_gre_id
          AND (
                pai.source_id NOT IN
                       (SELECT entry.element_entry_id
                          FROM pay_element_entries_f entry
                              ,pay_element_types_f   types
                         WHERE entry.assignment_id = pai.assignment_id
                           AND entry.element_type_id = types.element_type_id
                           AND types.element_name = 'Income Tax Challan Information'
                           AND types.legislation_code = 'IN'
                           AND entry.effective_start_date BETWEEN g_qr_start_date
                           AND g_qr_end_date
                        )
               OR
               pai.action_information1 <> pay_in_utils.get_ee_value(pai.source_id,'Challan or Voucher Number')
             )
         AND (
                pai.source_id NOT IN
                      (SELECT pai.source_id
                         FROM pay_action_information pai
                             ,pay_action_interlocks locks
                             ,pay_assignment_actions paa
                             ,pay_assignment_actions masters
                        WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
                          AND pai.action_information15        = 'D'
                          AND locks.locking_action_id         = pai.action_context_id
                          AND locks.locked_action_id          = paa.assignment_action_id
                          AND paa.payroll_action_id           = p_payroll_action_id
                          AND paa.assignment_id               = pai.assignment_id
                          AND masters.assignment_id            = pai.assignment_id
                          AND masters.assignment_action_id     = pai.action_context_id
                          AND masters.payroll_action_id IN(
                                                           SELECT org_information3
                                                             FROM hr_organization_information
                                                            WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                              AND organization_id  = g_gre_id
                                                              AND org_information1 = g_year
                                                              AND org_information2 = g_quarter
                                                              AND org_information5 = 'A'
                                                              AND org_information6 = 'C'
                                                     )
                      )
              OR
              pay_in_utils.get_ee_value(pai.source_id,'Challan or Voucher Number') NOT IN
              (
                      (SELECT pai.action_information1
                         FROM pay_action_information pai
                             ,pay_action_interlocks locks
                             ,pay_assignment_actions paa
                             ,pay_assignment_actions masters
                        WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
                          AND pai.action_information15        = 'A'
                          AND locks.locking_action_id         = pai.action_context_id
                          AND locks.locked_action_id          = paa.assignment_action_id
                          AND paa.payroll_action_id           = p_payroll_action_id
                          AND paa.assignment_id               = pai.assignment_id
                          AND masters.assignment_id            = pai.assignment_id
                          AND masters.assignment_action_id     = pai.action_context_id
                          AND masters.payroll_action_id IN(
                                                            SELECT org_information3
                                                              FROM hr_organization_information
                                                             WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                               AND organization_id  = g_gre_id
                                                               AND org_information1 = g_year
                                                               AND org_information2 = g_quarter
                                                               AND org_information5 = 'A'
                                                               AND org_information6 = 'C'
                                                     )
                      )
              )
           )
           ORDER BY 3 DESC;
Line: 890

      SELECT entry.element_entry_id  element_entry_id
            ,entry.assignment_id     assignment_id
            ,NULL                    last_action_context_id
            ,pay_in_utils.get_ee_value(entry.element_entry_id,'Challan or Voucher Number')
        FROM pay_element_entries_f entry
            ,pay_element_types_f   types
       WHERE entry.element_type_id = types.element_type_id
         AND types.element_name    = 'Income Tax Challan Information'
         AND types.legislation_code = 'IN'
         AND entry.effective_start_date BETWEEN g_qr_start_date AND g_qr_end_date
         AND (
                entry.element_entry_id NOT IN
                      (SELECT pai.source_id
                         FROM pay_action_information pai
                             ,pay_assignment_actions paa
                        WHERE pai.action_information_category = 'IN_24Q_DEDUCTEE'
                          AND pai.action_context_id   = paa.assignment_action_id
                          AND paa.payroll_action_id   = p_payroll_action_id
                      )
              OR
              pay_in_utils.get_ee_value(entry.element_entry_id,'Challan or Voucher Number') NOT IN
                      (SELECT pai.action_information1
                         FROM pay_action_information pai
                             ,pay_assignment_actions paa
                        WHERE pai.action_information_category = 'IN_24Q_DEDUCTEE'
                          AND pai.action_context_id   = paa.assignment_action_id
                          AND paa.payroll_action_id   = p_payroll_action_id
                          AND pai.source_id           = entry.element_entry_id
                      )
             )
         AND (
                 entry.element_entry_id NOT IN
                      (SELECT pai.source_id
                         FROM pay_action_information pai
                             ,pay_action_interlocks locks
                             ,pay_assignment_actions paa
                             ,pay_assignment_actions paa_24qc
                        WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
                          AND locks.locking_action_id         = pai.action_context_id
                          AND locks.locked_action_id          = paa.assignment_action_id
                          AND paa.payroll_action_id           = p_payroll_action_id
                          AND paa.assignment_id               = pai.assignment_id
                          AND pai.action_information2         = g_year||g_quarter
                          AND paa_24qc.assignment_id          = pai.assignment_id
                          AND paa_24qc.assignment_action_id   = pai.action_context_id
                          AND paa_24qc.payroll_action_id IN(
                                                      SELECT org_information3
                                                        FROM hr_organization_information
                                                       WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                         AND organization_id  = g_gre_id
                                                         AND org_information1 = g_year
                                                         AND org_information2 = g_quarter
                                                         AND org_information5 = 'A'
                                                         AND org_information6 = 'C'
                                                     )
                      )
              OR
              pay_in_utils.get_ee_value(entry.element_entry_id,'Challan or Voucher Number') NOT IN
              (
                      (SELECT pai.action_information1
                         FROM pay_action_information pai
                             ,pay_action_interlocks locks
                             ,pay_assignment_actions paa
                             ,pay_assignment_actions paa_24qc
                        WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
                          AND locks.locking_action_id         = pai.action_context_id
                          AND locks.locked_action_id          = paa.assignment_action_id
                          AND paa.payroll_action_id           = p_payroll_action_id
                          AND paa.assignment_id               = pai.assignment_id
                          AND pai.action_information2         = g_year||g_quarter
                          AND paa_24qc.assignment_id          = pai.assignment_id
                          AND paa_24qc.assignment_action_id   = pai.action_context_id
                          AND pai.source_id                   = entry.element_entry_id
                          AND paa_24qc.payroll_action_id IN(
                                                      SELECT org_information3
                                                        FROM hr_organization_information
                                                       WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                         AND organization_id  = g_gre_id
                                                         AND org_information1 = g_year
                                                         AND org_information2 = g_quarter
                                                         AND org_information5 = 'A'
                                                         AND org_information6 = 'C'
                                                     )
                      )
              )
            )
         AND pay_in_utils.get_ee_value(entry.element_entry_id,'Challan or Voucher Number') IN
             (
              SELECT hoi.org_information3
                FROM hr_organization_information hoi
                    ,hr_organization_units hou
               WHERE hoi.organization_id          = g_gre_id
                 AND  hoi.organization_id         = hou.organization_id
                 AND  hou.business_group_id       = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
                 AND  hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
                 AND  hoi.org_information1        = g_tax_year
                 AND  hoi.org_information13       = g_quarter
            )
         AND (
                (
                    g_correction_mode IN ('%','C3')
                    AND
                    pay_in_utils.get_ee_value(entry.element_entry_id,'Challan or Voucher Number') IN
                    (
                                       SELECT action_information1
                                         FROM pay_action_information
                                        WHERE action_information_category IN('IN_24QC_CHALLAN','IN_24Q_CHALLAN')
                                          AND (
                                                action_information15 = p_payroll_action_id
                                                AND action_information_category = 'IN_24QC_CHALLAN'
                                                AND action_information2 = g_year||g_quarter
                                                AND action_information15 IN
                                                                           (
                                                                            SELECT hoi.org_information3
                                                                              FROM hr_organization_information hoi
                                                                                  ,hr_organization_units hou
                                                                             WHERE hoi.organization_id          = g_gre_id
                                                                               AND  hoi.organization_id         = hou.organization_id
                                                                               AND  hou.business_group_id       = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
                                                                               AND  hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
                                                                               AND  hoi.org_information1        = g_tax_year
                                                                               AND  hoi.org_information13       = g_quarter
                                                                               AND  hoi.org_information6        = 'C'
                                                                           )
                                                )
                                                OR
                                                (
                                                action_information_category = 'IN_24Q_CHALLAN'
                                                AND action_context_id   = p_payroll_action_id
                                                )
                    )
                )
                OR
                (g_correction_mode IN ('%','C9')
                AND
                pay_in_utils.get_ee_value(entry.element_entry_id,'Challan or Voucher Number') NOT IN
                       (
                                       SELECT action_information1
                                         FROM pay_action_information
                                        WHERE action_information_category IN('IN_24QC_CHALLAN','IN_24Q_CHALLAN')
                                          AND (
                                                action_information15 = p_payroll_action_id
                                                AND action_information_category = 'IN_24QC_CHALLAN'
                                                AND action_information2 = g_year||g_quarter
                                                AND action_information15 IN
                                                                           (
                                                                            SELECT hoi.org_information3
                                                                              FROM hr_organization_information hoi
                                                                                  ,hr_organization_units hou
                                                                             WHERE hoi.organization_id          = g_gre_id
                                                                               AND  hoi.organization_id         = hou.organization_id
                                                                               AND  hou.business_group_id       = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
                                                                               AND  hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
                                                                               AND  hoi.org_information1        = g_tax_year
                                                                               AND  hoi.org_information13       = g_quarter
                                                                               AND  hoi.org_information6        = 'C'
                                                                           )
                                                )
                                                OR
                                                (
                                                action_information_category = 'IN_24Q_CHALLAN'
                                                AND action_context_id   = p_payroll_action_id
                                                )
                        )
                )
              )
              ORDER BY 4;
Line: 1058

    CURSOR c_update_mode_entries(p_payroll_action_id        NUMBER
                                )
    IS
       SELECT pai.source_id     element_entry_id
             ,pai.assignment_id assignment_id
             ,pai.action_context_id   last_action_context_id
             ,pep.effective_end_date
         FROM pay_action_information pai
             ,pay_assignment_actions  paa
             ,per_people_f            pep
             ,per_assignments_f       asg
        WHERE pai.action_information_category = 'IN_24Q_DEDUCTEE'
          AND pai.action_context_id = paa.assignment_action_id
          AND pai.action_information3 = g_gre_id
          AND paa.payroll_action_id = p_payroll_action_id
          AND asg.assignment_id     = pai.assignment_id
          AND asg.person_id         = pep.person_id
          AND asg.business_group_id = pep.business_group_id
          AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
          AND (
                (
                  (NVL(pai.action_information13,'0') <> (SELECT  NVL(paei.aei_information2,'0')
                                                           FROM  per_assignment_extra_info paei
                                                                ,per_assignments_f paa
                                                          WHERE  paei.information_type = 'PER_IN_TAX_EXEMPTION_DF'
                                                            AND  paei.aei_information_category = 'PER_IN_TAX_EXEMPTION_DF'
                                                            AND  paei.assignment_id = paa.assignment_id
                                                            AND  paa.assignment_id  = asg.assignment_id
                                                            AND  paei.aei_information1 = g_tax_year
                                                            AND  assignment_end_date(asg.assignment_id) BETWEEN paa.effective_start_date AND paa.effective_end_date
                                                            AND  ROWNUM = 1)
                 )
               OR
                  (TO_NUMBER(remove_curr_format(pai.action_information6))  <> TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Income Tax Deducted'))))
               OR
                  (TO_NUMBER(remove_curr_format(pai.action_information7))  <> TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Surcharge Deducted'))))
               OR
                  (TO_NUMBER(remove_curr_format(pai.action_information8))  <> TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Education Cess Deducted'))))
               OR
                  (TO_NUMBER(remove_curr_format(pai.action_information5))  <> TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Taxable Income'))))
               OR
                  (pai.action_information4  <> pay_in_utils.get_ee_value(pai.source_id,'Payment Date'))
               OR
                  (pai.action_information12 <> hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title))
               OR
                  (NVL(pai.action_information11,'0') <> NVL(pep.per_information14,'0'))
               OR(
                     (
                           g_correction_mode IN ('C5','%')
                       AND (pai.action_information10 NOT IN('APPLIEDFOR','PANNOTAVBL'))
                       AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))NOT IN('APPLIEDFOR','PANNOTAVBL'))
                       AND (pai.action_information10 <> (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
                     )
                     OR
                     (
                       g_correction_mode NOT IN ('C5')
                       AND (pai.action_information10 <> (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
                       AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))IN('APPLIEDFOR','PANNOTAVBL'))
                     )
                )
               )
              AND pay_in_utils.get_ee_value(pai.source_id,'Challan or Voucher Number') = pai.action_information1
             )
          AND   assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
          AND   assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date
          ORDER BY 4 DESC;
Line: 1131

       SELECT pai_locking.action_context_id  last_action_context_id
         FROM pay_action_information pai_locking
             ,pay_action_interlocks locks
             ,pay_assignment_actions paa
        WHERE pai_locking.action_information_category = 'IN_24QC_DEDUCTEE'
          AND pai_locking.action_information3  = g_gre_id
          AND pai_locking.action_information15 = p_mode
          AND pai_locking.action_context_id    = paa.assignment_action_id
          AND paa.payroll_action_id IN
                                    (
                                      SELECT org_information3
                                        FROM hr_organization_information
                                       WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                         AND organization_id  = g_gre_id
                                         AND org_information1 = g_year
                                         AND org_information2 = g_quarter
                                         AND org_information5 = 'A'
                                         AND org_information6 = 'C'
                                    )
          AND locks.locking_action_id          = pai_locking.action_context_id
          AND locks.locked_action_id           = p_24q_arc_action_context_id
          AND pai_locking.assignment_id        = p_assignment_id
          AND pai_locking.source_id            = p_element_entry_id
        ORDER BY pai_locking.action_context_id DESC;
Line: 1160

       SELECT pai.action_context_id
         FROM pay_action_information pai
             ,per_people_f           pep
             ,per_assignments_f      asg
             ,pay_assignment_actions paa
        WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
          AND paa.assignment_action_id = pai.action_context_id
          AND pai.source_id     = p_element_entry_id
          AND paa.payroll_action_id IN
                           (
                             SELECT org_information3
                               FROM hr_organization_information
                              WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                AND organization_id  = g_gre_id
                                AND org_information1 = g_year
                                AND org_information2 = g_quarter
                                AND org_information5 = 'A'
                                AND org_information6 = 'C'
                          )
          AND pai.action_information3 = g_gre_id
          AND pai.action_context_id = p_assignment_action_id
          AND asg.assignment_id     = pai.assignment_id
          AND asg.person_id         = pep.person_id
          AND asg.business_group_id = pep.business_group_id
          AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
          AND (
                  (NVL(pai.action_information18,'0') <> (SELECT  NVL(paei.aei_information2,'0')
                                                           FROM  per_assignment_extra_info paei
                                                                ,per_assignments_f paa
                                                          WHERE  paei.information_type = 'PER_IN_TAX_EXEMPTION_DF'
                                                            AND  paei.aei_information_category = 'PER_IN_TAX_EXEMPTION_DF'
                                                            AND  paei.assignment_id = paa.assignment_id
                                                            AND  paa.assignment_id = asg.assignment_id
                                                            AND  paei.aei_information1 = g_tax_year
                                                            AND  assignment_end_date(asg.assignment_id) BETWEEN paa.effective_start_date AND paa.effective_end_date
                                                            AND  ROWNUM = 1)
                 )
               OR
                  (TO_NUMBER(remove_curr_format(pai.action_information6))  <> TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Income Tax Deducted'))))
               OR
                  (TO_NUMBER(remove_curr_format(pai.action_information7))  <> TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Surcharge Deducted'))))
               OR
                  (TO_NUMBER(remove_curr_format(pai.action_information8))  <> TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Education Cess Deducted'))))
               OR
                  (TO_NUMBER(remove_curr_format(pai.action_information5))  <> TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Taxable Income'))))
               OR
                  (pai.action_information4  <> pay_in_utils.get_ee_value(pai.source_id,'Payment Date'))
               OR
                  (pai.action_information10 <> hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title))
               OR(--Checking PAN Number
                    (
                           g_correction_mode IN ('C5','%')
                       AND (pai.action_information9 NOT IN('APPLIEDFOR','PANNOTAVBL'))
                       AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))NOT IN('APPLIEDFOR','PANNOTAVBL'))
                       AND (pai.action_information9 <> (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
                     )
                     OR
                     (
                       g_correction_mode NOT IN ('C5')
                       AND (pai.action_information9 <> (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
                       AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))IN('APPLIEDFOR','PANNOTAVBL'))
                     )
                )
              OR -- Checking PAN reference Number
                  (NVL(pai.action_information11,'0') <> NVL(pep.per_information14,'0'))
              )
          AND  assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
          AND  assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date
          AND NOT EXISTS
          (
       SELECT 1
         FROM pay_action_information pai
             ,per_people_f           pep
             ,per_assignments_f      asg
        WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
          AND pai.source_id            = p_element_entry_id
          AND pai.action_information3  = g_gre_id
          AND pai.action_context_id    = p_assignment_action_id
          AND asg.assignment_id        = pai.assignment_id
          AND asg.person_id            = pep.person_id
          AND asg.business_group_id    = pep.business_group_id
          AND asg.business_group_id    = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
          AND (
                  (NVL(pai.action_information18,'0') = (SELECT  NVL(paei.aei_information2,'0')
                                                           FROM  per_assignment_extra_info paei
                                                                ,per_assignments_f paa
                                                          WHERE  paei.information_type = 'PER_IN_TAX_EXEMPTION_DF'
                                                            AND  paei.aei_information_category = 'PER_IN_TAX_EXEMPTION_DF'
                                                            AND  paei.assignment_id = paa.assignment_id
                                                            AND  paa.assignment_id = asg.assignment_id
                                                            AND  paei.aei_information1 = g_tax_year
                                                            AND  assignment_end_date(asg.assignment_id) BETWEEN paa.effective_start_date AND paa.effective_end_date
                                                            AND  ROWNUM = 1)
                 )
               AND
                  (TO_NUMBER(remove_curr_format(pai.action_information6))  = TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Income Tax Deducted'))))
               AND
                  (TO_NUMBER(remove_curr_format(pai.action_information7))  = TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Surcharge Deducted'))))
               AND
                  (TO_NUMBER(remove_curr_format(pai.action_information8))  = TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Education Cess Deducted'))))
               AND
                  (TO_NUMBER(remove_curr_format(pai.action_information5))  = TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Taxable Income'))))
               AND
                  (pai.action_information4  = pay_in_utils.get_ee_value(pai.source_id,'Payment Date'))
               AND
                  (pai.action_information10 = hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title))
               AND(--Checking PAN Number
                     (
                           g_correction_mode IN ('C5','%')
                       AND (pai.action_information9 NOT IN('APPLIEDFOR','PANNOTAVBL'))
                       AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))NOT IN('APPLIEDFOR','PANNOTAVBL'))
                       AND (pai.action_information9 = (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
                     )
                     OR
                     (
                       g_correction_mode NOT IN ('C5')
                       AND (pai.action_information9 = (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
                       AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))IN('APPLIEDFOR','PANNOTAVBL'))
                     )
                )
              AND -- Checking PAN reference Number
                  (NVL(pai.action_information11,'0') = NVL(pep.per_information14,'0'))
              )
          AND  assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
          AND  assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date
         );
Line: 1291

      SELECT MAX(pai.action_context_id) last_action_context_id
            ,pai.source_id              element_entry_id
            ,pai.assignment_id          assignment_id
        FROM pay_action_information pai
            ,pay_assignment_actions paa
       WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
         AND pai.action_information3         = g_gre_id
         AND pai.action_information2         = g_year||g_quarter
         AND pai.action_information15        = p_mode
         AND pai.action_context_id           = paa.assignment_action_id
         AND paa.payroll_action_id IN
                              (
                                SELECT org_information3
                                  FROM hr_organization_information
                                 WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                   AND organization_id  = g_gre_id
                                   AND org_information1 = g_year
                                   AND org_information2 = g_quarter
                                   AND org_information5 = 'A'
                                   AND org_information6 = 'C'
                              )
        AND (
                p_flag = 1
                OR
                (
                 pai.source_id NOT IN(
                                SELECT pai.source_id
                                  FROM pay_action_information pai
                                      ,pay_assignment_actions paa
                                 WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
                                   AND pai.action_information3         = g_gre_id
                                   AND pai.action_information2         = g_year||g_quarter
                                   AND pai.action_information15        = 'D'
                                   AND pai.action_context_id           = paa.assignment_action_id
                                   AND paa.payroll_action_id IN
                                                        (
                                                              SELECT org_information3
                                                                FROM hr_organization_information
                                                               WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                                 AND organization_id  = g_gre_id
                                                                 AND org_information1 = g_year
                                                                 AND org_information2 = g_quarter
                                                                 AND org_information5 = 'A'
                                                                 AND org_information6 = 'C'
                                                        )
                                   )
                AND pai.source_id IN
                                     (SELECT entry.element_entry_id
                                        FROM pay_element_entries_f entry
                                            ,pay_element_types_f   types
                                       WHERE entry.assignment_id = pai.assignment_id
                                         AND entry.element_type_id = types.element_type_id
                                         AND types.element_name = 'Income Tax Challan Information'
                                         AND types.legislation_code = 'IN'
                                         AND entry.effective_start_date BETWEEN g_qr_start_date
                                         AND g_qr_end_date
                                        )
                )
            )
        GROUP BY pai.source_id,pai.assignment_id;
Line: 1354

      SELECT entry.element_entry_id  element_entry_id
            ,entry.assignment_id     assignment_id
            ,pay_in_utils.get_ee_value(entry.element_entry_id,'Challan or Voucher Number') challan
        FROM pay_element_entries_f      entry
            ,pay_element_types_f        types
       WHERE entry.element_type_id   = types.element_type_id
         AND types.element_name      = 'Income Tax Challan Information'
         AND types.legislation_code  = 'IN'
         AND entry.effective_start_date BETWEEN g_qr_start_date AND g_qr_end_date;
Line: 1366

      SELECT 1
        FROM per_assignments_f
       WHERE business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
         AND assignment_id     = p_assignment_id;
Line: 1389

    pay_in_utils.set_location(g_debug,'Finding element entries deleted since last statement ',2);
Line: 1390

   FOR c_rec IN c_delete_mode_entries(p_payroll_action_id)
   LOOP
          l_bg_check := 0;
Line: 1399

               g_ee_data_rec_del(g_count_ee_delete).last_action_context_id := c_rec.last_action_context_id;
Line: 1400

               g_ee_data_rec_del(g_count_ee_delete).assignment_id          := c_rec.assignment_id;
Line: 1401

               g_ee_data_rec_del(g_count_ee_delete).element_entry_id       := c_rec.element_entry_id;
Line: 1402

               g_ee_data_rec_del(g_count_ee_delete).deductee_mode          := 'D';
Line: 1403

               g_count_ee_delete := g_count_ee_delete + 1;
Line: 1449

    pay_in_utils.set_location(g_debug,'Finding element entries updated since last statement ',4);
Line: 1453

    FOR csr_rec IN c_update_mode_entries(p_payroll_action_id)
    LOOP
        pay_in_utils.set_location(g_debug,'Diff between live data and 24Q Archival found', 1);
Line: 1479

        pay_in_utils.set_location(g_debug,'Mid way in finding updated element entries ', 5);
Line: 1484

           g_ee_data_rec_upd(g_count_ee_update).last_action_context_id := csr_rec.last_action_context_id;
Line: 1485

           g_ee_data_rec_upd(g_count_ee_update).assignment_id          := csr_rec.assignment_id;
Line: 1486

           g_ee_data_rec_upd(g_count_ee_update).element_entry_id       := csr_rec.element_entry_id;
Line: 1487

           g_ee_data_rec_upd(g_count_ee_update).deductee_mode          := 'U';
Line: 1488

           g_count_ee_update := g_count_ee_update + 1;
Line: 1501

                     g_ee_data_rec_upd(g_count_ee_update).last_action_context_id := l_dummy;
Line: 1502

                     g_ee_data_rec_upd(g_count_ee_update).assignment_id          := csr_rec.assignment_id;
Line: 1503

                     g_ee_data_rec_upd(g_count_ee_update).element_entry_id       := csr_rec.element_entry_id;
Line: 1504

                     g_ee_data_rec_upd(g_count_ee_update).deductee_mode          := 'U';
Line: 1505

                     g_count_ee_update := g_count_ee_update + 1;
Line: 1510

        pay_in_utils.set_location(g_debug,'Additional Checking on updated element entries ', 6);
Line: 1534

            FOR i IN 1.. g_count_ee_update - 1
            LOOP
               IF (
                  (g_ee_data_rec_upd(i).last_action_context_id = c_rec.last_action_context_id)
                  AND
                  (g_ee_data_rec_upd(i).assignment_id          = c_rec.assignment_id)
                  AND
                  (g_ee_data_rec_upd(i).element_entry_id       = c_rec.element_entry_id)
                  AND
                  (g_ee_data_rec_upd(i).deductee_mode          = 'U')
                  )
               THEN
                    l_flag := FALSE;
Line: 1556

               g_ee_data_rec_upd(g_count_ee_update).last_action_context_id := c_rec.last_action_context_id;
Line: 1557

               g_ee_data_rec_upd(g_count_ee_update).assignment_id          := c_rec.assignment_id;
Line: 1558

               g_ee_data_rec_upd(g_count_ee_update).element_entry_id       := c_rec.element_entry_id;
Line: 1559

               g_ee_data_rec_upd(g_count_ee_update).deductee_mode          := 'U';
Line: 1560

               g_count_ee_update := g_count_ee_update + 1;
Line: 1566

    pay_in_utils.set_location(g_debug,'Now Searching for those updated element entries that were 24QC+',1);
Line: 1580

               g_ee_data_rec_upd(g_count_ee_update).last_action_context_id := c_rec.last_action_context_id;
Line: 1581

               g_ee_data_rec_upd(g_count_ee_update).assignment_id          := c_rec.assignment_id;
Line: 1582

               g_ee_data_rec_upd(g_count_ee_update).element_entry_id       := c_rec.element_entry_id;
Line: 1583

               g_ee_data_rec_upd(g_count_ee_update).deductee_mode          := 'U';
Line: 1584

               g_count_ee_update := g_count_ee_update + 1;
Line: 1662

  SELECT  NVL(action_information2,0)
    FROM  pay_action_information
   WHERE  action_information_category = p_category
     AND  action_information1 = p_component_name
     AND  action_context_id = p_context_id
     AND  source_id = p_source_id;
Line: 1731

    CURSOR c_delete_mode_entries(p_payroll_action_id        NUMBER)
    IS
       SELECT pai.source_id           source_id
             ,pai.assignment_id       assignment_id
             ,pai.action_context_id   last_action_context_id
         FROM pay_action_information pai
             ,pay_assignment_actions paa
        WHERE pai.action_information_category LIKE 'IN_24Q%_PERSON'
          AND fnd_number.canonical_to_number(pai.action_information1)
              BETWEEN p_start_person AND p_end_person
          AND pai.action_context_id = paa.assignment_action_id
          AND
           ( pai.action_information_category = 'IN_24Q_PERSON'
             OR
             (pai.action_information_category = 'IN_24QC_PERSON' AND pai.action_information10 IN ('A'))
            )
          AND(
                paa.payroll_action_id IN (
                                           SELECT org_information3
                                             FROM hr_organization_information
                                            WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                              AND organization_id  = g_gre_id
                                              AND org_information1 = g_year
                                              AND org_information2 = g_quarter
                                              AND org_information5 = 'A'
                                          )
             )
          AND pai.action_information3 = g_gre_id
          AND (
              pai.source_id  NOT IN
             (
             SELECT
                 paa.assignment_action_id run_asg_action_id
             FROM pay_assignment_actions paa
                 ,pay_payroll_actions ppa
                 ,per_assignments_f paf
             WHERE paf.assignment_id = paa.assignment_id
             AND paa.assignment_id = pai.assignment_id
             AND paa.tax_unit_id  = pai.action_information3
             AND paa.payroll_action_id = ppa.payroll_action_id
             AND ppa.action_type IN('R','Q','I','B')
             AND ppa.payroll_id    = paf.payroll_id
             AND ppa.action_status ='C'
             AND ppa.effective_date between g_start_date and g_end_date
             AND paa.source_action_id IS NULL
             AND (1 = DECODE(ppa.action_type,'I',1,0)
             OR EXISTS (SELECT ''
                        FROM pay_action_interlocks intk,
                             pay_assignment_actions paa1,
                             pay_payroll_actions ppa1
                        WHERE intk.locked_action_id = paa.assignment_Action_id
                        AND intk.locking_action_id =  paa1.assignment_action_id
                        AND paa1.payroll_action_id =ppa1.payroll_action_id
                        AND ppa1.action_type in('P','U')
                        AND ppa.action_type in('R','Q','B')
                        AND ppa1.action_status ='C'
                        AND ppa1.effective_date BETWEEN g_start_date and g_end_date
                        AND ROWNUM =1 ))
                      )
             )
         AND (
                pai.source_id NOT IN
                      (SELECT pai.source_id
                         FROM pay_action_information pai
                             ,pay_action_interlocks locks
                             ,pay_assignment_actions paa
                             ,pay_assignment_actions masters
                        WHERE pai.action_information_category = 'IN_24QC_PERSON'
                          AND pai.action_information10        IN  ('D')
                          AND locks.locking_action_id         = pai.action_context_id
                          AND locks.locked_action_id          = paa.assignment_action_id
                          AND paa.payroll_action_id           = p_payroll_action_id
                          AND paa.assignment_id               = pai.assignment_id
                          AND masters.assignment_id            = pai.assignment_id
                          AND masters.assignment_action_id     = pai.action_context_id
                          AND masters.payroll_action_id IN(
                                                           SELECT org_information3
                                                             FROM hr_organization_information
                                                            WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                              AND organization_id  = g_gre_id
                                                              AND org_information1 = g_year
                                                              AND org_information2 = g_quarter
                                                              AND org_information5 = 'A'
                                                              AND org_information6 = 'C'
                                                     )
                      )
           )
           ORDER BY 2 DESC;
Line: 1822

    SELECT a.source_id,a.assignment_id,a.last_action_context_id
     FROM
           (SELECT
              FND_NUMBER.CANONICAL_TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) source_id
              ,paf.assignment_id       assignment_id
              ,NULL    last_action_context_id
             FROM pay_assignment_actions paa
                 ,pay_payroll_actions ppa
                 ,per_assignments_f paf
             WHERE paf.person_id BETWEEN p_start_person AND p_end_person
             AND paf.assignment_id = paa.assignment_id
             AND paa.tax_unit_id  = g_gre_id
             AND paa.payroll_action_id = ppa.payroll_action_id
             AND ppa.action_type IN('R','Q','I','B')
             AND ppa.payroll_id    = paf.payroll_id
             AND ppa.action_status ='C'
             AND ppa.effective_date between g_start_date and g_end_date
             AND paa.source_action_id IS NULL
             AND (1 = DECODE(ppa.action_type,'I',1,0)
              OR EXISTS (SELECT ''
                        FROM pay_action_interlocks intk,
                             pay_assignment_actions paa1,
                             pay_payroll_actions ppa1
                        WHERE intk.locked_action_id = paa.assignment_Action_id
                        AND intk.locking_action_id =  paa1.assignment_action_id
                        AND paa1.payroll_action_id =ppa1.payroll_action_id
                        AND ppa1.action_type in('P','U')
                        AND ppa.action_type in('R','Q','B')
                        AND ppa1.action_status ='C'
                        AND ppa1.effective_date BETWEEN g_start_date and g_end_date
                        AND ROWNUM =1 ))
                       GROUP BY paf.assignment_id) a
           WHERE (
                  a.source_id NOT IN
                      (SELECT pai.source_id
                         FROM pay_action_information pai
                             ,pay_assignment_actions paa
                        WHERE pai.action_information_category = 'IN_24Q_PERSON'
                          AND pai.action_context_id   = paa.assignment_action_id
                          AND paa.payroll_action_id   = p_payroll_action_id
                      )
                )
           AND (
                 a.source_id NOT IN
                      (SELECT pai.source_id
                         FROM pay_action_information pai
                             ,pay_action_interlocks locks
                             ,pay_assignment_actions paa
                             ,pay_assignment_actions paa_24qc
                        WHERE pai.action_information_category = 'IN_24QC_PERSON'
                          AND locks.locking_action_id         = pai.action_context_id
                          AND locks.locked_action_id          = paa.assignment_action_id
                          AND paa.payroll_action_id           = p_payroll_action_id
                          AND paa.assignment_id               = pai.assignment_id
                          AND pai.action_information2         = g_year||g_quarter
                          AND paa_24qc.assignment_id          = pai.assignment_id
                          AND paa_24qc.assignment_action_id   = pai.action_context_id
                          AND paa_24qc.payroll_action_id IN(
                                                      SELECT org_information3
                                                        FROM hr_organization_information
                                                       WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                         AND organization_id  = g_gre_id
                                                         AND org_information1 = g_year
                                                         AND org_information2 = g_quarter
                                                         AND org_information5 = 'A'
                                                         AND org_information6 = 'C'
                                                     )
                      )
                )
           AND (
                 a.source_id NOT IN
                      (SELECT pai.source_id
                         FROM pay_action_information pai
                             ,pay_action_interlocks locks
                             ,pay_assignment_actions paa
                             ,pay_assignment_actions paa_24qc
                        WHERE pai.action_information_category = 'IN_24QC_PERSON'
                          AND locks.locking_action_id         = pai.action_context_id
                          AND locks.locked_action_id          = paa.assignment_action_id
                          AND paa.payroll_action_id           = p_payroll_action_id
                          AND paa.assignment_id               = pai.assignment_id
                          AND pai.action_information2         = g_year||g_quarter
                          AND paa_24qc.assignment_id          = pai.assignment_id
                          AND paa_24qc.assignment_action_id   = pai.action_context_id
                          AND paa_24qc.payroll_action_id IN(
                                                      SELECT org_information3
                                                        FROM hr_organization_information
                                                       WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                         AND organization_id  = g_gre_id
                                                         AND org_information1 = g_year
                                                         AND org_information2 = g_quarter
                                                         AND org_information5 = 'A'
                                                         AND org_information6 = 'C'
                                                     )
                      )
                )
           AND (
                 a.source_id NOT IN
                      (SELECT pai.source_id
                         FROM pay_action_information pai
                             ,pay_assignment_actions paa_24qc
                        WHERE pai.action_information_category = 'IN_24QC_PERSON'
                          AND pai.action_information3         = g_gre_id
                          AND pai.action_information2         = g_year||g_quarter
                          AND paa_24qc.assignment_id          = pai.assignment_id
                          AND paa_24qc.assignment_action_id   = pai.action_context_id
                          AND paa_24qc.payroll_action_id IN(
                                                      SELECT org_information3
                                                        FROM hr_organization_information
                                                       WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                         AND organization_id  = g_gre_id
                                                         AND org_information1 = g_year
                                                         AND org_information2 = g_quarter
                                                         AND org_information5 = 'A'
                                                         AND org_information6 = 'C'
                                                     )
                      )
                )
            ORDER BY 2 DESC;
Line: 1943

   CURSOR c_update_mode_entries(p_payroll_action_id        NUMBER
                                )
    IS
       SELECT DISTINCT
              pai.source_id     source_id
             ,pai.assignment_id assignment_id
             ,pai.action_context_id   last_action_context_id
             ,pep.effective_end_date
         FROM pay_action_information pai
             ,pay_assignment_actions  paa
             ,per_people_f            pep
             ,per_assignments_f       asg
        WHERE pai.action_information_category = 'IN_24Q_PERSON'
          AND fnd_number.canonical_to_number(pai.action_information1)
              BETWEEN p_start_person AND p_end_person
          AND pai.action_context_id = paa.assignment_action_id
          AND pai.action_information3 = g_gre_id
          AND paa.payroll_action_id = p_payroll_action_id
          AND asg.assignment_id     = pai.assignment_id
          AND asg.person_id         = pep.person_id
          AND asg.business_group_id = pep.business_group_id
          AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
          AND pai.source_id  IN
             (
             SELECT
                 paa.assignment_action_id run_asg_action_id
             FROM pay_assignment_actions paa
                 ,pay_payroll_actions ppa
                 ,per_assignments_f paf
             WHERE paf.person_id BETWEEN p_start_person AND p_end_person
             AND paf.assignment_id = paa.assignment_id
             AND paa.assignment_id = pai.assignment_id
             AND paa.tax_unit_id  = pai.action_information3
             AND paa.payroll_action_id = ppa.payroll_action_id
             AND ppa.action_type IN('R','Q','I','B')
             AND ppa.payroll_id    = paf.payroll_id
             AND ppa.action_status ='C'
             AND ppa.effective_date between g_start_date and g_end_date
             AND paa.source_action_id IS NULL
             AND (1 = DECODE(ppa.action_type,'I',1,0)
             OR EXISTS (SELECT ''
                        FROM pay_action_interlocks intk,
                             pay_assignment_actions paa1,
                             pay_payroll_actions ppa1
                        WHERE intk.locked_action_id = paa.assignment_Action_id
                        AND intk.locking_action_id =  paa1.assignment_action_id
                        AND paa1.payroll_action_id =ppa1.payroll_action_id
                        AND ppa1.action_type in('P','U')
                        AND ppa.action_type in('R','Q','B')
                        AND ppa1.action_status ='C'
                        AND ppa1.effective_date BETWEEN g_start_date and g_end_date
                        AND ROWNUM =1 ))
               )
           AND (
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Salary Under Section 17',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Salary Under Section 17','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Profit in lieu of Salary',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Profit in lieu of Salary','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Value of Perquisites',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Value of Perquisites','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Gross Salary less Allowances',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Gross Salary less Allowances','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Allowances Exempt',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Allowances Exempt','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Deductions under Sec 16',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Deductions under Sec 16','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Income Chargeable Under head Salaries',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Income Chargeable Under head Salaries','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Other Income',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Other Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Gross Total Income',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Gross Total Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Total Income',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Total Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Tax on Total Income',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Tax on Total Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Marginal Relief',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Marginal Relief','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Total Tax payable',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Total Tax payable','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Relief under Sec 89',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Relief under Sec 89','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Employment Tax',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Employment Tax','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Entertainment Allowance',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Entertainment Allowance','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Surcharge',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Surcharge','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Education Cess',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Education Cess','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 TDS',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 TDS','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Total Chapter VI A Deductions',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Total Chapter VI A Deductions','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_VIA','80CCE',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Deductions Sec 80CCE','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                  (pai.action_information6 <> hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title))
               OR -- Checking PAN reference Number
                   (NVL(pai.action_information5,'0') <> NVL(pep.per_information14,'0'))
               OR ( --Checking PAN Number
                     (
                       g_correction_mode IN ('C5','%')
                       AND (pai.action_information4 NOT IN('APPLIEDFOR','PANNOTAVBL'))
                       AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))NOT IN('APPLIEDFOR','PANNOTAVBL'))
                       AND (pai.action_information4 <> (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
                     )
                     OR
                     (
                       g_correction_mode NOT IN ('C5')
                       AND (pai.action_information4 <> (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
                       AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))IN('APPLIEDFOR','PANNOTAVBL'))
                     )
                 )
              )
          AND   assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
          AND   assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date
          ORDER BY 2,4 DESC;
Line: 2088

       SELECT pai_locking.action_context_id  last_action_context_id
         FROM pay_action_information pai_locking
             ,pay_action_interlocks locks
             ,pay_assignment_actions paa
        WHERE pai_locking.action_information_category = 'IN_24QC_PERSON'
          AND fnd_number.canonical_to_number(pai_locking.action_information1)
              BETWEEN p_start_person AND p_end_person
          AND pai_locking.action_information3  = g_gre_id
          AND pai_locking.action_context_id    = paa.assignment_action_id
          AND paa.payroll_action_id IN
                                    (
                                      SELECT org_information3
                                        FROM hr_organization_information
                                       WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                         AND organization_id  = g_gre_id
                                         AND org_information1 = g_year
                                         AND org_information2 = g_quarter
                                         AND org_information5 = 'A'
                                         AND org_information6 = 'C'
                                    )
          AND locks.locking_action_id          = pai_locking.action_context_id
          AND locks.locked_action_id           = p_24q_arc_action_context_id
          AND pai_locking.assignment_id        = p_assignment_id
          AND pai_locking.source_id            = p_source_id
        ORDER BY pai_locking.action_context_id DESC;
Line: 2118

       SELECT pai.action_context_id
         FROM pay_action_information pai
             ,per_people_f           pep
             ,per_assignments_f      asg
             ,pay_assignment_actions paa
        WHERE pai.action_information_category = 'IN_24QC_PERSON'
          AND fnd_number.canonical_to_number(pai.action_information1)
              BETWEEN p_start_person AND p_end_person
          AND paa.assignment_action_id = pai.action_context_id
          AND pai.source_id     = p_source_id
          AND paa.payroll_action_id IN
                           (
                             SELECT org_information3
                               FROM hr_organization_information
                              WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                AND organization_id  = g_gre_id
                                AND org_information1 = g_year
                                AND org_information2 = g_quarter
                                AND org_information5 = 'A'
                                AND org_information6 = 'C'
                          )
          AND pai.action_information3 = g_gre_id
          AND pai.action_context_id = p_assignment_action_id
          AND asg.assignment_id     = pai.assignment_id
          AND asg.person_id         = pep.person_id
          AND asg.business_group_id = pep.business_group_id
          AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
          AND pai.source_id  IN
             (
             SELECT
                 paa.assignment_action_id run_asg_action_id
             FROM pay_assignment_actions paa
                 ,pay_payroll_actions ppa
                 ,per_assignments_f paf
             WHERE paf.person_id BETWEEN p_start_person AND p_end_person
             AND paf.assignment_id = paa.assignment_id
             AND paa.assignment_id = pai.assignment_id
             AND paa.tax_unit_id  = pai.action_information3
             AND paa.payroll_action_id = ppa.payroll_action_id
             AND ppa.action_type IN('R','Q','I','B')
             AND ppa.payroll_id    = paf.payroll_id
             AND ppa.action_status ='C'
             AND ppa.effective_date between g_start_date and g_end_date
             AND paa.source_action_id IS NULL
             AND (1 = DECODE(ppa.action_type,'I',1,0)
                  OR EXISTS (SELECT ''
                             FROM pay_action_interlocks intk,
                                  pay_assignment_actions paa1,
                                  pay_payroll_actions ppa1
                             WHERE intk.locked_action_id = paa.assignment_Action_id
                             AND intk.locking_action_id =  paa1.assignment_action_id
                             AND paa1.payroll_action_id =ppa1.payroll_action_id
                             AND ppa1.action_type in('P','U')
                             AND ppa.action_type in('R','Q','B')
                             AND ppa1.action_status ='C'
                             AND ppa1.effective_date BETWEEN g_start_date and g_end_date
                             AND ROWNUM =1 ))
                )
          AND (
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Salary Under Section 17',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Salary Under Section 17','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Profit in lieu of Salary',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Profit in lieu of Salary','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Value of Perquisites',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Value of Perquisites','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Gross Salary less Allowances',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Gross Salary less Allowances','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Allowances Exempt',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Allowances Exempt','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Deductions under Sec 16',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Deductions under Sec 16','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Income Chargeable Under head Salaries',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Income Chargeable Under head Salaries','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Other Income',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Other Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Gross Total Income',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Gross Total Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Total Income',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Total Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Tax on Total Income',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Tax on Total Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Marginal Relief',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Marginal Relief','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Total Tax payable',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Total Tax payable','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Relief under Sec 89',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Relief under Sec 89','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Employment Tax',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Employment Tax','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Entertainment Allowance',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Entertainment Allowance','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Surcharge',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Surcharge','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Education Cess',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Education Cess','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 TDS',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 TDS','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Total Chapter VI A Deductions',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Total Chapter VI A Deductions','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_VIA','80CCE',pai.action_context_id,pai.source_id,1)))
                   <> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Deductions Sec 80CCE','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               OR
                  (pai.action_information6 <> hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title))
               OR
                   (NVL(pai.action_information5,'0') <> NVL(pep.per_information14,'0'))
               OR(
                     (
                       g_correction_mode IN ('C5','%')
                       AND (pai.action_information4 NOT IN('APPLIEDFOR','PANNOTAVBL'))
                       AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))NOT IN('APPLIEDFOR','PANNOTAVBL'))
                       AND (pai.action_information4 <> (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
                     )
                     OR
                     (
                       g_correction_mode NOT IN ('C5')
                       AND (pai.action_information4 <> (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
                       AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))IN('APPLIEDFOR','PANNOTAVBL'))
                     )
                 )
              )
          AND  assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
          AND  assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date
          AND NOT EXISTS
          (
       SELECT 1
         FROM pay_action_information pai
             ,per_people_f           pep
             ,per_assignments_f      asg
        WHERE pai.action_information_category = 'IN_24QC_PERSON'
          AND fnd_number.canonical_to_number(pai.action_information1)
              BETWEEN p_start_person AND p_end_person
          AND pai.source_id            = p_source_id
          AND pai.action_information3  = g_gre_id
          AND pai.action_context_id    = p_assignment_action_id
          AND asg.assignment_id        = pai.assignment_id
          AND asg.person_id            = pep.person_id
          AND asg.business_group_id    = pep.business_group_id
          AND asg.business_group_id    = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
          AND (
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Salary Under Section 17',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Salary Under Section 17','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Profit in lieu of Salary',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Profit in lieu of Salary','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Value of Perquisites',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Value of Perquisites','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Gross Salary less Allowances',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Gross Salary less Allowances','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Allowances Exempt',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Allowances Exempt','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Deductions under Sec 16',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Deductions under Sec 16','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Income Chargeable Under head Salaries',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Income Chargeable Under head Salaries','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Other Income',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Other Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Gross Total Income',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Gross Total Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Total Income',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Total Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Tax on Total Income',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Tax on Total Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Marginal Relief',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Marginal Relief','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Total Tax payable',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Total Tax payable','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Relief under Sec 89',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Relief under Sec 89','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Employment Tax',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Employment Tax','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Entertainment Allowance',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Entertainment Allowance','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Surcharge',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Surcharge','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Education Cess',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Education Cess','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 TDS',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 TDS','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Total Chapter VI A Deductions',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Total Chapter VI A Deductions','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                 (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_VIA','80CCE',pai.action_context_id,pai.source_id,1)))
                   = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Deductions Sec 80CCE','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
               AND
                  (pai.action_information6 = hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title))
               AND(--Checking PAN Number
                     (
                       g_correction_mode IN ('C5','%')
                       AND (pai.action_information4 NOT IN('APPLIEDFOR','PANNOTAVBL'))
                       AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))NOT IN('APPLIEDFOR','PANNOTAVBL'))
                       AND (pai.action_information4 = (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
                     )
                     OR
                     (
                       g_correction_mode NOT IN ('C5')
                       AND (pai.action_information4 = (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
                       AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))IN('APPLIEDFOR','PANNOTAVBL'))
                     )
                )
              AND -- Checking PAN reference Number
                  (NVL(pai.action_information5,'0') = NVL(pep.per_information14,'0'))
              )
          AND  assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
          AND  assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date
         );
Line: 2366

      SELECT MAX(pai.action_context_id) last_action_context_id
            ,pai.source_id              source_id
            ,pai.assignment_id          assignment_id
        FROM pay_action_information pai
            ,pay_assignment_actions paa
       WHERE pai.action_information_category = 'IN_24QC_PERSON'
         AND fnd_number.canonical_to_number(pai.action_information1)
              BETWEEN p_start_person AND p_end_person
         AND pai.action_information3         = g_gre_id
         AND pai.action_information2         = g_year||g_quarter
         AND
          (    ((p_flag = 1) AND (pai.action_information10 IN ('A','NA')))
            OR ((p_flag = 2) AND (pai.action_information10 = p_mode))
          )
         AND pai.action_context_id           = paa.assignment_action_id
         AND paa.payroll_action_id IN
                              (
                                SELECT org_information3
                                  FROM hr_organization_information
                                 WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                   AND organization_id  = g_gre_id
                                   AND org_information1 = g_year
                                   AND org_information2 = g_quarter
                                   AND org_information5 = 'A'
                                   AND org_information6 = 'C'
                              )
        AND (
                p_flag = 1
                OR
                (
                 pai.source_id NOT IN(
                                SELECT pai.source_id
                                  FROM pay_action_information pai
                                      ,pay_assignment_actions paa
                                 WHERE pai.action_information_category = 'IN_24QC_PERSON'
                                   AND fnd_number.canonical_to_number(pai.action_information1)
                                       BETWEEN p_start_person AND p_end_person
                                   AND pai.action_information3         = g_gre_id
                                   AND pai.action_information2         = g_year||g_quarter
                                   AND pai.action_information10        = 'D'
                                   AND pai.action_context_id           = paa.assignment_action_id
                                   AND paa.payroll_action_id IN
                                                        (
                                                              SELECT org_information3
                                                                FROM hr_organization_information
                                                               WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                                 AND organization_id  = g_gre_id
                                                                 AND org_information1 = g_year
                                                                 AND org_information2 = g_quarter
                                                                 AND org_information5 = 'A'
                                                                 AND org_information6 = 'C'
                                                        )
                                   )
                   AND pai.source_id IN
                            (SELECT a.source_id
                             FROM
                                 (SELECT
                                    FND_NUMBER.CANONICAL_TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) source_id
                                    ,paf.assignment_id       assignment_id
                                    ,NULL    last_action_context_id
                                   FROM pay_assignment_actions paa
                                       ,pay_payroll_actions ppa
                                       ,per_assignments_f paf
                                   WHERE paf.person_id BETWEEN p_start_person AND p_end_person
                                   AND paf.assignment_id = paa.assignment_id
                                   AND paa.tax_unit_id  = g_gre_id
                                   AND paa.payroll_action_id = ppa.payroll_action_id
                                   AND ppa.action_type IN('R','Q','I','B')
                                   AND ppa.payroll_id    = paf.payroll_id
                                   AND ppa.action_status ='C'
                                   AND ppa.effective_date between g_start_date and g_end_date
                                   AND paa.source_action_id IS NULL
                                   AND (1 = DECODE(ppa.action_type,'I',1,0)
                                    OR EXISTS (SELECT ''
                                              FROM pay_action_interlocks intk,
                                                   pay_assignment_actions paa1,
                                                   pay_payroll_actions ppa1
                                              WHERE intk.locked_action_id = paa.assignment_Action_id
                                              AND intk.locking_action_id =  paa1.assignment_action_id
                                              AND paa1.payroll_action_id =ppa1.payroll_action_id
                                              AND ppa1.action_type in('P','U')
                                              AND ppa.action_type in('R','Q','B')
                                              AND ppa1.action_status ='C'
                                              AND ppa1.effective_date BETWEEN g_start_date and g_end_date
                                              AND ROWNUM =1 ))
                                             GROUP BY paf.assignment_id) a
                                                 WHERE
                                       a.assignment_id  = pai.assignment_id)
                 )
            )
        GROUP BY pai.assignment_id,pai.source_id;
Line: 2460

      SELECT 1
        FROM per_assignments_f
       WHERE business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
         AND assignment_id     = p_assignment_id;
Line: 2487

    pay_in_utils.set_location(g_debug,'Finding Assignment Actions deleted since last statement ',2);
Line: 2488

   FOR c_rec IN c_delete_mode_entries(p_payroll_action_id)
   LOOP
          l_bg_check := 0;
Line: 2497

               g_sal_data_rec_del(g_count_sal_delete).source_id              := c_rec.source_id;
Line: 2498

               g_sal_data_rec_del(g_count_sal_delete).last_action_context_id := c_rec.last_action_context_id;
Line: 2499

               g_sal_data_rec_del(g_count_sal_delete).assignment_id          := c_rec.assignment_id;
Line: 2500

               g_sal_data_rec_del(g_count_sal_delete).salary_mode            := 'D';
Line: 2501

               g_count_sal_delete := g_count_sal_delete + 1;
Line: 2528

    pay_in_utils.set_location(g_debug,'Finding Assignment Actions updated since last statement ',4);
Line: 2532

    FOR csr_rec IN c_update_mode_entries(p_payroll_action_id)
    LOOP
        pay_in_utils.set_location(g_debug,'Diff between live data and 24Q Archival found', 1);
Line: 2559

        pay_in_utils.set_location(g_debug,'Mid way in finding updated assignment actions', 3);
Line: 2566

           g_sal_data_rec_upd(g_count_sal_update).last_action_context_id := csr_rec.last_action_context_id;
Line: 2567

           g_sal_data_rec_upd(g_count_sal_update).assignment_id          := csr_rec.assignment_id;
Line: 2568

           g_sal_data_rec_upd(g_count_sal_update).source_id              := csr_rec.source_id;
Line: 2569

           g_sal_data_rec_upd(g_count_sal_update).salary_mode            := 'U';
Line: 2570

           g_count_sal_update := g_count_sal_update + 1;
Line: 2584

                     g_sal_data_rec_upd(g_count_sal_update).last_action_context_id := l_dummy;
Line: 2585

                     g_sal_data_rec_upd(g_count_sal_update).assignment_id          := csr_rec.assignment_id;
Line: 2586

                     g_sal_data_rec_upd(g_count_sal_update).source_id              := csr_rec.source_id;
Line: 2587

                     g_sal_data_rec_upd(g_count_sal_update).salary_mode            := 'U';
Line: 2588

                     g_count_sal_update := g_count_sal_update + 1;
Line: 2596

        pay_in_utils.set_location(g_debug,'Additional Checking on updated assignment actions ', 7);
Line: 2622

            FOR i IN 1.. g_count_sal_update - 1
            LOOP
               IF (
                  (g_sal_data_rec_upd(i).last_action_context_id = c_rec.last_action_context_id)
                  AND
                  (g_sal_data_rec_upd(i).assignment_id          = c_rec.assignment_id)
                  AND
                  (g_sal_data_rec_upd(i).source_id       = c_rec.source_id)
                  AND
                  (g_sal_data_rec_upd(i).salary_mode          = 'U')
                  )
               THEN
                    l_flag := FALSE;
Line: 2644

               g_sal_data_rec_upd(g_count_sal_update).last_action_context_id := c_rec.last_action_context_id;
Line: 2645

               g_sal_data_rec_upd(g_count_sal_update).assignment_id          := c_rec.assignment_id;
Line: 2646

               g_sal_data_rec_upd(g_count_sal_update).source_id              := c_rec.source_id;
Line: 2647

               g_sal_data_rec_upd(g_count_sal_update).salary_mode            := 'U';
Line: 2648

               g_count_sal_update := g_count_sal_update + 1;
Line: 2655

    pay_in_utils.set_location(g_debug,'Now Searching for those updated assignment actions that were 24QC+',8);
Line: 2669

               g_sal_data_rec_upd(g_count_sal_update).last_action_context_id := c_rec.last_action_context_id;
Line: 2670

               g_sal_data_rec_upd(g_count_sal_update).assignment_id          := c_rec.assignment_id;
Line: 2671

               g_sal_data_rec_upd(g_count_sal_update).source_id              := c_rec.source_id;
Line: 2672

               g_sal_data_rec_upd(g_count_sal_update).salary_mode            := 'U';
Line: 2673

               g_count_sal_update := g_count_sal_update + 1;
Line: 2722

    SELECT hoi.org_information1        tan
          ,hoi.org_information3 deductor_type
          ,hoi.org_information4        reg_org_id
          ,hoi.org_information7        division
          ,hou.location_id             location_id
      FROM hr_organization_information hoi
          ,hr_organization_units       hou
     WHERE hoi.organization_id         = p_gre_id
       AND hoi.org_information_context = 'PER_IN_INCOME_TAX_DF'
       AND hou.organization_id         = hoi.organization_id
       AND hou.business_group_id       = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
       AND g_qr_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
Line: 2737

    SELECT hoi.org_information3        pan
          ,hoi.org_information4        legal_name
      FROM hr_organization_information  hoi
          ,hr_organization_units        hou
     WHERE hoi.organization_id = p_reg_org_id
       AND hoi.org_information_context = 'PER_IN_COMPANY_DF'
       AND hou.organization_id = hoi.organization_id
       AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
       AND g_qr_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
Line: 2749

   SELECT nvl(pos.name,job.name) name
   FROM   per_positions     pos
         ,per_assignments_f asg
         ,per_jobs          job
   WHERE  asg.position_id=pos.position_id(+)
   AND    asg.job_id=job.job_id(+)
   AND    asg.person_id = p_person_id
   AND    asg.primary_flag = 'Y'
   AND    asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
   AND    g_qr_end_date BETWEEN pos.date_effective(+) AND NVL(pos.date_end(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
   AND    g_qr_end_date BETWEEN job.date_from(+) AND NVL(job.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
   AND    g_qr_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
Line: 2765

   SELECT hou.location_id rep_location
     FROM per_assignments_f   asg
         ,hr_organization_units hou
    WHERE asg.person_id = p_person_id
      AND asg.primary_flag = 'Y'
      AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
      AND hou.organization_id = asg.organization_id
      AND hou.business_group_id = asg.business_group_id
      AND g_qr_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
      AND g_qr_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
Line: 2778

   SELECT pep.person_id
         ,hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title) rep_name
         ,pep.email_address        email_id
     FROM hr_organization_information   hoi
         ,hr_organization_units         hou
         ,per_people_f              pep
    WHERE hoi.org_information_context = 'PER_IN_INCOME_TAX_REP_DF'
      AND hoi.organization_id = p_gre_id
      AND hou.organization_id = hoi.organization_id
      AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
      AND pep.person_id = hoi.org_information1
      AND pep.business_group_id = hou.business_group_id
      AND g_qr_end_date BETWEEN pep.effective_start_date AND pep.effective_end_date
      AND g_qr_end_date BETWEEN fnd_date.canonical_to_date(hoi.org_information2)
      AND NVL(fnd_date.canonical_to_date(hoi.org_information3),TO_DATE('31-12-4712','DD-MM-YYYY'))
      AND g_qr_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
Line: 2797

   SELECT phone_number rep_phone_no
         ,SUBSTR(phone_number
         ,INSTR(phone_number,'-',1,1) + 1
         ,INSTR(phone_number,'-',1,2) - INSTR(phone_number,'-',1,1) -1
         )STD_CODE
     FROM per_phones
    WHERE parent_id = p_person_id
      AND phone_type =  'W1'
      AND g_qr_end_date BETWEEN date_from AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
Line: 2893

       SELECT DISTINCT pai.action_context_id locking_id
             ,pai.action_information1        gre_id
         FROM pay_action_information pai
        WHERE pai.action_context_type = 'PA'
          AND pai.action_information_category = 'IN_24QC_ORG'
          AND pai.action_information1  = p_gre_id
          AND pai.action_information3 = g_year||g_quarter
          AND pai.source_id = p_payroll_action_id
          AND pai.action_context_id IN
                           (
                             SELECT org_information3
                               FROM hr_organization_information
                              WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                AND organization_id  = g_gre_id
                                AND org_information1 = g_year
                                AND org_information2 = g_quarter
                                AND org_information5 = 'A'
                                AND org_information6 = 'C'
                          )
        ORDER BY pai.action_context_id DESC;
Line: 2917

       SELECT action_information1 gre_id
         FROM pay_action_information
        WHERE action_information_category = 'IN_24Q_ORG'
          AND action_context_type = 'PA'
          AND action_information1 = p_gre_id
          AND action_information3 = g_year||g_quarter
          AND action_context_id   = p_payroll_action_id;
Line: 2947

       SELECT pai.action_context_id     last_action_context_id
         FROM pay_action_information pai
        WHERE pai.action_information_category = p_act_inf_category
          AND pai.action_context_type   = 'PA'
          AND pai.action_context_id     = p_action_context_id
          AND pai.action_information1   = p_gre_id
          AND pai.action_information2   = p_tan_number
          AND pai.action_information3   = g_year||g_quarter
          AND(
               (pai.action_information4  <> p_pan_number)
              OR
               (pai.action_information5  <> p_legal_name)
              OR
               (pai.action_information6  <> p_org_location)
              OR
               (pai.action_information7  <> p_deductor_type)
              OR
               (pai.action_information8  <> p_branch_or_division)
              OR
               (pai.action_information9  <> p_rep_name)
              OR
               (pai.action_information10 <> p_rep_email_id)
              OR
               (pai.action_information11 <> p_rep_position)
              OR
               (pai.action_information12 <> p_rep_location)
              OR
               (pai.action_information13 <> p_rep_work_phone)
              OR
               (SUBSTR(pai.action_information13
                      ,INSTR(pai.action_information13,'-',1,1) + 1
                      ,INSTR(pai.action_information13,'-',1,2) - INSTR(pai.action_information13,'-',1,1) -1
                      )                  <> p_rep_std_code
               )
              OR
               (DECODE(p_act_inf_category,'IN_24Q_ORG',p_rep_std_code,pai.action_information14) <> p_rep_std_code)
              );
Line: 3195

      SELECT hoi.org_information3           challan_number,
             hoi.org_information2           transfer_voucher_date,
             hoi.org_information4           amount,
             hoi.org_information7           surcharge,
             hoi.org_information8           education_cess,
             hoi.org_information10          other,
             hoi.org_information9           interest,
             (SELECT hoi_bank.org_information4
               FROM hr_organization_information hoi_bank
              WHERE hoi_bank.organization_id = p_gre_id
                AND hoi_bank.org_information_context = 'PER_IN_CHALLAN_BANK'
                AND hoi_bank.org_information_id = hoi.org_information5
             )                              bank_branch_code,
             hoi.org_information11          cheque_dd_num,
             hoi.org_information_id         org_information_id
        FROM hr_organization_information hoi
       WHERE hoi.org_information_id IN
       (
            SELECT hoi.org_information_id
              FROM hr_organization_information hoi
                  ,hr_organization_units hou
             WHERE hoi.organization_id     = p_gre_id
               AND  hoi.organization_id    = hou.organization_id
               AND  hou.business_group_id  = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
               AND org_information_context = 'PER_IN_IT_CHALLAN_INFO'
       )
         AND hoi.org_information13 = g_quarter
         AND hoi.org_information1  = p_tax_year
         AND(
             hoi.org_information3 LIKE p_challan_number
             OR
             hoi.org_information_id  = p_org_info_id
            );
Line: 3237

      SELECT hoi.org_information3           challan_number,
             hoi.org_information2           transfer_voucher_date,
             hoi.org_information4           amount,
             hoi.org_information7           surcharge,
             hoi.org_information8           education_cess,
             hoi.org_information10          other,
             hoi.org_information9           interest,
             (SELECT hoi_bank.org_information4
               FROM hr_organization_information hoi_bank
              WHERE hoi_bank.organization_id = p_gre_id
                AND hoi_bank.org_information_context = 'PER_IN_CHALLAN_BANK'
                AND hoi_bank.org_information_id = hoi.org_information5
             )                              bank_branch_code,
             hoi.org_information11          cheque_dd_num,
             hoi.org_information_id         org_information_id
        FROM hr_organization_information hoi
       WHERE hoi.org_information_id IN
       (
            SELECT hoi.org_information_id
              FROM hr_organization_information hoi
                  ,hr_organization_units hou
             WHERE hoi.organization_id     = p_gre_id
               AND  hoi.organization_id    = hou.organization_id
               AND  hou.business_group_id  = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
               AND org_information_context = 'PER_IN_IT_CHALLAN_INFO'
       )
         AND hoi.org_information13 = g_quarter
         AND hoi.org_information1  = p_tax_year
         AND(
             hoi.org_information3 LIKE p_challan_number
             OR
             hoi.org_information_id  = p_org_info_id
            );
Line: 3276

      SELECT action_information_id,
             action_information1     transfer_voucher_number,
             action_information4     bank_branch_code,
             action_information5     transfer_voucher_date,
             action_information6     amount,
             action_information7     surcharge,
             action_information8     cess,
             action_information9     interest,
             action_information10    other,
             action_information11    cheque_dd_num,
             source_id               org_information_id
        FROM pay_action_information
       WHERE action_information2         = g_year||g_quarter
         AND action_information3         = p_gre_id
         AND action_information_category = p_act_info_category
         AND action_context_id IN
                               (
                                 SELECT org_information3
                                   FROM hr_organization_information
                                  WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                    AND organization_id  = g_gre_id
                                    AND org_information1 = g_year
                                    AND org_information2 = g_quarter
                                    AND org_information5 = 'A'
                              )
         AND DECODE(p_act_info_category
                   ,'IN_24Q_CHALLAN'
                   ,action_context_id
                   ,p_payroll_action_id
                   )                    = p_payroll_action_id
         AND (
              action_information1 LIKE p_voucher_number
             OR
              source_id         =  p_org_information_id
             )
         AND DECODE(p_act_info_category
                   ,'IN_24QC_CHALLAN'
                   ,action_information15
                   ,p_payroll_action_id
                   ) = p_payroll_action_id
      ORDER BY action_information_id DESC;
Line: 3326

      SELECT action_information_id,
             action_information1     transfer_voucher_number,
             action_information4     bank_branch_code,
             action_information5     transfer_voucher_date,
             action_information6     amount,
             action_information7     surcharge,
             action_information8     cess,
             action_information9     interest,
             action_information10    other,
             action_information11    cheque_dd_num,
             source_id               org_information_id
        FROM pay_action_information
       WHERE action_information2         = g_year||g_quarter
         AND action_information3         = p_gre_id
         AND action_information_category = p_act_info_category
         AND action_context_id IN
                               (
                                 SELECT org_information3
                                   FROM hr_organization_information
                                  WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                    AND organization_id  = g_gre_id
                                    AND org_information1 = g_year
                                    AND org_information2 = g_quarter
                                    AND org_information5 = 'A'
                              )
         AND DECODE(p_act_info_category
                   ,'IN_24Q_CHALLAN'
                   ,action_context_id
                   ,p_payroll_action_id
                   )                    = p_payroll_action_id
         AND (
              action_information1 LIKE p_voucher_number
             OR
              source_id         =  p_org_information_id
             )
         AND DECODE(p_act_info_category
                   ,'IN_24QC_CHALLAN'
                   ,action_information15
                   ,p_payroll_action_id
                   ) = p_payroll_action_id
      ORDER BY action_information_id DESC;
Line: 3501

       pay_in_utils.set_location(g_debug,'Starting Search for Updated Challans',1);
Line: 3806

       SELECT pay_in_utils.get_ee_value(p_element_entry_id,'Challan or Voucher Number')  Challan_or_Voucher_Number,
              pay_in_utils.get_ee_value(p_element_entry_id,'Payment Date')               Payment_Date,
              pay_in_utils.get_ee_value(p_element_entry_id,'Taxable Income')             Taxable_Income,
              pay_in_utils.get_ee_value(p_element_entry_id,'Income Tax Deducted')        Income_Tax_Deducted,
              pay_in_utils.get_ee_value(p_element_entry_id,'Surcharge Deducted')         Surcharge_Deducted,
              pay_in_utils.get_ee_value(p_element_entry_id,'Education Cess Deducted')    Education_Cess_Deducted,
              pay_in_utils.get_ee_value(p_element_entry_id,'Amount Deposited')           Amount_Deposited
         FROM dual;
Line: 3817

       SELECT action_information1        Challan_or_Voucher_Number,
              action_information4        Payment_Date,
              action_information5        Taxable_Income,
              action_information6        Income_Tax_Deducted,
              action_information7        Surcharge_Deducted,
              action_information8        Education_Cess_Deducted,
              DECODE(action_information_category,'IN_24Q_DEDUCTEE'
                    ,action_information9,'IN_24QC_DEDUCTEE'
                    ,action_information16) Amount_Deposited
             ,action_information_id
         FROM pay_action_information pai
        WHERE pai.action_context_id           = p_action_context_id
          AND pai.action_information_category IN ('IN_24Q_DEDUCTEE','IN_24QC_DEDUCTEE')
          AND pai.assignment_id               = p_assignment_id
          AND pai.source_id                   = p_element_entry_id
          AND pai.action_information3         = g_gre_id
        ORDER BY action_information_id DESC;
Line: 3837

       SELECT action_information1   person_id
             ,action_information4   pan_number
             ,action_information5   pan_ref_number
             ,action_information6   full_name
             ,action_information_id
         FROM pay_action_information
        WHERE action_context_id           =     p_action_context_id
          AND action_context_type         =     'AAP'
          AND action_information_category =     'IN_24Q_PERSON'
          AND assignment_id               =     p_assignment_id
          AND action_information2         =     g_year||g_quarter
          AND action_information3         =     g_gre_id
         ORDER BY action_information_id DESC;
Line: 3853

       SELECT action_information13  tax_rate
         FROM pay_action_information
        WHERE action_context_id           =     p_action_context_id
          AND action_context_type         =     'AAP'
          AND action_information_category =     'IN_24Q_DEDUCTEE'
          AND assignment_id               =     p_assignment_id
          AND source_id                   =     p_element_entry_id
          AND action_information3         =     g_gre_id;
Line: 3864

       SELECT action_information12  person_id
             ,action_information9   pan_number
             ,action_information11  pan_ref_number
             ,action_information18  tax_rate
             ,action_information10  full_name
             ,action_information_id
         FROM pay_action_information
        WHERE action_context_id           =     p_action_context_id
          AND action_context_type         =     'AAP'
          AND action_information_category =     'IN_24QC_DEDUCTEE'
          AND assignment_id               =     p_assignment_id
          AND action_information2         =     g_year||g_quarter
          AND action_information3         =     g_gre_id
        ORDER BY action_information_id DESC;
Line: 3881

       SELECT asg.person_id         person_id
             ,DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4) pan
             ,pep.per_information14 pan_ref_number
             ,hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title) full_name
             ,pep.effective_end_date
       FROM   per_assignments_f  asg
             ,per_people_f       pep
       WHERE  asg.assignment_id     = p_assignment_id
         AND  pep.person_id         = asg.person_id
         AND  pep.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
         AND  asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
         AND  assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
         AND  assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date
        ORDER BY 5 DESC;
Line: 3898

       SELECT  paei.aei_information2
         FROM  per_assignment_extra_info paei
              ,per_assignments_f paa
        WHERE  paei.information_type = 'PER_IN_TAX_EXEMPTION_DF'
          AND  paei.aei_information_category = 'PER_IN_TAX_EXEMPTION_DF'
          AND  paei.assignment_id = paa.assignment_id
          AND  paa.person_id = p_person_id
          AND  paei.aei_information1 = g_tax_year
          AND  assignment_end_date(paa.assignment_id) BETWEEN paa.effective_start_date AND paa.effective_end_date
          AND  ROWNUM = 1;
Line: 4036

    SELECT pdb.defined_balance_id balance_id
          ,pbt.balance_name       balance_name
      FROM pay_balance_types pbt
          ,pay_balance_dimensions pbd
          ,pay_defined_balances pdb
     WHERE pbt.balance_name IN('F16 Salary Under Section 17'
                              ,'F16 Profit in lieu of Salary'
                              ,'F16 Value of Perquisites'
                              ,'F16 Gross Salary less Allowances'
                              ,'F16 Allowances Exempt'
                              ,'F16 Deductions under Sec 16'
                              ,'F16 Income Chargeable Under head Salaries'
                              ,'F16 Other Income'
                              ,'F16 Gross Total Income'
                              ,'F16 Total Income'
                              ,'F16 Tax on Total Income'
                              ,'F16 Marginal Relief'
                              ,'F16 Total Tax payable'
                              ,'F16 Relief under Sec 89'
                              ,'F16 Employment Tax'
                              ,'F16 Entertainment Allowance'
                              ,'F16 Surcharge'
                              ,'F16 Education Cess'
                              ,'F16 Sec and HE Cess'
                              ,'F16 TDS'
                             --Chapter VIA Balances
                              ,'F16 Total Chapter VI A Deductions'
                              ,'F16 Deductions Sec 80CCE'
                              )
       AND pbd.dimension_name   ='_ASG_LE_PTD'
       AND pbt.legislation_code = 'IN'
       AND pbd.legislation_code = 'IN'
       AND pdb.legislation_code = 'IN'
       AND pbt.balance_type_id = pdb.balance_type_id
       AND pbd.balance_dimension_id  = pdb.balance_dimension_id;
Line: 4103

    g_bal_name_tab.DELETE;
Line: 4104

    g_balance_value_tab.DELETE;
Line: 4105

    g_result_table.DELETE;
Line: 4106

    g_context_table.DELETE;
Line: 4176

    g_bal_name_tab.DELETE;
Line: 4177

    g_context_table.DELETE;
Line: 4178

    g_balance_value_tab.DELETE;
Line: 4179

    g_result_table.DELETE;
Line: 4228

       SELECT DISTINCT
       FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values(p_category,p_balance_name,pai.action_context_id,pai.source_id,1)))
         FROM pay_action_information pai
        WHERE pai.action_context_id           = p_action_context_id
          AND pai.action_information_category = p_category
          AND pai.source_id                   = p_24q_source_id;
Line: 4237

       SELECT DISTINCT
              pai.action_information1   person_id
             ,pai.action_information4   pan_number
             ,pai.action_information5   pan_ref_number
             ,pai.action_information6   full_name
             ,fnd_date.canonical_to_date(pai.action_information9)   start_date
             ,fnd_date.canonical_to_date(pai.action_information10)  end_date
             ,pai.source_id
          FROM pay_action_information pai
              ,pay_assignment_actions paa
         WHERE pai.action_information_category IN('IN_24Q_PERSON')
           AND pai.action_information3         = g_gre_id
           AND pai.action_information2         = g_year||g_quarter
           AND pai.assignment_id               = p_assignment_id
           AND (
                  (pai.source_id  = p_source_id AND p_mode NOT IN ('A'))
               OR (p_mode IN ('A'))
               )
           AND pai.action_context_id           = paa.assignment_action_id
           AND paa.payroll_action_id           = p_24qa_pay_act_id
         ORDER BY pai.source_id DESC;
Line: 4261

       SELECT DISTINCT
              pai.action_information1   person_id
             ,pai.action_information4   pan_number
             ,pai.action_information5   pan_ref_number
             ,pai.action_information6   full_name
             ,fnd_date.canonical_to_date(pai.action_information8)   start_date
             ,fnd_date.canonical_to_date(pai.action_information9)  end_date
             ,pai.source_id
         FROM pay_action_information pai
              ,pay_assignment_actions paa
              ,pay_action_interlocks  locks
              ,pay_assignment_actions paa_master
         WHERE pai.action_information_category = 'IN_24QC_PERSON'
           AND pai.assignment_id               = p_assignment_id
           AND pai.action_information3         = g_gre_id
           AND pai.action_information2         = g_year||g_quarter
           AND pai.action_information10        = 'NA'
           AND (
                  (pai.source_id  = p_source_id AND p_mode NOT IN ('A'))
               OR (p_mode IN ('A'))
               )
           AND locks.locking_action_id         = pai.action_context_id
           AND locks.locked_action_id          = paa.assignment_action_id
           AND paa.payroll_action_id           = p_24qa_pay_act_id
           AND paa_master.assignment_action_id = pai.action_context_id
           AND paa_master.payroll_action_id IN(
                                                  SELECT org_information3
                                                    FROM hr_organization_information
                                                   WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                     AND organization_id         = g_gre_id
                                                     AND org_information1        = g_year
                                                     AND org_information2        = g_quarter
                                                     AND org_information5        = 'A'
                                                     AND org_information6        = 'C'
                                                 )
                                     ORDER BY pai.source_id DESC;
Line: 4300

       SELECT asg.person_id         person_id
             ,DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4) pan
             ,pep.per_information14 pan_ref_number
             ,hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title) full_name
       FROM   per_assignments_f  asg
             ,per_people_f       pep
       WHERE  asg.assignment_id     = p_assignment_id
         AND  pep.person_id         = asg.person_id
         AND  pep.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
         AND  asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
         AND  assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
         AND  assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date;
Line: 4317

        SELECT pai.action_context_id, pai.source_id
          FROM pay_action_information pai
              ,pay_assignment_actions paa
              ,pay_action_interlocks  locks
              ,pay_assignment_actions paa_master
         WHERE pai.action_information_category = 'IN_24QC_PERSON'
           AND pai.assignment_id               = p_assignment_id
           AND pai.action_information3         = g_gre_id
           AND pai.action_information2         = g_year||g_quarter
           AND pai.action_information10        = 'A'
           AND (
                  (pai.source_id  = p_source_id AND p_mode NOT IN ('A'))
               OR (p_mode IN ('A'))
               )
           AND locks.locking_action_id         = pai.action_context_id
           AND locks.locked_action_id          = paa.assignment_action_id
           AND paa.payroll_action_id           = p_24qa_pay_act_id
           AND paa_master.assignment_action_id = pai.action_context_id
           AND paa_master.payroll_action_id IN(
                                                  SELECT org_information3
                                                    FROM hr_organization_information
                                                   WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                     AND organization_id         = g_gre_id
                                                     AND org_information1        = g_year
                                                     AND org_information2        = g_quarter
                                                     AND org_information5        = 'A'
                                                     AND org_information6        = 'C'
                                                 )
                                                 ORDER BY pai.source_id DESC;
Line: 4352

       SELECT DISTINCT pai.action_context_id, pai.source_id
          FROM pay_action_information pai
              ,pay_assignment_actions paa
         WHERE pai.action_information_category IN('IN_24Q_PERSON')
           AND pai.action_information3         = g_gre_id
           AND pai.action_information2         = g_year||g_quarter
           AND pai.assignment_id               = p_assignment_id
           AND (
                  (pai.source_id  = p_source_id AND p_mode NOT IN ('A'))
               OR (p_mode IN ('A'))
               )
           AND pai.action_context_id           = paa.assignment_action_id
           AND paa.payroll_action_id           = p_24qa_pay_act_id
           ORDER BY pai.source_id DESC;
Line: 4369

    SELECT  GREATEST(asg.effective_start_date,g_fin_start_date) start_date
           ,LEAST(asg.effective_end_date,g_fin_end_date)        end_date
           ,scl.segment1
      FROM  per_assignments_f  asg
           ,hr_soft_coding_keyflex scl
           ,pay_assignment_actions paa
     WHERE  asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
       AND  paa.assignment_action_id = p_action_context_id
       AND  asg.assignment_id = paa.assignment_id
       AND  paa.assignment_id = p_assignment_id
       AND  scl.segment1 LIKE g_gre_id
       AND  ( asg.effective_start_date BETWEEN g_fin_start_date  AND g_end_date
          OR  g_fin_start_date BETWEEN asg.effective_start_date  AND asg.effective_end_date
              )
       AND  asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
    ORDER BY 1 ;
Line: 4392

    SELECT FND_NUMBER.CANONICAL_TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) run_asg_action_id
      FROM pay_assignment_actions paa
          ,pay_payroll_actions ppa
          ,per_assignments_f paf
     WHERE paf.assignment_id = paa.assignment_id
       AND paf.assignment_id = p_assignment_id
       AND paa.tax_unit_id  = p_tax_unit_id
       AND paa.payroll_action_id = ppa.payroll_action_id
       AND ppa.action_type IN('R','Q','I','B')
       AND ppa.payroll_id    = paf.payroll_id
       AND ppa.action_status ='C'
       AND ppa.effective_date between p_start_date and p_end_date
       AND paa.source_action_id IS NULL
       AND (1 = DECODE(ppa.action_type,'I',1,0)
            OR EXISTS (SELECT ''
                     FROM pay_action_interlocks intk,
                          pay_assignment_actions paa1,
                          pay_payroll_actions ppa1
                    WHERE intk.locked_action_id = paa.assignment_Action_id
                      AND intk.locking_action_id =  paa1.assignment_action_id
                      AND paa1.payroll_action_id =ppa1.payroll_action_id
                      AND paa1.assignment_id = p_assignment_id
                      AND ppa1.action_type in('P','U')
                      AND ppa.action_type in('R','Q','B')
                      AND ppa1.action_status ='C'
                      AND ppa1.effective_date BETWEEN p_start_date and p_end_date
                      AND ROWNUM =1 ));
Line: 4423

    SELECT paa.assignment_id
          ,paa.payroll_action_id
          ,paf.person_id
      FROM pay_assignment_actions  paa
          ,per_all_assignments_f paf
     WHERE paa.assignment_action_id = p_action_context_id
       AND paa.assignment_id = paf.assignment_id
       AND ROWNUM =1;
Line: 4434

    SELECT NVL(pos.actual_termination_date,(fnd_date.string_to_date('31-12-4712','DD-MM-YYYY')))
      FROM   per_all_assignments_f  asg
            ,per_periods_of_service pos
     WHERE asg.person_id         = pos.person_id
       AND asg.assignment_id     = p_assignment_id
       AND asg.business_group_id = pos.business_group_id
       AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
       AND NVL(pos.actual_termination_date,(to_date('31-12-4712','DD-MM-YYYY')))
             BETWEEN asg.effective_start_date AND asg.effective_end_date
     ORDER BY 1 DESC;
Line: 4490

      g_asg_tab.DELETE;
Line: 4909

        SELECT MAX(FND_NUMBER.CANONICAL_TO_NUMBER(action_information11))
          FROM pay_action_information pai
              ,pay_assignment_actions paa
         WHERE pai.action_information_category = 'IN_24Q_PERSON'
           AND pai.action_information3         = g_gre_id
           AND pai.action_information2         = g_year||g_quarter
           AND pai.action_information1         = p_person_id
           AND pai.source_id                   = p_source_id
           AND pai.action_context_id           = paa.assignment_action_id
           AND paa.payroll_action_id           = p_24qa_pay_act_id
           AND paa.payroll_action_id IN(
                                        SELECT org_information3
                                          FROM hr_organization_information
                                         WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                           AND organization_id         = g_gre_id
                                           AND org_information1        = g_year
                                           AND org_information2        = g_quarter
                                           AND org_information5        = 'A'
                                           AND org_information6        = 'O'
                                       );
Line: 4934

       SELECT MAX(FND_NUMBER.CANONICAL_TO_NUMBER(action_information12))
          FROM pay_action_information pai
              ,pay_assignment_actions paa
              ,pay_action_interlocks  locks
              ,pay_assignment_actions paa_master
         WHERE pai.action_information_category = 'IN_24QC_PERSON'
           AND pai.action_information3         = g_gre_id
           AND pai.action_information2         = g_year||g_quarter
           AND pai.action_information1         = p_person_id
           AND pai.source_id                   = p_source_id
           AND locks.locking_action_id         = pai.action_context_id
           AND locks.locked_action_id          = paa.assignment_action_id
           AND paa.payroll_action_id           = p_24qa_pay_act_id
           AND paa_master.assignment_action_id = pai.action_context_id
           AND paa_master.payroll_action_id IN(
                                                  SELECT org_information3
                                                    FROM hr_organization_information
                                                   WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                     AND organization_id         = g_gre_id
                                                     AND org_information1        = g_year
                                                     AND org_information2        = g_quarter
                                                     AND org_information5        = 'A'
                                                     AND org_information6        = 'C'
                                                 );
Line: 4961

        SELECT MAX(FND_NUMBER.CANONICAL_TO_NUMBER(action_information11))
          FROM pay_action_information pai
              ,pay_assignment_actions paa
         WHERE pai.action_information_category = 'IN_24Q_PERSON'
           AND pai.action_information3         = g_gre_id
           AND pai.action_information2         = g_year||g_quarter
           AND pai.action_context_id           = paa.assignment_action_id
           AND paa.payroll_action_id           = p_24qa_pay_act_id
           AND paa.payroll_action_id IN(
                                        SELECT org_information3
                                          FROM hr_organization_information
                                         WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                           AND organization_id         = g_gre_id
                                           AND org_information1        = g_year
                                           AND org_information2        = g_quarter
                                           AND org_information5        = 'A'
                                           AND org_information6        = 'O'
                                       );
Line: 4983

       SELECT MAX(FND_NUMBER.CANONICAL_TO_NUMBER(action_information12))
          FROM pay_action_information pai
              ,pay_assignment_actions paa
              ,pay_action_interlocks  locks
              ,pay_assignment_actions paa_master
         WHERE pai.action_information_category = 'IN_24QC_PERSON'
           AND pai.action_information3         = g_gre_id
           AND pai.action_information2         = g_year||g_quarter
           AND locks.locking_action_id         = pai.action_context_id
           AND locks.locked_action_id          = paa.assignment_action_id
           AND paa.payroll_action_id           = p_24qa_pay_act_id
           AND paa_master.assignment_action_id = pai.action_context_id
           AND paa_master.payroll_action_id IN(
                                                  SELECT org_information3
                                                    FROM hr_organization_information
                                                   WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                     AND organization_id         = g_gre_id
                                                     AND org_information1        = g_year
                                                     AND org_information2        = g_quarter
                                                     AND org_information5        = 'A'
                                                     AND org_information6        = 'C'
                                                 );
Line: 5011

     SELECT assignment_action_id
       FROM pay_assignment_actions
      WHERE payroll_action_id = p_payroll_action_id
        AND assignment_id     = p_assignment_id;
Line: 5018

      SELECT
              FND_NUMBER.CANONICAL_TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) source_id
              ,paf.assignment_id       assignment_id
             FROM pay_assignment_actions paa
                 ,pay_payroll_actions ppa
                 ,per_assignments_f paf
             WHERE paf.assignment_id = paa.assignment_id
             AND paf.assignment_id =p_assignment_id
             AND paa.tax_unit_id  = g_gre_id
             AND paa.payroll_action_id = ppa.payroll_action_id
             AND ppa.action_type IN('R','Q','I','B')
             AND ppa.payroll_id    = paf.payroll_id
             AND ppa.action_status ='C'
             AND ppa.effective_date between g_start_date and g_end_date
             AND paa.source_action_id IS NULL
             AND (1 = DECODE(ppa.action_type,'I',1,0)
              OR EXISTS (SELECT ''
                        FROM pay_action_interlocks intk,
                             pay_assignment_actions paa1,
                             pay_payroll_actions ppa1
                        WHERE intk.locked_action_id = paa.assignment_Action_id
                        AND intk.locking_action_id =  paa1.assignment_action_id
                        AND paa1.payroll_action_id =ppa1.payroll_action_id
                        AND ppa1.action_type in('P','U')
                        AND ppa.action_type in('R','Q','B')
                        AND ppa1.action_status ='C'
                        AND ppa1.effective_date between g_start_date and g_end_date
                        AND ROWNUM =1 ))
                GROUP BY paf.assignment_id;
Line: 5050

      SELECT
              FND_NUMBER.CANONICAL_TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) source_id
              ,paf.assignment_id       assignment_id
             FROM pay_assignment_actions paa
                 ,pay_payroll_actions ppa
                 ,per_assignments_f paf
             WHERE paf.assignment_id = paa.assignment_id
             AND paf.assignment_id =p_assignment_id
             AND paa.tax_unit_id  = g_gre_id
             AND paa.payroll_action_id = ppa.payroll_action_id
             AND paa.assignment_action_id < p_max_asact_id
             AND ppa.action_type IN('R','Q','I','B')
             AND ppa.payroll_id    = paf.payroll_id
             AND ppa.action_status ='C'
             AND ppa.effective_date between g_start_date and g_end_date
             AND paa.source_action_id IS NULL
             AND (1 = DECODE(ppa.action_type,'I',1,0)
              OR EXISTS (SELECT ''
                        FROM pay_action_interlocks intk,
                             pay_assignment_actions paa1,
                             pay_payroll_actions ppa1
                        WHERE intk.locked_action_id = paa.assignment_Action_id
                        AND intk.locking_action_id =  paa1.assignment_action_id
                        AND paa1.payroll_action_id =ppa1.payroll_action_id
                        AND ppa1.action_type in('P','U')
                        AND ppa.action_type in('R','Q','B')
                        AND ppa1.action_status ='C'
                        AND ppa1.effective_date between g_start_date and g_end_date
                        AND ROWNUM =1 ))
                GROUP BY paf.assignment_id;
Line: 5085

        SELECT DISTINCT pai.source_id,pai.action_context_id
          FROM pay_action_information pai
              ,pay_assignment_actions paa
              ,pay_action_interlocks  locks
              ,pay_assignment_actions paa_master
         WHERE pai.action_information_category = 'IN_24QC_PERSON'
           AND pai.assignment_id               = p_assignment_id
           AND pai.action_information3         = g_gre_id
           AND pai.action_information2         = g_year||g_quarter
           AND pai.action_information10        = 'A'
           AND locks.locking_action_id         = pai.action_context_id
           AND locks.locked_action_id          = paa.assignment_action_id
           AND paa.payroll_action_id           = p_24qa_pay_act_id
           AND paa_master.assignment_action_id = pai.action_context_id
           AND paa_master.payroll_action_id IN(
                                                  SELECT org_information3
                                                    FROM hr_organization_information
                                                   WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                     AND organization_id         = g_gre_id
                                                     AND org_information1        = g_year
                                                     AND org_information2        = g_quarter
                                                     AND org_information5        = 'A'
                                                     AND org_information6        = 'C'
                                                 )
                                                 ORDER BY pai.source_id DESC;
Line: 5115

       SELECT DISTINCT pai.source_id,pai.action_context_id
          FROM pay_action_information pai
         WHERE pai.action_information_category IN('IN_24Q_PERSON')
           AND pai.action_information3         = g_gre_id
           AND pai.assignment_id               = p_assignment_id
           AND pai.action_context_id           = p_action_context_id
           ORDER BY pai.source_id DESC;
Line: 5125

       SELECT DISTINCT
       FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values(p_category,p_balance_name,pai.action_context_id,pai.source_id,1)))
         FROM pay_action_information pai
        WHERE pai.action_context_id           = p_action_context_id
          AND pai.action_information_category = p_category
          AND pai.source_id                   = p_24q_source_id;
Line: 5166

    l_count_delete            NUMBER;
Line: 5184

             pay_in_utils.set_location(g_debug,'Value of g_count_sal_delete : '|| g_count_sal_delete, 1);
Line: 5185

             FOR i IN 1..g_count_sal_delete - 1
             LOOP

                pay_in_utils.set_location(g_debug,'Checking archived presence of this deleted assignment action',1);
Line: 5197

                pay_in_utils.trace('l_flag in g_count_sal_delete ',l_flag);
Line: 5198

                pay_in_utils.trace('l_flag1 in g_count_sal_delete ',l_flag1);
Line: 5202

                      pay_in_utils.set_location(g_debug,'Deleted Salary Detail Record Not Archived. Hence doing '||g_sal_data_rec_del(i).source_id,1);
Line: 5222

                         pay_in_utils.trace('Delete Source ID            : ', g_sal_data_rec_del(i).source_id );
Line: 5393

                        pay_in_utils.trace('Delete Source ID :   ', g_sal_data_rec_del(i).source_id );
Line: 5800

                      l_count_delete := 0;
Line: 5811

                               FOR p IN 1..g_count_sal_delete - 1
                               LOOP
                                 IF (g_sal_data_rec_add(i).assignment_id = g_sal_data_rec_del(p).assignment_id)
                                 THEN
                                  l_count_delete := l_count_delete + 1;
Line: 5818

                               IF (l_count_delete = 0) THEN
                                  l_future_pay := TRUE;
Line: 5926

        pay_in_utils.set_location(g_debug,'Value of g_count_sal_update : '|| g_count_sal_update, 1);
Line: 5928

      FOR i IN 1..g_count_sal_update - 1
      LOOP
         pay_in_utils.set_location(g_debug,'Checking archived presence of this updated assignment action',1);
Line: 5941

          pay_in_utils.trace('l_flag in g_count_sal_update   : ', l_flag );
Line: 5942

          pay_in_utils.trace('l_flag1 in g_count_sal_update  : ', l_flag1 );
Line: 5943

          pay_in_utils.trace('l_flag2 in g_count_sal_update  : ', l_flag2 );
Line: 5948

              pay_in_utils.set_location(g_debug,'Updated Asg Action Not Archived. hence doing '||g_sal_data_rec_upd(i).source_id,1);
Line: 6041

                         pay_in_utils.trace('Update Source_id :',g_sal_data_rec_upd(i).source_id);
Line: 6043

                         pay_in_utils.trace('l_gre_count in g_count_sal_update: ',l_gre_count);
Line: 6287

    SELECT pai.action_information_category
          ,pai.action_context_id
      FROM pay_action_information pai
          ,pay_assignment_actions paa
     WHERE paa.assignment_action_id = p_last_arc_asg_action_id
       AND pai.action_context_id    = paa.payroll_action_id
       AND pai.action_context_type  = 'PA'
     ORDER BY pai.action_context_id DESC;
Line: 6300

        SELECT action_information25
          FROM pay_action_information pai
              ,pay_assignment_actions paa
         WHERE pai.action_information_category = 'IN_24Q_DEDUCTEE'
           AND pai.action_information3         = g_gre_id
           AND pai.action_information1         = p_challan_number
           AND pai.action_context_id           = paa.assignment_action_id
           AND paa.payroll_action_id           = p_24qa_pay_act_id
           AND pai.source_id                   = p_element_entry_id;
Line: 6314

        SELECT action_information25
          FROM pay_action_information pai
              ,pay_assignment_actions paa
              ,pay_action_interlocks  locks
         WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
           AND pai.action_information3         = g_gre_id
           AND pai.action_information2         = g_year||g_quarter
           AND pai.action_information1         = p_challan_number
           AND locks.locking_action_id         = pai.action_context_id
           AND locks.locked_action_id          = paa.assignment_action_id
           AND paa.payroll_action_id           = p_24qa_pay_act_id
           AND pai.source_id                   = p_element_entry_id;
Line: 6330

        SELECT MAX(TO_NUMBER(action_information25))
          FROM pay_action_information pai
              ,pay_assignment_actions paa
              ,pay_action_interlocks  locks
              ,pay_assignment_actions paa_master
         WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
           AND pai.action_information3         = g_gre_id
           AND pai.action_information2         = g_year||g_quarter
           AND pai.action_information1         = p_challan_number
           AND locks.locking_action_id         = pai.action_context_id
           AND locks.locked_action_id          = paa.assignment_action_id
           AND paa.payroll_action_id           = p_24qa_pay_act_id
           AND paa_master.assignment_action_id = pai.action_context_id
           AND paa_master.payroll_action_id IN(
                                                  SELECT org_information3
                                                    FROM hr_organization_information
                                                   WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                     AND organization_id         = g_gre_id
                                                     AND org_information1        = g_year
                                                     AND org_information2        = g_quarter
                                                     AND org_information5        = 'A'
                                                     AND org_information6        = 'C'
                                                 );
Line: 6356

        SELECT MAX(TO_NUMBER(action_information25))
          FROM pay_action_information pai
              ,pay_assignment_actions paa
         WHERE pai.action_information_category = 'IN_24Q_DEDUCTEE'
           AND pai.action_information3         = g_gre_id
           AND pai.action_information1         = p_challan_number
           AND pai.action_context_id           = paa.assignment_action_id
           AND paa.payroll_action_id           = p_24qa_pay_act_id
           AND paa.payroll_action_id IN(
                                        SELECT org_information3
                                          FROM hr_organization_information
                                         WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                           AND organization_id         = g_gre_id
                                           AND org_information1        = g_year
                                           AND org_information2        = g_quarter
                                           AND org_information5        = 'A'
                                           AND org_information6        = 'O'
                                       );
Line: 6377

     SELECT assignment_action_id
       FROM pay_assignment_actions
      WHERE payroll_action_id = p_payroll_action_id
        AND assignment_id     = p_assignment_id;
Line: 6407

      pay_in_utils.set_location(g_debug,'Value of g_count_ee_delete : '|| g_count_ee_delete, 1);
Line: 6410

             FOR i IN 1..g_count_ee_delete - 1
             LOOP

                pay_in_utils.set_location(g_debug,'Checking archived presence of this deleted ee',1);
Line: 6419

                      pay_in_utils.set_location(g_debug,'Deleted EE Not Archived. hence doing '||g_ee_data_rec_del(i).element_entry_id,1);
Line: 6648

      pay_in_utils.set_location(g_debug,'Value of g_count_ee_update : '|| g_count_ee_update, 1);
Line: 6649

      FOR i IN 1..g_count_ee_update - 1
      LOOP
         pay_in_utils.set_location(g_debug,'Checking archived presence of this updated ee',1);
Line: 6657

              pay_in_utils.set_location(g_debug,'Updated EE Not Archived. hence doing '||g_ee_data_rec_upd(i).element_entry_id,1);
Line: 6861

       SELECT  action_information13
              ,action_information14
              ,action_information17
              ,action_information25
              ,action_information_id
          FROM pay_action_information
         WHERE action_context_type         = 'PA'
           AND action_information_category = 'IN_24QC_CHALLAN'
           AND action_information15        = p_24qa_pay_act_id
           AND action_information1         = p_transfer_voucher_number
           AND action_context_id IN(
                                    SELECT org_information3
                                      FROM hr_organization_information
                                     WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                       AND organization_id         = g_gre_id
                                       AND org_information1        = g_year
                                       AND org_information2        = g_quarter
                                       AND org_information5        = 'A'
                                       AND org_information6        = 'C'
                                   )
         ORDER BY action_information_id DESC;
Line: 6885

        SELECT action_information4      bank_code
              ,action_information5      payment_date
              , NVL(action_information6,0)
              + NVL(action_information7,0)
              + NVL(action_information8,0)
              + NVL(action_information9,0)
              + NVL(action_information10,0)
              ,action_information25
          FROM pay_action_information pai
         WHERE pai.action_context_type         = 'PA'
           AND pai.action_information_category = 'IN_24Q_CHALLAN'
           AND pai.action_context_id           = p_24qa_pay_act_id
           AND pai.action_information1         = p_transfer_voucher_number
         ORDER BY action_information_id DESC;
Line: 6902

      SELECT hoi.org_information3           challan_number,
             hoi.org_information2           transfer_voucher_date,
             hoi.org_information4           amount,
             hoi.org_information7           surcharge,
             hoi.org_information8           education_cess,
             hoi.org_information10          other,
             hoi.org_information9           interest,
             (SELECT hoi_bank.org_information4
               FROM hr_organization_information hoi_bank
              WHERE hoi_bank.organization_id = g_gre_id
                AND hoi_bank.org_information_context = 'PER_IN_CHALLAN_BANK'
                AND hoi_bank.org_information_id = hoi.org_information5
             )                              bank_branch_code,
             hoi.org_information11          cheque_dd_num,
             hoi.org_information12          book_entry,
             hoi.org_information_id         org_information_id
        FROM hr_organization_information hoi
       WHERE hoi.org_information_id IN
       (
            SELECT hoi.org_information_id
              FROM hr_organization_information hoi
                  ,hr_organization_units hou
             WHERE hoi.organization_id     = g_gre_id
               AND  hoi.organization_id    = hou.organization_id
               AND  hou.business_group_id  = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
               AND org_information_context = 'PER_IN_IT_CHALLAN_INFO'
       )
         AND hoi.org_information13 = g_quarter
         AND hoi.org_information1  = g_tax_year;
Line: 6964

          pay_in_utils.set_location(g_debug,'Checking in Updated Challans ', 1);
Line: 7059

                                    ,p_action_information19           =>     update_challans(g_challan_data_noc(i).transfer_voucher_number)
                                    ,p_action_information25           =>     l_challan_deductee_no
                                    ,p_action_information_id          =>     l_action_info_id
                                    ,p_object_version_number          =>     l_ovn
                                    );
Line: 7096

      SELECT hoi.org_information3           challan_number,
             hoi.org_information2           transfer_voucher_date,
             hoi.org_information4           amount,
             hoi.org_information7           surcharge,
             hoi.org_information8           education_cess,
             hoi.org_information10          other,
             hoi.org_information9           interest,
             (SELECT hoi_bank.org_information4
               FROM hr_organization_information hoi_bank
              WHERE hoi_bank.organization_id = g_gre_id
                AND hoi_bank.org_information_context = 'PER_IN_CHALLAN_BANK'
                AND hoi_bank.org_information_id = hoi.org_information5
             )                              bank_branch_code,
             hoi.org_information11          cheque_dd_num,
             hoi.org_information12          book_entry,
             hoi.org_information_id         org_information_id
        FROM hr_organization_information hoi
       WHERE hoi.org_information_id IN
       (
            SELECT hoi.org_information_id
              FROM hr_organization_information hoi
                  ,hr_organization_units hou
             WHERE hoi.organization_id     = g_gre_id
               AND  hoi.organization_id    = hou.organization_id
               AND  hou.business_group_id  = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
               AND org_information_context = 'PER_IN_IT_CHALLAN_INFO'
       )
         AND hoi.org_information13 = g_quarter
         AND hoi.org_information1  = g_tax_year
         AND (
               (hoi.org_information3   = p_challan_number)
             OR
               (hoi.org_information_id = p_org_information_id)
             );
Line: 7157

             SELECT MAX(TO_NUMBER(action_information25))
               INTO j
               FROM pay_action_information
              WHERE action_information_category = 'IN_24Q_CHALLAN'
                AND action_information3         = g_gre_id
                AND action_information2         = g_year||g_quarter
                AND action_context_id           = p_24qa_pay_act_id
                AND action_context_id           IN(
                                                   SELECT org_information3
                                                     FROM hr_organization_information
                                                    WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                      AND organization_id  = g_gre_id
                                                      AND org_information1 = g_year
                                                      AND org_information2 = g_quarter
                                                      AND org_information5 = 'A'
                                                      AND org_information6 = 'O'
                                                    );
Line: 7176

             SELECT MAX(TO_NUMBER(action_information25))
               INTO k
               FROM pay_action_information
              WHERE action_information_category = 'IN_24QC_CHALLAN'
                AND action_information3         = g_gre_id
                AND action_information2         = g_year||g_quarter
                AND action_information15        = p_24qa_pay_act_id
                AND action_context_id           IN(
                                                   SELECT org_information3
                                                     FROM hr_organization_information
                                                    WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                      AND organization_id  = g_gre_id
                                                      AND org_information1 = g_year
                                                      AND org_information2 = g_quarter
                                                      AND org_information5 = 'A'
                                                      AND org_information6 = 'C'
                                                    );
Line: 7255

                                 ,p_action_information19           =>     update_challans(g_challan_data_add(i).transfer_voucher_number)
                                 ,p_action_information25           =>     l_challan_record_number + i
                                 ,p_action_information_id          =>     l_action_info_id
                                 ,p_object_version_number          =>     l_ovn
                                 );
Line: 7264

   pay_in_utils.set_location(g_debug,'Fetching Updated Challan Data ' ,1);
Line: 7271

             pay_in_utils.set_location(g_debug,'Checking archived presence of this updated challan',1);
Line: 7281

                              SELECT DISTINCT action_information25
                                INTO j
                                FROM pay_action_information
                               WHERE action_information_category = 'IN_24Q_CHALLAN'
                                 AND action_information3         = g_gre_id
                                 AND action_information2         = g_year||g_quarter
                                 AND action_context_id           = p_24qa_pay_act_id
                                 AND(action_information1         = g_challan_data_upd(i).transfer_voucher_number
                                    OR
                                     source_id                   = g_challan_data_upd(i).org_information_id
                                    )
                                 AND p_24qa_pay_act_id IN(
                                                         SELECT org_information3
                                                           FROM hr_organization_information
                                                          WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                            AND organization_id  = g_gre_id
                                                            AND org_information1 = g_year
                                                            AND org_information2 = g_quarter
                                                            AND org_information5 = 'A'
                                                            AND org_information6 = 'O'
                                                          );
Line: 7309

                            SELECT DISTINCT action_information25
                              INTO k
                              FROM pay_action_information
                             WHERE action_information_category = 'IN_24QC_CHALLAN'
                               AND action_information3         = g_gre_id
                               AND action_information2         = g_year||g_quarter
                               AND action_context_id           = p_24qa_pay_act_id
                               AND(action_information1         = g_challan_data_upd(i).transfer_voucher_number
                                  OR
                                   source_id                   = g_challan_data_upd(i).org_information_id
                                  )
                              AND action_information15 IN(
                                                       SELECT org_information3
                                                         FROM hr_organization_information
                                                        WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                                          AND organization_id  = g_gre_id
                                                          AND org_information1 = g_year
                                                          AND org_information2 = g_quarter
                                                          AND org_information5 = 'A'
                                                          AND org_information6 = 'C'
                                                        );
Line: 7383

                                   ,p_action_information19           =>     update_challans(g_challan_data_upd(i).transfer_voucher_number)
                                   ,p_action_information25           =>     l_challan_record_number
                                   ,p_action_information_id          =>     l_action_info_id
                                   ,p_object_version_number          =>     l_ovn
                                   );
Line: 7424

       SELECT org_information3
         FROM hr_organization_information
        WHERE org_information_context  = 'PER_IN_FORM24Q_RECEIPT_DF'
          AND organization_id          = g_gre_id
          AND org_information1         = g_year
          AND org_information4         = g_cancel_ref_number;
Line: 7433

       SELECT action_information2
             ,action_information5
             ,action_information7
         FROM pay_action_information
        WHERE action_context_id = p_action_context_id
          AND action_information_category LIKE 'IN_24Q%ORG'
          AND action_context_type = 'PA';
Line: 7539

       SELECT action_information5    legal_name
             ,action_information7    deductor_type
             ,1                      BUI
             ,action_context_id
         FROM pay_action_information
        WHERE action_information_category IN ('IN_24Q_ORG','IN_24QC_ORG')
          AND action_context_id   = p_last_act_cxt_id
          AND action_context_type = 'PA'
          AND action_information1 = g_gre_id
          AND action_information2 = p_tan_number
          AND action_information3 = g_year||g_quarter
        ORDER BY action_context_id DESC;
Line: 7554

        SELECT action_information26
         FROM pay_action_information
        WHERE action_information_category = 'IN_24Q_ORG'
          AND action_context_id   = p_24qa_pay_act_id
          AND action_context_type = 'PA'
          AND action_information1 = g_gre_id
          AND action_information2 = p_tan_number
          AND action_information3 = g_year||g_quarter
        ORDER BY action_context_id DESC;
Line: 7618

                SELECT DECODE(DECODE(g_24qc_empr_change,'N',DECODE(g_24qc_rep_adr_chg,'Y','Y','N'),'Y'),'Y',1,0)
                  INTO l_batch_upd_indicator
                  FROM dual;
Line: 7760

       SELECT 1
         FROM pay_action_information
        WHERE action_information_category = 'IN_24Q_PERSON'
          AND action_context_id = p_assignment_act_id
          AND action_information3 = g_gre_id;
Line: 7768

       SELECT 1
         FROM pay_action_information
        WHERE action_information_category = 'IN_24QC_DEDUCTEE'
          AND action_context_id = p_assignment_act_id
          AND action_information3 = g_gre_id;
Line: 7837

       SELECT assignment_action_id
             ,assignment_id
         FROM pay_assignment_actions
        WHERE payroll_action_id = p_payroll_action_id;
Line: 7846

       SELECT assignment_action_id
         FROM pay_assignment_actions paa
             ,pay_action_interlocks  pai
        WHERE paa.assignment_id        = p_assignment_id
          AND paa.assignment_action_id = pai.locking_action_id
          AND paa.payroll_action_id    = p_payroll_action_id;
Line: 7857

       SELECT 1
         FROM pay_action_interlocks  pai
        WHERE pai.locking_action_id   = p_locking_act_id
          AND pai.locked_action_id    = p_locked_act_id;
Line: 7862

    CURSOR c_select_prev_24qc
    IS
       SELECT MAX(org_information3)
         FROM hr_organization_information
        WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
          AND organization_id  = g_gre_id
          AND org_information1 = g_year
          AND org_information2 = g_quarter
          AND org_information5 = 'A'
          AND org_information6 = 'C'
          AND org_information3 <> p_locked_pay_act_id_qc;
Line: 7894

                   SELECT pay_assignment_actions_s.NEXTVAL
                     INTO l_action_id
                     FROM dual;
Line: 7902

                   pay_in_utils.set_location(g_debug,'Inserting Assignment Actions ', 14);
Line: 7930

               SELECT pay_assignment_actions_s.NEXTVAL
                 INTO l_action_id
                 FROM dual;
Line: 7934

                pay_in_utils.set_location(g_debug,'Inserting Assignment Actions as it was null', 14);
Line: 7961

    OPEN  c_select_prev_24qc;
Line: 7962

    FETCH c_select_prev_24qc INTO l_24qc_prv_pay_act_id;
Line: 7963

    CLOSE c_select_prev_24qc;
Line: 7982

                   SELECT pay_assignment_actions_s.NEXTVAL
                     INTO l_action_id
                     FROM dual;
Line: 7986

                    pay_in_utils.set_location(g_debug,'Inserting Assignment Actions as it was null', 14);
Line: 8039

  CURSOR c_select_deductee
  IS
     SELECT DISTINCT pai.action_information1 challan
       FROM pay_action_information pai
           ,pay_assignment_actions paa
      WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
        AND paa.assignment_action_id        = pai.action_context_id
        AND paa.payroll_action_id           = p_payroll_action_id
        AND pai.action_information19        IS NOT NULL
        AND pai.action_information20        IS NOT NULL;
Line: 8052

     SELECT pai.action_information25 challan_seq
       FROM pay_action_information pai
      WHERE pai.action_information_category IN ('IN_24QC_CHALLAN','IN_24Q_CHALLAN')
        AND pai.action_information1         = p_challan_number
        AND pai.action_context_id IN
                                   (
                                    SELECT org_information3
                                      FROM hr_organization_information
                                     WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
                                       AND organization_id  = g_gre_id
                                       AND org_information1 = g_year
                                       AND org_information2 = g_quarter
                                       AND org_information5 = 'A'
                                )
        AND ROWNUM                          = 1;
Line: 8071

    SELECT action_information2    tan_number
          ,action_information5    legal_name
          ,action_information7    deductor_type
      FROM pay_action_information
     WHERE action_information_category IN ('IN_24Q_ORG','IN_24QC_ORG')
       AND action_context_id   = p_last_act_cxt_id
       AND action_context_type = 'PA'
       AND action_information1 = g_gre_id
       AND action_information3 = g_year||g_quarter
     ORDER BY action_context_id DESC;
Line: 8107

   FOR c_rec IN c_select_deductee
   LOOP
        OPEN  c_challan_sequence(c_rec.challan);
Line: 8115

         pay_in_utils.set_location(g_debug,'Checking Challan Data in updated Data', 1);
Line: 8120

                pay_in_utils.set_location(g_debug,'Found Challan in updated Data', 1);
Line: 8140

                                    ,p_action_information19           =>     update_challans(g_challan_data_upd(i).transfer_voucher_number)
                                    ,p_action_information25           =>     l_challan_deductee_no
                                    ,p_action_information29           =>     'C5'
                                    ,p_action_information_id          =>     l_action_info_id
                                    ,p_object_version_number          =>     l_ovn
                                    );
Line: 8173

                                    ,p_action_information19           =>     update_challans(g_challan_data_noc(i).transfer_voucher_number)
                                    ,p_action_information25           =>     l_challan_deductee_no
                                    ,p_action_information29           =>     'C5'
                                    ,p_action_information_id          =>     l_action_info_id
                                    ,p_object_version_number          =>     l_ovn
                                    );
Line: 8283

      SELECT org_information3 -- payroll action id of this quarter Form 24Q A/C Archival
            ,org_information6 -- Archival Type (Original or Correction Statement)
        FROM hr_organization_information
       WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
         AND organization_id  = g_gre_id
         AND org_information1 = g_year
         AND org_information2 = g_quarter
         AND org_information5 = 'A'
       ORDER BY org_information3 DESC;
Line: 8297

      SELECT 1
        FROM pay_action_interlocks
       WHERE locking_action_id = p_locking_action_id
         AND locked_action_id  = p_locked_action_id;
Line: 8306

      SELECT assignment_action_id
        FROM pay_assignment_actions
       WHERE payroll_action_id = p_payroll_act_id
         AND assignment_id     = p_assignment_id;
Line: 8313

      SELECT max(locking_action_id)
       FROM pay_action_interlocks
      WHERE locked_action_id = p_24qc_arc_asg_act_id
      ORDER BY locking_action_id DESC;
Line: 8318

    CURSOR c_select_prev_24qc(p_locked_pay_act_id_qc  NUMBER)
    IS
       SELECT MAX(org_information3)
         FROM hr_organization_information
        WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
          AND organization_id  = g_gre_id
          AND org_information1 = g_year
          AND org_information2 = g_quarter
          AND org_information5 = 'A'
          AND org_information6 = 'C'
          AND org_information3 <> p_locked_pay_act_id_qc;
Line: 8332

       SELECT source_id
         FROM pay_action_information
        WHERE action_context_id           = p_pay_act_id_last_24Q
          AND action_information_category = 'IN_24QC_ORG'
          AND action_context_type         = 'PA'
          AND action_information3         = g_year||g_quarter;
Line: 8341

      SELECT DECODE(pai.action_information_category,'IN_24Q_ORG',1,2)
       FROM pay_action_information pai
      WHERE pai.action_information_category IN ('IN_24QC_ORG','IN_24Q_ORG')
        AND pai.action_context_id           = p_pay_act_id_last_24Q
        AND pai.action_context_type         = 'PA'
        AND pai.action_information3         = g_year||g_quarter
        AND pai.action_information1         = g_gre_id;
Line: 8432

           OPEN  c_select_prev_24qc(g_24qc_payroll_act_id);
Line: 8433

           FETCH c_select_prev_24qc INTO l_last_24qc_pay_act_id;
Line: 8434

           CLOSE c_select_prev_24qc;
Line: 8472

           pay_in_utils.set_location(g_debug,'Inserting assignment action ids for Salary Records',30);
Line: 8474

           pay_in_utils.set_location(g_debug,'Inserting Asg Action ID for Deleted Assignment Actions ' ,35);
Line: 8476

           pay_in_utils.set_location(g_debug,'Value of g_count_sal_delete is '|| g_count_sal_delete, 39);
Line: 8477

           IF ((g_count_sal_delete > 1) AND (g_correction_mode <> 'Y'))
           THEN
                   FOR i IN 1..g_count_sal_delete - 1
                   LOOP
                      l_24q_asg_action_id := NULL;
Line: 8490

                      pay_in_utils.trace('l_dummy in g_count_sal_delete ',l_dummy);
Line: 8494

                           SELECT pay_assignment_actions_s.NEXTVAL
                             INTO l_action_id
                             FROM dual;
Line: 8499

                           pay_in_utils.set_location(g_debug,'Inserting Assignment action id ',61);
Line: 8624

           pay_in_utils.set_location(g_debug,'Inserting Asg Action ID for Added Assignment Actions ' ,60);
Line: 8643

                           SELECT pay_assignment_actions_s.NEXTVAL
                             INTO l_action_id
                             FROM dual;
Line: 8648

                           pay_in_utils.set_location(g_debug,'Inserting Assignment action id ',61);
Line: 8774

           pay_in_utils.set_location(g_debug,'Inserting Asg Action ID for Updated Assignment Actions ' ,70);
Line: 8775

           pay_in_utils.set_location(g_debug,'Value of g_count_sal_update is '|| g_count_sal_update, 71);
Line: 8776

           IF ((g_count_sal_update > 1) AND (g_correction_mode <> 'Y'))
           THEN
                  FOR i IN 1..g_count_sal_update - 1
                  LOOP
                      l_24q_asg_action_id := NULL;
Line: 8790

                      pay_in_utils.trace('l_dummy in g_count_sal_update ',l_dummy);
Line: 8794

                           SELECT pay_assignment_actions_s.NEXTVAL
                             INTO l_action_id
                             FROM dual;
Line: 8799

                           pay_in_utils.set_location(g_debug,'Inserting Assignment action id ',61);
Line: 8922

           pay_in_utils.set_location(g_debug,'Asg Action ID Insertion Over for Salary Records ', 80);
Line: 8941

           pay_in_utils.set_location(g_debug,'Inserting assignment action id in pay_assignment_actions ',40);
Line: 8942

           pay_in_utils.set_location(g_debug,'Inserting Asg Action ID for Deleted Element Entries ' ,50);
Line: 8944

           pay_in_utils.set_location(g_debug,'Value of g_count_ee_delete is '|| g_count_ee_delete, 51);
Line: 8945

           IF ((g_count_ee_delete > 1)AND (g_correction_mode <> 'Y'))
           THEN
                   FOR i IN 1..g_count_ee_delete - 1
                   LOOP
                      l_24q_asg_action_id := NULL;
Line: 8957

                           SELECT pay_assignment_actions_s.NEXTVAL
                             INTO l_action_id
                             FROM dual;
Line: 8962

                           pay_in_utils.set_location(g_debug,'Inserting Assignment action id ',61);
Line: 9018

           pay_in_utils.set_location(g_debug,'Inserting Asg Action ID for Added Element Entries ' ,60);
Line: 9033

                           SELECT pay_assignment_actions_s.NEXTVAL
                             INTO l_action_id
                             FROM dual;
Line: 9038

                           pay_in_utils.set_location(g_debug,'Inserting Assignment action id ',61);
Line: 9095

           pay_in_utils.set_location(g_debug,'Inserting Asg Action ID for Updated Element Entries ' ,70);
Line: 9096

           pay_in_utils.set_location(g_debug,'Value of g_count_ee_update is '|| g_count_ee_update, 71);
Line: 9097

           IF ((g_count_ee_update > 1)AND(g_correction_mode <> 'Y'))
           THEN
                  FOR i IN 1..g_count_ee_update - 1
                  LOOP
                      l_24q_asg_action_id := NULL;
Line: 9113

                           SELECT pay_assignment_actions_s.NEXTVAL
                             INTO l_action_id
                             FROM dual;
Line: 9118

                           pay_in_utils.set_location(g_debug,'Inserting Assignment action id ',61);
Line: 9173

           pay_in_utils.set_location(g_debug,'Asg Action ID Insertion Over ', 80);
Line: 9200

           IF ((g_count_ee_delete = 1) AND (g_count_ee_addition = 1) AND (g_count_ee_update = 1))
           THEN
                        pay_in_utils.set_location(g_debug,'Generating Locking...',2);
Line: 9281

   SELECT DECODE(action_information15, 1, 'C1')
         ,action_information_id
         ,object_version_number
         ,action_information29
     FROM pay_action_information
    WHERE action_information1 = g_gre_id
      AND action_information3 = g_year||g_quarter
      AND action_context_type = 'PA'
      AND action_information_category = 'IN_24QC_ORG'
      AND action_context_id = p_payroll_action_id;
Line: 9294

    SELECT action_information1 challan
        ,DECODE(action_information18, 'U', 'C2', 'A', 'C9', 'NC', null) correction_type
        ,action_information_id
        ,object_version_number
    FROM pay_action_information
   WHERE action_information3 = g_gre_id
     AND action_information2 = g_year||g_quarter
     AND action_context_type = 'PA'
     AND action_information_category = 'IN_24QC_CHALLAN'
     AND action_context_id = p_payroll_action_id;
Line: 9308

    SELECT 'C3' correction_type
      FROM pay_action_information pai
     WHERE pai.action_context_id IN (SELECT assignment_action_id
                                       FROM pay_assignment_actions
                                      WHERE payroll_action_id = p_payroll_action_id
                                        AND assignment_id     = pai.assignment_id
                                    )
     AND action_context_type = 'AAP'
     AND action_information_category = 'IN_24QC_DEDUCTEE'
     AND action_information3 = g_gre_id
     AND action_information2 = g_year||g_quarter
     AND action_information15 IN ('U', 'A', 'D')
     AND (
            (    action_information19 IS NULL
             AND action_information20 IS NULL
            )
            OR
            (    (INSTR(action_information19,'C3') <> 0)
            )
         )
     AND action_information1 = p_challan_number;
Line: 9333

    SELECT 'C5' correction_type
      FROM pay_action_information pai
     WHERE pai.action_context_id IN ( SELECT assignment_action_id
                                        FROM pay_assignment_actions
                                       WHERE payroll_action_id = p_payroll_action_id
                                         AND assignment_id     = pai.assignment_id
                                    )
     AND action_context_type = 'AAP'
     AND action_information_category = 'IN_24QC_DEDUCTEE'
     AND action_information3 = g_gre_id
     AND action_information2 = g_year||g_quarter
     AND INSTR(action_information19,'C5') <> 0
     AND action_information20 IS NOT NULL
     AND action_information1 = p_challan_number;
Line: 9351

    SELECT 'C5' correction_type
      FROM pay_action_information pai
     WHERE pai.action_context_id IN ( SELECT assignment_action_id
                                        FROM pay_assignment_actions
                                       WHERE payroll_action_id = p_payroll_action_id
                                         AND assignment_id     = pai.assignment_id
                                    )
     AND action_context_type = 'AAP'
     AND action_information_category = 'IN_24QC_PERSON'
     AND action_information3 = g_gre_id
     AND action_information2 = g_year||g_quarter
     AND INSTR(action_information11,'C5') <> 0
     AND action_information1 = p_person_id;
Line: 9368

    SELECT 'C4' correction_type
      FROM pay_action_information pai
     WHERE pai.action_context_id IN ( SELECT assignment_action_id
                                        FROM pay_assignment_actions
                                       WHERE payroll_action_id = p_payroll_action_id
                                         AND assignment_id     = pai.assignment_id
                                    )
     AND action_context_type = 'AAP'
     AND action_information_category = 'IN_24QC_PERSON'
     AND action_information3 = g_gre_id
     AND action_information2 = g_year||g_quarter
     AND INSTR(action_information11,'C4') <> 0
     AND action_information1 = p_person_id;
Line: 9382

 CURSOR cur_salary_update_recs
  IS
SELECT DISTINCT action_information3          gre_id
  FROM pay_action_information
 WHERE action_information_category = 'IN_24QC_PERSON'
   AND action_context_type         = 'AAP'
   AND action_information10        = 'A'
   AND action_information11        = 'C4'
   AND action_context_id IN (SELECT assignment_action_id
                               FROM pay_assignment_actions
                              WHERE payroll_action_id = p_payroll_action_id)
   ORDER BY action_information3;
Line: 9396

 CURSOR cur_person_update_recs( p_gre_id VARCHAR2)
  IS
SELECT DISTINCT action_information1 person_id
     , source_id
     , action_information_id
     , object_version_number
  FROM pay_action_information
 WHERE action_information_category = 'IN_24QC_PERSON'
   AND action_context_type         = 'AAP'
   AND action_information3 = fnd_number.canonical_to_number(p_gre_id)
   AND action_information10 = 'A'
   AND action_information11 = 'C4'
   AND action_context_id IN (SELECT assignment_action_id
                               FROM pay_assignment_actions
                              WHERE payroll_action_id = p_payroll_action_id)
   ORDER BY  LENGTH(action_information1)
            ,action_information1
            ,source_id;
Line: 9417

SELECT MIN(fnd_number.canonical_to_number(action_information12)) min_salary_rec
  FROM pay_action_information
 WHERE action_information_category = 'IN_24QC_PERSON'
   AND action_context_type         = 'AAP'
   AND action_information3 = fnd_number.canonical_to_number(p_gre_id)
   AND action_information10 = 'A'
   AND action_information11 = 'C4'
   AND action_context_id IN (SELECT assignment_action_id
                               FROM pay_assignment_actions
                              WHERE payroll_action_id = p_payroll_action_id);
Line: 9431

SELECT DISTINCT action_information1 person_id
  FROM pay_action_information pai
 WHERE action_information_category = 'IN_24QC_PERSON'
   AND action_context_type         = 'AAP'
   AND action_context_id IN (SELECT assignment_action_id
                               FROM pay_assignment_actions
                              WHERE payroll_action_id = p_payroll_action_id
                                AND assignment_id     = pai.assignment_id)
   AND action_information3 = g_gre_id
   AND action_information2 = g_year||g_quarter;
Line: 9446

        SELECT SUM(NVL(pai.action_information9,0)) amount_deposited
          FROM pay_action_information  pai
              ,pay_assignment_actions  paa
         WHERE pai.action_information_category = 'IN_24Q_DEDUCTEE'
           AND paa.assignment_action_id        = pai.action_context_id
           AND pai.action_information1         = p_challan_number
           AND paa.payroll_action_id           = p_24q_pay_action_id;
Line: 9468

      l_org_update_string VARCHAR(2400);
Line: 9525

      pay_action_information_api.update_action_information
                (p_validate                       =>  FALSE
                ,p_action_information_id          =>  t_challan_nos(l_index).action_information_id
                ,p_object_version_number          =>  t_challan_nos(l_index).object_version_number
                ,p_action_information29           =>  t_challan_nos(l_index).correction_type
                );
Line: 9532

      l_org_update_string := NVL(l_org_update_string, '1') ||':'||t_challan_nos(l_index).correction_type;
Line: 9542

    FOR c_salary_rec IN cur_salary_update_recs
    LOOP
      OPEN cur_min_salary_record(c_salary_rec.gre_id);
Line: 9550

      FOR cur_rec IN cur_person_update_recs(c_salary_rec.gre_id)
      LOOP
               pay_action_information_api.update_action_information
                (p_validate                       => FALSE
                ,p_action_information_id          => cur_rec.action_information_id
                ,p_object_version_number          => cur_rec.object_version_number
                ,p_action_information12           => l_index
                );
Line: 9564

    pay_in_utils.set_location(g_debug,'Updated SD Record Number for A mode in Salary Records : ',3);
Line: 9575

       l_org_update_string := NVL(l_org_update_string, '1') ||':'||l_correction_type;
Line: 9583

       l_org_update_string := NVL(l_org_update_string, '1') ||':'||l_correction_type;
Line: 9590

    IF INSTR(l_org_update_string, 'C2') <> 0 THEN
       IF org_correction_type is null or org_correction_type = 'C1' THEN
          org_correction_type := 'C2';
Line: 9597

    IF INSTR(l_org_update_string, 'C3') <> 0 THEN
       IF org_correction_type IS NULL OR org_correction_type = 'C1' THEN
          org_correction_type := 'C3';
Line: 9604

    IF INSTR(l_org_update_string, 'C4') <> 0 THEN
       IF org_correction_type IS NULL THEN
          org_correction_type := 'C4';
Line: 9611

    IF INSTR(l_org_update_string, 'C5') <> 0 THEN
       IF org_correction_type IS NULL THEN
          org_correction_type := 'C5';
Line: 9618

    IF INSTR(l_org_update_string, 'C9') <> 0 THEN
       IF org_correction_type IS NULL THEN
          org_correction_type := 'C9';
Line: 9628

     pay_action_information_api.update_action_information
               (p_validate                       =>  FALSE
               ,p_action_information_id          =>  l_action_info_id
               ,p_object_version_number          =>  l_ovn
               ,p_action_information29           =>  org_correction_type
               );