nenu_devudni Posted November 14, 2014 Report Share Posted November 14, 2014 trigger epudu raledu ..ede first .. kasta help cheyandi .. aa climax lo una if condition sariga work avatledu ... enni sarlu try chesina ..flag asalu set katledu ... ekada thapu chesano kasta cheppi punyam katukondi ... :#< CREATE OR REPLACE TRIGGER UIMSMGR.U$_UCRCRHS_AFTER_DB AFTER INSERT OR UPDATE ON UIMSMGR.UCRCRHS FOR EACH ROW DECLARE creditpoints NUMBER; credit_points NUMBER; newpoints NUMBER; check_ind VARCHAR2(1); credit_ind VARCHAR2(1); nsf_count NUMBER :=0; credit_count NUMBER; ccat_value VARCHAR2(10); utrccat_value VARCHAR2(10); l_points NUMBER; max_hits NUMBER := 0; actual_hits NUMBER := 0; BEGIN SELECT nvl(ucbcust_pay_by_check_ind,'Y'), NVL(ucbcust_pay_by_cc_db_ind,'Y'), ucbcust_creditpoints INTO check_ind, credit_ind, credit_points FROM ucbcust WHERE ucbcust_cust_code = :new.ucrcrhs_cust_code; BEGIN SELECT nvl(utrccat_points_per_occur,0) INTO newpoints FROM utrccat ,utvvals WHERE utrccat_code = :new.ucrcrhs_ccat_code AND utvvals_mstr_code = 'CREDIT' AND utrccat_code = utvvals_value; EXCEPTION WHEN no_data_found THEN newpoints := 0; END; creditpoints := creditpoints + newpoints; UPDATE ucbcust SET ucbcust_creditpoints = creditpoints WHERE ucbcust_cust_code = :new.ucrcrhs_cust_code; BEGIN SELECT utrccat_code INTO ccat_value FROM utrccat, utvvals WHERE utrccat_code = :new.ucrcrhs_ccat_code AND utvvals_mstr_code = 'CREDIT' AND utrccat_code = utvvals_value; EXCEPTION WHEN no_data_found THEN ccat_value := 'NONE'; END; max_hits := 0; actual_hits := 0; BEGIN SELECT utrccat_max_credit_hits, NVL ( SUM ( TO_NUMBER ( decode ( ucrcrhs_reversal_ind, 'Y','-1', '1' ) ) ) ,0 ) INTO max_hits, actual_hits FROM ucrcrhs, utvvals, utrccat WHERE utrccat_code = :NEW.ucrcrhs_ccat_code AND utvvals_mstr_code = 'CREDIT' AND utrccat_code = utvvals_value and ucrcrhs_cust_code = :new.ucrcrhs_cust_code AND ucrcrhs_occurance_date BETWEEN TRUNC(ADD_MONTHS(SYSDATE,0 - utrccat_valid_for_months)) AND TRUNC(SYSDATE) + .99999 group by utrccat_max_credit_hits; EXCEPTION WHEN no_data_found THEN dbms_output.put_line(SQLERRM); WHEN OTHERS THEN dbms_output.put_line(sqlerrm); END; -- reversal IF :NEW.ucrcrhs_ccat_code = ccat_value --'NSF' THEN IF :NEW.ucrcrhs_reversal_ind = 'Y' THEN nsf_count := 0; nsf_count := nvl(credit_points,0) - newpoints; UPDATE ucbcust SET ucbcust_creditpoints = nsf_count WHERE ucbcust_cust_code = :new.ucrcrhs_cust_code; ELSE nsf_count := 0; nsf_count := nvl(credit_points,0) + newpoints; UPDATE ucbcust SET ucbcust_creditpoints = nsf_count WHERE ucbcust_cust_code = :new.ucrcrhs_cust_code; END IF; IF actual_hits > max_hits THEN IF check_ind = 'Y' THEN UPDATE ucbcust a SET a.ucbcust_pay_by_check_ind = 'N' WHERE a.ucbcust_cust_code = :new.ucrcrhs_cust_code; END IF; ELSE IF check_ind = 'N' THEN UPDATE ucbcust a SET a.ucbcust_pay_by_check_ind = 'Y' WHERE a.ucbcust_cust_code = :new.ucrcrhs_cust_code; END IF; END IF; END IF; END u$_ucrcrhs_after_db; / Quote Link to comment Share on other sites More sharing options...
MeetFriendz Posted November 14, 2014 Report Share Posted November 14, 2014 ltt Quote Link to comment Share on other sites More sharing options...
Mastanvali Posted November 14, 2014 Report Share Posted November 14, 2014 baboyee code raseyachu..kani dinni inspect cheyalante chiraku……i dont have work experience dintlo…. Quote Link to comment Share on other sites More sharing options...
kum5758 Posted November 14, 2014 Report Share Posted November 14, 2014 ltt Quote Link to comment Share on other sites More sharing options...
nenu_devudni Posted November 14, 2014 Author Report Share Posted November 14, 2014 Quote Link to comment Share on other sites More sharing options...
nenu_devudni Posted November 14, 2014 Author Report Share Posted November 14, 2014 baboyee code raseyachu..kani dinni inspect cheyalante chiraku……i dont have work experience dintlo…. logic lu enni sarlu alochinchina sakaga ardam ayyi savavu mayya .. enni sarlu try chesina ratledu .. :3D_Smiles_38: . Quote Link to comment Share on other sites More sharing options...
Mastanvali Posted November 14, 2014 Report Share Posted November 14, 2014 love india(genelia shashireka parinayam pic ) uncle sql teach chestadanta..i want him to solve this one Quote Link to comment Share on other sites More sharing options...
sandy240887 Posted November 14, 2014 Report Share Posted November 14, 2014 exception lo dbms msgs pettukunnatte, same to same aa last logic lo kuda pettukoni em values vasthunnayo check chesuko. First actual_hits inka max_hits print chesuko, then check_ind value check cheyyi..tharvatha update statement tharvatha inko dbms statement pettukoni SQL%ROWCOUNT use chesi, enni rows update ayinayo check chesuko...Itla every step ki dbms msgs pettukoni check chesuko nuvvu expect chesina value vasthunda leda ani... Quote Link to comment Share on other sites More sharing options...
puli raaja Posted November 14, 2014 Report Share Posted November 14, 2014 CITI#H@ Quote Link to comment Share on other sites More sharing options...
Mastanvali Posted November 14, 2014 Report Share Posted November 14, 2014 love india …itu sudali Quote Link to comment Share on other sites More sharing options...
nenu_devudni Posted November 14, 2014 Author Report Share Posted November 14, 2014 exception lo dbms msgs pettukunnatte, same to same aa last logic lo kuda pettukoni em values vasthunnayo check chesuko. First actual_hits inka max_hits print chesuko, then check_ind value check cheyyi..tharvatha update statement tharvatha inko dbms statement pettukoni SQL%ROWCOUNT use chesi, enni rows update ayinayo check chesuko...Itla every step ki dbms msgs pettukoni check chesuko nuvvu expect chesina value vasthunda leda ani... but mayya .. ee trigger front end form nundi records insert avtay ... so apudu dbms statement lu ekada kanadapatay ?? Quote Link to comment Share on other sites More sharing options...
nenu_devudni Posted November 14, 2014 Author Report Share Posted November 14, 2014 plsql block or procedure aithe ala test cheyochu but oka trigger lo dbms statement lo una output ekada kanapadatay ?? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.