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
] 
Blog
Search
Help
Members
Register
Login
Home
