APPS.PA_AP_INTEGRATION SQL Statements

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

Line: 14

Update pa_implementations_all set  Vendor_Id = p_new_vendor_id
Where  Vendor_Id = p_old_vendor_id;

Line: 20

UPDATE pa_expenditures_all e
   SET e.vendor_id = p_new_vendor_id
 WHERE e.vendor_id = p_old_vendor_id
   AND e.expenditure_id in (
          SELECT ---- /*+ LEADING(ei) */
             ei.expenditure_id
            FROM pa_cost_distribution_lines_all c,
                 pa_expenditure_items_all ei,
                 ap_invoices_all i
           WHERE TO_CHAR (i.invoice_id) = c.system_reference2
             AND c.expenditure_item_id = ei.expenditure_item_id
            -- AND ei.expenditure_id = e.expenditure_id
             AND c.system_reference1 = TO_CHAR(p_old_vendor_id)
             AND i.vendor_id = p_new_vendor_id
             AND i.payment_status_flag = DECODE (NVL (p_paid_inv_flag, 'Y'), 'N', 'N', i.payment_status_flag)
                )  ;

Line: 40

Update pa_expenditure_items_all ei set vendor_id =  p_new_vendor_id
Where  Vendor_Id = p_old_vendor_id
  and exists
       (select 1
        from  pa_cost_distribution_lines_all c,
              ap_invoices_all i
        where i.invoice_id = to_number(c.system_reference2)
        and   c.expenditure_item_id = ei.expenditure_item_id
        and   c.system_reference1 = p_old_vendor_id
        and   i.vendor_id = p_new_vendor_id
        and   i.PAYMENT_STATUS_FLAG =
decode(nvl(p_paid_inv_flag,'Y'),'N','N',i.PAYMENT_STATUS_FLAG)
        );

Line: 60

      Select c.rowid row_id, c.expenditure_item_id, c.line_num
      from pa_cost_distribution_lines_all c, ap_invoices_all i
      where to_char(i.invoice_id) = c.system_reference2
      /*and i.vendor_id = to_number(c.system_reference1)*/ /*Vendor_ID on Invoice is already  changed...so this is not needed */
      and c.system_reference1 = to_char(p_old_vendor_id)
      and i.vendor_id = p_new_vendor_id
      and i.PAYMENT_STATUS_FLAG = 'N';

Line: 75

	Update pa_cost_distribution_lines_all
	Set    System_reference1 = (p_new_vendor_id)
	Where  rowid = rec.row_id;

Line: 86

  Update Pa_Cost_Distribution_Lines_All
  Set    System_Reference1 = to_char(p_new_vendor_id)
  Where  System_Reference1 = to_char(p_old_vendor_id)
  And    system_reference1 is not null
  And    system_reference2 is not null
  And    system_reference3 is not null;

Line: 97

Update pa_bc_packets
set  Vendor_Id = p_new_vendor_id
Where  Vendor_Id = p_old_vendor_id
And  Status_Code = 'A';

Line: 104

Update pa_bc_commitments_all
set  Vendor_Id = p_new_vendor_id
Where  Vendor_Id = p_old_vendor_id;

Line: 110

update pa_project_asset_lines_all set po_vendor_id = p_new_vendor_id
where  po_vendor_id = p_old_vendor_id
and    po_vendor_id is not null;

Line: 118

update PA_CI_SUPPLIER_DETAILS set vendor_id = p_new_vendor_id
where  vendor_id = p_old_vendor_id
and    vendor_id is not null;

Line: 130

Select distinct resource_list_id from pa_resource_list_members
where vendor_id = p_old_vendor_id and enabled_flag = 'Y';

Line: 138

Select distinct resource_list_id from pa_resource_list_members
where vendor_id = p_old_vendor_id
and enabled_flag = 'Y'
 and nvl(migration_code,'M')= 'M';

Line: 171

   Select vendor_name into l_new_vendor_name from po_vendors where vendor_id = p_new_vendor_id;

Line: 176

   Select nvl(count(a.name),0) into l_new_vendor_exists_resource from pa_resource_types b, pa_resources a
   where  a.RESOURCE_TYPE_ID=b.RESOURCE_TYPE_ID and b.RESOURCE_TYPE_CODE='VENDOR'
   And    a.name = l_new_vendor_name;

Line: 184

   If  l_new_vendor_exists_resource = 0 Then -- Insert New vendor as a resource

   x_stage := 'New Vendor Does Not Exists ... Creating New vendor as resource';

Line: 223

		Select nvl(count(*),0) into l_new_vendor_exists_member from pa_resource_list_members
		where 	resource_list_id = rec1.resource_list_id and VENDOR_ID = p_new_vendor_id;

Line: 230

                Select nvl(count(*),0)
                into l_new_vendor_exists_member
                from pa_resource_list_members
		where 	resource_list_id = rec1.resource_list_id
                and VENDOR_ID = p_new_vendor_id
                    and nvl(migration_code,'M') = 'M';

Line: 245

		update pa_resource_list_members set enabled_flag = 'N'
		where  resource_list_id = rec1.resource_list_id
		and    vendor_id = p_old_vendor_id;

