APPS.PA_CUSTOMERS_CONTACTS_UTILS SQL Statements

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

Line: 35

      SELECT customer_id
      FROM pa_customers_v
      WHERE upper(customer_name) = upper(p_customer_name) and status = 'A';

Line: 41

	  SELECT customer_id
          INTO x_customer_id
          FROM pa_customers_v
          WHERE upper(customer_name) = upper(p_customer_name)
                and status = 'A';

Line: 52

         SELECT customer_id
         INTO x_customer_id
         FROM pa_customers_v
         WHERE customer_id = p_customer_id;

Line: 144

      SELECT customer_id
      FROM pa_customers_v
      WHERE upper(customer_number) = upper(p_customer_number) and status = 'A';

Line: 151

	      SELECT customer_id
	      INTO x_customer_id
	      FROM pa_customers_v
	      WHERE upper(customer_number) = upper(p_customer_number)
	      and status = 'A';

Line: 162

         SELECT customer_id
         INTO x_customer_id
         FROM pa_customers_v
         WHERE customer_id = p_customer_id;

Line: 255

      SELECT address_id
      FROM pa_customer_sites_v
      WHERE upper(address1) = upper(p_bill_site_name) AND
            customer_id = p_customer_id AND
            site_use_code = 'BILL_TO';

Line: 264

          SELECT address_id
          INTO x_bill_to_address_id
          FROM pa_customer_sites_v
          WHERE upper(address1) = upper(p_bill_site_name) AND
                customer_id = p_customer_id AND
                site_use_code = 'BILL_TO';

Line: 275

         SELECT address_id
         INTO x_bill_to_address_id
         FROM pa_customer_sites_v
         WHERE address_id = p_bill_to_address_id AND
               customer_id = p_customer_id AND
               site_use_code = 'BILL_TO';

Line: 367

      SELECT address_id
      FROM pa_customer_sites_v
      WHERE upper(address1) = upper(p_work_site_name) AND
            customer_id = p_customer_id AND
            site_use_code = 'SHIP_TO';

Line: 375

      	   SELECT address_id
      	   INTO x_ship_to_address_id
      	   FROM pa_customer_sites_v
      	   WHERE upper(address1) = upper(p_work_site_name) AND
           	   customer_id = p_customer_id AND
            	   site_use_code = 'SHIP_TO';

Line: 387

         SELECT address_id
         INTO x_ship_to_address_id
         FROM pa_customer_sites_v
         WHERE address_id = p_ship_to_address_id AND
               customer_id = p_customer_id AND
               site_use_code = 'SHIP_TO';

Line: 478

      SELECT contact_id
      FROM pa_customer_contact_names_v
      WHERE upper(rtrim(contact_name)) = upper(rtrim(p_contact_name)) AND -- Bug 4015644
            customer_id = p_customer_id  /* AND
            usage_code = decode(p_project_contact_type_code, 'BILLING', 'BILL_TO',
                                'SHIPPING', 'SHIP_TO', usage_code)  */
	AND (address_id is null OR  nvl(p_address_id, address_id)= address_id ); -- Added for Bug 2964227

Line: 491

	      SELECT contact_id
	      INTO x_contact_id
	      FROM pa_customer_contact_names_v
	      WHERE upper(rtrim(contact_name)) = upper(rtrim(p_contact_name)) AND -- Bug 4015644
	            customer_id = p_customer_id AND
	            usage_code = decode(p_project_contact_type_code, 'BILLING', 'BILL_TO',
	                                'SHIPPING', 'SHIP_TO', usage_code)
    	       AND (address_id is null OR  nvl(p_address_id, address_id)= address_id ); -- Added for Bug 2964227

Line: 517

	 SELECT distinct acct_role.cust_account_role_id contact_id
	 INTO x_contact_id
	 FROM hz_cust_account_roles acct_role
	    ,hz_role_responsibility role_resp
	 WHERE
	  acct_role.cust_account_role_id = role_resp.cust_account_role_id
	  and acct_role.role_type = 'CONTACT'
	  and nvl(acct_role.current_role_state, 'A') = 'A'
	  and acct_role.cust_account_role_id = p_contact_id
	  AND acct_role.cust_account_id = p_customer_id
	  AND role_resp.responsibility_type = decode(p_project_contact_type_code, 'BILLING', 'BILL_TO',
                                     		   'SHIPPING', 'SHIP_TO',role_resp.responsibility_type)
	 AND (acct_role.cust_acct_site_id is null OR nvl(p_address_id,acct_role.cust_acct_site_id)=acct_role.cust_acct_site_id );

Line: 613

      SELECT lookup_code
      FROM pa_lookups
      WHERE upper(meaning) = upper(p_project_contact_type_name) AND
            lookup_type = 'PROJECT CONTACT TYPE' AND
            trunc(sysdate) between start_date_active and nvl(end_date_active, trunc(sysdate));

Line: 623

           SELECT lookup_code
           INTO x_project_contact_type_code
           FROM pa_lookups
           WHERE upper(meaning) = upper(p_project_contact_type_name) AND
                 lookup_type = 'PROJECT CONTACT TYPE' AND
                 trunc(sysdate) between start_date_active and nvl(end_date_active, trunc(sysdate));

Line: 635

         SELECT lookup_code
         INTO x_project_contact_type_code
         FROM pa_lookups
         WHERE lookup_code = p_project_contact_type_code AND
               lookup_type = 'PROJECT CONTACT TYPE' AND
               trunc(sysdate) between start_date_active and nvl(end_date_active, trunc(sysdate));

Line: 718

      SELECT 'x'
      FROM pa_project_customers
      WHERE project_id = p_project_id AND
            customer_id = p_customer_id;

