AP to GL Link with different Sub ledger source

SELECT-------------------------------------------------------------------------------------------------------------------------------
         -->>  SQL1 : je_category = AP Purchase Invoices
         -------------------------------------------------------------------------------------------------------------------------------
         -- SL Fields
         'Payables' ae_source,
         xah.je_category_name ae_category_name,
         gsob.NAME ledger_name,
         hou.NAME operating_unit,
         gsob.currency_code ledger_currency_code,
         gcc.concatenated_segments gl_account,
         gcc.code_combination_id code_combination_id,                                                    ,
         xah.period_name ap_period_name,
         pv.vendor_name supplier,
         ai.invoice_num ap_invoice_num,
         ai.invoice_amount ap_invoice_amt,
         ai.invoice_date ap_invoice_date,
         ai.invoice_id ap_invoice_id,
         xal.ae_line_num ap_invoice_dist_line_num,
         NULL ap_check_num,                                   
         NULL ap_check_amt,                                   
         NULL ap_check_date,                                  
         NULL ap_check_id,                                    
         NULL ap_check_dist_line_num,                         
         TRUNC (xah.accounting_date) ap_gl_date,
         xal.accounting_class_code ap_line_type_lookup_code,
         xdl.unrounded_entered_dr sla_entered_dr,
         xdl.unrounded_entered_cr sla_entered_cr,
         xdl.unrounded_accounted_dr sla_accounted_dr,
         xdl.unrounded_accounted_cr sla_accounted_cr,
         NVL (xdl.unrounded_entered_dr, 0)
         - NVL (xdl.unrounded_entered_cr, 0)
            sla_net_entered_amt,
         NVL (xdl.unrounded_accounted_dr, 0)
         - NVL (xdl.unrounded_accounted_cr, 0)
            sla_net_accounted_amt,
         xah.gl_transfer_status_code ap_gl_transfer_flag        
                                                        ,
         xal.gl_sl_link_id gl_sl_link_id,
         xah.ae_header_id,
         jl.entered_dr gl_entered_dr,
         jl.entered_cr gl_entered_cr,
         jl.accounted_dr gl_accounted_dr,
         jl.accounted_cr gl_accounted_cr,
         NVL (jl.entered_dr, 0) - NVL (jl.entered_cr, 0) gl_net_entered_amt,
         NVL (jl.accounted_dr, 0) - NVL (jl.accounted_cr, 0)
            gl_net_accounted_amt,
         DECODE (
            gir.gl_sl_link_id,
            NULL,
            0,
            NVL (xdl.unrounded_entered_dr, 0)
            - NVL (xdl.unrounded_entered_cr, 0)
         )
            sla_gl_net_entered_amt,
         DECODE (
            gir.gl_sl_link_id,
            NULL,
            0,
            NVL (xdl.unrounded_accounted_dr, 0)
            - NVL (xdl.unrounded_accounted_cr, 0)
         )
            sla_gl_net_accounted_amt ,                          
         jb.NAME batch_name,
         jb.description batch_description,
         jh.NAME je_name,
         jh.description je_description,
         jh.posted_date je_posted_date,
         jl.je_line_num je_line_num,
         jh.je_header_id,
         jb.je_batch_id,
         DECODE (
            jh.status,
            'P',
            'Posted',
            'U',
            'Unposted',
            'F',
            'Error7 - Showing invalid journal entry lines or no journal entry lines',
            'K',
            'Error10 - Showing unbalanced intercompany journal entry',
            'Z',
            'Error7 - Showing invalid journal entry lines or no journal entry lines',
            'Unknown'
         )
            je_status,
         DECODE (jh.actual_flag,
                 'A',
                 'Actual',
                 'B',
                 'Budget',
                 'E',
                 'Encumbrance')
            je_type                                       
                   ,
         DECODE (
            xah.gl_transfer_status_code,
            'Y',
            DECODE (gir.gl_sl_link_id,
                    NULL, 'Transferred',
                    DECODE (jh.status, 'P', 'Posted', 'Imported')),
            'N',
            'Not-Transferred',
            'Not-Transferred'
         )
            reconcile_status,
         gsob.ledger_id,
         aid.invoice_line_number,
         aid.distribution_line_number,
         pv.vendor_id vendor_id,
         pvs.vendor_site_id,
         xdl.application_id,
         xdl.ref_ae_header_id,
         xdl.temp_line_num,
         xal.ae_line_num,
         xal.application_id xal_application_id,
         xah.application_id xah_application_id,
         xe.event_id,
         hou.organization_id hou_organization_id,
         ai.invoice_id invoice_id
  FROM   ap_invoices_all ai,
         ap_invoice_distributions_all aid,
         po_vendors pv,
         po_vendor_sites_all pvs,
         xla_distribution_links xdl,
         xla_ae_lines xal,
         xla_ae_headers xah,
         xla_events xe,
         gl_code_combinations_kfv gcc,
         gl_ledgers gsob,
         gl_import_references gir,
         gl_je_lines jl,
         gl_je_headers jh,
         gl_je_batches jb,
         gl_je_sources jes,
         gl_je_categories jec,
         hr_operating_units hou
 WHERE       1 = 1
         -- SL Joins
         AND ai.invoice_id = aid.invoice_id
         AND pv.vendor_id = ai.vendor_id
         AND pvs.vendor_site_id = ai.vendor_site_id
         -- SLA - SL joins
         AND xdl.ae_header_id = xal.ae_header_id(+)
         AND Xdl.Ae_Line_Num = Xal.Ae_Line_Num(+)
         AND Xal.Application_Id(+) = 200
         AND Xal.Ae_Header_Id = Xah.Ae_Header_Id(+)
         AND Xah.Event_Id = Xe.Event_Id(+)
         AND Aid.Invoice_Distribution_Id =
               Xdl.Source_Distribution_Id_Num_1(+)
         AND Xdl.Source_Distribution_Type(+) = 'AP_INV_DIST'
         AND Xal.Code_Combination_Id = Gcc.Code_Combination_Id(+)
         AND xah.ledger_id = gsob.ledger_id(+)
         -- SLA : GIR Joins
         AND Xal.Gl_Sl_Link_Id = Gir.Gl_Sl_Link_Id(+)
         AND xal.gl_sl_link_table = gir.gl_sl_link_table(+)
         -- GL Joins
         AND Gir.Je_Header_Id = Jl.Je_Header_Id(+)
         AND Gir.Je_Line_Num = Jl.Je_Line_Num(+)
         AND Jl.Je_Header_Id = Jh.Je_Header_Id(+)
         AND Jh.Je_Batch_Id = Jb.Je_Batch_Id(+)
         AND Jh.Je_Source = Jes.Je_Source_Name(+)
         AND jh.je_category = jec.je_category_name(+)
         AND ai.org_id = hou.organization_id 
