WITH CUOTAS AS
(
SELECT B.PO_REF AS S_SEM_CODIGO,
A.BILL_TO_CUST_ID AS S_ALU_CODIGO,
A.INVOICE AS S_CUO_INVOICE,
Y.ENTRY_TYPE AS ENTRY_TYPE,
Y.ENTRY_REASON AS ENTRY_REASON,
A.BILL_STATUS AS S_CUO_INVOICE_STATUS,
A.INVOICE_AMOUNT AS N_CUO_INVOICE_AMOUNT,
A.INVOICE_DT AS D_CUO_FECHA_EMISION,
CASE WHEN (A.PYMNT_TERMS_CD = 00000)
    THEN  TO_CHAR(A.DUE_DT,'YYYY-MM-DD')
    ELSE  TO_CHAR(F.DUE_DT,'YYYY-MM-DD')
END AS D_CUO_FECHA_VENCIMIENTO,
CASE WHEN D.ITEM_STATUS = 'C' THEN NVL(PY.PAYMENT_DT,Y.ACCOUNTING_DT) ELSE NULL END AS D_CUO_FECHA_PAGO,
PY.PAYMENT_METHOD_CDR AS S_CUO_TIPO_PAGO,
B.LINE_SEQ_NUM AS N_CUO_SEQ_LINEA,
B.DESCR AS S_CUO_CONCEPTO,
AVG(B.NET_EXTENDED_AMT) AS N_CUO_MONTO_LINEA,
D.BAL_AMT AS N_CUO_BALANCE,
CASE WHEN C.INSTALL_NBR IS NULL
  THEN 0
  ELSE C.INSTALL_NBR
END AS N_CUO_CUOTA
FROM PS_BI_HDR A
LEFT JOIN PS_BI_LINE B ON A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.INVOICE = B.INVOICE
LEFT JOIN PS_ITEM_ACTIVITY Y ON B.INVOICE = Y.ITEM AND Y.ITEM_SEQ_NUM = (SELECT MAX(Y_MAX.ITEM_SEQ_NUM)
                                                                          FROM PS_ITEM_ACTIVITY Y_MAX
                                                                          WHERE Y_MAX.ITEM = Y.ITEM)
LEFT JOIN PS_BI_INSTALL_SCHE C ON A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.INVOICE = C.GENERATED_INVOICE
LEFT JOIN PS_ITEM D ON A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.INVOICE = D.INVOICE
LEFT JOIN PS_PAY_TRMS_NET E ON E.PYMNT_TERMS_CD = A.PYMNT_TERMS_CD AND E.EFFDT = (SELECT MAX(E_ED.EFFDT)
                                                                                  FROM PS_PAY_TRMS_NET E_ED
                                                                                  WHERE E.SETID = E_ED.SETID
                                                                                  AND E.PYMNT_TERMS_CD = E_ED.PYMNT_TERMS_CD
                                                                                  AND E_ED.EFFDT <= SYSDATE)
LEFT JOIN PS_BI_LINE_NOTE G ON A.BUSINESS_UNIT = G.BUSINESS_UNIT AND A.INVOICE = CASE WHEN SUBSTR(G.TEXT254,1,17) = 'ORIGINAL_INVOICE:'
                                                                                    THEN SUBSTR(G.TEXT254,19,11)
                                                                                    ELSE ' '
                                                                                 END
LEFT JOIN PS_BI_HDR H ON G.BUSINESS_UNIT = H.BUSINESS_UNIT AND G.INVOICE = H.INVOICE
LEFT JOIN PS_BI_LINE_NOTE I ON A.BUSINESS_UNIT = I.BUSINESS_UNIT AND A.INVOICE = I.INVOICE AND B.LINE_SEQ_NUM = I.LINE_SEQ_NUM
LEFT JOIN PS_PAY_TRMS_TIME F ON E.SETID = F.SETID AND F.PAY_TRMS_TIME_ID = E.PAY_TRMS_TIME_ID
LEFT JOIN (SELECT PC.INVOICE, PD.PAYMENT_DT, PD.PAYMENT_METHOD_CDR
            FROM PS_LI_GBL_ARPY_REF PC
            INNER JOIN PS_LI_GBL_ARPY_DTL PD ON PC.PYMNT_REF_ID = PD.PYMNT_REF_ID AND PC.DEPOSIT_BU = PD.DEPOSIT_BU
            WHERE PC.DEPOSIT_BU = 'PER03'
) PY ON A.INVOICE = PY.INVOICE
WHERE
/*B.DESCR NOT LIKE '%Mat%'
AND*/ A.BILL_TYPE_ID IN ('B1','F1')
AND A.BILL_STATUS NOT IN ('CAN','HLD','RDY')
AND B.DESCR NOT LIKE '%ADELANT%'
--AND (H.BILL_TYPE_ID = 'D1' OR H.BILL_TYPE_ID = 'D2' OR H.BILL_TYPE_ID IS NULL OR H.BILL_TYPE_ID = '''''')
AND A.INVOICE NOT LIKE 'DUP%'
AND A.INVOICE NOT LIKE 'GAP%'
AND CASE WHEN  I.TEXT254 IS NULL OR  I.TEXT254 = '' OR  I.TEXT254 = ' '
    THEN 'A'
    ELSE (CASE WHEN SUBSTR(I.TEXT254,32,1) IS NULL OR SUBSTR(I.TEXT254,32,1) = '' OR SUBSTR(I.TEXT254,32,1) = ' '
            THEN 'A'
            ELSE (SUBSTR(I.TEXT254,32,1)) END)
          END <> 'M'
AND EXISTS (SELECT 1
            FROM LOE_STVTERM TRM
            WHERE TRM.STVTERM_CODE = B.PO_REF
            AND SUBSTR(TRM.STVTERM_CODE,4,1) IN (4,5)
            AND SYSDATE BETWEEN STVTERM_START_DATE AND STVTERM_END_DATE)
--AND A.BILL_TO_CUST_ID = 'N00009718'
GROUP BY B.PO_REF, A.BILL_TO_CUST_ID, A.INVOICE,Y.ENTRY_TYPE,Y.ENTRY_REASON, A.BILL_STATUS, A.INVOICE_AMOUNT, A.INVOICE_DT,
CASE WHEN (A.PYMNT_TERMS_CD = 00000)
  THEN TO_CHAR(A.DUE_DT,'YYYY-MM-DD')
  ELSE TO_CHAR(F.DUE_DT,'YYYY-MM-DD')
END,
D.ITEM_STATUS,
PY.PAYMENT_METHOD_CDR,
PY.PAYMENT_DT,
Y.ACCOUNTING_DT,
B.LINE_SEQ_NUM, B.DESCR, D.BAL_AMT,
CASE WHEN C.INSTALL_NBR IS NULL
  THEN 0
  ELSE C.INSTALL_NBR
END
)


