SLA常用追溯子模块SQL

1.1.SLA与AP_INVOICE

  
  
  

   
Select Aia.Org_Id,
   
       Aia.Invoice_Id,
   
       Aia.Invoice_Num,
   
       Aila.Line_Number Invoice_Line_Number,
   
       Aida.Distribution_Line_Number,
   
       Aida.Invoice_Distribution_Id,
   
       Aida.Line_Type_Lookup_Code,
   
       Xte.Application_Id,
   
       Xte.Entity_Id,
   
       Xe.Event_Id,
   
       Xah.Ae_Header_Id,
   
       Xal.Ae_Line_Num,
   
       Xal.Displayed_Line_Number,
   
       Xdl.Source_Distribution_Type,
   
       Xdl.Accounting_Line_Code,
   
       Xdl.Unrounded_Accounted_Dr,
   
       Xdl.Unrounded_Accounted_Cr,
   
       Gjh.Name,
   
       Gjl.Je_Line_Num
   
  From Ap_Invoices_All              Aia,
   
       Ap_Invoice_Lines_All         Aila,
   
       Ap_Invoice_Distributions_All Aida,
   
       Xla.Xla_Transaction_Entities Xte,
   
       Xla_Events                   Xe,
   
       Xla_Ae_Headers               Xah,
   
       Xla_Ae_Lines                 Xal,
   
       Xla_Distribution_Links       Xdl,
   
       
   
       Gl_Import_References Gir,
   
       Gl_Je_Headers        Gjh,
   
       Gl_Je_Lines          Gjl
   
 Where Aia.Invoice_Id = Aila.Invoice_Id
   
      
   
   And Aila.Invoice_Id = Aida.Invoice_Id
   
   And Aila.Line_Number = Aida.Invoice_Line_Number
   
      
   
   And Xte.Ledger_Id = Aia.Set_Of_Books_Id
   
   And Xte.Entity_Code = 'AP_INVOICES'
   
   And Nvl(Xte.Source_Id_Int_1, -99) = Aia.Invoice_Id
   
      
   
   And Xte.Application_Id = Xe.Application_Id
   
   And Xte.Entity_Id = Xe.Entity_Id
   
      
   
   And Xe.Application_Id = Xah.Application_Id
   
   And Xe.Event_Id = Xah.Event_Id
   
      
   
   And Xe.Event_Type_Code Not In ('PREPAYMENT APPLIED', 'PREPAYMENT UNAPPLIED')
   
      
   
   And Xah.Application_Id = Xal.Application_Id
   
   And Xah.Ae_Header_Id = Xal.Ae_Header_Id
   
      
   
   And Xal.Application_Id = Xdl.Application_Id
   
   And Xal.Ae_Header_Id = Xdl.Ae_Header_Id
   
   And Xal.Ae_Line_Num = Xdl.Ae_Line_Num
   
      
   
   And Xdl.Source_Distribution_Type = 'AP_INV_DIST'
   
   And Xdl.Source_Distribution_Id_Num_1 = Aida.Invoice_Distribution_Id
   
      
   
   And Xal.Gl_Sl_Link_Table = Gir.Gl_Sl_Link_Table
   
   And Xal.Gl_Sl_Link_Id = Gir.Gl_Sl_Link_Id
   
      
   
   And Gir.Je_Header_Id = Gjl.Je_Header_Id
   
   And Gir.Je_Line_Num = Gjl.Je_Line_Num
   
      
   
   And Gjh.Je_Header_Id = Gjl.Je_Header_Id
   
      
   
   And Aia.Invoice_Num = '02764984'
   
   And Aida.Distribution_Line_Number = 1
   

   
 Order By Aia.Invoice_Num, Aida.Invoice_Line_Number, Aida.Distribution_Line_Number;
  

