DDL of tables:


CREATE TABLE INVOICE (
ID INTEGER NOT NULL,
CLIENT_ID INTEGER,
DIVISION_ID INTEGER,
INVOICE_NO INTEGER,
INVOICE_DATE DATE,
SHIPPING_CHARGE NUMERIC(15,2) DEFAULT 0 NOT NULL,
DISCOUNT NUMERIC(15,2) DEFAULT 0 NOT NULL,
SHIPTO_ID INTEGER,
SALESREP_ID INTEGER,
TRAY VARCHAR(128),
POSTED BOOLEAN DEFAULT 'N' /* BOOLEAN = CHAR(1) NOT NULL CHECK (VALUE IN ('Y', 'N')) */,
COMMENT VARCHAR(250),
CREATE_DATE TIMESTAMP DEFAULT 'NOW',
PICKEDBY VARCHAR(32),
KIND VARCHAR(2) DEFAULT 'RO' NOT NULL,
ORDER_NO INTEGER,
ORDER_DATE DATE DEFAULT 'TODAY' NOT NULL,
GROUPINDEX INTEGER,
CREATORID INTEGER,
POSTERID INTEGER,
REVERSEID INTEGER,
COMMISION NUMERIC(15,2) DEFAULT 0
);

CREATE TABLE INVOICE_DETAIL (
ID INTEGER NOT NULL,
INVOICE_ID INTEGER,
INVENTORY_ID INTEGER,
MATRIX_ID INTEGER,
PART_ID INTEGER,
QTY INTEGER DEFAULT 0 NOT NULL,
UNIT_PRICE NUMERIC(15,2) DEFAULT 0 NOT NULL,
RIVETING_PRICE NUMERIC(15,2) DEFAULT 0 NOT NULL,
GROUPINDEX INTEGER,
ADJUSTINVENTORY CHAR(1),
COMMENTS VARCHAR(250),
GST NUMERIC(15,2) DEFAULT 0 NOT NULL,
POSTED CHAR(1) DEFAULT 'N' NOT NULL,
NETPRICING BOOLEAN DEFAULT 'N' NOT NULL /* BOOLEAN = CHAR(1) NOT NULL CHECK (VALUE IN ('Y', 'N')) */,
BACKORDER_ID INTEGER,
ETD DATE,
INVENTORYTYPE CHAR(1) DEFAULT 'P' NOT NULL,
DIVISION_ID INTEGER,
FRAME_ID INTEGER,
AMOUNT NUMERIC(15,4)
);

CREATE TABLE MATERIAL (
ID INTEGER NOT NULL,
DESCRIPTION VARCHAR(128),
TARIFF_ID INTEGER
);

CREATE TABLE TARIFF (
ID INTEGER NOT NULL,
DESCRIPTION VARCHAR(64),
CODE VARCHAR(32)
);

CREATE TABLE COUNTRY (
ID INTEGER NOT NULL,
NAME VARCHAR(64),
TARIFF BOOLEAN /* BOOLEAN = CHAR(1) NOT NULL CHECK (VALUE IN ('Y', 'N')) */,
CODE VARCHAR(8)
);

CREATE TABLE DIVISION (
ID INTEGER NOT NULL,
NAME VARCHAR(64),
CODE VARCHAR(8),
LAST_TARIFF_INVOICENO INTEGER
);

CREATE TABLE INVENTORY (
ID INTEGER NOT NULL,
MODEL_NO VARCHAR(64),
DESCRIPTION VARCHAR(128),
CATEGORY_ID INTEGER,
DIVISION_ID INTEGER,
INVENTORY_TYPE CHAR(1),
CURRENCY_ID INTEGER,
TARIFF_ID INTEGER,
COUNTRY_ID INTEGER,
SUPPLIER_ID INTEGER,
COST_PRICE NUMERIC(15,2) DEFAULT 0,
LANDED_COST NUMERIC(15,4) DEFAULT 0,
START_DATE DATE DEFAULT 'TODAY',
MATERIAL_ID INTEGER,
GST_APPLICABLE BOOLEAN /* BOOLEAN = CHAR(1) NOT NULL CHECK (VALUE IN ('Y', 'N')) */,
NETPRICING BOOLEAN DEFAULT 'N' /* BOOLEAN = CHAR(1) NOT NULL CHECK (VALUE IN ('Y', 'N')) */,
QTYONHAND INTEGER DEFAULT 0 NOT NULL,
QTYONHOLD INTEGER DEFAULT 0,
PRODUCTLINE_ID INTEGER,
QTYONORDER INTEGER DEFAULT 0 NOT NULL
);

