Oracle Receivables Module Technical Details Part 2

RA_CUSTOMER_TRX_ALL: This table stores invoice, debit memo, commit-ment, chargeback, bills receivable,and credit memo header information.
• Each row includes general invoice information such as customer, transaction type, and printing instructions.

• You need one row for each invoice, debit memo, commitment, and credit memo you create in Oracle Receivables and these are all distinguished by their transaction types stored in RA_CUST_ TRX_ TYPES_ALL.

RA_CUSTOMER_TRX_LINES_ALL: This table stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines. It describes to the customer the charges that appear on these documents.

AR_PAYMENT_SCHEDULES_ALL: This table stores all transactions except adjust-ments and miscellaneous cash receipts. A miscellaneous cash receipt is one that is not connected to a customer.
• All customer-related activity is logged in this table.
• This table is updated when an activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, bills receivable, receipt, or commitments.

RA_CUST_TRX_TYPES_ALL: This table stores information about each transaction type for all classes of transactions, for example, invoices, commitments, and credit memos.
• Each row includes Auto Accounting information as well as standard defaults for the resulting invoices. The primary key for this table is CUST_TRX_TYPE_ID.

AR_TRANSACTION_HISTORY_ALL: This table is a Bills Receivable-specific table containing the history of a transaction’s lifecycle.
• A new row is created each time there is activity on the transaction or the status of the transaction has changed.
• This table stores the header for the Receivables posting information.

AR_DISTRIBUTIONS_ALL: This table stores the accounting distributions for cash receipts, miscellaneous receipts, adjustments, credit memo applications, cash receipt applications, and bills receivable transactions.

AR_CASH_RECEIPTS_ALL: This table stores one record for each receipt entry.
• All cash receipts are logged in this table.
• Oracle Receivables creates records concurrently in the AR_CASH _RECEIPT_ HISTORY_ ALL, AR_PAYMENT_ SCHEDULES_ ALL, AR_DISTRI-BUTIONS_ALL, and AR_ RECEIVABLE_ APPLICA-TIONS_ALL tables for invoice-related receipts.
• For receipts that are not related to invoices, records are created in the AR_MISC_CASH_ DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ ALL table.

AR_CASH_RECEIPT_HISTORY_ALL: This table stores all of the activity that is contained for the life cycle of a receipt.
• Each row represents one step.
• The status field for that row tells you which step the receipt has reached.
• Possible statuses are Approved, Confirmed, Remitted, Cleared, and Reversed.

AR_RECEIVABLE_APPLICATIONS_ALL: This table stores all accounting entries for cash and credit memo applications.
• Each row includes the amount applied, status, and accounting flex field information.

AR_MISC_CASH_DISTRIBUTIONS_ALL: This table stores all accounting entries for miscellaneous cash applications.
• Miscellaneous cash cannot be invoiced, such as stock revenue, interest income, and investment income.
• AR_CASH_RECEIPTS_ALL stores one record for each payment, and this table stores one record for each distribution of the receipt.

AR_RECEIPT_CLASSES: This table stores the different receipt classes that you define.
• Receipt classes determine whether the receipt[s] belonging to this class are created manually or automatically, and whether the receipts go through the different steps in a receipt’s life-cycle.

AR_RECEIPT_METHODS: This table stores information about Payment Methods, receipt attributes that you define and assign to Receipt Classes to account for receipts and their applications.
• For automatically created receipts, a Payment Method defines the rules for creating these receipts.

• For manually created receipts, a Payment Method defines a user-definable type for the receipt.

• Each Payment Method is associated with a set of bank accounts, which forms the set of bank accounts you can assign to your receipt.

AR_ADJUSTMENTS_ALL: This table stores information about the adjustment applied to the Invoices.

<<<Previous

Read More »

Oracle Receivables Module Technical Details

Oracle Accounts Receivable uses the following tables for recording customer account information:

Party and Customer Tables	Transaction Tables
HZ_PARTIES HZ_CUST_ACCOUNTS HZ_PARTY_SITES HZ_CUST_ACCT_SITES_ALL HZ_CUST_SITE_USES_ALL HZ_LOCATIONS HZ_PARTY_RELATIONSHIPS HZ_ORGANIZATION_PROFILES HZ_CONTACT_POINTS HZ_CUST_ACCOUNT_ROLES HZ_PERSON_PROFILES HZ_ORG_CONTACTS	RA_CUSTOMER_TRX_ALL RA_CUSTOMER_TRX_LINES_ALL RA_CUST_TRX_LINE_GL_DIST_ALL AR_PAYMENT_SCHEDULES_ALL AR_CASH_RECEIPTS_ALL AR_CASH_RECEIPT_HISTORY_ALL AR_RECEIVABLE_APPLICATIONS_ALL AR_ADJUSTMENTS_ALL

  

HZ_PARTIES: A party is an entity that can enter into a business relationship.
This table stores basic information about parties, which is true regardless of this relationship to the deploying company. Entities are modeled only once in HZ_PARTIES, regardless of how many roles they play. For example, if an organization is a customer, a distributor, and a partner, there is still only one record for them in HZ_PARTIES.

Parties can be one of four types:

Organization - Oracle Corporation
Person - Jane Doe
Group – Doc Household
Relationship - Jane Doe at Oracle Corporation

HZ_LOCATIONS: A location is a point in geographical space described by an address and/or geographical Indicators such as latitude or longitude.
• This table stores information about an address such as: street address and postal code.
• This table provides physical location information about parties (organizations and people) and customer accounts.
• Records in HZ_LOCATIONS can store delivery and postal code information about a location, store latitude and longitude, and can be used to determine the appropriate calculations and tax rates for sales tax and VAT calculations.

HZ_PARTY_SITES: This table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-Specific party information such as a person’s mail stops at their work address.
• One party can point to one or more party sites.
• One location can point to one or more party site.
• Party sites serve as the intersection between parties and locations, allowing for a many-to-many relationship between the two.

HZ_RELATIONSHIPS: This table stores information about relationships between two entities, for example, one party and another party.
• The SUBJECT_ID and OBJECT_ID columns specify the relationship that exists between two parties. For example, if the party relationship type is “Parent Of,” then a holding company could be the “SUBJECT_ID” in the relationship while one of its subsidiaries could be the OBJECT_ID. Creating a party contact causes a party relationship to be created.
• A party can have different relationships with one or more other parties that can change over time.

HZ_ORGANIZATION_PROFILES: This table stores a variety of information about a party of type Organization.
• This table gets populated when a party of type ORGANIZATION is created.
• Historical data is also stored in this table.

HZ_PERSON_PROFILES: This table stores a variety of information about a party of type Person.
• For example, this table could contain the correct spelling and phonetic pronunciation of the person’s name.
• Some information in this table may also be entered into the HZ_PARTIES table.

HZ_ORG_CONTACTS: This table stores a variety of information about an organization contact.
• The records in this table provide information about a contact position such as job title, rank, and department.
• This table is not used to store information about a specific person or organization. For example, this table may include a record for the position of Vice President of Manufacturing that indicates that the contact is a senior executive, but it would not include the name of the person in that position.

HZ_CUST_ACCOUNTS: This table stores information about customer/financial relationships established between a Party and the deploying company.
• Because a party can have multiple customer accounts, this table may contain several records for a single party. For example, an individual person may establish a personal account, a family account, and a professional account for a consulting practice.

HZ_CUST_ACCT_SITES_ALL: This table stores information about customer/financial account sites information.
Stores information about customer sites. One customer account can have multiple sites.

HZ_CUST_SITE_USES_ALL: This table stores information about the business purposes assigned to a customer account site.
• A customer account site can have multiple purposes, but each record in this table only specifies one purpose for a customer account site. For example, a customer account site may be assigned as a ship-to site in one record and as a bill-to site in another record.

HZ_CUST_ACCOUNT_ROLES: This table stores information about a role or function that a party performs as related to a customer account. For example, Jane Doe might be the Legal Contact for a specific customer account of Corporation ABC. Note that account ownership such as financial responsibility for an account is determined by the single party ID that is stored directly on the HZ_CUST_ACCOUNTS table.

