Tuesday, May 11, 2021

Is it possible in Oracle Property Manager to record Rent Prepayments for Payments and Billings (AP/AR) without finalize the lease?

  • Yes” this is possible in R12 Oracle Property Manager for both Expense and Revenue Leases.
  • Below process will assist you in using the prepayments Rent/Billing functionality in Oracle Release R12. It will take you through the process of creating prepayments for expense leases and testing purposes.

How to create Prepayment Invoice through Oracle Property Manager before finalize the lease

Prerequisites:

  • Lease status should be Lease Ordered (LOF) or Signed (SGN)
  • Frequency should be “One Time”
  • Billing/Payment terms should be “Non-Normalized”

Go to Property Manager Responsibility.

Leases and Documents  à Main Lease à Enter Leases and Documents.

Step1: Enter Lease

Click on “New” button.

Enter required details for lease creation as shown in below screen shot.

  • Make sure lease status should be “Lease Ordered” or “Signed” 
  • This is mandatory for prepayment creation otherwise system will not create the prepayment schedule and items.

Step 2: Enter Lease Details


Step 3: Enter Location Information

Step 4: Enter Payment Details

Step 5: Enter Prepayment Term (Line):

Here you can attach Prepayment Template if you created otherwise enter prepayment line manually.

  • Payment term type should be “Prepayment”, if you want to select “Deposit” or other system will create schedule for that line also but that line should be no-normalized with once time frequency. 
  • Target date is mandatory for "prepayment" line but this date should not be before or after lease key dates as soon as you save the lease property manager will creates schedules & items for all onetime non-normalized terms. 


Save the lease. System will popups small window as shown below in screen shot click “Yes”


After clicking on “Yes” button system will generate schedules and items for prepayment line.

Click “OK” button and check the output.


Step 6: View the Schedules and Item Output Report

ViewàRequestsàFind and see Prepayment schedules & items output report.

  • Further you can check the accounting and invoice so do “authorization” and export this schedule to AP and then import in AP through “Oracle Payables Interfaces”.
  • Once contract signed and date are confirmed then retrieve the lease and change the status from (Lease Ordered/Signed) to “ACTIVE” and approval status as “Final” Oracle Property Manager runs the Schedules and Items concurrent program and creates billing/payment schedules based on given frequency. 

Monday, May 10, 2021

Query to get GL Balances

 SELECT bal.code_combination_id code_combination_id

      ,segment1 fund

  ,segment2 org

  ,segment3 project

  ,segment4 account

  ,segment5 location

  ,DECODE( account_type, 'A','Asset' 

   ,'C','Budgetary (CR)'

   ,'D','Budgetary (DR)'

   ,'E','Expense'

   ,'L','Liability'

   ,'O','Owners equity'

   ,'R','Revenue'

   ,account_type) account_type

  ,period_name period_abbr

  ,LAST_DAY( TO_DATE( period_name, 'MON-RR')) period_date

  ,DECODE( account_type, 'R', -1, 

   DECODE( segment4, '1000', -1, 

   DECODE( segment4, '2000', -1,  

   DECODE( segment4, '3000', -1,  

   DECODE( segment4, '4000', -1,  

+1))))) xyz

  ,SUM( DECODE( actual_flag, 'B', 

NVL( period_net_dr, 0) - NVL( period_net_cr, 0), 

0)) period_net_budget_bal 

  ,SUM( DECODE( actual_flag, 'A', 

NVL( period_net_dr, 0) - NVL( period_net_cr, 0), 

0)) PERIOD_NET_ACTUAL_BALANCE 

  ,SUM( DECODE( actual_flag, 'E', DECODE( encumbrance_type, 'Invoice', 

NVL( period_net_dr, 0) - NVL( period_net_cr, 0), 

0), 0)) period_net_invoice_encumb

  ,SUM( DECODE( actual_flag, 'A', 

NVL( begin_balance_dr, 0) + NVL( period_net_dr, 0) 

  - NVL( begin_balance_cr, 0) - NVL( period_net_cr, 0), 

0)) current_gl_balance 

  ,SUM( DECODE( actual_flag, 'E', DECODE( encumbrance_type, 'Invoice', 

nvl( begin_balance_dr, 0) + nvl( period_net_dr, 0) 

  - nvl( begin_balance_cr, 0) - nvl( period_net_cr, 0), 

0), 0)) invoice_encumbrance 

  ,period_year

  FROM gl_balances bal

      ,gl_encumbrance_types get

  ,gl_code_combinations gcc 

 WHERE bal.encumbrance_type_id = get.encumbrance_type_id(+) 

   AND bal.code_combination_id = gcc.code_combination_id 

GROUP BY bal.code_combination_id

        ,segment1

,segment2

,segment3

,segment4

,segment5

,account_type

,period_name

,period_year

;

Sunday, May 9, 2021

