Bank Export query

 -----------------------------Bank Export query-----------------------------

--------------parameters--------------------

1.p_pay_date(mandatory)

2.p_legal_entity

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

--------------list of values for legal entity----------------

SELECT DISTINCT haou1.NAME

FROM   hr_organization_units_f_tl haou1,

       per_all_assignments_f paam

WHERE  1 = 1

       AND Trunc(sysdate) BETWEEN Trunc(paam.effective_start_date) AND

                                  Trunc(paam.effective_end_date)

       AND paam.assignment_type IN ( 'E', 'C' )

       AND paam.primary_flag = 'Y'

       AND paam.system_person_type = 'EMP'

       AND paam.effective_latest_change = 'Y'

       AND paam.legal_entity_id = haou1.organization_id

       AND Trunc(sysdate) BETWEEN Trunc(haou1.effective_start_date) AND

                                  Trunc(haou1.effective_end_date)

       AND haou1.language = 'US' 

--and rownum<=1

-----------------------------main query for bank export---------------

WITH dedec

     AS (SELECT DISTINCT PRRV.result_value,

                         PRR.source_result_id,

                         PAAPF.person_id,

                         haou1.NAME Legal_Entity,

                         ptp.end_date

         FROM   fusion.pay_element_types_vl ELE,

                fusion.pay_run_results PRR,

                fusion.pay_run_result_values PRRV,

                fusion.pay_input_values_vl PIV,

                fusion.pay_payroll_rel_actions PPRA,

                fusion.pay_pay_relationships_dn PREL,

                fusion.pay_payroll_actions PPA,

                fusion.pay_all_payrolls_f PAP,

                fusion.per_all_assignments_f PAA,

                hr_organization_units_f_tl haou1,

                fusion.pay_ele_classifications_vl PEC,

                fusion.per_person_names_f PPN,

                fusion.per_all_people_f PAAPF,

                per_person_types_vl PER_TYPES,

                per_periods_of_service ppos,

                pay_payroll_assignments ppas,

                pay_balance_feeds_f pbff,

                pay_balance_types_tl pbtl,

                pay_time_periods ptp

         --per_all_people_f papf

         WHERE  PPA.payroll_action_id = PPRA.payroll_action_id

                AND PPA.payroll_id = PAP.payroll_id

                AND PAA.person_type_id = PER_TYPES.person_type_id

                AND PPA.action_type IN ( 'R', 'Q' )

                AND PRR.payroll_rel_action_id = PPRA.payroll_rel_action_id

                AND PPRA.run_type_id IS NOT NULL

                AND ppra.source_action_id IS NOT NULL --Newly added

                AND PRR.element_type_id = ELE.element_type_id

                AND PRRV.run_result_id = PRR.run_result_id

                AND PIV.input_value_id = PRRV.input_value_id

                AND PREL.payroll_relationship_id = PPRA.payroll_relationship_id

                AND PREL.person_id = PAA.person_id

                AND ppos.person_id = PAAPF.person_id

                AND ppos.period_of_service_id = paa.period_of_service_id

                AND paa.legal_entity_id = haou1.organization_id

                AND Trunc(sysdate) BETWEEN Trunc(haou1.effective_start_date) AND

                                           Trunc(haou1.effective_end_date)

                AND haou1.language = 'US'

                AND paa.effective_latest_change = 'Y'

                AND paa.primary_flag = 'Y'

                AND PREL.person_id = PPN.person_id

                AND PPN.name_type = 'GLOBAL'

                AND PREL.person_id = PAAPF.person_id

                AND PREL.payroll_relationship_id = ppas.payroll_relationship_id

                AND ppas.hr_assignment_id = paa.assignment_id

                AND Trunc(sysdate) BETWEEN PPN.effective_start_date AND

                                           PPN.effective_end_date

                AND Trunc(sysdate) BETWEEN PAA.effective_start_date AND

                                           PAA.effective_end_date

                AND Trunc(sysdate) BETWEEN PAAPF.effective_start_date AND

                                           PAAPF.effective_end_date

                AND Trunc(sysdate) BETWEEN ELE.effective_start_date AND

                                           ELE.effective_end_date

                AND Trunc(sysdate) BETWEEN piv.effective_start_date AND

                                           piv.effective_end_date

                AND Trunc(sysdate) BETWEEN PAP.effective_start_date AND

                                           PAP.effective_end_date

                --AND     flv.lookup_type(+)          ='EMP_CAT'

                --AND     paa.employment_category     = flv.lookup_code(+)

                --AND     PPA.EFFECTIVE_DATE   between TRUNC(:Effective_Date_Parameter) and trunc(:Effective_end_Date_Parameter)

                AND PAA.primary_assignment_flag = 'Y'

                AND paa.assignment_type IN ( 'E', 'N' )

                AND PAA.assignment_status_type IN( 'ACTIVE' )

                AND ELE.classification_id = PEC.classification_id

                AND Trunc(ptp.end_date) BETWEEN PEC.date_from AND

                    Nvl(PEC.date_to, PPA.effective_date)

                AND retro_component_id IS NULL

                --AND     papf.person_id           =:PER_ID

                AND PIV.element_type_id = pbff.element_type_id

                AND PIV.input_value_id = pbff.input_value_id

                AND pbff.balance_type_id = pbtl.balance_type_id

                AND pbtl.language = 'US'

                AND PTP.period_category = 'E'

                AND pap.payroll_id = ptp.payroll_id

                AND ppa.earn_time_period_id = ptp.time_period_id

                AND pbtl.balance_name = 'Net Pay'

                AND PEC.classification_name IN( 'Social Insurance Deductions',

                                                'Other' )

                AND PIV.base_name = ( 'Pay Value' )

         --AND PAAPF.person_id = papf.person_id

         --AND papf.person_number = '16'

         ORDER  BY PRR.source_result_id DESC)