SELECT pension.S_SEM_CODIGO,
pension.S_ALU_CODIGO,
pension.S_CUO_INVOICE,
pension.ENTRY_TYPE,
pension.ENTRY_REASON,
pension.N_CUO_INVOICE_AMOUNT,
pension.D_CUO_FECHA_EMISION,
pension.D_CUO_FECHA_VENCIMIENTO,
pension.D_CUO_FECHA_PAGO,
pension.S_CUO_TIPO_PAGO,
pension.N_CUO_CUOTA,
(CASE WHEN pension.N_CUO_CUOTA = 0 THEN (CASE WHEN UPPER(S_CUO_CONCEPTO) LIKE '%MATRICULA%' THEN 'MATRICULA' ELSE 'CUOTA INICIAL' END) ELSE 'CUOTA ' || pension.N_CUO_CUOTA END) AS S_CUO_CONCEPTO,
pension.MONTO AS N_CUO_MONTO,
NVL(dscto.MONTO,0) AS N_CUO_DESCUENTO,
NVL(beca.MONTO,0) AS N_CUO_BECA,
CASE WHEN pension.N_CUO_BALANCE IS NULL OR pension.N_CUO_BALANCE > 0 THEN (pension.MONTO + NVL(dscto.MONTO,0) + NVL(beca.MONTO,0)) ELSE pension.N_CUO_BALANCE END AS N_CUO_SALDO
FROM
(
  SELECT S_SEM_CODIGO, S_ALU_CODIGO, S_CUO_INVOICE, ENTRY_TYPE , ENTRY_REASON ,  N_CUO_INVOICE_AMOUNT, N_CUO_BALANCE, D_CUO_FECHA_EMISION, D_CUO_FECHA_VENCIMIENTO, D_CUO_FECHA_PAGO, S_CUO_TIPO_PAGO, N_CUO_CUOTA, S_CUO_CONCEPTO, SUM(N_CUO_MONTO_LINEA) AS MONTO
  FROM CUOTAS
  WHERE UPPER(S_CUO_CONCEPTO) LIKE '%ARANCEL%' OR UPPER(S_CUO_CONCEPTO) LIKE '%CUOTA%INI%' OR UPPER(S_CUO_CONCEPTO) LIKE '%CUOTA%1%' OR UPPER(S_CUO_CONCEPTO) LIKE '%MATRI%'
  GROUP BY S_SEM_CODIGO, S_ALU_CODIGO, S_CUO_INVOICE, ENTRY_TYPE , ENTRY_REASON , N_CUO_INVOICE_AMOUNT, N_CUO_BALANCE, D_CUO_FECHA_EMISION, D_CUO_FECHA_VENCIMIENTO, D_CUO_FECHA_PAGO, S_CUO_TIPO_PAGO, N_CUO_CUOTA, S_CUO_CONCEPTO
) pension
left join (
  SELECT S_SEM_CODIGO, S_ALU_CODIGO, S_CUO_INVOICE, SUM(N_CUO_MONTO_LINEA) AS MONTO
  FROM CUOTAS
  WHERE UPPER(S_CUO_CONCEPTO) LIKE '%DESCUENTO%'
  GROUP BY S_SEM_CODIGO, S_ALU_CODIGO, S_CUO_INVOICE
) dscto ON pension.S_SEM_CODIGO = dscto.S_SEM_CODIGO AND pension.S_ALU_CODIGO = dscto.S_ALU_CODIGO AND pension.S_CUO_INVOICE = dscto.S_CUO_INVOICE
left join (
  SELECT S_SEM_CODIGO, S_ALU_CODIGO, S_CUO_INVOICE, SUM(N_CUO_MONTO_LINEA) AS MONTO
  FROM CUOTAS
  WHERE UPPER(S_CUO_CONCEPTO) LIKE '%BECA%'
  GROUP BY S_SEM_CODIGO, S_ALU_CODIGO, S_CUO_INVOICE
) beca ON pension.S_SEM_CODIGO = beca.S_SEM_CODIGO AND pension.S_ALU_CODIGO = beca.S_ALU_CODIGO AND pension.S_CUO_INVOICE = beca.S_CUO_INVOICE