Query to get Termination Details in Oracle HRMS

 SELECT papf.person_number

      ,ppnf.full_name

  ,to_char(ppos.actual_termination_date, 'dd-mon-yyyy','NLS_DATE_LANGUAGE=ENGLISH') termination_date

  ,part.action_reason  termination_reason 

  FROM per_all_people_f papf

      ,per_person_names_f ppnf

  ,per_periods_of_service_v ppos

  ,per_action_reasons_tl part

  ,per_action_occurrences pao

 WHERE papf.person_id =ppnf.person_id 

   AND papf.person_id =ppos.person_id  

   AND UPPER(ppnf.name_type)='GLOBAL' 

   AND TO_CHAR(ppos.actual_termination_date,'dd-mm-yyyy')<TO_CHAR(SYSDATE,'dd-mm-yyyy') 

   AND ppos.action_occurrence_id = pao.action_occurrence_id 

   AND pao.action_reason_id = part.action_reason_id

   AND part.language = USERENV('LANG')

   AND TRUNC(SYSDATE) BETWEEN TRUNC(papf.effective_start_date) AND TRUNC(papf.effective_end_date)

   AND TRUNC(SYSDATE) BETWEEN TRUNC(ppnf.effective_start_date) AND TRUNC(ppnf.effective_end_date)

Friday, May 7, 2021

12.2.5 Features in Oracle Cash Management

  • CAMT.053 - Bank to Customer Statement Support
    • The CAMT.053 Bank to Customer Statement messaging is used to inform account owners of the entries booked in their bank account and to provide account owners with balance information. 
    • Additionally, the CAMT.053 Bank to customer Statement provides reporting of the post settlement returns and reversals of the direct debit transactions. 
    • Oracle Cash Management contains the following features for supporting the use of CAMT.053 Bank to Customer Statement messaging:
      • Loading and processing of the CAMT.053 bank statement format
      • Matching and automatic reconciliation of SEPA direct debit transactions Oracle E-Business Suite Releases 12.1 and 12.2 Release Content Document Oracle Credit Management 10 
      • Automatic reconciliation of bank statements with direct debit reversals and returns.

12.2.9 Functional Changes in Oracle Property Manager

  • Compliance changes as per IFRS 16 and ASC 842 (US GAAP) 
    • Account for Right of Use (ROU) and Lease liability 
    • Manage amendments
    • Manage options
    • Period end accrual accounting 
    • Upload balances on transition date
    • Calculation of gain and loss

12.2.9 Features in Oracle Property Manager

Improve Flexibility for Transaction Processing:

  • Customers need to create the right-of-use assets (ROU) and lease liability on the balance sheet when the contract is created and ensure the balances are removed when the contract is terminated to ensure adherence to IFRS 16 and ASC 842.
Streamlined Amendments:
  • Support for multiple amendments within the same period. 
  • Categorizing amendments by the type of change (ex: rate change, reclassification, term change, etc.). 
  • Amendments for re-classification, rate change, contractions, and extensions using the same amount for the ROU and Lease Liability, which is in line with IFRS 16 and ASC 842. Oracle E-Business Suite Releases 12.1 and 12.2 Release Content Document Oracle Property Manager 93 
  • ROU and lease liability balances are carried forward from previous terms by linking the new terms added during an amendment.
  • All the details of the amendment are captured in an audit table to provide visibility and transparency of contract changes and their financial impact.

Additions to Disclosure Reports

Amortization Improvements: 

  • A new interest calculation method has been added. The new method of Daily Compounding Interest has been added to both daily and periodical amortizations.

Equipment Enhancements: 

  • New functionality has been added to improve the operational efficiency of handling equipment leases. This includes: 
    • Attachments: Similar to the property solution, attachments are now supported for equipment. This allows users to capture documents related to the contract, such as copies of the contract, schedules, asset information, etc. 
    • Workflow: Also in line with the property solution, an approval workflow is initiated when activating a lease contract.
    • Asset Auto-Numbers: A lease can have many assets included. As assets are created on the contract, they can be automatically numbered to save time during contract creation.

View Leasing related Accounts Payable Transactions: Users are able to view Accounts Payable invoices initiated by Property Manager from the view transactions options in Property Manager.

The Oracle Lease Contracts Command Center feature is available at no additional cost to licensed users of Oracle Financials or Oracle Property Manager. It can be applied to Release 12.2.4 and above.

Lease Contracts Dashboard functionality requires Oracle Enterprise Command Center Framework Release 12.2 V4.



12.2.8 Features in Oracle Property Manager

  • Support for ASC 842 and IFRS 16 for Property and Equipment

12.2 Features in Oracle Property Manager

  •  Override Variable Rent Calculations Client Extensions

12.2.4 Features in Oracle Property Manager

  • 12.2.4 release provides a new integration which brings the organizational hierarchy defined in Oracle Property Manager into Oracle Environmental Accounting & Reporting.
  • Data entities for Region, Property, Building, Floor, and Office are imported from Oracle Property Manager along with their implicit hierarchy to create a new default hierarchy in the Environmental Accounting & Reporting module. 
  • This hierarchy is called the "GHG Property Manager Organization Hierarchy".


