Home » Developer & Programmer » Forms » inserting summary data into bal table from detail-table after commit (forms 6i)
inserting summary data into bal table from detail-table after commit [message #360176] Thu, 20 November 2008 00:06 Go to next message
spiritual_baba
Messages: 3
Registered: November 2008
Location: sukkur
Junior Member

Hi!!

i was create the master detail purchase form it is completly done, but i want that all detail rows will summarised and after that insert into balance table

bye
Re: inserting summary data into bal table from detail-table after commit [message #360190 is a reply to message #360176] Thu, 20 November 2008 00:42 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Then in the 'post-database-commit' trigger redo your calculations and update the 'balance' table.

David
Re: inserting summary data into bal table from detail-table after commit [message #360198 is a reply to message #360176] Thu, 20 November 2008 01:00 Go to previous messageGo to next message
anasjamil
Messages: 28
Registered: April 2008
Location: KARACHI
Junior Member

"all details rows will summarized" ?
can you explain your problem in detail.

you may require a procedure for it at an event.



regards,
anas jamil


Re: inserting summary data into bal table from detail-table after commit [message #360292 is a reply to message #360198] Thu, 20 November 2008 05:39 Go to previous messageGo to next message
spiritual_baba
Messages: 3
Registered: November 2008
Location: sukkur
Junior Member

Table Master purchase
---------------------
CREATE TABLE MPURCHASE
(
CONS_NO NUMBER(6) PRIMARY KEY,
CONS_dT DATE,
ORDER_DT DATE,
ORDER_NO NUMBER(15),
B_CODE NUMBER(2) REFERENCES BRANCH(B_CODE),
H_cODE NUMBER(3),
PARTY_CODE NUMBER(5),
PARTY_NAME VARCHAR2(30),
ADDRESS VARCHAR2(30),
PHONE VARCHAR2(15),
FAX VARCHAR2(15),
CITY VARCHAR2(25),
GR_AMT NUMBER(14,4),
DIS_AMT NUMBER(14,4),
NET_AMT NUMBER(14,4),
POST CHAR(1))
/
-----------------------
Table Detail purchase
-----------------------
CREATE TABLE DPURCHASE
(
CONS_NO NUMBER(6) REFERENCES MPURCHASE(CONS_NO),
CONS_dT DATE,
B_CODE NUMBER(2),
H_cODE NUMBER(3),
PARTYCODE NUMBER(5),
C_CODE NUMBER(2) REFERENCES COMP(s_CODE),
G_CODE NUMBER(2),
I_CODE NUMBER(5) REFERENCES PRODUCT(I_CODE),
I_NAME VARCHAR2(30),
QTY NUMBER(12),
TP NUMBER(9,4),
DIS NUMBER(2,4),
DIS_AMT NUMBER(7,4),
SUBTOTAL NUMBER(12,4),
POST CHAR(1)
)
/
-------------------------------
Table Stock Balance
-------------------------------
CREATE TABLE STOCK
(
C_CODE NUMBER(2) REFERENCES COMP(S_CODE),
G_CODE NUMBER(2),
I_CODE NUMBER(5) REFERENCES PRODUCT(I_CODE),
I_NAME VARCHAR2(30),
PUR_PRICE NUMBER(10,4), SAL_PRICE NUMBER(10,4),
UPDATE_DT DATE,
OP NUMBER(7),
BAL NUMBER(7)
)
/
---------------------------------
TAble Master Sale
---------------------------------
CREATE TABLE STOCK
(
C_CODE NUMBER(2) REFERENCES COMP(S_CODE),
G_CODE NUMBER(2),
I_CODE NUMBER(5) REFERENCES PRODUCT(I_CODE),
I_NAME VARCHAR2(30),
PUR_PRICE NUMBER(10,4), SAL_PRICE NUMBER(10,4),
UPDATE_DT DATE,
OP NUMBER(7),
BAL NUMBER(7)
)
/
-----------------------------------
Table Detail Sale
-----------------------------------
CREATE TABLE DETAIL_SALE
(
INVNO NUMBER(7),
INVDT DATE,
b_CODE NUMBER(2),
H_cODE NUMBER(3),
CUS_CODE NUMBER(5),
RSO_CODE NUMBER(2),
Z_CODE NUMBER(3),
A_CODE NUMBER(3),
S_CODE NUMBER(2),
G_cODE NUMBER(2),
I_CODE NUMBER(5),
I_NAME NUMBER(30),
QTY NUMBER(Cool,
RATE NUMBER(10,4),
DIS NUMBER(2,2),
DIS_AMT NUMBER(10,4),
SUBTOTAL NUMBER(12,4))
/
-----------------------------------
I create 2 form using form6i, 1st is Purchase form and 2nd is sale form.
In purchase form i use two table that name is mpurchase and dpurchase. and sale form have two table name is master_Sale and detail_Sale.
Stock table create to handle inventory purpose. my query is that when i purchase inventory using purchase form, then all detail will summaries and insert into stock table. and when i sale inventory then less inventory through stock table and update it in each row

Regards
Re: inserting summary data into bal table from detail-table after commit [message #360304 is a reply to message #360292] Thu, 20 November 2008 06:14 Go to previous messageGo to next message
anasjamil
Messages: 28
Registered: April 2008
Location: KARACHI
Junior Member

you have to write 2 procedures.

1. when the records is saved in purchased table the 1st procedure
should also insert it in stock table (if that specific purchase
order is not in the stock table before).
"procedure can be called after saving record in purchase table"

2.when that specific order is saved in sales table the 2nd procedure should delete that specific order from stock table which is in currently saved in sales table.
"procedure can be called after saving record in sales table"

remember you might have to use if conditions also in the procedures and also link of primary and foreign keys of the tables are also important.


regards,
anas jamil
Re: inserting summary data into bal table from detail-table after commit [message #360311 is a reply to message #360304] Thu, 20 November 2008 06:39 Go to previous message
spiritual_baba
Messages: 3
Registered: November 2008
Location: sukkur
Junior Member

can u provide my query or procedure
what should procedure i call there
Previous Topic: select file that contains specific word
Next Topic: Radio group initial value
Goto Forum:
  


Current Time: Sat Dec 03 04:18:46 CST 2016

Total time taken to generate the page: 0.09098 seconds