Line: 767

      SELECT 'x'
      FROM pa_project_contacts
      WHERE project_id = c_project_id AND
            customer_id = c_customer_id AND
            contact_id = c_contact_id AND
            project_contact_type_code = c_project_contact_type_code;

Line: 843

      SELECT sum(customer_bill_split)
      FROM pa_project_customers
      WHERE project_id = c_project_id AND
            customer_id <> c_customer_id;

Line: 924

      SELECT cc_prvdr_flag
      FROM pa_project_types
      WHERE project_type =
         (SELECT project_type
          FROM pa_projects_all
          WHERE project_id = c_project_id);

Line: 935

      SELECT count(customer_id) count, sum(customer_bill_split) sum
      FROM pa_project_customers
      WHERE project_id = c_project_id
      GROUP BY project_id;

Line: 941

      SELECT '1'
      FROM pa_project_customers
      WHERE project_id = c_project_id AND
            customer_id = c_customer_id;

Line: 958

      if p_action = 'INSERT' then
         if l_recinfo.count > 0 then
            x_return_status := FND_API.G_RET_STS_ERROR;

Line: 970

      elsif p_action = 'UPDATE' then
         if l_recinfo.count > 1 then
            x_return_status := FND_API.G_RET_STS_ERROR;

Line: 1026

      SELECT '1'
      FROM
/* Commented for Bug 2499051
      pa_projects a,
*/
      pa_project_types b
      WHERE b.project_type_class_code = 'CONTRACT' AND
            b.project_type =
               (SELECT project_type
                FROM  pa_projects_all
                WHERE project_id = c_project_id) AND
            (b.cc_prvdr_flag = 'N' OR b.cc_prvdr_flag is NULL);

Line: 1039

      SELECT '1'
      FROM pa_implementations_all
      WHERE customer_id = c_customer_id;

Line: 1043

      SELECT '1'
      FROM pa_implementations
      WHERE cc_ic_billing_prvdr_flag = 'Y';

Line: 1112

      SELECT project_currency_code, multi_currency_billing_flag
      FROM pa_projects_all
      WHERE project_id = c_project_id;

Line: 1129

   select pt.cc_prvdr_flag
   into l_cc_prvdr_flag
   FROM         pa_project_types pt
   ,            pa_projects p
   WHERE        p.project_type = pt.project_type
   AND          p.project_id = p_project_id
   AND		p.org_id     = pt.org_id;

Line: 1212

PROCEDURE CHECK_UPDATE_CONTRIB_ALLOWED
(  p_project_id                    IN NUMBER
  ,p_customer_id                   IN NUMBER
  ,p_customer_bill_split           IN NUMBER
  ,x_return_status                 OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
  ,x_error_msg_code                OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
   c1_exists                       VARCHAR2(1) := NULL;

Line: 1225

   SELECT 'x'
   FROM sys.dual
   WHERE exists
         (SELECT null
          FROM pa_draft_revenues r
          WHERE r.project_id = c_project_id) OR
         exists
         (SELECT null
          FROM pa_draft_invoices i
          WHERE i.project_id = c_project_id);

Line: 1239

   SELECT 'x'
   FROM pa_agreements_all a, pa_project_fundings f
   WHERE a.customer_id+0 = c_customer_id AND
         a.agreement_id  = f.agreement_id AND
         f.project_id    = c_project_id AND
         f.budget_type_code = 'BASELINE';*/

Line: 1249

        SELECT 'x'
        FROM    pa_agreements_all a,
                pa_summary_project_fundings f
        WHERE a.customer_id   = c_customer_id
          AND a.agreement_id  = f.agreement_id
          AND f.project_id    = c_project_id
          AND ( f.total_unbaselined_amount <>0
                OR f.total_baselined_amount <> 0);

Line: 1260

   SELECT customer_bill_split
   FROM pa_project_customers
   WHERE project_id = c_project_id AND
         customer_id = c_customer_id;

Line: 1299

END CHECK_UPDATE_CONTRIB_ALLOWED;

Line: 1311

PROCEDURE CHECK_DELETE_CUSTOMER_ALLOWED
(  p_project_id                    IN NUMBER
  ,p_customer_id                   IN NUMBER
  ,x_return_status                 OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
  ,x_error_msg_code                OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
   c_exists                        VARCHAR2(1);

Line: 1322

   SELECT 'x'
   FROM pa_agreements_all a, pa_project_fundings f
   WHERE a.customer_id+0 = c_customer_id AND
         a.agreement_id = f.agreement_id AND
         f.project_id = c_project_id;

Line: 1342

END CHECK_DELETE_CUSTOMER_ALLOWED;

Line: 1355

   SELECT org_id
   INTO x_org_id
   FROM pa_implementations;

Line: 1385

      SELECT customer_id
      INTO x_customer_id
      FROM pa_customers_v
      WHERE upper(customer_number) = upper(p_customer_number) and
	    upper(customer_name) = upper(p_customer_name)
 	     and status = 'A';

Line: 1421

   SELECT sum(customer_bill_split)
   FROM pa_project_customers
   WHERE project_id = c_project_id;

Line: 1484

SELECT
	HZ_C.cust_account_id ,
	HZ_P.party_name ,
	HZ_P.party_number,
	'Y'
FROM
	pa_project_customers proj_cust,
	hz_cust_accounts HZ_C,
	HZ_PARTIES HZ_P
WHERE
	proj_cust.project_id	= p_project_id
AND	proj_cust.customer_id	= hz_c.cust_account_id
AND	hz_c.party_id		= hz_p.party_id
ORDER BY
	proj_cust.customer_bill_split desc,
	hz_p.party_name,
	HZ_P.party_number ;