Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

SQL: show all cost lines where more than 2 invoices are present

BjornlaatSep 11 2023 — edited Sep 12 2023

Good afternoon,

We have a challenge and for this we need your help. We would like to see all cost rules of invoices where more than 2 invoices have been created on a shipment.

The logic of our system:
we have a shipment for example shipment: 0001 with the following charges:
base cost: € 400
Additional cost: € 200
Total cost is €600, an invoice(01) is created from this for €600 and is sent to the customer. A day later the shipment is updated to:
base cost: €400
Additional costs: € 300
Total costs are €700, now an invoice(02) is created for €100 and sent to the customer.

What we now want to see with this scenario is:

Community uitleg.png

It is important that we only get to see results where at least 2 invoices have been created. If there is only 1 invoice on a shipment, we don't want it in the overview.

Datamodel:

DATAMODEL COMMUNITY.png

Create table:

CREATE TABLE SHIPMENT
(
SHIPMENT_GID varchar(255),
PERSPECTIVE varchar(255),
TOTAL_ACTUAL_COST int
);

CREATE TABLE SHIPMENT_COST
(
SHIPMENT_GID varchar(255),
COST_TYPE varchar(255),
COST int
);
CREATE TABLE SHIPMENT_BILL
(
SHIPMENT_GID varchar(255),
BILL_GID varchar(255)
);
CREATE TABLE INVOICE
(
INVOICE_GID varchar(255),
NET_AMOUNT_DUE int
);

Insert:

INSERT INTO SHIPMENT (SHIPMENT_GID, PERSPECTIVE, TOTAL_ACTUAL_COST) VALUES ('0001', 'S', '700');
INSERT INTO SHIPMENT (SHIPMENT_GID, PERSPECTIVE, TOTAL_ACTUAL_COST) VALUES ('0002', 'S', '200');
INSERT INTO SHIPMENT_COST (SHIPMENT_GID, COST_TYPE, COST) VALUES ('0001', 'BASE', '400');
INSERT INTO SHIPMENT_COST (SHIPMENT_GID, COST_TYPE, COST) VALUES ('0001', 'EXTRA', '300');
INSERT INTO SHIPMENT_COST (SHIPMENT_GID, COST_TYPE, COST) VALUES ('0002', 'BASE', '200');
INSERT INTO SHIPMENT_BILL (SHIPMENT_GID, BILL_GID) VALUES ('0001', '01');
INSERT INTO SHIPMENT_BILL (SHIPMENT_GID, BILL_GID) VALUES ('0001', '02');
INSERT INTO SHIPMENT_BILL (SHIPMENT_GID, BILL_GID) VALUES ('0002', '03');
INSERT INTO INVOICE (INVOICE_GID, NET_AMOUNT_DUE) VALUES ('01', '600');
INSERT INTO INVOICE (INVOICE_GID, NET_AMOUNT_DUE) VALUES ('02', '100');
INSERT INTO INVOICE (INVOICE_GID, NET_AMOUNT_DUE) VALUES ('03', '200');

This post has been answered by Frank Kulash on Sep 11 2023
Jump to Answer
Comments
Post Details
Added on Sep 11 2023
7 comments
371 views