SELECT sih.Company_Code,
       sih.System_Id,
       sih.Bulk_Suffix,
       sih.Inv_Number,
       sih.Inv_Status,
       sih.exl_Ledger,
       Row_number() OVER(PARTITION BY sih.Partner_Code ORDER BY sih.Partner_Code) seq,
       COUNT(* ) OVER(PARTITION BY Insil.Partner_Code ) Tot_Sub,
       sih.Partner_Code,
       sih.Partner_Sub_Code,
       sih.Inv_Date,
       sih.num_Lines,
       sih.Shipcomp_Code,
       sih.Department_Code,
       sih.Company_Code,
       sih.System_Id,
       sih.Inv_Number,
       sih.Inv_Status,
       sih.Inv_Number Our_ref,
       sih.Inv_Source Inv_Source,
       sih.exl_Ledger,
       sih.Currency t_Currency_Code,
       sih.Inv_Amount sih_Inv_Amount,
       sih.Base_Amount t_Base_Amount,
       sih.Base_Vat_Amt t_Base_Vat_Amt,
       sih.Partner_Code,
       sih.Partner_Sub_Code,
       sih.Inv_Type Doc_Type,
       DECODE(sih.Inv_Type,'I','D',
                           'C','C',
                           'C') Type_dc,
       DECODE(sih.Inv_Type,'C',Substr(sih.Orig_Inv_Number,1,12),
                           '') Match_ref,
       DECODE(sih.Inv_Type,'I',sih.Inv_Number
                               ||'01',
                           'C',Substr(sih.Orig_Inv_Number,1,12)
                               ||'02',
                           sih.Inv_Number
                           ||'01') comm_Inv_num,
       sih.Due_Date,
       sih.Voyage_Reference,
       sih.Inv_Date,
       sih.Department_Code,
       sih.Payment_Office,
       sih.Import_Export,
       sih.bol_Number,
       sih.Point_From,
       sih.Point_Load,
       sih.Point_Disch,
       sih.Visit_No,
       sih.Disch_Visit_No,
       sih.Point_To,
       sih.Service_No,
       DECODE(sih.Import_Export,'I',sih.eta_Date,
                                sih.etd_Date) Vessel_Date,
       sih.Application_Id t_Application_Id,
       sih.exch_Rate t_exch_Rate,
       sih.Vat_Amount_ag_Cur t_Vat_Amount_ag_Cur,
       sih.Agent_Currency t_Agent_Currency,
       sih.Carrier_Number t_Carrier_Number,
       sih.Addr_Number t_Addr_Number,
       sih.Address_Type t_Address_Type,
       sih.Inv_Sub_Type t_Inv_Sub_Type,
       sih.Inv_Number_Group t_Inv_Number_Group,
       sih.Local_Voyage_Reference t_Local_Voyage_Reference,
       sih.Inv_Local_Payment_ref t_Inv_Local_Payment_ref,
       sih.LogOn_Id,
       sih.bol_Type,
       sih.Consignee_Code,
       sih.Notify_Code,
       sih.decIdIng_Party_Code,
       sih.BookIng_Party_Code,
       sih.Invoice_Mode_Code,
       sih.Insih_Currency_Roe_Type Insih_ccy_Roe_Type,
       sih.Insih_Currency_Roe_Date Insih_ccy_Roe_Date,
       Company.Vat_No,
       Company.Currency_Code Base_Currency,
       Insil.Currency,
       Insil.Vat_Code,
       Insil.Vat_Amount,
       Insil.Vat_Rate,
       Insil.Vat_Amount_ag_Cur,
       Insil.Inv_Amount,
       Insil.Charge_Code,
       Insil.Carrier_Number,
       Insil.Currency_Amount,
       Insil.chg_Rate,
       Insil.chg_Quantity,
       Insil.chg_Location,
       Insil.Basic_exch_Rate,
       Insil.Invoice_Charge_Group,
       Insih.Partner_Code,
       Insih.Partner_Sub_Code,
       Insih.Inv_Number,
       Insil.Line_Number,
       Insih.Shipcomp_Code,
       Insil.calc_Type,
       Insil.Payment_Method,
       Insih.Currency,
       Insih.Department_Code
