pl-sql-editor - Project Hosting on Google Code

Older Aug 10, 2009 Feedbacks (Please leave your feedback here commenting this page.) Wiki page commented on by psccox - oops, ignore that, it seems oracle lite doesn't support pl/sql! oops, ignore that, it seems oracle lite doesn't support pl/sql! Aug 10, 2009 Feedbacks (Please leave your feedback here commenting this page.) Wiki page commented on by psccox - The Oracle database I use is accessed via the oracle.lite.poljdbc.POLJDBCDriver which needs its own library jar and custom jdbc connection string Can't you re-use the standard Services --> Databases tab and connection logic? This would make it more standard. The Oracle database I use is accessed via the oracle.lite.poljdbc.POLJDBCDriver which needs its own library jar and custom jdbc connection string Can't you re-use the standard Services --> Databases tab and connection logic? This would make it more standard. Aug 05, 2009 issue 5 (Enter one-line summary) reported by brucefau...@yahoo.com - What steps will reproduce the problem? 1. connect to Oracle 11g 2. I enter in the server name the port number 3. my user name and password What is the expected output? What do you see instead? I expect connected as my Oracle client connects with out problems. I get ORA-12505 TNS:listener does not currently know of SID What version of the product are you using? On what operating system? Net beans 6.5, Windows XP, Oracle 11g Please provide any additional information below. I can connect using jdbc and my url looks like this url = "jdbc:oracle:thin:@( DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = edrzd4504v.wellsfargo.com)(PORT = 1595))(ADDRESS = (PROTOCOL = TCP)(HOST = edrzd4505v.wellsfargo.com)(PORT = 1595))(ADDRESS = (PROTOCOL = TCP)(HOST = edrzd4506v.wellsfargo.com)(PORT = 1595))(LOAD_BALANCE = yes)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = sms01d)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=250) (DELAY=5))))"; What steps will reproduce the problem? 1. connect to Oracle 11g 2. I enter in the server name the port number 3. my user name and password What is the expected output? What do you see instead? I expect connected as my Oracle client connects with out problems. I get ORA-12505 TNS:listener does not currently know of SID What version of the product are you using? On what operating system? Net beans 6.5, Windows XP, Oracle 11g Please provide any additional information below. I can connect using jdbc and my url looks like this url = "jdbc:oracle:thin:@( DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = edrzd4504v.wellsfargo.com)(PORT = 1595))(ADDRESS = (PROTOCOL = TCP)(HOST = edrzd4505v.wellsfargo.com)(PORT = 1595))(ADDRESS = (PROTOCOL = TCP)(HOST = edrzd4506v.wellsfargo.com)(PORT = 1595))(LOAD_BALANCE = yes)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = sms01d)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=250) (DELAY=5))))"; Apr 07, 2009 issue 4 (Many packages, causes plugin to Crash/Not Return.) commented on by alexan...@sumsoftsolutions.com - As workaround I would like to suggest setting maximum Java heap size to 1024MB. To set “ -J-Xmx1024m” option for launcher modifying /etc/.conf file. Hope this help. As workaround I would like to suggest setting maximum Java heap size to 1024MB. To set “ -J-Xmx1024m” option for launcher modifying /etc/.conf file. Hope this help. Apr 07, 2009 issue 4 (Many packages, causes plugin to Crash/Not Return.) reported by kristian...@hotmail.co.uk - What steps will reproduce the problem? 1. Log on to database as apps user on an EBS environment (or one with lots and lots of packages). 2. Expand the package node 3. What is the expected output? What do you see instead? Application seems to be unresponsive for a long period of time. In my case too long and actually didn't complete. SQL Developer has the same problems but eventually returns. The pl-sql-editor plugin did not complete and seemed to crash. I'm guessing this is purely down to the amount of packages in such a schema. SQLDeveloper uses a filter which can also be canceled. What version of the product are you using? On what operating system? Windows/Linux Please provide any additional information below. Would a filter offering similar functionality to that of SQL Developer be possible in your fantastic plug-in. What steps will reproduce the problem? 1. Log on to database as apps user on an EBS environment (or one with lots and lots of packages). 2. Expand the package node 3. What is the expected output? What do you see instead? Application seems to be unresponsive for a long period of time. In my case too long and actually didn't complete. SQL Developer has the same problems but eventually returns. The pl-sql-editor plugin did not complete and seemed to crash. I'm guessing this is purely down to the amount of packages in such a schema. SQLDeveloper uses a filter which can also be canceled. What version of the product are you using? On what operating system? Windows/Linux Please provide any additional information below. Would a filter offering similar functionality to that of SQL Developer be possible in your fantastic plug-in. Jan 12, 2009 issue 3 (PL/SQL editor does not support JOIN expressions) commented on by alexan...@sumsoftsolutions.com - Hello, the sql qery is the part of NetBeans, perhaps you should ask direct at netbeans.org Regards, Alexandre Hello, the sql qery is the part of NetBeans, perhaps you should ask direct at netbeans.org Regards, Alexandre Jan 11, 2009 issue 3 (PL/SQL editor does not support JOIN expressions) commented on by hu8297da - how 2 RT this code in sql query (report) ------------------------------------ PROCEDURE get_vouchers IS cursor get1 is select ent_acct_no,cc_id,ent_cr_amt,ent_db_amt,TXN_ent_cr_amt,TXN_ent_db_amt from posted_entry where (trunc(ent_voh_date) between nvl(:f_date,ent_voh_date) and nvl (:t_date,ent_voh_date)) and (ent_acct_no = :acc_no or :acc_no is null) and (cc_id = :cost_center or :cost_center is null) and (ent_voh_code = 1) and (com_no = :global.com_no) group by ent_acct_no,cc_id,ent_cr_amt,ent_db_amt,TXN_ent_cr_amt,TXN_ent_db_amt having (count(1) > 1) order by 3,4; db number(12,2); cr number(12,2); cursor get2(acc varchar,cc varchar,cr number ,db number) is select distinct ent_voh_no,ent_voh_code from posted_entry where ent_acct_no = acc and cc_id = cc and nvl(TXN_ent_cr_amt,0) = nvl(cr,0) and nvl(TXN_ENT_db_amt,0) = nvl(db,0) and com_no = :global.com_no; CURSOR GET_CURR (P_VOH_NO NUMBER,P_VOH_CODE VARCHAR2) IS SELECT CURRENCY FROM VOUCHER_MASTER WHERE VOUCHER_NO = P_VOH_NO AND VOUCHER_CODE = P_VOH_CODE AND COM_NO = :GLOBAL.COM_NO; BEGIN --show_message('ent_acct_no = '\|\|:acc_no\|\|' cc_id = '\|\|:cost_center\|\|' :f_date '\|\|:f_date\|\|' :t_date '\|\|:t_date); for rec1 in get1 loop select rec1.ent_cr_amt,rec1.ent_db_amt into :ENT_CR_AMT ,:ENT_DB_AMT from dual; --show_message('acc '\|\|rec1.ent_acct_no\|\|' cc '\|\|rec1.cc_id\|\|' cr '\|\|rec1.ent_cr_amt\|\|' db '\|\|rec1.ent_db_amt); for rec2 in get2 (rec1.ent_acct_no,rec1.cc_id,rec1.TXN_ent_cr_amt,rec1.TXN_ent_db_amt) loop --show_message('second '\|\|rec2.ent_voh_no); select rec1.ent_cr_amt,rec1.ent_db_amt into :ENT_CR_AMT ,:ENT_DB_AMT from dual; select rec2.ent_voh_no,rec1.ent_acct_no into :res.voh_no,:res.acct from dual; begin select DECODE(:GLOBAL.LANG,'L',voh_name_l,'A',VOH_NAME_A) into :voh_code from voucher_type where voh_code = rec2.ent_voh_code and com_no = :global.com_no ; exception when no_data_found then :voh_code:='Not Defined'; end; :VO_CODE_NUMBER:=rec2.ent_voh_code; begin select DECODE(:GLOBAL.LANG,'L',cc_name_l,'A',CC_NAME_A) into :cc_name from cost_center where cc_id = rec1.cc_id and com_no = :global.com_no ; exception when no_data_found then :cc_name:='Not Defined'; end; begin select DECODE(:GLOBAL.LANG,'L',acct_name_l,'A',ACCT_NAME_A) into :acct_name from account_master where acct_no = rec1.ent_acct_no and com_no = :global.com_no ; exception when no_data_found then :acct_name:='Not Defined'; end; OPEN GET_CURR(REC2.ent_voh_no,REC2.ent_voh_code); FETCH GET_CURR INTO :CURRENCY_N; CLOSE GET_CURR; if REC1.TXN_ENT_DB_AMT > 0 then :amount := ACCP_CUR.NUM_FORMAT(TO_NUMBER(REC1.TXN_ENT_DB_AMT),15,GET_DEC (:CURRENCY_N),0,'L'); elsif REC1.TXN_ENT_CR_AMT > 0 then :amount :=ACCP_CUR.NUM_FORMAT(TO_NUMBER(REC1.TXN_ENT_CR_AMT),15,GET_DEC (:CURRENCY_N),0,'L'); end if; next_record; end loop; end loop; first_record; END; how 2 RT this code in sql query (report) ------------------------------------ PROCEDURE get_vouchers IS cursor get1 is select ent_acct_no,cc_id,ent_cr_amt,ent_db_amt,TXN_ent_cr_amt,TXN_ent_db_amt from posted_entry where (trunc(ent_voh_date) between nvl(:f_date,ent_voh_date) and nvl (:t_date,ent_voh_date)) and (ent_acct_no = :acc_no or :acc_no is null) and (cc_id = :cost_center or :cost_center is null) and (ent_voh_code = 1) and (com_no = :global.com_no) group by ent_acct_no,cc_id,ent_cr_amt,ent_db_amt,TXN_ent_cr_amt,TXN_ent_db_amt having (count(1) > 1) order by 3,4; db number(12,2); cr number(12,2); cursor get2(acc varchar,cc varchar,cr number ,db number) is select distinct ent_voh_no,ent_voh_code from posted_entry where ent_acct_no = acc and cc_id = cc and nvl(TXN_ent_cr_amt,0) = nvl(cr,0) and nvl(TXN_ENT_db_amt,0) = nvl(db,0) and com_no = :global.com_no; CURSOR GET_CURR (P_VOH_NO NUMBER,P_VOH_CODE VARCHAR2) IS SELECT CURRENCY FROM VOUCHER_MASTER WHERE VOUCHER_NO = P_VOH_NO AND VOUCHER_CODE = P_VOH_CODE AND COM_NO = :GLOBAL.COM_NO; BEGIN --show_message('ent_acct_no = '\|\|:acc_no\|\|' cc_id = '\|\|:cost_center\|\|' :f_date '\|\|:f_date\|\|' :t_date '\|\|:t_date); for rec1 in get1 loop select rec1.ent_cr_amt,rec1.ent_db_amt into :ENT_CR_AMT ,:ENT_DB_AMT from dual; --show_message('acc '\|\|rec1.ent_acct_no\|\|' cc '\|\|rec1.cc_id\|\|' cr '\|\|rec1.ent_cr_amt\|\|' db '\|\|rec1.ent_db_amt); for rec2 in get2 (rec1.ent_acct_no,rec1.cc_id,rec1.TXN_ent_cr_amt,rec1.TXN_ent_db_amt) loop --show_message('second '\|\|rec2.ent_voh_no); select rec1.ent_cr_amt,rec1.ent_db_amt into :ENT_CR_AMT ,:ENT_DB_AMT from dual; select rec2.ent_voh_no,rec1.ent_acct_no into :res.voh_no,:res.acct from dual; begin select DECODE(:GLOBAL.LANG,'L',voh_name_l,'A',VOH_NAME_A) into :voh_code from voucher_type where voh_code = rec2.ent_voh_code and com_no = :global.com_no ; exception when no_data_found then :voh_code:='Not Defined'; end; :VO_CODE_NUMBER:=rec2.ent_voh_code; begin select DECODE(:GLOBAL.LANG,'L',cc_name_l,'A',CC_NAME_A) into :cc_name from cost_center where cc_id = rec1.cc_id and com_no = :global.com_no ; exception when no_data_found then :cc_name:='Not Defined'; end; begin select DECODE(:GLOBAL.LANG,'L',acct_name_l,'A',ACCT_NAME_A) into :acct_name from account_master where acct_no = rec1.ent_acct_no and com_no = :global.com_no ; exception when no_data_found then :acct_name:='Not Defined'; end; OPEN GET_CURR(REC2.ent_voh_no,REC2.ent_voh_code); FETCH GET_CURR INTO :CURRENCY_N; CLOSE GET_CURR; if REC1.TXN_ENT_DB_AMT > 0 then :amount := ACCP_CUR.NUM_FORMAT(TO_NUMBER(REC1.TXN_ENT_DB_AMT),15,GET_DEC (:CURRENCY_N),0,'L'); elsif REC1.TXN_ENT_CR_AMT > 0 then :amount :=ACCP_CUR.NUM_FORMAT(TO_NUMBER(REC1.TXN_ENT_CR_AMT),15,GET_DEC (:CURRENCY_N),0,'L'); end if; next_record; end loop; end loop; first_record; END; Dec 11, 2008 issue 1 (Problem on installing plug in with NetBeans 6.5Beta) commented on by alexan...@sumsoftsolutions.com - Modify file <NetBeans Folder>/etc/netbeans.conf Modify file <NetBeans Folder>/etc/netbeans.conf Dec 11, 2008 issue 1 (Problem on installing plug in with NetBeans 6.5Beta) commented on by nunok7 - hi there, were do i put those parameters? hi there, were do i put those parameters? Dec 05, 2008 Version_History (PL/SQL Editor Version History) Wiki page edited by alexan...@sumsoftsolutions.com Dec 05, 2008 Version_History (PL/SQL Editor Version History) Wiki page edited by alexan...@sumsoftsolutions.com Dec 05, 2008 Version_History (PL/SQL Editor Version History) Wiki page edited by alexan...@sumsoftsolutions.com Dec 05, 2008 Version_History (PL/SQL Editor Version History) Wiki page edited by alexan...@sumsoftsolutions.com Dec 05, 2008 Version_History (PL/SQL Editor Version History) Wiki page edited by alexan...@sumsoftsolutions.com Dec 05, 2008 Version_History (PL/SQL Editor Version History) Wiki page edited by alexan...@sumsoftsolutions.com Dec 05, 2008 Feedbacks (Please leave your feedback here commenting this page.) Wiki page edited by alexan...@sumsoftsolutions.com Dec 05, 2008 Version_History (PL/SQL Editor Version History) Wiki page edited by alexan...@sumsoftsolutions.com Dec 05, 2008 Version_History (PL/SQL Editor Version History.) Wiki page added by alexan...@sumsoftsolutions.com Dec 05, 2008 VersionHistory Wiki page deleted by alexan...@sumsoftsolutions.com Dec 05, 2008 VersionHistory Wiki page edited by alexan...@sumsoftsolutions.com Dec 05, 2008 VersionHistory Wiki page edited by alexan...@sumsoftsolutions.com Dec 05, 2008 VersionHistory (One-sentence summary of this page.) Wiki page added by alexan...@sumsoftsolutions.com Dec 05, 2008 pl_sql-editor-bundle_1.1.zip (PL/SQL Editor bundle v1.1) file uploaded by alexan...@sumsoftsolutions.com - Labels: Featured Labels: Featured Dec 05, 2008 r102 (Help corrected.) committed by alexan...@sumsoftsolutions.com - Help corrected. Help corrected. Dec 05, 2008 r101 (All plug-in modules are signed now) committed by alexan...@sumsoftsolutions.com - All plug-in modules are signed now All plug-in modules are signed now Dec 05, 2008 r100 (fixes) committed by alexan...@sumsoftsolutions.com - fixes fixes Dec 05, 2008 issue 3 (PL/SQL editor does not support JOIN expressions) Status changed by alexan...@sumsoftsolutions.com - Will be fixed in the version 1.1 Status: Fixed Will be fixed in the version 1.1 Status: Fixed Dec 05, 2008 issue 3 (PL/SQL editor does not support JOIN expressions) changed by alexan...@sumsoftsolutions.com - Status: Started Cc: alexan...@sumsoftsolutions.com Status: Started Cc: alexan...@sumsoftsolutions.com Dec 04, 2008 issue 2 (Problem on installing plug in with NetBeans 6.5Beta) Status changed by alexan...@sumsoftsolutions.com - Status: Done Status: Done Dec 04, 2008 issue 1 (Problem on installing plug in with NetBeans 6.5Beta) Status changed by alexan...@sumsoftsolutions.com - Status: Done Status: Done Nov 27, 2008 issue 1 (Problem on installing plug in with NetBeans 6.5Beta) commented on by gbuffolino - ok guys thanks a lot! It works fine now! ok guys thanks a lot! It works fine now! Nov 26, 2008 r99 (No more questions where to save PL/SQL objects) committed by alexan...@sumsoftsolutions.com - No more questions where to save PL/SQL objects No more questions where to save PL/SQL objects Nov 26, 2008 issue 1 (Problem on installing plug in with NetBeans 6.5Beta) commented on by cybermaxx95 - Consider setting "-J-Xmx512m" option for launcher modifying /etc/.conf file. Means you must increase the amount of memory allocated for the virtual marchine with the "-J-Xmx512m" parameters Consider setting "-J-Xmx512m" option for launcher modifying /etc/.conf file. Means you must increase the amount of memory allocated for the virtual marchine with the "-J-Xmx512m" parameters Oct 22, 2008 issue 3 (PL/SQL editor does not support JOIN expressions) reported by Alexey.Kyurshunov - What steps will reproduce the problem? Add portion of code to PL/SQL editor such as: SELECT k.ID INTO v_komp_id FROM P_PRODUCT p JOIN P_KOMP k ON p.ID = k.FK_PROD_ID; What is the expected output? I expect no errors behaviour. What do you see instead? Editor shows error: separator expected. What version of the product are you using? On what operating system? Windows XP Pro, NetBeans 6.5 RC1, pl_sql-editor_1.0 Please provide any additional information below. See attached file: join.JPG What steps will reproduce the problem? Add portion of code to PL/SQL editor such as: SELECT k.ID INTO v_komp_id FROM P_PRODUCT p JOIN P_KOMP k ON p.ID = k.FK_PROD_ID; What is the expected output? I expect no errors behaviour. What do you see instead? Editor shows error: separator expected. What version of the product are you using? On what operating system? Windows XP Pro, NetBeans 6.5 RC1, pl_sql-editor_1.0 Please provide any additional information below. See attached file: join.JPG Sep 30, 2008 issue 2 (Problem on installing plug in with NetBeans 6.5Beta) commented on by gbuffolino - Yes, i fixed the problem. Thanks a lot, guys Yes, i fixed the problem. Thanks a lot, guys Sep 30, 2008 issue 2 (Problem on installing plug in with NetBeans 6.5Beta) commented on by LMogolane - Thanks, I had the same problem and now fixed. Thanks, I had the same problem and now fixed. Sep 29, 2008 issue 2 (Problem on installing plug in with NetBeans 6.5Beta) commented on by mauro.edgar - It works.... It works.... Sep 18, 2008 issue 1 (Problem on installing plug in with NetBeans 6.5Beta) commented on by rnwoodward - See issue 2 See issue 2 Sep 18, 2008 issue 2 (Problem on installing plug in with NetBeans 6.5Beta) commented on by rnwoodward - Try doing what it says; in the folder where netbeans is installed, there is a folder called etc. In that folder there is a file called netbeans.conf. Open this folder with a text editor, find the line with other options of type -J-X... and add the option -J-Xmx512m Then start netbeans, it should work Try doing what it says; in the folder where netbeans is installed, there is a folder called etc. In that folder there is a file called netbeans.conf. Open this folder with a text editor, find the line with other options of type -J-X... and add the option -J-Xmx512m Then start netbeans, it should work Sep 17, 2008 r98 (Connection form bug fixed) committed by alexan...@sumsoftsolutions.com - Connection form bug fixed Connection form bug fixed Sep 10, 2008 issue 2 (Problem on installing plug in with NetBeans 6.5Beta) reported by gbuffolino - What steps will reproduce the problem? 1. just install the plug in as specified What version of the product are you using? On what operating system? using pl_sql-editor-bundle_1.0 on windows XP service pack 2 Please provide any additional information below. the error message is: org.netbeans.InvalidException: StandardModule:org.netbeans.modules.languages.pl_sql jarFile: C:\Documents and Settings\g.buffolino\.netbeans\6.5beta\modules\org-netbeans-modules-languages-pl_sql.jar: java.lang.IllegalStateException: PL/SQL Language module required at least 500MB heap memory size. Found only 404MB. Consider setting "-J-Xmx512m" option for launcher modifying /etc/.conf file. note that i have disable all the optional plugins Thanx in advance for your reply What steps will reproduce the problem? 1. just install the plug in as specified What version of the product are you using? On what operating system? using pl_sql-editor-bundle_1.0 on windows XP service pack 2 Please provide any additional information below. the error message is: org.netbeans.InvalidException: StandardModule:org.netbeans.modules.languages.pl_sql jarFile: C:\Documents and Settings\g.buffolino\.netbeans\6.5beta\modules\org-netbeans-modules-languages-pl_sql.jar: java.lang.IllegalStateException: PL/SQL Language module required at least 500MB heap memory size. Found only 404MB. Consider setting "-J-Xmx512m" option for launcher modifying /etc/.conf file. note that i have disable all the optional plugins Thanx in advance for your reply Sep 10, 2008 issue 1 (Problem on installing plug in with NetBeans 6.5Beta) reported by gbuffolino - What steps will reproduce the problem? 1. just install the plug in as specified What version of the product are you using? On what operating system? using pl_sql-editor-bundle_1.0 on windows XP service pack 2 Please provide any additional information below. the error message is: org.netbeans.InvalidException: StandardModule:org.netbeans.modules.languages.pl_sql jarFile: C:\Documents and Settings\g.buffolino\.netbeans\6.5beta\modules\org-netbeans-modules-languages-pl_sql.jar: java.lang.IllegalStateException: PL/SQL Language module required at least 500MB heap memory size. Found only 404MB. Consider setting "-J-Xmx512m" option for launcher modifying /etc/.conf file. note that i have disable all the optional plugins Thanx in advance for your reply What steps will reproduce the problem? 1. just install the plug in as specified What version of the product are you using? On what operating system? using pl_sql-editor-bundle_1.0 on windows XP service pack 2 Please provide any additional information below. the error message is: org.netbeans.InvalidException: StandardModule:org.netbeans.modules.languages.pl_sql jarFile: C:\Documents and Settings\g.buffolino\.netbeans\6.5beta\modules\org-netbeans-modules-languages-pl_sql.jar: java.lang.IllegalStateException: PL/SQL Language module required at least 500MB heap memory size. Found only 404MB. Consider setting "-J-Xmx512m" option for launcher modifying /etc/.conf file. note that i have disable all the optional plugins Thanx in advance for your reply Aug 26, 2008 Feedbacks Wiki page edited by alexan...@sumsoftsolutions.com Aug 12, 2008 expansion_possibilities Wiki page edited by alexan...@sumsoftsolutions.com Aug 12, 2008 expansion_possibilities Wiki page edited by alexan...@sumsoftsolutions.com Aug 12, 2008 expansion_possibilities Wiki page added by alexan...@sumsoftsolutions.com Aug 02, 2008 r94 (author and url for module added) committed by alexan...@sumsoftsolutions.com - author and url for module added author and url for module added Aug 02, 2008 Feedbacks Wiki page edited by alexan...@sumsoftsolutions.com Aug 02, 2008 Feedbacks Wiki page edited by alexan...@sumsoftsolutions.com Aug 02, 2008 Feedbacks Wiki page edited by alexan...@sumsoftsolutions.com

