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);