`
sanyecao2314
  • 浏览: 135793 次
  • 性别: Icon_minigender_1
  • 来自: 西安
社区版块
存档分类
最新评论

存储过程

 
阅读更多
CREATE OR REPLACE PACKAGE CURSPKG AS
TYPE refCursorType IS REF CURSOR;

procedure sp_Page(p_PageSize       int, --每页记录数
                    p_PageNo         int, --当前页码,从 1 开始
                    p_SqlSelect      varchar2, --查询语句,含排序部分
                    p_SqlCount       varchar2, --获取记录总数的查询语句
                    p_OutRecordCount out int, --返回总记录数
                    test out varchar,
                    p_OutCursor      out refCursorType);

--4.1客户信息校验接口
procedure YG001_Login(CustomerinId in varchar,
                      p_PageNo in int,
                      Rd1 in varchar,
                      Rd2 in varchar,
                      test out varchar,
                      p_OutCursor out refCursorType);

procedure YG002_GetSecCustInfo(CustomerinId in varchar,
                      p_PageNo in int,
                      Rd1 in varchar,
                      Rd2 in varchar,
                      test out varchar,
                      p_OutCursor out refCursorType);

procedure YG003_GetCustInfo(p_PageNo in int,
                      Rd1 in varchar,
                      Rd2 in varchar,
                      test out varchar,
                      p_OutCursor out refCursorType); 


procedure YG101_GetProductInfo(p_PageNo in int,
                      Rd1 in varchar,
                      Rd2 in varchar,
                      test out varchar,
                      p_OutCursor out refCursorType);

---4.5企业产品分类日终同步接口D00100
procedure YG100_GetCategoryInfo(p_PageNo in int,
                      Rd1 in varchar,
                      Rd2 in varchar,
                      test out varchar,
                      p_OutCursor out refCursorType) ;


procedure YG404_InsertPayInfo(SecCustomerId  in  varchar,
                      SecCustomerName  in  varchar,
                      PaymentId  in  varchar,
                      PaymentDate  in  varchar,
                      PayMethod  in  varchar,
                      PayAmount  in  varchar,
                      Bank  in  varchar,
                      AccountNo  in  varchar,
                      Remark  in  varchar,
                      Rd1  in  varchar,
                      Rd2  in  varchar,
                      icbcSecCustomerId out varchar,
                      icbcPaymentId out varchar);

procedure YG301_InsertOnlineOrder(partPopedom_xml in clob,test out varchar,res out int);

procedure YG302_GetOrderInfo(OrderId in varchar,
                      Rd1 in varchar,
                      Rd2 in varchar,
                      test out varchar,
                      p_OutCursor out refCursorType);

procedure YG501_GetCustomerBalance(customerid in varchar,
                      startdate in varchar,
                      enddate in varchar,
                      test out clob,
                       p_OutCursor out refCursorType);

end;

 

 

 

create or replace package body CURSPKG is

procedure sp_Page(p_PageSize       int, --每页记录数
                    p_PageNo         int, --当前页码,从 1 开始
                    p_SqlSelect      varchar2, --查询语句,含排序部分
                    p_SqlCount       varchar2, --获取记录总数的查询语句
                    p_OutRecordCount out int, --返回总记录数
                    test out varchar,
                    p_OutCursor      out refCursorType) is
    v_sql       varchar2(3000);
    v_count     int;
    v_heiRownum int;
    v_lowRownum int;
begin

    ----取记录总数
    execute immediate p_SqlCount
      into v_count;
    p_OutRecordCount := v_count;
    ----执行分页查询
    v_heiRownum := p_PageNo * p_PageSize;
    v_lowRownum := v_heiRownum - p_PageSize + 1;

    v_sql := 'SELECT * FROM (SELECT A.*, rownum rn
    FROM (' || p_SqlSelect || ') A
    WHERE rownum <= ' || to_char(v_heiRownum) || ') B
    WHERE rn >= ' || to_char(v_lowRownum);
    --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn

    OPEN p_OutCursor FOR v_sql;

     EXCEPTION
    WHEN OTHERS THEN
      DBMS_output.PUT_LINE(SQLERRM);
      test:=SQLERRM;

end sp_Page;

--4.1客户信息校验接口
procedure YG001_Login(CustomerinId in varchar,
                      p_PageNo in int,
                      Rd1 in varchar,
                      Rd2 in varchar,
                      test out varchar,
                      p_OutCursor out refCursorType)is
    v_sql       varchar2(3000);
    v_count     int;

begin
    v_sql := 'select distinct nvl(a.fnumber,'''') CustomerId,nvl(a.fname_l2,'''') CustomerName,nvl(c.FContactPerson,'''') ContactName,nvl(c.FMobile,'''') CustomerTel from t_bd_customer a
left join t_bd_customersaleinfo b on a.fid = b.fcustomerid
left join T_BD_CustomerLinkMan c on b.fid = c.FCustomerSaleID
where a.fparentid is null  and  a.fnumber=''' || CustomerinId || '''' ;
    sp_page(p_pagesize => 100,
                  p_pageno => p_PageNo,
                  p_sqlselect => v_sql,
                  p_sqlcount => 'select 1 from dual ',
                  p_outrecordcount => v_count,
                  test => test,
                  p_outcursor => p_OutCursor);
     test := v_sql;

      EXCEPTION
    WHEN OTHERS THEN
      DBMS_output.PUT_LINE(SQLERRM);
      test:=SQLERRM;

end YG001_Login;

procedure YG002_GetSecCustInfo(CustomerinId in varchar,
                      p_PageNo in int,
                      Rd1 in varchar,
                      Rd2 in varchar,
                      test out varchar,
                      p_OutCursor out refCursorType) is
    v_sql       varchar2(3000);
    v_count     int;

begin
 v_sql := 'select distinct nvl(a.fnumber,'''') CustomerId,0 CustomerDebt ,
    nvl(sec.fnumber,'''') SecCustomerId,nvl(sec.fname_l2,'''') SecCustomerName,
    nvl(c.FContactPerson,'''') SecContactName,nvl(c.FMobile,'''') SecCustomerTel ,
     0 SecCustomerDebt ,nvl(e.fname_l2,'''') Province,nvl(f.fname_l2,'''') Area
from t_bd_customer a
left join t_bd_customer sec on sec.fparentid = a.fid
left join t_bd_customersaleinfo b on sec.fid = b.fcustomerid
left join T_BD_CustomerLinkMan c on b.fid = c.FCustomerSaleID
left join T_BD_Province e on sec.FProvince = e.fid
left join T_BD_Region f on sec.FRegionID = f.fid
where a.fcontrolunitid in (''00000000-0000-0000-0000-000000000000CCE7AED4'',''sREAAAAAx2PM567U'') and b.fcontrolunitid in (''00000000-0000-0000-0000-000000000000CCE7AED4'',''sREAAAAAx2PM567U'')  and a.fnumber=''' || CustomerinId || '''' ;
    sp_page(p_pagesize => 100,
                  p_pageno => p_PageNo,
                  p_sqlselect => v_sql,
                  p_sqlcount => 'select 1 from dual ',
                  p_outrecordcount => v_count,
                  test => test,
                  p_outcursor => p_OutCursor);
     test := v_sql;

      EXCEPTION
    WHEN OTHERS THEN
      DBMS_output.PUT_LINE(SQLERRM);
      test:=SQLERRM;

end YG002_GetSecCustInfo;

procedure YG003_GetCustInfo(p_PageNo in int,
                      Rd1 in varchar,
                      Rd2 in varchar,
                      test out varchar,
                      p_OutCursor out refCursorType) is
    v_sql       varchar2(6000);
    v_count     int;

begin
    v_sql := 'select distinct nvl(a.fnumber,'''') CustomerId,nvl(a.fname_l2,'''') CustomerName ,
       nvl(e.fname_l2,'''') Province,nvl(f.fname_l2,'''') Area ,
    nvl(sec.fnumber,'''') ParentId,nvl(sec.fname_l2,'''') ParentName,
    nvl(c.FContactPerson,'''') ContactName,nvl(c.FMobile,'''') CustomerTel,
    nvl(e.fname_l2,'''') fProvince,nvl(f.fname_l2,'''') fArea
from t_bd_customer a
left join t_bd_customer sec on a.fparentid = sec.fid
left join t_bd_customersaleinfo b on a.fid = b.fcustomerid
left join T_BD_CustomerLinkMan c on b.fid = c.FCustomerSaleID
left join T_BD_Province e on a.FProvince = e.fid
left join T_BD_Region f on a.FRegionID = f.fid
where a.fcontrolunitid in (''00000000-0000-0000-0000-000000000000CCE7AED4'',''sREAAAAAx2PM567U'') and b.fcontrolunitid in (''00000000-0000-0000-0000-000000000000CCE7AED4'',''sREAAAAAx2PM567U'')  ' ;
      sp_page(p_pagesize => 100,
                  p_pageno => p_PageNo,
                  p_sqlselect => v_sql,
                  p_sqlcount => 'select 1 from dual ',
                  p_outrecordcount => v_count,
                  test => test,
                  p_outcursor => p_OutCursor);
     test := v_sql;

      EXCEPTION
    WHEN OTHERS THEN
      DBMS_output.PUT_LINE(SQLERRM);
      test:=SQLERRM;

end YG003_GetCustInfo;


procedure YG101_GetProductInfo(p_PageNo in int,
                      Rd1 in varchar,
                      Rd2 in varchar,
                      test out varchar,
                      p_OutCursor out refCursorType) is
    v_sql       varchar2(3000);
    v_count     int;

begin
    v_sql := 'select nvl(a.fnumber,'''') ProductId,nvl(a.fname_l2,'''') ProductName,nvl(FModel,'''') Specification,a.falias falias,
nvl(unit1.fname_l2,'''') Unit,unit2.fname_l2 baseUnit,a.FHelpCode StandardLen ,nvl(mgroup.fnumber,'''') CategoryId,
nvl(a.fname_l2,'''') Brand
 from t_bd_material a
 left join T_BD_MeasureUnit unit1 on a.FAssistUnit = unit1.fid
 left join T_BD_MeasureUnit unit2 on a.FBaseUnit = unit2.fid
 left join T_BD_MaterialGroup mgroup on a.FMaterialGroupID = mgroup.fid
left join T_BD_MaterialGroupStandard c on mgroup.FGroupStandard = c.fid and c.FStandardType=1 
left join T_BD_MaterialSales d on d.FMaterialID = a.fid 
where a.FStatus = 1 and a.fcontrolunitid in (''00000000-0000-0000-0000-000000000000CCE7AED4'',''sREAAAAAx2PM567U'') 
 and d.fcontrolunitid = ''sREAAAAAx2PM567U'' ';
    sp_page(p_pagesize => 100,
                  p_pageno => p_PageNo,
                  p_sqlselect => v_sql,
                  p_sqlcount => 'select 1 from dual ',
                  p_outrecordcount => v_count,
                  test => test,
                  p_outcursor => p_OutCursor);
     test := v_sql;

      EXCEPTION
    WHEN OTHERS THEN
      DBMS_output.PUT_LINE(SQLERRM);
      test:=SQLERRM;

end YG101_GetProductInfo;

---4.5企业产品分类日终同步接口D00100
procedure YG100_GetCategoryInfo(p_PageNo in int,
                      Rd1 in varchar,
                      Rd2 in varchar,
                      test out varchar,
                      p_OutCursor out refCursorType) is
    v_sql       varchar2(3000);
    v_count     int;

begin
    v_sql := 'select distinct nvl(a.fnumber,'''') CategoryId,nvl(a.fname_l2,'''') CategoryName,nvl(a.FLevel,'''') fLevel, case when b.fid is null then 1 else 0 end  HasChild
from T_BD_MaterialGroup a
left join  T_BD_MaterialGroup b on b.FParentID = a.fid and b.FDeletedStatus =1
left join T_BD_MaterialGroupStandard c on a.FGroupStandard = c.fid and c.FStandardType=1 
where a.FDeletedStatus =1  and a.fcontrolunitid in (''00000000-0000-0000-0000-000000000000CCE7AED4'',''sREAAAAAx2PM567U'') ' ;
    sp_page(p_pagesize => 100,
                  p_pageno => p_PageNo,
                  p_sqlselect => v_sql,
                  p_sqlcount => 'select 1 from dual ',
                  p_outrecordcount => v_count,
                  test => test,
                  p_outcursor => p_OutCursor);
     test := v_sql;

      EXCEPTION
    WHEN OTHERS THEN
      DBMS_output.PUT_LINE(SQLERRM);
      test:=SQLERRM;

end YG100_GetCategoryInfo;

--工行提交订单接口D00301
/*procedure YG301_InsertOnlineOrder_old(OrderId  in  varchar,
                       CustomerId  in  varchar,
                       SecCustomerId  in  varchar,
                       SecCustomerName  in  varchar,
                       CreateTime  in  varchar,
                       Address  in  varchar,
                       ShipMethod  in  varchar,
                       Contact  in  varchar,
                       ContactNum  in  varchar,
                       ToDate  in  varchar,
                       Remark  in  varchar,
                       Rd1  in  varchar,
                       Rd2  in  varchar,
                       OrderEntry  in  varchar,
                       ProductId  in  varchar,
                       ProductName  in  varchar,
                       Specification  in  varchar,
                       Unit  in  varchar,
                       StandardLen  in  varchar,
                       icbcCount  in  varchar,
                       res out int)is
    v_sql       varchar2(3000);
    v_count     int;

begin
    v_sql := 'select 1 from dual ' ;

  \*  IF OrderEntry=1 THEN
             ;
      ELSE
      ;
     END IF;*\


end YG301_InsertOnlineOrder_old;   */


procedure YG302_GetOrderInfo(OrderId in varchar,
                      Rd1 in varchar,
                      Rd2 in varchar,
                      test out varchar,
                      p_OutCursor out refCursorType) is
    v_sql       varchar2(3000);
    v_count     int;

begin
    v_sql := 'select a.cforderid OrderId,a.cfbillstate billstatus,
  c.fnumber OrderCode,
  c.FBaseStatus OrderStatus,
  c.FCreateTime CreateTime,
  c.FBizDate BusinessDate,
h.fnumber SecCustomerId,
h.fname_l2 SecCustomerName,
i.fnumber CompanyId,
i.fname_l2 CompanyName,
c.FTotalAmount SumTotal,
c.FLinkMan Contact,
c.FCustomerPhone ContactNum,
c.fdescription Remark,
f.fnumber DeliveryOrderId,
d.fseq OrderEntry,
j.fnumber ProductId,
j.fname_l2 ProductName,
j.FModel Specification,
k.fname_l2 Unit,
j.FHelpCode StandardLen,
d.FQty fCount,
d.FPlanDeliveryQty ArrangedQty,
d.FTotalIssueBaseQty OutedQty
from CT_CUS_ICBC_SALEORDER a
left outer join T_BOT_Relation b on a.fid = b.fsrcobjectid
left outer join  T_SD_SaleOrder c on b.fdestobjectid = c.fid
left outer join t_sd_saleorderentry d on c.fid = d.fparentid
left outer join T_BOT_Relation e on c.fid = e.fsrcobjectid
left outer join T_DT_CarryBill f on e.fdestobjectid = f.fid
left outer join T_BD_Customer h on c.FOrderCustomerID = h.fid
left outer join T_ORG_Sale i on c.fsaleorgunitid = i.fid
left outer join t_bd_material j on j.fid = d.fmaterialid
left outer join T_BD_MeasureUnit k on j.FAssistUnit = k.fid
left outer join T_BD_MeasureUnit l on j.FBaseUnit = l.fid
where a.cforderid=''' || OrderId || ''' order by c.fnumber,d.fseq ' ;
    sp_page(p_pagesize => 100,
                  p_pageno => 1,
                  p_sqlselect => v_sql,
                  p_sqlcount => 'select 1 from dual ',
                  p_outrecordcount => v_count,
                  test => test,
                  p_outcursor => p_OutCursor);
     test := v_sql;

     EXCEPTION
    WHEN OTHERS THEN
      DBMS_output.PUT_LINE(SQLERRM);
      test:=SQLERRM;

end YG302_GetOrderInfo;


procedure YG404_InsertPayInfo(SecCustomerId  in  varchar,
                      SecCustomerName  in  varchar,
                      PaymentId  in  varchar,
                      PaymentDate  in  varchar,
                      PayMethod  in  varchar,
                      PayAmount  in  varchar,
                      Bank  in  varchar,
                      AccountNo  in  varchar,
                      Remark  in  varchar,
                      Rd1  in  varchar,
                      Rd2  in  varchar,
                      icbcSecCustomerId out varchar,
                      icbcPaymentId out varchar)is
    v_sql       varchar2(3000);
    v_count     int;
    exist       int;

begin

select count(fid) into exist from CT_CUS_Icbc_payment where cfpaymentid = paymentid ;

if exist is not null and exist > 0 then
   icbcSecCustomerId := 'error.billno exist';
else
    insert into CT_CUS_Icbc_payment(fid,CFSecCustomerId,cfPaymentId,
cfPaymentDate,cfPayMethod,cfPayAmount,cfBank,cfAccountNo,cfRemark,cfRd1,cfRd2,
FCREATORID,FCREATETIME,FLASTUPDATEUSERID,FLASTUPDATETIME,FCONTROLUNITID)
values(newbosid('B33B6367'),
''||SecCustomerId||'',
''||PaymentId||'',
''||PaymentDate||'',
''||PayMethod||'',
''||PayAmount||'',
''||Bank||'',
''||AccountNo||'',
''||Remark||'',
''||Rd1||'',
''||Rd2||'',
'256c221a-0106-1000-e000-10d7c0a813f413B7DE7F',
sysdate,'256c221a-0106-1000-e000-10d7c0a813f413B7DE7F',
sysdate,'00000000-0000-0000-0000-000000000000CCE7AED4');

commit;
icbcSecCustomerId := SecCustomerId;
icbcPaymentId := PaymentId;
end if;

Exception

When others then

Dbms_output.put_line(sqlcode||sqlerrm(sqlcode));
icbcPaymentId := SQLERRM;


end YG404_InsertPayInfo;



procedure YG301_InsertOnlineOrder(partPopedom_xml in clob,test out varchar,res out int)
as 
        OrderId  varchar2(100);
        CustomerId  varchar2(100);
        SecCustomerId  varchar2(100);
        SecCustomerName  varchar2(100);
        CreateTime  varchar2(100);
        Address  varchar2(100);
        ShipMethod  varchar2(100);
        Contact  varchar2(100);
        ContactNum  varchar2(100);
        ToDate  varchar2(100);
        Remark  varchar2(100);
        Rd1  varchar2(100);
        Rd2  varchar2(100);
        
        OrderEntry  varchar2(100);
        ProductId  varchar2(100);
        ProductName  varchar2(100);
        Specification  varchar2(100);
        Unit  varchar2(100);
        StandardLen  varchar2(100);
        icbcCount  varchar2(100);
        materialid varchar2(100);
        PARENTID varchar2(100);
        exist int;
        fcustmoerid varchar2(100);

--XML解析器 
 xmlPar XMLPARSER.parser := XMLPARSER.NEWPARSER; 
 --//DOM文档对象 
 doc xmldom.DOMDocument; 
 len Integer; 
 personNodes xmldom.DOMNodeList; 
 chilNodes xmldom.DOMNodeList; 
 tempNode xmldom.DOMNode; 
 tempArrMap xmldom.DOMNamedNodeMap; 
 --================================ 
 --以下变量用于获取XML节点的值 
/* partNum varchar2(50); --角色编号
 menuNum varchar2(50); --菜单编号
 operateNum varchar2(50); --操作编号*/
 tmp Integer;
rootnode    dbms_xmldom.domnode; 
--================
BEGIN
 xmlPar := xmlparser.newParser;
 xmlparser.parseClob(xmlPar,partPopedom_xml);
 doc := xmlparser.getDocument(xmlPar);
 -- 释放解析器实例 
 xmlparser.freeParser(xmlPar);
 
 personNodes := xmldom.getElementsByTagName(doc, 'ProductInfo'); 
 len := xmldom.getLength( personNodes );

 
  SELECT EXTRACTVALUE(VALUE(t),'/in/OrderId'),
         EXTRACTVALUE(VALUE(t),'/in/CustomerId'),
         EXTRACTVALUE(VALUE(t),'/in/SecCustomerId'),
         EXTRACTVALUE(VALUE(t),'/in/SecCustomerName'),
         EXTRACTVALUE(VALUE(t),'/in/CreateTime'),
         EXTRACTVALUE(VALUE(t),'/in/Address'),
         EXTRACTVALUE(VALUE(t),'/in/ShipMethod'),
         EXTRACTVALUE(VALUE(t),'/in/Contact'),   
         EXTRACTVALUE(VALUE(t),'/in/ContactNum'),  
         EXTRACTVALUE(VALUE(t),'/in/ToDate'),  
         EXTRACTVALUE(VALUE(t),'/in/Remark'),  
         EXTRACTVALUE(VALUE(t),'/in/Rd1'),  
         EXTRACTVALUE(VALUE(t),'/in/Rd2')      
           INTO OrderId,
           CustomerId,
           SecCustomerId,
           SecCustomerName,
           CreateTime,
           Address,
           ShipMethod,
           Contact,
           ContactNum,
           ToDate,
           Remark,
           Rd1,
           Rd2   
    FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(partPopedom_xml), '/in'))) t; 
 
 select count(fid) into exist from CT_CUS_ICBC_SALEORDER where CFORDERID = OrderId ;
 
select fid into fcustmoerid from T_BD_Customer where fnumber=SecCustomerId and fcontrolunitid in ('00000000-0000-0000-0000-000000000000CCE7AED4','sREAAAAAB9LM567U');

if exist is not null and exist > 0 then 
   test := 'error.billno exist';
else 
 
 PARENTID := newbosid('D1F43888');
 --insert maintable data 
insert into CT_CUS_ICBC_SALEORDER(fid,CFORDERID,CFICBCCUSTOMERID,
CFSECCUSTOMERID,CFICBCCREATETIME,CFADDRESS,CFSHIPMETHOD,CFCONTACT,
CFCONTACTNUM,CFTODATE,CFREMARK,CFRD1,CFRD2,FBIZDATE,CFREQDATE,
CFCUSTOMERID,
FCREATORID,FCREATETIME,FLASTUPDATEUSERID,FLASTUPDATETIME,FCONTROLUNITID)  
values(PARENTID,
''||OrderId||'',
''||CustomerId||'',
''||SecCustomerId||'',
''||CreateTime||'',
''||Address||'',
''||ShipMethod||'',
''||Contact||'',
''||ContactNum||'',
''||ToDate||'',
''||Remark||'',
''||Rd1||'',
''||Rd2||'',
sysdate,
to_timestamp(ToDate,'''yyyyMMdd'''),
''||fcustmoerid||'',
'256c221a-0106-1000-e000-10d7c0a813f413B7DE7F',
sysdate,'256c221a-0106-1000-e000-10d7c0a813f413B7DE7F',
sysdate,'00000000-0000-0000-0000-000000000000CCE7AED4');
 
 --遍历所有PERSON元素 
 FOR i in 0..len-1 
   LOOP 
     --获取第i个 
     tempNode := xmldom.item(personNodes, i); 
     --所有属性 
     tempArrMap := xmldom.getAttributes(tempNode); 
     --获取PERSONID的值 
     --pid := xmldom.getNodeValue(xmldom.getNamedItem(tempArrMap,'POPEDOM')); 
     --获取子元素的值 
     chilNodes := xmldom.getChildNodes(tempNode); 
     tmp := xmldom.GETLENGTH(chilNodes); 
     OrderEntry := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 0))); 
     ProductId := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 1))); 
     ProductName := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 2)));
     Specification := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 3))); 
     Unit := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 4))); 
     StandardLen := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 5))); 
     icbcCount := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 6)));  
     --translate number to id 
     select fid into materialid from t_bd_material where fnumber=ProductId and fcontrolunitid in ('00000000-0000-0000-0000-000000000000CCE7AED4','sREAAAAAB9LM567U');
     --插入数据 
     insert into CT_CUS_ICBC_SALEORDERENTRY(fid,FPARENTID,FSEQ,CFORDERENTRY,CFPRODUCTID,CFSTANDARDLEN,CFUNIT,CFCOUNT,Cfnum,CFMATERIALID)  
            values(newbosid('C201F4CA'),
                 ''||PARENTID||'',
                 i+1,
                 ''||OrderEntry||'',
                 ''||ProductId||'',
                 ''||StandardLen||'',
                 ''||Unit||'',
                 icbcCount,
                 icbcCount,
                 ''||materialid||'');
     
END LOOP; 
    
    COMMIT; 
    
    end if ;
   -- 释放文档对象 
   xmldom.freeDocument(doc); 
   EXCEPTION 
    WHEN OTHERS THEN 
      DBMS_output.PUT_LINE(SQLERRM); 
      test:=SQLERRM;
 END YG301_InsertOnlineOrder;

procedure YG501_GetCustomerBalance(customerid in varchar,
                      startdate in varchar,
                      enddate in varchar,
                      test out clob,
                       p_OutCursor out refCursorType) is
    v_sql       clob;
    v_count     int;

begin
    v_sql := 'select fcussnum,fcussname,sum(FStartBalanceAmt) FStartBalanceAmt, sum(FDebitAmt) FDebitAmt,  sum(FCreditAmt) FCreditAmt,  sum(FBalanceAmt) FBalanceAmt
from (
  select  t4.FName_l2 FCurrency,  t2.FId FCussTypeId, t2.FName_l2 FCussType
, t3.FNumber FCussNum, t3.FName_l2 FCussName, t3.FId FCussAcctId, t1.FCurrencyId FCurrencyId
,  sum(t1.FBeginBalanceFor) FStartBalanceAmt, 0 FDebitAmt, 0 FCreditAmt, 0 FBalanceAmt
  from t_ar_arBalance t1 left join t_bd_AsstActType t2 on t2.FId = t1.FAsstActTypeId
 inner join t_bd_customer t3 on t3.FId = t1.FAsstActId left join t_bd_currency t4 on t4.FId = t1.FCurrencyId
 where 1 = 1 and FBalType IN (70,100) and (t1.FAsstActTypeId = ''YW3xsAEJEADgAAUWwKgTB0c4VZA='')  and t1.FCompanyid = ''sREAAAAAx2PM567U''
 and t1.FPeriodId = ''sREAAAAmbTOCOIxM''
 group by   t2.FId, t2.FName_l2 , t3.FNumber, t3.FName_l2 , t1.FAsstActId, t4.FName_l2 , t1.FCurrencyId, t3.FId
 union all
 select  t4.FName_l2 FCurrency,  t2.FId FCussTypeId, t2.FName_l2 FCussType
, t3.FNumber FCussNum, t3.FName_l2 FCussName, t3.FId FCussAcctId, t1.FCurrencyId FCurrencyId
, -1 * sum(t1.FBeginBalanceFor) FStartBalanceAmt, 0 FDebitAmt, 0 FCreditAmt, 0 FBalanceAmt
  from t_ap_apBalance t1 left join t_bd_AsstActType t2 on t2.FId =  t1.FAsstActTypeId
 inner join t_bd_customer t3 on t3.FId = t1.FAsstActId left join t_bd_currency t4 on t4.FId = t1.FCurrencyId
 where 1 = 1  and FBalType IN (70,100) and (t1.FAsstActTypeId = ''YW3xsAEJEADgAAUWwKgTB0c4VZA='')  and t1.FCompanyid = ''sREAAAAAx2PM567U''
 and t1.FPeriodId = ''sREAAAAmbTOCOIxM''
 group by   t2.FId, t2.FName_l2 , t3.FNumber, t3.FName_l2 , t1.FAsstActId, t4.FName_l2 , t1.FCurrencyId, t3.FId
 union all
  select  t4.FName_l2 FCurrency,  t2.FId FCussTypeId, t2.FName_l2 FCussType
, t3.FNumber FCussNum, t3.FName_l2 FCussName, t3.FId FCussAcctId, t1.FCurrencyId FCurrencyId
,  sum(t1.FAmounts) FStartBalanceAmt, 0 FDebitAmt, 0 FCreditAmt, 0 FBalanceAmt
  from T_AR_InitBadAccount t1 left join t_bd_AsstActType t2 on t2.FId = t1.FAsstActTypeID
 left join t_bd_customer t3 on t3.FId = t1.FAcctCussentID
 left join t_bd_currency t4 on t4.FId = t1.FCurrencyId
 where 1= 1 and t1.FAcctCussentID in (select FId from t_bd_customer)
 and (t1.FAsstActTypeID = ''YW3xsAEJEADgAAUWwKgTB0c4VZA='')  and t1.FCompanyid = ''sREAAAAAx2PM567U''
 group by   t2.FId, t2.FName_l2 , t3.FNumber, t3.FName_l2 , t3.FId, t4.FName_l2 , t1.FCurrencyId

 union all

 select t4.FName_l2 FCurrency,  t2.FId FCussTypeId
, t2.FName_l2 FCussType, t3.FNumber FCussNum, t3.FName_l2 FCussName, t3.FId FCussAcctId
, t4.FId FCurrencyId, 0 FStartBalanceAmt, sum(t1.FReceiving) FDebitAmt, sum(t1.FActual) FCreditAmt, 0 FBalanceAmt
 from
 (select t1.FCompanyId FCompanyId, t1.FAsstActID FCUSTOMERID, t1.FCurrencyId FCURRENCYID, t1.FBillDate FBILLDATE
,  t1.FAmount FRECEIVING, 0 FActual , t1.FAuditorID FAUDITORID, t1.FAsstActTypeID FASSTACTTYPEID
 from T_AR_OTHERBILL t1
 inner join t_bd_customer tCus on tCus.FId = t1.FAsstActID
 where 1= 1 and t1.FBillStatus = 3 AND t1.FIsBizBill = 0 and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
 and t1.FBillDate >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'')  and t1.FBillDate < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 union all
select t1.FCompanyId FCompanyId, t1.FPayerID FCUSTOMERID, t1.FCurrencyID FCURRENCYID, t1.FBizDate FBILLDATE
,  0 FRECEIVING, t1.FAmount FActual , t1.FAuditorID FAUDITORID, t1.FPayerTypeID FASSTACTTYPEID
 from T_CAS_ReceivingBill t1
 inner join t_bd_customer tCus on tCus.FId = t1.FPayerID
 where 1= 1 and t1.FBillStatus >= 14 and t1.FSourceType = 100 and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
 and t1.FBizDate >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'')  and t1.FBizDate < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Main FCUSTOMERID, t1.FCurrencyID_Main FCURRENCYID, t1.FBizDate FBILLDATE
,  0 FRECEIVING, t1.FThisVerificateAmt_Main FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Main FASSTACTTYPEID
 from T_AR_VerificationBill t1
 inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Main
 where 1= 1 and ((t1.FVerificationType = 101 or t1.FVerificationType = 107 or t1.FVerificationType = 103 or t1.FVerificationType = 109) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
 and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
 and t1.FBizDate >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'')  and t1.FBizDate < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Second FCUSTOMERID, t1.FCurrencyID_Second FCURRENCYID, t1.FBizDate FBILLDATE
,  t1.FThisVerificateAmt_Second FRECEIVING, 0 FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Second FASSTACTTYPEID
 from T_AR_VerificationBill t1
 inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Second
 where 1= 1 and ((t1.FVerificationType = 101 or t1.FVerificationType = 107 ) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
 and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
 and t1.FBizDate >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'')  and t1.FBizDate < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Main FCUSTOMERID, t1.FCurrencyID_Main FCURRENCYID, t1.FBizDate FBILLDATE
, t1.FThisVerificateAmt_Main FRECEIVING, 0 FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Main FASSTACTTYPEID
 from T_AR_VerificationBill t1
 inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Main
 where 1= 1 and ((t1.FVerificationType = 104 or t1.FVerificationType = 110 or t1.FVerificationType = 113 or t1.FVerificationType = 114) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
 and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
 and t1.FBizDate >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'')  and t1.FBizDate < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Second FCUSTOMERID, t1.FCurrencyID_Second FCURRENCYID, t1.FBizDate FBILLDATE
,  0 FRECEIVING, t1.FThisVerificateAmt_Second  FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Second FASSTACTTYPEID
 from T_AP_VerificationBill t1
 inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Second
 where 1= 1 and ((t1.FVerificationType = 203 or t1.FVerificationType = 209 ) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
 and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
 and t1.FBizDate >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'')  and t1.FBizDate < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Second FCUSTOMERID, t1.FCurrencyID_Second FCURRENCYID, t1.FBizDate FBILLDATE
,  t1.FThisVerificateAmt_Second FRECEIVING, 0 FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Second FASSTACTTYPEID
 from T_AP_VerificationBill t1
 inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Second
 where 1= 1 and ((t1.FVerificationType = 204 or t1.FVerificationType = 210 or t1.FVerificationType = 211 or t1.FVerificationType = 212) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
 and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
 and t1.FBizDate >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'')  and t1.FBizDate < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 union all
select A.FCompanyId FCompanyId, B.FAsstActId FCUSTOMERID, A.FCurrencyId FCURRENCYID, A.FBillDate FBILLDATE,
  0 FRECEIVING, A.FAmount FActual , A.FAuditorID FAUDITORID, B.FAsstActTypeId FASSTACTTYPEID
 from T_AR_OTHERBILL A INNER JOIN T_AR_OTHERBILL B ON A.FSourceBillID = B.FID and A.FCompanyId = B.FCompanyId
 inner join t_bd_customer tCus on tCus.FId = B.FAsstActId
 where 1=1  and A.FCompanyId = ''sREAAAAAx2PM567U''

 and A.FBillDate >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'') and A.FBillDate < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 and A.FCurrencyId = B.FCurrencyId  and B.FCompanyId = ''sREAAAAAx2PM567U'' and B.FBillDate < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
  and B.FBillStatus = 3 and (A.FIsReverseBill = 0  and A.FIsTransBill = 1 and A.FBillStatus = 3 )
 union all
select A.FCompanyId FCompanyId, B.FPayerID FCUSTOMERID, A.FCurrencyId FCURRENCYID,  A.FBizDate  FBILLDATE,
 A.FAmount FRECEIVING, 0 FActual , A.FAuditorID FAUDITORID, B.FPayerTypeID FASSTACTTYPEID
 from T_CAS_ReceivingBill A INNER JOIN T_CAS_ReceivingBill B ON A.FSourceBillID = B.FID and A.FCompanyId = B.FCompanyId
 inner join t_bd_customer tCus on tCus.FId = B.FPayerID
 where 1=1  and A.FCompanyId = ''sREAAAAAx2PM567U''

 and  A.FBizDate  >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'') and  A.FBizDate  < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 and A.FCurrencyId = B.FCurrencyId  and B.FCompanyId = ''sREAAAAAx2PM567U'' and  B.FBizDate  < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
  and B.FSourceType= 100 and B.FBillStatus>= 14  AND A.FSourceType= 100 and A.FBillStatus>= 14 and A.FIsTransBill = 1
 union all
select A.FCompanyId FCompanyId, B.FAsstActID FCUSTOMERID, A.FCurrencyId FCURRENCYID,  A.FBizDate  FBILLDATE,
 0 AS FRECEIVING, A.FAmount As FActual , A.FAuditorID FAUDITORID, B.FAsstActTypeID FASSTACTTYPEID
 from T_CAS_PaymentBill A INNER JOIN T_AR_OtherBill B ON A.FSourceBillID = B.FID and A.FCompanyId = B.FCompanyId
 inner join t_bd_customer tCus on tCus.FId = B.FAsstActID
 where 1=1  and A.FCompanyId = ''sREAAAAAx2PM567U''

 and  A.FBizDate  >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'') and  A.FBizDate  < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 and A.FCurrencyId = B.FCurrencyId  and B.FCompanyId = ''sREAAAAAx2PM567U'' and  B.FBillDate  < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
  and A.FSourceType= 101 and A.FBillStatus>= 15 AND B.FBillStatus= 3 and A.FIsTransOtherBill = 1
 )  t1 left join t_bd_AsstActType t2 on t2.FId = t1.FAsstActTypeId
 left join t_bd_customer t3 on t3.FId = t1.FCustomerId left join t_bd_currency t4 on t4.FId = t1.FCurrencyId
 group by   t2.FId, t2.FName_l2 , t3.FNumber, t3.FName_l2 , t3.FId, t4.FName_l2 , t4.FId
 union all
 select t4.FName_l2 FCurrency,  t2.FId FCussTypeId
, t2.FName_l2 FCussType, t3.FNumber FCussNum, t3.FName_l2 FCussName, t3.FId FCussAcctId
, t4.FId FCurrencyId, 0 FStartBalanceAmt, sum(t1.FActual) FDebitAmt, sum(t1.FPayment) FCreditAmt, 0 FBalanceAmt
 from
 (select t1.FCompanyId FCompanyId, t1.FAsstActID FSUPPLIERID, t1.FCurrencyID FCURRENCYID, t1.FBillDate FBILLDATE
,  t1.FAmount FPayment, 0 FActual , t1.FAuditorID FAUDITORID, t1.FAsstActTypeID FASSTACTTYPEID
 from T_AP_OTHERBILL t1
 inner join t_bd_customer tCus on tCus.FId = t1.FAsstActID
 where 1= 1 and t1.FBillStatus = 3 AND t1.FIsBizBill = 0 and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
 and t1.FBillDate >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'')  and t1.FBillDate < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 union all
select t1.FCompanyId FCompanyId, t1.FPayeeID FSUPPLIERID, t1.FCurrencyID FCURRENCYID, t1.FBizDate FBILLDATE
,  0 FPAYMENT, t1.FAmount FActual , t1.FAuditorID FAUDITORID, t1.FPayeeTypeID FASSTACTTYPEID
 from T_CAS_PaymentBill t1
 inner join t_bd_customer tCus on tCus.FId = t1.FPayeeID
 where 1= 1 and t1.FBillStatus >= 15 and t1.FSourceType = 101 and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
 and t1.FBizDate >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'')  and t1.FBizDate < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Main FSUPPLIERID, t1.FCurrencyID_Main FCURRENCYID, t1.FBizDate FBILLDATE
,  0 FPayment, t1.FThisVerificateAmt_Main FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Main FASSTACTTYPEID
 from T_AP_VerificationBill t1
 inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Main
 where 1= 1 and ((t1.FVerificationType = 201 or t1.FVerificationType = 207 or t1.FVerificationType = 203 or t1.FVerificationType = 209) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
 and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
 and t1.FBizDate >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'')  and t1.FBizDate < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Second FSUPPLIERID, t1.FCurrencyID_Second FCURRENCYID, t1.FBizDate FBILLDATE
,  t1.FThisVerificateAmt_Second FPayment, 0 FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Second FASSTACTTYPEID
 from T_AP_VerificationBill t1
 inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Second
 where 1= 1 and ((t1.FVerificationType = 201 or t1.FVerificationType = 207 ) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
 and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
 and t1.FBizDate >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'')  and t1.FBizDate < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Main FSUPPLIERID, t1.FCurrencyID_Main FCURRENCYID, t1.FBizDate FBILLDATE
,  t1.FThisVerificateAmt_Main FPayment, 0 FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Main FASSTACTTYPEID
 from T_AP_VerificationBill t1
 inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Main
 where 1= 1 and ((t1.FVerificationType = 204 or t1.FVerificationType = 210 or t1.FVerificationType = 211 or t1.FVerificationType = 212) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
 and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
 and t1.FBizDate >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'')  and t1.FBizDate < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Second FSUPPLIERID, t1.FCurrencyID_Second FCURRENCYID, t1.FBizDate FBILLDATE
,  0 FPayment, t1.FThisVerificateAmt_Second FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Second FASSTACTTYPEID
 from T_AR_VerificationBill t1
 inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Second
 where 1= 1 and ((t1.FVerificationType = 103 or t1.FVerificationType = 109 ) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
 and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
 and t1.FBizDate >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'')  and t1.FBizDate < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 union all
select t1.FCompanyId FCompanyId, t1.FAcctCussID_Second FSUPPLIERID, t1.FCurrencyID_Second FCURRENCYID, t1.FBizDate FBILLDATE
,  t1.FThisVerificateAmt_Second FPayment, 0 FActual , ''1'' FAUDITORID, t1.FAcctCussTypeId_Second FASSTACTTYPEID
 from T_AR_VerificationBill t1
 inner join t_bd_customer tCus on tCus.FId = t1.FAcctCussID_Second
 where 1= 1 and ((t1.FVerificationType = 104 or t1.FVerificationType = 110 or t1.FVerificationType = 113 or t1.FVerificationType = 114) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0))
 and t1.FCOMPANYID = ''sREAAAAAx2PM567U''
 and t1.FBizDate >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'')  and t1.FBizDate < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 union all
select A.FCompanyId FCompanyId, B.FAsstActId FSUPPLIERID, A.FCurrencyId FCURRENCYID, A.FBillDate FBILLDATE,
  0 FPayment, A.FAmount FActual , A.FAuditorID FAUDITORID, B.FAsstActTypeId FASSTACTTYPEID
 from T_AP_OTHERBILL A INNER JOIN T_AP_OTHERBILL B ON A.FSourceBillID = B.FID and A.FCompanyId = B.FCompanyId
 inner join t_bd_customer tCus on tCus.FId = B.FAsstActId
 where 1=1  and A.FCompanyId = ''sREAAAAAx2PM567U''

 and A.FBillDate >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'') and A.FBillDate < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 and A.FCurrencyId = B.FCurrencyId  and B.FCompanyId = ''sREAAAAAx2PM567U'' and B.FBillDate < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
  and B.FBillStatus = 3 and (A.FIsReverseBill = 0  and A.FIsTransBill = 1 and A.FBillStatus = 3 )
 union all
select A.FCompanyId FCompanyId, B.FPayeeID FSUPPLIERID, A.FCurrencyId FCURRENCYID,  A.FBizDate  FBILLDATE,
 A.FAmount FPayment, 0 FActual , A.FAuditorID FAUDITORID, B.FPayeeTypeID FASSTACTTYPEID
 from T_CAS_PaymentBill  A INNER JOIN T_CAS_PaymentBill  B ON A.FSourceBillID = B.FID and A.FCompanyId = B.FCompanyId
 inner join t_bd_customer tCus on tCus.FId = B.FPayeeID
 where 1=1  and A.FCompanyId = ''sREAAAAAx2PM567U''

 and  A.FBizDate  >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'') and  A.FBizDate  < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 and A.FCurrencyId = B.FCurrencyId  and B.FCompanyId = ''sREAAAAAx2PM567U'' and  B.FBizDate  < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
  and B.FSourceType= 101 and B.FBillStatus>= 15  AND A.FSourceType= 101 and A.FBillStatus>= 15 and A.FIsTransBill = 1
 union all
select A.FCompanyId FCompanyId, B.FAsstActID FSUPPLIERID, A.FCurrencyId FCURRENCYID,  A.FBizDate  FBILLDATE,
 0 AS FPayment, A.FAmount As FActual , A.FAuditorID FAUDITORID, B.FAsstActTypeID FASSTACTTYPEID
 from T_CAS_ReceivingBill A INNER JOIN T_AP_OtherBill B ON A.FSourceBillID = B.FID and A.FCompanyId = B.FCompanyId
 inner join t_bd_customer tCus on tCus.FId = B.FAsstActID
 where 1=1  and A.FCompanyId = ''sREAAAAAx2PM567U''

 and  A.FBizDate  >= to_date(''' || startdate ||  ''',''yyyy-mm-dd'') and  A.FBizDate  < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
 and A.FCurrencyId = B.FCurrencyId  and B.FCompanyId = ''sREAAAAAx2PM567U'' and  B.FBillDate  < to_date(''' || enddate ||  ''',''yyyy-mm-dd'')
  and A.FSourceType= 100 and A.FBillStatus>= 14 AND B.FBillStatus= 3 and A.FIsTransOtherBill = 1
 )  t1 left join t_bd_AsstActType t2 on t2.FId = t1.FAsstActTypeId
 left join t_bd_customer t3 on t3.FId = t1.FSupplierId left join t_bd_currency t4 on t4.FId = t1.FCurrencyId
 group by   t2.FId, t2.FName_l2 , t3.FNumber, t3.FName_l2 , t3.FId, t4.FName_l2 , t4.FId

)
where fcussnum = '''|| customerid || '''
group by fcussnum,fcussname ' ;

OPEN p_OutCursor FOR v_sql;

     test := v_sql;

      EXCEPTION
    WHEN OTHERS THEN
      DBMS_output.PUT_LINE(SQLERRM);
      test:=SQLERRM;

end YG501_GetCustomerBalance;

end;

 

分享到:
评论

相关推荐

    SAP HANA 中调试存储过程

    SAP HANA是一个高性能的内存数据库系统,它提供了一系列功能强大的工具来进行数据分析、应用开发、存储过程编写等操作。其中,对于存储过程的调试是开发者日常开发工作中的一个重要环节,SAP HANA为存储过程提供了...

    SqlServer存储过程及调试指南

    SqlServer存储过程及调试指南的知识点如下: 1. 存储过程概念:存储过程是一组为完成特定功能的SQL语句集,这些语句经过编译后存储在数据库中,供用户通过指定存储过程名和参数(如有)来执行。存储过程被称作...

    pb调用存储过程

    在IT行业中,数据库操作是日常开发中的重要环节,而存储过程是数据库中一种高效、封装性强的预编译语句集合。本问题涉及到的是在PowerBuilder(简称Pb)环境中如何调用Oracle或SQL Server等数据库中的存储过程。以下...

    db2 存储过程语法与实例

    DB2存储过程是一种在数据库管理系统中预编译的SQL代码集合,它允许开发人员封装复杂的业务逻辑和数据处理操作,并可以被多次调用。DB2作为一款强大的关系型数据库管理系统,其存储过程功能强大,提高了应用程序的...

    sqlserver存储过程解密工具

    SQL Server存储过程是一种预编译的SQL代码集合,它们允许数据库开发者封装复杂的业务逻辑和数据操作,提高数据库应用的性能和可维护性。然而,由于存储过程的源代码通常是不可见的,对于需要查看或修改这些过程的...

    存储过程扩展学习

    在SQL Server中,存储过程分为不同种类,包括用户定义的存储过程、系统存储过程、临时存储过程以及扩展存储过程。 1. **用户定义的存储过程**:由开发人员或DBA创建,可以根据业务需求定制各种复杂操作,如数据处理...

    pl sql developer调试存储过程及调试包中创建的存储过程

    PL/SQL Developer 调试存储过程及调试包中创建的存储过程 PL/SQL Developer 调试存储过程是指使用 PL/SQL Developer 工具来调试 Oracle 数据库中的存储过程。调试存储过程可以帮助开发者快速地定位和解决存储过程...

    数据库查询的存储过程

    数据库查询的存储过程 数据库查询的存储过程是数据库管理系统中一种非常重要的概念。它可以将多个SQL语句组合成一个单元,提高数据库的查询效率和性能。 存储过程的优点: 1. 可以在单个存储过程中执行一系列SQL...

    存储过程的优点

    ### 存储过程的优点 #### 一、提升执行效率 1. **编译优势**:存储过程在创建时仅编译一次,之后每次执行时都无需再次编译。相比之下,一般的SQL语句每次执行都需要重新编译。这种差异使得存储过程能够显著提高...

    mysql存储过程教程

    MySQL存储过程是数据库管理系统中的一种重要功能,它允许开发者预编译一系列SQL语句并封装成一个可重复使用的单元,从而提高数据处理的效率和代码的复用性。本教程将深入探讨MySQL存储过程的创建、调用以及相关概念...

    oracle存储过程学习经典入门

    本文将从 Oracle 存储过程的基础知识开始,逐步深入到 Oracle 存储过程的高级应用,包括 Hibernate 调用 Oracle 存储过程和 Java 调用 Oracle 存储过程的方法。 Oracle 存储过程基础知识 Oracle 存储过程是 Oracle...

    C# winform调用SQL存储过程-菜鸟入门 详细注释

    内容概要:简单的C# winform调用存储过程实例,创建存储过程入参,通过SqlConnection对象和SqlCommand对象调用存储过程,获取存储过程的出参并显示出来,详细代码注释,希望对用到C#调用存储过程的小伙伴有帮助 ...

    MySQL存储过程的异常处理方法

    在MySQL中,存储过程是一种预编译的SQL代码集合,它可以执行复杂的操作并提供更好的性能。在编写存储过程时,异常处理是确保程序稳定性和健壮性的重要环节。本实例展示了如何在MySQL存储过程中实现异常处理,以捕获...

    用友r9知识存储过程

    《用友R9知识存储过程详解》 在IT行业中,特别是在企业级财务系统中,数据库的高效管理和数据处理是至关重要的。用友R9作为一款先进的财务管理系统,它利用存储过程这一强大的数据库功能来优化账务处理,提高系统...

    SQL Server存储过程对比工具

    SQL Server存储过程对比工具是一种高效且实用的软件解决方案,它专为数据库管理员和开发人员设计,用于比较和分析两个SQL Server数据库中的存储过程。这款工具的主要功能是帮助用户快速识别和定位不同数据库间存储...

    创建存储过程,触发器

    在数据库管理中,存储过程和触发器是两个重要的概念,它们极大地增强了数据库的功能和效率。存储过程是一组预先编写的SQL语句,可以被多次调用,减少了网络流量,提高了性能,并提供了更好的安全性。而触发器则是一...

    Informatica调用存储过程图文流程

    Informatica调用存储过程图文流程 Informatica 是一款功能强大的数据集成工具,能够帮助用户快速、可靠地集成各种数据源。调用存储过程是 Informatica 中的一种常用功能,下面将详细介绍 Informatica 调用存储过程...

    存储过程文档--mysql

    存储过程文档--MySQL 存储过程是 MySQL 中的一个强大功能,它允许用户预先将常用的或复杂的工作写入 SQL 语句,并将其存储起来,以便在以后的数据库操作中可以快速调用和执行。存储过程可以提高数据库的执行速度,...

    SQL_Server存储过程调试指南

    资源名称:SQL_Server存储过程调试指南内容简介: 存储过程( Stored Procedure)是一组为了完成特定功能的 SQL 语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来...

Global site tag (gtag.js) - Google Analytics