Getting Code Combination ID using fnd_flex_ext.get_ccid

Sometimes for Retrival of the Code Combinations the view(gl_code_combinations_kfv) will not be enough. We need a function to get the value so at that time we can use fnd_flex_ext.get_ccid to get the Code Combination.

select fnd_flex_ext.get_ccid(

‘SQLGL’, –Application Short Name

‘GL#’, –key_flex_code

50357, –structure_number   => this value we can obtain from this Query –select chart_of_accounts_id from gl_sets_of_books where set_of_books_id = 2021;

sysdate, –Validation Date

’01-00-11113-001-00-00′ –Concatenated Segments

) from dual;

 

 

Error in Requisition AME(The attribute REQUISITION_TOTAL has invalid usage.)

When a user was trying to create a Requisition he was not able to because the approver list was not created and then when I checked the AME. In AME test workbench I checked with the Transaction ID. The Requisition Header ID is the transaction ID and then when I checked I got this Error

Warning

The attribute REQUISITION_TOTAL has invalid usage. Approver list may not be genarated if this attribute used in conditions.

AME attribute has invalid usage

AME attribute has invalid usage

So I just checked the attribute and it is fine. So I checked the SR and got the document(Doc ID 951573.1).

ERROR
The attribute REQUISITION_TOTAL has invalid usage. Approver list may not be generated if this attribute used in conditions.

Steps To Reproduce:
The issue can be reproduced with the following steps:
1. Log in as Approvals Management Business Analyst.
2. Click test for Purchase Requisition Approval.
3. Click run real transaction test.
4. Enter the requisition_header_id and click go.
5. The message below appears:

“The attribute REQUISITION_TOTAL has invalid usage. Approver list may not be generated if this
attribute used in conditions.”

Cause

The profile option “POR: Default Currency Conversion Rate Type” is not set

The dynamic query that calculates the REQUISITION_TOTAL uses the Currency, Rate Type, Rate Date, Rate, Price and Amount to obtain the final value.

Solution

To implement the solution, please execute the following steps:

1. Navigate to System Administrator responsibility navigate to  > Profiles > System
2. Query profiles POR: Default Currency Conversion Rate Type
3. Set the value correctly at the Site level.

 

 

Adding Invoice and registering as asset

To add the Invoice to the Fixed Asset first we have to put a Invoice and then make sure that Given Account is a Asset Account and then Validate the Invoice and make accounting once done run the Concurrent named Mass Additions Create with parameter as GL date which is Given in Invoice and select the Book that the Asset has to be registered.

Invoice Distribution Line

Invoice Distribution Line

Below is the Screenshot of the program

Mass Additions Create in AP

Mass Additions Create in AP

Once this is done you can check the same by running the Mass Additions Create Report and then you can verify the output whether the particular invoice has been send to FA and then you can go to Fixed Assets and check in Mass Additions the Invoice will be coming as new and then you can give the category and other fields and post as a Asset.

 

HRMS to GL Mapping Issue

I got a weird Issue from finance department that they were unable to get the payroll information. When I checked the costing report in HRMS everything seemed fine. Then on seeing the backend information I was able to see that LedgerID and Chart of Accounts ID was missing from the GL_INTERFACE table.

I checked the GL Mapping there the mapping was not there.

Navigation

Global HRMS Manager –> Payroll –> GL Flexfield Map.

Now after the mapping is done Issue is resolved.

GL Balances info and Query

To Check the Available funds

GL Super User –> Inquiry –> Funds

Now you can query the information that you need. To show it month wise you can go to Tools –> Period Balances and then if you need more information about the particulare Periods Information you can again go to Tools –> Budget Lines, Actual Lines and Encumbrance Lines accordingly.

Below is the Query for GL Balances

Funds Available in Oracle apps

SELECT
SUM (NVL (gb.begin_balance_dr, 0) – NVL (gb.begin_balance_cr, 0))
beginning_bal,
SUM(  NVL (gb.begin_balance_dr, 0)
– NVL (gb.begin_balance_cr, 0)
+ (NVL (gb.period_net_Dr, 0) – NVL (gb.period_net_cr, 0)))
end_bal
FROM   gl_balances gb, gl_code_combinations_kfv gcc
WHERE   gb.code_combination_id = gcc.code_combination_id
AND gcc.CONCATENATED_SEGMENTS =
’01-00-52217-000-00-00′   — Enter GL Account
–AND gb.ledger_id             = 2021 — Enter the Ledger
AND gb.Actual_flag = ‘E’–Encumbrance
AND gb.period_name = ‘JAN-16’                    –Enter the Period
AND gb.currency_code = ‘OMR’;

