8
I have two tables named as “COMPANY and BILL_DETAIL” with the following structure.
BILL_DETAIL TABLE
Name Type Nullable
------------- -------------- --------
BILL_ID NUMBER(7)
S_HEAD_CODE VARCHAR2(12)
SR_NO NUMBER(9)
BILL_AMOUNT NUMBER(10) Y
DESCRIPTION VARCHAR2(1000)
CATEGORY_CODE NUMBER(6) Y
COMP_ID (Foreign Key) NUMBER(9) Y
TAX_AMOUNT NUMBER(9) Y
TAX_ID NUMBER(9) Y
COMPANY TABLE
Name Type Nullable
----------------------- ------------- --------
COMP_ID(PRIMARY Key) NUMBER(9)
COMP_NAME VARCHAR2(40)
CONTACT_PERSON VARCHAR2(25) Y
COMP_ADDRESS VARCHAR2(200) Y
NTN VARCHAR2(15) Y
OWNER_CNIC VARCHAR2(15) Y
TOTAL_BUSINESS_PURCHASE INTEGER Y
TOTAL_BUSINESS_REPAIR INTEGER Y
I am updating COMPANY table whenever I insert values in BILL_DETAIL Table. The following code is working well in solving this problem.
CODE IS AS FOLLOWS:
/*
** CODE WRITTEN TO UPDATE COMPANY BUSINESS RECORD
*/
BEGIN
IF :SUB_HEAD.S_HEAD_CODE IN ('A13001','A13101') THEN
UPDATE COMPANY
SET TOTAL_BUSINESS_PURCHASE = TOTAL_BUSINESS_PURCHASE + :BILL_DETAIL.BILL_AMOUNT
WHERE COMPANY.COMP_ID = :BILL_DETAIL.COMP_ID ;
ELSE IF :SUB_HEAD.S_HEAD_CODE IN ('A13201','A13801') THEN
UPDATE COMPANY
SET TOTAL_BUSINESS_REPAIR = TOTAL_BUSINESS_REPAIR + :BILL_DETAIL.BILL_AMOUNT
WHERE COMP_ID = :BILL_DETAIL.COMP_ID ;
END IF;
END IF;
END;
----------------------------------------------------------------------
/*
** CODE WRITTEN TO DEDUCT TAX AMOUNT FROM COMPANIES BUSINESS
*/
DECLARE
COMPAMOUNT NUMBER;--variable to hold concerened company business (PURCHASE or REPAIR)
SEALAMOUNT NUMBER;--variable to hold the sealing amount from TAX_RULES table
PERCENTAGE NUMBER;--variable to hold the percentage of TAX from TAX_RULES table
-- CODE FOR CAPTURING COMPANIES PURCHASE & REPAIR RECORD
BEGIN
IF :SUB_HEAD.S_HEAD_CODE IN ('A13001','A13101') THEN
SELECT TOTAL_BUSINESS_PURCHASE INTO COMPAMOUNT FROM COMPANY
WHERE COMPANY.COMP_ID=:BILL_DETAIL.COMP_ID;
ELSE
IF :SUB_HEAD.S_HEAD_CODE IN ('A13201','A13801') THEN
SELECT TOTAL_BUSINESS_REPAIR INTO COMPAMOUNT FROM COMPANY
WHERE COMPANY.COMP_ID=:BILL_DETAIL.COMP_ID;
END IF;
END IF;
--CODE TO CAPTURE SEALING AMOUNT & TAX PERCENTAGE FROM TAX_RULES RECORD
SELECT max(nvl(SEALING_AMOUNT,0)),max(nvl(TAX_PERCENTAGE, 0)) INTO SEALAMOUNT,PERCENTAGE FROM TAX_RULES
WHERE TAX_RULES.S_HEAD_CODE=:BILL_DETAIL.S_HEAD_CODE
AND TAX_RULES.TAX_ID=:BILL_DETAIL.TAX_ID;
IF COMPAMOUNT>=SEALAMOUNT --CHECKS IF COMPANY BUSINESS EXCEEDS THAN SEALING AMOUNT
--IF SO, THEN APPLY THE BELOW UPDATE STATEMENT
THEN
UPDATE BILL_DETAIL
SET TAX_AMOUNT=BILL_AMOUNT*(PERCENTAGE/100)
WHERE BILL_DETAIL.COMP_ID=:COMP_ID;
END IF;
END;
Now, I want that when I delete a record from BILL_DETAIL Table then that record should be deleted from company table also.
Please help me in solving this problem.
thanks
