Supplier Search results in ‘No results found’

While Searching Supplier using Payable Manager or any other responsibility in R12.1.X results into ‘No Results Found’ though supplier is already defined in system.

This is because POS: SM: Enable Data Security for Supplier” profile option value. Value should be NO at site level. In case if  you wish to use this feature the refer to Oracle Supplier Management Implementation and Administration Guide

How to Create Code Combination ID by API

I was on an integration assignment between Home Grown Project System and Oracle Payable. A big gap which I faced there was how to maintain single source of Account Code Combinations. We had following options to manage this issue.
Built a View of GL_CODE_COMBINATIONS table and pass Combination and ID from Oracle to Custom System
Use API and create Code Combination
I used second way to handle this. Here is script which i used in that Assignment ( it works for me but you need to be careful as it’s not documented any where 🙂
—–Create Function
CREATE OR REPLACE FUNCTION APPS.XXX_CREATE_CCID
( P_CONCAT_SEGS IN VARCHAR2
) RETURN VARCHAR2
IS
L_STATUS BOOLEAN;
L_COA_ID NUMBER;
BEGIN
SELECT CHART_OF_ACCOUNTS_ID
INTO L_COA_ID
FROM GL_SETS_OF_BOOKS
WHERE SET_OF_BOOKS_ID = 2021; –UPDATE THIS WITH SET OF BOOKS ID

L_STATUS := FND_FLEX_KEYVAL.VALIDATE_SEGS(
‘CREATE_COMBINATION’,
‘SQLGL’,
‘GL#’,
L_COA_ID,
P_CONCAT_SEGS,
‘V’,
SYSDATE,
‘ALL’, NULL, NULL, NULL, NULL,
FALSE,FALSE, NULL, NULL, NULL);
IF L_STATUS THEN
RETURN ‘S’;
ELSE
RETURN ‘F’;
END IF;
END ;
/

—–EXECUTE FUNCTION
DECLARE
RETVAL VARCHAR2(200);
P_CONCAT_SEGS VARCHAR2(200); /* ‘10.2001.2211101.987872.001.0000’ THIS COMBINATION I WANT TO CREATE */
BEGIN
RETVAL := APPS.XXX_CREATE_CCID ( P_CONCAT_SEGS );
COMMIT;
END;

Remittance Advice by Email to Supplier by Using UTL_SMTP

Click on Link to Read Article

Oppps Employee as Supplier in Oracle Payables R12. How Crazy….

How to enable creation of employee as supplier in oracle payable supplier entry form

How to Get Supplier Bank Detail In R12

In Release 12, Payables Supplier Bank Information is stored somewhere else instead of PO_VENDORS table as in 11i. After going thru the guides, it seems that the bank information (such as bank account numbers, etc) for suppliers may be in TCA or in Cash Management Bank Model. Please provide the tables names where bank account information and other bank related information is stored?

By Using Following Queries you can get Required Information on Basis on Supplier Site Name

/* For Supplier Bank Account Number Use This One */

SELECT bank_account_name, bank_account_num, branch_id

FROM iby_ext_bank_accounts

WHERE ext_bank_account_id IN (

SELECT ext_bank_account_id

FROM iby_account_owners

WHERE account_owner_party_id IN (

SELECT party_id

FROM hz_party_sites

WHERE party_site_name LIKE

‘%UPC%’))

/* USE THIS QUERY TO GET BANK NAME AND BANK BRANCH NAME OF SUPPLIER BANK*/

SELECT BANKORGPROFILE.HOME_COUNTRY BANK_HOME_COUNTRY,

BANKORGPROFILE.PARTY_ID BANK_PARTY_ID,

BANKORGPROFILE.ORGANIZATION_NAME BANK_NAME,

BANKORGPROFILE.BANK_OR_BRANCH_NUMBER BANK_NUMBER,

BRANCHPARTY.PARTY_ID BRANCH_PARTY_ID,

BRANCHPARTY.PARTY_NAME BANK_BRANCH_NAME,

BRANCHPARTY.PARTY_ID

FROM HZ_ORGANIZATION_PROFILES BANKORGPROFILE,

HZ_CODE_ASSIGNMENTS BANKCA,

HZ_PARTIES BRANCHPARTY,

HZ_ORGANIZATION_PROFILES BRANCHORGPROFILE,

HZ_CODE_ASSIGNMENTS BRANCHCA,

HZ_RELATIONSHIPS BRREL,

HZ_CODE_ASSIGNMENTS BRANCHTYPECA,

HZ_CONTACT_POINTS BRANCHCP,

HZ_CONTACT_POINTS EDICP

WHERE SYSDATE BETWEEN TRUNC (BANKORGPROFILE.EFFECTIVE_START_DATE)

AND NVL (TRUNC (BANKORGPROFILE.EFFECTIVE_END_DATE),

SYSDATE + 1

)

AND BANKCA.CLASS_CATEGORY = ‘BANK_INSTITUTION_TYPE’

AND BANKCA.CLASS_CODE IN (‘BANK’, ‘CLEARINGHOUSE’)

AND BANKCA.OWNER_TABLE_NAME = ‘HZ_PARTIES’

AND (BANKCA.STATUS = ‘A’ OR BANKCA.STATUS IS NULL)

AND BANKCA.OWNER_TABLE_ID = BANKORGPROFILE.PARTY_ID

AND BRANCHPARTY.PARTY_TYPE = ‘ORGANIZATION’

AND BRANCHPARTY.STATUS = ‘A’

AND BRANCHORGPROFILE.PARTY_ID = BRANCHPARTY.PARTY_ID

AND SYSDATE BETWEEN TRUNC (BRANCHORGPROFILE.EFFECTIVE_START_DATE)

AND NVL (TRUNC (BRANCHORGPROFILE.EFFECTIVE_END_DATE),

SYSDATE + 1

)

AND BRANCHCA.CLASS_CATEGORY = ‘BANK_INSTITUTION_TYPE’

AND BRANCHCA.CLASS_CODE IN (‘BANK_BRANCH’, ‘CLEARINGHOUSE_BRANCH’)

AND BRANCHCA.OWNER_TABLE_NAME = ‘HZ_PARTIES’

AND (BRANCHCA.STATUS = ‘A’ OR BRANCHCA.STATUS IS NULL)

AND BRANCHCA.OWNER_TABLE_ID = BRANCHPARTY.PARTY_ID

AND BANKORGPROFILE.PARTY_ID = BRREL.OBJECT_ID

AND BRREL.RELATIONSHIP_TYPE = ‘BANK_AND_BRANCH’

AND BRREL.RELATIONSHIP_CODE = ‘BRANCH_OF’

AND BRREL.STATUS = ‘A’

AND BRREL.SUBJECT_TABLE_NAME = ‘HZ_PARTIES’

AND BRREL.SUBJECT_TYPE = ‘ORGANIZATION’

AND BRREL.OBJECT_TABLE_NAME = ‘HZ_PARTIES’

AND BRREL.OBJECT_TYPE = ‘ORGANIZATION’

AND BRREL.SUBJECT_ID = BRANCHPARTY.PARTY_ID

AND BRANCHTYPECA.CLASS_CATEGORY(+) = ‘BANK_BRANCH_TYPE’

AND BRANCHTYPECA.PRIMARY_FLAG(+) = ‘Y’

AND BRANCHTYPECA.OWNER_TABLE_NAME(+) = ‘HZ_PARTIES’

AND BRANCHTYPECA.OWNER_TABLE_ID(+) = BRANCHPARTY.PARTY_ID

AND BRANCHTYPECA.STATUS(+) = ‘A’

AND BRANCHCP.OWNER_TABLE_NAME(+) = ‘HZ_PARTIES’

AND BRANCHCP.OWNER_TABLE_ID(+) = BRANCHPARTY.PARTY_ID

AND BRANCHCP.CONTACT_POINT_TYPE(+) = ‘EFT’

AND BRANCHCP.STATUS(+) = ‘A’

AND EDICP.OWNER_TABLE_NAME(+) = ‘HZ_PARTIES’

AND EDICP.OWNER_TABLE_ID(+) = BRANCHPARTY.PARTY_ID

AND EDICP.CONTACT_POINT_TYPE(+) = ‘EDI’

AND EDICP.STATUS(+) = ‘A’

AND BRANCHCA.OWNER_TABLE_ID = :IBY_BRANCH_ID /*USER BRANCH ID FROM ABOVE QUERY*/

If anyone have a better solution then please update

Setup Steps and Considerations Common to Oracle Purchasing and Payables

Some setup steps are common to both Oracle Purchasing and Oracle Payables. First, we will discuss the common setup steps and considerations. The common setup steps are as follows:

· System administrator

· Other modules flex fields

· Accounting calendars

· Workday calendar

· Currencies

· Conversion rates

· Set of Books

· Opening and closing accounting periods

· Organizations

· Human resources

· Tax names

· Locations

· Suppliers

System Administrator

Setting up system administrator functions is a required task, and involves setting up system security, defining user names, and defining system printers. User IDs must be defined for all users, and additional responsibilities may also need to be defined. The Oracle System Administrator responsibility will need to be set up to perform this task.

Considerations involved in this step are security and establishing naming conventions to be used throughout Oracle Applications. You must determine which responsibilities should be tied to which users and whether to exclude forms and functions from the standard menu. You must also establish a naming convention for printers and set them up. This lets users know where the printer will be located by simply reading the printer name. You must also determine whether a dedicated printer will be necessary for checks and purchase orders, which usually have special preprinted forms.

Other Module Key Flexfields

This step involves setting up other modules’ Key flexfields related to Oracle Purchasing and Oracle Payables. These Key flexfields include accounting, human resources, and inventory. The accounting Key flexfield must be defined through the General Ledger setup and configuration. Next, set up the four human resources Key flexfields: job, position, grade, and people group. You needn’t set up these flexfields if you already have installed Oracle Human Resources, or if you’re installing in a common application setup (in which case, it will be done during the HR setup). The following inventory Key flexfields should be defined as part of the inventory setup: item catalog, item category and system items. Once you have defined the system items flex field, you must run the Item Flex field view.

Considerations involved in this step involve constructing Key flex fields that have room to grow, that can be used across modules, and that can be validated according to everyone’s need. You must also determine how to maintain valid values, whether to allow dynamic insertions, and how to enforce security.

Accounting Calendars

If you have not defined your accounting calendars in Oracle General Ledger, then you must define them for Oracle Purchasing and Oracle Payables. If you are performing a multi-org implementation of Purchasing and Payables, you may also create more than one calendar.

Whether you need an adjusting period and how to name your accounting period are the two considerations for this setup step. Your accounting calendar is dictated by your business needs, and all Oracle Applications modules share the same accounting calendar, yet they maintain a separate status for each module. For example, an open period in Payables can be a closed period in Receivables.

Workday Calendar

If you have not defined your workday calendars in Oracle Inventory, you must define them for Oracle Purchasing and Oracle Payables. If you are performing a multi-org implementation of Purchasing and Payables, you may also create more than one calendar.

How to name your inventory period and how to create an exception template to filter out nonworking days are the two considerations for this setup step. Your workday calendar is dictated by your business needs, and all Oracle Applications modules share the same workday calendar.

Currencies

If you are planning to use the multi currency features of Oracle, you must also enable the currencies you need. This step is done in Oracle General Ledger.

Determining which currencies you need is the only consideration for this setup step. Oracle Applications has all ISO currencies, but if you have a currency that is not defined, you must define it in Oracle General Ledger.

Conversion Rates

If you plan to use Oracle’s multi currency features, you must also define conversion rate types, daily rates, period rates, and historical rates in Oracle General Ledger.

Determining what kind of exchange rates to use and who will maintain the exchange rates for your company are the two considerations for this setup step.

Set of Books

At least one Set of Books must be defined in Oracle General Ledger in order to use the other modules. Once you have created the Sets of Books, assign responsibilities of Oracle Purchasing and Oracle Payables to the defined Sets of Books.

Considerations for this step, which include whether you want to perform suspense accounting, intercompany accounting, and budgetary control, among others, are covered in the first part of this book.

Opening and Closing Accounting Periods

Next, you must open the appropriate accounting period in Oracle General Ledger. Whether you should open and close the accounting periods is the only consideration for this step. If you open the accounting period, accounting entries and transactions can be entered for the opened accounting period. Open the appropriate inventory and purchasing periods, and open purchasing periods for which you have purchasing activity. Oracle Purchasing will only create journal entries for transactions in an open purchasing period, including creating the correct receipt accrual journal entry. Use the Control Purchasing Periods form to open/close purchasing periods.

Organizations

If you use expense reporting in Oracle Payables, you must define one or more organizations before using Oracle Purchasing and Oracle Payables. Organizations describe and define unique entities within your company, such as manufacturing plants, warehouses, distribution centers, and sales offices. Oracle Purchasing fully supports both centralized and decentralized purchasing for multiple receiving organizations. You can change your current receiving organization at any time with the Change Organization form.

There are multiple considerations for this step. You must determine your company structure, and what kind of security precautions to enforce. In other words, define how you want to segregate data and how you want personnel from one organization to interact with data from another organization. You also need to determine whether you want to centralize or decentralize each Oracle Applications module.

Human Resources

You will need to set up personnel, or human resources information if Human Resources is not installed. This step involves defining employee lookup codes, supplier and employee numbering, positions, position hierarchies, and employees. You must define employees when you define buyers, which is a required step for Oracle Purchasing. You must also define positions, position hierarchies, and employees if you will be using hierarchies for approvals and workflow.

How you want to number your employees and approve purchasing documents are the two considerations for this step. Approving purchase orders can be very complicated, and you can use a purchasing hierarchy for this task. A purchasing hierarchy is a position hierarchy that you can use to determine the relationship between employees when Oracle Purchasing tries to get the next level of approval.

Tax Names

Until now, all common setups have been performed in Oracle Applications modules, with the exception of the procurement modules. The remaining common setup steps are specific to Oracle Purchasing and Oracle Payables. To get to the Setup form, you must sign on as the Operation user and select the Purchasing, Vision Operations (USA) responsibility. The tax name, which you can use when entering tax amounts in payables invoices, defines a specific tax rate and tax account. You must define tax names for each operating unit. To set up tax names, you follow the navigation path Setup : Financials : Accounting: Taxes.

The only consideration for this step is that the tax name you choose be sufficiently descriptive when your users select it within the Payables invoices.

Locations

For purchasing, you must set up locations, which can be defined as ship-to, bill-to, office, internal, or receiving sites. You can follow the navigation path Setup : Organizations: Locations to set up locations.

Suppliers

Suppliers can be created and maintained in Oracle Purchasing or Oracle Payables. You define the suppliers, the supplier sites, and the supplier contacts. Deciding who should maintains suppliers, since you do not want two departments within your organization to duplicate maintenance of the supplier master, is the primary consideration for this step. You must also establish and maintain a naming convention so that your users know how to select existing suppliers.