求ERP的物料入库数，跪拜、跪拜

hangzhao

如果只关心入库数
rcv_transactions        

Po.Rcv_Receiving_Sub_Ledger

串一下呢

xjc845

谁能给个正确的脚本哦？

xjc845

create or replace procedure XF_INV_SUM_DELIVER(errbuf                  out varchar2  ,
                                            retcode                 out varchar2,
                                            p_org_id                in  varchar2 ,
                                            p_name                  in varchar2,
                                            p_date_from               in  varchar2,
                                            p_date_to                 in  varchar2
                                                                                   ) is
l_header varchar2(1000);
l_seperate varchar2(1000);
l_line varchar2(1000);
l_org_id number;
l_name varchar2(50);
l_date_from  date;
l_date_to   date;
l_org_name varchar2(40);


begin
l_org_id:=to_number(p_org_id);
select organization_name into l_org_name from org_organization_definitions where organization_id =l_org_id;
l_date_from := to_date(p_date_from,'YYYY/MM/DD HH24:MI:SS') ;
l_date_to := to_date(p_date_to,'YYYY/MM/DD HH24:MI:SS') ;
if p_date_from is not null and p_date_to is not null and p_date_from > p_date_to then
        fnd_file.put_line(fnd_file.log,'Parameter error!!p_date_from>p_date_to!') ;
        retcode := '2' ;
        return ;
end if ;

select fu.description into l_name
    from fnd_user fu
    where fu.user_name=nvl(p_name,fu.user_name)
    and rownum<2;
  
   
fnd_file.put_line(fnd_file.output,lpad('供应商入库对帐单',80,' '));
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,lpad('组织名称:'||rpad(rtrim(ltrim(l_org_name)),20,' '),40,' '));
fnd_file.put_line(fnd_file.output,lpad('报检者:  '||rpad(rtrim(ltrim(l_name)),40,' '),60,' '));
fnd_file.put_line(fnd_file.output,' ');


l_header:=rpad('组织',12,' ')||chr(9)
          ||rpad('订单号',10,' ')||chr(9)
          ||rpad('行号',7,' ')||chr(9)
          ||rpad('接收号',10,' ')||chr(9)
          ||rpad('物料编码',15,' ')||chr(9)
          ||rpad('物料描述',50,' ')||chr(9)
          ||rpad('最后处理日期',14,' ')||chr(9)
          ||rpad('供应商名称',80,' ')||chr(9)
          ||rpad('供应商地点',15,' ')||chr(9)
          ||rpad('仓库保管员',20,' ')||chr(9)
          ||rpad('入库数量',15,' ');
l_seperate:=rpad('-',12,'-')||chr(9)
            ||rpad('-',10,'-')||chr(9)
            ||rpad('-',7,'-')||chr(9)
            ||rpad('-',10,'-')||chr(9)
            ||rpad('-',15,'-')||chr(9)
            ||rpad('-',50,'-')||chr(9)
            ||rpad('-',14,'-')||chr(9)
            ||rpad('-',80,'-')||chr(9)
            ||rpad('-',15,'-')||chr(9)
            ||rpad('-',20,'-')||chr(9)
            ||rpad('-',15,'-');


fnd_file.put_line(fnd_file.output,l_header);
fnd_file.put_line(fnd_file.output,l_seperate);

---------------------------------------------------------------------------