SELECT DISTINCT Round(Nvl(( ban.allowance + ban.basic -

                            ban.gosi_ded_other_deducion ),

                                            ban.allowance + ban.basic))Net_pay,

                ban.*

FROM   (SELECT /*(SELECT SUM(To_number(peevf1.screen_entry_value))

                      FROM   pay_element_entry_values_f peevf1,

                             pay_input_values_tl pivt1,

                             pay_input_values_f pivf1,

                             pay_element_types_tl petf1,

                             pay_element_entries_f peef1

                      WHERE  peevf1.screen_entry_value IS NOT NULL

                             AND SYSDATE BETWEEN peevf1.effective_start_date AND

                                                 peevf1.effective_end_date

                             AND peevf1.input_value_id = pivf1.input_value_id

                             AND peevf1.element_entry_id = peef1.element_entry_id

                             AND pivt1.name = 'Amount'

                             AND pivt1.input_value_id = pivf1.input_value_id

                             AND SYSDATE BETWEEN pivf1.effective_start_date AND

                                                 pivf1.effective_end_date

                             AND pivf1.element_type_id = petf1.element_type_id

                             AND petf1.LANGUAGE = 'US'

                             AND pivt1.LANGUAGE = 'US'

                             AND petf1.element_name IN ( 'Basic Salary' )

                             AND petf1.element_type_id = peef1.element_type_id

                             AND SYSDATE BETWEEN peef1.effective_start_date AND

                                                 peef1.effective_end_date

                             AND peef1.person_id = papf.person_id)

                     Total_salary,*/ ppnf.full_name,

                              (SELECT pad.iban_number

                               FROM   pay_person_pay_methods_f ppm,

                                      pay_bank_accnt_details_vl pad,

                                      pay_pay_relationships_dn ppr

                               WHERE  ppm.bank_account_id IS NOT NULL

                                      AND ppm.bank_account_id =

                                          pad.bank_account_id

                                      AND ppr.payroll_relationship_id =

                                          ppm.payroll_relationship_id

                                      AND sysdate BETWEEN

                                          ppm.effective_start_date AND

                                          ppm.effective_end_date

                                      AND ppr.person_id = papf.person_id

                                      AND ppm.payroll_relationship_id =

                              (SELECT Max(ppr1.payroll_relationship_id)

                               FROM   pay_pay_relationships_dn ppr1

                               WHERE  ppr1.person_id = ppr.person_id)

                                      AND ppm.personal_payment_method_id =

                              (SELECT Max(ppm1.personal_payment_method_id)

                               FROM

                              fusion.pay_person_pay_methods_f ppm1

                                                                WHERE

                                  ppm1.payroll_relationship_id =

                                  ppm.payroll_relationship_id

                                  AND sysdate BETWEEN

                                      ppm1.effective_start_date AND

                                      ppm1.effective_end_date)) IBAN_NUMBER

                              ,

                              (SELECT pbae.eft_swift_code

                               FROM   pay_bank_accounts PBAE,

                                      per_all_people_f PPAF,

                                      per_all_assignments_m PAAM,

                                      pay_payroll_assignments PA,

                                      pay_payroll_rel_actions PPRA,

                                      pay_pre_payments PPP

                               WHERE  1 = 1

                                      AND PBAE.bank_account_id =

                                          PPP.payee_bank_account_id

                                      AND PPAF.person_id = PAAM.person_id

                                      AND sysdate BETWEEN

                                          paam.effective_start_date AND

                                          paam.effective_end_date

                                      AND sysdate BETWEEN

                                          ppaf.effective_start_date AND

                                          ppaf.effective_end_date

                                      AND paam.effective_latest_change = 'Y'

                                      AND paam.assignment_id =

                                          pa.hr_assignment_id

                                      AND PA.payroll_relationship_id =

                                          ppra.payroll_relationship_id

                                      AND PPP.payroll_rel_action_id =

                                          ppra.payroll_rel_action_id

                                      AND PPAF.person_id = papf.person_id)

                              SWIFT_CODE,

                              Decode((SELECT cs.salary_basis_code

                                      FROM   cmp_salary cs

                                      WHERE  papf.person_id = cs.person_id

                                             AND rownum = 1), 'MONTHLY',

                              'SALARY')

                              TYPE,

                              (SELECT cs.salary_amount

                               FROM   cmp_salary cs

                               WHERE  papf.person_id = cs.person_id

                                      AND rownum = 1)

                              Basic,

                              (SELECT pni.national_identifier_number

                               FROM   per_national_identifiers pni

                               WHERE  pni.person_id = papf.person_id

                                      AND rownum = 1

                                      AND pni.last_update_date =

                                          (SELECT Max(pni1.last_update_date)

                                           FROM   per_national_identifiers pni1

                                           WHERE  pni.national_identifier_id =

                                                  pni1.national_identifier_id

                                                  AND pni1.person_id =

                                                      pni.person_id))

                              "ID (National / Iqama)",

                              (SELECT Sum(To_number(peevf1.screen_entry_value))

                               FROM   pay_element_entry_values_f peevf1,

                                      pay_input_values_tl pivt1,

                                      pay_input_values_f pivf1,

                                      pay_element_types_tl petf1,

                                      pay_element_entries_f peef1

                               WHERE  sysdate BETWEEN

                                      peevf1.effective_start_date AND

                                      peevf1.effective_end_date

                                      AND peevf1.input_value_id =

                                          pivf1.input_value_id

                                      AND peevf1.element_entry_id =

                                          peef1.element_entry_id

                                      AND pivt1.NAME = 'Pay Value'

                                      AND pivt1.input_value_id =

                                          pivf1.input_value_id

                                      AND sysdate BETWEEN

                                          pivf1.effective_start_date

                                          AND

                                          pivf1.effective_end_date

                                      AND pivf1.element_type_id =

                                          petf1.element_type_id

                                      AND petf1.language = 'US'

                                      AND pivt1.language = 'US'

                                      AND petf1.element_name IN (

                                          'Housing Allowance' )

                                      AND petf1.element_type_id =

                                          peef1.element_type_id

                                      AND sysdate BETWEEN

                                          peef1.effective_start_date

                                          AND

                                          peef1.effective_end_date

                                      AND peef1.person_id = papf.person_id)

                              "Housing Allowance",

                              (SELECT Sum(To_number(peevf1.screen_entry_value))

                               FROM   pay_element_entry_values_f peevf1,

                                      pay_input_values_tl pivt1,

                                      pay_input_values_f pivf1,

                                      pay_element_types_tl petf1,

                                      pay_element_entries_f peef1

                               WHERE  sysdate BETWEEN

                                      peevf1.effective_start_date AND

                                      peevf1.effective_end_date

                                      AND peevf1.input_value_id =

                                          pivf1.input_value_id

                                      AND peevf1.element_entry_id =

                                          peef1.element_entry_id

                                      AND pivt1.NAME = 'Pay Value'

                                      AND pivt1.input_value_id =

                                          pivf1.input_value_id

                                      AND sysdate BETWEEN

                                          pivf1.effective_start_date

                                          AND

                                          pivf1.effective_end_date

                                      AND pivf1.element_type_id =

                                          petf1.element_type_id

                                      AND petf1.language = 'US'

                                      AND pivt1.language = 'US'

                                      AND petf1.element_name NOT IN(

                                          'Basic Salary', 'Housing Allowance' )

                                      AND petf1.element_type_id =

                                          peef1.element_type_id

                                      AND sysdate BETWEEN

                                          peef1.effective_start_date

                                          AND

                                          peef1.effective_end_date

                                      AND peef1.person_id = papf.person_id)

                              Transport_Other_Allowance,

                              (SELECT Sum(To_number(peevf1.screen_entry_value))

                               FROM   pay_element_entry_values_f peevf1,

                                      pay_input_values_tl pivt1,

                                      pay_input_values_f pivf1,

                                      pay_element_types_tl petf1,

                                      pay_element_entries_f peef1

                               WHERE  sysdate BETWEEN

                                      peevf1.effective_start_date AND

                                      peevf1.effective_end_date

                                      AND peevf1.input_value_id =

                                          pivf1.input_value_id

                                      AND peevf1.element_entry_id =

                                          peef1.element_entry_id

                                      AND pivt1.NAME = 'Pay Value'

                                      AND pivt1.input_value_id =

                                          pivf1.input_value_id

                                      AND sysdate BETWEEN

                                          pivf1.effective_start_date

                                          AND

                                          pivf1.effective_end_date

                                      AND pivf1.element_type_id =

                                          petf1.element_type_id

                                      AND petf1.language = 'US'

                                      AND pivt1.language = 'US'

                                      -- AND petf1.element_name NOT IN(

                                      -- 'Basic Salary', 'Housing Allowance' )

                                      AND petf1.element_type_id =

                                          peef1.element_type_id

                                      AND sysdate BETWEEN

                                          peef1.effective_start_date

                                          AND

                                          peef1.effective_end_date

                                      AND peef1.person_id = papf.person_id)

                              Allowance,

                              (SELECT Sum(To_number(peevf1.screen_entry_value))

                               FROM   pay_element_entry_values_f peevf1,

                                      pay_input_values_tl pivt1,

                                      pay_input_values_f pivf1,

                                      pay_element_types_tl petf1,

                                      pay_element_entries_f peef1

                               WHERE  sysdate BETWEEN

                                      peevf1.effective_start_date AND

                                      peevf1.effective_end_date

                                      AND peevf1.input_value_id =

                                          pivf1.input_value_id

                                      AND peevf1.element_entry_id =

                                          peef1.element_entry_id

                                      --AND pivt1.NAME = 'Pay Value'

                                      AND pivt1.input_value_id =

                                          pivf1.input_value_id

                                      AND sysdate BETWEEN

                                          pivf1.effective_start_date

                                          AND

                                          pivf1.effective_end_date

                                      AND pivf1.element_type_id =

                                          petf1.element_type_id

                                      AND petf1.language = 'US'

                                      AND pivt1.language = 'US'

                                      AND petf1.element_name IN (

                                          'Employee GOSI Annuities',

                                          'Employee GOSI Saned' )

                                      AND petf1.element_type_id =

                                          peef1.element_type_id

                                      AND sysdate BETWEEN

                                          peef1.effective_start_date

                                          AND

                                          peef1.effective_end_date

                                      AND peef1.person_id = papf.person_id)

                              "Gosi Ded",

                              (SELECT Sum(dedec.result_value)

                               FROM   dedec

                               WHERE  1 = 1

                                      AND dedec.person_id = papf.person_id

                                      AND rownum <= 2)

                              Gosi_Ded_Other_deducion,

                              /*(SELECT DISTINCT

                              

                              prb.balance_value

                              

                              FROM

                              

                              per_periods_of_service     ppos,

                              per_all_assignments_f      paaf,

                              pay_payroll_assignments    ppasg,

                              pay_pay_relationships_dn   pprd,

                              pay_payroll_rel_actions    ppra,

                              pay_run_results            prr,

                              pay_run_result_values      prrv,

                              pay_input_values_f         pivf,

                              pay_balance_feeds_f        pbf,

                              pay_balance_types_vl       pbtv,

                              pay_payroll_actions        ppa,

                              pay_all_payrolls_f         prl,

                              pay_run_balances           prb,

                              pay_defined_balances       pdb,

                              pay_balance_dimensions     pbd

                              

                              WHERE 1=1

                              

                              AND ppos.person_id = papf.person_id

                              AND paaf.person_id = ppos.person_id

                              AND paaf.period_of_service_id = ppos.period_of_service_id

                              AND trunc(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date

                              AND paaf.assignment_id = ppasg.hr_assignment_id

                              AND paaf.work_terms_assignment_id = ppasg.hr_term_id

                              AND trunc(SYSDATE) BETWEEN ppasg.start_date AND ppasg.end_date

                              AND ppasg.payroll_relationship_id = pprd.payroll_relationship_id

                              AND trunc(SYSDATE) BETWEEN pprd.start_date AND pprd.end_date

                              AND ppra.payroll_relationship_id = pprd.payroll_relationship_id

                              AND ppra.payroll_relationship_id = ppasg.payroll_relationship_id

                              AND ppra.payroll_rel_action_id = prr.payroll_rel_action_id

                              AND prrv.run_result_id = prr.run_result_id

                              AND pivf.input_value_id = prrv.input_value_id

                              AND pbf.input_value_id = pivf.input_value_id

                              AND pbtv.balance_type_id = pbf.balance_type_id

                              AND pbf.input_value_id = pivf.input_value_id

                              AND ppra.payroll_action_id = ppa.payroll_action_id

                              AND prl.payroll_id = ppa.payroll_id

                              AND prb.payroll_relationship_id = ppra.payroll_relationship_id

                              AND prb.payroll_rel_action_id = ppra.payroll_rel_action_id

                              AND trunc(prb.effective_date) = trunc(ppa.effective_date)

                              AND pdb.defined_balance_id = prb.defined_balance_id

                              AND pdb.balance_type_id = pbtv.balance_type_id

                              AND pdb.balance_dimension_id = pbd.balance_dimension_id

                              --AND pbd.base_dimension_name = 'Core Relationship Tax Unit Run'

                              AND BASE_DB_ITEM_SUFFIX = '_ASG_RUN'

                              AND pbtv.balance_name = 'Net Pay'

                              AND ROWNUM <= 1

                              --AND trunc(PPA.effective_date) = to_date('04/30/2021','MM/DD/YYYY') 

                              ) Basicnet, */

                              papf.person_number,

                              dedec.legal_entity

        FROM   per_all_people_f papf,

               per_person_names_f ppnf,

               dedec

        WHERE  1 = 1

               --AND papf.person_number = '32'

               AND dedec.person_id = papf.person_id

               AND papf.person_id = ppnf.person_id

               AND ppnf.name_type = 'GLOBAL'

               AND sysdate BETWEEN papf.effective_start_date AND

                                   papf.effective_end_date

               AND sysdate BETWEEN ppnf.effective_start_date AND

                                   ppnf.effective_end_date

               --AND dedec.Legal_Entity = NVL(:p_legal_entity,dedec.Legal_Entity)

               --AND ((dedec.Legal_Entity) IN (:p_legal_entity) OR 'All' IN (:p_legal_entity|| 'All'))

               AND ( COALESCE(NULL, :p_legal_entity) IS NULL

                      OR dedec.legal_entity IN ( :p_legal_entity ) )

               AND To_char(dedec.end_date, 'MM-DD-YYYY') <= :P_pay_date) ban