Oracle EBS:SQL 失效一揽子PO协议

1.查询一揽子PO:

这里要注意的是:不同的付款条件,币种,税率,都会可能产生一张新的一揽子PO协议


select pla.PO_LINE_ID,
       pla.PO_HEADER_ID,
       pla.ITEM_ID,
       msib.SEGMENT1 ITEM_CODE,
        pla.UNIT_PRICE,
       pla.ITEM_DESCRIPTION,
       pla.START_DATE,
       pla.EXPIRATION_DATE,
       pla.CANCEL_FLAG,
       pla.CLOSED_CODE,
       pha.PO_HEADER_ID,
       pha.VENDOR_ID,
       pha.VENDOR_SITE_ID,
       pha.TERMS_ID,
       pla.TAX_CODE_ID
  from PO_LINES_ALL pla, PO_HEADERS_ALL pha,MTL_SYSTEM_ITEMS_B msib
where nvl(pla.closed_code, 'OPEN') != 'FINALLY CLOSED'
   and nvl(pla.CANCEL_FLAG, 'N') = 'N'
   and pla.PO_HEADER_ID=pha.PO_HEADER_ID
   and nvl(pha.closed_code, 'OPEN') != 'FINALLY CLOSED'
   and nvl(pha.CANCEL_FLAG,'N')='N'
   and pha.ENABLED_FLAG='Y'
   and pha.TYPE_LOOKUP_CODE = 'BLANKET'
   and msib.INVENTORY_ITEM_ID=pla.ITEM_ID


2.通过查找对应的ID,失效对应的价格,我这里好像少了付款条件跟币种,税率这几个条件,


procedure SetPriceExpireDate(P_ITEM_CODE        IN VARCHAR2,
                               P_VENDOR_CODE      IN VARCHAR2,
                               P_VENDOR_SITE_CODE IN VARCHAR2,
                               P_ORG_ID           IN VARCHAR2,
                               v_retval           out number) is
  begin
    Update PO_LINES_ALL pla
       set pla.EXPIRATION_DATE = sysdate
     where pla.ITEM_ID = GetItemID(P_ITEM_CODE, P_ORG_ID)
       and pla.ORG_ID = P_ORG_ID
       and nvl(pla.cancel_flag, 'N') = 'N'
       and nvl(closed_code, 'OPEN') != 'FINALLY CLOSED'
       and exists
     (select *
              from PO_HEADERS_ALL pha
             where pha.PO_HEADER_ID = pla.PO_HEADER_ID
               and pha.TYPE_LOOKUP_CODE = 'BLANKET'
               and pha.VENDOR_ID = GetVendorID(P_VENDOR_CODE)
               and pha.VENDOR_SITE_ID =
                   GetVendorSiteID(P_VENDOR_SITE_CODE, P_ORG_ID));
  
    v_retval := 1;
    COMMIT;
  
  EXCEPTION
    WHEN OTHERS THEN
      v_retval := 0;
    
  end SetPriceExpireDate;


3.辅助的几个函数

Function GetVendorID(P_Vendor_Code IN VARCHAR2) RETURN NUMBER as
    v_vendor_id number;
  
  begin
  
    select pv.VENDOR_ID
      into v_vendor_id
      from po_vendors pv
    
     where pv.SEGMENT1 = P_Vendor_Code;
  
    return v_vendor_id;
  
  end;

  Function GetVendorSiteID(P_Vendor_SITE_CODE IN VARCHAR2,
                           P_ORG_ID           IN NUMBER) RETURN NUMBER as
    v_vendor_site_id number;
  begin
    select pvsa.VENDOR_SITE_ID
      into v_vendor_site_id
      from PO_VENDOR_SITES_ALL pvsa
     where pvsa.VENDOR_SITE_CODE = P_Vendor_SITE_CODE
       and pvsa.ORG_ID = P_ORG_ID;
  
    return v_vendor_site_id;
  
  end;

  FUNCTION GetItemID(P_ITEM_CODE IN VARCHAR2, P_ORG_ID IN NUMBER)
    RETURN NUMBER as
  
    v_item_id number;
  
  begin
    select msib.INVENTORY_ITEM_ID
      into v_item_id
      from MTL_SYSTEM_ITEMS_B msib
    
     where msib.SEGMENT1 = P_ITEM_CODE
       AND msib.ORGANIZATION_ID = P_ORG_ID;
  
    return v_item_id;
  
  end;


我这里只对料件里的到期日进行了控制处理。



上一篇: Oracle EBS :PO培训文档
下一篇: NPOI动态获取数据源绑定Gridview显示
文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags: Oracle EBS
相关日志:
评论: 0 | 引用: 0 | 查看次数: 6956
发表评论
昵 称:
密 码: 游客发言不需要密码.
邮 箱: 邮件地址支持Gravatar头像,邮箱地址不会公开.
网 址: 输入网址便于回访.
内 容:
验证码:
选 项:
虽然发表评论不用注册,但是为了保护您的发言权,建议您注册帐号.
字数限制 30 字 | UBB代码 关闭 | [img]标签 关闭