DBA Data[Home] [Help]

APPS.PO_APPROVAL_LIST_WF1S SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 39

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 50

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 99

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 111

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 171

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 201

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 221

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 232

           /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 235

        PO_APPROVALLIST_S1.update_approval_list_itemkey(
                         p_approval_list_header_id=>l_approval_list_header_id,
                         p_itemtype=>itemtype,
                         p_itemkey=>itemkey,
                         p_return_code=>l_return_code);
Line: 241

        l_progress := 'Find_Approval_List: 005- update_approval_list_itemkey - '||
                       to_char(l_return_code);
Line: 244

           /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 323

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 342

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 379

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 396

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 403

            /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 414

                           p_last_update_date=>null,
                           p_return_code=>l_return_code,
                           p_error_stack=>l_error_stack);
Line: 421

           /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 430

             /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 433

          PO_APPROVALLIST_S1.update_approval_list_itemkey(
                          p_approval_list_header_id=>l_approval_list_header_id,
                          p_itemtype=>itemtype,
                          p_itemkey=>itemkey,
                          p_return_code=>l_return_code);
Line: 440

                         '- update_approval_list_itemkey-'||
                         to_char(l_return_code);
Line: 443

             /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 460

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 467

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 478

            /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 496

          /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 507

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 560

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 590

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 605

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 676

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 706

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 721

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 792

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 823

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 838

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 913

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 943

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 958

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 985

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1069

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1102

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1117

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1150

procedure Insert_Action_History(itemtype        in varchar2,
                                itemkey         in varchar2,
                                actid           in number,
                                funcmode        in varchar2,
                                resultout       out NOCOPY varchar2) IS

  l_progress                  VARCHAR2(100) := '000';
Line: 1169

    l_progress := 'Insert_Action_History: 001';
Line: 1171

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1200

       /* update po action history */
      PO_APPROVAL_LIST_HISTORY_SV.Forward_Action_History(itemtype=>itemtype,
                                             itemkey=>itemkey,
                                             x_forward_to_id=>l_next_approver_id,
					     x_req_header_id=>l_req_header_id,
                                             x_approval_path_id=>l_approval_path_id);
Line: 1207

      l_progress := 'Insert_Action_History: 005 - Forward_Action_History';
Line: 1209

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1229

    l_progress := 'Insert_Action_History: 999';
Line: 1231

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1238

    wf_core.context('PO_APPROVAL_LIST_WF1S','Insert_Action_History',l_progress,sqlerrm);
Line: 1239

    PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.INSERT_ACTION_HISTORY');
Line: 1242

END Insert_Action_History;
Line: 1244

procedure Update_Action_History_Approve(itemtype        in varchar2,
                                itemkey         in varchar2,
                                actid           in number,
                                funcmode        in varchar2,
                                resultout       out NOCOPY varchar2) IS

  l_progress                  VARCHAR2(100) := '000';
Line: 1267

    l_progress := 'Update_Action_History_Approve: 001';
Line: 1269

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1302

     l_progress := 'Update_Action_History_Approve: 002-'||
                           to_char(l_document_id)||'-'||
                           l_document_type||'-'||l_document_subtype;
Line: 1306

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1309

     /* update po action history */
     PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History(itemtype=>itemtype,
                                         itemkey=>itemkey,
                                         x_action=>l_action,
                                         x_req_header_id=>l_document_id,
                                         x_last_approver=>l_result,
                                         x_note=>l_note);
Line: 1317

     l_progress := 'Update_Action_History_Approve: 005 - Update_Action_History';
Line: 1319

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1325

     l_progress := 'Update_Action_History_Approve: 006';
Line: 1327

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1335

    l_progress := 'Update_Action_History_Approve: 999';
Line: 1337

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1345

    wf_core.context('PO_APPROVAL_LIST_WF1S','Update_Action_History_Approve',l_progress,sqlerrm);
Line: 1346

    PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_ACTION_HISTORY_APPROVE');
Line: 1349

END Update_Action_History_Approve;
Line: 1352

procedure Update_Action_History_Timeout(itemtype        in varchar2,
                                itemkey         in varchar2,
                                actid           in number,
                                funcmode        in varchar2,
                                resultout       out NOCOPY varchar2) IS

  l_progress                  VARCHAR2(100) := '000';
Line: 1374

    l_progress := 'Update_Action_History_Timeout: 001';
