Monday, 19 August 2013

Triggers:
 
 CREATE TABLE evaluations_log (log_date DATE 
                            , action VARCHAR2(50));
evaluation is database table 
 
CREATE OR REPLACE TRIGGER eval_modification_trigger
  AFTER INSERT OR UPDATE OR DELETE
  ON evaluations
DECLARE log_action evaluations_log.action%TYPE;
BEGIN
  IF INSERTING THEN log_action := 'Insert';
  ELSIF UPDATING THEN log_action := 'Update';
  ELSIF DELETING THEN log_action := 'Delete';
  ELSE DBMS_OUTPUT.PUT_LINE('This code is not reachable.');
  END IF;
  INSERT INTO evaluations_log (log_date, action)
    VALUES (SYSDATE, log_action);
END;

Monday, 12 August 2013

To check the po line taxes in 11i
-------------
SELECT     LINE_LOCATION_ID,PO_HEADER_ID,PO_LINE_ID,0 aexcise,SUM(JIPLLT.TAX_AMOUNT) excise,
           0 lst, 0 cst, 0 edcess, 0 hedcess,
           0 freight,0 cvd,0 cd,0 sad,0 ins,
        0 ocd,0 ocvd,0 obed,0 ocess,0 ohed,0 ost,0 ostcess,0 osthed,0 ovat,0 odis

       FROM ja.JA_IN_PO_LINE_LOCATION_TAXES  jipllt,
           (SELECT TAX_ID,TAX_TYPE,TAX_NAME FROM ja.ja_in_tax_codes WHERE end_date IS NULL AND org_id = 107) jitc

        WHERE  jipllt.tax_id = jitc.tax_id
         AND UPPER (jitc.tax_type) = 'EXCISE'