issue 3 (PL/SQL editor does not support JOIN expressions) commented on by hu8297da - how 2 RT this code in sql query (report) ------------------------------------ PROCEDURE get_vouchers IS cursor get1 is select ent_acct_no,cc_id,ent_cr_amt,ent_db_amt,TXN_ent_cr_amt,TXN_ent_db_amt from posted_entry where (trunc(ent_voh_date) between nvl(:f_date,ent_voh_date) and nvl (:t_date,ent_voh_date)) and (ent_acct_no = :acc_no or :acc_no is null) and (cc_id = :cost_center or :cost_center is null) and (ent_voh_code = 1) and (com_no = :global.com_no) group by ent_acct_no,cc_id,ent_cr_amt,ent_db_amt,TXN_ent_cr_amt,TXN_ent_db_amt having (count(1) > 1) order by 3,4; db number(12,2); cr number(12,2); cursor get2(acc varchar,cc varchar,cr number ,db number) is select distinct ent_voh_no,ent_voh_code from posted_entry where ent_acct_no = acc and cc_id = cc and nvl(TXN_ent_cr_amt,0) = nvl(cr,0) and nvl(TXN_ENT_db_amt,0) = nvl(db,0) and com_no = :global.com_no; CURSOR GET_CURR (P_VOH_NO NUMBER,P_VOH_CODE VARCHAR2) IS SELECT CURRENCY FROM VOUCHER_MASTER WHERE VOUCHER_NO = P_VOH_NO AND VOUCHER_CODE = P_VOH_CODE AND COM_NO = :GLOBAL.COM_NO; BEGIN --show_message('ent_acct_no = '||:acc_no||' cc_id = '||:cost_center||' :f_date '||:f_date||' :t_date '||:t_date); for rec1 in get1 loop select rec1.ent_cr_amt,rec1.ent_db_amt into :ENT_CR_AMT ,:ENT_DB_AMT from dual; --show_message('acc '||rec1.ent_acct_no||' cc '||rec1.cc_id||' cr '||rec1.ent_cr_amt||' db '||rec1.ent_db_amt); for rec2 in get2 (rec1.ent_acct_no,rec1.cc_id,rec1.TXN_ent_cr_amt,rec1.TXN_ent_db_amt) loop --show_message('second '||rec2.ent_voh_no); select rec1.ent_cr_amt,rec1.ent_db_amt into :ENT_CR_AMT ,:ENT_DB_AMT from dual; select rec2.ent_voh_no,rec1.ent_acct_no into :res.voh_no,:res.acct from dual; begin select DECODE(:GLOBAL.LANG,'L',voh_name_l,'A',VOH_NAME_A) into :voh_code from voucher_type where voh_code = rec2.ent_voh_code and com_no = :global.com_no ; exception when no_data_found then :voh_code:='Not Defined'; end; :VO_CODE_NUMBER:=rec2.ent_voh_code; begin select DECODE(:GLOBAL.LANG,'L',cc_name_l,'A',CC_NAME_A) into :cc_name from cost_center where cc_id = rec1.cc_id and com_no = :global.com_no ; exception when no_data_found then :cc_name:='Not Defined'; end; begin select DECODE(:GLOBAL.LANG,'L',acct_name_l,'A',ACCT_NAME_A) into :acct_name from account_master where acct_no = rec1.ent_acct_no and com_no = :global.com_no ; exception when no_data_found then :acct_name:='Not Defined'; end; OPEN GET_CURR(REC2.ent_voh_no,REC2.ent_voh_code); FETCH GET_CURR INTO :CURRENCY_N; CLOSE GET_CURR; if REC1.TXN_ENT_DB_AMT > 0 then :amount := ACCP_CUR.NUM_FORMAT(TO_NUMBER(REC1.TXN_ENT_DB_AMT),15,GET_DEC (:CURRENCY_N),0,'L'); elsif REC1.TXN_ENT_CR_AMT > 0 then :amount :=ACCP_CUR.NUM_FORMAT(TO_NUMBER(REC1.TXN_ENT_CR_AMT),15,GET_DEC (:CURRENCY_N),0,'L'); end if; next_record; end loop; end loop; first_record; END;