For a detailed amount where you are in need of PR and PO information you can use the below Query.

SELECT T.*, T.”Budget”-T.”Encumbrance”-T.”Actual” “Funds Available” FROM (Select      C.Segment1
|| ‘-‘
|| C.Segment2
|| ‘-‘
|| C.Segment3
|| ‘-‘
|| C.Segment4
|| ‘-‘
|| C.Segment5
|| ‘-‘
|| C.Segment6 “COA”,
TO_NUMBER(nvl(Sum (Decode (B.Actual_Flag,
‘B’, (  NVL (B.begin_balance_dr, 0)
– NVL (B.begin_balance_cr, 0)
+ (NVL (B.period_net_Dr, 0) – NVL (B.period_net_cr, 0))
)
)
) ,0) )  “Budget”,
TO_NUMBER(nvl( Sum (Decode (B.Actual_Flag,
‘E’, (  NVL (B.begin_balance_dr, 0)
– NVL (B.begin_balance_cr, 0)
+ (NVL (B.period_net_Dr, 0) – NVL (B.period_net_cr, 0))
)
)
) ,0)) + nvl(v.req_amount, 0) “Encumbrance”,
TO_NUMBER( nvl(Sum (Decode (B.Actual_Flag,
‘A’, (  NVL (B.begin_balance_dr, 0)
– NVL (B.begin_balance_cr, 0)
+ (NVL (B.period_net_Dr, 0) – NVL (B.period_net_cr, 0))
)
)
),0))  “Actual”

From Gl_Balances B, Gl_Code_Combinations C,
(select nvl(sum(LINE_ACCOUNTED_DR) – sum(LINE_ACCOUNTED_CR),0) req_amount, period_name, LINE_CODE_COMBINATION_ID ccid from  PSA_JE_BCP_LINES_V
where 1=1 –and Nvl (:P_Period, Period_Name)
and Actual_flag = ‘E’  and JE_CATEGORY = ‘Requisitions’
group by Period_Name, LINE_CODE_COMBINATION_ID ) V
Where B.Code_Combination_Id = C.Code_Combination_Id
And B.Period_Name = Nvl (:P_Period, B.Period_Name)
AND B.currency_code = ‘OMR’
&WHERE_FLEX
and V.ccid(+) = b.Code_Combination_Id
and v.period_name(+) = b.period_name
–And C.Code_Combination_Id = 2955
Group By B.Code_Combination_Id,
C.Segment1,
C.Segment2,
C.Segment3,
C.Segment4,
C.Segment5,
C.Segment6 ,
B.period_name,
v.req_amount) T
WHERE
( T.”Encumbrance”<0 OR T.”Actual”<0 );

 

Submitting a Concurrent Request from BackEnd in Oracle apps

Sometimes there will be some validation that prevents us to submit Concurrent Request from frontend. So at that time we can submit the concurrent request from backend.

Below is the script to submit concurrent request from backend.

DECLARE
LN_REQUEST_ID   NUMBER := 0;
BEGIN
MO_GLOBAL.SET_POLICY_CONTEXT (‘S’, 567);
FND_GLOBAL.APPS_INITIALIZE (USER_ID           => 10835,
RESP_ID           => 51546,
RESP_APPL_ID      => 200
);
LN_REQUEST_ID :=
FND_REQUEST.SUBMIT_REQUEST
(APPLICATION      => ‘IBY’,
PROGRAM          => ‘IBY_FD_PAYMENT_FORMAT’,
DESCRIPTION      => ‘Format Payment Instructions’,
START_TIME       => SYSDATE,
SUB_REQUEST      => FALSE,
ARGUMENT1        => 46602,
ARGUMENT2        => ‘N’
);

IF LN_REQUEST_ID = 0
THEN
INSERT INTO OAF_DEBUG_TEMP
VALUES (SYSDATE, 1, ‘Concurrent Request Failed to Submit.’, NULL);
ELSE
INSERT INTO OAF_DEBUG_TEMP
VALUES (SYSDATE, 1, ‘Concurrent Request Successfully  Submitted’,
LN_REQUEST_ID);
END IF;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
INSERT INTO OAF_DEBUG_TEMP
VALUES (SYSDATE, 1, ‘Inside Exception’, NULL);
END;

Thank you.

 

