Home » SQL & PL/SQL » SQL & PL/SQL » Data Not insert into table from PROCEDURE (oracle 9i,forms61,XP)
Data Not insert into table from PROCEDURE [message #411548] Sun, 05 July 2009 02:49 Go to next message
sanjeev.sharda
Messages: 14
Registered: December 2008
Location: Ludhiana
Junior Member

hello friends

i want to insert data into a table so i write this procedure but i m unable to insert data. i did't get any complie error so i did't understand wht i write wrong so pls help me

thx in advance


PROCEDURE P_Sale_Comm IS
V_DueBill v_due%rowtype;
v_SaleBillDtl sale_bill_dtl%rowtype;
V_SaleAgent agent_party%rowtype;
v_gross_amount number;
v_total_bill_items number;
v_other_charges number;
v_sale_return_amount number;
v_Sale_amt_for_commission number;
v_comm_per_amt number;
v_comm_qty_amt number;
v_net_amt number;
V_Comm_rate number;
V_comm_Qty number;
V_fix_amt number;
BEGIN
Declare
CURSOR C_DueBill is
Select *
from v_due
where due_amt<=0 AND (BANK_AMT>0 OR CASH_AMT>0)
and acc_code in (Select unique acc_code from agent_party)
and comp_code||bill_ser||bill_no not in ( Select comp_code||ser_abv||bill_no
From sale_bill_dtl
where dtl_pk in ( Select sale_dtl_pk
from Sale_commission_dtl
)
)
order by comp_code,bill_ser,bill_no;
Begin
Open c_DueBill;
LOOP
FETCH C_DueBill INTO V_DueBill;
EXIT WHEN C_DueBill%NOTFOUND;

Select nvl(GROSS_AMT,0)
into v_gross_amount
from sale_bill_hdr
where comp_code = V_DueBill.comp_code
and ser_abv = V_DueBill.bill_ser
and bill_no = V_DueBill.bill_no;

Select count(*)
into v_total_bill_items
from sale_bill_dtl
where comp_code = V_DueBill.comp_code
and ser_abv = V_DueBill.bill_ser
and bill_no = V_DueBill.bill_no;

if v_total_bill_items>1 then
v_other_charges:=round((V_DueBill.tds_amt+V_DueBill.other_amt)/v_total_bill_items,0);
elsif :v_due.d_tot_items=1 then
v_other_charges:=V_DueBill.tds_amt+V_DueBill.other_amt;
else
v_other_charges:=0;
end if;

Declare
CURSOR C_SaleBillDtl is
Select *
from sale_bill_dtl
where comp_code = V_DueBill.comp_code
and ser_abv = V_DueBill.bill_ser
and bill_no = V_DueBill.bill_no
order by dtl_pk;
Begin
Open C_SaleBillDtl;
LOOP
FETCH C_SaleBillDtl INTO v_SaleBillDtl;
EXIT WHEN C_SaleBillDtl%NOTFOUND;

Select nvl(net_amt,0)
into v_sale_return_amount
from sale_return_dtl
where sale_bill_dtl_pk = v_SaleBillDtl.dtl_pk;

Declare
CURSOR C_SaleAgent is
Select *
from agent_party
Where acc_code = V_DueBill.acc_code
and V_DueBill.bill_date between ACTIVE_FROM_DATE and ACTIVE_TO_DATE
order by agent_code;
Begin
Open C_SaleAgent;
LOOP
FETCH C_SaleAgent INTO V_SaleAgent;
EXIT WHEN C_SaleAgent%NOTFOUND;

if V_SaleAgent.FIX_COMMISSION_RULE=1 then
v_Sale_amt_for_commission:=v_SaleBillDtl.amount-v_other_charges-v_sale_return_amount;
v_comm_per_amt:=round(v_Sale_amt_for_commission*V_SaleAgent.commission_rate/100,0);
V_comm_Qty:=0;
v_comm_qty_amt:=0;
V_fix_amt:=0;
v_net_amt:=v_comm_per_amt+v_comm_qty_amt+V_fix_amt;
elsif V_SaleAgent.FIX_COMMISSION_RULE=2 then
v_Sale_amt_for_commission:=0;
v_comm_per_amt:=0;
V_comm_Qty:=v_SaleBillDtl.qty;
v_comm_qty_amt:=V_comm_Qty*V_SaleAgent.commission_rate;
V_fix_amt:=0;
v_net_amt:=v_comm_per_amt+v_comm_qty_amt+V_fix_amt;
elsif V_SaleAgent.FIX_COMMISSION_RULE=3 then
v_Sale_amt_for_commission:=0;
v_comm_per_amt:=0;
V_comm_Qty:=0;
v_comm_qty_amt:=0;
V_fix_amt:=V_SaleAgent.commission_rate;
v_net_amt:=v_comm_per_amt+v_comm_qty_amt+V_fix_amt;
end if;

insert into SALE_COMMISSION_DTL values
(
V_DueBill.comp_code,
v_SaleBillDtl.dtl_pk,
V_SaleAgent.agent_code,
v_Sale_amt_for_commission,
V_SaleAgent.fix_commission_rule,
V_SaleAgent.commission_rate,
v_comm_per_amt,
v_comm_qty,
v_comm_qty_amt,
v_fix_amt,
v_net_amt
);
post;
END LOOP;
close C_SaleAgent;
Exception when no_data_found then exit;
End;
END LOOP;
close C_SaleBillDtl;
Exception when no_data_found then exit;
End;
END LOOP;
close c_DueBill;
End;
END;
Re: Data Not insert into table from PROCEDURE [message #411551 is a reply to message #411548] Sun, 05 July 2009 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous SQL topic:
Michel Cadot wrote on Sat, 24 January 2009 15:58
Use SQL*Plus and copy and paste your session.
Before read OraFAQ Forum Guide, especially "How to format your post?" section
and format your answer like Littlefoot did it for your question.

Regards
Michel


Re: Data Not insert into table from PROCEDURE [message #411606 is a reply to message #411548] Mon, 06 July 2009 00:04 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Put some breakpoints and understand whether your Insert Statement is getting called or not, See Whether its going into Cursor Loop or not,

Put Dbms_output.put_line('testing....') after each statements to determine whether its getting failed or not !!

And do Format your code while posting
Re: Data Not insert into table from PROCEDURE [message #411645 is a reply to message #411548] Mon, 06 July 2009 03:33 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
you missed Commit I guess Shocked
Re: Data Not insert into table from PROCEDURE [message #411647 is a reply to message #411548] Mon, 06 July 2009 03:38 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

you missed Commit I guess  


He really missed Commit ( but there is a possibility that he uses a front end which will take care of committing)

But have you tried putting messages between all your statements....

I believe OP wants a ready made solution...In that case you provide DDL/DML
Re: Data Not insert into table from PROCEDURE [message #411653 is a reply to message #411548] Mon, 06 July 2009 04:11 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
That post command isn't going to do anything.
I assume this is a forms procedure. In forms post is used to apply changes to datablocks to the database. It isn't needed for direct dml (inserts, updates, deletes).
Previous Topic: Query to find parent - child records
Next Topic: How to find the occurance of non numeric characters in number column.
Goto Forum:
  


Current Time: Fri Dec 09 21:17:06 CST 2016

Total time taken to generate the page: 0.09214 seconds