Line: 1376

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1405

     l_progress := 'Update_Action_History_Timeout: 002-'||
                           to_char(l_document_id)||'-'||
                           l_document_type||'-'||l_document_subtype;
Line: 1410

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1413

     /* update po action history */
     PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History(itemtype=>itemtype,
                                         itemkey=>itemkey,
                                         x_action=>l_action,
                                         x_req_header_id=>l_document_id,
                                         x_last_approver=>l_result,
                                         x_note=>l_note);
Line: 1421

     l_progress := 'Update_Action_History_Timeout: 003- Update_Action_History';
Line: 1424

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1427

     l_progress := 'Update_Action_History_App_Fwd: 004';
Line: 1430

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1438

    l_progress := 'Update_Action_History_Timeout: 999';
Line: 1440

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1447

    wf_core.context('PO_APPROVAL_LIST_WF1S','Update_Action_History_Timeout',l_progress,sqlerrm);
Line: 1448

    PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_ACTION_HISTORY_TIMEOUT');
Line: 1451

END Update_Action_History_Timeout;
Line: 1454

procedure Update_Action_History_App_Fwd(itemtype        in varchar2,
                                itemkey         in varchar2,
                                actid           in number,
                                funcmode        in varchar2,
                                resultout       out NOCOPY varchar2) IS

  l_progress                  VARCHAR2(100) := '000';
Line: 1476

    l_progress := 'Update_Action_History_App_Fwd: 001';
Line: 1478

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1511

     l_progress := 'Update_Action_History_App_Fwd: 002-'||
                           to_char(l_document_id)||'-'||
                           l_document_type||'-'||l_document_subtype;
Line: 1515

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1518

     /* update po action history */
     PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History(itemtype=>itemtype,
                                         itemkey=>itemkey,
                                         x_action=>l_action,
                                         x_req_header_id=>l_document_id,
                                         x_last_approver=>l_result,
                                        x_note=>l_note);
Line: 1526

     l_progress := 'Update_Action_History_App_Fwd: 005- Update_Action_History';
Line: 1528

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1534

     l_progress := 'Update_Action_History_App_Fwd: 006';
Line: 1536

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1544

    l_progress := 'Update_Action_History_App_Fwd: 999';
Line: 1546

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1553

    wf_core.context('PO_APPROVAL_LIST_WF1S','Update_Action_History_App_Fwd',l_progress,sqlerrm);
Line: 1554

    PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_ACTION_HISTORY_APP_FWD');
Line: 1557

END Update_Action_History_App_Fwd;
Line: 1559

procedure Update_Action_History_Forward(itemtype        in varchar2,
                                itemkey         in varchar2,
                                actid           in number,
                                funcmode        in varchar2,
                                resultout       out NOCOPY varchar2) IS

  l_progress                  VARCHAR2(100) := '000';
Line: 1581

    l_progress := 'Update_Action_History_Forward: 001';
Line: 1583

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1616

     l_progress := 'Update_Action_History_Forward: 002-'||
                           to_char(l_document_id)||'-'||
                           l_document_type||'-'||l_document_subtype;
Line: 1620

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1623

     /* update po action history */
     PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History(itemtype=>itemtype,
                                         itemkey=>itemkey,
                                         x_action=>l_action,
                                         x_req_header_id=>l_document_id,
                                         x_last_approver=>l_result,
                                         x_note=>l_note);
Line: 1631

     l_progress := 'Update_Action_History_Forward: 005- Update_Action_History';
Line: 1633

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1639

     l_progress := 'Update_Action_History_Forward: 006';
Line: 1641

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1648

    l_progress := 'Update_Action_History_Forward: 999';
Line: 1650

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1657

    wf_core.context('PO_APPROVAL_LIST_WF1S','Update_Action_History_Forward',l_progress,sqlerrm);
Line: 1658

    PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_ACTION_HISTORY_FORWARD');
Line: 1661

END Update_Action_History_Forward;
Line: 1663

procedure Update_Action_History_Reject(itemtype        in varchar2,
                                itemkey         in varchar2,
                                actid           in number,
                                funcmode        in varchar2,
                                resultout       out NOCOPY varchar2) IS

  l_progress                  VARCHAR2(100) := '000';
Line: 1685

    l_progress := 'Update_Action_History_Reject: 001';
Line: 1687

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1720

     l_progress := 'Update_Action_History_Reject: 002-'||
                           to_char(l_document_id)||'-'||
                           l_document_type||'-'||l_document_subtype;
