<-----Google Analytics Code Start----> <-----Google Analytics Code Close---->
Skip to content

Green

color

Default screen resolution  Wide screen resolution  Increase font size  Decrease font size  Default font size  Skip to content Default color Pink color Green color Green color
Home arrow Technical Articles arrow R12 arrow Suppliers in TCA - A dive into Vendor Tables in R12
This website has now moved to http://apps2fusion.com





Suppliers in TCA - A dive into Vendor Tables in R12 | Print |  E-mail
Written by Anil Passi   
Monday, 26 November 2007
Prior to R12, creation of a vendor/supplier record in eBusiness suite largely meant insertion of records in PO_VENDORS.
However, from R12 onwards, records are inserted into at least half a dozen tables when a single Supplier record is created.
This is largely due to the fact that Suppliers have been moved into the TCA DataModel.

In this article, I would like to show you the set of tables that are effected when a Supplier record gets created in Release12.
I will also touch base upon Supplier Sites and changes to taxation related tables.


End User Step 1
To begin with, we need to create a Supplier. Lets name it Go4Gold [which also happens to be the name of my old company].
Simply enter name of the Supplier in organization name field and click on Apply. This will create a Supplier.
End User Step 2.
You can doublecheck the created Supplier, which has Supplier Number 20186.
This supplier number comes from a table  named  AP_SUPPLIERS.

The registry id that you see is the Party_number field from hz_parties [TCA Party Table]



