Friday 24 November 2017

APP-SQLAP-10199

Setup an Invoice Batch

Advantages of Invoices Batches

1. Enter invoice defaults at the batch level that override system or supplier site defaults for all invoices in the batch.

2. Maximize accuracy by tracking variances between the control invoice count and total and the actual invoice count and total resulting from your invoice entry.

3. Easily locate a batch online and review the name of the person who created the batch and the date it was created.

Responsibility: Payables, Vision Operations (USA)
Navigation: Invoices > Entry > Invoice Batches


APP-SQLAP-10199: You cannot navigate to the Invoice Batches window because batch control is not enabled for your system.

If you want to enable invoice batch control for your system, then ask your system administrator to enable the profile option AP: Use Invoice Batch Controls in the System Profile Options window.

Profile Option

AP: Use Invoice Batch Controls

YES -> Create the Invoice with batch
NO  -> Create the Invoice without batch.


To enter an Invoice Batch

1. In the Invoice Batches window enter a unique value in the Invoice Batch Name field. This name will appear on your reports and will help you locate the batch online.

2. Enter the number of invoices in the batch in the Control Count field.

Enter the sum of invoice amounts in the batch in the Control Total field. Payables tracks variances between the Control Count and Total and the Actual Count and Total as you enter invoices.

Note: If there is a discrepancy between the invoice amount and batch amount, Payables warns you when you exit a batch but it does not prevent Invoice Validation and payment of the individual invoices within a batch. You can make a correction immediately, or you can adjust the invoice batch later.

3. Enter any Invoice Defaults you want for the invoices. Defaults include: Currency, Type, Document Category, Hold Name, Liability Account, Payment Terms, Pay Group, GL Date, and Hold Reason.

These values you enter for defaults override any system and supplier site defaults for the invoices. For example, if you want the purchase order to provide the default value for Payment Terms on the invoice, then leave the Payment Terms field here blank. When you enter individual invoices you can override any values that default from the batch.

4. Choose the Invoices button and enter the invoices.

 
Responsibility: Payables, Vision Operations (USA)
Navigation: Invoices > Entry > Invoice Batches
 
Batch Name: ABC Batch

Tuesday 21 November 2017

How to cancel Scheduled Concurrent Programs in Oracle apps

From Front End:

1) Login with the same user, who scheduled the concurrent program
2) Go to view requests from the responsibility it's been scheduled -->View Requests--> Query for the program
3) You should get one record in scheduled state
4) Click on view details end date the schedule and cancel the request.


Select * from fnd_concurrent_programs
WHERE CONCURRENT_PROGRAM_ID = ----------------

Select * from fnd_concurrent_programs_VL
WHERE USER_CONCURRENT_PROGRAM_NAME = 'Name'

Select * from fnd_concurrent_requests
WHERE CONCURRENT_PROGRAM_ID = ----------------




From Back End
Use below query to cancel scheduled concurrent program.

UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code IN ('Q','I')
AND requested_start_date > SYSDATE
AND hold_flag = 'N'
AND CONCURRENT_PROGRAM_ID=&P_CONCURRENT_PROGRAM_ID;

COMMIT;



Friday 17 November 2017

GL Code Combinaton segment and description

select * from gl_code_combinations

select * from gl_code_combinations_kfv

--Query to get GL Acounting Flex Fields

SELECT *
  FROM fnd_flex_key_seg_vset_v
 WHERE application_name = 'General Ledger'
   AND id_flex_name = 'Accounting Flexfield'
  

SELECT   PARENT_SEGMENT_NAME
  FROM   fnd_flex_key_seg_vset_v
 WHERE   application_name = 'General Ledger'
         AND id_flex_name = 'Accounting Flexfield'


--Query to Get values for  which segment stores which value

       
select * from fnd_application_vl where application_id = 101

select distinct ID_FLEX_CODE,APPLICATION_ID from fnd_id_flex_segments 

SELECT *
  FROM fnd_id_flex_segments
 WHERE application_id = 101 AND id_flex_code = 'GL#'
 order by application_column_name;

SELECT application_column_name,segment_name
  FROM fnd_id_flex_segments
 WHERE application_id = 101 AND id_flex_code = 'GL#'
 order by application_column_name;


-Query to Get Segment Value and Description of Each segment.

/* Formatted on 11/17/2017 12:12:15 PM (QP5 v5.114.809.3010) */
SELECT      gcc.segment1
         || '-'
         || gcc.segment2
         || '-'
         || gcc.segment3
         || '-'
         || gcc.segment4
         || '-'
         || gcc.segment5
         || '-'
         || gcc.segment6
            ACCOUNT,
            A1.DESCRIPTION
         || '-'
         || A2.DESCRIPTION
         || '-'
         || A3.DESCRIPTION
         || '-'
         || A4.DESCRIPTION
         || '-'
         || A5.DESCRIPTION
         || '-'
         || A6.DESCRIPTION
            description
  FROM   fnd_flex_values_vl A1,
         fnd_flex_values_vl A2,
         fnd_flex_values_vl A3,
         fnd_flex_values_vl A4,
         fnd_flex_values_vl A5,
         fnd_flex_values_vl A6,
         gl_code_combinations gcc
 WHERE       a1.flex_value = gcc.segment1
         AND a2.flex_value = gcc.segment2
         AND a3.flex_value = gcc.segment3
         AND a4.flex_value = gcc.segment4
         AND a5.flex_value = gcc.segment5
         AND a6.flex_value = gcc.segment6;


SELECT gl_flexfields_pkg.get_concat_description
                                          (chart_of_accounts_id,
                                           code_combination_id
                                          )
            FROM gl_code_combinations
           


SELECT gl_flexfields_pkg.get_description_sql
                                     (chart_of_accounts_id,--- chart of account id
                                      1,----- Position of segment
                                      segment1 ---- Segment value
                                     )
