#14 I have a great idea about the interface module "Due register

[Andrew Subbotin]

I have a great idea about the interface module "Due register". The main part of idea is to use lookup column on the grid.

SQL of grid on the form "Expirations":

SELECT DOC19_EXPIRATIONS.COMPANY_CODE_SYS01 ... DOC19_EXPIRATIONS.ALREADY_PAYED, DOC28_PAYMENT_DISTRIBUTION.PAYMENT_VALUE, DOC28_PAYMENT_DISTRIBUTION.PROGRESSIVE_DOC27, DOC27_PAYMENTS.PROGRESSIVE, DOC27_PAYMENTS.ACCOUNT_CODE_ACC02, DOC27_PAYMENTS.PAYMENT_TYPE_CODE_REG11
FROM DOC19_EXPIRATIONS
LEFT OUTER JOIN DOC28_PAYMENT_DISTRIBUTION ON DOC28_PAYMENT_DISTRIBUTION.PROGRESSIVE_DOC19=DOC19_EXPIRATIONS.PROGRESSIVE
LEFT OUTER JOIN DOC27_PAYMENTS ON DOC28_PAYMENT_DISTRIBUTION.PROGRESSIVE_DOC27=DOC27_PAYMENTS.PROGRESSIVE
WHERE ...

LookupColumn: DOC28_PAYMENT_DISTRIBUTION.PROGRESSIVE_DOC27
SQL: SELECT DOC27_PAYMENTS.PROGRESSIVE, ... FROM DOC27_PAYMENTS WHERE DOC27_PAYMENTS.CUSTOMER_SUPPLIER_CODE = ? and DOC27_PAYMENTS.PAYMENT_VALUE_FREE > o;
The condition DOC27_PAYMENTS.PAYMENT_VALUE_FREE > o can be replaced to DOC27_PAYMENTS.FULL_DISTRIBUTHED ='N'.

When pressed button "Edit record" then cells column of grid "DOC28_PAYMENT_DISTRIBUTION.PROGRESSIVE_DOC27" with buttons "..." and "+" are switch to enabled.
Lookup query can be executed in the background in order to the button "..." not be enabled if result lookup query is empty.

If pressed the button "+" then form "New payment" is opening.
To the form "New payment" default values must be transmitted:
DOC27_PAYMENTS.CUMPANY_CODE_SYS01 = DOC19_EXPIRATIONS.CUMPANY_CODE_SYS01,
DOC27_PAYMENTS.CUSTOMER_SUPPLIEER_CODE = DOC19_EXPIRATIONS.CUSTOMER_SUPPLIER_CODE,
DOC27_PAYMENTS.VALUE = DOC19_EXPIRATIONS.ALREADY_PAYED,
DOC27_PAYMENTS.PAYMENT_TYPE_CODE_REG11 = DOC19_EXPIRATIONS.PAYMENT_TYPE_CODE_REG11,
DOC27_PAYMENTS.CURRENCY_CODE_REG03 = DOC19_EXPIRATIONS.CURRENCY_CODE_REG03
but values of fields DOC27_PAYMENTS.VALUE, DOC27_PAYMENTS.PAYMENT_TYPE_CODE_REG11, DOC27_PAYMENTS.CURRENCY_CODE_REG03 need to free of constraints to change.

When value of field "DOC28_PAYMENT_DISTRIBUTION.PROGRESSIVE_DOC27" is set, values of fields DOC27_PAYMENTS.... on the grid are set and CELL of column DOC28_PAYMENT_DISTRIBUTION.PAYMENT_VALUE switches to enabled with default value
DOC19_EXPIRATIONS.ALREADY_PAYED >= DOC28_PAYMENT_DISTRIBUTION.PAYMENT_VALUE <= DOC27_PAYMENTS.PAYMENT_VALUE_FREE and receives focus.

When saving changes then queries "INSERT INTO DOC28_PAYMENT_DISTRIBUTION...", "UPDATE DOC27_PAYMENTS SET DOC27_PAYMENTS.PAYMENT_VALUE_FREE= DOC27_PAYMENTS.PAYMENT_VALUE_FREE-DOC28_PAYMENT_DISTRIBUTION.PAYMENT_VALUE","UPDATE DOC19_EXPIRATIONS SET DOC19_EXPIRATIONS.ALREADY_PAYED=DOC19_EXPIRATIONS.ALREADY_PAYED-DOC28_PAYMENT_DISTRIBUTION.PAYMENT_VALUE" etc must be executed.

Requered fields:
DOC27_PAYMENTS.PAYMENT_VALUE_FREE DECIMAL(18,5), DOC27_PAYMENTS.FULL_DISTRIBUTHED CHAR(1).

Desirable field:
DOC28_PAYMENT_DISTRIBUTION.PROGRESSIVE NUMERIC(12).

Unused fields:
DOC28_PAYMENT_DISTRIBUTION.PAYED, DOC19_EXPIRATIONS.REAL_PAYMENT_TYPE_CODE_REG11, DOC19_EXPIRATIONS.REAL_ACCOUNT_CODE_ACC02.