UNION

SELECT rept2.S_SEM_CODIGO,
rept2.S_ALU_CODIGO,
rept2.S_CUO_INVOICE,
rept2.ENTRY_TYPE ,
rept2.ENTRY_REASON ,
rept2.N_CUO_INVOICE_AMOUNT,
rept2.D_CUO_FECHA_EMISION,
rept2.D_CUO_FECHA_VENCIMIENTO,
rept2.D_CUO_FECHA_PAGO,
rept2.S_CUO_TIPO_PAGO,
rept2.N_CUO_CUOTA,
' CUOTA ' || rept2.N_CUO_CUOTA || ' ' || REPLACE(REPLACE(UPPER(rept2.S_CUO_CONCEPTO),'CURSOS POR','PENALIDAD POR'),'CUOTA POR','PENALIDAD POR') AS S_CUO_CONCEPTO,
rept2.MONTO AS N_CUO_MONTO,
0 AS N_CUO_DESCUENTO,
0 AS N_CUO_BECA,
CASE WHEN rept2.N_CUO_BALANCE IS NULL OR rept2.N_CUO_BALANCE > 0 THEN rept2.MONTO ELSE rept2.N_CUO_BALANCE END
FROM
(
  SELECT S_SEM_CODIGO, S_ALU_CODIGO, S_CUO_INVOICE, ENTRY_TYPE , ENTRY_REASON , N_CUO_INVOICE_AMOUNT, N_CUO_BALANCE, D_CUO_FECHA_EMISION, D_CUO_FECHA_VENCIMIENTO, D_CUO_FECHA_PAGO, S_CUO_TIPO_PAGO, N_CUO_CUOTA, SUBSTR(S_CUO_CONCEPTO,1,14) S_CUO_CONCEPTO, SUM(N_CUO_MONTO_LINEA) AS MONTO
  FROM CUOTAS
  WHERE UPPER(S_CUO_CONCEPTO) LIKE '%2DA%'
  GROUP BY S_SEM_CODIGO, S_ALU_CODIGO, S_CUO_INVOICE, ENTRY_TYPE , ENTRY_REASON , N_CUO_INVOICE_AMOUNT, N_CUO_BALANCE, D_CUO_FECHA_EMISION, D_CUO_FECHA_VENCIMIENTO, D_CUO_FECHA_PAGO, S_CUO_TIPO_PAGO, N_CUO_CUOTA, SUBSTR(S_CUO_CONCEPTO,1,14)
) rept2