预付款核销：

   
   
   

    
Select Aia.Org_Id,
    
       Aia.Invoice_Id,
    
       Aia.Invoice_Num,
    
       Aila.Line_Number Invoice_Line_Number,
    
       Aida.Distribution_Line_Number,
    
       Aida.Invoice_Distribution_Id,
    
       Aida.Line_Type_Lookup_Code,
    
       Xte.Application_Id,
    
       Xte.Entity_Id,
    
       Xe.Event_Id,
    
       Xah.Ae_Header_Id,
    
       Xal.Ae_Line_Num,
    
       Xal.Displayed_Line_Number,
    
       Xdl.Source_Distribution_Type,
    
       Xdl.Accounting_Line_Code,
    
       Xdl.Unrounded_Accounted_Dr,
    
       Xdl.Unrounded_Accounted_Cr,
    
       Gjh.Name,
    
       Gjl.Je_Line_Num
    
  From Ap_Invoices_All              Aia,
    
       Ap_Invoice_Lines_All         Aila,
    
       Ap_Invoice_Distributions_All Aida,
    
       Ap_Prepay_App_Dists          Apad,
    
       Xla.Xla_Transaction_Entities Xte,
    
       Xla_Events                   Xe,
    
       Xla_Ae_Headers               Xah,
    
       Xla_Ae_Lines                 Xal,
    
       Xla_Distribution_Links       Xdl,
    
       
    
       Gl_Import_References Gir,
    
       Gl_Je_Headers        Gjh,
    
       Gl_Je_Lines          Gjl
    
 Where Aia.Invoice_Id = Aila.Invoice_Id
    
      
    
   And Aila.Invoice_Id = Aida.Invoice_Id
    
   And Aila.Line_Number = Aida.Invoice_Line_Number
    
      
    
   And Aida.Invoice_Distribution_Id = Apad.Invoice_Distribution_Id
    
      
    
   And Xte.Ledger_Id = Aia.Set_Of_Books_Id
    
   And Xte.Entity_Code = 'AP_INVOICES'
    
   And Nvl(Xte.Source_Id_Int_1, -99) = Aia.Invoice_Id
    
      
    
   And Xte.Application_Id = Xe.Application_Id
    
   And Xte.Entity_Id = Xe.Entity_Id
    
      
    
   And Xe.Application_Id = Xah.Application_Id
    
   And Xe.Event_Id = Xah.Event_Id
    
      
    
   And Xe.Event_Type_Code In ('PREPAYMENT APPLIED', 'PREPAYMENT UNAPPLIED')
    
      
    
   And Xah.Application_Id = Xal.Application_Id
    
   And Xah.Ae_Header_Id = Xal.Ae_Header_Id
    
      
    
   And Xal.Application_Id = Xdl.Application_Id
    
   And Xal.Ae_Header_Id = Xdl.Ae_Header_Id
    
   And Xal.Ae_Line_Num = Xdl.Ae_Line_Num
    
      
    
   And Xdl.Source_Distribution_Type = 'AP_PREPAY'
    
   And Xdl.Source_Distribution_Id_Num_1 = Apad.Prepay_App_Dist_Id
    
      
    
   And Xal.Gl_Sl_Link_Table = Gir.Gl_Sl_Link_Table
    
   And Xal.Gl_Sl_Link_Id = Gir.Gl_Sl_Link_Id
    
      
    
   And Gir.Je_Header_Id = Gjl.Je_Header_Id
    
   And Gir.Je_Line_Num = Gjl.Je_Line_Num
    
      
    
   And Gjh.Je_Header_Id = Gjl.Je_Header_Id
    
      
    
   And Aia.Invoice_Num = '06532493'
    

    
 Order By Aia.Invoice_Num, Aida.Invoice_Line_Number, Aida.Distribution_Line_Number;
   

1.1.SLA与AP_PAYMENT

     
     
     

      
Select Ac.Org_Id,
      
       Ac.Check_Id,
      
       Ac.Check_Number,
      
       Apha.Payment_History_Id,
      
       Aphd.Payment_Hist_Dist_Id,
      
       Aphd.Pay_Dist_Lookup_Code,
      
       Xte.Application_Id,
      
       Xte.Entity_Id,
      
       Xe.Event_Id,
      
       Xah.Ae_Header_Id,
      
       Xal.Ae_Line_Num,
      
       Xal.Displayed_Line_Number,
      
       Xdl.Source_Distribution_Type,
      
       Xdl.Accounting_Line_Code,
      
       Xdl.Unrounded_Accounted_Dr,
      
       Xdl.Unrounded_Accounted_Cr,
      
       Gjh.Name,
      
       Gjl.Je_Line_Num
      
  From Ap_Checks_All                Ac,
      
       Ap_System_Parameters_All     Aspa,
      
       Ap_Payment_History_All       Apha,
      
       Ap_Payment_Hist_Dists        Aphd,
      
       Ap_Invoice_Distributions_All Aida,
      
       Xla.Xla_Transaction_Entities Xte,
      
       Xla_Events                   Xe,
      
       Xla_Ae_Headers               Xah,
      
       Xla_Ae_Lines                 Xal,
      
       Xla_Distribution_Links       Xdl,
      
       
      
       Gl_Import_References Gir,
      
       Gl_Je_Headers        Gjh,
      
       Gl_Je_Lines          Gjl
      
 Where Ac.Org_Id = Aspa.Org_Id
      
      
      
   And Ac.Check_Id = Apha.Check_Id
      
   And Apha.Payment_History_Id = Aphd.Payment_History_Id
      
   And Aphd.Invoice_Distribution_Id = Aida.Invoice_Distribution_Id
      
      
      
   And Xte.