12.1.3+ Features in Oracle Property Manager

  • Lease and Terms APIs programmatically convert legacy system leases and update existing leases details and payment terms into Oracle Property Manager.
  • Variable Rent and Breakpoint APIs
  • Index Rent APIs

12.2.9 Features in Oracle Assets

  • Export Functionality 
  • DFF support

Note: Above Functionalities requires Oracle Enterprise Command Center Framework (ECC) Release 12.2 V4.

12.2.8+ Features in Oracle Assets

  • Enhancements to Asset Reconciliation Dashboard
  • Enhancement to Asset Cost Dashboard
  • Enhancement to Asset Location Dashboard
  • Grouping of Attributes in Attribute Groups

12.2.8 Features in Oracle Assets

  •  New Depreciation and Amortization BI Publisher Report to Support Form 4562 Filing to US IRS
  • Oracle Assets Command Center:
    • Asset Reconciliation Dashboard
    • Asset Cost Dashboard
    • Asset Aging Dashboard
    • Asset Location Dashboard
    • Mass Transactions Dashboard
    • Additions Pipeline Dashboard

12.2.9 Functional Impact of Projects Costing

 Transaction fund check for bottom up integration enabled projects

  • Asset line generation using capital events
  • Transaction exception details
  • Borrowed and lent amounts processing when override schedule exists for costing and invoice/revenue
  • Invoice workbench
  • Contingent worker time entry
  • Revenue distributed flag maintenance for capital projects 
  • IPV transactions for inventory based PO
  • Capitalization workbench
  • Project Capitalization Summary

12.2.9 features in Oracle Projects Costing

  • Asset lines generation when capital event name more than 30 characters: The concurrent program ‘PRC: Generate Asset Lines for a Single Project’ would now generate asset lines successfully when capital event name is more than 30 characters.
  • Transaction exception details report to display full task name when task name contains more than 15 characters
  • Borrowed and lent process to process amounts correctly when burden schedule overrides exist
  • Transaction currency derivation for contingent worker time cards

  • Revenue Distribution Flag for Capital Projects: When burden multiplier is modified for a capital project that uses enhanced burdening and capitalization on burden cost setup, the revenue distribution flag would not be updated on expenditure items.

  • New Transaction Source introduced to handle Invoice Price Variance (IPV) for inventory based PO : New transaction source ‘PJM_CSTBP_WIP_GL_ACCOUNTS’ has been introduced to interface IPV lines for inventory based PO, directly from Oracle Inventory to Oracle Projects.
  • Expenditures with Unit of Measure (UOM) as currency in Asset Line Details form : The Asset Line Details form would now show all the asset lines details, if the asset line generated from the expenditure item has currency as UOM.
  • Supplier Invoices with Closed Projects Status: User would now be able to query the project related supplier invoices in Invoice Workbench form even though the project not in active status.
  • Budgeted Cost in Task Summary UI: Budgeted cost amount would now show correct amount for the task even though no actual cost exists for that task.


12.2.9 Features in Oracle Payables

 Export Functionality:

  • You can export results table data to a csv file across all dashboards.

Descriptive Flex field Support:

  • Display of Descriptive Flexfield (DFF) attributes in Enterprise Command Center dashboards, which allow search and filter by DFF attributes,

Period Close Dashboard:

  • The Period Close dashboard provides real-time visibility into the accounting process in helping you identify any potential bottlenecks early in the closing cycle by focusing on the unprocessed accounting events, accounting errors, and untransformed journals to General Ledger. 
  • The dashboard helps the accounts receivable manager to reduce period close time, accelerate delivery of financial results to internal management, and identify improvements to be made over the next period.
Note: Above functionalities requires Oracle Enterprise Command Center Framework (ECC) Release 12.2 V4.


12.2.4 features in Oracle Payables

Parallel Approval of Supplier Invoices: 

  • Significantly improve invoice approval processing efficiency with Parallel Approval of Supplier Invoices. 
  • Parallel approval for invoice header level approvals, enables faster approval cycles by allowing all identified approvers to receive approval notifications simultaneously upon invoice submission. 
  • Parallel approval reduces overall approval processing time by enabling all approvers to have immediate visibility to action invoices requiring approval.


Wednesday, February 19, 2020

API to Delete Descriptive Flex Field Context in Oracle APPS

--------------------------------------------------------------------------------
--API to delete Descriptive Flex Field Context
--------------------------------------------------------------------------------
DECLARE
CURSOR c1
IS
SELECT dff.application_id
  ,dff.descriptive_flexfield_name
              ,dff_context.descriptive_flex_context_code
  FROM fnd_descriptive_flexs_vl dff
              ,fnd_descr_flex_contexts_vl dff_context
         WHERE title = 'Requisition Headers'
   AND dff.descriptive_flexfield_name = dff_context.descriptive_flexfield_name
   AND dff_context.descriptive_flex_context_code IN ('Test','Req Operating Enable')
