Home » Developer & Programmer » Forms » Update Another Table (forms 6i, Oracle 9i Windows XP)
Update Another Table [message #437283] Sun, 03 January 2010 03:10 Go to next message
*munnabhai*
Messages: 157
Registered: March 2008
Location: Riyadh
Senior Member
Hi guyz,

after a long time i'm posting a message that i need a help from your side im stuck in one form to update another table. letme explain my scenario.

I have the master details table
SQL> DESC MN_ADN_MASTER
 Name                            Null?    Type
 ------------------------------- -------- ----
 MRF_NO                          NOT NULL VARCHAR2(15)
 ISU_DATE                                 DATE
 PROJECT_CODE                             VARCHAR2(20)
 SENT_BY                                  VARCHAR2(40)
 TITLE_NO                                 VARCHAR2(100)
 MATERIAL_TYPE                            VARCHAR2(30)
 DEPARTMENT                               VARCHAR2(30)
 PROJECT_LOC                              VARCHAR2(30)
 DATE_REQ                                 DATE

SQL> DESC MN_ADN_DETAILS
 Name                            Null?    Type
 ------------------------------- -------- ----
 MRF#                                     VARCHAR2(15)
 SERIAL#                                  VARCHAR2(60)
 ITEM_DESCP                               VARCHAR2(100)
 MANUFACTURER                             VARCHAR2(30)
 UOM                                      VARCHAR2(6)
 QTY                                      NUMBER(6)
 REMARKS                                  VARCHAR2(250)
 CATEGORY                                 VARCHAR2(30)

im using the below trigger to update the stock table on form level.
PRE-INSERT TRIGGER (FORM LEVEL)
UPDATE MN_ASSET_STOCK
SET QTY = NVL(QTY,0) - NVL(:MN_ADN_DETAILS.QTY,0)
WHERE ITEM_DESCP = :MN_ADN_DETAILS.ITEM_DESCP;

PRE-UPDATE TRIGGER (FORM LEVEL)
Forms_ddl ('UPDATE MN_ASSET_STOCK SET QTY=NVL(QTY,0)+(NVL(' || :MN_ADN_DETAILS.QTY
     || ',0)- NVL(' || GET_ITEM_PROPERTY('MN_ADN_DETAILS.QTY',DATABASE_VALUE) || ',0)) WHERE
QTY=' || :MN_ADN_DETAILS.QTY);


and below is the STOCK table
SQL> DESC MN_ASSET_STOCK;
 Name                            Null?    Type
 ------------------------------- -------- ----
 ITEM_DESCP                               VARCHAR2(100)
 MANUFACTURER                             VARCHAR2(30)
 PART                                     VARCHAR2(30)
 SERIAL#                                  VARCHAR2(40)
 UOM                                      VARCHAR2(6)
 QTY                                      NUMBER(6)
 UNIT_PRICE                               NUMBER(17,2)
 NOTES                                    VARCHAR2(250)
 CATEGORY                                 VARCHAR2(40)


when user insert the record in the above tables and taking the items for sale from mn_asset_stock table during commit it will update the stock table and quantity, as well upon commit im inserting the data in another below table using the post-insert trigger
MRF#                                     VARCHAR2(15)ITEM_DESCP                               VARCHAR2(100)
SERIAL#                                  VARCHAR2(60)
MANUFACTURER                             VARCHAR2(30)
UOM                                      VARCHAR2(6)
QTY                                      NUMBER(6)
REMARKS                                  VARCHAR2(250)
CATEGORY                                 VARCHAR2(30)

if customer want to return some items user just put the invoice number into the MRF# it will display all the details but how can i update the mn_asset_stock table quantity,items etc etc?
just wanna to do sales return. and update the stock table.

anyone help me in this plz.

Regards
Re: Update Another Table [message #437292 is a reply to message #437283] Sun, 03 January 2010 05:15 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Isn't that a process that is just the opposite to what you already did?
Sale  : quantity = quantity - (number of sold items)
Return: quantity = quantity + (number of returned items)
Re: Update Another Table [message #437760 is a reply to message #437283] Tue, 05 January 2010 23:13 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Where are you 'at' with this problem?

David
Previous Topic: General question
Next Topic: Oracle Developer suits that supports 11g and windows vista
Goto Forum:
  


Current Time: Wed Sep 28 00:39:20 CDT 2016

Total time taken to generate the page: 0.10963 seconds