FROM gl_code_combinations

SELECT gl_flexfields_pkg.get_description_sql
                                     (chart_of_accounts_id,--- chart of account id
                                      1,----- Position of segment
                                      segment2 ---- Segment value
                                     )
FROM gl_code_combinations


GL Opening Balance:

/* Formatted on 11/17/2017 12:31:55 PM (QP5 v5.114.809.3010) */
SELECT   *
  FROM   (  SELECT   GL.GL_CODE_COMBINATIONS.SEGMENT1 LEGAL_ENTITY,
                     GL.GL_CODE_COMBINATIONS.SEGMENT2 ACCOUNT,
                     GL.GL_CODE_COMBINATIONS.SEGMENT5 ENTRY_IND,
                     GL.GL_BALANCES.PERIOD_NAME,
                     GL_CODE_COMBINATIONS.ACCOUNT_TYPE,
                     SUM( (NVL (BEGIN_BALANCE_DR, 0)
                           - NVL (BEGIN_BALANCE_CR, 0))
                         + (NVL (PERIOD_NET_DR, 0) - NVL (PERIOD_NET_CR, 0)))
                        YTD_ACTUAL_AMOUNT
              FROM   GL.GL_BALANCES, GL.GL_CODE_COMBINATIONS
             WHERE   GL.GL_BALANCES.CODE_COMBINATION_ID =
                        GL.GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
                     AND GL.GL_CODE_COMBINATIONS.ACCOUNT_TYPE IN
                              ('O', 'L', 'A')
                     AND GL.GL_BALANCES.LEDGER_ID = 2021
                     AND GL.GL_BALANCES.PERIOD_NAME = 'Jul-17'
                     --AND GL.GL_BALANCES.CURRENCY_CODE = 'USD'
                     AND GL.GL_BALANCES.ACTUAL_FLAG = 'A'
          GROUP BY   SEGMENT1,
                     SEGMENT2,
                     SEGMENT5,
                     PERIOD_NAME,
                     ACCOUNT_TYPE
          ORDER BY   ACCOUNT_TYPE, SEGMENT1)
 WHERE   YTD_ACTUAL_AMOUNT <> 0;

GL Code Combinaton segment and description 

/* Formatted on 11/17/2017 1:02:53 PM (QP5 v5.114.809.3010) */
SELECT   GCC.CODE_COMBINATION_ID,
         GCC.SEGMENT1,
         GCC.SEGMENT2,
         GCC.SEGMENT3,
         GCC.SEGMENT4,
         GCC.SEGMENT5,
         GCC.SEGMENT6,
         GCC.SEGMENT7,
         GCC.SEGMENT8,
         SUBSTR (
            APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
               GCC.CHART_OF_ACCOUNTS_ID,
               1,
               GCC.SEGMENT1
            ),
            1,
            40
         )
            SEGMENT1_DESC,
         SUBSTR (
            APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
               GCC.CHART_OF_ACCOUNTS_ID,
               2,
               GCC.SEGMENT2
            ),
            1,
            40
         )
            SEGMENT2_DESC,
         DECODE (
            GCC.SEGMENT3,
            NULL,
            '',
            SUBSTR (
               APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                  GCC.CHART_OF_ACCOUNTS_ID,
                  3,
                  GCC.SEGMENT3
               ),
               1,
               40
            )
         )
            SEGMENT3_DESC,
         DECODE (
            GCC.SEGMENT4,
            NULL,
            ' ',
            SUBSTR (
               APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                  GCC.CHART_OF_ACCOUNTS_ID,
                  4,
                  GCC.SEGMENT4
               ),
               1,
               40
            )
         )
            SEGMENT4_DESC,
         DECODE (
            GCC.SEGMENT5,
            NULL,
            ' ',
            SUBSTR (
               APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                  GCC.CHART_OF_ACCOUNTS_ID,
                  5,
                  GCC.SEGMENT5
               ),
               1,
               40
            )
         )
            SEGMENT5_DESC,
         DECODE (
            GCC.SEGMENT6,
            NULL,
            ' ',
            SUBSTR (
               APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                  GCC.CHART_OF_ACCOUNTS_ID,
                  6,
                  GCC.SEGMENT6
               ),
               1,
               40
            )
         )
            SEGMENT6_DESC,
         DECODE (
            GCC.SEGMENT7,
            NULL,
            ' ',
            SUBSTR (
               APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                  GCC.CHART_OF_ACCOUNTS_ID,
                  7,
                  GCC.SEGMENT7
               ),
               1,
               40
            )
         )
            SEGMENT7_DESC,
         DECODE (
            GCC.SEGMENT9,
            NULL,
            '',
            SUBSTR (
               APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                  GCC.CHART_OF_ACCOUNTS_ID,
                  8,
                  GCC.SEGMENT8
               ),
               1,
               40
            )
         )
            SEGMENT8_DESC,
         GCC.CHART_OF_ACCOUNTS_ID CHART_OF_ACCOUNTS_ID,
         GCC.ACCOUNT_TYPE
  FROM   GL_CODE_COMBINATIONS GCC
 WHERE   1 = 1
 and ACCOUNT_TYPE = 'A'
 and GCC.CODE_COMBINATION_ID = :P_ID


ACCOUNT_TYPE column:

A - Asset

E - Expense

L - Liability

O - Owners Equity

R - Revenue

Friday 3 November 2017

TCG ETL Error


 Error 1:-


Error code: 9154
ODI-1226:

 oracle.apps.grc.common.job.JobExecutionException: Scenario with id: 9154 failed - will set the job to failed.


oracle.apps.grc.common.job.JobExecutionException: Scenario with id: 51154 failed - will set the job to failed.