;
BEGIN
FOR i IN c1
LOOP
BEGIN 
fnd_descr_flex_contexts_pkg.delete_row(x_application_id                => i.application_id
  ,x_descriptive_flexfield_name    => i.descriptive_flexfield_name
  ,x_descriptive_flex_context_cod  => i.descriptive_flex_context_code
  ); 
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error occured while deleting DFF Context. Error Message: '||SQLERRM);
END;

dbms_output.put_line('DFF Conetext Filed: '||i.descriptive_flex_context_code ||' deleted successfully');

END LOOP;


END;

--
--Run below query to validate deletion successfully or not
/*SELECT dff.application_id
  ,dff.descriptive_flexfield_name
              ,dff_context.descriptive_flex_context_code
  FROM fnd_descriptive_flexs_vl dff
              ,fnd_descr_flex_contexts_vl dff_context
         WHERE title = 'Requisition Headers'
   AND dff.descriptive_flexfield_name = dff_context.descriptive_flexfield_name
   AND dff_context.descriptive_flex_context_code IN ('Test','Req Operating Enable')
;
*/

-- Issue commit 

API to create Sales Order Quote in Oracle APPS

To import Quote in Order Management we can use OE_ORDER_PUB.process_order API by setting TRANSACTION_PHASE_CODE to "N".

Below is the sample code:

set serveroutput on
DECLARE
 l_header_rec              OE_ORDER_PUB.Header_Rec_Type;
 l_x_header_rec          OE_ORDER_PUB.Header_Rec_Type;
 l_line_tbl              OE_ORDER_PUB.Line_Tbl_Type;
 l_x_line_tbl              OE_ORDER_PUB.Line_Tbl_Type;
 l_action_request_tbl      OE_ORDER_PUB.Request_Tbl_Type;
 l_x_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
 l_header_adj_tbl          OE_ORDER_PUB.Header_Adj_Tbl_Type;
 l_line_adj_tbl          OE_ORDER_PUB.line_adj_tbl_Type;
 l_header_scr_tbl          OE_ORDER_PUB.Header_Scredit_Tbl_Type;
 l_line_scredit_tbl      OE_ORDER_PUB.Line_Scredit_Tbl_Type;
 l_request_rec              OE_ORDER_PUB.Request_Rec_Type ;
 l_return_status          VARCHAR2(1000);
 l_msg_count              NUMBER;
 l_msg_data              VARCHAR2(1000);
 p_api_version_number      NUMBER :=1.0;
 p_init_msg_list          VARCHAR2(10) := FND_API.G_FALSE;
 p_return_values          VARCHAR2(10) := FND_API.G_FALSE;
 p_action_commit          VARCHAR2(10) := FND_API.G_FALSE;
 x_return_status          VARCHAR2(1);
 x_msg_count              NUMBER;
 x_msg_data              VARCHAR2(100);
 p_header_rec              OE_ORDER_PUB.Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC;
 p_old_header_rec          OE_ORDER_PUB.Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC;
 p_header_val_rec          OE_ORDER_PUB.Header_Val_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;
 p_old_header_val_rec      OE_ORDER_PUB.Header_Val_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;
 p_Header_Adj_tbl          OE_ORDER_PUB.Header_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;
 p_old_Header_Adj_tbl      OE_ORDER_PUB.Header_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;
 p_Header_Adj_val_tbl      OE_ORDER_PUB.Header_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;
 p_old_Header_Adj_val_tbl   OE_ORDER_PUB.Header_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;
 p_Header_price_Att_tbl     OE_ORDER_PUB.Header_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;
 p_old_Header_Price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;
 p_Header_Adj_Att_tbl       OE_ORDER_PUB.Header_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;
 p_old_Header_Adj_Att_tbl   OE_ORDER_PUB.Header_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;
 p_Header_Adj_Assoc_tbl     OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;
 p_old_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;
 p_Header_Scredit_tbl        OE_ORDER_PUB.Header_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;
 p_old_Header_Scredit_tbl      OE_ORDER_PUB.Header_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;
 p_Header_Scredit_val_tbl      OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;
 p_old_Header_Scredit_val_tbl   OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;
 p_line_tbl                     OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
 p_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
 p_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;
 p_old_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;
 p_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;
 p_old_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;
 p_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;
 p_old_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;
 p_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;
 p_old_Line_Price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;
 p_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;
 p_old_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;
 p_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
 p_old_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
 p_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;
 p_old_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;
 p_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;
 p_old_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;
 p_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;
 p_old_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;
 p_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;
 p_old_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;
 p_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type := OE_ORDER_PUB.G_MISS_REQUEST_TBL;
 x_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type;
 x_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;
 x_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;
 x_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
 x_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
 x_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
 x_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
 x_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
 x_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;
 x_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;
 x_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;
 x_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
 x_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
 x_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
 x_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;
 x_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;
 x_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;
 x_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;
 x_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
 X_DEBUG_FILE VARCHAR2(2000);
 l_line_tbl_index NUMBER;
 l_msg_index_out NUMBER(10);
