Show relevant customers in Project Management assigned to the Operating Unit
When implementing Project Management across multiple operating units and using customers assigned to your created projects, for example to bill them your hours through Project Billing, you might run into the problem that an user from an operating unit will see ALL the customers available in Account Receivables. This user will also see the customers belonging to other operating units.
In Oracle E-Business Suite the customers are shared across all the operating units and customer addresses are assigned to operating units so they will use their own customer addresses. By default the customer list of values in Project Management will present a big list of all customers and the addresses assigned to the current active operating unit.
If you're asking for example for the billing and shipping customer during Quick Entry of a new project you'll get the below Customer List of Values by default.
In the example the addresses that are visible are the ones assigned to the active operating unit. The other customers, without an address, are customers from a different operating unit (customers without an addresses assigned to the current operating unit). In a real life situation you don't want to show these customers to the end-user as billing and shipping addresses are not selectable which will cause problems while creating and approving the project.
To change this a small customization is needed which is also supported by Oracle Support.
The Customer List of Values in Project Management is driven by database view APPS.PA_CUSTOMER_LOV_V. This view needs to be changed to hide irrelevant customers for the end-user. Please note that changing the view will influence all the customer related list of values. So Project Quick Entry List of Values will change due to this but also the List of Values used while assigning Billing Accounts. Project Management will use the same view here.
The default view definition for PA_CUSTOMER_LOV_V is the one below.
As can be seen in the last WHERE clause the default view is using 2 outer joins. Due to this all the customers will show in the List of Values with and without an Operating Unit assigned customer address.
By removing the outer joins and changing this into inner joins only customers will appear with an active current operating unit assigned address.
Change the last WHERE clause in
WHERE r.cust_account_id = pc.customer_id AND r.address_id = r1.address_id;
The result will be the below. A much smaller list will be presented to the end-user which makes it more efficient to find the correct customer to be assigned to the project.
In Oracle E-Business Suite the customers are shared across all the operating units and customer addresses are assigned to operating units so they will use their own customer addresses. By default the customer list of values in Project Management will present a big list of all customers and the addresses assigned to the current active operating unit.
If you're asking for example for the billing and shipping customer during Quick Entry of a new project you'll get the below Customer List of Values by default.
In the example the addresses that are visible are the ones assigned to the active operating unit. The other customers, without an address, are customers from a different operating unit (customers without an addresses assigned to the current operating unit). In a real life situation you don't want to show these customers to the end-user as billing and shipping addresses are not selectable which will cause problems while creating and approving the project.
To change this a small customization is needed which is also supported by Oracle Support.
The Customer List of Values in Project Management is driven by database view APPS.PA_CUSTOMER_LOV_V. This view needs to be changed to hide irrelevant customers for the end-user. Please note that changing the view will influence all the customer related list of values. So Project Quick Entry List of Values will change due to this but also the List of Values used while assigning Billing Accounts. Project Management will use the same view here.
The default view definition for PA_CUSTOMER_LOV_V is the one below.
CREATE OR REPLACE FORCE VIEW apps.pa_customer_lov_v (customer_name, customer_number,
customer_id,
LOCATION,
address1,
address2,
address3,
address4,
details,
status,
site_use_code,
party_id,
party_type,
cust_name_and_number,
bill_to_location
)
AS
SELECT /*+ cardinality(100) first_rows */
pc.customer_name, pc.customer_number, pc.customer_id, r1.LOCATION,
r.address1, r.address2, r.address3, r.address4, r.details,
pc.status, r1.site_use_code, pc.party_id, pc.party_type,
pc.customer_name || '(' || pc.customer_number || ')',
DECODE (r1.site_use_code,
'BILL_TO', r1.LOCATION,
NULL
) bill_to_location
FROM pa_customers_v pc,
(SELECT hz_cs.cust_account_id cust_account_id,
hz_cs.cust_acct_site_id address_id, hz_l.address1 address1,
hz_l.address2 address2, hz_l.address3 address3,
hz_l.address4 address4,
hz_l.city
|| ', '
|| NVL (hz_l.state, hz_l.province)
|| ', '
|| hz_l.county
|| ', '
|| hz_l.country
|| ','
|| hz_l.postal_code AS details
FROM hz_cust_acct_sites hz_cs,
hz_party_sites hz_ps,
hz_locations hz_l
WHERE NVL (hz_cs.status, 'I') = 'A'
AND hz_cs.party_site_id = hz_ps.party_site_id
AND hz_ps.location_id = hz_l.location_id) r,
(SELECT cust_acct_site_id address_id, LOCATION, site_use_code
FROM hz_cust_site_uses su
WHERE (su.site_use_code = 'BILL_TO'
OR su.site_use_code = 'SHIP_TO'
)
AND NVL (su.status, 'I') = 'A') r1
WHERE r.cust_account_id(+) = pc.customer_id AND r.address_id = r1.address_id(+);
customer_id,
LOCATION,
address1,
address2,
address3,
address4,
details,
status,
site_use_code,
party_id,
party_type,
cust_name_and_number,
bill_to_location
)
AS
SELECT /*+ cardinality(100) first_rows */
pc.customer_name, pc.customer_number, pc.customer_id, r1.LOCATION,
r.address1, r.address2, r.address3, r.address4, r.details,
pc.status, r1.site_use_code, pc.party_id, pc.party_type,
pc.customer_name || '(' || pc.customer_number || ')',
DECODE (r1.site_use_code,
'BILL_TO', r1.LOCATION,
NULL
) bill_to_location
FROM pa_customers_v pc,
(SELECT hz_cs.cust_account_id cust_account_id,
hz_cs.cust_acct_site_id address_id, hz_l.address1 address1,
hz_l.address2 address2, hz_l.address3 address3,
hz_l.address4 address4,
hz_l.city
|| ', '
|| NVL (hz_l.state, hz_l.province)
|| ', '
|| hz_l.county
|| ', '
|| hz_l.country
|| ','
|| hz_l.postal_code AS details
FROM hz_cust_acct_sites hz_cs,
hz_party_sites hz_ps,
hz_locations hz_l
WHERE NVL (hz_cs.status, 'I') = 'A'
AND hz_cs.party_site_id = hz_ps.party_site_id
AND hz_ps.location_id = hz_l.location_id) r,
(SELECT cust_acct_site_id address_id, LOCATION, site_use_code
FROM hz_cust_site_uses su
WHERE (su.site_use_code = 'BILL_TO'
OR su.site_use_code = 'SHIP_TO'
)
AND NVL (su.status, 'I') = 'A') r1
WHERE r.cust_account_id(+) = pc.customer_id AND r.address_id = r1.address_id(+);
As can be seen in the last WHERE clause the default view is using 2 outer joins. Due to this all the customers will show in the List of Values with and without an Operating Unit assigned customer address.
By removing the outer joins and changing this into inner joins only customers will appear with an active current operating unit assigned address.
Change the last WHERE clause in
WHERE r.cust_account_id = pc.customer_id AND r.address_id = r1.address_id;
The result will be the below. A much smaller list will be presented to the end-user which makes it more efficient to find the correct customer to be assigned to the project.