Line: 1724

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1727

     /* update po action history */
     PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History(itemtype=>itemtype,
                                         itemkey=>itemkey,
                                         x_action=>l_action,
                                         x_req_header_id=>l_document_id,
                                         x_last_approver=>l_result,
                                         x_note=>l_note);
Line: 1735

     l_progress := 'Update_Action_History_Reject: 005 - Update_Action_History';
Line: 1737

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1743

     l_progress := 'Update_Action_History_Reject: 006';
Line: 1745

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1752

    l_progress := 'Update_Action_History_Reject: 999';
Line: 1754

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1761

    wf_core.context('PO_APPROVAL_LIST_WF1S','Update_Action_History_Reject',l_progress,sqlerrm);
Line: 1762

    PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_ACTION_HISTORY_REJECT');
Line: 1765

END Update_Action_History_Reject;
Line: 1768

** Desc: We now use the new API: Update_App_List_Resp_Success.
**
** Also reverted the change of bug# 1394711 in the old API
** 'Update_Approval_List_Response'. It will now return 'ACTIVITY_PERFORMED'
** as before the fix in bug# 1394711.
**
** This is to support reqs submitted for approval before applying this bug
** fix otherwise their approval will error out due to change in return value.
**
** For reqs submitted for approval after bug# 1394711 and before this new
** fix, modified the API 'Update_Approval_List_Response' to check for the
** expected result type for that req approval process and return
** 'SUCCESS-FAILURE' instead of 'ACTIVITY_PERFORMED' if the expected result
** lookup type is 'PO_SUCCESS_FAILURE'.
*/

procedure Update_Approval_List_Response(itemtype        in varchar2,
                                itemkey         in varchar2,
                                actid           in number,
                                funcmode        in varchar2,
                                resultout       out NOCOPY varchar2) IS
  CURSOR c_group_id (p_itemtype VARCHAR2, p_itemkey VARCHAR2, p_activity_name VARCHAR2) IS
    SELECT notification_id
    FROM   wf_item_activity_statuses_v
    WHERE  item_type = p_itemtype
    AND    item_key = p_itemkey
    AND    activity_name = p_activity_name
    ORDER BY activity_end_date DESC;
Line: 1805

    SELECT '1'
     FROM   WF_NOTIFICATIONS
    WHERE   notification_id = notif_id
      AND   status = 'CANCELED';
Line: 1811

    SELECT recipient_role, attribute_value
    FROM   wf_notification_attr_resp_v
    WHERE  group_id = p_group_id
    AND    attribute_name = 'RESULT';
Line: 1817

    SELECT attribute_value
    FROM   wf_notification_attr_resp_v
    WHERE  group_id = p_group_id
    AND    attribute_name = 'NOTE';
Line: 1826

    SELECT nvl((wfu.orig_system_id), -9996)
    FROM   wf_users wfu
    WHERE  wfu.name = wf_notification.responder(p_notification_id)
    AND    wfu.orig_system not in ('POS', 'ENG_LIST', 'CUST_CONT');
Line: 1834

    SELECT nvl((wfu.orig_system_id), -9996)
    FROM   wf_users wfu
    WHERE  wfu.name = p_responder
    AND    wfu.orig_system not in ('HZ_PARTY', 'POS', 'ENG_LIST', 'CUST_CONT');
Line: 1853

  E_UPDATE_RESPONSE_FAIL      EXCEPTION;
Line: 1875

  l_progress := 'Update_Approval_List_Response: 001- at beginning of function';
Line: 1877

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1892

** fix, modified the API 'Update_Approval_List_Response' to check for the
** expected result type for that req approval process and return
** 'SUCCESS-FAILURE' instead of 'ACTIVITY_PERFORMED' if the expected result
** lookup type is 'PO_SUCCESS_FAILURE'.
**
** We can achieve this by running the following sql which returns
** the expected result type.
*/

    Begin
        select wa.result_type
        into exp_result
	from wf_activities wa,
	     wf_process_activities wpa,
	     wf_items wi
	where wpa.instance_id = actid
	and wpa.process_item_type = wa.item_type
	and wpa.activity_name = wa.name
	and wi.item_type = wpa.process_item_type
	and wi.item_key =  itemkey
	and wi.begin_date > wa.begin_date
	and wi.begin_date <= nvl(wa.end_date,wi.begin_date);