The form "New payment" can be make part of module "Finance".
Have yuo interest by topic of module "Finance"?

[Andrew Subbotin]

seems without 'UNION' will not do...
SQL of grid on the form "Expirations" (only debts unpaid and partially paid):

(SELECT DOC19_EXPIRATIONS.COMPANY_CODE_SYS01,DOC19_EXPIRATIONS.DOC_TYPE,DOC19_EXPIRATIONS.DOC_NUMBER,DOC19_EXPIRATIONS.PROGRESSIVE,DOC19_EXPIRATIONS.DOC_YEAR, DOC19_EXPIRATIONS.DOC_SEQUENCE,DOC19_EXPIRATIONS.NAME_1,DOC19_EXPIRATIONS.NAME_2,DOC19_EXPIRATIONS.DESCRIPTION,DOC19_EXPIRATIONS.VALUE,DOC19_EXPIRATIONS.PAYED,DOC19_EXPIRATIONS.DOC_DATE,DOC19_EXPIRATIONS.CURRENCY_CODE_REG03,DOC19_EXPIRATIONS.ALREADY_PAYED,DOC19_EXPIRATIONS.EXPIRATION_DATE,DOC19_EXPIRATIONS.PROGRESSIVE_REG04,DOC19_EXPIRATIONS.CUSTOMER_SUPPLIER_CODE, DOC19_EXPIRATIONS.PAYED_DATE,DOC19_EXPIRATIONS.PAYED_VALUE, NULL AS "DOC28_PAYMENT_DISTRIBUTION.PAYMENT_VALUE", NULL AS "DOC28_PAYMENT_DISTRIBUTION.PROGRESSIVE_DOC27", NULL AS "DOC27_PAYMENTS.ACCOUNT_CODE_ACC02", NULL AS "DOC27_PAYMENTS.PAYMENT_TYPE_CODE_REG11" FROM DOC19_EXPIRATIONS WHERE DOC19_EXPIRATIONS.PAYED='N' AND DOC19_EXPIRATIONS.PAYED_VALUE>0 AND DOC19_EXPIRATIONS.COMPANY_CODE_SYS01 in ('COMPANY_CODE') and DOC19_EXPIRATIONS.PROGRESSIVE_REG04=?) UNION ALL (SELECT DOC19_EXPIRATIONS.COMPANY_CODE_SYS01,DOC19_EXPIRATIONS.DOC_TYPE,DOC19_EXPIRATIONS.DOC_NUMBER,DOC19_EXPIRATIONS.PROGRESSIVE,DOC19_EXPIRATIONS.DOC_YEAR,DOC19_EXPIRATIONS.DOC_SEQUENCE,DOC19_EXPIRATIONS.NAME_1,DOC19_EXPIRATIONS.NAME_2,DOC19_EXPIRATIONS.DESCRIPTION,DOC19_EXPIRATIONS.VALUE,DOC19_EXPIRATIONS.PAYED,DOC19_EXPIRATIONS.DOC_DATE,DOC19_EXPIRATIONS.CURRENCY_CODE_REG03,DOC19_EXPIRATIONS.ALREADY_PAYED,DOC19_EXPIRATIONS.EXPIRATION_DATE,DOC19_EXPIRATIONS.PROGRESSIVE_REG04,DOC19_EXPIRATIONS.CUSTOMER_SUPPLIER_CODE, DOC19_EXPIRATIONS.PAYED_DATE,DOC19_EXPIRATIONS.PAYED_VALUE, DOC28_PAYMENT_DISTRIBUTION.PAYMENT_VALUE, DOC28_PAYMENT_DISTRIBUTION.PROGRESSIVE_DOC27, DOC27_PAYMENTS.ACCOUNT_CODE_ACC02, DOC27_PAYMENTS.PAYMENT_TYPE_CODE_REG11 FROM DOC19_EXPIRATIONS LEFT OUTER JOIN DOC28_PAYMENT_DISTRIBUTION ON DOC28_PAYMENT_DISTRIBUTION.PROGRESSIVE_DOC19=DOC19_EXPIRATIONS.PROGRESSIVE LEFT OUTER JOIN DOC27_PAYMENTS ON DOC28_PAYMENT_DISTRIBUTION.PROGRESSIVE_DOC27=DOC27_PAYMENTS.PROGRESSIVE WHERE DOC19_EXPIRATIONS.COMPANY_CODE_SYS01 in ('COMPANY_CODE') and DOC19_EXPIRATIONS.PROGRESSIVE_REG04=? AND DOC19_EXPIRATIONS.PAYED='N' AND DOC28_PAYMENT_DISTRIBUTION.PAYMENT_VALUE IS NULL ORDER BY DOC19_EXPIRATIONS.EXPIRATION_DATE ASC, DOC19_EXPIRATIONS.DOC_TYPE ASC, DOC19_EXPIRATIONS.DOC_SEQUENCE ASC)