Line: 254

                update pa_resource_list_members set
                enabled_flag = 'N'
		where  resource_list_id = rec1.resource_list_id
		and    vendor_id = p_old_vendor_id
                    and nvl(migration_code,'M') = 'M';

Line: 264

   If  l_new_vendor_exists_member = 0 Then -- Insert New vendor as a resource list member

	    x_stage:=' New Vendor Does not esists as resource member.. creating resource member';

Line: 285

	SELECT
	RESOURCE_LIST_ID, RESOURCE_ID, ORGANIZATION_ID, EXPENDITURE_CATEGORY, REVENUE_CATEGORY
 	INTO
 	L_RESOURCE_LIST_ID, L_RESOURCE_ID, L_ORGANIZATION_ID,L_EXPENDITURE_CATEGORY, L_REVENUE_CATEGORY
 	From pa_resource_list_members
 	Where RESOURCE_LIST_ID = rec1.resource_list_id
 	And   resource_list_member_id  = (Select parent_member_id from pa_resource_list_members
				   where RESOURCE_LIST_ID = rec1.resource_list_id
				   and vendor_id= p_old_vendor_id);

Line: 300

        select group_resource_type_id
        into l_res_grouped
        from pa_resource_lists_all_BG
        where  RESOURCE_LIST_ID = rec1.resource_list_id;

Line: 307

	SELECT
 	 RESOURCE_LIST_ID, RESOURCE_ID, ORGANIZATION_ID, EXPENDITURE_CATEGORY, REVENUE_CATEGORY
 	INTO
 	 L_RESOURCE_LIST_ID, L_RESOURCE_ID, L_ORGANIZATION_ID,L_EXPENDITURE_CATEGORY, L_REVENUE_CATEGORY
 	From pa_resource_list_members
 	Where RESOURCE_LIST_ID = rec1.resource_list_id
 	And   resource_list_member_id  = (Select parent_member_id from pa_resource_list_members
	     			          where RESOURCE_LIST_ID = rec1.resource_list_id
				           and vendor_id= p_old_vendor_id
                                           and nvl(migration_code,'M') = 'M' );

Line: 322

        SELECT
         RESOURCE_LIST_ID, RESOURCE_ID, ORGANIZATION_ID, EXPENDITURE_CATEGORY, REVENUE_CATEGORY
        INTO
         L_RESOURCE_LIST_ID, L_RESOURCE_ID, L_ORGANIZATION_ID,L_EXPENDITURE_CATEGORY, L_REVENUE_CATEGORY
        From pa_resource_list_members
        Where RESOURCE_LIST_ID = rec1.resource_list_id
         and vendor_id =p_old_vendor_id
         and nvl(migration_code,'M') = 'M';

Line: 363

   x_stage := ' Calling Resource List change api to update summarization data';

Line: 395

select 'Y' into l_budget_exists
from pa_resource_assignments assign, pa_resource_list_members member, pa_budget_lines budget
where assign.RESOURCE_LIST_MEMBER_ID=member.RESOURCE_LIST_MEMBER_ID
and   member.vendor_id = p_vendor_id
and   budget.resource_assignment_id = assign.resource_assignment_id
and   rownum < 2 ;

Line: 407

    select 'Y'
    into l_budget_exists
    from pa_resource_assignments assign
    , pa_resource_list_members member
    , pa_budget_lines budget
    where assign.RESOURCE_LIST_MEMBER_ID=member.RESOURCE_LIST_MEMBER_ID
    and   member.vendor_id = p_vendor_id
    and   budget.resource_assignment_id = assign.resource_assignment_id
    and   rownum < 2
     and  nvl(member.migration_code,'M') = 'M';

Line: 433

   select decode(pa_resource_utils.chk_supplier_in_use(p_vendor_id),'Y','N','Y')
   into   l_allow_merge_flg
   from   dual;

Line: 467

  SELECT  ptype.project_type_class_code
    INTO  l_project_type_class_code
    FROM  pa_project_types_all ptype,
          pa_projects_all      proj
   WHERE  ptype.project_type     = proj.project_type
     --R12 AND  NVL(ptype.org_id, -99) = NVL(proj.org_id, -99)
     AND  ptype.org_id = proj.org_id
     AND  proj.project_id        = p_project_id;

Line: 512

 SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
  INTO  l_project_type
  FROM  pa_project_types_all ptype,
        pa_projects_all      proj
 WHERE proj.project_type = ptype.project_type
 -- R12 AND   NVL(ptype.org_id, -99) = NVL(proj.org_id, -99)
 AND   ptype.org_id = proj.org_id
 AND   proj.project_id   = p_project_id ;

Line: 561

      select expenditure_item_date
        from po_distributions_all
       where po_distribution_id = p_po_distribution_id ;

Line: 634

   SELECT organization_id
   FROM hr_all_organization_units_tl
   WHERE name = p_org_name;

Line: 639

   SELECT name
   FROM per_organization_units
   WHERE organization_id = p_organization_id;