for l in(

select decode(other.org_id,81,'电器',82,'家电',595,'制冷') 组织,
       other.订单号,
       other.行号,
       other.接收号,
       other.最后处理日期,
       other.物料编码,
       other.物料描述,
       other.供应商名称,
       other.供应商地点,
       fuser.description 仓库保管员,
       sum(deliver.quantity-nvl(deltorec.quantity,0)+nvl(correct1.quantity,0)) 入库数量
         
from
------------------------------------------------------------
(--other
SELECT rsh1.shipment_header_id shipment_header_id,
       rsl1.item_id item_id,
       pha1.po_header_id,
       pla1.po_line_id,
       rsl1.shipment_line_id shipment_line_id,
       pha1.org_id,
       pv.vendor_name 供应商名称,
       pvsa.vendor_site_code 供应商地点,
       pha1.segment1 订单号,
       pla1.line_num 行号,
       rsh1.receipt_num 接收号,
       msib.segment1 物料编码,
       msib.description 物料描述,
       max(rt1.transaction_date) 最后处理日期
FROM RCV_TRANSACTIONS RT1,
     RCV_SHIPMENT_HEADERS RSH1,
     rcv_shipment_lines rsl1,
     po_headers_all pha1,
     po_lines_all pla1,
     po_line_locations_all plla1,
     po_distributions_all pda1,
     po_vendors pv,
     po_vendor_sites_all pvsa,
     mtl_system_items_b msib
WHERE rsh1.shipment_header_id=rsl1.shipment_header_id(+)
and rt1.shipment_header_id=rsh1.shipment_header_id
and  rt1.shipment_line_id=rsl1.shipment_line_id
and  pha1.po_header_id=pla1.po_header_id
and  pla1.po_line_id=plla1.po_line_id
and  plla1.line_location_id=pda1.line_location_id
and rt1.po_line_id=pla1.po_line_id
and  pha1.vendor_id=pv.vendor_id
and  pha1.vendor_site_id=pvsa.vendor_site_id
and  pla1.item_id=msib.inventory_item_id
and  pla1.org_id=msib.organization_id
and  pla1.org_id=l_org_id
--and  trunc(rt1.transaction_date) between l_date_from and l_date_to
--and rsh1.receipt_num='6235'
group by rsh1.shipment_header_id,
         rsl1.shipment_line_id,
         rsl1.item_id,
         pha1.po_header_id,
         pla1.po_line_id,
         pv.vendor_name,
          pha1.org_id,
        pvsa.vendor_site_code,
        pha1.segment1 ,
        pla1.line_num,
        rsh1.receipt_num ,
        msib.segment1 ,
        msib.description
         ) other,

(--将已入库的数进行汇总-------------改过-----
SELECT rsh1.shipment_header_id shipment_header_id,
       rsl1.item_id item_id,
       pha1.po_header_id,
       pla1.po_line_id,
       rsl1.shipment_line_id shipment_line_id,
       sum(RT1.QUANTITY) QUANTITY
FROM RCV_TRANSACTIONS RT1,
     RCV_SHIPMENT_HEADERS RSH1,
     rcv_shipment_lines rsl1,
     po_headers_all pha1,
     po_lines_all pla1
WHERE RT1.TRANSACTION_TYPE ='DELIVER'
and   rsh1.shipment_header_id=rsl1.shipment_header_id(+)
and rt1.shipment_header_id=rsh1.shipment_header_id
and  rt1.shipment_line_id=rsl1.shipment_line_id
and  pha1.po_header_id=pla1.po_header_id
and rt1.po_line_id=pla1.po_line_id
and  trunc(rt1.transaction_date) between l_date_from and l_date_to
and pla1.org_id=l_org_id
group by rsh1.shipment_header_id ,
       rsl1.item_id ,
       pha1.po_header_id,
       pla1.po_line_id,
       rsl1.shipment_line_id
         ) deliver,        
------------------------------------------------------------------
(--return to receiving from deliver
SELECT SUM(RT2.QUANTITY) QUANTITY,
       RSL2.ITEM_ID ITEM_ID,
       rsh2.shipment_header_id shipment_header_id,
       rsl2.shipment_line_id shipment_line_id
FROM RCV_TRANSACTIONS RT2,
     RCV_TRANSACTIONS RT22,
     RCV_SHIPMENT_HEADERS RSH2,
     RCV_SHIPMENT_LINES RSL2
WHERE RT2.TRANSACTION_TYPE='RETURN TO RECEIVING'
and rt2.parent_transaction_id=rt22.transaction_id
and rt22.transaction_type ='DELIVER'
and rt2.shipment_line_id=rsl2.shipment_line_id
AND RT2.SHIPMENT_HEADER_ID=RSH2.SHIPMENT_HEADER_ID
AND RT2.SHIPMENT_LINE_ID=RSL2.SHIPMENT_LINE_ID
--and  trunc(rt2.transaction_date) between l_date_from and l_date_to
and  rsh2.organization_id=l_org_id
GROUP BY rsh2.shipment_header_id,
         rsl2.shipment_line_id,
         RSL2.ITEM_ID
        ) deltorec,
------------------------------------------------------------------
(--将从入库更正回检验数量进行汇总correct1
select sum(rt3.quantity) quantity,
       rsl3.shipment_line_id shipment_line_id,
       rsl3.item_id item_id,
       rsh3.shipment_header_id shipment_header_id
from rcv_transactions rt3,
     rcv_transactions rt33,
     rcv_shipment_headers rsh3,
     rcv_shipment_lines rsl3
where rsh3.shipment_header_id=rsl3.shipment_header_id
and   rt3.shipment_line_id=rsl3.shipment_line_id
and   rt3.transaction_type='CORRECT'
and   rt3.destination_type_code='INVENTORY'
AND   RT33.TRANSACTION_TYPE='DELIVER'
and   rt3.shipment_header_id=rsh3.shipment_header_id
AND   rt3.parent_transaction_id=rt33.transaction_id
--and  trunc(rt3.transaction_date) between l_date_from and l_date_to
and   rsh3.organization_id=l_org_id
group by rsl3.shipment_line_id,
         rsh3.shipment_header_id,
         rsl3.item_id
                         ) correct1,
(select rsh.shipment_header_id,
       fu.description
from rcv_shipment_headers rsh,                        
     fnd_user fu            
where rsh.created_by=fu.user_id      
and   fu.description=l_name  ) fuser     
------------------------------------------------------------
where other.shipment_header_id=deliver.shipment_header_id(+)
and   other.item_id=deliver.item_id(+)
and   other.shipment_line_id=deliver.shipment_line_id(+)--
and  other.shipment_header_id=deltorec.shipment_header_id(+)
and   other.item_id=deltorec.item_id(+)
and   other.shipment_line_id=deltorec.shipment_line_id(+)--
and   other.shipment_header_id=correct1.shipment_header_id(+)
and   other.item_id=correct1.item_id(+)
and   other.shipment_line_id=correct1.shipment_line_id(+)--
and   other.shipment_header_id=fuser.shipment_header_id
group by decode(other.org_id,81,'电器',82,'家电',595,'制冷'),
       other.订单号,
       other.行号,
       other.接收号,
       other.最后处理日期,
       other.物料编码,
       other.物料描述,
       other.供应商名称,
       other.供应商地点,
       fuser.description
having   nvl(sum(deliver.quantity-nvl(deltorec.quantity,0)+nvl(correct1.quantity,0)),0)<>0      
order by 组织,
         other.供应商名称,
         other.订单号,
         other.物料编码,
         other.接收号
         ) loop
         
