APPS.PA_AP_INTEGRATION SQL Statements

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

Line: 26

Update pa_implementations_all set  Vendor_Id = p_new_vendor_id
Where  Vendor_Id = p_old_vendor_id;

Line: 31

   UPDATE pa_expenditures_all e
   SET   e.vendor_id = p_new_vendor_id
         -- Bug#10254549 added the last updated columns
         ,last_update_date = sysdate
         ,last_updated_by = fnd_global.user_id
         ,last_update_login =fnd_global.login_id
   WHERE e.vendor_id = p_old_vendor_id  and
         orig_exp_txn_reference1 = nvl(p_invoice_id, orig_exp_txn_reference1) and -- Added nvl for the Bug 14387738
         exists (
          select 1 from ap_invoices_all i
          where invoice_id = nvl(p_invoice_id, invoice_id) -- added nvl for the Bug 14387738
          and to_char(invoice_id) = orig_exp_txn_reference1
          and vendor_id = p_new_vendor_id
          and payment_status_flag = DECODE (NVL (p_paid_inv_flag, 'Y'), 'N', 'N',  i.payment_status_flag)
                 )
      returning expenditure_id BULK COLLECT INTO expid_rec;

Line: 50

UPDATE pa_expenditures_all e
   SET e.vendor_id = p_new_vendor_id
       -- Bug#10254549 added the last updated columns
       ,last_update_date = sysdate
       ,last_updated_by = fnd_global.user_id
       ,last_update_login =fnd_global.login_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: 75

Update pa_expenditure_items_all ei
set vendor_id =  p_new_vendor_id
,last_update_date = sysdate     --bug 14012059
,last_updated_by = fnd_global.user_id -- bug 14012059
Where  Vendor_Id = p_old_vendor_id
  and exists
       (select /*+ no_unnest */ 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: 96

   UPDATE  PA_COST_DISTRIBUTION_LINES_ALL
   SET     System_reference1 = to_char(p_new_vendor_id)
           -- Bug#10254549 added the program update columns
           ,program_id = FND_GLOBAL.CONC_PROGRAM_ID()
           ,program_update_date = sysdate
   WHERE   expenditure_item_id IN (
             SELECT expenditure_item_id
             FROM PA_EXPENDITURE_ITEMS_ALL ei
             WHERE ei.expenditure_id = expid_rec(i)
             );

Line: 112

      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: 127

	Update pa_cost_distribution_lines_all
	Set    System_reference1 = (p_new_vendor_id)
               -- Bug#10254549 added the program update columns
               ,program_id = FND_GLOBAL.CONC_PROGRAM_ID()
               ,program_update_date = sysdate
	Where  rowid = rec.row_id;

Line: 141

  Update Pa_Cost_Distribution_Lines_All cdl
  Set    System_Reference1 = to_char(p_new_vendor_id)
          -- Bug#10254549 added the program update columns
         ,program_id = FND_GLOBAL.CONC_PROGRAM_ID()
         ,program_update_date = sysdate
  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
  and exists (select 1  -- added this for bug8562065
                   from ap_invoices_all inv
                  where to_char(inv.invoice_id) = cdl.system_reference2
                    and inv.vendor_id = p_new_vendor_id
              );

Line: 161

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

Line: 168

Update pa_bc_commitments_all
set  Vendor_Id = p_new_vendor_id
Where  Vendor_Id = p_old_vendor_id;

Line: 174

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: 182

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: 194

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

Line: 202

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: 235

   Select vendor_name into l_new_vendor_name from po_vendors where vendor_id = p_new_vendor_id;

Line: 240

   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: 248

   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: 287

		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: 294

                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: 309

		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: 318

                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: 328

   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: 349

	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: 364

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

Line: 371

	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: 386

        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: 427

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

Line: 459

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: 471

    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: 497

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

Line: 531

  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: 576

 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: 625

      select expenditure_item_date
        from po_distributions_all
       where po_distribution_id = p_po_distribution_id ;

Line: 704

   SELECT organization_id
   FROM hr_all_organization_units_tl
   WHERE name = p_org_name;

Line: 709

   SELECT name
   FROM per_organization_units
   WHERE organization_id = p_organization_id;

Line: 831

	select expenditure_item_date
        from po_distributions_all
	where po_distribution_id = p_po_distribution_id ;

Line: 838

	select
	POD.project_id PROJECT_ID,
	POD.task_id TASK_ID,
	decode(NVL(FND_PROFILE.VALUE('PA_AP_EI_DATE_DEFAULT'),'POTRNSDT'),
	'INVTRNSDT',
	p_transaction_date,
	'INVGLDT',
	p_gl_date,
	'INVSYSDT',
	p_creation_date,
	'POTRNSDT',
	l_po_exp_item_date) PROFILE_DATE,
	POD.expenditure_type EXPENDITURE_TYPE,
	NULL NON_LABOR_RESOURCE,
	NULL EMPLOYEE_ID,
	QUANTITY_ORDERED QUANTITY,
	NULL ,
	G.CURRENCY_CODE,
	NULL,
	NULL ACCT_RAW_COST,
	NULL ,
	NULL ,
	NULL ,
	NULL TRANSFER_EI,
	POD.EXPENDITURE_ORGANIZATION_ID,
	NULL NL_RESOURCE_ORG_ID,
	'AP INVOICE' TRANSACTION_SOURCE,
	NULL,
	NULL ENTERED_BY_USER_ID,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL
	from po_distributions_all pod,
	po_headers_all poh,
	GL_SETS_OF_BOOKS G
	where
	poh.po_header_id = pod.po_header_id
	and pod.po_distribution_id = p_po_distribution_id
	and G.set_of_books_id = pod.set_of_books_id;

Line: 913

/*bug 14050469 - Divided the single query for selecting all 3 - award id, project id and task id to two queries and also added the gms validation
for checking if the user is a gms user*/

begin
select  pod.project_id,pod.task_id,pod.expenditure_type,DESTINATION_TYPE_CODE /* Added for bug 16312792 */
into l_project_id,l_task_id,l_expenditure_type,l_DESTINATION_TYPE_CODE  /* Added for bug 16312792 */
        from po_distributions_all pod
     where
       pod.po_distribution_id = p_po_distribution_id;

Line: 934

 select  awd.award_id,
		 decode(NVL(FND_PROFILE.VALUE('PA_AP_EI_DATE_DEFAULT'),'POTRNSDT'),
								'INVTRNSDT',
								p_transaction_date,
								'INVGLDT',
								p_gl_date,
								'INVSYSDT',
								p_creation_date,
								'POTRNSDT',
								l_po_exp_item_date)
 into l_award_id,l_profile_date
        from    gms_awards_all               awd,
             po_distributions_all pod,
             gms_award_distributions      adl
     where   adl.award_id          = awd.award_id
       and   pod.po_distribution_id = p_po_distribution_id
       and   pod.award_id                = adl.award_set_id
       and   adl.adl_line_num            = 1 ;