CREATE DOMAIN BOOLEAN AS
CHAR(1) CHARACTER SET WIN1251
NOT NULL
CHECK (VALUE IN ('Y', 'N'))
COLLATE WIN1251

DDL of store procedure:

CREATE PROCEDURE REPORT_TARIFFJOURNAL (
DIVISIONID INTEGER)
RETURNS (
DESCRIPTION VARCHAR(128),
TARIFF_CODE VARCHAR(32),
COUNTRY_CODE VARCHAR(8),
QTY INTEGER,
INVOICE_AMOUNT NUMERIC(15,4),
SHIPPING_CHARGE NUMERIC(15,2),
SECTION INTEGER)
AS
DECLARE VARIABLE LAST_TARIFF_INVOICENO INTEGER;
DECLARE VARIABLE SQLSTRING VARCHAR(1024);
DECLARE VARIABLE EXCLUDEINVOICES VARCHAR(1024);
begin
excludeinvoices = '';

select LAST_TARIFF_INVOICENO from DIVISION
where ID = :divisionid
into :last_tariff_invoiceno;

if (last_tariff_invoiceno is null) then
last_tariff_invoiceno = 0;

select sum(SHIPPING_CHARGE) from INVOICE
where ((:divisionid <= 0) or (DIVISION_ID = :divisionid)) and
POSTED = 'Y' and INVOICE_NO > :last_tariff_invoiceno
into :SHIPPING_CHARGE;

SECTION = 1;

sqlstring = 'select m.DESCRIPTION, t.CODE, c.CODE, sum(id.QTY), sum(id.AMOUNT) '
|| 'from INVOICE i ' ||
'inner join INVOICE_DETAIL id on id.INVOICE_ID = i.ID and ' ||
'id.PART_ID is null and id.ADJUSTINVENTORY <> ''B'' '||
'inner join INVENTORY iv on iv.ID = id.INVENTORY_ID ' ||
'inner join COUNTRY c on c.ID = iv.COUNTRY_ID ' ||
'inner join MATERIAL m on m.ID = iv.MATERIAL_ID ' ||
'inner join TARIFF t on t.ID = m.TARIFF_ID ';

if (divisionid > 0) then
sqlstring = sqlstring ||
'where i.DIVISION_ID = ' || divisionid || ' and ' ||
'i.POSTED = ''Y'' and i.INVOICE_NO > ' || :last_tariff_invoiceno;
else
sqlstring = sqlstring ||
'where i.POSTED = ''Y'' and i.INVOICE_NO > ' || :last_tariff_invoiceno;

if (excludeinvoices <> '') then
sqlstring = sqlstring ||
' and i.INVOICE_NO not in (' || excludeinvoices || ') ';

sqlstring = sqlstring ||
' group by m.DESCRIPTION, t.CODE, c.CODE';

for execute statement sqlstring
into :DESCRIPTION, :TARIFF_CODE, :COUNTRY_CODE, :QTY, :INVOICE_AMOUNT
do
begin
suspend;
end

end


test sql: select * from REPORT_TARIFF(-1)

Thanks

William

Rick Debay <rdebay@...> wrote: Please post the DDL for the tables involved, and the variable
declarations for DESCRIPTION, TARIFF_CODE, COUNTRY_CODE, QTY, and
INVOICE_AMOUNT.



William, Yu

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

[Non-text portions of this message have been removed]