how 2 RT this code in sql query (report) ------------------------------------ PROCEDURE get_vouchers IS cursor get1 is select ent_acct_no,cc_id,ent_cr_amt,ent_db_amt,TXN_ent_cr_amt,TXN_ent_db_amt from posted_entry where (trunc(ent_voh_date) between nvl(:f_date,ent_voh_date) and nvl (:t_date,ent_voh_date)) and (ent_acct_no = :acc_no or :acc_no is null) and (cc_id = :cost_center or :cost_center is null) and (ent_voh_code = 1) and (com_no = :global.com_no) group by ent_acct_no,cc_id,ent_cr_amt,ent_db_amt,TXN_ent_cr_amt,TXN_ent_db_amt having (count(1) > 1) order by 3,4; db number(12,2); cr number(12,2); cursor get2(acc varchar,cc varchar,cr number ,db number) is select distinct ent_voh_no,ent_voh_code from posted_entry where ent_acct_no = acc and cc_id = cc and nvl(TXN_ent_cr_amt,0) = nvl(cr,0) and nvl(TXN_ENT_db_amt,0) = nvl(db,0) and com_no = :global.com_no; CURSOR GET_CURR (P_VOH_NO NUMBER,P_VOH_CODE VARCHAR2) IS SELECT CURRENCY FROM VOUCHER_MASTER WHERE VOUCHER_NO = P_VOH_NO AND VOUCHER_CODE = P_VOH_CODE AND COM_NO = :GLOBAL.COM_NO; BEGIN --show_message('ent_acct_no = '||:acc_no||' cc_id = '||:cost_center||' :f_date '||:f_date||' :t_date '||:t_date); for rec1 in get1 loop select rec1.ent_cr_amt,rec1.ent_db_amt into :ENT_CR_AMT ,:ENT_DB_AMT from dual; --show_message('acc '||rec1.ent_acct_no||' cc '||rec1.cc_id||' cr '||rec1.ent_cr_amt||' db '||rec1.ent_db_amt); for rec2 in get2 (rec1.ent_acct_no,rec1.cc_id,rec1.TXN_ent_cr_amt,rec1.TXN_ent_db_amt) loop --show_message('second '||rec2.ent_voh_no); select rec1.ent_cr_amt,rec1.ent_db_amt into :ENT_CR_AMT ,:ENT_DB_AMT from dual; select rec2.ent_voh_no,rec1.ent_acct_no into :res.voh_no,:res.acct from dual; begin select DECODE(:GLOBAL.LANG,'L',voh_name_l,'A',VOH_NAME_A) into :voh_code from voucher_type where voh_code = rec2.ent_voh_code and com_no = :global.com_no ; exception when no_data_found then :voh_code:='Not Defined'; end; :VO_CODE_NUMBER:=rec2.ent_voh_code; begin select DECODE(:GLOBAL.LANG,'L',cc_name_l,'A',CC_NAME_A) into :cc_name from cost_center where cc_id = rec1.cc_id and com_no = :global.com_no ; exception when no_data_found then :cc_name:='Not Defined'; end; begin select DECODE(:GLOBAL.LANG,'L',acct_name_l,'A',ACCT_NAME_A) into :acct_name from account_master where acct_no = rec1.ent_acct_no and com_no = :global.com_no ; exception when no_data_found then :acct_name:='Not Defined'; end; OPEN GET_CURR(REC2.ent_voh_no,REC2.ent_voh_code); FETCH GET_CURR INTO :CURRENCY_N; CLOSE GET_CURR; if REC1.TXN_ENT_DB_AMT > 0 then :amount := ACCP_CUR.NUM_FORMAT(TO_NUMBER(REC1.TXN_ENT_DB_AMT),15,GET_DEC (:CURRENCY_N),0,'L'); elsif REC1.TXN_ENT_CR_AMT > 0 then :amount :=ACCP_CUR.NUM_FORMAT(TO_NUMBER(REC1.TXN_ENT_CR_AMT),15,GET_DEC (:CURRENCY_N),0,'L'); end if; next_record; end loop; end loop; first_record; END;

Older