R12 query to check Credit Limit of a customer

 SELECT  haou.name ou_name,
         c.customer_number,
         c.customer_name,
         g.party_site_number,
         d.location,
         a.overall_credit_limit,
         d.site_use_id,
         SUM (e.amount_due_remaining) credit_balance      
    FROM HZ_CUST_PROFILE_AMTS a,
         HZ_CUST_ACCOUNTS b,
         ar_customers c,
         hz_cust_site_uses_all d,
         ar_payment_schedules_all e,
         hz_cust_acct_sites_all f,
         hz_party_sites g,
         hr_all_organization_units haou
   WHERE     overall_credit_limit IS NOT NULL
         AND a.cust_account_id = b.cust_account_id
         AND b.account_number = c.customer_number
         AND a.site_use_id = d.site_use_id
         AND c.customer_id = e.customer_id
         AND d.site_use_id = e.customer_site_use_id
         --and c.customer_number = :customer_number
         AND d.cust_acct_site_id = f.cust_acct_site_id
         AND g.party_site_id = f.party_site_id
         AND a.overall_credit_limit is not null
         and a.overall_credit_limit not in (0,1)
         --AND e.org_id = 101
         AND haou.organization_id=e.org_id
GROUP BY c.customer_name,
         c.customer_number,
         d.site_use_id,
         d.location,
         a.overall_credit_limit,
         g.party_site_number,
         haou.name
ORDER BY haou.name, CUSTOMER_NAME;

References:
http://erpqueries.blogspot.com/2013/11/r12-query-to-check-credit-limit-of.html

Data Quality Management(DQM) in TCA

In this article I will touch base upon the data quality management feature of TCA. This article also contains a link to a DQM presentation that will compliment the Q & A in this article. A combination of this article and thepowerpoint presentation should help you understand the basics of DQM [ Data Quality Management ] in Oracle TCA. 
Is DQM a part of TCA?
Yes it is, the setup can be done using responsibility named "Trading Community Manager".

What are the setup steps for DQM?
Note: These steps will get clearer once you finish reading this article + PowerPoint Presentation
Here are the steps :-
1. Identify the Attributes that you wish to use for Search and Scoring.
2. Define custom attributes for those columns on which you would like to either search or score, but those columns are not in TCA tables.
3. Assign transformation to each column. Define custom transformation rules if need be, by using PL/SQL 
4. Decide which transformed values are used for Searching and which transformed values are to be used for [both] Searching and/or just Scoring
The attribute transformations that you wish to search upon, will have "Acquisition" checkbox set to Yes.
5. Define your match rules. You can define as many match rules
6. In Match rules, you specify how the users search criteria[values being used by users] are transformed prior to acquisition.
7. Define scoring/ranking rules
8. Run the staging process.


Give me some examples where DQM is used?
Lets say your organization has 50000 employees and a person named john smith gets recruited for a job. HRMS team must know whether this John Smith has had any kind of interaction with the organization in past. Ideally if John Smith has had any kind of interaction with the company in past, then there must exist an entry for this record in TCA. In this case, HR must ensure that HRMS record can be made to point to existing TCA record. By doing so a 360 degree view of the employee be maintained in your organization.


So DQM is about being able to identify a duplicate entry in your system.
Correct, and this I made possible by applying some transformations as you will see in the presentation.


Is DQM only applicable at party level in TCA?
No way, DQM can be made to work at party address level & contact level too.


Isn't finding duplicates as simple as matching the social security number or national insurance number?
Correct, but say if your payables department wants to pay expenses to a third party for their interview travel expenses, then payables team will not have access to SSN or "NI Number". In this case, they rely upon other attributes of the person to find duplicates.


Ah, it means DQM will not be used merely by HRMS department or the Receivables department?
Certainly not, in an efficient organization, any department that registers a party into oracle apps must have access to DQM functionality.


But what if Payables team wants to use DQM, but with different set of search and ranking criteria altogether?
This is a very common requirement. Hence in this case Oracle lets you define multiple "Match Rules" in DQM.


Fine, we can define different Match Rules, but how will we enforce Payables users to use say "Match Rule 1" and HRMS users to use say "Match Rule 2"?
You can differentiate such requirements by creating different DQM search responsibilities. For each such responsibility you will assign profile option "DQM Match Rule for Search" to respective value.
In case you do not wish to have different responsibilities, though recommended, you can set this profile option at user level too.


