Query AF

SELECT DISTINCT fcfi.sfactura,
       LTRIM(TO_CHAR(fcfi.caea_ptovta,'0000'))||'-'||
                fcfi.ctiplet ||'-'||
                LTRIM(TO_CHAR(fcfi.nfactura,'00000000')) nro_fac,
        fcfi.fhemision,
        fcfi.dnomyap cliente,
        LTRIM(TO_CHAR(fcfi.scli_responsable,'0000000000')) nro_Cliente,
        fcfi.ddomic domicilio,
        fcfi.dloca localidad,
        fcfi.cpostal,
    ctdc.dtpdocu,
        fcfi.ncuit,
    fcfi.ndoc,
        fcfi.dnomyap_despachante despachante,
        fcfi.ctpcomprobante,
        fcfi.cconvta,
        fcfi.nfactura,
        ccli.dnomyap,
    civa.d_iva,
    (NVL(fcfi.ineto,0)+NVL(fcfi.iib,0)-NVL(fcfi.ilibib,0)+NVL(fcfi.iiva,0)-NVL(fcfi.ilibiva,0)+NVL(fcfi.iivaper,0)-NVL(fcfi.ilibivaper,0)+NVL(fcfi.iivanoins,0))
    total,
    fcfi.cconvta,
    sddt.nanio||'-'||LTRIM(TO_CHAR(sddt.caduana,'0000'))||'-'||sddt.cdctodestinacion||'-'||LTRIM(TO_CHAR(sddt.ndcto,'000000'))||'-'||sddt.dletra doc_aduana,
    stcg.dtpcarga,
    stin.dtipoingreso,
    sgui.cprefijo||'-'||sgui.nguia guia,
    sddt.qbuldest bul_des,
    sddt.qpesdest pes_des,
      LTRIM(TO_CHAR(fcfi.caea_ptovta,'0000'))||'-'||
     fcfi.ctiplet ||'-'||
      DECODE(fcfi.ctpcomprobante, 'FA', ( LTRIM(TO_CHAR((SELECT remito.nfactura FROM fcfi remito WHERE remito.sfactura_padre = fcfi.sfactura),'00000000'))),   LTRIM(TO_CHAR((fcfi.nfactura ),'00000000'))) NRO_REMITO
FROM    CLI_CLIENTES ccli,
        FAC_CABECERAS_FACTURAS_INTERNA fcfi,
    CLI_IVA civa,
    CLI_TP_DOCUMENTOS ctdc,
        FAC_SECCIONES_ITEMS fsei,
    STK_TP_INGRESOS stin,
        STK_TP_CARGA stcg,
        STK_GUIAS sgui,
        STK_DCTOS_DESTINACIONES sddt,
        STK_DESTINACIONES sdes
WHERE   fcfi.sfactura = $P{prm_factura}
AND     fcfi.sfactura = fsei.sfactura
AND fsei.sdctodest = sddt.sdctodest
AND sdes.sguia = fsei.sguia
AND sdes.sdctoing = fsei.sdctoing
AND sdes.sdctodest = fsei.sdctodest
AND stcg.ctpcarga = sdes.ctpcarga
AND stin.ctipoingreso = sdes.ctipoingreso
AND sgui.sguia = fsei.sguia
AND     fcfi.ctpdocu = ctdc.ctpdocu
AND     civa.c_iva = fcfi.civa
AND     fcfi.ctpcomprobante IN( 'FA','RE')
AND     fcfi.nfactura IS NOT NULL
AND     fcfi.scli_xctayorden = ccli.scli(+)