--         and ai.invoice_num ='MWS11063'   
-------------------------------------------------------------------------------------------------------------------------------
-->>  SQL2 : Matched pre-payment distributions
-------------------------------------------------------------------------------------------------------------------------------
UNION ALL
SELECT                                                           
      'Payables' ae_source,
         xah.je_category_name ae_category_name,
         gsob.NAME ledger_name,
         hou.NAME operating_unit,
         gsob.currency_code ledger_currency_code,
         gcc.concatenated_segments gl_account,
         gcc.code_combination_id code_combination_id
                                                    ,
         xah.period_name ap_period_name,
         pv.vendor_name supplier,
         ai.invoice_num ap_invoice_num,
         ai.invoice_amount ap_invoice_amt,
         ai.invoice_date ap_invoice_date,
         ai.invoice_id ap_invoice_id,
         xal.ae_line_num ap_invoice_dist_line_num,
         NULL ap_check_num,                                   
         NULL ap_check_amt,                                    
         NULL ap_check_date,                                    
         NULL ap_check_id,                                      
         NULL ap_check_dist_line_num,                           
         TRUNC (xah.accounting_date) ap_gl_date,
         xal.accounting_class_code ap_line_type_lookup_code,
         xdl.unrounded_entered_dr sla_entered_dr,
         xdl.unrounded_entered_cr sla_entered_cr,
         xdl.unrounded_accounted_dr sla_accounted_dr,
         xdl.unrounded_accounted_cr sla_accounted_cr,
         NVL (xdl.unrounded_entered_dr, 0)
         - NVL (xdl.unrounded_entered_cr, 0)
            sla_net_entered_amt,
         NVL (xdl.unrounded_accounted_dr, 0)
         - NVL (xdl.unrounded_accounted_cr, 0)
            sla_net_accounted_amt,
         xah.gl_transfer_status_code ap_gl_transfer_flag          
                                                        ,
         xal.gl_sl_link_id gl_sl_link_id,
         xah.ae_header_id,
         jl.entered_dr gl_entered_dr,
         jl.entered_cr gl_entered_cr,
         jl.accounted_dr gl_accounted_dr,
         jl.accounted_cr gl_accounted_cr,
         NVL (jl.entered_dr, 0) - NVL (jl.entered_cr, 0) gl_net_entered_amt,
         NVL (jl.accounted_dr, 0) - NVL (jl.accounted_cr, 0)
            gl_net_accounted_amt
                                ,
         DECODE (
            gir.gl_sl_link_id,
            NULL,
            0,
            NVL (xdl.unrounded_entered_dr, 0)
            - NVL (xdl.unrounded_entered_cr, 0)
         )
            sla_gl_net_entered_amt,
         DECODE (
            gir.gl_sl_link_id,
            NULL,
            0,
            NVL (xdl.unrounded_accounted_dr, 0)
            - NVL (xdl.unrounded_accounted_cr, 0)
         )
            sla_gl_net_accounted_amt                           
                                    ,
         jb.NAME batch_name,
         jb.description batch_description,
         jh.NAME je_name,
         jh.description je_description,
         jh.posted_date je_posted_date,
         jl.je_line_num je_line_num,
         jh.je_header_id,
         jb.je_batch_id,
         DECODE (
            jh.status,
            'P',
            'Posted',
            'U',
            'Unposted',
            'F',
            'Error7 - Showing invalid journal entry lines or no journal entry lines',
            'K',
            'Error10 - Showing unbalanced intercompany journal entry',
            'Z',
            'Error7 - Showing invalid journal entry lines or no journal entry lines',
            'Unknown'
         )
            je_status,
         DECODE (jh.actual_flag,
                 'A',
                 'Actual',
                 'B',
                 'Budget',
                 'E',
                 'Encumbrance')
            je_type                                       
                   ,
         DECODE (
            xah.gl_transfer_status_code,
            'Y',
            DECODE (gir.gl_sl_link_id,
                    NULL, 'Transferred',
                    DECODE (jh.status, 'P', 'Posted', 'Imported')),
            'N',
            'Not-Transferred',
            'Not-Transferred'
         )
            reconcile_status,
         gsob.ledger_id,
         aid.invoice_line_number,
         aid.distribution_line_number,
         pv.vendor_id vendor_id,
         pvs.vendor_site_id,
         xdl.application_id,
         xdl.ref_ae_header_id,
         xdl.temp_line_num,
         xal.ae_line_num,
         xal.application_id xal_application_id,
         xah.application_id xah_application_id,
         xe.event_id,
         hou.organization_id hou_organization_id,
         ai.invoice_id invoice_id
  FROM   ap_invoices_all ai,
         ap_invoice_distributions_all aid,
         ap_prepay_app_dists apad,
         po_vendors pv,
         po_vendor_sites_all pvs,
         xla_distribution_links xdl,
         xla_ae_lines xal,
         xla_ae_headers xah,
         xla_events xe,
         gl_code_combinations_kfv gcc,
         gl_ledgers gsob,
         gl_import_references gir,
         gl_je_lines jl,
         gl_je_headers jh,
         gl_je_batches jb,
         gl_je_sources jes,
         gl_je_categories jec,
         hr_operating_units hou
 WHERE       1 = 1
         -- SL Joins
         AND ai.invoice_id = aid.invoice_id
         AND apad.invoice_distribution_id = aid.invoice_distribution_id
         AND pv.vendor_id = ai.vendor_id
         AND pvs.vendor_site_id = ai.vendor_site_id
         -- SLA - SL joins
         AND Xdl.Ae_Header_Id = Xal.Ae_Header_Id(+)
         AND Xdl.Ae_Line_Num = Xal.Ae_Line_Num(+)
         AND Xal.Application_Id(+) = 200
         AND Xal.Ae_Header_Id = Xah.Ae_Header_Id(+)
         AND Xah.Event_Id = Xe.Event_Id(+)
         AND Apad.Prepay_App_Dist_Id = Xdl.Source_Distribution_Id_Num_1(+)
         AND Xdl.Source_Distribution_Type(+) = 'AP_PREPAY'
         AND xal.code_combination_id = gcc.code_combination_id(+)
         AND xah.ledger_id = gsob.ledger_id(+)
         -- SLA : GIR Joins
         AND Xal.Gl_Sl_Link_Id = Gir.Gl_Sl_Link_Id(+)
         AND xal.gl_sl_link_table = gir.gl_sl_link_table(+)
         -- GL Joins
         AND gir.je_header_id = jl.je_header_id(+)
         AND Gir.Je_Line_Num = Jl.Je_Line_Num(+)
         AND Jl.Je_Header_Id = Jh.Je_Header_Id(+)
         AND Jh.Je_Batch_Id = Jb.Je_Batch_Id(+)
         AND Jh.Je_Source = Jes.Je_Source_Name(+)
         AND jh.je_category = jec.je_category_name(+)
         AND ai.org_id = hou.organization_id