But DQM searches for duplication in TCA tables, however "NI Number" and SSN [Social Security] are stored in HRMS tables. How will DQM search data in other modules?
You can define custom attributes in DQM. Custom attribute is nothing but a pseudo column which uses pl/sql to return the value,in this case from per_people_f


Does DQM search data within TCA tables?
Well, after defining rules for DQM setup, you run a concurrent program named "DQM Staging Program". This process de-normalises TCA data into staging tables. Transformed values for columns are stored in staging table. This will be clear when you go through the PowerPoint presentation.


What are the staging tables?
There are a list staging tables where DQM Transformed data is stored. Each transformed value resides in a different column. The transformed values used for Acquisition are indexed by Oracle.
The list of tables are
HZ_STAGED_CONTACTS
HZ_STAGED_CONTACT_POINTS
HZ_STAGED_PARTIES
HZ_STAGED_PARTY_SITES



What is event "oracle.apps.ar.hz.DQM.realtimesync" which keeps on firing when employee records or the TCA records are modified?
As you know, the TCA data is transformed and de-normalized into staging tables. Any changes to the TCA data must update the DQM staging tables, so that changes can be searched upon in DQM. The subscription of this event reads the pending entries in staging interface table HZ_DQM_SYNC_INTERFACE and transfers them to DQM staging tables after having applied the transformations.

NOTE: Although the event name is realtimesync, Oracle has disabled the realtime option for this event subscription. Hence synchronization with DQM staging tables happens in deferred mode only.


Here is the link to DQM presentation
Link for DQM and TCA presentation 

References:
http://oracle.anilpassi.com/dqm-tca.html

Load Customers using TCA API

Overview:

Trading Community Architecture (TCA) is an architecture concept designed to support complex trading communities. This document provides information about how to create a customer using TCA API. These APIs utilize the new TCA model, inserting directly to the HZ tables. 

Architecture


Create Organization

DECLARE

p_organization_rec   hz_party_v2pub.organization_rec_type;

x_return_status      VARCHAR2 (2000);

x_msg_count          NUMBER;

x_msg_data           VARCHAR2 (2000);

x_party_id           NUMBER;

x_party_number       VARCHAR2 (2000);

x_profile_id         NUMBER;

BEGIN

p_organization_rec.organization_name := ’erpschools’;

p_organization_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_party_v2pub.create_organization (‘T’,

p_organization_rec,

x_return_status,

x_msg_count,

x_msg_data,

x_party_id,

x_party_number,

x_profile_id

);

DBMS_OUTPUT.put_line (‘party id ‘ || x_party_id);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Note: The above API creates a record in hz_parties table and one record in hz_organization_profiles table. Similarly you can call hz_party_v2pub.create_person to create a record in the HZ_PARTIES and one record in HZ_PERSON_PROFILES tables. 

Create a Location

DECLARE

p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;

x_location_id NUMBER;

x_return_status VARCHAR2(2000);

x_msg_count NUMBER;

x_msg_data VARCHAR2(2000);

BEGIN

p_location_rec.country := ’US’;

p_location_rec.address1 := ’2500 W Higgins Rd’;

p_location_rec.address2 := ’Suite 920′;

p_location_rec.city := ’Thumuluru’;

p_location_rec.postal_code := ’60118′;

p_location_rec.state := ’IL’;

p_location_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_location_v2pub.create_location(

‘T’,

p_location_rec,

x_location_id,

x_return_status,

x_msg_count,

x_msg_data);

dbms_output.put_line(‘location id ‘||x_location_id);

dbms_output.put_line(SubStr(‘x_return_status = ‘||x_return_status,1,255));

dbms_output.put_line(‘x_msg_count = ‘||TO_CHAR(x_msg_count));

dbms_output.put_line(SubStr(‘x_msg_data = ‘||x_msg_data,1,255));

IF x_msg_count >1 THEN

FOR I IN 1..x_msg_count

LOOP

dbms_output.put_line(I||’. ‘||SubStr(FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));

END LOOP;

END IF;

END

Note: The above API shall create an address record in hz_locations table.

Create a Party Site:

Use the organization_id and location_id created above and create a party site.