BEGIN
 dbms_output.enable(1000000);

 fnd_global.apps_initialize(0,21623,660); -- 1318 pass in user_id, responsibility_id, and application_id

 MO_GLOBAL.INIT('ONT');
 MO_GLOBAL.SET_POLICY_CONTEXT('S', 204);

 oe_debug_pub.debug_on;
 oe_debug_pub.initialize;

 X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE');

 oe_Debug_pub.setdebuglevel(5);
 dbms_output.put_line('File : '||X_DEBUG_FILE);
 dbms_output.put_line('START OF NEW DEBUG');

 --This is to CREATE an order header and an order line

 --Create Header record
 --Initialize header record to missing
 l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
 l_header_rec.TRANSACTIONAL_CURR_CODE := 'USD';
 l_header_rec.pricing_date := SYSDATE;
 l_header_rec.cust_po_number := 'TestQuote1';
 l_header_rec.sold_to_org_id := 1005;
 l_header_rec.price_list_id := 1000;
 l_header_rec.ordered_date := SYSDATE;
 l_header_rec.shipping_method_code := 'DHL';
 l_header_rec.sold_from_org_id := 204;
 l_header_rec.salesrep_id := -3;
 l_header_rec.order_type_id := 1000;
 l_header_rec.transaction_phase_code := 'N';
 l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
 l_line_tbl_index :=1;

 -- FIRST LINE RECORD
 -- Initialize record to missing
 l_line_tbl(l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;
 -- Line attributes
 l_line_tbl(l_line_tbl_index).inventory_item_id := 149;
 l_line_tbl(l_line_tbl_index).ordered_quantity := 5;
 l_line_tbl(l_line_tbl_index).ship_from_org_id := 207;
 l_line_tbl(l_line_tbl_index).subinventory := 'FGI';
 l_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_CREATE;
 -- CALL TO PROCESS ORDER Check the return status and then commit.

 --"TRANSACTION_PHASE_CODE"should be set to "N" while using Process Order API to import quote.

 OE_ORDER_PUB.process_order(p_api_version_number => 1.0
                           ,p_init_msg_list => fnd_api.g_false
                           ,p_return_values => fnd_api.g_false
                           ,p_action_commit => fnd_api.g_false
                           ,x_return_status => l_return_status
                           ,x_msg_count => l_msg_count
                           ,x_msg_data => l_msg_data
                           ,p_header_rec => l_header_rec
                           ,p_line_tbl => l_line_tbl
                           ,p_action_request_tbl => l_action_request_tbl
   -- OUT PARAMETERS
   ,x_header_rec => l_x_header_rec
   ,x_header_val_rec => x_header_val_rec
   ,x_Header_Adj_tbl => x_Header_Adj_tbl
   ,x_Header_Adj_val_tbl => x_Header_Adj_val_tbl
   ,x_Header_price_Att_tbl => x_Header_price_Att_tbl
   ,x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl
   ,x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl
   ,x_Header_Scredit_tbl => x_Header_Scredit_tbl
   ,x_Header_Scredit_val_tbl => x_Header_Scredit_val_tbl
   ,x_line_tbl => l_x_line_tbl
   ,x_line_val_tbl => x_line_val_tbl
   ,x_Line_Adj_tbl => x_Line_Adj_tbl
   ,x_Line_Adj_val_tbl => x_Line_Adj_val_tbl
   ,x_Line_price_Att_tbl => x_Line_price_Att_tbl
   ,x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl
   ,x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl
   ,x_Line_Scredit_tbl => x_Line_Scredit_tbl
   ,x_Line_Scredit_val_tbl => x_Line_Scredit_val_tbl
   ,x_Lot_Serial_tbl => x_Lot_Serial_tbl
   ,x_Lot_Serial_val_tbl => x_Lot_Serial_val_tbl
   ,x_action_request_tbl => l_x_action_request_tbl
);
dbms_output.put_line('OM Debug file: ' ||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE);

-- Retrieve messages
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get( p_msg_index => i
                        ,p_encoded => Fnd_Api.G_FALSE
                        ,p_data => l_msg_data
                        ,p_msg_index_out => l_msg_index_out
);

DBMS_OUTPUT.PUT_LINE('message is: ' || l_msg_data);
DBMS_OUTPUT.PUT_LINE('message index is: ' || l_msg_index_out);
END LOOP;
 -- Check the return status
 IF l_return_status = FND_API.G_RET_STS_SUCCESS
 THEN
  dbms_output.put_line('Process Order Sucess');
 ELSE
  dbms_output.put_line('Failed');
 END IF;
END;
/
Commit;


You can see data in ORDER_NUMBER, QUOTE_NUMBER columns in OE_ORDER_HEADERS_ALL table .

Tuesday, February 11, 2020

Budgetary Control Options in Oracle General Ledger(Lookups: FUNDS_CHECK_LEVEL,PTD_YTD and BOUNDARY_TYPE)

In Budgetory Control Options form we Funds Check Level, Amount Type, Boundary and Funding Budget.

We have Advisory, Absolute and None types of Funds Check Level.

Use Funds Check level when setting budgetary control options for account ranges, for source and category combinations in budgetary control groups, and for summary account templates.
  • Advisory: Use Advisory as Fund Check Level option for online notification when transactions fail funds checking. The system still reserves funds for transactions even when no funds are available.
  • Absolute: Use Absolute as Fund Check Level option to prohibit you from reserving funds for a transaction unless funds are available.
  • None: Use None as Fund Check Level option for no funds checking or funds reservation.

Funds Check Level is coming from Lookup Type "FUNDS_CHECK_LEVEL". Below is the query we can use.

Amount Type is coming from Lookup Type "PTD_YTD". Below is the query we can use.



Boundary is coming from Lookup Type "BOUNDARY_TYPE". Below is the query we can use.




Thursday, January 30, 2020

FORMULA_RESULT_FLAG column in the PAY_RUN_RESULT_VALUES table


FORMULA_RESULT_FLAG column in the PAY_RUN_RESULT_VALUES table:-

D means Run result calculated by the Fast Formula.

N means Run result created by a call to the API (Ex: PAY_RUN_RESULT_PKG).

NULL means Run result created as an indirect result or the input value directly entered by the user.

Monday, January 27, 2020

Move Order Status in Oracle Apps

select *
  from fnd_lookup_values
where lookup_type = 'MTL_TXN_REQUEST_STATUS'
and language = USERENV('LANG')
order by lookup_code


Friday, April 12, 2019

"PO: Allow Autocreation of Oracle Sourcing Documents" Profile Option in Oracle APPS

PO: Allow Autocreation of Oracle Sourcing Documents:-
  • Yes or No indicates whether the buyer can create Oracle Sourcing documents from the AutoCreate window. 
  • The default is No.
  •  

"PO: Allow Auto-generate Sourcing Rules" Profile in Oracle APPS

PO: Allow Auto-generate Sourcing Rules:-

  • Create Only indicates whether the buyer can create new sourcing rules, sourcing assignments, and approved supplier list entries while approving a blanket purchase agreement. 
  • Create and Update enables the buyer to update sourcing rules and assignments as well as create new sourcing rules. 
  • The default is None.

"MRP: Default Sourcing Assignment Set" Profile Option in Oracle Apps


  • "MRP: Default Sourcing Assignment Set" profile option indicates which sourcing rules assignment set will be used in Purchasing and Supplier Scheduling
  • Planning allows the user to use multiple Assignment Sets, but Purchasing looks at only a single Assignment Set. 
  • The value of this profile option should be the name of the Assignment Set used in Purchasing. 
  • This profile option must be set if the user is using sourcing rules in Purchasing.

Wednesday, April 10, 2019

Query to identify the Customer Bank details in Oracle Apps R12


SELECT DISTINCT ac.customer_name, ac.customer_number
  FROM iby_pmt_instr_uses_all instr_assign,
       iby_external_payers_all payee,
       iby_ext_bank_accounts bank_acct,
       hz_cust_accounts_all hca,
       ar_customers ac
 WHERE instr_assign.instrument_id = bank_acct.ext_bank_account_id
   AND instr_assign.ext_pmt_party_id = payee.ext_payer_id
   AND instr_assign.instrument_type = 'BANKACCOUNT'
   AND instr_assign.payment_flow = 'FUNDS_CAPTURE'
   AND instr_assign.payment_function = 'CUSTOMER_PAYMENT'
   AND hca.cust_account_id = payee.cust_account_id
   AND ac.customer_number = hca.account_number

Monday, April 1, 2019

Query to get Print Lead Days in Oracle Lease and Finance Management(OLFM)

SELECT okl_stream_billing_pvt.get_printing_lead_days(:p_chr_id)
  FROM dual

p_chr_id --> okc_k_headers_all_b.id

Friday, March 1, 2019

OKL_LA_SALES_TAX_PVT.PROCESS_SALES_TAX


  • This is a private API. 
  • This procedure calls the tax engine and generates invoices for upfront taxes.

OKL_LA_JE_PVT.GENERATE_JOURNAL_ENTRIES


  • This is a private API. 
  • This procedure calls the accounting engine.


OKL_INTEREST_CALC_PUB.CALC_INTEREST_ACTIVATE


  • This is a public API. 
  • This procedure capitalizes or bill interim interest.

OKL_ACTIVATE_IB_PUB.ACTIVATE_IB_INSTANCE


  • This is a public API. 
  • It calls Install Base API to create an item instance in Install Base.

OKL_ACTIVATE_ASSET_PUB.ACTIVATE_ASSET


  • This is a public API. 
  • It creates an asset transaction record for each asset on the contract.

Thursday, February 28, 2019

SQL Query to get the linkage between Fixed Asset and Oracle Lease and Finance Management(OLFM) Asset

SELECT lse.lty_code,
       cle.id,
       cle.cle_id,
       itm.object1_id1,
       itm.jtot_object1_code
  FROM apps.okc_k_lines_b     cle,
       apps.okc_line_styles_b lse,
       apps.okc_k_items       itm,
       fa_additions           ast
 WHERE lse.id = cle.lse_id
   AND itm.cle_id = cle.id
   AND ast.asset_id = itm.object1_id1
   AND cle.dnz_chr_id = (SELECT khr.id
                           FROM apps.okc_k_headers_all_b khr
                          WHERE khr.contract_number = 'CONTRACT_NUMBER')
   AND itm.jtot_object1_code = 'OKX_ASSET';

List of API's used for Asset Disposal in Oracle Lease and Finance Management(OLFM)

Below are the list of api's used in OLFM for asset disposal.

API Name
API Description
FA_ADDITION_PUB.DO_ADDITION
This API is used to add an asset to a specific book in Fixed Assets.
OKL_OKC_MIGRATION_PVT.UPDATE_CONTRACT_ITEM
After creation of asset disposal in Fixed Assets, update contract item with the proper linkage(Asset_ID)
OKL_TRX_ASSETS_PUB.CREATE_TRX_ASSET_DEF
This API is used to create asset disposal header in OKL_TRX_ASSETS
OKL_TXL_ASSETS_PUB.CREATE_TXL_ASSET_DEF
This API is used to create asset disposal line in OKL_TXD_ASSETS_V
OKL_TXD_ASSETS_PUB.CREATE_TXD_ASSET_DEF
This API is used to create asset disposal details in OKL_TXL_ASSETS_V
OKL_SLA_ACC_SOURCES_PVT.POPULATE_SOURCES
This API is used to populate proper asset accounting entries information for asset in OLFM.
OKL_AM_ASSET_DISPOSE_PUB.DISPOSE_ASSET
This procedure is used to retire an asset in Fixed Assets. It checks if the asset is to be fully or partially retired, based upon the parameter value p_quantity and then calls the appropriate routine to retire the asset. It then stores the disposition transactions in OKL tables, calls accounting engine and then finally cancels all pending transactions in OKL tables for this asset.
FA_ADJUSTMENT_PUB.DO_ADJUSTMENT
This API is used to adjust the financial information of an asset in a specific book in Fixed Assets.
FA_RETIREMENT_PUB.DO_RETIREMENT
This API is used to partially or fully retire an asset in a specific book in Fixed Assets.
OKL_AM_ASSET_DISPOSE_PVT.EXPIRE_ITEM
This procedure expires an item in Installed Base(IB).
CSI_ITEM_INSTANCE_PUB.EXPIRE_ITEM_INSTANCE
This procedure expires an existing item instance and its child entities in Install Base.



List of tables for off-lease amortization Oracle Lease and Finance Management(OLFM) Internal Transactions:

OKL_TRX_ASSETS:-

  • A top level transaction table to group all the transactions for creation of Asset lines for a contract.

OKL_TXD_ASSETS_V:-

  • A transaction table to record tax book related asset attributes needed by Oracle Fixed Assets before activating the contract and additionally to store the split asset transaction attributes needed by Fixed Assets before activating the contract.

OKL_TXL_ASSETS_V:-

  • A contract line level transaction table to record asset attributes needed by Fixed Assets before activating the contract.

Asset return table(OKL_ASSET_RETURNS_B) in Oracle Lease and Finance Management(OLFM)

OKL_ASSET_RETURNS_B:-


  • This is the transaction table for managing Assets that is off lease after a Contract Termination or assets that have been identified as possible returns.
  • The return asset transaction is created by one of the following: 
  1. Repossession request
  2. Customer or Vendor notification of intent to return 
  3. System notification of potential return




Terminate Expired Contracts in Oracle Lease and Finance Management(OLFM)


  • Run the Standard Concurrent Program"Terminate Expired Contracts"  to set the status of contracts to "Expired"
  • This step will create the off-lease amortization transaction for each asset in Lease and Finance Management. 
  • The following steps or activities are performed when processing terminated expired DF lease contracts:
  1. Eligible for Termination:- Checks if billing is complete up to the termination date (only if contract has expired).
  2. Close Balances (Write-off Small Balances):- Creates adjustment transactions for the balances less than tolerance amount (Only for Full Termination).
  3. Post Accounting Entries:-


  • Creates termination accounting entries.
  • Creates accrual accounting entries for catch-up (i.e., transfer accruals from non-income bucket to income bucket). Only for Full Terminations.
  • Reverse loss provisions. Only for Full Terminations.
  • Reverse accruals (if excess accruals exist). Only for Full Terminations.
  • Accelerate income accrual recognition for financed fees. Only for Full Terminations.
  • Accelerate income recognition or refund subsidy (based on subsidy setup).
      4.Dispose Asset:-
  • Retires assets in Fixed Assets. Expires Item in Install base. Retires asset in FA on quote effective from date.

     5.Amortize Asset (Create Off-Lease Amortization):-

  •   Creates off-lease amortization transactions in Lease and Finance Management, which are then processed into Fixed Assets (to adjust depreciation). Off-lease amortization transaction date is set to quote effective date.

     6. Create Asset Return:-

  • Creates asset return record in Lease and Finance Management with return status as Scheduled. 

     7. Update Contract Status (streams, contracts and contract lines):-

  • Updates stream status to HISTORIZE (except for residual value and investor contract obligation payable streams).
  • Sets the lines and contract to Terminated. Sets date terminated as quote effective from date. (Only for Full Termination). If the contract is evergreen eligible the contract and lines are updated to Evergreen status. Along with that off-lease amortization and Termination accounting is also done for the contract.

     8.Update Contract Details (payments, contract lines):-

  • Updates the payments i.e. remove future payments for terminated assets after termination date.
  • Updates lines to Terminated for all the terminated financial assets. If the financial asset is attached to service or fees, terminates those sub lines as well. Terminates the primary service or fee line if all sub-lines terminated. (Does not terminate those primary service and fee lines that do not have any sub lines).
  • Launches mass Rebook, which Rebooks the contract. Sets Rebook date as quote-effective date. Pass terminated assets to Rebook API. (Only for Partial Termination).


SQL Query to get the linkage between Install Base and Lease and Finance Management(OLFM) Asset Instance:

SELECT lse.lty_code,
       cle.id,
       cle.cle_id,
       itm.object1_id1,
       itm.jtot_object1_code
  FROM apps.okc_k_lines_b     cle,
       apps.okc_line_styles_b lse,
       apps.okc_k_items       itm,
       csi_item_instances     ib
 WHERE lse.id = cle.lse_id
   AND itm.cle_id = cle.id
   AND ib.instance_id = itm.object1_id1
   AND cle.dnz_chr_id = (SELECT khr.id
                           FROM apps.okc_k_headers_all_b khr
                          WHERE khr.contract_number = 'CONTRACT_NUMBER')
   AND itm.jtot_object1_code = 'OKX_IB_ITEM';

SQL Query to get the linkage between Fixed Asset and Lease and Finance Management(OLFM) Asset

SELECT lse.lty_code,
       cle.id,
       cle.cle_id,
       itm.object1_id1,
       itm.jtot_object1_code
  FROM apps.okc_k_lines_b     cle,
       apps.okc_line_styles_b lse,
       apps.okc_k_items       itm,
       fa_additions           ast
 WHERE lse.id = cle.lse_id
   AND itm.cle_id = cle.id
   AND ast.asset_id = itm.object1_id1
   AND cle.dnz_chr_id = (SELECT khr.id
                           FROM apps.okc_k_headers_all_b khr
                          WHERE khr.contract_number = 'CONTRACT_NUMBER')
       AND itm.jtot_object1_code = 'OKX_ASSET';

Contract Import Step by step process in OLFM


  • The following process should be sequentially executed.

         Import àFundingàValidationàStream GenerationàDraft Journal EntryàApproval

  • Some of the processes mentioned are optional like Draft Journal Entry & Approval. These optional processes can be skipped based on the business process and the state of the contracts in the legacy systems. 
  • If the contracts are created in batches, while one batch is undergoing one process, other batch can be run in parallel. For example, while the first batch is undergoing stream generation, the second batch can be submitted for validation.


API Used to Approve Contract in Oracle Lease and Finance Management(OLFM)


  • Review and approve the contract before activation, you can approve one contract at a time from the booking page and initiating the approval workflow. 
  • To perform a mass update to change the contract status to APPROVED using the approval API. 
  • The contract status must be updated to APPROVED before a contract can be activated i.e. BOOKED, but execution of the approval workflow is an optional step. 
  • Use the following API to submit contracts for approval.
API Name
Description
OKL_CONTRACT_BOOK_PUB. SUBMIT_FOR_APPROVAL ()
This program will submit the contract for approval
Note:- There is no parallel processing or bulk processing capabilities for contract approval program.This API can be called one contract at a time. If you need batch processing capabilities to submit multiple contracts at one time, you can create custom wrappers. If you want to by-pass the approval workflow, you can create a custom program to update the contract status to APPROVED once contracts are validated and in PASSED status.

API to Generate Draft Journal Entries in Oracle Lease and Finance Management(OLFM)


  • In order to see draft journal entry records of the expected booking accounting before booking the contract, you can request draft journal entry creation. 
  • You do not need to create or view draft journal entries in order to book (activate) a contract. 
  • Streams must be completed for a contract (status COMPLETED) before you can generate draft journal entries. 
  • After generating draft journal entries, the contract status remains as COMPLETED. 
  • You can submit a contract for creating journal entries from the screen or call private Lease and Finance Management APIs to process the draft journal entry generation.
  • Following API can be used to generate the draft journal entries.
API Name
Description
OKL_CONTRACT_BOOK_PUB. GENERATE_JOURNAL_ENTRIES ()
This program will submit the contract for draft journal entry generation

Note:- There is no parallel processing or bulk processing capabilities for draft journal entry program. This API can be called one contract at a time. If you need batch processing capabilities to submit multiple contracts at one time, you can create custom wrappers.

APEX$TASK_PK

  APEX$TASK_PK is a substitution string holding the primary key value of the system of records