HZ_CONTACT_POINTS: This table stores information about how to communicate with parties or party sites using electronic media or methods such as Electronic Data Interchange (EDI), e-mail, telephone, telex, and the Internet.
• Each medium or method should be stored as a separate method in this table. For example, the attributes of a complete telephone number connection should be stored in a record, while EDI information should be stored in a different record.

Continue>>>

Read More »

AR customer Party Site Creation in Oracle Applications R12

AR customer Party Site Creation in Oracle Applications R12

DECLARE
l_party_site_id NUMBER;
l_party_site_number NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_party_site_rec hz_party_site_v2pub.party_site_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_party_site_rec.party_id := 308013;
l_party_site_rec.location_id := 25254;
l_party_site_rec.created_by_module := 'TCA_MIGRATION';
hz_party_site_v2pub.create_party_site
(p_init_msg_list => 'T',
p_party_site_rec => l_party_site_rec,
x_party_site_id => l_party_site_id,
x_party_site_number => l_party_site_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);

IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;

DBMS_OUTPUT.put_line ('Error mesg' || l_output);
ELSE
DBMS_OUTPUT.put_line ('l_party_site_id ' || l_party_site_id);
DBMS_OUTPUT.put_line ('l_party_site_number ' || l_party_site_number);
END IF;
END;

Read More »

Account Receivables Interview Questions and Answers

1) What is Autolockbox?

A) Auto lockbox is a service that commercial banks offer corporate customers to enable them to out source their account receivable payment processing. Auto lockbox can also be used to transfer receivables from previous accounting systems into current receivables. It eliminates manual data entry by automatically processing receipts that are sent directly to banks. It involves three steps Import (Formats data from bank file and populates the Interface Table), Validation(Validates the data and then Populates data into Interim Tables), Post Quick Cash(Applies Receipts and updates Balances in BaseTables).

2)What is Transmission Format?

A) Transmission Format specifies how data in the lockbox bank file should be organized such that it can be successfully imported into receivables interface tables. Example, Default, Convert, Cross Currency, Zengen are some of the standard formats provided by oracle.

3)What is Auto Invoice?

A) Autoinvoice is a tool used to import and validate transaction data from other financial systems and create invoices, debit-memos, credit memos, and on account credits in Oracle receivables. Using Custom Feeder programs transaction data is imported into the autoinvoice interface tables.

Autoinvoice interface program then selects data from interface tables and creates transactions in receivables (Populates receivable base tables) . Transactions with invalid information are rejected by receivables and are stored in RA_INTERFACE_ERRORS_ALL interface table.

4) What are the Mandatory Interface Tables in Auto Invoice?

A)RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL

RA_INTERFACE_SALESCREDITS_ALL.

5) What are the Set up required for Custom Conversion, Autolockbox and Auto Invoice?

A) Autoinvoice program Needs AutoAccounting to be defined prior to its execution.6) 

6)What is AutoAccounting?

A) By defining AutoAccounting we specify how the receivables should determine the general ledger accounts for transactions manually entered or imported using Autoinvoice. Receivables automatically creates default accounts(Accounting Flex field values) for revenue, tax, freight, financial charge, unbilled receivable, and unearned revenue accounts using the AutoAccounting information.

7) What are Autocash rules?

A) Autocash rules are used to determine how to apply the receipts to the customers outstanding debit items. Autocash Rule Sets are used to determine the sequence of Autocash rules that Post Quickcash uses to update the customers account balances.

8) What are Grouping Rules? (Used by Autoinvoice)

A) Grouping rules specify the attributes that must be identical for lines to appear on the same transaction. After the grouping rules are defined autoinvoice uses them to group revenues and credit transactions into invoices debit memos, and credit memos.

9) What are Line Ordering Rules? (Used by Autoinvoice)

A) Line ordering rules are used to order transaction lines when grouping the transactions into invoices, debit memos and credit memos by autoinvoice program. For instance if transactions are being imported from oracle order management , and an invoice line ordering rule for sales_order _line is created then the invoice lists the lines in the same order of lines in sales order.

10) In which table you can see the amount due of a customer?

A) AR_PAYMENT_SCHEDULES_ALL

11) How do you tie Credit Memo to the Invoice?

At table level, In RA_CUSTOMER_TRX_ALL, If you entered a credit memo, the PREVIOUS_CUSTOMER_TRX_ID column stores the customer transaction ID of the invoice that you credited. In the case of on-account credits, which are not related to any invoice when the credits are created, the PREVIOUS_CUSTOMER_TRX_ID column is null.

12)What are the available Key Flex Fields in Oracle Receivables?

A) Sales Tax Location Flex field, It’s used for sales tax calculations.

Territory Flex field is used for capturing address information.

13). What are Transaction types? Types of Transactions in AR?

A) Transaction types are used to define accounting for different transactions such as Debit Memo, Credit Memo, On-Account Credits, Charge Backs, Commitments and invoices.

14) What are the different statuses for Receipts?

A) Unidentified – Lack of Customer Information

Unapplied – Lack of Transaction/Invoice specific information (Ex- Invoice Number)

Applied – When all the required information is provided.

On-Account, Non-Sufficient Funds, Stop Payment, and Reversed receipt.

Customer Conversion:

Interface Tables :

RA_CUSTOMERS_INTERFACE_ALL

RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL

Base Tables :

RA_CUSTOMERS

RA_ADDRESSES

RA_SITE_USES_ALL

RA_CUSTOMER_PROFILES_ALL

RA_PHONES

Auto Invoice:

Interface Tables :

RA_INTERFACE_LINES_ALL,

RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL,

RA_INTERFACE_ERRORS_ALL
Base Tables :

RA_CUSTOMER_TRX_ALL,

RA_CUSTOMER_TRX_LINES_ALL,
RA_CUST_TRX_LINE_GL_DIST_ALL,

RA_CUST_TRX_LINE_SALESREPS_ALL,

RA_CUST_TRX_TYPES_ALL

AutoLockBox:

Interface Tables :

AR_PAYMENTS_INTERFACE_ALL (POPULATED BY IMPORT PROCESS)
Interim tables :

AR_INTERIM_CASH_RECEIPTS_ALL (All Populated by Submit Validation)
AR_INTERIM_CASH_RCPT_LINES_ALL,
AR_INTERIM_POSTING
Base Tables :

AR_CASH_RECEIPTS_ALL,

AR_RECEIVABLE_APPLICATIONS_ALL,

AR_PAYMENT_SCHEDULES_ALL ( All Populated by post quick cash)

Read More »

Oracle Receivables Module Technical Details

Oracle Receivables Module Technical Details

Oracle Accounts Receivable uses the following tables for recording customer account information:

The major tables containing parties and customer accounts information in Oracle Receivables are grouped by business function.
• HZ_PARTIES
• HZ_CUST_ACCOUNTS
• HZ_PARTY_SITES
• HZ_CUST_ACCT_SITES_ALL
• HZ_CUST_SITE_USES_ALL
• HZ_LOCATIONS
• HZ_PARTY_RELATIONSHIPS
• HZ_ORGANIZATION_PROFILES
• HZ_CONTACT_POINTS
• HZ_CUST_ACCOUNT_ROLES
• HZ_PERSON_PROFILES
• HZ_ORG_CONTACTS

Transaction Tables
• RA_CUSTOMER_TRX_ALL
• RA_CUSTOMER_TRX_LINES_ALL
• RA_CUST_TRX_LINE_GL_DIST_ALL
• AR_PAYMENT_SCHEDULES_ALL
• AR_CASH_RECEIPTS_ALL
• AR_CASH_RECEIPT_HISTORY_ALL
• AR_RECEIVABLE_APPLICATIONS_ALL
• AR_ADJUSTMENTS_ALL

HZ_PARTIES
A party is an entity that can enter into a business relationship.
This table stores basic information about parties, which is true regardless of this relationship to the deploying company. Entities are modeled only once in HZ_PARTIES, regardless of how many roles they play. For example, if an organization is a customer, a distributor, and a partner, there is still only one record for them in HZ_PARTIES.

Parties can be one of four types:
Organization - Oracle Corporation
Person - Jane Doe
Group – Doc Household
Relationship - Jane Doe at Oracle Corporation

HZ_LOCATIONS
A location is a point in geographical space described by an address and/or geographical Indicators such as latitude or longitude.
• This table stores information about an address such as: street address and postal code.
• This table provides physical location information about parties (organizations and people) and customer accounts.
• Records in HZ_LOCATIONS can store delivery and postal code information about a location, store latitude and longitude, and can be used to determine the appropriate calculations and tax rates for sales tax and VAT calculations.

HZ_PARTY_SITES
This table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-Specific party information such as a person’s mail stops at their work address.
• One party can point to one or more party sites.
• One location can point to one or more party site.
• Party sites serve as the intersection between parties and locations, allowing for a many-to-many relationship between the two.

HZ_RELATIONSHIPS
This table stores information about relationships between two entities, for example, one party and another party.
• The SUBJECT_ID and OBJECT_ID columns specify the relationship that exists between two parties. For example, if the party relationship type is “Parent Of,” then a holding company could be the “SUBJECT_ID” in the relationship while one of its subsidiaries could be the OBJECT_ID. Creating a party contact causes a party relationship to be created.
• A party can have different relationships with one or more other parties that can change over time.

HZ_ORGANIZATION_PROFILES
This table stores a variety of information about a party of type Organization.
• This table gets populated when a party of type ORGANIZATION is created.
• Historical data is also stored in this table.

HZ_PERSON_PROFILES
This table stores a variety of information about a party of type Person.
• For example, this table could contain the correct spelling and phonetic pronunciation of the person’s name.
• Some information in this table may also be entered into the HZ_PARTIES table.

HZ_ORG_CONTACTS
This table stores a variety of information about an organization contact.
• The records in this table provide information about a contact position such as job title, rank, and department.
• This table is not used to store information about a specific person or organization. For example, this table may include a record for the position of Vice President of Manufacturing that indicates that the contact is a senior executive, but it would not include the name of the person in that position.

HZ_CUST_ACCOUNTS
This table stores information about customer/financial relationships established between a Party and the deploying company.
• Because a party can have multiple customer accounts, this table may contain several records for a single party. For example, an individual person may establish a personal account, a family account, and a professional account for a consulting practice.

HZ_CUST_ACCT_SITES_ALL
This table stores information about customer/financial account sites information.
Stores information about customer sites. One customer account can have multiple sites.

HZ_CUST_SITE_USES_ALL
This table stores information about the business purposes assigned to a customer account site.
• A customer account site can have multiple purposes, but each record in this table only specifies one purpose for a customer account site. For example, a customer account site may be assigned as a ship-to site in one record and as a bill-to site in another record.

HZ_CUST_ACCOUNT_ROLES
This table stores information about a role or function that a party performs as related to a customer account. For example, Jane Doe might be the Legal Contact for a specific customer account of Corporation ABC. Note that account ownership such as financial responsibility for an account is determined by the single party ID that is stored directly on the HZ_CUST_ACCOUNTS table.

HZ_CONTACT_POINTS
This table stores information about how to communicate with parties or party sites using electronic media or methods such as Electronic Data Interchange (EDI), e-mail, telephone, telex, and the Internet.
• Each medium or method should be stored as a separate method in this table. For example, the attributes of a complete telephone number connection should be stored in a record, while EDI information should be stored in a different record.

RA_CUSTOMER_TRX_ALL
This table stores invoice, debit memo, commit-ment, chargeback, bills receivable, and credit memo header information.
• Each row includes general invoice information such as customer, transaction type, and printing instructions.
• You need one row for each invoice, debit memo, commitment, and credit memo you create in Oracle Receivables and these are all distinguished by their transaction types stored in RA_CUST_ TRX_ TYPES_ALL.

RA_CUSTOMER_TRX_LINES_ALL
This table stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines. It describes to the customer the charges that appear on these documents.

AR_PAYMENT_SCHEDULES_ALL
This table stores all transactions except adjust-ments and miscellaneous cash receipts. A miscellaneous cash receipt is one that is not connected to a customer.
• All customer-related activity is logged in this table.
• This table is updated when an activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, bills receivable,
receipt, or commitments.

RA_CUST_TRX_TYPES_ALL
This table stores information about each transaction type for all classes of transactions, for example, invoices, commitments, and credit memos.
• Each row includes Auto Accounting information
as well as standard defaults for the resulting invoices. The primary key for this table is CUST_TRX_TYPE_ID.

AR_TRANSACTION_HISTORY_ALL
This table is a Bills Receivable-specific table containing the history of a transaction’s lifecycle.
• A new row is created each time there is activity on the transaction or the status of the transaction has changed.
• This table stores the header for the Receivables posting information.

AR_DISTRIBUTIONS_ALL
This table stores the accounting distributions for cash receipts, miscellaneous receipts, adjustments, credit memo applications, cash receipt applications, and bills receivable transactions.

AR_CASH_RECEIPTS_ALL
This table stores one record for each receipt entry.
• All cash receipts are logged in this table.
• Oracle Receivables creates records concurrently in the AR_CASH _RECEIPT_ HISTORY_ ALL, AR_PAYMENT_ SCHEDULES_ ALL, AR_DISTRI-BUTIONS_ALL, and AR_ RECEIVABLE_ APPLICA-TIONS_ALL tables for invoice-related receipts.
• For receipts that are not related to invoices, records are created in the AR_MISC_CASH_ DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ ALL table.

AR_CASH_RECEIPT_HISTORY_ALL
This table stores all of the activity that is contained for the life cycle of a receipt.
• Each row represents one step.
• The status field for that row tells you which step the receipt has reached.
• Possible statuses are Approved, Confirmed, Remitted, Cleared, and Reversed.

AR_RECEIVABLE_APPLICATIONS_ALL
This table stores all accounting entries for cash and credit memo applications.
• Each row includes the amount applied, status, and accounting flex field information.

AR_MISC_CASH_DISTRIBUTIONS_ALL
This table stores all accounting entries for miscellaneous cash applications.
• Miscellaneous cash cannot be invoiced, such as stock revenue, interest income, and investment income.
• AR_CASH_RECEIPTS_ALL stores one record for each payment, and this table stores one record for each distribution of the receipt.

AR_RECEIPT_CLASSES
This table stores the different receipt classes that you define.
• Receipt classes determine whether the receipt[s] belonging to this class are created manually or automatically, and whether the receipts go through the different steps in a receipt’s life-cycle.

AR_RECEIPT_METHODS
This table stores information about Payment Methods, receipt attributes that you define and assign to Receipt Classes to account for receipts and their applications.
• For automatically created receipts, a Payment Method defines the rules for creating these receipts.
• For manually created receipts, a Payment Method defines a user-definable type for the receipt.
• Each Payment Method is associated with a set of bank accounts, which forms the set of bank accounts you can assign to your receipt.

AR_ADJUSTMENTS_ALL
This table stores information about the adjustment applied to the Invoices.

Read More »

AR interface

Download Query

CREATE OR REPLACE PACKAGE BODY XXTE_AR_INVOICE_INTF
AS
   PROCEDURE XXTE_ARINVOICE_MAIN (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
   IS
      lv_errbuff   VARCHAR2 (1000);
      lv_retcode   NUMBER;
       BEGIN    
            xxte_arinvoice_ins (lv_errbuff, lv_retcode);
            EXCEPTION
            WHEN OTHERS
            THEN
                lv_retcode := 1;
                lv_errbuff := 'Error: In Main Procudure.' || SQLERRM;
                fnd_file.put_line (fnd_file.LOG, lv_errbuff);
       END;
   PROCEDURE xxte_arinvoice_ins (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
   IS    
      CURSOR stg_cur
      IS
         SELECT a.*, a.ROWID
           FROM xxte_ar_invoice_st a
          WHERE process_flag = 'N';
      
           p_api_version             NUMBER;
           p_init_msg_list           VARCHAR2 (200);
           p_commit                  VARCHAR2 (200);
           p_batch_source_rec        apps.ar_invoice_api_pub.batch_source_rec_type;
           p_trx_header_tbl          apps.ar_invoice_api_pub.trx_header_tbl_type;
           p_trx_lines_tbl           apps.ar_invoice_api_pub.trx_line_tbl_type;
           p_trx_dist_tbl            apps.ar_invoice_api_pub.trx_dist_tbl_type;
           p_trx_salescredits_tbl    apps.ar_invoice_api_pub.trx_salescredits_tbl_type;
           p_trx_contingencies_tbl   apps.ar_invoice_api_pub.trx_contingencies_tbl_type;
           x_customer_trx_id         NUMBER;
           x_return_status           VARCHAR2 (200);
           x_msg_count               NUMBER;
           x_msg_data                VARCHAR2 (200);
           h_trx_header_id           ra_customer_trx_all.customer_trx_id%TYPE;
           l_trx_ln_hdr_id           ra_customer_trx_all.customer_trx_id%TYPE;
           l_trx_line_id             ra_customer_trx_lines_all.customer_trx_line_id%TYPE;
           lv_errbuff                VARCHAR2 (1000);
           lv_retcode                NUMBER;         
           lv_user_id                NUMBER;
           lv_resp_id                NUMBER;
           lv_resp_appl_id           NUMBER;
           lv_source                 NUMBER;
           lv_gl_date                VARCHAR2(1);
           lv_trx_date               VARCHAR2(1);
           lv_class                  NUMBER;
           lv_customer_id            NUMBER;
           lv_error_message          VARCHAR2 (2400);
           lv_error_code             NUMBER := 0;
         
   BEGIN    
      lv_user_id := fnd_profile.VALUE ('USER_ID');
      lv_resp_id := fnd_profile.VALUE ('RESP_ID');
      lv_resp_appl_id := fnd_profile.VALUE ('RESP_APPL_ID');
          BEGIN
             fnd_global.apps_initialize (lv_user_id, lv_resp_id, lv_resp_appl_id);
             mo_global.init ('AR');
          END;
    
      -- Purge the previous processed data in the stage table
          BEGIN
                DELETE FROM xxte_ar_invoice_st WHERE process_flag != 'N';
                EXCEPTION
                WHEN OTHERS
                THEN
                    lv_error_code := 1;
                    lv_error_message :=
                    'Error: Unable To Delete Record(s) In Staging Table.'
                    || SQLERRM;
                    fnd_file.put_line (fnd_file.LOG, lv_error_message);
          END;
    
   fnd_file.put_line(2,'****************************AR Invoice Process***************************');
   fnd_file.put_line(2,'------------------------------------------------------------------------------- ');
 
    FOR i IN stg_cur
        LOOP
            lv_error_message := NULL;
            lv_error_code := 0;
            lv_source := 1002;--'TE INSIS';
            --lv_class := 1080;

            -- Validation for GL date
             BEGIN
                SELECT 'X'
                INTO lv_gl_date
                FROM gl_period_statuses
                WHERE application_id = 222                ---For Oracle Receivable
                AND set_of_books_id = 2022
                AND (i.gl_date >= start_date AND i.gl_date <= end_date)
                AND closing_status = 'O';    

                IF lv_gl_date IS NULL
                THEN
                   RAISE NO_DATA_FOUND;
                END IF;
                
                EXCEPTION
                WHEN NO_DATA_FOUND
                THEN
                    lv_error_code := 1;
                    lv_error_message := 'Error: ' || 'GL Date Is NULL or Period is Not Open for the GL Date ';
                    fnd_file.put_line (fnd_file.LOG, lv_error_message);
                WHEN OTHERS
                THEN
                    lv_error_code := 1;
                    lv_error_message :=
                     'Error: '
                    || 'Period is Not Open for the GL Date or GL Date IS Null'
                    || ' '
                    || i.gl_date;
                    fnd_file.put_line (fnd_file.LOG, lv_error_message);
             END;
          
        -- Validation for Trx date
            BEGIN
                SELECT 'X'
                INTO lv_trx_date
                FROM gl_period_statuses
                WHERE application_id = 222                ---For Oracle Receivable
                AND set_of_books_id = 2022
                AND (i.trx_date >= start_date AND i.trx_date <= end_date)
                AND closing_status = 'O';    
                
                IF lv_trx_date IS NULL
                THEN
                    RAISE NO_DATA_FOUND;
                END IF;
                
                EXCEPTION
                WHEN NO_DATA_FOUND
                THEN
                    lv_error_code := 1;
                    lv_error_message := 'Error: ' || 'Transaction Date Is NULL or Period is Not Open for the Transaction Date ';
                    fnd_file.put_line (fnd_file.LOG, lv_error_message);
                WHEN OTHERS
                THEN
                    lv_error_code := 1;
                    lv_error_message :=
                     'Error: '
                    || 'Period is Not Open for the Transaction Date or Transaction Date IS Null'
                    || ' '
                    || i.trx_date;
                    fnd_file.put_line (fnd_file.LOG, lv_error_message);
                 END;   
             
             -- Validation for customer name
             BEGIN
                SELECT customer_id
                INTO lv_customer_id
                FROM ar_customers
                WHERE customer_name = i.bill_to_name;

                IF lv_customer_id IS NULL
                THEN
                    lv_error_code := 1;
                    lv_error_message :=
                    'Error: '
                    || 'Customer Name '
                    || ' '
                    || i.bill_to_name
                    || ' '
                    || 'is Not Found';
                    fnd_file.put_line (fnd_file.LOG, lv_error_message);
                END IF;
                
                EXCEPTION
                WHEN OTHERS
                THEN
                    lv_error_code := 1;
                    lv_error_message :=
                    'Error: '
                    || 'Customer Name '
                    || ' '
                    || i.bill_to_name
                    || ' '
                    || 'is Not Found';
                    fnd_file.put_line (fnd_file.LOG, lv_error_message);
             END;

             --- Validation for description       
             BEGIN
                IF i.description IS NULL
                THEN
                    lv_error_code := 1;
                    lv_error_message :=
                    'Error: '
                    || 'Description is null'                  ;
                    fnd_file.put_line (fnd_file.LOG, lv_error_message);
                END IF; 
             END;
           
             --- Validation for unit price       
             BEGIN
                IF i.unit_price IS NULL
                THEN
                    lv_error_code := 1;
                    lv_error_message :=
                    'Error: '
                    || 'Unit Price is null'                  ;
                    fnd_file.put_line (fnd_file.LOG, lv_error_message);
                END IF; 
             END;
           
             --- Validation for invoicing rule
            BEGIN
                IF i.class = '1020' THEN
                    IF i.rule_end_date IS NULL and i.rule_start_date IS NULL THEN
                           lv_error_code := 1;
                           lv_error_message :=
                                 'Error: '
                              || 'Rule Start Date/Rule End Date Is Null'                  ;
                           fnd_file.put_line (fnd_file.LOG, lv_error_message);
                    ELSIF i.rule_start_date > i.rule_end_date THEN
                           lv_error_code := 1;
                           lv_error_message :=
                                 'Error: '
                              || 'Rule End Date Is Greater Then Rule Start Date'                  ;
                           fnd_file.put_line (fnd_file.LOG, lv_error_message);
                    ELSIF i.rule_end_date <> i.gl_date and i.rule_end_date <> i.trx_date THEN
                           lv_error_code := 1;
                           lv_error_message :=
                                 'Error: '
                              || 'GL Date, Transaction Date and Rule End Date Must Be Same'                  ;
                           fnd_file.put_line (fnd_file.LOG, lv_error_message);     
                    END IF;
                END IF;
            END;
           
           
             IF lv_error_code = 0 THEN
           
             /* API initialize */
               p_api_version := 1.0;
               p_init_msg_list := fnd_api.g_false;
               p_commit := fnd_api.g_true;         
               p_batch_source_rec.batch_source_id := lv_source;
             
               /*Header Part*/
               SELECT ra_customer_trx_s.NEXTVAL
                 INTO h_trx_header_id
                 FROM DUAL;

               p_trx_header_tbl (1).trx_header_id := h_trx_header_id;
               p_trx_header_tbl (1).trx_date := i.trx_date;
                --  p_trx_header_tbl (1).trx_currency := 'AED';
                --  p_trx_header_tbl (1).trx_class := 'INV';
               p_trx_header_tbl (1).cust_trx_type_id := i.class;
               p_trx_header_tbl (1).gl_date := i.gl_date;
               p_trx_header_tbl (1).bill_to_customer_id := lv_customer_id;
             
               IF i.class = '1020' THEN
                p_trx_header_tbl (1).invoicing_rule_id := -2;
               END IF;    
               --p_trx_header_tbl (1).term_id := 5;
             
               /*Line Part*/
               SELECT ra_customer_trx_s.CURRVAL
                 INTO l_trx_ln_hdr_id
                 FROM DUAL;

                p_trx_lines_tbl (1).trx_header_id := l_trx_ln_hdr_id;

               SELECT ra_customer_trx_lines_s.NEXTVAL
                 INTO l_trx_line_id
                 FROM DUAL;

               p_trx_lines_tbl (1).trx_line_id := l_trx_line_id;
               p_trx_lines_tbl (1).line_number := 1;         
               p_trx_lines_tbl (1).description := i.description;                            
               p_trx_lines_tbl (1).quantity_invoiced := 1;
               p_trx_lines_tbl (1).unit_selling_price := i.unit_price;
               p_trx_lines_tbl (1).line_type := 'LINE';
             
               IF i.class = '1020' THEN
                p_trx_lines_tbl (1).accounting_rule_id := 2000;
                p_trx_lines_tbl (1).rule_start_date := i.rule_start_date;
                p_trx_lines_tbl (1).rule_end_date := i.rule_end_date;
               END IF;         
                      
               x_customer_trx_id := NULL;
               x_return_status := NULL;
               x_msg_count := NULL;
               x_msg_data := NULL;

               BEGIN                            
                   apps.ar_invoice_api_pub.create_single_invoice (p_api_version,
                                                                  p_init_msg_list,
                                                                  p_commit,
                                                                  p_batch_source_rec,
                                                                  p_trx_header_tbl,
                                                                  p_trx_lines_tbl,
                                                                  p_trx_dist_tbl,
                                                                  p_trx_salescredits_tbl,
                                                                  p_trx_contingencies_tbl,
                                                                  x_customer_trx_id,
                                                                  x_return_status,
                                                                  x_msg_count,
                                                                  x_msg_data
                                                                 );
                  
                fnd_file.put_line (2, 'Receivable Invoice Is Created. Customer Trx ID:'|| x_customer_trx_id);
                UPDATE xxte_ar_invoice_st SET process_flag = 'S' WHERE ROWID = i.ROWID;                                                                                                                                                                                           
               
               EXCEPTION                    
               WHEN OTHERS
               THEN
                     lv_error_code := 1;
                     lv_error_message :=
                              'Error: Unable To Insert Data In Base Table.' || SQLERRM;
                     fnd_file.put_line (fnd_file.LOG, lv_error_message);                                    
               END;
               ELSE
                    fnd_file.put_line (2, 'Please Check The Log File For Error(s)');
                    UPDATE xxte_ar_invoice_st
                      SET process_flag = 'E',
                          error_message = lv_error_message
                    WHERE ROWID = i.ROWID;
               END IF;
        END LOOP;  
           COMMIT;
           fnd_file.put_line(2,'**************************** END ***************************');
           fnd_file.put_line(2,'------------------------------------------------------------------------------- ');         
EXCEPTION
WHEN OTHERS
THEN
     lv_error_code := 1;
     lv_error_message :=
              'Error: Unable To Insert Data In Base Table.' || SQLERRM;
     fnd_file.put_line (fnd_file.LOG, lv_error_message);     
      
END; 
END xxte_ar_invoice_intf;

Download Query

Read More »

Delete AR Transaction (Receivables) Using API

Step 1 : Create a table which is having all the transaction which has to be deleted.

CREATE TABLE xx_delete_transaction
AS
   SELECT customer_trx_id,
          trx_date,
          trx_number,
          bill_to_customer_id,
          bill_to_site_use_id,
          org_id,
          creation_date,
          created_by,
          'N' v_ret_status,
          'N' v_msg_count,
          'N' v_msg_data,
          'N' v_message_tbl
     FROM ra_customer_trx_all
    WHERE org_id = 109 AND trx_date = '03-AUG-2013'    

Step 2 : Now run this procedure

DECLARE
   CURSOR cur_all_trx
   IS
      SELECT ROWID,
             NULL party_number,
             rct.org_id,
             rct.customer_trx_id,
             rct.trx_number
        FROM xx_delete_transaction rct
       WHERE NVL (V_RET_STATUS, 0) <> 'S';

   xv_msg_data     VARCHAR2 (4000) := NULL;
   xv_msg_count    NUMBER := 0;
   v_msg_index     NUMBER := 0;
   xv_ret_status   VARCHAR2 (1) := NULL;
   v_message_tbl   arp_trx_validate.message_tbl_type;
   v_res           VARCHAR2 (4000) := NULL;
   v_res_name      VARCHAR2 (4000) := NULL;
   v_app           VARCHAR2 (4000) := NULL;
   v_user          NUMBER := 1712;
BEGIN
   DBMS_OUTPUT.put_line ('Detele Transaction...');

   FOR c_rec IN cur_all_trx
   LOOP
      DBMS_OUTPUT.put_line ('   Transaction No.: ' || c_rec.trx_number);
      DBMS_OUTPUT.put_line ('   Transaction ID : ' || c_rec.customer_trx_id);
      DBMS_OUTPUT.put_line ('   Org ID         : ' || c_rec.org_id);
      ----------------------------------------------------------------------------
      ---- Setting the org context for the particular session
      apps.mo_global.set_policy_context ('S', c_rec.org_id);

      -- apps.mo_global.init('AR');

      SELECT application_id, responsibility_id
        INTO v_app, v_res
        FROM fnd_responsibility_tl
       WHERE responsibility_id = 50705;

      ---- Setting the oracle applications context for the particular session
      apps.fnd_global.apps_initialize (v_user, v_res, v_app);
      ----------------------------------------------------------------------------
      xv_ret_status := NULL;
      xv_msg_count := NULL;
      xv_msg_data := NULL;

      --update the Allow Transaction Deletion to Yes to Delete (As mentioned above, better to do it from application)
      UPDATE ar_system_parameters_all
         SET invoice_deletion_flag = 'Y'
       WHERE org_id = c_rec.org_id;

      ar_invoice_api_pub.delete_transaction (
         p_api_name           => 'Delete_Transaction',
         p_api_version        => 1.0,
         p_init_msg_list      => fnd_api.g_true,
         p_commit             => fnd_api.g_true,
         p_validation_level   => fnd_api.g_valid_level_full,
         p_customer_trx_id    => c_rec.customer_trx_id,
         p_return_status      => xv_ret_status,
         p_msg_count          => xv_msg_count,
         p_msg_data           => xv_msg_data,
         p_errors             => v_message_tbl);

      UPDATE xx_delete_transaction
         SET v_ret_status = xv_ret_status
       WHERE ROWID = c_rec.ROWID;

      UPDATE xx_delete_transaction
         SET v_msg_count = xv_msg_count
       WHERE ROWID = c_rec.ROWID;



      IF xv_ret_status <> 'S'
      THEN
         DBMS_OUTPUT.put_line ('   Status: ' || xv_ret_status);

         UPDATE xx_delete_transaction
            SET v_msg_data = v_ret_status
          WHERE ROWID = c_rec.ROWID;

         FOR i IN 1 .. xv_msg_count
         LOOP
            apps.fnd_msg_pub.get (i,
                                  apps.fnd_api.g_false,
                                  xv_msg_data,
                                  v_msg_index);
            DBMS_OUTPUT.put_line ('   Error : ' || xv_msg_data);
         END LOOP;

         DBMS_OUTPUT.put_line ('   ' || xv_msg_data);
      ELSE
         DBMS_OUTPUT.put_line ('   Deleted.');

         -- Revert back to the original value for the deletion flag
         UPDATE ar_system_parameters_all
            SET invoice_deletion_flag = 'N'
          WHERE org_id = c_rec.org_id;
      END IF;

      DBMS_OUTPUT.put_line ('--------------------');
      COMMIT;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error : ' || SQLERRM);
END;

Read More »

Technical Interview Questions in Oracle Apps

Download this document : Click Here

Question: How will you migrate Oracle General Ledger Currencies and Sets of Books Definitions fromone environment to another without reKeying? Will you use FNDLOAD?
Answer: FNDLOAD can not be used in the scenario. You can use migrator available in "Oracle iSetup" Responsibility

Question: This is a very tough one, almost impossible to answer, but yet I will ask. Which Form in Oracle Applications has most number of Form Functions?
Answer: "Run Reports". And why not, the Form Function for this screen has a parameter to which we pass name of the "Request Group", hence securing the list of Concurrent Programs that are visible in "Run Request" Form. Just so that you know, there are over 600 form functions for "Run Reports"

Question: Which responsibility do you need to extract Self Service Personalizations?
Answer:Functional Administrator

Question: Can you list any one single limitation of Forms Personalization feature that was delivered with 11.5.10
Answer:You can not implement interactive messages, i.e. a message will give multiple options for Response. The best you can get from Forms Personalization to do is popup up Message with OK option.

Question: You have just created two concurrent programs namely "XX PO Prog1" & "XX PO Prog2". Now you wish to create a menu for Concurrent Request submission such that only these two Concurrent Programs are visible from that Run Request menu. Please explain the steps to implement this?
Answer: a) Define a request group, lets say with name "XX_PO_PROGS"
b) Add these two concurrent programs to the request group "XX_PO_PROGS"
c) Define a new Form Function that is attached to Form "Run Reports"
d) In the parameter field of Form Function screen, enter
REQUEST_GROUP_CODE="XX_PO_PROGS" REQUEST_GROUP_APPL_SHORT_NAME="XXPO" TITLE="XXPO:XX_PO_PROGS"
e) Attach this form function to the desired menu.

Question: Does Oracle 10g support rule based optimization?
Answer: The official stance is that RBO is no longer supported by 10g.

Question: Does oracle support partitioning of tables in Oracle Apps?
Answer: Yes, Oracle does support partitioning of tables in Oracle Applications. There are several implementations that partition on GL_BALANCES. However your client must buy licenses to if they desire to partition tables. To avoid the cost of licensing you may suggest the clients may decide to permanently close their older GL Periods, such that historical records can be archived.
Note: Before running the archival process the second time, you must clear down the archive table GL_ARCHIVE_BALANCES (don’t forget to export archive data to a tape).

Question: What will be your partitioning strategy on GL_BALANCES? Your views please?
Answer: This really depends upon how many periods are regularly reported upon, how many periods are left open etc. You can then decide to partition on period_name, or period ranges, or on the status of the GL Period.


Question: Does Oracle support running of gather stats on SYS schema in Oracle Apps?
Answer: If your Oracle Applications instance is on 10g, then you can decide to run stats for SYS schema. This can be done by exec dbms_stats.gather_schema_stats('SYS');
Alternately using command dbms_stats.gather_schema_stats('SYS',cascade=>TRUE,degree=>20);
I will prefer the former with default values.
If you wish to delete the stats for SYS use exec dbms_stats.delete_schema_stats('SYS');
You can schedule a dbms_job for running stats for SYS schema.


Question: Can you use concurrent program "Gather Schema Statistics" to gather stats on sys schema in oracle apps?
Answer: No, "Gather Schema Statistics" has no parameters for SYS schema. Please use dbms_job.


Question: Which table is used to provide drill down from Oracle GL into sub-ledger?
Answer: GL_IMPORT_REFERENCES

Question: What is the significance of profile option “Node Trust Level” in Oracle Apps.
Answer: If this profile option is set to a value of external against a server, then it signifies that the specific mid-tier is External i.e. it will be exposed to the www. In other words this server is not within the firewall of your client. The idea behind this profile option is to flag such middle-tier so that special restrictions can be applied against its security, which means a very restricted set of responsibilities will be available from such Middle-Tier.


Question: What is the significance of profile option “Responsibility Trust Level”.
Answer: In order to make a responsibility accessible from an external web tier, you must set profile option “Responsibility Trust Level” at responsibility level to “External”. Only those responsibilities that have this profile option against them will be accessible from External Middle tiers.


Question: What else can you suggest to restrict the access to screens from external web tiers?
Answer: You may use URL filtering within Apache.


Question: What is the role of Document Manager in Oracle Purchasing?
Answer: POXCON is an immediate concurrent program. It receives pipe signal from the application when a request is made for approval/reservations/receipts.


Question: How to debug a document manager in Oracle Apps? 
Answer: Document manger runs within the concurrent manager in Oracle Applications. When an application uses a Document Manager, it sends a pipe signal which is picked up by the document manager.
There are two mechanisms by which to trace the document manager
1. Set the debugging on by using profile option
STEP 1. Set profile option "Concurrent:Debug Flags" to TCTM1
This profile should only generate debugs when set at Site level(I think, as I have only tried site), because Document Manager runs in a different session.
STEP 2. Bounce the Document Managers
STEP 3. Retry the Workflow to generate debugs.
STEP 4. Reset profile option "Concurrent:Debug Flags" to blank
STEP 5. have a look at debug information in table fnd_concurrent_debug_info

2. Enable tracing for the document managers 
This can be done by setting profile option “Initialization SQL Statement – Custom” against your username before reproducing the issue. The value of this profile will be set so as to enable trace using event 10046, level 12.

Question: You have written a Java Concurrent Program in Oracle Apps. You want to modify the CLASSPATH such that new class CLASSPATH is effective just for this program.
Answer: In the options field of the concurrent program you can enter something similar to below.
-cp <your custom lib pathused by Java Conc Prog> :/home/xxvisiondev/XXDEVDB/comn/java/appsborg.zip:/home/xxvisiondev/XXDEVDB/comn/java

Question: How will you open a bc4j package in jdeveloper?
Answer: Oracle ships a file named server.xml with each bc4j package. You will need to ftp that file alongside other bc4j objects(VO’s, EO’s, AM, Classes etc).
Opening the server.xml will load the complete package starting from AM(application module). This is a mandatory step when building Extensions to framework.


Question: In OA Framework Self-Service screen, you wish to disable a tab. How will you do it?
Answer: Generally speaking, the tabs on a OA Framework page are nothing but the SubMenus. By entering menu exclusion against the responsibility, you can remove the tab from self service page.

Question: In self service, you wish to change the background color and the foreground text of the OA Framework screens to meet your corporate standards. How will you do it?
Answer: You will need to do the below steps
a….Go to Mid Tier, and open $OA_HTML/cabo/styles/custom.xss
b…Enter below text( change colours as needed)
<style name="DarkBackground">
<property name="background-color">#000066</property>
</style>
<style name="TextForeground">
<property name="color">#0000FF</property>
</style>
c… cd $OA_HTML/cabo/styles/cache
d…Take a backup of all the css files.
e…Delete all the files of following pattern oracle-desktop*.css
The idea here is to delete the cache. Next time when you logon to Oracle Apps Self Service, the Framework will rebuild the css file if found missing for your browser.


Question: Can you extend and substitue a root AM ( Application Module) in OA Framework using JDeveloper.
Answer: You can extend the AM in jDeveloper, but it doesn’t work( at least it didn’t work in 11.5.9). I am hopeful that Oracle will deliver a solution to this in the future.

Question: In a workflow notification, you have a free text response field where the user enters the Vendor Number for the new vendor. You want to validate the value entered in the notification response field upon the submission of a response. How will you do it?
Answer: You will need to attach a post notification function to the Workflow Notification.
The PL/SQL code will look similar to below:-
The below code will display an error in the notification when user attempts to create a Duplicate Vendor Number.
PROCEDURE validate_response_from_notif
(
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2
) IS
l_nid NUMBER;
l_activity_result_code VARCHAR2(200);
v_newly_entered_vendor_num VARCHAR2(50);
CURSOR c_get_response_for_new_vendor IS
SELECT wl.lookup_code
FROM wf_notification_attributes wna
,wf_notifications wn
,wf_message_attributes_vl wma
,wf_lookups wl
WHERE wna.notification_id = l_nid
AND wna.notification_id = wn.notification_id
AND wn.message_name = wma.message_name
AND wn.message_type = wma.message_type
AND wna.NAME = wma.NAME
AND wma.SUBTYPE = 'RESPOND'
AND wma.format = wl.lookup_type
AND wna.text_value = wl.lookup_code
AND wma.TYPE = 'LOOKUP'
AND decode(wma.NAME, 'RESULT', 'RESULT', 'NORESULT') = 'RESULT';
BEGIN
IF (funcmode IN ('RESPOND'))
THEN
l_nid := wf_engine.context_nid;
OPEN c_get_response_for_new_vendor;
FETCH c_get_response_for_new_vendor
INTO l_activity_result_code;
CLOSE c_get_response_for_new_vendor;
v_newly_entered_vendor_num := wf_notification.getattrtext(l_nid,'NEWLY_ENTERED_VENDOR_NUM_4_PO');
IF l_activity_result_code = 'NEW_VENDOR'
AND does_vendor_exist(p_vendor => v_newly_entered_vendor_num)
THEN
RESULT := 'ERROR: VendorNumber you entered already exists';
RETURN;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
RESULT := SQLERRM;
END validate_response_from_notif;
Question: How to make concurrent program end with warning?
Answer: If the concurrent program is of type PL/SQL, you can assign a value of 1 to the “retcode” OUT Parameter.
For a Java Concurrent program, use the code similar to below
ReqCompletion lRC;
//get handle on request completion object for reporting status
lRC = pCpContext.getReqCompletion();
lRC.setCompletion(ReqCompletion.WARNING, "WARNING");

Question: How do you link a Host type concurrent program to Concurrent Manager?
Answer: Assuming your executable script is LOADPO.prog, then use the commands below
cd $XXPO_TOP/bin
ln -s $FND_TOP/bin/fndcpesr $XXPO_TOP/bin/LOADPO

Question: How do you know if a specific Oracle patch has been applied in apps to your environment.
Answer: Use table ad_bugs, in which column bug_number is the patch number.
SELECT bug_number
,to_char(creation_date, 'DD-MON-YYYY HH24:MI:SS') dated
FROM apps.ad_bugs
WHERE bug_number = TRIM('&bug_number') ;

Question: How do you send a particular Oracle Apps Workflow Activity/Function within a workflow process into background mode.
Answer: If cost of the workflow activity is greater than 50, then the workflow activity will be processed in background mode only, and it won’t be processed in online mode.

Question: What are the various ways to kick-off a workflow
Answer: You can eiter use wf_engine.start_process or you can attach a runnable process such ghat it subscribes to a workflow event.

Question: When starting (kicking off) an oracle workflow process, how do you ensure that it happens in a background mode?
--a)if initiating the process using start_process, do the below
wf_engine.threshold := -1;
wf_engine.createprocess(l_itemtype
,l_itemkey
,'<YOUR PROCESS NAME>');
wf_engine.startprocess(l_itemtype, l_itemkey)--B) When initiating the workflow process through an event subscription, set the Execution Condition Phase to be equal to or above 100 for it to be executed by background process.


Question: On 10g, how will you use awr?
Answer: By running below scripts. These are both the same scripts, but with differing parameters.
$ORACLE_HOME/rdbms/admin/awrrpt.sql
$ORACLE_HOME/rdbms/admin/awrrpti.sql
Question : How will you configure Apache to run in Debug mode, specifically usefull when debugging iProcurement ( prior to 11.5.10).
Answer: After 11.5.10, FND Logging can be used for debugging Oracle iProcurement.
Prior to 11.5.10
----STEPS IN A NUTSHELL-----
cd $ORACLE_HOME/../iAS/Apache
vi $ORACLE_HOME/../iAS/Apache/Jserv/etc/ssp_init.txt
DebugOutput=/home/<<SID>>/ora9/iAS/Apache/Apache/logs/debug.log
DebugLevel=5
DebugSwitch=ON

vi $ORACLE_HOME/../iAS/Apache/Jserv/etc/jserv.conf
ApJServLogLevel debug

vi $ORACLE_HOME/../iAS/Apache/Jserv/etc/jserv.properties
log=true

Question: How will you add a new column to a List Of Values ( LOV ) in Oracle Applications Framework? Can this be done without customization?
Answer: Yes, this can be done without customization, i.e. by using OA Framework Extension coupled with Personalization. Implement the following Steps :-
a) Extend the VO ( View Object ), to implement the new SQL required to support the LOV.
b) Substitute the base VO, by using jpximport [ similar to as explained in Link ]
c) Personalize the LOV Region, by clicking on Add New Item. While adding the new Item, you will cross reference the newly added column to VO.

Question: Can you do fnd_request.submit_request from SQL Plus in Oracle?
Answer: You will need to initialize the global variables first using fnd_global.initialize
DECLARE
v_session_id INTEGER := userenv('sessionid') ;
BEGIN
fnd_global.initialize
(
SESSION_ID => v_session_id
,USER_ID => <your user id from fnd_user.user_id>
,RESP_ID => <You may use Examine from the screen PROFILE/RESP_ID>
,RESP_APPL_ID => <You may use Examine from the screen PROFILE/RESP_APPL_ID>
,SECURITY_GROUP_ID => 0
,SITE_ID => NULL
,LOGIN_ID => 3115003--Any number here
,CONC_LOGIN_ID => NULL
,PROG_APPL_ID => NULL
,CONC_PROGRAM_ID => NULL
,CONC_REQUEST_ID => NULL
,CONC_PRIORITY_REQUEST => NULL
) ;
commit ;
END ;
/Optionally you may use fnd_global.apps_initialize, which internally calls fnd_global.initialize
fnd_global.apps_initialize(user_id => :user_id,
resp_id => :resp_id,
resp_appl_id => :resp_appl_id,
security_id => :security_id,
server_id => :server_id);By doing the above, your global variables upon which Concurrent Managers depend upon will be populated. This will be equivalent to logging into Oracle Apps and submitting the concurrent request from a responsibility.

Question: You are told that the certain steps in the Oracle Apps Form/Screen are running slow, and you are asked to tune it. How do you go about it.
Answer: First thing to do is to enable trace. Preferably, enable the trace with Bind Variables. This can be done by selecting menu Help/Diagnostics/Trace/”Trace With Binds and Wait”
Internally Oracle Forms issues a statement similar to below:-
alter session set events='10046 trace name context forever, level 12' ;

Enable Trace with Bind Variables in Apps

This will enable the trace with Bind Variable values being shown in the trace file.
The screen in Oracle Apps will also provide the name of the trace file which is located in directly identified by 
select value from v$parameter where name like '%us%r%dump%'
Doing a tkprof with explain plan option, reviewing plans and stats in trace file can help identify the slow performing SQL.




Question: What is the difference between running Gather Stats and “Program – Optimizer[RGOPTM]” in Oracle General Ledger?
Answer: â€œGather Stats” will simply gather the stats against existing tables, indexes etc. However Gather Stats does not create any new indexes. But “Program – Optimizer[RGOPTM]” can create indexes on GL_CODE_COMBINATIONS, provided accounting segment has the indexed flag enabled, 


Question: You have written a piece of code in POR_CUSTOM_PKG for Oracle iProcurement, but its not taking any effect? What may be the reason?
Answer: Depending upon which procedure in POR_CUSTOM_PKG has been programmed, one or more of the below profile options must be set to Yes
POR: Enable Req Header Customization
POR: Enable Requisition Line Customization
POR: Enable Req Distribution Customization


Question: What is the key benefit of punching out to suppliers catalogs rather than loading their catalogs locally in Oracle iProcurement?
Answer: Punchout has several advantages like, Catalogs don’t need to be loaded locally saves space on your system. You can get up-to-date list of catalogs by punching out and also you get the benefit of up-to-date pricing information on vendor items.

Question: Does oracle have a test environment on exchange?
Answer: http://testexchange.oracle.com

Question: Does Oracle Grants use its own schema or does it uses Oracle Project Accounting schema?
Answer: Although Oracle Grants has its own schema i.e. GMS, it reuses many of the tables with in Oracle Projects Schema like PA_PROJECTS_ALL, PA_EXPENDITURE_ITEMS_ALL, PA_EXPENDITURE_TYPES etc.


Question: How to make an Oracle Report Type concurrent program produce an excel friendly output?
Answer: Comma can be concatenated between the column values, however a better option is to create tab delimited file, as it takes care of commas within the string.
For this, use SQL similar to below in the report
select 'a' || chr(9) || 'b' from dual;

Question: What are the settings needed for printing bitmap reports?
Answer: Get your DBA to configure two files i.e. uiprint.txt & default.ppd
For details, refer to Metalink Note 189708.1

Question: For a PL/SQL based concurrent program do you have to issue a commit at the end?
Answer: The concurrent program runs within its own new session. In APPS, the default database setting enforces a commit at the end of each session. Hence no explicit COMMIT is required.

Question: What is the best way to add debugging to the code in apps?
Answer: Use fnd_log.string , i.e. FND Logging. Behind the scenes Oracles FND Logging uses autonomous transaction to insert records in a table named fnd_log_messages.
For example
DECLARE
BEGIN
fnd_log.STRING(log_level => fnd_log.level_statement
,module => 'xxxx ' || 'pkg/procedurename '
,message => 'your debug message here');
END ;Three profile options effecting FND Logging are
FND: Debug Log Mode
FND: Debug Log Enabled
FND: Debug Log Module

Question: If you wish to trigger of an update or insert in bespoke table or take some action in response to a TCA record being created or modified, how would you do it? Will you write a database triggers on TCA Tables?
Answer: There are various pre-defined Events that are invoked from the Oracle TCA API’s.
TCA was Oracle’s first initiative towards a fully API based approach, which means the screen and the processes all use the same set of APIs for doing same task.
In order to take an action when these events occur, you can subscribe a custom PL/SQL procedure or a Custom Workflow to these events. Some of the important TCA events are listed below:-
oracle.apps.ar.hz.ContactPoint.update
oracle.apps.ar.hz.CustAccount.create
oracle.apps.ar.hz.CustAccount.update
oracle.apps.ar.hz.CustAcctSite.create
oracle.apps.ar.hz.CustAcctSite.update
oracle.apps.ar.hz.CustAcctSiteUse.create
oracle.apps.ar.hz.CustAcctSiteUse.update
oracle.apps.ar.hz.Location.create
oracle.apps.ar.hz.Location.update
oracle.apps.ar.hz.Organization.create
oracle.apps.ar.hz.Organization.update
oracle.apps.ar.hz.PartySite.create
oracle.apps.ar.hz.PartySite.update
oracle.apps.ar.hz.PartySiteUse.create
oracle.apps.ar.hz.PartySiteUse.update
oracle.apps.ar.hz.Person.create
oracle.apps.ar.hz.Person.update

Question: In Oracle OA Framework, is the MDS page/document definition stored in database or in the file system?
Answer: The MDS document details are loaded into database, in the following sets of tables.
JDR_ATTRIBUTES
JDR_ATTRIBUTES_TRANS
JDR_COMPONENTS
JDR_PATHS
The Document is loaded via XMLImporter, as detailed in XMLImporter Article

Question: In a Oracle Report data group, you have a “data link” between two queries. How do you ensure that the data link is made Outer Joined?
Answer: The data link is an Outer Join by default.

Question: How does substitution work in OA Framework?
What are the benefits of using Substitution in OA Framework?
Answer: Based on the user that has logged into OA Framework, MDS defines the context of the logged in user. Based upon this logged in context, all applicable personalization are applied by MDS. Given that substitutions are loaded as site level personalizations, MDS applies the substituted BC4J objects along with the personalizations. The above listed steps occur as soon as Root Application module has been loaded.
The benefit of using Substitution is to extend the OA Framework without customization of the underlying code. This is of great help during Upgrades. Entity Objects and Validation Objects can be substituted. I think Root AM’s can’t be substituted given that substitution kicks off after Root AM gets loaded.

Question: In OA Framework, once your application has been extended by substitutions, is it possible to revert back to remove those substitutions?
Answer: yes, by setting profile option “Disable Self-Service Personal%” to Yes, keeping in mind that all your personalizations will get disabled by this profile option. This profile is also very useful when debugging your OA Framework based application in the event of some error. By disabling the personalization via profile, you can isolate the error, i.e. is being caused by your extension/substitution code or by Oracle’s standard functionality.

Question: How can you import invoices into Oracle Receivables?
Answer: You can either use AutoInvoice by populating tables RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL & RA_INTERFACE_SALESCREDITS_ALL.
Alternately you may decide to use API ar_invoice_api_pub.create_single_invoice for Receivables Invoice Import.

Question: How do you setup a context sensitive flexfield
Answer: Note: I will publish a white paper to sho step by step approach.
But for the purpose of your interview, a brief explanation is…a)Create a reference field, b) Use that reference field in “Context Field” section of DFF Segment screen c) For each possible value of the context field, you will need to create one record in section “Context Field Value” ( beneath the global data elements).

Question: Does Oracle iProcurement use same tables as Oracle Purchasing?
Answer: Yes, iProcurement uses the same set of requisition tables as are used by Core Purchasing.

Question: What is the name of the schema for tables in tca
Answer: AR (at least till 11.5.10, not sure about 11.5.10).

Question: Are suppliers a part of TCA?
Answer: Unfortunately not yet. However, Release 12 will be merging Suppliers into TCA.

Question: What is the link between order management and purchasing
Answer: Internal Requisitions get translated into Internal Sales Orders.

Question: How would you know if the purchase order XML has been transmitted to vendor, looking at the tables.
Answer: The XML delivery status can be found from a table named ecx_oxta_logmsg. Use the query below
SELECT edoc.document_number
,decode(eol.result_code, 1000, 'Success', 'Failure') AS status
,eol.result_text
FROM ecx_oxta_logmsg eol
,ecx_doclogs edoc
,ecx_outbound_logs eog
WHERE edoc.msgid = eol.sender_message_id
AND eog.out_msgid = edoc.msgid
ORDER BY edoc.document_number

Question: You have done forms personalization, now how will you move it from one environment to another?
Answer: Use FNDLOAD. For examples visit FNDLOAD Article


Question: What are the key benefits of forms personalization over custom.pll?
Answer: 
-->Multiple users can develop forms personalization at any given point in time.
-->It is fairly easy to enable and disable forms personalizations.
-->A programmer is not required to do simple things such as hide/disable fields or buttons.
-->Provides more visibility on customizations to the screen.

Question: Tell me some limitations of forms personalization when compared to CUSTOM.pll?
Answer: 
-->Can't create record group queries, hence can’t implement LOV Query changes.
-->Can't make things interactive, i.e. can’t have a message box that gives multiple choices for example Proceed or Stop etc.

Question: Give me one example where apps uses partitioning?
Answer: WF_LOCAL_ROLES


Question: Give me one example of securing attributes in iProcurement.
Answer: You can define Realm to bundle suppliers into a Category. Such realm can then be assigned to the User using Define User Screen. Security Attribute ICX_POR_REALM_ID can be used. By doing so, the user will only be made visible those Punchout suppliers that belong to the realm against their securing attributes.


Question: Can you send blob attachments via workflow notifications?
Answer: Yes, you can send BLOB Attachments.

Download this document : Click Here

Read More »