Line: 1920

    l_progress := 'Update_Approval_List_Response : 001-2'||
                  'exp_result: ' || exp_result || ' actid: ' || to_char(actid);
Line: 1923

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1941

   l_progress := 'Update_Approval_List_Response: 002';
Line: 1943

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1950

   l_progress := 'Update_Approval_List_Response: 003';
Line: 1952

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1964

      l_progress := 'Update_Approval_List_Response: 0031';
Line: 1966

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1975

     l_progress := 'Update_Approval_List_Response: 0032';
Line: 1977

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1988

        l_progress := 'Update_Approval_List_Response: 0033';
Line: 1990

          /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1999

       l_progress := 'Update_Approval_List_Response: 0034';
Line: 2001

          /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2007

       l_progress := 'Update_Approval_List_Response: 00341';
Line: 2009

          /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2017

         l_progress := 'Update_Approval_List_Response: 00342';
Line: 2040

   l_progress := 'Update_Approval_List_Response: 0035';
Line: 2042

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2050

    l_progress := 'Update_Approval_List_Response: 004';
Line: 2052

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2058

	SELECT wfn.responder, wfn.recipient_role, wfn.end_date
	INTO l_responder, l_recipient_role, l_end_date
	FROM   wf_notifications wfn
	WHERE  wfn.notification_id = l_group_id;
Line: 2086

      l_progress := 'Update_Approval_List_Response: 005';
Line: 2088

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2103

      l_progress := 'Update_Approval_List_Response: 007 -' || l_responder_user_name;
Line: 2105

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2118

      l_progress := 'Update_Approval_List_Response: 008' ;
Line: 2120

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2129

       l_progress := 'Update_Approval_List_Response: 009' ;
Line: 2131

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2154

      l_progress := 'Update_Approval_List_Response: 010 APP'||
                       to_char(l_approver_id)||
                       ' RES'||to_char(l_responder_id)||
                       ' FWD'||to_char(l_forward_to_id);
Line: 2159

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2162

      PO_APPROVALLIST_S1.update_approval_list_response(
                      p_document_id=>l_document_id,
                      p_document_type=>l_document_type,
                      p_document_subtype=>l_document_subtype,
                      p_itemtype=>itemtype,
                      p_itemkey=>itemkey,
                      p_approver_id=>l_approver_id,
                      p_responder_id=>l_responder_id,
                      p_forward_to_id=>l_forward_to_id,
                      p_response=>l_value,
                      p_response_date=>l_end_date,
                      p_comments=>substrb(l_note,1,480), -- bug 3105327
                      p_return_code=>l_return_code);
Line: 2176

      l_progress := 'Update_Approval_List_Response: 011'||to_char(l_return_code);
Line: 2178

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2186

         l_progress := 'Update_Approval_List_Response: 012';
Line: 2188

            /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2200

         l_progress := 'Update_Approval_List_Response : 013'||to_char(l_return_code);
Line: 2202

            /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2221

       ** Desc: The Update_Approval_List_Response() procedure raises exception
       ** when the rebuild_approval_list() fails and the approval workflow
       ** hangs. We need to handle the situation when the rebuild_approval_list()
       ** fails because of No approver found in order to return the Requisition
       ** to the preparer.
       **
       ** Changed the procedure Update_Approval_List_Response() to return FAILURE
       ** for the above condition or SUCCESS instead of ACTIVITY_PERFORMED.
       **
       ** The Requsition workflow also has been changed to handle the above.
       **
       ** Dependency: poxwfrqa.wft
       */

         ELSIF l_return_code = PO_APPROVALLIST_S1.E_NO_ONE_HAS_AUTHORITY THEN

            /* Bug# 1712121 */

            if retnew = TRUE then
              resultout:='COMPLETE' || ':' ||  'FAILURE';
Line: 2247

          l_progress := 'Update_Approval_List_Response : 100';
Line: 2249

             /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2262

     END IF; -- update success
Line: 2267

   l_progress := 'Update_Approval_List_Response : 999';
Line: 2269

     /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2272

  RAISE E_UPDATE_RESPONSE_FAIL;
Line: 2276

 WHEN E_UPDATE_RESPONSE_FAIL THEN
   IF (c_group_id%ISOPEN) THEN
     CLOSE c_group_id;
Line: 2287

                   'Update_Approval_List_Response E_FAILURE',
                   l_progress,l_return_code,sqlerrm);
