APPS.ASO_PA_APR_INT SQL Statements

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

Line: 56

        select  meaning
	   from    fnd_lookups
	   where   lookup_type = 'AME_APPROVER_CATEGORY'
	   and     lookup_code = l_value
	   and     enabled_flag = 'Y';

Line: 64

        select  meaning
	   from    fnd_lookups
	   where   lookup_type = 'FND_WF_ORIG_SYSTEMS'
	   and     lookup_code = l_value
	   and     enabled_flag = 'Y';

Line: 72

        select  meaning
	   from    fnd_lookups
	   where   lookup_type = 'AME_SUBLIST_TYPES'
	   and     lookup_code = l_value
	   and     enabled_flag = 'Y';

Line: 80

      select USER_ACTION_TYPE_NAME
      from ame_action_types_vl
      where ACTION_TYPE_ID = l_value
      and nvl(end_date,sysdate)>=trunc(sysdate);

Line: 87

      select item_class_id,nvl(approver_category,'A') approver_category
      from ame_rules_v
      where fnd_application_id=l_application_id
      and rule_id=l_value;

Line: 94

      select USER_ITEM_CLASS_NAME
      from ame_item_classes_vl
      where item_class_id=l_value
      and nvl(end_date,sysdate)>=trunc(sysdate);

Line: 102

    select APPROVAL_DET_ID, APPROVER_PERSON_ID, approver_user_id,APPROVER_SEQUENCE, APPROVER_STATUS, APPROVER_COMMENTS,
    name,item_class, item_id,action_type_id, group_or_chain_id, occurrence, authority,approver_category
    from ASO_APR_APPROVAL_DETAILS
    where object_approval_id = p_object_appr_id
    order by APPROVAL_DET_ID;

Line: 205

        select approval_Status into l_appr_instance_status
        from aso_apr_obj_approvals
        where object_approval_id= p_object_approval_id;

Line: 443

          Select meaning
          into x_approvers_list( m).approver_Status
          from aso_lookups
          where lookup_type = 'ASO_APPROVER_STATUS'
          and lookup_code = l_appr_status_code
          and enabled_flag='Y'
           And trunc(nvl(start_date_active, sysdate-1)) <= trunc(sysdate)
          And trunc(nvl(end_date_active, sysdate+1)) >= trunc(sysdate);

Line: 482

       insert_approvers_gtt(approvertblin=>x_approvers_list,
                            p_object_appr_id => p_object_approval_id,
                            x_return_status => x_return_status,
                            x_msg_count => x_msg_count,
                            x_msg_data => x_msg_data);

Line: 498

         select approval_Status into l_appr_instance_status
         from aso_apr_obj_approvals
         where p_object_approval_id=object_approval_id;

Line: 506

            for c1 in (select OAM_RULE_ID
                       from aso_apr_rules
                        where p_object_approval_id=object_approval_id)
             loop
               m:=m+1;

Line: 751

      SELECT NVL (
               (MAX (
                  approval_instance_id
                ) + 1
               ),
               1
             )
      FROM aso_apr_obj_approvals
      WHERE object_id = c_object_id;

Line: 762

   select employee_id
   from fnd_user
   where user_id = l_user_id;

Line: 767

    SELECT count(*)
    FROM   aso_apr_obj_approvals
    WHERE  object_id = p_object_id
    AND    approval_status = 'PEND';

Line: 994

        'Inserting a row into the header table ',
        1,
        'N'
      );

Line: 1001

    aso_apr_approvals_pkg.header_insert_row (
      p_object_approval_id,
      p_object_id,
      p_object_type,
      p_approval_instance_id,
      'PEND',
      p_application_id,
      SYSDATE -- p_START_DATE
      ,
      NULL -- p_END_DATE
      ,
      SYSDATE -- p_CREATION_DATE
      ,
      g_user_id -- p_CREATED_BY
      ,
      SYSDATE -- p_LAST_UPDATE_DATE
      ,
      g_user_id -- P_UPDATED_BY
      ,
      fnd_global.conc_login_id -- p_LAST_UPDATE_LOGIN
      ,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
	 NULL -- p_CONTEXT
      ,
      NULL -- p_SECURITY_GROUP_ID
      ,
      1 -- p_OBJECT_VERSION_NUMBER
      ,
      g_user_id -- p_REQUESTER_USERID
      ,
      p_requester_comments -- p_REQUESTER_COMMENTS
      ,
      l_requester_group_id -- p_REQUESTER_GROUP_ID
    );

Line: 1068

	      update aso_apr_obj_approvals
	      set approval_status = 'APPR',
	     end_date = sysdate
	   where object_approval_id = p_object_approval_id;

Line: 1218

      SELECT object_approval_id
      FROM aso_apr_obj_approvals
      WHERE object_id = c_object_id
            AND object_type = c_object_type
            AND approval_instance_id = (SELECT MAX (
                                                 approval_instance_id
                                               )
                                        FROM aso_apr_obj_approvals
                                        WHERE object_id = c_object_id
                                              AND object_type = c_object_type);