FROM   Sales_Inv_Headers sih,
       Company,
       (SELECT Insil.Currency,
               Insil.Vat_Code,
               Insih.Partner_Code Insil.Vat_Amount,
               Insil.Vat_Rate,
               Insil.Vat_Amount_ag_Cur,
               Insil.Inv_Amount,
               Insil.Charge_Code,
               Insil.Carrier_Number,
               Insil.Currency_Amount,
               Insil.chg_Rate,
               Insil.chg_Quantity,
               Insil.chg_Location,
               Insil.Basic_exch_Rate,
               Insil.Invoice_Charge_Group,
               Insih.Partner_Code,
               Insih.Partner_Sub_Code,
               Insih.Inv_Number,
               Insil.Line_Number,
               Insih.Shipcomp_Code,
               Insil.calc_Type,
               Insil.Payment_Method,
               Insih.Currency,
               Insih.Department_Code
        FROM   Sales_Inv_Lines Insil,
               Sales_Inv_Headers Insih
        WHERE  Insil.Inv_Number = Insih.Inv_Number
               AND Insil.Company_Code = Insih.Company_Code
               AND Insil.Bulk_Suffix = Insih.Bulk_Suffix
               AND Insil.System_Id = Insih.System_Id
               AND Insil.Company_Code = Insih.Company_Code) Insil
WHERE  Insih.Company_Code = :p_code
       AND Insih.Inv_Status = :p_status
                              --validation of agents and partners
       AND ((Insih.Partner_Code IN (SELECT edMes.Partner_Code
                                    FROM   edi_Message edMes
                                    WHERE  edMes.Company_Code = :p_code
                                           AND edMes.Application = :p_application_1)
              OR Insih.Partner_Code IN (SELECT edgrp.Group_Partner_Code
                                        FROM   edi_Partner_Group_Details edgrp
                                        WHERE  edgrp.Partner_Code IN (SELECT Partner_Code
                                                                      FROM   edi_Message edMes
                                                                      WHERE  edMes.Company_Code = :p_code
                                                                             AND edMes.Application = :p_application_1)))
             OR Insih.Department_Code IN (SELECT dept.Department_Code
                                          FROM   Departments dept
                                          WHERE  dept.Company_Code = :p_code
                                                 AND dept.Partner_Code IN (SELECT Partner_Code
                                                                           FROM   edi_Message edMes
                                                                           WHERE  edMes.Company_Code = :p_code
                                                                                  AND edMes.Application = :P_application)))
       AND Insih.Inv_Number BETWEEN :p_from_number
                                    AND :p_to_number
       AND (Insih.Inv_Date >= :p_to_date
            AND Insih.Inv_Date < :p_from_date);

SELECT *
       Row_number() OVER(PARTITION BY sih.Partner_Code ORDER BY sih.Partner_Code) seq,
       COUNT(* ) OVER(PARTITION BY Insil.Partner_Code ) Tot_Sub
FROM   Sales_Inv_Headers sih,
       Company,
       (SELECT *
        FROM   Sales_Inv_Lines Insil,
               Sales_Inv_Headers Insih
        WHERE  Insil.Inv_Number = Insih.Inv_Number
               AND Insil.Company_Code = Insih.Company_Code
               AND Insil.Bulk_Suffix = Insih.Bulk_Suffix
               AND Insil.System_Id = Insih.System_Id
               AND Insil.Company_Code = Insih.Company_Code) Insil
WHERE Insih.Company_Code = :p_code
AND Insih.Inv_Status = :p_status
AND Insih.Inv_Number BETWEEN :p_from_number AND :p_to_number
AND Insih.Inv_Date >= :p_to_date
AND Insih.Inv_Date < :p_from_date
AND (
        (Insih.Partner_Code IN (SELECT edMes.Partner_Code
                                FROM   edi_Message edMes
                                WHERE  edMes.Company_Code = :p_code
                                       AND edMes.Application = :p_application_1
                               )
              OR Insih.Partner_Code IN (SELECT edgrp.Group_Partner_Code
                                        FROM   edi_Partner_Group_Details edgrp
                                        WHERE  edgrp.Partner_Code IN (SELECT Partner_Code
                                                                      FROM   edi_Message edMes
                                                                      WHERE  edMes.Company_Code = :p_code
                                                                             AND edMes.Application = :p_application_1
                                                                     )
                                       )
         )
             OR Insih.Department_Code IN (SELECT dept.Department_Code
                                          FROM   Departments dept
                                          WHERE  dept.Company_Code = :p_code
                                                 AND dept.Partner_Code IN (SELECT Partner_Code
                                                                           FROM   edi_Message edMes
                                                                           WHERE  edMes.Company_Code = :p_code
                                                                                  AND edMes.Application = :P_application
                                                                          )
                                         )
    )