Line: 2291

   PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_APPROVAL_LIST_RESPONSE');
Line: 2306

                   'Update_Approval_List_Response',l_progress,sqlerrm);
Line: 2308

   PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_APPROVAL_LIST_RESPONSE');
Line: 2312

END Update_Approval_List_Response;
Line: 2352

    SELECT notification_id
    FROM   wf_item_activity_statuses_v
    WHERE  item_type = p_itemtype
    AND    item_key = p_itemkey
    AND    activity_name in ( p_activity_name, p_activity_name2,
                              p_activity_name3, p_activity_name4)
    ORDER BY activity_end_date DESC;
Line: 2361

    SELECT '1'
     FROM   WF_NOTIFICATIONS
    WHERE   notification_id = notif_id
      AND   status = 'CANCELED';
Line: 2367

    SELECT recipient_role, attribute_value
    FROM   wf_notification_attr_resp_v
    WHERE  group_id = p_group_id
    AND    attribute_name = 'RESULT';
Line: 2373

    SELECT attribute_value
    FROM   wf_notification_attr_resp_v
    WHERE  group_id = p_group_id
    AND    attribute_name = 'NOTE';
Line: 2380

    SELECT nvl((wfu.orig_system_id), -9996)
    FROM   wf_users wfu
    WHERE  wfu.name = p_responder
    AND    wfu.orig_system not in ('HZ_PARTY', 'POS', 'ENG_LIST', 'CUST_CONT');
Line: 2403

  l_progress := 'Update_App_List_Resp_Success: 001- at beginning of function';
Line: 2405

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2423

   l_progress := 'Update_App_List_Resp_Success: 002';
Line: 2425

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2433

   l_progress := 'Update_App_List_Resp_Success: 003';
Line: 2435

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2448

      l_progress := 'Update_App_List_Resp_Success: 0031';
Line: 2450

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2459

     l_progress := 'Update_App_List_Resp_Success: 0032';
Line: 2461

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2472

        l_progress := 'Update_App_List_Resp_Success: 0033';
Line: 2474

          /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2482

       l_progress := 'Update_App_List_Resp_Success: 0034';
Line: 2484

          /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2490

       l_progress := 'Update_App_List_Resp_Success: 00341';
Line: 2492

          /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2500

         l_progress := 'Update_App_List_Resp_Success: 00342';
Line: 2523

   l_progress := 'Update_App_List_Resp_Success: 0035';
Line: 2525

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2533

    l_progress := 'Update_App_List_Resp_Success: 004';
Line: 2535

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2541

	SELECT wfn.responder, wfn.recipient_role, wfn.end_date
	INTO l_responder, l_recipient_role, responseEndDate
	FROM   wf_notifications wfn
	WHERE  wfn.notification_id = l_group_id;
Line: 2568

      l_progress := 'Update_App_List_Resp_Success: 005';
Line: 2570

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2585

      l_progress := 'Update_App_List_Resp_Success: 007 -' || l_responder_user_name;
Line: 2587

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2600

      l_progress := 'Update_App_List_Resp_Success: 008' ;
Line: 2602

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2611

       l_progress := 'Update_App_List_Resp_Success: 009' ;
Line: 2613

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2618

   l_progress := 'Update_App_List_Resp_Success : 999';
Line: 2620

     /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2634

                   'Update_App_List_Resp_Success',l_progress,sqlerrm);
Line: 2646

** Update_Approval_List_Response from 'Activity Performed' to 'SUCCESS/FAILURE'.
** This changed was made to the API as well as the workflow.
**
** Now the reqs created after applying this patch would work fine but the
** requisitions submitted for approval before applying this fix, which are still
** in process, get stuck when the users try to approve them.
**
** In scenarios when we have to change the return type in wf, we should
** create a new API and leave the old one as it is. Now the workflow activity
** should be calling the new API. With this the new reqs will work fine as the
** new API will be returning the changed return types as expected by the new
** workflow definition and also the reqs submitted for approval before the fix
** will also work fine as the old workflow definition will be calling the old
** API which still returns the return types as expected by the old definition.
**
** Created a new API 'Update_App_List_Resp_Success'. This API will return
** 'SUCCESS-FAILURE'. The workflow activity 'Update Approval List Response'
** has also been changed to call this new API.
**
** Also reverted the change of bug# 1394711 in the old API
** 'Update_Approval_List_Response'. It will now return 'ACTIVITY_PERFORMED'
** as before the fix in bug# 1394711.
*/

