Custom Workflow For Oracle Qoutations

-- Package Spec
create or replace PACKAGE XXCUS_QOUTATIONS_WF AS

  procedure VERIFY_QOUTE_DETAILS(itemtype  IN VARCHAR2,
                                 itemkey   IN VARCHAR2,
                                 actid     IN NUMBER,
                                 funcmode  IN VARCHAR2,
                                 resultout IN OUT NOCOPY VARCHAR2);
  procedure SET_PROCESS_DTL(itemtype  IN VARCHAR2,
                            itemkey   IN VARCHAR2,
                            actid     IN NUMBER,
                            funcmode  IN VARCHAR2,
                            resultout IN OUT NOCOPY VARCHAR2);
  PROCEDURE get_qoute_line_details(document_id   in varchar2,
                                   display_type  in varchar2,
                                   document      in out NOCOPY varchar2,
                                   document_type in out NOCOPY varchar2);
  PROCEDURE get_attachment(document_id   in varchar2,
                                   display_type  in varchar2,
                                   document      in out blob,
                                   document_type in out NOCOPY varchar2);
  PROCEDURE LAUNCH_WORKFLOW(P_QOUTE_ID in number, P_USER_ID in number);
  Procedure Upd_TO_Approved(itemtype in varchar2,
                            itemkey  in varchar2,
                            actid    in number,
                            funcmode in varchar2,
                            result   out nocopy varchar2);
  Procedure Upd_TO_Part_Approved(itemtype in varchar2,
                                 itemkey  in varchar2,
                                 actid    in number,
                                 funcmode in varchar2,
                                 result   out nocopy varchar2);
  Procedure Upd_TO_Rejected(itemtype in varchar2,
                            itemkey  in varchar2,
                            actid    in number,
                            funcmode in varchar2,
                            result   out nocopy varchar2);
  Procedure Upd_TO_Pending(itemtype in varchar2,
                           itemkey  in varchar2,
                           actid    in number,
                           funcmode in varchar2,
                           result   out nocopy varchar2);
  Procedure get_forward_from(itemtype in varchar2,
                             itemkey  in varchar2,
                             actid    in number,
                             funcmode in varchar2,
                             result   out nocopy varchar2);

END XXCUS_QOUTATIONS_WF;


-- Package Body