Invoking Report Based on Trigger in Oracle Apps

We have a Requirement that to run the Payment Voucher Report when user makes the payment.

Trigger1

So we have a Trigger for the purpose and Trigger is on IBY_PAYMENTS_ALL table. So when a payment is Made this trigger will be initiated.

In the trigger the Concurrent Request will be called and depending on the Operating Unit the Report Layout will change and the request will be submitted. I have attached the script in this Blog. Below is the script.

XX_check_print_trg1

Now you can just Include a Menu in the forms like View Payment Voucher Report Button and then the report comes. This is one way of automating the report invocation.

 

GL Journal Approval in Oracle apps

Journal Approval is a feature in General Ledger to send journals for approval to appropriate management before those can be posted. Journal approval uses Oracle workflow to control and monitor the approval process. Before using this feature, it must be enabled for the set of books.
Below are the setup steps.

Step 1: Enable Journal approval on the Set of Book level

Responsibility: General Ledger SuperUser
Navigation: Setup > Financials > Books > Define
Create the Set of Books

Go to Journalling tab

Check the box, Journal Approval. Save and close the form.

Step 2: Enable Journal approval on the Journal Source level

Responsibility: General Ledger SuperUser
Navigation: Setup > Journal > Sources

Check the box, Require Journal Approval, on the sources for which the journals have to be approved before those are posted.

Step 3: Create employees

For a shared HRMS installation
Responsibility: General Ledger SuperUser
Navigation: Setup > Employees > Enter
Since I have a full Oracle HRMS installation I cannot give you the screenshot of this form as it gives me an error on opening.
For a full HRMS installation
Responsibility: HRMS Manager/SuperUser
Navigation: People > Enter and Maintain

Create the employees who have to be in the approval hierarchy.

Step 4: Set authorization limits

Responsibility: General Ledger SuperUser
Navigation: Setup > Employees > Limits

Enter the employees in the hierarchy and set their limits.

Step 5: Set the profile options

The values of the following profile options will be set

Profile Option Name Meaning
Journals: Allow Preparer Approval Determines whether journal preparers can approve their own journals
Journals: Find Approver Method The default method to identify the approverThe methods are:

  1. Go Direct
  2. Go Up Management Chain
  3. One Stop Then Go Direct

Journals: Allow Preparer Approval

Journals: Find Approver Method

Options for this profile option

 

Step 6: Check the Journal Approval workflow

Open Workflow Builder and connect to the APPS database. Open the workflow named, Journal Batch.

Check the process named, GL Journal Approval Process.

This is the process in which the Journal will be approved.
The procedures and functions that are called from the workflow are located in the database package named, GL_WF_JE_APPROVAL_PKG. It does not contain the customizable procedures.
The customizable procedures are grouped together in another package, GL_WF_CUSTOMIZATION_PKG. There are 4 procedures in this package and can be modified as per your requirement as per Oracle. These procedures are referred to by the following workflow functions,

  • Customizable: Is Journal Batch Valid?
  • Customizable: Does Journal Batch Need Approval?
  • Customizable: Is Preparer Authorized to Approve?
  • Customizable: Verify Authority

Additionally Oracle allows modification/customization of the process named, Customizable: Verify Authority Process.

Oracle has defined the barebones of this process

You can add your own logic as per the business requirement.
After completing all these steps you are all set to use Journal approval process in Oracle. You can test the approval functionality as shown in Steps 3 & 4 in this article.

Source

Org ID and Set of Books ID in Oracle Finance

Kishur's Weblog

In Oracle Finance Org ID and Set of Books ID stored in two different place. You may select the below table record to find the Org ID and SOB ID.

Org ID – select * from hr_all_organization_units

Set of Books ID – select * from gl_sets_of_books

In certain situation Oracle Finance developer find difficulty to join this tables. Instead of writing a new join query you may select this ID’s from the hr_operating_units. This tables stored the Org ID and SOB ID in the same table.

View original post

The MO: % Profile Options in Oracle Apps

The Difference between the three Profiles are given below.
The MO: Default Operating Unit profile option only provides access to one operating unit and this is used as the default operating unit during transaction entry.
The MO: Operating Unit profile option only provides access to one operating unit
The MO: Security Profile provides access to multiple operating units from a single responsibility.

So regardless of whether MO: Default Operating Unit or MO: Operating Unit are set, in order to access multiple operating units the profile MO: Security Profile must be set.

 

 

Follow

Get every new post delivered to your Inbox.

Join 365 other followers