Now, lets have a look at the list of tables impacted by creating the above Supplier record.
I am not saying that inserting into below listed tables is the way to create Suppliers in R12 TCA Model [Use API's for that].
This article is purely for your understanding of the new data model for Suppliers in R12 TCA.
Of course this will also be helpful to you when developing reports in R12.





Table HZ_PARTIES
SELECT * FROM hz_parties WHERE party_name= 'Go4Gold' ;
This happens to be the master table now instead of PO_VENDORS.
You will notice that the PARTY_NUMBER below is the Registry id in the R12 supplier screen.
Also, this party_id = 301934 will be referenced in the remainder set of tables.






Table HZ_PARTY_USG_ASSIGNMENTS
SELECT party_id ,party_usg_assignment_id,party_usage_code FROM hz_party_usg_assignments
WHERE party_id = 301934;

This table stores the Party Usages, for example, in this case it captures the fact that the given party_id is of type SUPPLIER.





Table HZ_ORGANIZATION_PROFILES
SELECT * FROM hz_organization_profiles WHERE party_id = 301934
This table captures additional Supplier information, for example, credit scoring details of Supplier or the Number of Employees working in Supplier Organization.





Table IBY_EXTERNAL_PAYEES_ALL
SELECT * FROM iby_external_payees_all WHERE payee_party_id = 301934
This table captures Payment related details of the Supplier.
For example :-
    1. How should the supplier's remittance advice must be sent?
    2. What is the default Payment method Code for this supplier?
    3. Who bears the bank charges when lets say SWIFT payment is made?
This information can be setup at either the Supplier level or at Supplier Site level.





Table AP_SUPPLIERS
SELECT vendor_id, vendor_name,segment1,enabled_flag FROM ap_suppliers WHERE party_id = 301934
Alongside HZ_PARTIES, this is another master table that replaces the PO_VENDORS table of 11i.
Instead of expanding the design of HZ_PARTIES, oracle decided to hold the supplier specific attributes in AP_SUPPLIERS [fair enough ! ].





Table POS_SUPPLIER_MAPPINGS
SELECT * FROM pos_supplier_mappings WHERE party_id = 301934
This table holds the mapping between the AP_SUPPLIERS.VENDOR_ID and HZ_PARTIES.PARTY_ID.
This is useful in cases whereby two vendors  effectively belong  the same HZ_Party Record.





Table ZX_PARTY_TAX_PROFILE

SELECT party_type_code, party_tax_profile_id FROM zx_party_tax_profile WHERE party_id = 301934
The taxation related details like Tax Codes, and Tax Accounts etc have been moved from AP into ZX.
ZX is the name of a new Application "E-Business Tax".

Efectively this application is the Tax repository/Taxation Engine for eBusiness Suite starting from R12.
Effectively this also means that our good old AP_TAX_CODES_ALL is no longer the master table for Taxes.
Now we have a new tax rate table, i.e. ZX_RATES_B
.
ZX_ACCOUNTS is another table that has been introduced to capture accounting setup related to Tax Codes.





Database View PO_VENDORS

select vendor_name, segment1, party_number from po_vendors WHERE party_id = 301934
PO_VENDORS is a view in R12, that joins AP_SUPPLIERS & HZ_PARTIES.
Similarly, PO_VENDOR_SITES and PO_VENDOR_SITES_ALL are also views based upon AP_SUPPLIER_SITES_ALL.
Comments (11)add
R12 Upgradation Issues [Help Needed]
written by Vj , November 27, 2007
Hi,

We are upgrading HRMS from 11.0.3 to R 12 (12.0.2) for one of our clients and using UK localization.

All the Fast Formulae are not verified and especially with the suffix YTD, ITD and ASG have not been compiled. This is basically because all the aliases & global variables are recognized as local variables while compilation.

Moreover, all the self-service pages in 11.0.3 were PL/SQL packages and were called as Self-Services pages which are not compatible with R12. Hence we are facing problem in acessing all those self-service pages. Can you plz guide us on this issue?

Thanks in advance!!

Regards,

Vj
Nice Post
written by Gareth Roberts , November 27, 2007
Great stuff Anil ... love the info on new stuff!
Gareth
Employees conversion incuding assignments thru HR API
written by Pranathi , November 28, 2007
hi,
I am new to HRMS. I need to do Employees conversion incuding assignments thru HR API.
Can u pls help me where do we find the interface tables and mandatory columns..
Party Table 'hz' Prefix Logic
written by Harshad Poogalia , November 29, 2007
Hi Anil,

All this information is great. I have always wondered what's the rational behind prefix 'hz' for party related table.
Similarly prefix 'qp' for pricing tables.

If you are aware about it, can you please let all of us know about it. It will be a nice trivia to be aware about.

Keep up the great work.

God Bless

regards,
Harshad
hi, anil
written by suri , December 03, 2007
hi, anil
i would be thanks to u, really u r doing a very job for oracle developers, i want a screen shots in gl for overview of general ledger.
hi, anil
written by murali , December 03, 2007
hi, anil
I thanks to u , because i got a wonderful information from u.
i want order import interface as detailed with code.
hi anil, .
written by raju.m , December 06, 2007
. u are doing great..this is raj. i am oracle financial functional person, here i want find some sample documents like BP80,BP90. CAN U sagest me to fin d these ?
Hi Anil
written by usman , December 19, 2007
u are doing great job.That's looking too much effective.Please continue that
OAF transition
written by Aparna , January 14, 2008
Hi Anil ,
We are currently in the process of upgrading from 11.5.8 to 12i. Any thoughts on how to deal with OAF customizations when upgrading?

Thanks
Aparna
Suppliers
written by zxcvb_bnm , February 28, 2008
Hi Anil,
We are trying to import suppliers in R12 through standard interface tables - ap_suppliers_int, ap_supplier_sites_int and ap_sup_site_contact_int. here (for example take only supplier) if there is some error in the the record the import prog is not importing to ap_suppliers but however it is importing to hz_parties. and now even if the error is correted in the interface table and we re-submit the prog its not picking up coz the record already exists in hz_parties. how to handle this situation in the prod data, as we dont have access to delete from those base tables. similar is the case with suplier_sites, here it is creating multiple locations. please revert asap.
Thanks,
vicky.
...
written by Anil Passi , February 28, 2008
Hey Vicky,

The API must be consistent when it comes to performing a rollback on errored supplier.
I suggest you raise an SR, as this sounds a bug with Oracle Standard Functionality.

Cheers
Anil Passi
You must be logged in to a comment. Please register if you do not have an account yet.

busy