procedure Update_App_List_Resp_Success(itemtype        in varchar2,
                                itemkey         in varchar2,
                                actid           in number,
                                funcmode        in varchar2,
                                resultout       out NOCOPY varchar2) IS

  l_progress                  VARCHAR2(1000) := '000';
Line: 2688

  E_UPDATE_RESPONSE_FAIL      EXCEPTION;
Line: 2698

  l_progress := 'Update_App_List_Resp_Success: 001- at beginning of function';
Line: 2700

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2745

      l_progress := 'Update_App_List_Resp_Success: 010 APP'||
                       to_char(l_approver_id)||
                       ' RES'||to_char(l_responder_id)||
                       ' FWD'||to_char(l_forward_to_id);
Line: 2750

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2753

      PO_APPROVALLIST_S1.update_approval_list_response(
                      p_document_id=>l_document_id,
                      p_document_type=>l_document_type,
                      p_document_subtype=>l_document_subtype,
                      p_itemtype=>itemtype,
                      p_itemkey=>itemkey,
                      p_approver_id=>l_approver_id,
                      p_responder_id=>l_responder_id,
                      p_forward_to_id=>l_forward_to_id,
                      p_response=>l_value,
                      p_response_date=>l_end_date,
                      p_comments=>substrb(l_note,1,480), -- bug 3105327
                      p_return_code=>l_return_code);
Line: 2767

      l_progress := 'Update_App_List_Resp_Success: 011'||to_char(l_return_code);
Line: 2769

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2777

         l_progress := 'Update_App_List_Resp_Success: 012';
Line: 2779

            /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2790

         l_progress := 'Update_App_List_Resp_Success : 013'||to_char(l_return_code);
Line: 2792

            /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2805

       ** Desc: The Update_Approval_List_Response() procedure raises exception
       ** when the rebuild_approval_list() fails and the approval workflow
       ** hangs. We need to handle the situation when the rebuild_approval_list()
       ** fails because of No approver found in order to return the Requisition
       ** to the preparer.
       **
       ** Changed the procedure Update_Approval_List_Response() to return FAILURE
       ** for the above condition or SUCCESS instead of ACTIVITY_PERFORMED.
       **
       ** The Requsition workflow also has been changed to handle the above.
       **
       ** Dependency: poxwfrqa.wft
       */

         ELSIF l_return_code = PO_APPROVALLIST_S1.E_NO_ONE_HAS_AUTHORITY THEN
            resultout:='COMPLETE' || ':' ||  'FAILURE';
Line: 2841

     END IF; -- update success
Line: 2843

    l_progress := 'Update_App_List_Resp_Success : 999';
Line: 2845

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2848

    RAISE E_UPDATE_RESPONSE_FAIL;
Line: 2855

 WHEN E_UPDATE_RESPONSE_FAIL THEN

   l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
Line: 2860

                   'Update_App_List_Resp_Success E_FAILURE',
                   l_progress,l_return_code,sqlerrm);
Line: 2865

   sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_APP_LIST_RESP_SUCCESS');
Line: 2874

                   'Update_App_List_Resp_Success',l_progress,sqlerrm);
Line: 2877

   sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_APP_LIST_RESP_SUCCESS');
Line: 2881

END Update_App_List_Resp_Success;
Line: 3011

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 3025

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 3042

                /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 3053

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 3063

** Desc: Added new wf api to insert null action before
** Reserving a Requisition, if the null action does not exists.
** Otherwise the Reserve action is not recorded.
*/
procedure Insert_Res_Action_History(itemtype    in varchar2,
                                itemkey         in varchar2,
                                actid           in number,
                                funcmode        in varchar2,
                                resultout       out NOCOPY varchar2) IS

  l_progress                  VARCHAR2(100) := '000';
Line: 3084

    l_progress := 'Insert_Res_Action_History: 001';
Line: 3086

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 3120

      l_progress := 'Insert_Res_Action_History: 005 - Reserve_Action_History';
Line: 3122

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 3130

    l_progress := 'Insert_Res_Action_History: 999';
Line: 3132

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 3139

    wf_core.context('PO_APPROVAL_LIST_WF1S','Insert_Res_Action_History',
                     l_progress,sqlerrm);
Line: 3142

           l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.INSERT_ACTION_HISTORY');
Line: 3145

END Insert_Res_Action_History;