Line: 1649

    p_last_update_date          IN             DATE,
    x_obj_ver_num               OUT NOCOPY     NUMBER,
    x_last_update_date          OUT NOCOPY     DATE,
    x_return_status             OUT NOCOPY /* file.sql.39 change */      VARCHAR2,
    x_msg_count                 OUT NOCOPY /* file.sql.39 change */      NUMBER,
    x_msg_data                  OUT NOCOPY /* file.sql.39 change */      VARCHAR2
  )
IS

    l_api_name                  CONSTANT VARCHAR2 (240) := 'upd_status_self_appr';

Line: 1659

    l_last_update_date          DATE;

Line: 1672

    select last_update_date,object_version_number
    from aso_quote_headers_all
    where quote_header_id = l_qte_hdr_id;

Line: 1677

    SELECT Quote_Status_Id
    FROM ASO_QUOTE_STATUSES_B
    WHERE Status_Code = 'APPROVAL PENDING';

Line: 1682

    select object_approval_id,object_type,application_id,object_version_number
    from aso_apr_obj_approvals
    where OBJECT_ID=l_qte_header_id
    and approval_Status='PEND';

Line: 1701

       aso_debug_pub.add('upd_status_self_appr: p_last_update_date:  || p_last_update_date ', 1, 'Y');

Line: 1705

      Fetch c_get_qte_info into l_LAST_UPDATE_DATE,l_object_version_number;

Line: 1708

              FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_UPDATE_TARGET');

Line: 1717

          aso_debug_pub.add('upd_status_self_appr: l_LAST_UPDATE_DATE:      '|| l_LAST_UPDATE_DATE, 1, 'Y');

Line: 1721

      If (l_last_update_date is NULL or l_last_update_date = FND_API.G_MISS_Date ) Then
          IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
              FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_COLUMN');

Line: 1724

              FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);

Line: 1731

      If ( (l_last_update_date <> p_last_update_date ) OR (l_object_version_number <> p_obj_ver_num ) ) Then
          IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
              FND_MESSAGE.Set_Name('ASO', 'ASO_API_RECORD_CHANGED');

Line: 1748

     x_last_update_date := sysdate;

Line: 1752

    update aso_quote_headers_all
    set    quote_status_id       = l_status_id,
           object_version_number = x_obj_ver_num,
           last_update_date      = x_last_update_date
    where  quote_header_id = p_qte_hdr_id;

Line: 1789

      x_last_update_date := sysdate;

Line: 1796

        update aso_apr_obj_approvals
          set approval_status = 'APPR',
              object_version_number = l_apr_object_version,
              end_date = x_last_update_date,
            last_update_date = x_last_update_date
        where object_approval_id = l_object_approval_id;

Line: 1866

procedure insert_approvers_gtt
(approvertblin               IN aso_pa_apr_pub.approvers_list_tbl_type,
 p_object_appr_id            IN  number,
 x_return_status             OUT NOCOPY /* file.sql.39 change */      VARCHAR2,
 x_msg_count                 OUT NOCOPY /* file.sql.39 change */      NUMBER,
 x_msg_data                  OUT NOCOPY /* file.sql.39 change */      VARCHAR2)
as
cursor get_approver_details(p_object_appr_id number) is
select APPROVAL_DET_ID, APPROVER_PERSON_ID, approver_user_id,APPROVER_SEQUENCE, APPROVER_STATUS, APPROVER_COMMENTS,
name,item_class, item_id,action_type_id, group_or_chain_id, occurrence, authority,approver_category
from ASO_APR_APPROVAL_DETAILS
where object_approval_id = p_object_appr_id
order by APPROVAL_DET_ID;

Line: 1882

        select  meaning
	   from    fnd_lookups
	   where   lookup_type = 'AME_APPROVER_CATEGORY'
	   and     lookup_code = l_value
	   and     enabled_flag = 'Y';

Line: 1891

        select  meaning
	   from    fnd_lookups
	   where   lookup_type = 'AME_SUBLIST_TYPES'
	   and     lookup_code = l_value
	   and     enabled_flag = 'Y';

Line: 1899

      select USER_ACTION_TYPE_NAME
      from ame_action_types_vl
      where ACTION_TYPE_ID = l_value
      and nvl(end_date,sysdate)>=trunc(sysdate);

Line: 1916

l_api_name                      varchar2(240):='insert_approvers_gtt';

Line: 1921

       aso_debug_pub.add('insert_approvers_gtt: BEGIN ', 1, 'Y');

Line: 1928

    SAVEPOINT insert_approvers_gtt;

Line: 1930

  select approval_status
  into l_approval_status
  from ASO_APR_OBJ_APPROVALS
  where OBJECT_APPROVAL_ID=p_object_appr_id;

Line: 1936

      aso_debug_pub.ADD ('insert_approvers_gtt:',1,'N');

Line: 1942

  select count(*) into l_count
  from ASO_APR_APPROVAL_DETAILS_TEMP
  where object_approval_id = p_object_appr_id;

Line: 1947

      aso_debug_pub.ADD ('insert_approvers_gtt: temp count'||l_count,1,'N');

Line: 1951

    Delete from ASO_APR_APPROVAL_DETAILS_TEMP
    where object_approval_id = p_object_appr_id;

