/* Formatted on 2006/05/09 14:16 (Formatter Plus v4.8.5) */
DROP TABLE facture_nl
/
CREATE TABLE facture_nl
(
  idtfac  NUMBER,
  nomfac  VARCHAR2(200 BYTE)
)
/
DROP TABLE type_ligne_facture_nl
/
CREATE TABLE type_ligne_facture_nl
(
  idttypligfac  NUMBER,
  typligfac     VARCHAR2(200 BYTE)
)
/
DROP TABLE ligne_facture_nl
/
CREATE TABLE ligne_facture_nl
(
  idtligfac     NUMBER,
  idtfac        NUMBER,
  idttypligfac  NUMBER
)
/
DROP TABLE d_nature_prime_nl
/
CREATE TABLE d_nature_prime_nl
(
  idtnarprm  NUMBER,
  ntrprm     VARCHAR2(200 BYTE)
)
/
DROP TABLE detail_ligne_facture_nl
/
CREATE TABLE detail_ligne_facture_nl
(
  idtdetligfac  NUMBER,
  idtligfac     NUMBER,
  idtnarprm     NUMBER,
  mnt           NUMBER
)
/
INSERT INTO facture_nl
            (idtfac, nomfac
            )
     VALUES (1, 'Facture NL'
            );
INSERT INTO facture_nl
            (idtfac, nomfac
            )
     VALUES (2, 'Facture Autre'
            );
COMMIT ;

INSERT INTO type_ligne_facture_nl
            (idttypligfac, typligfac
            )
     VALUES (1, 'RR'
            );
INSERT INTO type_ligne_facture_nl
            (idttypligfac, typligfac
            )
     VALUES (2, 'RO'
            );
INSERT INTO type_ligne_facture_nl
            (idttypligfac, typligfac
            )
     VALUES (3, 'RVAP'
            );
COMMIT ;

INSERT INTO ligne_facture_nl
            (idtligfac, idtfac, idttypligfac
            )
     VALUES (1, 1, 1
            );
INSERT INTO ligne_facture_nl
            (idtligfac, idtfac, idttypligfac
            )
     VALUES (2, 1, 2
            );
INSERT INTO ligne_facture_nl
            (idtligfac, idtfac, idttypligfac
            )
     VALUES (3, 1, 3
            );
INSERT INTO ligne_facture_nl
            (idtligfac, idtfac, idttypligfac
            )
     VALUES (4, 2, 2
            );
COMMIT ;

INSERT INTO d_nature_prime_nl
            (idtnarprm, ntrprm
            )
     VALUES (1, 'Prime'
            );
INSERT INTO d_nature_prime_nl
            (idtnarprm, ntrprm
            )
     VALUES (2, 'PB'
            );
INSERT INTO d_nature_prime_nl
            (idtnarprm, ntrprm
            )
     VALUES (3, 'Ristourne'
            );
INSERT INTO d_nature_prime_nl
            (idtnarprm, ntrprm
            )
     VALUES (4, 'Back Service'
            );
INSERT INTO d_nature_prime_nl
            (idtnarprm, ntrprm
            )
     VALUES (5, 'Transfert In'
            );
COMMIT ;

INSERT INTO detail_ligne_facture_nl
            (idtdetligfac, idtligfac, idtnarprm, mnt
            )
     VALUES (1, 1, 1, 100
            );
INSERT INTO detail_ligne_facture_nl
            (idtdetligfac, idtligfac, idtnarprm, mnt
            )
     VALUES (2, 1, 2, 200
            );
INSERT INTO detail_ligne_facture_nl
            (idtdetligfac, idtligfac, idtnarprm, mnt
            )
     VALUES (3, 1, 3, 300
            );
INSERT INTO detail_ligne_facture_nl
            (idtdetligfac, idtligfac, idtnarprm, mnt
            )
     VALUES (4, 2, 1, 25
            );
INSERT INTO detail_ligne_facture_nl
            (idtdetligfac, idtligfac, idtnarprm, mnt
            )
     VALUES (5, 2, 2, 456
            );
INSERT INTO detail_ligne_facture_nl
            (idtdetligfac, idtligfac, idtnarprm, mnt
            )
     VALUES (6, 2, 3, 78
            );
INSERT INTO detail_ligne_facture_nl
            (idtdetligfac, idtligfac, idtnarprm, mnt
            )
     VALUES (7, 3, 1, 78
            );
INSERT INTO detail_ligne_facture_nl
            (idtdetligfac, idtligfac, idtnarprm, mnt
            )
     VALUES (8, 3, 2, 18
            );
INSERT INTO detail_ligne_facture_nl
            (idtdetligfac, idtligfac, idtnarprm, mnt
            )
     VALUES (9, 3, 3, 89
            );
INSERT INTO detail_ligne_facture_nl
            (idtdetligfac, idtligfac, idtnarprm, mnt
            )
     VALUES (10, 1, 4, 4554
            );
INSERT INTO detail_ligne_facture_nl
            (idtdetligfac, idtligfac, idtnarprm, mnt
            )
     VALUES (11, 3, 5, 87787
            );
INSERT INTO detail_ligne_facture_nl
            (idtdetligfac, idtligfac, idtnarprm, mnt
            )
     VALUES (12, 1, 5, 645
            );
INSERT INTO detail_ligne_facture_nl
            (idtdetligfac, idtligfac, idtnarprm, mnt
            )
     VALUES (13, 4, 3, 769
            );
COMMIT ;

SELECT typ.idttypligfac
     , lig.idtfac
     , prm.mnt prime
     , pb.mnt pb
     , rst.mnt ristourne
     , bcs.mnt backservice
     , t.mnt transfertin
  FROM facture_nl fac
     , type_ligne_facture_nl typ
     , ligne_facture_nl lig
     , detail_ligne_facture_nl prm
     , detail_ligne_facture_nl pb
     , detail_ligne_facture_nl rst
     , detail_ligne_facture_nl bcs
     , detail_ligne_facture_nl t
 WHERE fac.idtfac = lig.idtfac(+)
   AND typ.idttypligfac(+) = lig.idttypligfac
   AND prm.idtligfac(+) = lig.idtligfac
   AND pb.idtligfac(+) = lig.idtligfac
   AND rst.idtligfac(+) = lig.idtligfac
   AND bcs.idtligfac(+) = lig.idtligfac
   AND t.idtligfac(+) = lig.idtligfac
   AND prm.idtnarprm(+) = 1
   AND pb.idtnarprm(+) = 2
   AND rst.idtnarprm(+) = 3
   AND bcs.idtnarprm(+) = 4
   AND t.idtnarprm(+) = 5
ORDER BY
   idtfac, idttypligfac;