OraFAQ Forum: Forms » inserting summary data into bal table from detail-table after commit

Home » Developer & Programmer » Forms » inserting summary data into bal table from detail-table after commit (forms 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

inserting summary data into bal table from detail-table after commit [message #360176]

Thu, 20 November 2008 00:06

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

Attachment: master_purchase.JPG
(Size: 93.97KB, Downloaded 509 times)

Report message to a moderator

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

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Then in the 'post-database-commit' trigger redo your calculations and update the 'balance' table.

David

Report message to a moderator

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

anasjamil
Messages: 31
Registered: April 2008
Location: Sydney

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

Report message to a moderator

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

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

Attachment: sale form.JPG
(Size: 97.80KB, Downloaded 504 times)

Report message to a moderator

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

anasjamil
Messages: 31
Registered: April 2008
Location: Sydney

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

Report message to a moderator

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

spiritual_baba
Messages: 3
Registered: November 2008
Location: sukkur

Junior Member

can u provide my query or procedure
what should procedure i call there

Report message to a moderator

Previous Topic:	select file that contains specific word
Next Topic:	Radio group initial value

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 03 14:41:46 CST 2025