Home » SQL & PL/SQL » SQL & PL/SQL » balance of stock amount (10.2.0.3, windows)
balance of stock amount [message #571716] Thu, 29 November 2012 07:46 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear All,

i want to get balanced amount based on max(suid ).

my query is

create or replace procedure prc_stk_upd_pur(v_comp_suid in i_purchase_h.pih_company_suid%type,
                                            v_area_suid in i_purchase_h.pih_area_suid%type,
                                            v_invoice_dt in i_purchase_h.pih_invoice_date%type,
                 v_pih_suid  in i_purchase_h.pih_suid%type,
                 v_type in i_purchase_h.pih_type%type) is
 begin
   for i in (
  select  v_invoice_dt as pih_invoice_date,
                       ipd.pid_lot_suid as pid_lot_suid,
                       ml.lot_lc_suid as lot_lc_suid,
                       v_pih_suid as pih_suid,
                       v_type as pih_type,
                       v_comp_suid as pih_company_suid,
                       v_area_suid as pih_area_suid,
                       ipd.pid_suid as pid_suid,
                       ipd.pid_rate_usd as pid_rate_usd,
                       ipd.pid_rate_lcurn as pid_rate_lcurn,
                       nvl(ipd.pid_quantity,0) as pid_quantity,
                       0,
                       NVL(tmp2.cur_stk_qty, 0)+ nvl(ipd.pid_quantity, 0) qty
                       from
                       i_purchase_d ipd
                       inner join m_lot ml on ipd.pid_lot_suid = ml.lot_suid
                       left join
                       (select
                       se2.seff_lot_suid lot_suid,
                       sum(se2.seff_current_stock) cur_stk_qty
                       from
                       (
                        select
                          se1.seff_lot_suid lot_suid,
                          max(se1.seff_suid) seff_suid
                        from
                           o_stock_effects se1
                           inner join i_purchase_d pd1 on se1.seff_lot_suid = pd1.pid_lot_suid
                                 and se1.seff_pid_suid=pd1.pid_suid
                                 and se1.seff_tran_suid=pd1.pid_pih_suid
                                 and pd1.isactive = 'Y'
                             where
                           pd1.pid_pih_suid =v_pih_suid
                        group by
                          se1.seff_lot_suid, se1.seff_pid_suid
                      )tmp1
                    inner join o_stock_effects se2 on tmp1.seff_suid = se2.seff_suid
          group by se2.seff_lot_suid
          )tmp2 on ipd.pid_lot_suid = tmp2.lot_suid
          and ipd.isactive = 'Y'
           where 1 = 1
          and ipd.pid_pih_suid=v_pih_suid)
     loop
                          insert into o_stock_effects
                          (
           seff_inv_date,
                          seff_lot_suid,
                          seff_loc_suid,
                          seff_tran_suid,
                          seff_tran_type,
                          seff_comp_suid,
                          seff_area_suid,
          seff_rate_usd,
          seff_rate_lcurn,
          seff_pid_suid,
                          seff_stock_in,
                          seff_stock_out,
                          seff_current_stock
            )
                          values
                          (
          i.pih_invoice_date,
                          i.pid_lot_suid,
                          i.lot_lc_suid,
                          i.pih_suid,
                          i.pih_type,
                          i.pih_company_suid,
                          i.pih_area_suid,
          i.pid_rate_usd,
          i.pid_rate_lcurn,
          i.pid_suid,
          i.pid_quantity,
                          null,
                          i.qty
           );
           end loop;
               end;


my output is

   	SEFF_SUID	SEFF_INV_DATE	SEFF_LOT_SUID	SEFF_LOC_SUID	SEFF_TRAN_SUID	SEFF_TRAN_TYPE	SEFF_COMP_SUID	SEFF_AREA_SUID	SEFF_STOCK_IN	SEFF_STOCK_OUT	SEFF_CURRENT_STOCK	CREATE_USER	CREATE_TIME	SEFF_RATE_USD	SEFF_RATE_LCURN	SEFF_PID_SUID
1	1214	11/29/2012	1317	15	1346	LP	11	1157	20.000		20.000	SYSTEM	29-NOV-12 06.53.45.000000 PM	116.1410	7665.3060	1382
2	1215	11/29/2012	1317	15	1346	LP	11	1157	55.000		55.000	SYSTEM	29-NOV-12 06.53.45.000000 PM	116.1409	7665.3000	1383
3	1216	11/29/2012	1317	15	1346	LP	11	1157	30.000		30.000	SYSTEM	29-NOV-12 06.53.45.000000 PM	0.4407	29.0840	1384



my output should come


   	SEFF_SUID	SEFF_INV_DATE	SEFF_LOT_SUID	SEFF_LOC_SUID	SEFF_TRAN_SUID	SEFF_TRAN_TYPE	SEFF_COMP_SUID	SEFF_AREA_SUID	SEFF_STOCK_IN	SEFF_STOCK_OUT	SEFF_CURRENT_STOCK	CREATE_USER	CREATE_TIME	SEFF_RATE_USD	SEFF_RATE_LCURN	SEFF_PID_SUID
1	1214	11/29/2012	1317	15	1346	LP	11	1157	20.000		20.000	SYSTEM	29-NOV-12 06.53.45.000000 PM	116.1410	7665.3060	1382
2	1215	11/29/2012	1317	15	1346	LP	11	1157	55.000		75.000	SYSTEM	29-NOV-12 06.53.45.000000 PM	116.1409	7665.3000	1383
3	1216	11/29/2012	1317	15	1346	LP	11	1157	30.000		105.000	SYSTEM	29-NOV-12 06.53.45.000000 PM	0.4407	29.0840	1384



please help me out on this query

Regards
Ishika
Re: balance of stock amount [message #571717 is a reply to message #571716] Thu, 29 November 2012 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: balance of stock amount [message #571718 is a reply to message #571717] Thu, 29 November 2012 08:09 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Michel,

I have given my procedure and its output. What else u require? do u need table data or what?
Re: balance of stock amount [message #571719 is a reply to message #571718] Thu, 29 November 2012 08:34 Go to previous message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes. Isn't what I posted clear?

Regards
Michel
Previous Topic: missing values in database
Next Topic: Displaying N number of rows from a large result set
Goto Forum:
  


Current Time: Thu Oct 23 17:16:43 CDT 2014

Total time taken to generate the page: 0.10787 seconds