UNION

SELECT rept3.S_SEM_CODIGO,
rept3.S_ALU_CODIGO,
rept3.S_CUO_INVOICE,
rept3.ENTRY_TYPE ,
rept3.ENTRY_REASON ,
rept3.N_CUO_INVOICE_AMOUNT,
rept3.D_CUO_FECHA_EMISION,
rept3.D_CUO_FECHA_VENCIMIENTO,
rept3.D_CUO_FECHA_PAGO,
rept3.S_CUO_TIPO_PAGO,
rept3.N_CUO_CUOTA,
' CUOTA ' || rept3.N_CUO_CUOTA || ' ' || REPLACE(REPLACE(UPPER(rept3.S_CUO_CONCEPTO),'CURSOS POR','PENALIDAD POR'),'CUOTA POR','PENALIDAD POR') AS S_CUO_CONCEPTO,
rept3.MONTO AS N_CUO_MONTO,
0 AS N_CUO_DESCUENTO,
0 AS N_CUO_BECA,
CASE WHEN rept3.N_CUO_BALANCE IS NULL OR rept3.N_CUO_BALANCE > 0 THEN rept3.MONTO ELSE rept3.N_CUO_BALANCE END
FROM
(
  SELECT S_SEM_CODIGO, S_ALU_CODIGO, S_CUO_INVOICE,ENTRY_TYPE , ENTRY_REASON , N_CUO_INVOICE_AMOUNT, N_CUO_BALANCE, D_CUO_FECHA_EMISION, D_CUO_FECHA_VENCIMIENTO, D_CUO_FECHA_PAGO, S_CUO_TIPO_PAGO, N_CUO_CUOTA, SUBSTR(S_CUO_CONCEPTO,1,14) S_CUO_CONCEPTO, SUM(N_CUO_MONTO_LINEA) AS MONTO
  FROM CUOTAS
  WHERE UPPER(S_CUO_CONCEPTO) LIKE '%3RA%'
  GROUP BY S_SEM_CODIGO, S_ALU_CODIGO, S_CUO_INVOICE,ENTRY_TYPE , ENTRY_REASON , N_CUO_INVOICE_AMOUNT, N_CUO_BALANCE, D_CUO_FECHA_EMISION, D_CUO_FECHA_VENCIMIENTO, D_CUO_FECHA_PAGO, S_CUO_TIPO_PAGO, N_CUO_CUOTA, SUBSTR(S_CUO_CONCEPTO,1,14)

) rept3
ORDER BY 1,2,3,6;