create or replace PACKAGE BODY XXCUS_QOUTATIONS_WF AS
  Function get_froward_from_role(p_user_name in varchar2) return varchar2 is
    LV_forward_from wf_user_roles.role_name%type;
  begin
    select role_name
      into LV_forward_from
      from wf_user_roles
     where user_name = p_user_name
       and role_orig_system = 'PER';
    return LV_forward_from;
  exception
    when no_data_found then
      return null;
  end;
  Procedure cust_wf_log(column1 IN VARCHAR2,
                        column2 IN VARCHAR2,
                        column3 IN VARCHAR2,
                        column4 IN VARCHAR2,
                        column5 IN VARCHAR2,
                        column6 IN VARCHAR2) as
    pragma AUTONOMOUS_TRANSACTION;
  begin
    null;
    -- insert into INV.CUSTOM_MOVE_ORDER_WF_LOG values(column1,column2,column3,column4,column5,column6);
    -- commit;
  end;
  Procedure get_forward_from(itemtype in varchar2,
                             itemkey  in varchar2,
                             actid    in number,
                             funcmode in varchar2,
                             result   out nocopy varchar2) as
    L_forward_from wf_user_roles.role_name%type;
    L_approver     varchar2(50);
  begin
 
    if (funcmode <> wf_engine.eng_run) then
      result := wf_engine.eng_null;
      return;
    end if;
 
    if (funcmode = 'RUN') then
      L_approver     := WF_ENGINE.GETITEMATTRTEXT(ITEMTYPE => itemtype,
                                                  ITEMKEY  => itemkey,
                                                  ANAME    => 'APPROVER');
      L_forward_from := get_froward_from_role(L_approver);
      WF_ENGINE.SETITEMATTRTEXT(ITEMTYPE => itemtype,
                                ITEMKEY  => itemkey,
                                ANAME    => 'FORWARD_FROM_USERNAME',
                                AVALUE   => L_forward_from);
   
      result := 'COMPLETE';
      return;
    end if;
  exception
    when others then
      result := 'ERROR:' || sqlerrm;
      raise;
  End;
  procedure SET_PROCESS_DTL(itemtype  IN VARCHAR2,
                            itemkey   IN VARCHAR2,
                            actid     IN NUMBER,
                            funcmode  IN VARCHAR2,
                            resultout IN OUT NOCOPY VARCHAR2) AS
 
    L_creator_user       varchar2(50);
    L_current_approver   varchar2(50);
    L_NEXT_APPROVER      varchar2(50);
    L_APPROVE_NOTIF_BODY varchar2(2000);
    L_QOUTE_ID           number;
    L_forward_from       wf_user_roles.role_name%type;
 
    Function get_appprover(p_user_name in varchar2) return varchar2 is
      L_APPROVER varchar2(100);
    begin
   
      select NEXT_USER.USER_NAME
        into L_APPROVER
        from per_pos_structure_elements str,
             per_pos_structure_versions psv,
             PER_POSITION_STRUCTURES    pps,
             per_all_assignments_f      paf_sub,
             per_all_assignments_f      paf_parent,
             fnd_user                   cur_user,
             FND_USER                   next_user
       where str.pos_structure_version_id = psv.pos_structure_version_id
         and pps.POSITION_STRUCTURE_ID = psv.POSITION_STRUCTURE_ID
         and str.SUBORDINATE_POSITION_ID = paf_sub.POSITION_ID
         and str.PARENT_POSITION_ID = paf_parent.POSITION_ID
         and paf_sub.ASSIGNMENT_TYPE = 'E'
         and paf_parent.assignment_type = 'E'
         and paf_sub.PRIMARY_FLAG = 'Y'
         and paf_parent.PRIMARY_FLAG = 'Y'
         and trunc(sysdate) between paf_sub.EFFECTIVE_START_DATE and
             nvl(paf_sub.EFFECTIVE_END_DATE, sysdate)
         and trunc(sysdate) between paf_parent.EFFECTIVE_START_DATE and
             nvl(paf_parent.EFFECTIVE_END_DATE, sysdate)
         AND PAF_SUB.PERSON_ID = CUR_USER.EMPLOYEE_ID
         and UPPER(cur_user.user_name) = p_user_name
         and paf_parent.person_id = next_user.employee_id
         and UPPER(pps.NAME) =
             upper(FND_MESSAGE.GET_STRING('PO', 'LESCO_CUST_QOUT_WF_POSHIR'))
         and psv.VERSION_NUMBER = 1;
      return L_APPROVER;
    exception
      when no_data_found then
        return null;
     
    end;
 
  BEGIN
 
    if (funcmode <> wf_engine.eng_run) then
   
      resultout := wf_engine.eng_null;
      return;
   
    end if;
 
    if (funcmode = 'RUN') then
   
      -- get prepared by and current approver   
      L_creator_user     := WF_ENGINE.GETITEMATTRTEXT(ITEMTYPE => itemtype,
                                                      ITEMKEY  => itemkey,
                                                      ANAME    => 'CREATOR');
      L_current_approver := WF_ENGINE.GETITEMATTRTEXT(ITEMTYPE => itemtype,
                                                      ITEMKEY  => itemkey,
                                                      ANAME    => 'APPROVER');
      L_QOUTE_ID         := WF_ENGINE.GETITEMATTRNUMBER(ITEMTYPE => itemtype,
                                                        ITEMKEY  => itemkey,
                                                        ANAME    => 'QOUTATION_ID');
   
      -- check if curret approver is inventory planner then end the approval process 
      if L_current_approver is null then
     
        -- get first the approver
     
        L_forward_from  := get_froward_from_role(L_creator_user);
        L_NEXT_APPROVER := get_appprover(L_creator_user);
     
        if L_NEXT_APPROVER is null then
          resultout := 'F';
          return;
        end if;
     
        WF_ENGINE.SETITEMATTRTEXT(ITEMTYPE => itemtype,
                                  ITEMKEY  => itemkey,
                                  ANAME    => 'FORWARD_FROM_USERNAME',
                                  AVALUE   => L_forward_from);
        WF_ENGINE.SETITEMATTRTEXT(ITEMTYPE => itemtype,
                                  ITEMKEY  => itemkey,
                                  ANAME    => 'APPROVER',
                                  AVALUE   => L_NEXT_APPROVER);
     
        resultout := 'T';
        return;
     
      Else
     
        L_forward_from  := get_froward_from_role(L_current_approver);
        L_NEXT_APPROVER := get_appprover(L_current_approver);
     
        if L_NEXT_APPROVER is null then
          resultout := 'F';
          return;
        End if;
     
        WF_ENGINE.SETITEMATTRTEXT(ITEMTYPE => itemtype,
                                  ITEMKEY  => itemkey,
                                  ANAME    => 'FORWARD_FROM_USERNAME',
                                  AVALUE   => L_forward_from);
        WF_ENGINE.SETITEMATTRTEXT(ITEMTYPE => itemtype,
                                  ITEMKEY  => itemkey,
                                  ANAME    => 'APPROVER',
                                  AVALUE   => L_NEXT_APPROVER);
     
        resultout := 'T';
        return;
     
      End if;
   
    end if;
 
    if (funcmode = 'CANCEL') then
      /* add your custom logic here*/
      resultout := 'T';
      return;
    end if;
 
    if (funcmode = 'RESPOND') then
   
      /* add your custom logic here*/
      resultout := 'T';
      return;
    end if;
 
    if (funcmode = 'FORWARD') then
      /* add your custom logic here*/
      resultout := wf_engine.eng_completed;
      return;
    end if;
 
    if (funcmode = 'TRANSFER') then
   
      /* add your custom logic here*/
      resultout := 'T';
      return;
    end if;
 
    if (funcmode = 'TIMEOUT') then
   
      /* add your custom logic here*/
      if (1 = 2) then
        resultout := 'F';
      else
        resultout := 'T';
      end if;
      return;
    end if;
 
  EXCEPTION
    WHEN OTHERS THEN
      resultout := 'Error:' || sqlerrm;
   
  END SET_PROCESS_DTL;
  PROCEDURE START_WF_PROCESS(P_QOUTE_ID    in number,
                             P_PREPARED_BY in varchar2) as
 
    LV_creator_user varchar2(100);
    itemtype        VARCHAR2(20) := 'XXQOUTE';
    process         VARCHAR2(20) := 'XXMAINPRC';
    itemkey         varchar2(20);
    l_error         varchar2(2000);
  BEGIN
    -- gegenrate item key
    select LESCO_QOUTE_WF_KEY_S.nextval into itemkey from dual;
 
    WF_ENGINE.Threshold := 1;
 
    WF_ENGINE.CREATEPROCESS(ITEMTYPE => itemtype,
                            ITEMKEY  => itemkey,
                            PROCESS  => process);
 
    WF_ENGINE.SETITEMATTRNUMBER(ITEMTYPE => itemtype,
                                ITEMKEY  => itemkey,
                                ANAME    => 'QOUTATION_ID',
                                AVALUE   => P_QOUTE_ID);
 
    wf_engine.SetItemOwner(itemtype => itemtype,
                           itemkey  => itemkey,
                           owner    => P_PREPARED_BY);
 
    WF_ENGINE.SETITEMATTRTEXT(ITEMTYPE => itemtype,
                              ITEMKEY  => itemkey,
                              ANAME    => 'CREATOR',
                              AVALUE   => P_PREPARED_BY);
 
      WF_ENGINE.SETITEMATTRTEXT(ITEMTYPE => itemtype,
                              ITEMKEY  => itemkey,
                              ANAME    => 'HEADER_ATTACHMENTS',
                              AVALUE   => 'plsqlblob:Lesco_Qoutations_Wf.Get_Attachment/' || P_QOUTE_ID);
 
    WF_ENGINE.STARTPROCESS(ITEMTYPE => itemtype, ITEMKEY => itemkey);
 
    wf_engine.background(itemtype         => itemtype,
                         minthreshold     => NULL,
                         maxthreshold     => NULL,
                         process_deferred => TRUE,
                         process_timeout  => FALSE,
                         process_stuck    => FALSE);
 
  EXCEPTION
    WHEN OTHERS THEN
      --    l_error := sqlerrm;
      --     cust_wf_log(P_MOVE_ORDER_ID,P_PREPARED_BY,itemkey,'Launch Error:',l_error,null);
      raise;
  end START_WF_PROCESS;
  PROCEDURE LAUNCH_WORKFLOW(P_QOUTE_ID in number, P_USER_ID in number) as
    lv_user_name varchar2(200);
 
  BEGIN
 
    Begin
      select user_name
        into lv_user_name
        from fnd_user
       where user_id = P_USER_ID;
    exception
      when others then
        null;
    End;
 
    START_WF_PROCESS(P_QOUTE_ID, lv_user_name);
  exception
    when others then
      raise;
  end LAUNCH_WORKFLOW;
  PROCEDURE get_attachment(document_id   in varchar2,
                                   display_type  in varchar2,
                                   document      in out blob,
                                   document_type in out NOCOPY varchar2)
  
     is
  
      lv_file_name           VARCHAR2 (100) := NULL;
      lv_file_content_type   VARCHAR2 (100) := NULL;
      lv_doc_in            BLOB;
      lv_document_id po_headers_all.PO_HEADER_ID%TYPE;
      lv_doc_line_id po_lines_all.PO_LINE_ID%TYPE;
      lv_length Integer;
  begin
    lv_document_id := substr(document_id,
                            instr(document_id, '/') + 1,
                            length(document_id));
   begin
 
    Select PO_LINE_ID
     into lv_doc_line_id
     from PO.PO_LINES_ALL
     where PO_HEADER_ID = lv_document_id
       and LINE_NUM = 1;
 
      select fl.file_name, fl.file_content_type, fl.file_data
       INTO lv_file_name, lv_file_content_type, lv_doc_in
      from APPLSYS.FND_ATTACHED_DOCUMENTS fad,
           APPLSYS.FND_DOCUMENTS fd ,
           APPLSYS.FND_LOBS fl
      where fad.ENTITY_NAME = any('PO_LINES' , 'PO_HEADERS')
        and fad.DOCUMENT_ID = fd.DOCUMENT_ID
        and fd.MEDIA_ID = fl.FILE_ID
        and ( fad.PK1_VALUE = lv_document_id or
                fad.PK1_VALUE = lv_doc_line_id);
      document_type := lv_file_content_type || ';name=' || lv_file_name;
      lv_length := DBMS_LOB.getlength(lv_doc_in);
     
      DBMS_LOB.COPY (dest_lob => document,
                     src_lob  => lv_doc_in,
                     amount   => lv_length);
      commit;
    Exception when no_data_found then
     Null;
   End;
  
  exception
    when others then
      wf_core.context('LESCO_QOUTATIONS_WF',
                      'get_qoute_line_details',
                      document_id);
    raise;
   
 end;
  PROCEDURE get_qoute_line_details(document_id   in varchar2,
                                   display_type  in varchar2,
                                   document      in out NOCOPY varchar2,
                                   document_type in out NOCOPY varchar2) IS
 
    l_document_id po_headers_all.PO_HEADER_ID%TYPE;
    l_org_id      po_headers_all.ORG_ID%TYPE;
    l_document    VARCHAR2(32000) := '';
    l_error       VARCHAR2(3200);
    NL            VARCHAR2(1) := fnd_global.newline;
 
    CURSOR line_csr(v_document_id NUMBER) IS
      SELECT pla.line_num LINE_NUMBER,
             pltb.line_type,
             nvl(MSI.SEGMENT1, null) ITEM,
             mck.concatenated_segments category,
             nvl(msi.description, pla.item_description) item_description,
             OOD.ORGANIZATION_NAME,
             pla.revision_num REVISION,
             pla.unit_price,
             pla.unit_meas_lookup_code UOM_CODE,
             plla.quantity PRIMARY_QUANTITY,
             pla.note_to_vendor
        FROM po_lines_all                 pla,
             po_line_types_tl             pltb,
             mtl_categories_kfv           mck,
             po_line_locations_all        plla,
             ORG_ORGANIZATION_DEFINITIONS ood,
             INV.MTL_SYSTEM_ITEMS_B       MSI
       WHERE pla.PO_HEADER_ID = v_document_id
         and pla.line_type_id = pltb.line_type_id
         and pltb.language = 'US'
         and pla.category_id = mck.category_id(+)
         and pla.po_header_id = plla.po_header_id
         and pla.po_line_id = plla.po_line_id
         and plla.ship_to_organization_id = OOD.ORGANIZATION_ID(+)
         and pla.item_id = MSI.INVENTORY_ITEM_ID(+)
         and plla.ship_to_organization_id = MSI.ORGANIZATION_ID(+)
       ORDER BY pla.LINE_NUM;
 
  BEGIN
 
    l_document_id := substr(document_id,
                            instr(document_id, '/') + 1,
                            length(document_id));
 
    --cust_wf_log('L_Document_id',l_document_id,null,null,null,null);
 
    l_document := '<table  width="100%" border="0" cellspacing="0" cellpadding="0" ><tr><td class="OraHeaderSub">Move Order Lines</td></tr><tr><td><table width="100%" summary="Move Order Lines" ><tr><td>';
    l_document := l_document ||
                  '<table class="OraTableContent" width="100%" cellpadding="1" cellspacing="0" summary="Move Order Lines" border="1" >';
    l_document := l_document || '<tr>';
    l_document := l_document ||
                  '<th class="OraTableColumnHeader" scope="col" width="5%" align="LEFT" valign="baseline" ><span class="OraTableHeaderLink">Line No</span></th>';
    l_document := l_document ||
                  '<th class="OraTableColumnHeader" scope="col" width="5%" align="LEFT" valign="baseline" ><span class="OraTableHeaderLink">Line Type</span></th>';
    l_document := l_document ||
                  '<th class="OraTableColumnHeader" scope="col" width="10%" align="LEFT" valign="baseline" ><span class="OraTableHeaderLink">Item No</span></th>';
    l_document := l_document ||
                  '<th class="OraTableColumnHeader" scope="col" width="10%" align="LEFT" valign="baseline" ><span class="OraTableHeaderLink">Category</span></th>';
    l_document := l_document ||
                  '<th class="OraTableColumnHeader" scope="col" width="15%" align="LEFT" valign="baseline" ><span class="OraTableHeaderLink">Description</span></th>';
    l_document := l_document ||
                  '<th class="OraTableColumnHeader" scope="col" width="15%" align="LEFT" valign="baseline" ><span class="OraTableHeaderLink">UOM</span></th>';
    l_document := l_document ||
                  '<th class="OraTableColumnHeader" scope="col" width="15%" align="LEFT" valign="baseline" ><span class="OraTableHeaderLink">Quantity</span></th>';
    l_document := l_document ||
                  '<th class="OraTableColumnHeader" scope="col" width="15%" align="LEFT" valign="baseline" ><span class="OraTableHeaderLink">Unit Price</span></th>';
    l_document := l_document ||
                  '<th class="OraTableColumnHeader" scope="col" width="40%" align="LEFT" valign="baseline" ><span class="OraTableHeaderLink">Note to Supplier</span></th>';
    l_document := l_document || '</tr>';
 
    For rec in line_csr(l_document_id) loop
      l_document := l_document || '<tr>';
      l_document := l_document ||
                    '<td align="CENTER" valign="baseline"  class="OraTableCellText OraTableBorder1100">' ||
                    rec.LINE_NUMBER || '</td>';
      l_document := l_document ||
                    '<td align="CENTER" valign="baseline"  class="OraTableCellText OraTableBorder1100">' ||
                    rec.LINE_TYPE || '</td>';
      l_document := l_document ||
                    '<td align="LEFT" valign="baseline"  class="OraTableCellText OraTableBorder1100">' ||
                    rec.ITEM || '</td>';
      l_document := l_document ||
                    '<td align="LEFT" valign="baseline"  class="OraTableCellText OraTableBorder1100">' ||
                    rec.category || '</td>';
      l_document := l_document ||
                    '<td align="LEFT" valign="baseline"  class="OraTableCellText OraTableBorder1100">' ||
                    rec.item_description || '</td>';
      l_document := l_document ||
                    '<td align="LEFT" valign="baseline"  class="OraTableCellText OraTableBorder1100">' ||
                    rec.UOM_CODE || '</td>';
      l_document := l_document ||
                    '<td align="LEFT" valign="baseline"  class="OraTableCellText OraTableBorder1100">' ||
                    rec.PRIMARY_QUANTITY || '</td>';
      l_document := l_document ||
                    '<td align="LEFT" valign="baseline"  class="OraTableCellText OraTableBorder1100">' ||
                    rec.unit_price || '</td>';
      l_document := l_document ||
                    '<td align="LEFT" valign="baseline"  class="OraTableCellText OraTableBorder1100">' ||
                    rec.note_to_vendor || '</td>';
      l_document := l_document || '</tr>';
    end loop;
    l_document := l_document || '</table>';
    l_document := l_document || '</td></tr></table>';
 
    document := l_document;
 
    document_type := 'text/html';
  exception
    when others then
      wf_core.context('LESCO_QOUTATIONS_WF',
                      'get_qoute_line_details',
                      document_id);
    raise;
  END;
  procedure VERIFY_QOUTE_DETAILS(itemtype  IN VARCHAR2,
                                 itemkey   IN VARCHAR2,
                                 actid     IN NUMBER,
                                 funcmode  IN VARCHAR2,
                                 resultout IN OUT NOCOPY VARCHAR2) AS
    lv_header_id         number;
    lv_qoute_NUMBER      varchar2(100);
    lv_description       varchar2(500);
    LV_TYPE              varchar2(100);
    LV_rfq_num           varchar2(100);
    lv_agent_name        varchar2(100);
    LV_FROM_SUBINVENTORY varchar2(100);
    LV_DATE_REQUIRED     date;
    lv_document_id       CLOB;
  begin
 
    if (funcmode <> wf_engine.eng_run) then
   
      resultout := wf_engine.eng_null;
      return;
   
    end if;
 
    if (funcmode = 'RUN') then
   
      -- get approval notif message body text
      lv_header_id := WF_ENGINE.GETITEMATTRNUMBER(ITEMTYPE => itemtype,
                                                  ITEMKEY  => itemkey,
                                                  ANAME    => 'QOUTATION_ID');
   
      begin
     
        SELECT a.segment1         qoute_number,
               a.COMMENTS         description,
               ppf.full_name,
               A.TYPE_LOOKUP_CODE,
               b.segment1         rfq_number,
               A.REPLY_DATE
          into lv_qoute_NUMBER,
               lv_description,
               lv_agent_name,
               LV_TYPE,
               LV_rfq_num,
               LV_DATE_REQUIRED
          FROM po_headers_all a, po_headers_all b, per_people_f ppf
         WHERE a.PO_HEADER_ID = lv_header_id
           and A.FROM_HEADER_ID = B.PO_HEADER_ID (+)
           and a.STATUS_LOOKUP_CODE = 'A'
           and nvl(a.approved_flag, 'N') = 'N'
           and A.AGENT_ID = PPF.PERSON_ID
           and PPF.EFFECTIVE_END_DATE >= sysdate;
     
      exception
        when no_data_found then
          resultout := 'F:' || sqlerrm;
          return;
      end;
   
      WF_ENGINE.SETITEMATTRTEXT(ITEMTYPE => itemtype,
                                ITEMKEY  => itemkey,
                                ANAME    => 'QOUTE_NUMBER',
                                AVALUE   => lv_qoute_NUMBER);
      WF_ENGINE.SETITEMATTRTEXT(ITEMTYPE => itemtype,
                                ITEMKEY  => itemkey,
                                ANAME    => 'AGENT_NAME',
                                AVALUE   => lv_agent_name);
      WF_ENGINE.SETITEMATTRTEXT(ITEMTYPE => itemtype,
                                ITEMKEY  => itemkey,
                                ANAME    => 'RFQ_NUM',
                                AVALUE   => LV_rfq_num);
      WF_ENGINE.SETITEMATTRTEXT(ITEMTYPE => itemtype,
                                ITEMKEY  => itemkey,
                                ANAME    => 'DESCRIPTION',
                                AVALUE   => lv_description);
      -- WF_ENGINE.SETITEMATTRTEXT(ITEMTYPE => itemtype,ITEMKEY => itemkey ,ANAME => 'FROM_SUBINVENTORY',AVALUE => LV_FROM_SUBINVENTORY);
      WF_ENGINE.SETITEMATTRTEXT(ITEMTYPE => itemtype,
                                ITEMKEY  => itemkey,
                                ANAME    => 'DATE_REQUIRED',
                                AVALUE   => LV_DATE_REQUIRED);
   
      lv_document_id := 'plsql:lesco_qoutations_wf.get_qoute_line_details/' ||
                        lv_header_id;
      --cust_wf_log('Document_id',lv_document_id,'Setting string','Point 0',null,null);
      WF_ENGINE.SETITEMATTRTEXT(ITEMTYPE => itemtype,
                                ITEMKEY  => itemkey,
                                ANAME    => 'LINE_DETAILS',
                                AVALUE   => lv_document_id);
   
      resultout := 'T';
      return;
    end if;
 
  exception
    when others then
      resultout := 'F:' || sqlerrm;
  end VERIFY_QOUTE_DETAILS;
  Procedure Upd_TO_Approved(itemtype in varchar2,
                            itemkey  in varchar2,
                            actid    in number,
                            funcmode in varchar2,
                            result   out nocopy varchar2) is
    l_header_id Number;
    l_debug     number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
  Begin
    if (funcmode = 'RUN') then
   
      l_header_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
                                                 itemkey  => itemkey,
                                                 aname    => 'QOUTATION_ID');
   
      BEGIN
     
        UPDATE PO_HEADERS_ALL
           SET APPROVED_FLAG = 'Y',
               APPROVED_DATE = sysdate,
               status_lookup_code = 'A'
         WHERE PO_HEADER_ID = l_header_id
           AND TYPE_LOOKUP_CODE = 'QUOTATION';
     
        UPDATE po_line_locations_all
           SET APPROVED_FLAG = 'Y', APPROVED_DATE = sysdate
         WHERE PO_HEADER_ID = l_header_id;
     
        COMMIT;
     
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK;
      END;
   
      result := 'COMPLETE';
      return;
    end if;
 
    if (funcmode = 'CANCEL') then
      result := 'COMPLETE';
      return;
    else
      result := '';
      return;
    end if;
 
  exception
    when others then
      wf_core.context('LESCO_QOUTATIONS_WF',
                      'Upd_TO_Approved',
                      itemtype,
                      itemkey,
                      to_char(actid),
                      funcmode);
      raise;
   
  End Upd_TO_Approved;
  Procedure Upd_TO_Part_Approved(itemtype in varchar2,
                                 itemkey  in varchar2,
                                 actid    in number,
                                 funcmode in varchar2,
                                 result   out nocopy varchar2) is
    l_header_id Number;
    l_debug     number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
  Begin
    if (funcmode = 'RUN') then
      l_header_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
                                                 itemkey  => itemkey,
                                                 aname    => 'QOUTATION_ID');
   
      result := 'COMPLETE';
      return;
    end if;
 
    if (funcmode = 'CANCEL') then
      result := 'COMPLETE';
      return;
    else
      result := '';
      return;
   
    end if;
 
  exception
    when others then
      wf_core.context('LESCO_QOUTATIONS_WF',
                      'Upd_TO_Part_Approved',
                      itemtype,
                      itemkey,
                      to_char(actid),
                      funcmode);
      raise;
   
  End Upd_TO_Part_Approved;
  Procedure Upd_TO_Rejected(itemtype in varchar2,
                            itemkey  in varchar2,
                            actid    in number,
                            funcmode in varchar2,
                            result   out nocopy varchar2) is
    l_header_id Number;
    l_debug     number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
  Begin
    if (funcmode = 'RUN') then
   
      l_header_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
                                                 itemkey  => itemkey,
                                                 aname    => 'QOUTATION_ID');
   
      BEGIN
     
        UPDATE PO_HEADERS_ALL
           SET APPROVED_FLAG = 'N',
               APPROVED_DATE = sysdate,
               STATUS_LOOKUP_CODE  = 'C'
         WHERE PO_HEADER_ID = l_header_id
           AND TYPE_LOOKUP_CODE = 'QUOTATION';
     
        UPDATE po_line_locations_all
           SET APPROVED_FLAG = 'N', APPROVED_DATE = sysdate
         WHERE PO_HEADER_ID = l_header_id;
     
        COMMIT;
     
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK;
      END;
   
      result := 'COMPLETE';
      return;
   
    end if;
 
    if (funcmode = 'CANCEL') then
   
      result := 'COMPLETE';
      return;
    else
      result := '';
      return;
   
    end if;
 
  exception
    when others then
      wf_core.context('LESCO_QOUTATIONS_WF',
                      'Upd_TO_Rejected',
                      itemtype,
                      itemkey,
                      to_char(actid),
                      funcmode);
      raise;
   
  End Upd_TO_Rejected;
  Procedure Upd_TO_Pending(itemtype in varchar2,
                           itemkey  in varchar2,
                           actid    in number,
                           funcmode in varchar2,
                           result   out nocopy varchar2) as
    l_header_id Number;
    l_debug     number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
  Begin
    if (funcmode = 'RUN') then
   
      l_header_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
                                                 itemkey  => itemkey,
                                                 aname    => 'QOUTATION_ID');
   
      BEGIN
     
        UPDATE PO_HEADERS_ALL
           SET status_lookup_code = 'I'
         WHERE PO_HEADER_ID = l_header_id
           AND TYPE_LOOKUP_CODE = 'QUOTATION';
     
        COMMIT;
     
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK;
      END;
   
      result := 'COMPLETE';
      return;
   
    end if;
 
    if (funcmode = 'CANCEL') then
   
      result := 'COMPLETE';
      return;
    else
      result := '';
      return;
   
    end if;
 
  exception
    when others then
      wf_core.context('LESCO_QOUTATIONS_WF',
                      'Upd_TO_Pending',
                      itemtype,
                      itemkey,
                      to_char(actid),
                      funcmode);
      raise;
   
  End Upd_TO_Pending;

END XXCUS_QOUTATIONS_WF;
/

Download Worflow