Home » Developer & Programmer » Reports & Discoverer » Tricky Report (Reports 6i)
Tricky Report [message #500755] Wed, 23 March 2011 07:38 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

I have one view which returns the number of receipts from ledger
along with age_Days and receipt qty.I want to traverse this view to find out receipts which are lesser than stock ending qty.I want to consider those receipts which are below stock balance and if the last receipt qty is greater than stock balance qty it must take only partial qty of reciept to tally with stock balance.

In the attached image there is stock balance of 1150 for one item
and there are 4 receipts from whom full qty is taken for ageing purpose and since the last receipt has 500 only 150 is taken in order to tally the stock balance.How i can do it.


select  BSL_H_SYS_ID TXN_H_SYS,
         bsl_I_sys_id TXN_I_SYS
        ,bsl_dt TXN_DT,BSL_TXN_CODE||'-'||BSL_NO TXN_DOC ,
         TRUNC(SYSDATE)-TRUNC(BSL_dT) AGE_DAYS
         ,bsl_qty TXN_QTY,bsl_item_code TXN_ITEM
         ,sum(bsl_qty) over (
                                  partition by bsl_item_Code
                                 order by bsl_i_sys_id
                                ) TXN_BAL
   from os_batch_stk_ledger
         WHERE BSL_RCVD_ISSD='R'
--         AND BSL_ITEM_CODE='12PAB38030006000'
           order by 
           BSL_H_SYS_ID,bsl_I_sys_id
         ,bsl_dt,BSL_QTY,BSL_ITEM_CODE 
					



  • Attachment: STK_aGE.png
    (Size: 29.82KB, Downloaded 241 times)
Re: Tricky Report [message #500842 is a reply to message #500755] Thu, 24 March 2011 00:08 Go to previous messageGo to next message
Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates
Senior Member
Hai,

How u categorize the qty under different age 0-90 , 91-180 , 181-270..........

Regards,
Paules
Re: Tricky Report [message #519868 is a reply to message #500842] Wed, 17 August 2011 05:31 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Based on total receipts qty
Re: Tricky Report [message #520415 is a reply to message #500755] Mon, 22 August 2011 06:39 Go to previous message
dude4084
Messages: 222
Registered: March 2005
Location: Mux
Senior Member
Hi
Since you have asked this question in rpoerts forms, so i suggest you to use "Formula Column" to attain your this target.

I dont have your data/tables so cant give you anymore idea.

I have made aging report of accounts receiveable in this format.


Good Luck.

-Dude

Previous Topic: Hiding Reports Server window
Next Topic: Fake columns in Matrix
Goto Forum:
  


Current Time: Mon Apr 06 04:57:10 CDT 2020