l_line:=rpad(l.组织,12,' ')||chr(9)
        ||rpad(l.订单号,10,' ')||chr(9)
        ||rpad(l.行号,7,' ')||chr(9)
        ||rpad(l.接收号,10,' ')||chr(9)
        ||rpad(l.物料编码,15,' ')||chr(9)
        ||rpad(l.物料描述,50,' ')||chr(9)
        ||rpad(to_char(l.最后处理日期,'yyyy-mm-dd'),14,' ')||chr(9)
        ||rpad(l.供应商名称,80,' ')||chr(9)
        ||rpad(l.供应商地点,15,' ')||chr(9)
        ||rpad(l.仓库保管员,20,' ')||chr(9)
        ||rpad(l.入库数量,15,' ');      
         

      
fnd_file.put_line(fnd_file.output,l_line);      
end loop;

exception
           when others then
        retcode := '2' ;
        errbuf := sqlcode||': '||sqlerrm ;
        fnd_file.put_line(fnd_file.log,errbuf) ;
        
end XF_INV_SUM_DELIVER;

xjc845

select pha.org_id 组织,
       pv.vendor_name 供应商,
       pha.segment1 订单号,
       rsh.receipt_num 接收号,
       msib.segment1 物料编码,
       msib.description 物料描述,
       msib.primary_unit_of_measure 单位,
       pla.quantity 订单数量,           
       rsl.quantity_received 接收数量,
       mmt.transaction_quantity 入库数量,
       mmt.subinventory_code 子库存,
       mil.segment1||'.'||mil.segment2 货位,
       mil.description 货位描述,      
       nvl(cic.material_cost,0) 价格,
       mmt.transaction_quantity*nvl(cic.material_cost,0) 金额,
       mmt.transaction_date 日期,
       decode(mmt.transaction_type_id,18,'PO 接收',36,'向供应商退货',71,'PO接收调整','其他') 原因
from mtl_material_transactions mmt,--物料交易表
     rcv_transactions rt,
     po_headers_all pha,  
     po_lines_all pla,     
     mtl_item_locations      mil,
     mtl_system_items_b msib,
     cst_item_costs          cic,
     po_vendors     pv,     
     rcv_shipment_headers rsh,       --接收发送行表头
     rcv_shipment_lines   rsl         --接收发送行表行
     
where pha.org_id=mmt.organization_id
and mil.organization_id=mmt.organization_id
and mil.subinventory_code=mmt.subinventory_code
and mil.inventory_location_id=mmt.locator_id


and msib.organization_id=mmt.organization_id
and msib.inventory_item_id=mmt.inventory_item_id

and cic.organization_id=msib.organization_id
and cic.inventory_item_id=msib.inventory_item_id
and cic.cost_type_id=1
  
and pha.vendor_id=pv.vendor_id
  
  and pha.authorization_status='APPROVED'
  and pha.org_id=pla.org_id
  and pha.po_header_id=pla.po_header_id  --订单行与订单头
  
  and mmt.rcv_transaction_id=rt.transaction_id
  and rt.shipment_header_id=rsh.shipment_header_id
  and rt.shipment_line_id=rsl.shipment_line_id

  
and  mmt.transaction_source_id=pha.po_header_id --与订单头ID关联
--and  mmt.transaction_type_id=18       --po接收
--and  mmt.transaction_action_id =27     --接收至库存
and  mmt.transaction_source_type_id=1  --采购订单

and rsh.shipment_header_id=rsl.shipment_header_id
and rsl.po_header_id=pha.po_header_id
and rsl.po_line_id=pla.po_line_id
and rsl.po_header_id=pla.po_header_id


--and pha.segment1=4568 --订单号
--and rsh.receipt_num=72179
--and pha.org_id=595
and mmt.transaction_date>=to_date('2010-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS') --限定时间
and mmt.transaction_date<=to_date('2010-10-30 23:59:59','YYYY-MM-DD HH24:MI:SS') --限定时间
order by pha.segment1, rsh.receipt_num
这个是正确的，可以结贴了