-------------------------------------------------------------------------------------------------------------------------------
-->>  SQL3 : je_category = AP Payments + Reconciled Payments
-------------------------------------------------------------------------------------------------------------------------------
UNION ALL
SELECT                                                           
      'Payables' ae_source,
         xah.je_category_name ae_category_name,
         gsob.NAME ledger_name,
         hou.NAME operating_unit,
         gsob.currency_code ledger_currency_code,
         gcc.concatenated_segments gl_account,
         gcc.code_combination_id code_combination_id
                                                    ,
         xah.period_name ap_period_name,
         ac.vendor_name supplier,
         ai.invoice_num ap_invoice_num,                       
         ai.invoice_amount ap_invoice_amt,                    
         ai.invoice_date ap_invoice_date,                     
         ai.invoice_id ap_invoice_id,                         
         NULL ap_invoice_dist_line_num,                       
         ac.check_number ap_check_num                         
                                     ,
         ac.amount ap_check_amt                                  
                               ,
         ac.check_date ap_check_date,                   
         ac.check_id ap_check_id,                                 
         xal.ae_line_num ap_check_dist_line_num,                     
         TRUNC (xah.accounting_date) ap_gl_date,                     
         xal.accounting_class_code ap_line_type_lookup_code,
         xdl.unrounded_entered_dr sla_entered_dr,
         xdl.unrounded_entered_cr sla_entered_cr,
         xdl.unrounded_accounted_dr sla_accounted_dr,
         xdl.unrounded_accounted_cr sla_accounted_cr,
         NVL (xdl.unrounded_entered_dr, 0)
         - NVL (xdl.unrounded_entered_cr, 0)
            sla_net_entered_amt,
         NVL (xdl.unrounded_accounted_dr, 0)
         - NVL (xdl.unrounded_accounted_cr, 0)
            sla_net_accounted_amt,
         xah.gl_transfer_status_code ap_gl_transfer_flag        
                                                        ,
         xal.gl_sl_link_id gl_sl_link_id,
         xah.ae_header_id,
         jl.entered_dr gl_entered_dr,
         jl.entered_cr gl_entered_cr,
         jl.accounted_dr gl_accounted_dr,
         jl.accounted_cr gl_accounted_cr,
         NVL (jl.entered_dr, 0) - NVL (jl.entered_cr, 0) gl_net_entered_amt,
         NVL (jl.accounted_dr, 0) - NVL (jl.accounted_cr, 0)
            gl_net_accounted_amt
                                ,
         DECODE (
            gir.gl_sl_link_id,
            NULL,
            0,
            NVL (xdl.unrounded_entered_dr, 0)
            - NVL (xdl.unrounded_entered_cr, 0)
         )
            sla_gl_net_entered_amt,
         DECODE (
            gir.gl_sl_link_id,
            NULL,
            0,
            NVL (xdl.unrounded_accounted_dr, 0)
            - NVL (xdl.unrounded_accounted_cr, 0)
         )
            sla_gl_net_accounted_amt                           
                                    ,
         jb.NAME batch_name,
         jb.description batch_description,
         jh.NAME je_name,
         jh.description je_description,
         jh.posted_date je_posted_date,
         jl.je_line_num je_line_num,
         jh.je_header_id,
         jb.je_batch_id,
         DECODE (
            jh.status,
            'P',
            'Posted',
            'U',
            'Unposted',
            'F',
            'Error7 - Showing invalid journal entry lines or no journal entry lines',
            'K',
            'Error10 - Showing unbalanced intercompany journal entry',
            'Z',
            'Error7 - Showing invalid journal entry lines or no journal entry lines',
            'Unknown'
         )
            je_status,
         DECODE (jh.actual_flag,
                 'A',
                 'Actual',
                 'B',
                 'Budget',
                 'E',
                 'Encumbrance')
            je_type                                       
                   ,
         DECODE (
            xah.gl_transfer_status_code,
            'Y',
            DECODE (gir.gl_sl_link_id,
                    NULL, 'Transferred',
                    DECODE (jh.status, 'P', 'Posted', 'Imported')),
            'N',
            'Not-Transferred',
            'Not-Transferred'
         )
            reconcile_status,
         gsob.ledger_id,
         NULL invoice_line_number,
         NULL distribution_line_number,
         NULL vendor_id,
         NULL vendor_site_id,
         xdl.application_id,
         xdl.ref_ae_header_id,
         xdl.temp_line_num,
         xal.ae_line_num,
         xal.application_id xal_application_id,
         xah.application_id xah_application_id,
         NULL event_id,
         hou.organization_id hou_organization_id,
         ai.invoice_id invoice_id
  FROM   ap_payment_hist_dists aphd,
         ap_invoice_payments_all aip,
         ap_invoices_all ai,
         ap_checks_all ac,
         po_vendors pov,
         xla_distribution_links xdl,
         xla_ae_headers xah,
         xla_ae_lines xal,
         gl_code_combinations_kfv gcc,
         gl_ledgers gsob,
         gl_import_references gir,
         gl_je_lines jl,
         gl_je_headers jh,
         gl_je_batches jb,
         gl_je_sources jes,
         gl_je_categories jec,
         hr_operating_units hou
 WHERE                                                            
      aphd   .invoice_payment_id = aip.invoice_payment_id
         AND ai.invoice_id = aip.invoice_id
         AND ac.check_id = aip.check_id
         AND Pov.Vendor_Id = Ac.Vendor_Id
         AND Xal.Ae_Header_Id = Xah.Ae_Header_Id(+)
         AND Xal.Ae_Header_Id(+) = Xdl.Ae_Header_Id
         AND Xal.Ae_Line_Num(+) = Xdl.Ae_Line_Num
         AND Xdl.Source_Distribution_Id_Num_1(+) = Aphd.Payment_Hist_Dist_Id
         AND Xdl.Source_Distribution_Type(+) = 'AP_PMT_DIST'
         AND Xah.Ledger_Id = Gsob.Ledger_Id(+)
         AND xal.code_combination_id = gcc.code_combination_id(+)
         -- SLA : GIR Joins
         AND Xal.Gl_Sl_Link_Id = Gir.Gl_Sl_Link_Id(+)
         AND xal.gl_sl_link_table = gir.gl_sl_link_table(+)
         -- GL Joins
         AND Gir.Je_Header_Id = Jl.Je_Header_Id(+)
         AND Gir.Je_Line_Num = Jl.Je_Line_Num(+)
         AND Jl.Je_Header_Id = Jh.Je_Header_Id(+)
         AND jh.je_batch_id = jb.je_batch_id(+)
         AND Jh.Je_Source = Jes.Je_Source_Name(+)
         AND jh.je_category = jec.je_category_name(+)
         AND aip.org_id = hou.organization_id
        -- and ai.invoice_num ='MWS11063'