DECLARE

p_party_site_rec      hz_party_site_v2pub.party_site_rec_type;

x_party_site_id       NUMBER;

x_party_site_number   VARCHAR2 (2000);

x_return_status       VARCHAR2 (2000);

x_msg_count           NUMBER;

x_msg_data            VARCHAR2 (2000);

BEGIN

p_party_site_rec.party_id := 1272023;

p_party_site_rec.location_id := 359086;

p_party_site_rec.identifying_address_flag := ’Y';

p_party_site_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_party_site_v2pub.create_party_site (‘T’,

p_party_site_rec,

x_party_site_id,

x_party_site_number,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (‘party site id ‘ || x_party_site_id);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Note: The above API creates a record in hz_party_sites table.

Create Party Site Use

Use the above party site created

DECLARE

p_party_site_use_rec   hz_party_site_v2pub.party_site_use_rec_type;

x_party_site_use_id    NUMBER;

x_return_status        VARCHAR2 (2000);

x_msg_count            NUMBER;

x_msg_data             VARCHAR2 (2000);

BEGIN

p_party_site_use_rec.site_use_type := ’SHIP_TO’;

p_party_site_use_rec.party_site_id := 349327;

p_party_site_use_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_party_site_v2pub.create_party_site_use (‘T’,

p_party_site_use_rec,

x_party_site_use_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Create a Contact Point

DECLARE

p_contact_point_rec   hz_contact_point_v2pub.contact_point_rec_type;

p_edi_rec             hz_contact_point_v2pub.edi_rec_type;

p_email_rec           hz_contact_point_v2pub.email_rec_type;

p_phone_rec           hz_contact_point_v2pub.phone_rec_type;

p_telex_rec           hz_contact_point_v2pub.telex_rec_type;

p_web_rec             hz_contact_point_v2pub.web_rec_type;

x_return_status       VARCHAR2 (2000);

x_msg_count           NUMBER;

x_msg_data            VARCHAR2 (2000);

x_contact_point_id    NUMBER;

BEGIN

p_contact_point_rec.contact_point_type := ’PHONE’;

p_contact_point_rec.owner_table_name := ’HZ_PARTIES’;

p_contact_point_rec.owner_table_id := ’1272023′;

p_contact_point_rec.primary_flag := ’Y';

p_contact_point_rec.contact_point_purpose := ’BUSINESS’;

p_phone_rec.phone_area_code := ’650′;

p_phone_rec.phone_country_code := ’1′;

p_phone_rec.phone_number := ’506-7000′;

p_phone_rec.phone_line_type := ’GEN’;

p_contact_point_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_contact_point_v2pub.create_contact_point (‘T’,

p_contact_point_rec,

p_edi_rec,

p_email_rec,

p_phone_rec,

p_telex_rec,

p_web_rec,

x_contact_point_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Create an Org Contact:

DECLARE

p_org_contact_rec   hz_party_contact_v2pub.org_contact_rec_type;

x_org_contact_id    NUMBER;

x_party_rel_id      NUMBER;

x_party_id          NUMBER;

x_party_number      VARCHAR2 (2000);

x_return_status     VARCHAR2 (2000);

x_msg_count         NUMBER;

x_msg_data          VARCHAR2 (2000);

BEGIN

p_org_contact_rec.department_code := ’ACCOUNTING’;

p_org_contact_rec.job_title := ’ACCOUNTS OFFICER’;

p_org_contact_rec.decision_maker_flag := ’Y';

p_org_contact_rec.job_title_code := ’APC’;

p_org_contact_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

p_org_contact_rec.party_rel_rec.subject_id := 16077;

p_org_contact_rec.party_rel_rec.subject_type := ’PERSON’;

p_org_contact_rec.party_rel_rec.subject_table_name := ’HZ_PARTIES’;

p_org_contact_rec.party_rel_rec.object_id := 1272023;

p_org_contact_rec.party_rel_rec.object_type := ’ORGANIZATION’;

p_org_contact_rec.party_rel_rec.object_table_name := ’HZ_PARTIES’;

p_org_contact_rec.party_rel_rec.relationship_code := ’CONTACT_OF’;

p_org_contact_rec.party_rel_rec.relationship_type := ’CONTACT’;

p_org_contact_rec.party_rel_rec.start_date := SYSDATE;

hz_party_contact_v2pub.create_org_contact (‘T’,

p_org_contact_rec,

x_org_contact_id,

x_party_rel_id,

x_party_id,

x_party_number,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Note: The above API creates a record in hz_org_contacts table and one record in hz_relationships table. When a contact is created, a record in hz_parties table gets created with party_type as ‘PARTY_RELATIONSHIP’.

Create a Customer Account:

DECLARE

p_cust_account_rec       hz_cust_account_v2pub.cust_account_rec_type;

p_person_rec             hz_party_v2pub.person_rec_type;

p_customer_profile_rec   hz_customer_profile_v2pub.customer_profilerec_type;

x_cust_account_id        NUMBER;

x_account_number         VARCHAR2 (2000);

x_party_id               NUMBER;

x_party_number           VARCHAR2 (2000);

x_profile_id             NUMBER;

x_return_status          VARCHAR2 (2000);

x_msg_count              NUMBER;

x_msg_data               VARCHAR2 (2000);

BEGIN

p_cust_account_rec.account_name := ’John”s A/c’;

p_cust_account_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

p_person_rec.person_first_name := ’John’;

p_person_rec.person_last_name := ’Smith’;

hz_cust_account_v2pub.create_cust_account (‘T’,

p_cust_account_rec,

p_person_rec,

p_customer_profile_rec,

‘F’,

x_cust_account_id,

x_account_number,

x_party_id,

x_party_number,

x_profile_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Note:

This routine is used to create a Customer Account. The API creates a record in the HZ_CUST_ACCOUNTS table for party type Person or Organization. Account can be created for an existing party by passing party_id of the party. Alternatively, this routine creates a new party and an account for the party.

Customer profile record in the HZ_CUSTOMER_PROFILES can also be created while calling this routine based on value passed in p_customer_profile_rec. The routine is overloaded for Person and Organization.

Create a Customer Account Site

Use an existing Party Site

DECLARE

p_cust_acct_site_rec   hz_cust_account_site_v2pub.cust_acct_site_rec_type;

x_return_status        VARCHAR2 (2000);

x_msg_count            NUMBER;

x_msg_data             VARCHAR2 (2000);

x_cust_acct_site_id    NUMBER;

BEGIN

p_cust_acct_site_rec.cust_account_id := 3472;

p_cust_acct_site_rec.party_site_id := 1024;

p_cust_acct_site_rec.LANGUAGE := ’US’;

p_cust_acct_site_rec.created_by_module := ’TCA-EXAMPLE’;

hz_cust_account_site_v2pub.create_cust_acct_site (‘T’,

p_cust_acct_site_rec,

x_cust_acct_site_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Create Customer Account Site Use Code:

DECLARE

p_cust_site_use_rec      hz_cust_account_site_v2pub.cust_site_use_rec_type;

p_customer_profile_rec   hz_customer_profile_v2pub.customer_profile_rec_type;

x_site_use_id            NUMBER;

x_return_status          VARCHAR2 (2000);

x_msg_count              NUMBER;

x_msg_data               VARCHAR2 (2000);

BEGIN

p_cust_site_use_rec.cust_acct_site_id := 3580;

p_cust_site_use_rec.site_use_code := ’INV’;

p_cust_site_use_rec.LOCATION := ’TCA’;

p_cust_site_use_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_cust_account_site_v2pub.create_cust_site_use (‘T’,

p_cust_site_use_rec,

p_customer_profile_rec,

”,

”,

x_site_use_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

More Customer API’s:

Org Contact Role	Hz_party_contact_v2pub.Create_Org_Contact_Role
Relationships	HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE
Customer Profile	HZ_CUSTOMER_PROFILE_V2PUB. create_customer_profile
Customer Profile Amount	HZ_CUSTOMER_PROFILE_V2PUB. create_cust_profile_amt
Customer Credit Rating	HZ_PARTY_INFO_V2PUB.create_credit_rating
Sales Person	JTF_RS_SALESREPS_PUB.CREATE_SALESREP
Sales reps Territories	JTF_RS_SRP_TERRITORIES_PUB.CREATE_RS_SRP_TERRITORIES
Customer contacts	HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CUST_ACCOUNT_ROLE
Customer Contact Role	HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility