Integrated Procurement - Repository Contracts Solution

The Evolving Contract and Procurement Processes

With rapidly growing Global Footprint of Organizations and increasing interactions between their Subsidiaries and Trading Partners on multiple operational fronts; there is an increasing need of streamlining the traditional contract processes and Come up with better coordination between Legal and Procurement teams for improved Contract Management and Contract reference on purchasing documents.

Sample Business Scenario:  An Organization may have a Supplier who sells materials and also provides Services; Same Supplier may also be a Customer as well.

More and More Organizations are trying to streamline their contract processes; While evolving the contract processes there is also need to store and maintain old Contracts references and Terms with advance search capabilities.

Why Need a Comprehensive Procurement / Repository Contract Solution

Most Businesses have manual and Isolated Process of managing Contract and correlating the Master Agreements and Child Documents and managing appropriate Terms and Conditions which resulted in below Challenges.

Challenges faced by Legal and Supply chain teams

·        Inability to create/Manage Link between Master Agreement and Child Purchasing Documents, Hence lot of communication time spend between, Buyers (Procurement) and Contracts (Legal)  teams.

·        Difference in Terms and Condition between Master Agreement and Child Purchase documents, Potential Legal Challenge.

·        No standard terms and Conditions template, multiple version of Draft template causing confusion.

·        Manual Process of updating terms and condition on Stand Alone Purchasing Documents, Resulting in reconciliation issues, and lack of transparency for legal team.

·        Time consuming means of Master Agreement review for Buyers.

·        Inability to maintain relationships between Contracts.

·        No effective Master Agreements Search mechanism, resulting in Time Consuming, ineffective Search of Contracts.

Solution Approach:

The Integrated Procurement / Repository Contract solution will leverage the Oracle Purchasing, Procurement Contracts and Repository contract Modules of Oracle application as the Core of Solution, extensions will be developed to achieve additional requirements and meet the GAPs.

Procurement and Repository Contracts Integration - Benefits:

·        Ability to create and manage the Contracts within Oracle and maintain a Link with Content Management tool (Eg: File Net, Share point Etc.).

·        Ability to reference the Master Contracts on Child Purchasing Documents.

·        Ability for the Buyers to View the Contracts in Read only mode, for better understanding while creating Purchase order.

·        Accurate Visibility to Historical Amendments, Notes and reference of a Contract and Improved Version management.

·        Ability to Capture and manage Important Contractual / Legal milestones with Effective Notification / Reminder system

·        Effective Expiry and Approval notification mechanism.

·        Manage Relation Ship / hierarchies/ Cross references within different Contracts.

·        Better and Accurate Reporting and Dashboard for Existing Contracts.

·        Ability to manage Internal and Supplier Contacts, relevant to Contract.

·        Ability to Upload Historical Contracts in the System using an automated and Easy to use Import mechanism.

·        Effective Management of Contract Templates, for Initiating Draft Contracts and potential Redlining capabilities.

·        Robust Access Control.

·        Effective Keyword/ Advance Search capabilities for contracts. Big time saving for respective teams.

·        Capability to Tag and Monitor High Risks Contracts.

·        Ability to Upload Historical Contracts in the System using an automated and Easy to use Import mechanism.

·        Effective Management of Contract Templates, for Initiating Draft Contracts and potential Redlining capabilities.

·        Contracts Authoring and Clause Management.

·        Capability to Automate Contract Routing procedures in Approval workflow in Future.

·        Ability to Author Contract with in oracle, and Modify the standard / Non Standard Clauses.

·        XML Publisher reports, for Contracts and all relevant Child documents.

R12 Accounts Payable Useful Queries

Get Invoice Details

select aia.invoice_id, aia.invoice_num,gcc.segment1,gcc.concatenated_segments,aia.creation_date, aia.invoice_date,aia.pay_group_lookup_code,aia.source,aia.*
aia.org_id,hou.name,aia.source, to_char(aia.creation_date, 'MM-YYYY'),aia.payment_method_code,aia.invoice_currency_code,aia.pay_group_lookup_code,aia.invoice_num,gcc.segment1,gcc.concatenated_segments
from
apps.ap_invoices_all aia,
apps.ap_invoice_lines_all ail,
apps.ap_invoice_distributions_all aid,
apps.gl_code_combinations_kfv gcc
where
aia.invoice_id = ail.invoice_id
and aia.invoice_id = aid.invoice_id
and ail.line_number = aid.invoice_line_number
and aia.org_id = <ENTER ORG ID>
and aid.dist_code_combination_id = gcc.code_combination_id
--and gcc.segment1 = '<ENTER SEGMENT 1>'
--and aia.source = '<ENTER SOURCE>'
and aia.cancelled_date is null
and aia.payment_status_flag = 'Y'
and aia.invoice_num in ('<ENTER INVOIC NUMBER>')
and aia.creation_date >trunc(sysdate-10) -- For invoices created in some time in past

;




Get the Invoice and Payment Details (For Paid Invoices)

select
aia.org_id,aia.invoice_id,aia.vendor_id,aca.check_number, aia.invoice_num,aia.invoice_amount,aia.amount_paid,aia.discount_amount_taken, aca.amount "payment_amount",gcc.concatenated_segments,aia.creation_date, aia.invoice_date, aia.payment_method_code, aba.bank_account_name,aca.check_id
from
apps.ap_invoices_all aia,
apps.ap_invoice_lines_all ail,
apps.ap_invoice_distributions_all aid,
apps.gl_code_combinations_kfv gcc,
apps.gl_code_combinations_kfv gcc1,
apps.ap_invoice_payments_all aip,
apps.ap_checks_all aca
,apps.ce_bank_accounts aba
,apps.CE_BANK_ACCT_USES_ALL abu
,apps.hr_operating_units hou

where
aia.invoice_id = ail.invoice_id
and aia.invoice_id = aid.invoice_id
and ail.line_number = aid.invoice_line_number
and aip.invoice_id = aia.invoice_id
and aip.check_id = aca.check_id
and aid.dist_code_combination_id = gcc1.code_combination_id
and aca.CE_BANK_ACCT_USE_ID = abu.BANK_ACCT_USE_ID
and abu.bank_account_id = aba.bank_account_id
and aia.org_id = hou.organization_id
and aia.org_id = <ORG_ID>
and aba.asset_code_combination_id = gcc.code_combination_id
and aia.creation_date >trunc(sysdate-90)
--and gcc.segment1 = <COMPANY>

--and aia.invoice_currency_code = <CURR>
--and aia.payment_method_code = <PAYMENT METHOD>
--and aia.vendor_id in (<ENTER VENDOR IDs>)
and aia.cancelled_date is null
and aia.payment_status_flag = 'Y'
order by aia.org_id, to_char(aia.creation_date, 'MM-YYYY')
;

To Find Invoices on Hold

select aha.* from apps.ap_invoices_all aia,
apps.ap_holds_all aha
where
aia.invoice_id = aha.invoice_id
and aia.org_id = <Enter ORG ID>
and aha.hold_lookup_code = <ENTER HOLD CODE> -- Holds Codes can be found in table : AP_HOLD_CODES
--and aia.VENDOR_SITE_ID in (<ENTER VENDOR SITE ID>)
;

Holds Codes can be found in table: AP_HOLD_CODES

Query to Get Bank account details

select cba.bank_account_type,cbb.bank_name,cba.*
from
apps.ce_bank_accounts cba,
apps.cefv_bank_branches cbb,
apps.CE_BANK_ACCT_USES_ALL cbu
where
cba.bank_branch_id = cbb.bank_branch_id
and cba.bank_account_id = cbu.bank_account_id
AND ( cba.end_date IS NULL OR cba.end_date > TRUNC(SYSDATE) )
;

Bank account and payment template analysis

SELECT xep.name                                       legal_entity_name, 

       ou.name                                        "Operating Unit", 

       cba.bank_account_name                          "Bank Account Name", 

       cba.bank_account_num                           "Bank Account Number", 

       NVL(cba.attribute1, cba.currency_code)         currency, 

       cba.multi_currency_allowed_flag                "Multi Currency Flag", 

       cba.account_classification                     "Account Classification", 

       bb.bank_name                                   "Bank Name", 

       bb.bank_branch_type                            "Bank Branch Type", 

       bb.bank_branch_name                            "Bank Branch Name", 

       bb.bank_branch_number                          "Bank Branch Number", 

       bb.eft_swift_code                              "Swift Code", 

       bau.ap_use_enable_flag                         bank_setup_ap_use_flag, 

       bau.ar_use_enable_flag                         bank_setup_ar_use_flag, 

       cba.iban_number,

       gcf.concatenated_segments       "ASSET GL Code Combination", 

       (SELECT RTRIM(XMLAGG(XMLELEMENT(e, apg.vendor_pay_group|| 

                     ',')) .extract('//text()'), 

                       ',') 

        FROM   (SELECT DISTINCT apg.vendor_pay_group, 

                                bank_account_id 

                FROM   apps.ap_payment_templates apt, 

                       apps.ap_pay_group apg 

                WHERE  1 = 1 

                   AND apg.template_id = apt.template_id 

                   AND NVL(apt.inactive_date, SYSDATE + 1) > SYSDATE) apg 

        WHERE  bank_account_id = cba.bank_account_id) used_in_ap_pay_groups, 

       (SELECT RTRIM(XMLAGG(XMLELEMENT(e, arm.name|| CHR(10)|| 

                     ',')) .extract('//text()'), ',') 

        FROM   apps.ar_receipt_method_accounts_all arma, 

               apps.ar_receipt_methods arm 

        WHERE  arm.receipt_method_id = arma.receipt_method_id 

           AND arma.remit_bank_acct_use_id = bau.bank_acct_use_id) 

                                                      used_in_ar_recipt_mthods 

FROM   apps.ce_bank_accounts cba, 

       apps.ce_bank_acct_uses_all bau, 

       apps.cefv_bank_branches bb, 

       apps.hr_operating_units ou, 

       apps.xle_entity_profiles xep, 

       apps.gl_code_combinations_kfv gcf 

WHERE  cba.bank_account_id = bau.bank_account_id 

   AND cba.bank_branch_id = bb.bank_branch_id 

   AND ou.organization_id = bau.org_id 

   AND cba.asset_code_combination_id = gcf.code_combination_id 

   AND ( cba.end_date IS NULL 

          OR cba.end_date > TRUNC(SYSDATE) ) 

   AND ou.default_legal_context_id = xep.legal_entity_id 

ORDER  BY ( cba.bank_account_num ); 

LINK PAYMENT AND INTERNAL BANK ACCOUNT

select 

aisc.checkrun_name,

apt.template_name,

apt.payment_method_code,

app.system_profile_code,

--api.payment_currency_code,

cb.bank_name,

cba.bank_account_name,

cba.currency_code,

cba.multi_currency_allowed_flag,

cba.payment_multi_currency_flag,

cba.zero_amount_allowed,

from

APPS.AP_INV_SELECTION_CRITERIA_all aisc,

apps.ap_checks_all aca,

apps.ap_payment_templates apt,

apps.IBY_ACCT_PMT_PROFILES_B app,

apps.ce_bank_accounts cba,

apps.cefv_bank_branches cb

where 1=1

and aca.checkrun_id= aisc.checkrun_id

and aisc.template_id = apt.template_id

and apt.payment_profile_id = app.payment_profile_id

--and app.payment_profile_id = api.payment_profile_id

and aisc.bank_account_id = cba.bank_account_id

and cba.bank_branch_id = cb.bank_branch_id

--and aisc.org_id = api.org_id

--and app.system_profile_code = <PAYMENT PROCESS PROFILE>

and aca.check_id in (<ENTER CHECK ID>)

Project Accounting Basics

Project Accounting Basics

Project: A project is a primary unit of work that can be broken down into one or more tasks.

Task: It is the small unit of work created under the project against which transaction can be created.

Note: we cannot create transaction/expenditure items directly on the project, hence it is mandatory for each project to have at least one task.

 Project Classes:

Oracle PA has provided three pre defined project classes.

• Indirect projects – Track overhead activities and costs of the organization. Note: Does not generate revenue and invoicing process.
• Capital projects – Track asset development activities and costs, and costs are capitalized as one or more assets.
• Contract projects – Contract projects created with external customer to track cost, revenue, and billing.

 Project Type:

The project type controls how Oracle Project Foundation creates and processes projects, and is a primary classification for the projects your business manages. You must set up at least one project type to create projects. You must set up project types for each operating unit.

•  Examples of Indirect project: Admin Project, Transport Facility etc.
• Example of Contract project: External Project, Intercompany project etc.

Expenditure category: It is the category created to classify the different types of cost an organization may incur.

Revenue category: It is the category created to classify the different types of revenue an organization may earn.

Expenditure type:It is the identification of cost that is associated to an expenditure item.It is assigned to the expenditure category and revenue category during the setups.

Expenditure class: It is the classification for an expenditure type as how the expenditure type can be used to create transaction. When an expenditure type is classified for a certain type then only those expenditure class transactions are allowed to enter.

 In Oracle Project Costing we have predefined expenditure classes.

 Labor:

Straight Time: For standard time entry. We use the cost rate multiple with number of hours.

Overtime: Labor cost calculated using a premium cost rate multiplied by hours.

 Non-labor project costs:

Expense Reports – Expense reports from Oracle Payables or Oracle Internet Expenses. You cannot enter expense reports directly into Oracle Project Costing. Expense reports that you import into Oracle Project Costing must be fully accounted prior to import.

Usages – You must specify the non-labor resource for every usage item you charge to a project. For each expenditure type classified by a Usage expenditure type class, you also define non-labor resources and organizations that own each non-labor resource.

Supplier Invoices – Supplier invoices, discounts, and payments from Oracle Payables or an external system, and receipt accruals from Oracle Purchasing.

Miscellaneous Transaction – Miscellaneous Transactions are used to track miscellaneous project costs. Examples of uses for miscellaneous transactions are:

• Fixed assets depreciation
• Allocations
• Interest charges

Burden Transaction – Burden transactions track burden costs that are calculated in an external system or calculated by Oracle Project Costing as separate, summarized transactions. These costs are created as a separate expenditure item that has a burdened cost amount, but has a quantity and raw cost value of zero. You can adjust burden transactions that are not system-generated.

Work In Process – You use this expenditure type class for Oracle Project Manufacturing WIP transactions that you interface from Manufacturing to Oracle Project Costing. You can also use this expenditure type class when you import other manufacturing costs via Transaction Import or when you enter transactions via pre-approved batch entry.

 Inventory – This expenditure type class is used for the following transactions:

• Oracle Project Manufacturing transactions that you import from Manufacturing or Oracle Inventory.
• Oracle Inventory Issues and Receipts that you import from Oracle Inventory in a manufacturing or non-manufacturing installation.

 Example:

Sr No	Expenditure Category	Revenue category	Expenditure type	Expenditure Class
1	Labor Time	Labor Revenue	Standard_Time	Straight Time
2	Vendor Cost	Service Revenue	Supplier_cost	Supplier Invoices
3	Travel Cost	Travel Revenue	Travel Cost	Expense Report & Supplier Invoices
4	Misc Cost	Misc Revenue	Misc_Transactions	Miscellaneous and usages

In the above examples for line number 1, time can be entered for expenditure type “Standard_Time”.

Similarly for line number 2 & 4 the respective expenditure class transactions can be entered.

For line number 3 supplier invoices & expense reports can be entered as it has been classified for two different classes.

Types of Currencies

Transaction amounts are stored in the following currencies:

• Transaction Currency:The currency in which a project transaction occurs.
•  Expenditure Functional Currency: The functional currency of the expenditure operating unit.
• Project Functional Currency: The functional currency of the operating unit that owns the project.
• Project Currency: The user–defined project currency.

When you enter transactions in a currency that is different from functional currency or project currency, Oracle Project Costing must convert the transaction amount to the functional and project currencies. To convert transaction currencies, Oracle Project Costing must first determine the exchange rate type and exchange rate date.

 Task Type:

• Billable Item: It is a functionality associated at the task level identifying the expenditure items created against the task to be billed to the client.
• Non Billable Item: It is functionality associated at the task level identifying the expenditure items created on the task is only to record the cost incurred against it.

Note: We can adjust the task at the expenditure item window to make the non billable as billable and vice versa. This is called as expenditure item adjustments.

Employee/Job bill rate Override: In case a different rate needs to be updated for a particular job or employee the same can be updated at the project or task level.

The employee name needs to be entered along with the bill rate, currency and period applicable. The rate at the task level overwrites the rates at the project level and the rates at the project level overwrites the rate of the bill rate schedule attached at the project level.

Cost rate: It is the rate defined at the job or employee level to derive at the basic cost for a particular expenditure item. The cost rate scheduled are defined in the OU functional currency.

The cost rate can be defined at two levels:

• In the setups -> Expenditure -> Rate schedule.
• Overrides: Labor Costing Overrides

( Navigation: PA Responsibility->Setup->Costing->Labor->Labor Costing Overrides.)

Bill rate: It is the rate defined at the job or employee level to derive at an amount which will be billed to the client.

The bill rate can be defined at two levels:

1. In the setups -> Expenditure -> Rate schedule.
2. Overrides: At the project level as employee or job override.

•  For employee override: Navigation: Project -> Options -> – Bill Rates and Discount Overrides -> Employee Bill Rate and Discount Overrides
•  For job override: Navigation: Project -> Options ->   Bill Rates and Discount Overrides -> Job Bill Rate and Discount Overrides

Transfer Price Rules and Schedule: Transfer price rules control the calculation of transfer prices for labor and non-labor cross charged transactions. To drive transfer price calculation for cross charge transactions between the provider and receiver, use the Transfer Price Schedule window to assign labor or non-labor (or both) transfer price rules to the provider and receiver pair on a schedule line

ICB: It is a process, where we have different legal entities involved, and the OUs under these LE owns the employees and projects.

• The OU to which the employee is assigned is called PROVIDER OU.
• The OU to which owns the project is called RECEIVER OU.

 Note: In ICB process, an internal invoice is generated in provider OU.

Accounts Receivables Accounting Flow

Accounts Receivables Accounting Flow

Receivables creates default accounts for revenue, receivable, freight, tax, unearned revenue, unbilled receivable, finance charges, and AutoInvoice clearing (suspense) accounts using the information specified in your AutoAccounting structure.

AR Transactions

When a regular AR invoice is entered, Receivables creates the following journal entry:

        DR Receivables

                       CR Revenue

                       CR Tax (if you charge tax)

                       CR Freight (if you charge freight)

AR Receipt

When cash is received, Receivables creates the following journal entry:

        DR Cash

                       CR Receivables

Other Scenarios for Accounts Receivables Accounting Are

Bill in Arrears

If you enter an invoice with a Bill in Arrears invoicing rule, Receivables creates the following journal entry:

In the first period of Rule:

        DR Unbilled Receivables

                       CR Revenue

In all periods of Rule, for the portion that is recognized:

        DR Receivables

                       CR Unbilled Receivables

                       CR Tax (if you charge tax)

                       CR Freight (if you charge freight)

Bill in Advance

If you enter an invoice with a Bill in Advance invoicing rule, Receivables creates the following journal entries.

In the first period of the rule:

        DR Receivables

                       CR Unearned Revenue

                       CR Tax (if you charge tax)

                       CR Freight (if you charge freight)

In all periods of the rule for the portion that is recognized.

        DR Unearned Revenue

                       CR Revenue

Accounts Receivables Credit Memos

When you credit an invoice, debit memo, or chargeback, Receivables creates the following journal entry:

        DR Revenue

        DR Tax (if you credit tax)

        DR Freight (if you credit freight)

                       CR Receivables (Credit Memo)

        DR Receivables (Credit Memo)

                       CR Receivables (Invoice)

When you credit a commitment, Receivables creates the following journal entries:

        DR Revenue

                       CR Receivables

Commitments

When you enter a deposit, Receivables creates the following journal entry:

        DR Receivables (Deposit)

                       CR Unearned Revenue

When you enter an invoice against this deposit, Receivables creates the following journal entries:

        DR Receivables (Invoice)

                       CR Revenue

                       CR Tax (if you charge tax)

                       CR Freight (if you charge freight)

        DR Unearned Revenue

                       CR Receivables (Invoice)

When you apply an invoice to a deposit, Receivables creates a receivable adjustment against the invoice. Receivables use the account information you specified in your AutoAccounting structure to create these entries.

When cash is received against this deposit, Receivables creates the following journal entry:

        DR Cash

                       CR Receivables (Deposit)

Receipts

When you enter a receipt and fully apply this receipt to an invoice, Receivables creates the following journal entry:

        DR Cash

                       CR Receivables

When you enter an unapplied receipt, Receivables creates the following journal entry:

        DR Cash

                       CR Unapplied

When you enter an unidentified receipt, Receivables creates the following journal entry:

        DR Cash

                       CR Unidentified

When you enter an on-account receipt, Receivables creates the following journal entry:

        DR Cash

                       CR On-Account

When your receipt includes a discount, Receivables creates the following journal entry:

        DR Receivables

                       CR Revenue

        DR Cash

                       CR Receivables

        DR Earned/Unearned Discount

                       CR Receivables

Receivables uses the default Cash, Unapplied, Unidentified, On-Account, Unearned, and Earned accounts that you specified in the Remittance Banks window for this receipt class.

When you enter a receipt and combine it with an on-account credit (which increases the balance of the receipt), Receivables creates the following journal entry:

        DR Cash

                       CR Unapplied Cash

To close the receivable on the credit memo and increase the unapplied cash balance, Receivables creates the following journal entry:

        DR Receivables

                       CR Unapplied Cash

When you enter a receipt and combine it with a negative adjustment, Receivables creates the following journal entries:

        DR Cash

                       CR Receivables (Invoice)

        DR Write-Off

                       CR Receivables (Invoice)

Set up a Write-Off account when defining your Receivables Activity.

When you enter a receipt and combine it with a positive adjustment, Receivables creates the following journal entries:

        DR Cash

                       CR Receivables (Invoice)

        DR Receivables (Invoice)

                       CR Write-Off

When you enter a receipt and combine it with a Chargeback, Receivables creates the following journal entries:

        DR Cash

                       CR Receivables (Invoice)

        DR Receivables (Chargeback)

                       CR Receivables (Invoice)

        DR Chargeback

                       CR Receivables (Chargeback)

Set up a Chargeback account when defining your Receivables Activity.