create or replace package pack_posplit is
  /*
  Pack_POSsplit Version 0.56
  Procedure createpos
  Process to Purchase Orders waiting for Approval
  
  ALT - ABS-57337 - Central stockroom issues - ABS-58458 - When module FININTERFACEPOS is in the license, the TaxCode is filled with value "T" 
  when creating purchaseorderlines.
  */
  procedure createpos(fp_stockroomid           in stockroom.stockroom_id%type,
                      fp_systemuserid          in systemuser.systemuser_id%type,
                      fp_posplitadditionalinfo in purchaseorderheader.posplitadditionalinfo%type,
                      fp_hasfininterfacepos in integer);
end pack_posplit;
/

create or replace package body pack_posplit is
  ----------------------------------------------------------------------------
  --internal variables:
  ge_user___exception exception;
  gv_message         abslog.message%type := null;
  gv_languageid      languages.language_id%type;
  gv_sizestarid      sizedefinition.sizedefinition_id%type;
  gv_orderpriorityid purchaseorderheader.orderpriority_id%type;
  gv_po_ap_statusid  status.status_id%type;
  gv_enddate2099     date := to_date('31-12-2099', 'DD-MM-YYYY');
  gv_fininterfacepos integer := 0; 
  ----------------------------------------------------------------------------
  --internal functions:
  procedure write_log(fp_text         in abslog.message%type,
                      fp_priority     in abslog.priority%type,
                      fp_systemuserid in systemuser.systemuser_id%type) is
  begin
    --writelog
    abslogwrite.abslog_write(5, 'pack_posplit', null, fp_priority, fp_text || ' ' || sqlerrm,
                             fp_systemuserid);
  end write_log;

  procedure initialize is
  begin
    --initialize
    --get size star
    gv_message := 'initialize; get size star';
    select s.sizedefinition_id into gv_sizestarid from sizedefinition s where s.code = '*';
    --get language
    gv_message := 'initialize: get language';
    select min(language_id) into gv_languageid from languages where translatedescription = 'Y';
    if gv_languageid is null then
      select min(language_id) into gv_languageid from languages;
    end if;
    --get PO approval status
    select st.status_id
    into   gv_po_ap_statusid
    from   status st
    where  st.statustype = 'PO'
    and    st.code = '05'; --waiting for approval
    --get default orderpriority
    gv_message := 'initialize.get default orderpriority ordertype=2 standardpriority=Y';
    select opr.orderpriority_id
    into   gv_orderpriorityid
    from   orderpriority opr
    where  opr.ordertype = 2
    and    opr.standardpriority = 'Y';
  end initialize;

  procedure createpos(fp_stockroomid           in stockroom.stockroom_id%type,
                      fp_systemuserid          in systemuser.systemuser_id%type,
                      fp_posplitadditionalinfo in purchaseorderheader.posplitadditionalinfo%type,
                      fp_hasfininterfacepos    in integer) is
    lv_rec_stockroom          stockroom%rowtype;
    lv_wocbu_yn               varchar2(1) := 'N'; -- Split Work Order Creation Business Unit
    lv_businessunitid_old     businessunit.businessunit_id%type := -1;
    lv_wocustno_yn            varchar2(1) := 'N'; -- Split Work Order Customer Number
    lv_customerid_old         customer.customer_id%type := -1;
    lv_worc_yn                varchar2(1) := 'N'; -- Split Work Order Reason Code
    lv_reasoncodeid_old       reasoncode.reasoncode_id%type := -1;
    lv_woexby_yn              varchar2(1) := 'N'; -- Split Work Order Executed By, 1 = Internal and 2 = External
    lv_executedby_old         workorder.executedby%type := -1;
    lv_woreqdel_yn            varchar2(1) := 'N'; -- Split Work Order Requested Delivery Date
    lv_neededdate_old         date := to_date('1-1-2000', 'dd-mm-yyyy');
    lv_wophaseddel_yn         varchar2(1) := 'N'; -- Split Work Order Phased Delivery Allowed
    lv_allowphasedelivery_old workorder.allowphasedelivery%type := 'X';
    lv_relatedcustid          customer.customer_id%type := -1;
    lv_supplierid             supplier.supplier_id%type := -1;
    lv_purchaseorderheaderid  purchaseorderheader.purchaseorderheader_id%type := null;
    lv_priority               purchaseorderheader.priority%type;
    lv_orderamount            purchaseorderheader.orderamount%type;
    
    procedure get_stockroom_splitcriteria is
    begin
      --get_stockroom_splitcriteria
      gv_message := 'get_stockroom_splitcriteria';
      /*
      STOCKROOM.USEPOSPLITFORGARMENTS IS '
      Flag that indicates whether PO Splitting Criteria should be used
      when generating PO concerning garments for this stockroom.
        Possible values:
          0 = PO Split criterions will not be used
          1 = PO Split criterions will be used
      */
      if lv_rec_stockroom.useposplitforgarments = 0 then
        begin
          --0 = PO Split criterions will not be used
          --Split on lowest level
          gv_message        := 'get_stockroom_splitcriteria, PO Split criterions will not be used';
          lv_wocbu_yn       := 'Y'; -- Split Work Order Creation Business Unit
          lv_wocustno_yn    := 'Y'; -- Split Work Order Customer Number
          lv_worc_yn        := 'Y'; -- Split Work Order Reason Code
          lv_woexby_yn      := 'Y'; -- Split Work Order Executed By, 1 = Internal and 2 = External
          lv_woreqdel_yn    := 'Y'; -- Split Work Order Requested Delivery Date
          lv_wophaseddel_yn := 'Y'; -- Split Work Order Phased Delivery Allowed
        end;
      else
        begin
          /*
          -->1 = PO Split criterions will be used
          --Always look at item type = 2 = garments for this package, no others are defined anyway..
          --We do NOT check if they are active yes or know the definition only counts.
          --If nothing is select, NO PO will be made!
          ID CODE        FACTORYDESCRIPTION
          1  WOCBU       Work Order Creation Business Unit
          2  WOCUSTNO    Work Order Customer Number
          3  WORC        Work Order Reason Code
          4  WOEXBY      Work Order Executed By, 1 = Internal and 2 = External
          5  WOREQDEL    Work Order Requested Delivery Date
          6  WOPHASEDDEL Work Order Phased Delivery Allowed
          */
          gv_message := 'get_stockroom_splitcriteria, PO Split criterions will be used';

          for ps in (select post.code
                     from   posplitcriterionstockroom pos
                     join   posplitcriteriontype post
                     on     post.posplitcriteriontype_id = pos.posplitcriteriontype_id
                     where  pos.stockroom_id = fp_stockroomid
                     order  by post.posplitcriteriontype_id) loop
            begin
              if ps.code = 'WOCBU' then
                lv_wocbu_yn := 'Y'; -- Split Work Order Creation Business Unit
              end if;
              if ps.code = 'WOCUSTNO' then
                lv_wocustno_yn := 'Y'; -- Split Work Order Customer Number
              end if;
              if ps.code = 'WORC' then
                lv_worc_yn := 'Y'; -- Split Work Order Reason Code
              end if;
              if ps.code = 'WOEXBY' then
                lv_woexby_yn := 'Y'; -- Split Work Order Executed By, 1 = Internal and 2 = External
              end if;
              if ps.code = 'WOREQDEL' then
                lv_woreqdel_yn := 'Y'; -- Split Work Order Requested Delivery Date
              end if;
              if ps.code = 'WOPHASEDDEL' then
                lv_wophaseddel_yn := 'Y'; -- Split Work Order Phased Delivery Allowed
              end if;
            end;
          end loop;
        end;
      end if;
    end get_stockroom_splitcriteria;

    procedure create_purchaseorderheader(fp_supplierid             in purchaseorderheader.supplier_id%type,
                                         fp_creationbusinessunitid in purchaseorderheader.creationbusinessunit_id%type,
                                         fp_splitcustomerlinkid    in purchaseorderheader.splitcustomer_link_id%type,
                                         fp_stockroomid            in purchaseorderheader.stockroom_id%type,
                                         fp_requesteddate          in purchaseorderheader.requesteddate%type,
                                         fp_customerid             in purchaseorderheader.customer_link_id%type,
                                         fp_currencyid             in purchaseorderheader.currency_id%type,
                                         fp_orderamount            in purchaseorderheader.orderamount%type,
                                         fp_purchaseordertype      in purchaseorderheader.purchaseordertype%type,
                                         fp_priority               in purchaseorderheader.priority%type,
                                         fp_supplyingstockroomid   in purchaseorderheader.suplyingstockroom_id%type default null,
                                         fp_executedby             in purchaseorderheader.executedby%type default 1,
                                         fp_reasoncodeid           in purchaseorderheader.reasoncode_id%type default null,
                                         fp_allowphasedelivery     in purchaseorderheader.allowphaseddelivery%type default 'N',
                                         fp_orderpriorityid        in purchaseorderheader.orderpriority_id%type default null) is
      lv_purchaseordernumber  purchaseorderheader.purchaseordernumber%type := 0;
      lv_orderdate            purchaseorderheader.orderdate%type := trunc(sysdate);
      lv_ordertype            purchaseorderheader.ordertype%type := 0;
      lv_exportdate           purchaseorderheader.exportdate%type := gv_enddate2099;
      lv_exportbatchnumber    purchaseorderheader.exportbatchnumber%type := null;
      lv_confirmeddate        purchaseorderheader.confirmeddate%type := gv_enddate2099;
      lv_approvaldate         purchaseorderheader.approvaldate%type := gv_enddate2099;
      lv_approvaluserid       purchaseorderheader.approvaluser_id%type := null;
      lv_exportuserid         purchaseorderheader.exportuser_id%type := null;
      lv_confirmeduserid      purchaseorderheader.confirmeduser_id%type := null;
      lv_firstreceivingdate   purchaseorderheader.firstreceivingdate%type := gv_enddate2099;
      lv_lastreceivingdate    purchaseorderheader.lastreceivingdate%type := gv_enddate2099;
      lv_origin               purchaseorderheader.origin%type := 1; --1=purchase request
      lv_canceldate           purchaseorderheader.canceldate%type := gv_enddate2099;
      lv_canceluserid         purchaseorderheader.canceluser_id%type := null;
      lv_orderuserid          purchaseorderheader.orderuser_id%type := null;
      lv_remark               purchaseorderheader.remark%type := null;
      lv_plantcompanyid       purchaseorderheader.plantcompany_id%type := null;
      lv_finexpdate           purchaseorderheader.finexpdate%type := null;
      lv_processedtoworkorder purchaseorderheader.processedtoworkorder%type := 'N';
      lv_deliverypointid      purchaseorderheader.deliverypoint_link_id%type := null;
      --lv_suplyingstockroomid  purchaseorderheader.suplyingstockroom_id%type := null;
    begin
      --insert into purchaseorderheader
      gv_message := 'create_purchaseorderheader.insert into purchaseorderheader';
      insert into purchaseorderheader
        (purchaseorderheader_id, purchaseordernumber, supplier_id, businessunit_id, stockroom_id,
         orderdate, requesteddate, status_id, ordertype, exportdate, exportbatchnumber,
         confirmeddate, customer_link_id, systemuser_id, timestamp, approvaldate, approvaluser_id,
         exportuser_id, confirmeduser_id, firstreceivingdate, lastreceivingdate, currency_id, origin,
         canceldate, canceluser_id, orderamount, orderuser_id, remark, plantcompany_id, finexpdate,
         purchaseordertype, processedtoworkorder, deliverypoint_link_id, suplyingstockroom_id,
         priority, executedby, reasoncode_id, allowphaseddelivery, posplitadditionalinfo,
         orderpriority_id, creationbusinessunit_id, splitcustomer_link_id)
      values
        (seq_purchaseorderheader.nextval, lv_purchaseordernumber, fp_supplierid,
         lv_rec_stockroom.managedbybu_id, fp_stockroomid, lv_orderdate, fp_requesteddate,
         gv_po_ap_statusid, lv_ordertype, lv_exportdate, lv_exportbatchnumber, lv_confirmeddate,
         fp_customerid, fp_systemuserid, sysdate, lv_approvaldate, lv_approvaluserid,
         lv_exportuserid, lv_confirmeduserid, lv_firstreceivingdate, lv_lastreceivingdate,
         fp_currencyid, lv_origin, lv_canceldate, lv_canceluserid, fp_orderamount, lv_orderuserid,
         lv_remark, lv_plantcompanyid, lv_finexpdate, fp_purchaseordertype, lv_processedtoworkorder,
         lv_deliverypointid, fp_supplyingstockroomid, fp_priority, nvl(fp_executedby, 1),
         fp_reasoncodeid, nvl(fp_allowphasedelivery, 'N'), fp_posplitadditionalinfo,
         coalesce(fp_orderpriorityid, gv_orderpriorityid), fp_creationbusinessunitid,
         fp_splitcustomerlinkid)
      returning purchaseorderheader_id into lv_purchaseorderheaderid;
    end create_purchaseorderheader;

    function create_purchaseorderline(fp_productid            in product.product_id%type,
                                      fp_sizedefinitionid     in sizedefinition.sizedefinition_id%type,
                                      fp_quantityordered      in purchaseorderline.quantityordered%type,
                                      fp_purchaseprice        in purchaseorderline.purchaseprice%type,
                                      fp_requestedgradeid     in purchaseorderline.requestedgrade_id%type,
                                      fp_linesequentialnumber in purchaseorderline.linesequentialnumber%type,
                                      fp_uniqueitem           in purchaseorderline.uniqueitem%type,
                                      fp_supplierid           in supplier.supplier_id%type)
      return purchaseorderline.purchaseorderline_id%type is
      lv_lastreceiveddate            purchaseorderline.lastreceiveddate%type;
      lv_quantityreceived            purchaseorderline.quantityreceived%type := 0;
      lv_quantitycancelled           purchaseorderline.quantitycancelled%type := 0;
      lv_supplierproductcode         purchaseorderline.supplierproductcode%type;
      lv_suppliersizecode            purchaseorderline.suppliersizecode%type;
      lv_quantityorderedstock        purchaseorderline.quantityorderedstock%type := fp_quantityordered;
      lv_quantityordereddirectissue  purchaseorderline.quantityordereddirectissue%type := 0;
      lv_quantityreceiveddirectissue purchaseorderline.quantityreceiveddirectissue%type := 0;
      lv_quantityconfirmed           purchaseorderline.quantityconfirmed%type;
      lv_confirmeddate               purchaseorderline.confirmeddate%type := gv_enddate2099;
      lv_confirmationuser_id         purchaseorderline.confirmationuser_id%type;
      lv_remark                      purchaseorderline.remark%type;
      lv_purchaseorderlineid         purchaseorderline.purchaseorderline_id%type;
      lv_taxcode                     purchaseorderline.taxcode%type;
    begin
      --create_purchaseorderline
      gv_message := 'create_purchaseorderline';
      select max(sp.supplierproductcode), max(sp.suppliersizecode)
      into   lv_supplierproductcode, lv_suppliersizecode
      from   supplierproduct sp
      join   productsizepersupproduct psp
      on     psp.supplierproduct_id = sp.supplierproduct_id
      where  sp.supplier_id = fp_supplierid
      and    psp.product_id = fp_productid
      and    psp.sizedefinition_id = fp_sizedefinitionid;
      --update or insert into purchaseorderline
      gv_message := 'update or insert into purchaseorderline';
      select pol.purchaseorderline_id
      into   lv_purchaseorderlineid
      from   purchaseorderline pol
      where  pol.purchaseorderheader_id = lv_purchaseorderheaderid
      and    pol.product_link_id = fp_productid
      and    pol.sizedefinition_link_id = fp_sizedefinitionid
      and    pol.uniqueitem = fp_uniqueitem
      and    pol.requestedgrade_id = fp_requestedgradeid;
      --update
      gv_message := 'update purchaseorderline ' || lv_purchaseorderlineid;
      update purchaseorderline pol
      set    pol.quantityordered = pol.quantityordered + fp_quantityordered,
             pol.quantityorderedstock = pol.quantityorderedstock + lv_quantityorderedstock,
             pol.timestamp = sysdate, pol.systemuser_id = fp_systemuserid
      where  pol.purchaseorderline_id = lv_purchaseorderlineid;
      return lv_purchaseorderlineid;
    exception
      when no_data_found then
        begin
          --insert into purchaseorderline
          gv_message := 'insert into purchaseorderline';
          lv_taxcode := case when gv_fininterfacepos = 1 then 'T' else null end; 
          insert into purchaseorderline
            (purchaseorderline_id, purchaseorderheader_id, product_link_id, sizedefinition_link_id,
             lastreceiveddate, quantityordered, quantityreceived, quantitycancelled, systemuser_id,
             timestamp, supplierproductcode, suppliersizecode, quantityorderedstock,
             quantityordereddirectissue, quantityreceiveddirectissue, quantityconfirmed,
             purchaseprice, confirmeddate, confirmationuser_id, uniqueitem, remark, taxcode, 
             requestedgrade_id, linesequentialnumber)
          values
            (seq_purchaseorderline.nextval, lv_purchaseorderheaderid, fp_productid,
             fp_sizedefinitionid, lv_lastreceiveddate, fp_quantityordered, lv_quantityreceived,
             lv_quantitycancelled, fp_systemuserid, sysdate, lv_supplierproductcode,
             lv_suppliersizecode, lv_quantityorderedstock, lv_quantityordereddirectissue,
             lv_quantityreceiveddirectissue, lv_quantityconfirmed, fp_purchaseprice,
             lv_confirmeddate, lv_confirmationuser_id, fp_uniqueitem, lv_remark, lv_taxcode, fp_requestedgradeid,
             fp_linesequentialnumber)
          returning purchaseorderline_id into lv_purchaseorderlineid;
        end;
        return lv_purchaseorderlineid;
    end create_purchaseorderline;

    procedure link_wol_2_pol(fp_purchaserequestid   in purchaserequest.purchaserequest_id%type,
                             fp_businessunitid      in workorder.businessunit_id%type,
                             fp_neededdate          in workorder.neededdate%type,
                             fp_customerid          in workorder.customer_link_id%type,
                             fp_reasoncodeid        in workorderline.reasoncode_id%type,
                             fp_executedby          in workorder.executedby%type,
                             fp_allowphasedelivery  in workorder.allowphasedelivery%type,
                             fp_purchaseorderlineid in purchaseorderline.purchaseorderline_id%type) is
    begin
      ---link_wol_2_pol
      gv_message := 'link_wol_2_pol for purchaserequest id ' || to_char(fp_purchaserequestid);
      update workorderline wol
      set    wol.purchaseorderline_id = fp_purchaseorderlineid, wol.systemuser_id = fp_systemuserid,
             wol.timestamp = sysdate, wol.purchaserequest_id = null
      where  wol.purchaserequest_id = fp_purchaserequestid
      and    exists (select 1
              from   workorder wo
              where  wo.workorder_id = wol.workorder_id
              and    wo.neededdate = fp_neededdate
              and    wo.businessunit_id = fp_businessunitid
              and    wo.executedby = fp_executedby
              and    wo.reasoncode_id = fp_reasoncodeid
              and    wo.allowphasedelivery = fp_allowphasedelivery
              and    (wo.customer_link_id = fp_customerid or
                    (fp_customerid is null and wo.customer_link_id is null)));
    end link_wol_2_pol;

    procedure link_wole_2_pol(fp_purchaserequestid   in purchaserequest.purchaserequest_id%type,
                              fp_businessunitid      in workorder.businessunit_id%type,
                              fp_neededdate          in workorder.neededdate%type,
                              fp_customerid          in workorder.customer_link_id%type,
                              fp_reasoncodeid        in workorderline.reasoncode_id%type,
                              fp_executedby          in workorder.executedby%type,
                              fp_allowphasedelivery  in workorder.allowphasedelivery%type,
                              fp_purchaseorderlineid in purchaseorderline.purchaseorderline_id%type) is
    begin
      ---link_wole_2_pol
      gv_message := 'link_wole_2_pol for purchaserequest id ' || to_char(fp_purchaserequestid);
      update workorderlineemblem woem
      set    woem.purchaseorderline_id = fp_purchaseorderlineid,
             woem.systemuser_id = fp_systemuserid, woem.timestamp = sysdate,
             woem.purchaserequest_id = null
      where  woem.purchaserequest_id = fp_purchaserequestid
      and    exists (select 1
              from   workorderline wol
              join   workorder wo
              on     wo.workorder_id = wol.workorder_id
              where  wol.workorderline_id = woem.workorderline_id
              and    wo.neededdate = fp_neededdate
              and    wo.businessunit_id = fp_businessunitid
              and    wo.executedby = fp_executedby
              and    wo.reasoncode_id = fp_reasoncodeid
              and    wo.allowphasedelivery = fp_allowphasedelivery
              and    (wo.customer_link_id = fp_customerid or
                    (fp_customerid is null and wo.customer_link_id is null)));
    end link_wole_2_pol;

    procedure update_purchaseorderheader(fp_purchaseorderheadeid purchaseorderheader.purchaseorderheader_id%type,
                                         fp_orderamount          in purchaseorderheader.orderamount%type) is
    begin
      --update_purchaseorderheader
      gv_message := 'update_purchaseorderheader';
      update purchaseorderheader poh
      set    poh.orderamount = fp_orderamount, poh.systemuser_id = fp_systemuserid,
             poh.timestamp = sysdate
      where  poh.purchaseorderheader_id = fp_purchaseorderheadeid;
    end update_purchaseorderheader;

    procedure createpos4workorder is
      lv_quantityleftover     number;
      lv_linesequentialnumber purchaseorderline.linesequentialnumber%type;
      lv_uniquelycode         supplierproduct.uniqueitem%type;

    begin
      --loop purchaserequest on lowest group by level possible
      gv_message := 'createpos4workorder, loop pox';
      for pox in ( --workorderline.purchaserequest_id part 1 of the unions 2 parts
                  select wo.businessunit_id, wo.customer_link_id, wo.reasoncode_id, wo.executedby,
                          wo.neededdate, wo.allowphasedelivery, pr.customer_id relatedcust_id,
                          pr.supplier_id, max(s.currency_id) currency_id,
                          max(s.suppliertype) suppliertype, pr.priority,
                          max(rs.purchaseorderpriority_id) purchaseorderpriority_id,
                          max(wol.codeuniquely) codeuniquely,
                          max(s.supplyingstockroom_id) supplyingstockroom_id
                  from   purchaserequest pr
                  join   workorderline wol
                  on     wol.purchaserequest_id = pr.purchaserequest_id
                  join   workorder wo
                  on     wo.workorder_id = wol.workorder_id
                  join   supplier s
                  on     s.supplier_id = pr.supplier_id --supplier must exists!!
                  join   reasoncode rs
                  on     rs.reasoncode_id = wo.reasoncode_id
                  where  pr.stockroom_id = fp_stockroomid -->selected stockroomid
                  and    pr.poapprovaltype = 1 --> all select Purchase requests via the application
                  group  by wo.businessunit_id, wo.customer_link_id, wo.reasoncode_id, wo.executedby,
                             wo.neededdate, wo.allowphasedelivery, pr.customer_id, pr.supplier_id,
                             pr.priority
                  union
                  --workorderlineemblem.purchaserequest_id  part 2 of the unions 2 parts
                  select wo.businessunit_id, wo.customer_link_id, wo.reasoncode_id, wo.executedby,
                          wo.neededdate, wo.allowphasedelivery, pr.customer_id relatedcust_id,
                          pr.supplier_id, max(s.currency_id) currency_id,
                          max(s.suppliertype) suppliertype, pr.priority,
                          max(rs.purchaseorderpriority_id) purchaseorderpriority_id,
                          max(wol.codeuniquely) codeuniquely,
                          max(s.supplyingstockroom_id) supplyingstockroom_id
                  from   purchaserequest pr
                  join   workorderlineemblem woem
                  on     woem.purchaserequest_id = pr.purchaserequest_id
                  join   workorderline wol
                  on     wol.workorderline_id = woem.workorderline_id
                  join   workorder wo
                  on     wo.workorder_id = wol.workorder_id
                  join   supplier s
                  on     s.supplier_id = pr.supplier_id --supplier must exists!!
                  join   reasoncode rs
                  on     rs.reasoncode_id = wo.reasoncode_id
                  where  pr.stockroom_id = fp_stockroomid -->selected stockroomid
                  and    pr.poapprovaltype = 1 --> all select Purchase requests via the application
                  group  by wo.businessunit_id, wo.customer_link_id, wo.reasoncode_id, wo.executedby,
                             wo.neededdate, wo.allowphasedelivery, pr.customer_id, pr.supplier_id,
                             pr.priority
                  order  by businessunit_id, customer_link_id, reasoncode_id, executedby, neededdate,
                             allowphasedelivery, customer_link_id, supplier_id, priority) loop
        begin
          --check if a new PO needs to be created
          if ((lv_wocbu_yn = 'Y' and pox.businessunit_id <> lv_businessunitid_old) or
             (lv_wocustno_yn = 'Y' and pox.customer_link_id <> lv_customerid_old) or
             (lv_worc_yn = 'Y' and pox.reasoncode_id <> lv_reasoncodeid_old) or
             (lv_woexby_yn = 'Y' and pox.executedby <> lv_executedby_old) or
             (lv_woreqdel_yn = 'Y' and pox.neededdate <> lv_neededdate_old) or
             (lv_wophaseddel_yn = 'Y' and pox.allowphasedelivery <> lv_allowphasedelivery_old) or
             (lv_relatedcustid <> nvl(pox.relatedcust_id, -1)) or --always for related customer
             (lv_supplierid <> nvl(pox.supplier_id, -1)) or --always for supplier id
             (lv_priority <> pox.priority) --always for priority
             ) then
            begin
              --create the new PO
              lv_linesequentialnumber := 0;
              lv_orderamount          := 0;
              create_purchaseorderheader(pox.supplier_id, pox.businessunit_id, pox.customer_link_id,
                                         fp_stockroomid, pox.neededdate, pox.relatedcust_id,
                                         pox.currency_id, lv_orderamount, pox.suppliertype,
                                         pox.priority, pox.supplyingstockroom_id, pox.executedby,
                                         pox.reasoncode_id, pox.allowphasedelivery,
                                         pox.purchaseorderpriority_id);
            end;
          end if;
          --for selected records, lookup workorders etc
          gv_message := 'createpos4workorder, loop poy wol';
          for poy in (select pr.product_id, pr.sizedefinition_id, pr.purchaseprice,
                             pr.purchaserequest_id, pr.requestedgrade_id,
                             (select sum(wol.quantityordered - wol.quantitycancelled -
                                           wol.quantityfilled)
                               from   workorderline wol
                               join   workorder wo
                               on     wo.workorder_id = wol.workorder_id
                               where  wol.purchaserequest_id = pr.purchaserequest_id
                               and    wo.businessunit_id = pox.businessunit_id
                               and    wo.neededdate = pox.neededdate
                               and    ((wo.customer_link_id = pox.customer_link_id) or
                                     (wo.customer_link_id is null and pox.customer_link_id is null))
                               and    wo.reasoncode_id = pox.reasoncode_id
                               and    wo.executedby = pox.executedby
                               and    wo.allowphasedelivery = pox.allowphasedelivery) quantityordered,
                             pr.quantitytoorder
                      from   purchaserequest pr
                      where  pr.stockroom_id = fp_stockroomid -->selected stockroomid
                      and    pr.poapprovaltype = 1 --> all selected Purchase requests via the application
                      and    nvl(pr.customer_id, -1) = nvl(pox.relatedcust_id, -1)
                      and    pr.supplier_id = pox.supplier_id --supplier must exists!!
                      and    pr.priority = pox.priority
                      and    exists
                       (select 1
                              from   workorderline wol
                              join   workorder wo
                              on     wo.workorder_id = wol.workorder_id
                              where  wol.purchaserequest_id = pr.purchaserequest_id
                              and    wo.businessunit_id = pox.businessunit_id
                              and    wo.neededdate = pox.neededdate
                              and    ((wo.customer_link_id = pox.customer_link_id) or
                                    (wo.customer_link_id is null and pox.customer_link_id is null))
                              and    wo.reasoncode_id = pox.reasoncode_id
                              and    wo.executedby = pox.executedby
                              and    wo.allowphasedelivery = pox.allowphasedelivery)
                      order  by pr.priority, pr.product_id, pr.sizedefinition_id,
                                pr.requestedgrade_id, pr.customer_id) loop
            begin
              --link_wol_2_pol
              lv_linesequentialnumber := lv_linesequentialnumber + 1;
              lv_orderamount          := lv_orderamount + (poy.quantityordered * poy.purchaseprice);
              select nvl(min(sp.uniqueitem), pox.codeuniquely)
              into   lv_uniquelycode
              from   productsizepersupproduct pspsp, supplier s, supplierproduct sp
              where  sp.supplier_id = s.supplier_id
              and    sp.supplier_id = pox.supplier_id
              and    sp.supplierproduct_id = pspsp.supplierproduct_id
              and    pspsp.product_id = poy.product_id
              and    pspsp.sizedefinition_id = poy.sizedefinition_id;
              link_wol_2_pol(poy.purchaserequest_id, pox.businessunit_id, pox.neededdate,
                             pox.customer_link_id, pox.reasoncode_id, pox.executedby,
                             pox.allowphasedelivery,
                             create_purchaseorderline(poy.product_id, poy.sizedefinition_id,
                                                       poy.quantityordered, poy.purchaseprice,
                                                       poy.requestedgrade_id, lv_linesequentialnumber,
                                                       lv_uniquelycode, pox.supplier_id));
              --delete from purchaserequest pr
              gv_message := 'createpos4workorder, update/delete purchaserequest';
              update purchaserequest pr
              set    pr.quantitytoorder = pr.quantitytoorder - poy.quantityordered
              where  pr.purchaserequest_id = poy.purchaserequest_id
              returning pr.quantitytoorder into lv_quantityleftover;
              if lv_quantityleftover <= 0 then
                begin
                  delete from purchaserequest pr
                  where  pr.purchaserequest_id = poy.purchaserequest_id
                  and    pr.quantitytoorder <= 0
                  and    not exists (select 1
                          from   workorderline wol
                          where  wol.purchaserequest_id = pr.purchaserequest_id)
                  and    not exists
                   (select 1
                          from   workorderlineemblem woem
                          where  woem.purchaserequest_id = pr.purchaserequest_id);
                end;
              end if;
            end;
          end loop; --poy
          --for selected records, lookup workorder line emblems
          gv_message := 'createpos4workorder, loop poz wol emlems';
          for poz in (select pr.product_id, pr.sizedefinition_id, pr.purchaseprice,
                             pr.purchaserequest_id, pr.requestedgrade_id,
                             (select sum(woem.qtyrequested)
                               from   workorderlineemblem woem
                               join   workorderline wol
                               on     wol.workorderline_id = woem.workorderline_id
                               join   workorder wo
                               on     wo.workorder_id = wol.workorder_id
                               where  woem.purchaserequest_id = pr.purchaserequest_id
                               and    wo.businessunit_id = pox.businessunit_id
                               and    wo.neededdate = pox.neededdate
                               and    ((wo.customer_link_id = pox.customer_link_id) or
                                     (wo.customer_link_id is null and pox.customer_link_id is null))
                               and    wo.reasoncode_id = pox.reasoncode_id
                               and    wo.executedby = pox.executedby
                               and    wo.allowphasedelivery = pox.allowphasedelivery) quantityordered,
                             pr.quantitytoorder
                      from   purchaserequest pr
                      where  pr.stockroom_id = fp_stockroomid -->selected stockroomid
                      and    pr.poapprovaltype = 1 --> all selected Purchase requests via the application
                      and    nvl(pr.customer_id, -1) = nvl(pox.relatedcust_id, -1)
                      and    pr.supplier_id = pox.supplier_id --supplier must exists!!
                      and    pr.priority = pox.priority
                      and    exists
                       (select 1
                              from   workorderlineemblem woem
                              join   workorderline wol
                              on     wol.workorderline_id = woem.workorderline_id
                              join   workorder wo
                              on     wo.workorder_id = wol.workorder_id
                              where  woem.purchaserequest_id = pr.purchaserequest_id
                              and    wo.businessunit_id = pox.businessunit_id
                              and    wo.neededdate = pox.neededdate
                              and    ((wo.customer_link_id = pox.customer_link_id) or
                                    (wo.customer_link_id is null and pox.customer_link_id is null))
                              and    wo.reasoncode_id = pox.reasoncode_id
                              and    wo.executedby = pox.executedby
                              and    wo.allowphasedelivery = pox.allowphasedelivery)
                      order  by pr.priority, pr.product_id, pr.sizedefinition_id,
                                pr.requestedgrade_id, pr.customer_id) loop
            begin
              --link_wol_2_pol
              lv_linesequentialnumber := lv_linesequentialnumber + 1;
              lv_orderamount          := lv_orderamount + (poz.quantityordered * poz.purchaseprice);
              select nvl(min(sp.uniqueitem), pox.codeuniquely)
              into   lv_uniquelycode
              from   productsizepersupproduct pspsp, supplier s, supplierproduct sp
              where  sp.supplier_id = s.supplier_id
              and    sp.supplier_id = pox.supplier_id
              and    sp.supplierproduct_id = pspsp.supplierproduct_id
              and    pspsp.product_id = poz.product_id
              and    pspsp.sizedefinition_id = poz.sizedefinition_id;
              link_wole_2_pol(poz.purchaserequest_id, pox.businessunit_id, pox.neededdate,
                              pox.customer_link_id, pox.reasoncode_id, pox.executedby,
                              pox.allowphasedelivery,
                              create_purchaseorderline(poz.product_id, poz.sizedefinition_id,
                                                        poz.quantityordered, poz.purchaseprice,
                                                        poz.requestedgrade_id, lv_linesequentialnumber,
                                                        lv_uniquelycode, pox.supplier_id));
              --delete from purchaserequest pr
              gv_message := 'createpos4workorder, update/delete purchaserequest';
              update purchaserequest pr
              set    pr.quantitytoorder = pr.quantitytoorder - poz.quantityordered
              where  pr.purchaserequest_id = poz.purchaserequest_id
              returning pr.quantitytoorder into lv_quantityleftover;
              if lv_quantityleftover <= 0 then
                begin
                  delete from purchaserequest pr
                  where  pr.purchaserequest_id = poz.purchaserequest_id
                  and    pr.quantitytoorder <= 0
                  and    not exists (select 1
                          from   workorderline wol
                          where  wol.purchaserequest_id = pr.purchaserequest_id)
                  and    not exists
                   (select 1
                          from   workorderlineemblem woem
                          where  woem.purchaserequest_id = pr.purchaserequest_id);
                end;
              end if;
            end;
          end loop; --poz
          --update_purchaseorderheader.orderamount
          update_purchaseorderheader(lv_purchaseorderheaderid, lv_orderamount);
        end;
        lv_businessunitid_old     := pox.businessunit_id;
        lv_customerid_old         := pox.customer_link_id;
        lv_reasoncodeid_old       := pox.reasoncode_id;
        lv_executedby_old         := pox.executedby;
        lv_neededdate_old         := pox.neededdate;
        lv_allowphasedelivery_old := pox.allowphasedelivery;
        lv_relatedcustid          := nvl(pox.relatedcust_id, -1);
        lv_supplierid             := pox.supplier_id;
        lv_priority               := pox.priority;
      end loop; --pox
    end createpos4workorder;

    procedure createposleftover is
      lv_purchaseorderlineid  purchaseorderline.purchaseorderline_id%type;
      lv_linesequentialnumber purchaseorderline.linesequentialnumber%type;
    begin
      --createposleftover STOCKROOM_ID, SUPPLIER_ID, CUSTOMER_ID, PRODUCT_ID, SIZEDEFINITION_ID, REQUESTEDGRADE_ID, PRIORITY
      gv_message := 'createposleftover';
      for polx in (select pr.supplier_id, pr.priority, pr.customer_id,
                          max(pr.requesteddate) requesteddate,
                          sum(pr.quantitytoorder) quantitytoorder,
                          sum(pr.quantitytoorder * pr.purchaseprice) amounttoorder,
                          max(s.currency_id) currency_id, max(s.suppliertype) suppliertype,
                          max(s.supplyingstockroom_id) supplyingstockroom_id
                   from   purchaserequest pr
                   join   supplier s
                   on     s.supplier_id = pr.supplier_id --supplier must exists!!
                   where  pr.stockroom_id = fp_stockroomid -->selected stockroomid
                   and    pr.poapprovaltype = 1 --> all select Purchase requests via the application
                   group  by pr.supplier_id, pr.priority, pr.customer_id
                   order  by pr.supplier_id, pr.priority, pr.customer_id) loop
        begin
          --create the new PO
          lv_linesequentialnumber := 0;
          create_purchaseorderheader(polx.supplier_id, lv_rec_stockroom.managedbybu_id, null,
                                     fp_stockroomid, polx.requesteddate, polx.customer_id,
                                     polx.currency_id, polx.amounttoorder, polx.suppliertype,
                                     polx.priority, polx.supplyingstockroom_id);
          for poly in (select pr.product_id, pr.sizedefinition_id, pr.quantitytoorder,
                              pr.purchaseprice, pr.requestedgrade_id, pr.purchaserequest_id,
                              nvl((select max(sp.uniqueitem)
                                   from   supplierproduct sp
                                   join   productsizepersupproduct psp
                                   on     psp.supplierproduct_id = sp.supplierproduct_id
                                   where  psp.product_id = pr.product_id
                                   and    psp.sizedefinition_id = pr.sizedefinition_id
                                   and    sp.supplier_id = pr.supplier_id), 'N') uniqueitem
                       from   purchaserequest pr
                       where  pr.supplier_id = polx.supplier_id --supplier must exists!!
                       and    pr.stockroom_id = fp_stockroomid -->selected stockroomid
                       and    pr.poapprovaltype = 1 --> all select Purchase requests via the application
                       and    pr.priority = polx.priority
                       and    (pr.customer_id = polx.customer_id or
                             (pr.customer_id is null and polx.customer_id is null))
                       order  by pr.priority, pr.product_id, pr.sizedefinition_id,
                                 pr.requestedgrade_id, pr.customer_id) loop
            begin
              lv_linesequentialnumber := lv_linesequentialnumber + 1;
              lv_purchaseorderlineid  := create_purchaseorderline(poly.product_id,
                                                                  poly.sizedefinition_id,
                                                                  poly.quantitytoorder,
                                                                  poly.purchaseprice,
                                                                  poly.requestedgrade_id,
                                                                  lv_linesequentialnumber,
                                                                  poly.uniqueitem, polx.supplier_id);
              --remove the purchaserequest
              gv_message := 'createposleftover, delete purchaserequest';
              delete from purchaserequest pr where pr.purchaserequest_id = poly.purchaserequest_id;
            end;
          end loop; --poly
        end;
      end loop; --polx
    end createposleftover;

  begin
    --see chapter 2.11.4.1  Create PO object based on split criteria
    gv_fininterfacepos := fp_hasfininterfacepos;
    --createpos; Process to Purchase Orders waiting for Approval
    gv_message := 'stockroom id ' || nvl(to_char(fp_stockroomid), 'NULL');
    select sr.* into lv_rec_stockroom from stockroom sr where sr.stockroom_id = fp_stockroomid;
    --get_stockroom_splitcriteria
    get_stockroom_splitcriteria;
    --createpos4workorder
    createpos4workorder;
    --createposleftover
    createposleftover;
    --commit
    commit;
  exception
    when ge_user___exception then
      begin
        rollback;
        write_log('createpos. ' || gv_message, 3, fp_systemuserid);
      end;

    when no_data_found then
      begin
        rollback;
        write_log('createpos. No data found ' || gv_message, 3, fp_systemuserid);
      end;

    when others then
      begin
        rollback;
        write_log('createpos. Error others ' || gv_message || ' ' || sqlerrm, 3, fp_systemuserid);
      end;

  end createpos;

begin
  initialize;
exception
  when others then
    begin
      rollback;
      write_log(gv_message || ' ' || sqlerrm, 5, 0);
    end;

end pack_posplit;
/