Line: 1957

        aso_debug_pub.ADD ('insert_approvers_gtt: approvertblin count'||approvertblin.count,1,'N');

Line: 1964

           select APPROVAL_DET_ID    , approver_comments
           into  l_approval_Detail_id, l_approver_comments
           from ASO_APR_APPROVAL_DETAILS
           where object_approval_id = p_object_appr_id
           and nvl(approver_person_id,approver_user_id)=nvl(approvertblin(i).approver_person_id,approvertblin(i).approver_user_id)
           and name=approvertblin(i).name
           and APPROVER_SEQUENCE=approvertblin(i).approver_sequence
           and ((item_id is null) or (item_id=approvertblin(i).item_id))
           and ((item_class is null) or (item_class=approvertblin(i).item_class))
           and (nvl(action_type_id,0)=nvl(approvertblin(i).action_type_id,0))
           and (nvl(group_or_chain_id,0)=nvl(approvertblin(i).chain_number,0))
           and (nvl(occurrence,0) = nvl(approvertblin(i).occurrence,0));

Line: 1981

                  aso_debug_pub.ADD ('insert_approvers_gtt: approvertblin count'||approvertblin.count,1,'N');

Line: 1990

           insert into ASO_APR_APPROVAL_DETAILS_TEMP
           (APPROVAL_DET_ID,OBJECT_APPROVAL_ID,APPROVER_PERSON_ID,APPROVER_USER_ID,APPROVER_SEQUENCE,
            APPROVER_STATUS,NAME,APPROVER_NAME,APPROVAL_COMMENTS,APPROVER_CATEGORY,APPROVER_CATEGORY_DESC,
            APPROVER_TYPE_DESC,SUB_LIST_DESC,ITEM_ID,ITEM_CLASS, ACTION_TYPE,  ACTION_TYPE_ID,
            CHAIN_NUMBER,AUTHORITY)
            values
            (l_approval_Detail_id,p_object_appr_id,approvertblin(i).approver_person_id,approvertblin(i).approver_user_id,approvertblin(i).APPROVER_SEQUENCE,
            approvertblin(i).approver_status,approvertblin(i).name,approvertblin(i).approver_name,l_approver_comments,approvertblin(i).approver_category,approvertblin(i).approver_category_desc,
            approvertblin(i).approver_type_desc,approvertblin(i).SUB_LIST_DESC,approvertblin(i).ITEM_ID,approvertblin(i).ITEM_CLASS,
            approvertblin(i).ACTION_TYPE,approvertblin(i).ACTION_TYPE_ID,approvertblin(i).CHAIN_NUMBER,approvertblin(i).AUTHORITY
            );

Line: 2003

        select count(*) into l_count
        from ASO_APR_APPROVAL_DETAILS_TEMP
        where object_approval_id = p_object_appr_id;

Line: 2008

           aso_debug_pub.ADD ('insert_approvers_gtt: After inserting into temp table count'||l_count,1,'N');

Line: 2020

           aso_debug_pub.ADD ('insert_approvers_gtt: c1.name'||c1.name,1,'N');

Line: 2067

         Select meaning
          into l_approval_status_desc
          from aso_lookups
          where lookup_type = 'ASO_APPROVER_STATUS'
          and lookup_code = c1.approver_status
          and enabled_flag='Y'
	   And trunc(nvl(start_date_active, sysdate-1)) <= trunc(sysdate)
          And trunc(nvl(end_date_active, sysdate+1)) >= trunc(sysdate);

Line: 2082

      insert into ASO_APR_APPROVAL_DETAILS_TEMP
           (APPROVAL_DET_ID,OBJECT_APPROVAL_ID,APPROVER_PERSON_ID,APPROVER_USER_ID,APPROVER_SEQUENCE,
            APPROVER_STATUS,NAME,APPROVER_NAME,APPROVAL_COMMENTS,APPROVER_CATEGORY,APPROVER_CATEGORY_DESC,
            APPROVER_TYPE_DESC,SUB_LIST_DESC,ITEM_ID,ITEM_CLASS, ACTION_TYPE,  ACTION_TYPE_ID,
            CHAIN_NUMBER,AUTHORITY)
            values
            (c1.APPROVAL_DET_ID,p_object_appr_id,c1.approver_person_id,c1.approver_user_id,c1.APPROVER_SEQUENCE,
            l_approval_status_desc,l_name,l_displayname,c1.approver_comments,c1.approver_category,l_approver_category_desc,
            l_approver_type,l_sub_list_desc,c1.ITEM_ID,c1.ITEM_CLASS,l_ACTION_TYPE,c1.ACTION_TYPE_ID,c1.group_or_chain_id,c1.AUTHORITY
            );

Line: 2098

  Select count(*) into l_count
  from ASO_APR_APPROVAL_DETAILS_TEMP
  where object_approval_id = p_object_appr_id;

Line: 2102

  Insert into test_pa_approval_debug_log values ('ASO_APR_APPROVAL_DETAILS_TEMP Record Count :'||l_count);

Line: 2148

end insert_approvers_gtt;