, , ,

R12 – How to See the AP Invoices Interface from the Front end?

Let’s say Invoices are imported from External Systems – irrespective of the transportation layer / method. Instead of viewing the Interface tables in the back end – say AP_INVOICES_INTERFACE , AP_INVOICE_LINES_INTERFACE, they can be viewed in the front-end.


Responsibility – Payables Manager
Navigation – Invoices – Entry – Open Interface Invoices
Clicking on the above link, opens the below form ..
Open the Query-mode to view the Invoice in the Interface table
Above is the header line. Click on Lines to view the lines information.
This would avoid looking for Invoices by querying in the back-end.
/by
, , , ,

SQL Query for Link Between PO-RCV-XLA-AP

Here is the SQL Query for Link Between PO-RCV-XLA-AP.

SELECT DISTINCT
       A.PO_NO,
       A.LINE_NUM,
       A.DIST_NUM,
       A.ITEM_CATEGORY,
       A.IS_CAPITAL,
       A.SEGMENT2 PO_ACCT,
       A.ACCT PO_ACCT_DESC,
       A.UNIT_PRICE,
       A.AMOUNT_ORDERED,
       NVL (
          (SELECT TO_CHAR (RT.TRANSACTION_DATE, ‘yyyy-mm-dd’)
             FROM RCV_TRANSACTIONS RT
            WHERE     RT.PO_HEADER_ID = A.PO_HEADER_ID
                  AND RT.PO_LINE_ID = A.PO_LINE_ID
                  AND RT.PO_LINE_LOCATION_ID = A.LINE_LOCATION_ID
                  AND RT.PO_DISTRIBUTION_ID = A.PO_DISTRIBUTION_ID
                  AND RT.TRANSACTION_TYPE = ‘DELIVER’
                  AND ROWNUM = 1),
          ‘N’)
          RECIEVED_FLAG,
       B.SEGMENT2 SLA_ACCT,
       B.ACCT SLA_ACCT_DESC,
       B.ENTERED_DR,
       B.ENTERED_CR,
       B.ACCOUNTED_DR,
       B.ACCOUNTED_CR,
       C.SEGMENT2 AP_ACCT,
       C.ACCT AP_ACCT_DESC,
       C.AMOUNT,
       A.SHIP_RECEIPT_FLAG,
       A.DIST_RECEIPT_FLAG
  FROM (SELECT POH.SEGMENT1 PO_NO,
               POL.LINE_NUM,
               POLL.LINE_LOCATION_ID,
               POL.PO_LINE_ID,
               POL.PO_HEADER_ID,
               POD.DISTRIBUTION_NUM DIST_NUM,
               POD.CODE_COMBINATION_ID,
               POD.PO_DISTRIBUTION_ID,
               POL.UNIT_PRICE,
               POD.AMOUNT_ORDERED,
               GCC.SEGMENT2,
               GL_FLEXFIELDS_PKG.GET_CONCAT_DESCRIPTION (
                  GCC.CHART_OF_ACCOUNTS_ID,
                  GCC.CODE_COMBINATION_ID,
                  ‘.’)
                  ACCT,
               NVL (POLL.ACCRUE_ON_RECEIPT_FLAG, ‘N’) SHIP_RECEIPT_FLAG,
               NVL (POD.ACCRUE_ON_RECEIPT_FLAG, ‘N’) DIST_RECEIPT_FLAG,
               (SELECT SEGMENT1
                  FROM MTL_CATEGORIES_B
                 WHERE CATEGORY_ID = POL.CATEGORY_ID AND ROWNUM = 1)
                  ITEM_CATEGORY,
               HL_PO_UTL_PKG.IS_CAPITAL (POL.CATEGORY_ID) IS_CAPITAL
          FROM PO_HEADERS_ALL POH,
               PO_LINES_ALL POL,
               PO_LINE_LOCATIONS_ALL POLL,
               PO_DISTRIBUTIONS_ALL POD,
               GL_CODE_COMBINATIONS GCC
         WHERE     POH.PO_HEADER_ID = POL.PO_HEADER_ID
               AND POH.PO_HEADER_ID = POD.PO_HEADER_ID
               AND POL.PO_LINE_ID = POD.PO_LINE_ID
               AND POH.PO_HEADER_ID = POLL.PO_HEADER_ID
               AND POL.PO_LINE_ID = POLL.PO_LINE_ID
               AND POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
               AND POD.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
               AND POL.ITEM_ID IS NULL /*AND poh.po_header_id IN
                                       (SELECT * FROM tmp_po_accrue_on_receipt_flag)*/
                                      ) A,
       (SELECT RT.PO_HEADER_ID,
               RT.PO_LINE_ID,
               RT.PO_LINE_LOCATION_ID,
               RT.PO_DISTRIBUTION_ID,
               RT.VENDOR_ID,
               RT.VENDOR_SITE_ID,
               SLA.*
          FROM (SELECT XTE.SOURCE_ID_INT_1,
                       XL.CODE_COMBINATION_ID,
                       XL.ENTERED_DR,
                       XL.ENTERED_CR,
                       XL.ACCOUNTED_DR,
                       XL.ACCOUNTED_CR,
                       GCC.SEGMENT2,
                       GL_FLEXFIELDS_PKG.GET_CONCAT_DESCRIPTION (
                          GCC.CHART_OF_ACCOUNTS_ID,
                          GCC.CODE_COMBINATION_ID,
                          ‘.’)
                          ACCT
                  FROM XLA.XLA_AE_HEADERS XH,
                       XLA.XLA_AE_LINES XL,
                       XLA.XLA_TRANSACTION_ENTITIES XTE,
                       XLA.XLA_EVENTS XEA,
                       GL.GL_CODE_COMBINATIONS GCC
                 WHERE     XH.AE_HEADER_ID = XL.AE_HEADER_ID
                       AND XTE.ENTITY_ID = XH.ENTITY_ID
                       AND XL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                       AND XEA.EVENT_ID = XH.EVENT_ID
                       AND XH.BALANCE_TYPE_CODE = ‘A’
                       AND XH.JE_CATEGORY_NAME = ‘Receiving’
                       AND XL.ACCOUNTED_DR IS NOT NULL) SLA,
               RCV_TRANSACTIONS RT
         WHERE RT.TRANSACTION_ID = SLA.SOURCE_ID_INT_1(+)
               AND RT.TRANSACTION_TYPE = ‘DELIVER’) B,
       (SELECT AID.DIST_CODE_COMBINATION_ID,
               POD.PO_DISTRIBUTION_ID,
               POD.PO_HEADER_ID,
               POD.PO_LINE_ID,
               POD.LINE_LOCATION_ID,
               GCC.SEGMENT2,
               GL_FLEXFIELDS_PKG.GET_CONCAT_DESCRIPTION (
                  GCC.CHART_OF_ACCOUNTS_ID,
                  GCC.CODE_COMBINATION_ID,
                  ‘.’)
                  ACCT,
               AID.AMOUNT
          FROM AP_INVOICE_DISTRIBUTIONS_ALL AID,
               PO_DISTRIBUTIONS_ALL POD,
               GL_CODE_COMBINATIONS GCC
         WHERE     AID.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
               AND AID.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
               AND AID.LINE_TYPE_LOOKUP_CODE IN (‘ITEM’, ‘ACCRUAL’)) C
 WHERE     A.PO_HEADER_ID = B.PO_HEADER_ID(+)
       AND A.PO_LINE_ID = B.PO_LINE_ID(+)
       AND A.LINE_LOCATION_ID = B.PO_LINE_LOCATION_ID(+)
       AND A.PO_DISTRIBUTION_ID = B.PO_DISTRIBUTION_ID(+)
       AND A.PO_DISTRIBUTION_ID = C.PO_DISTRIBUTION_ID(+);

/by
, , , ,

How to Enable Personalization link for OAF Pages using Profile Options

To Access OAF Personalization in Oracle Apps, we need to set the values of following profiles options to enable Personalization Page link in OAF Pages. You can enable these profile options at all levels (Function, Site, Responsibility, User)


These four profile options are mandatory for working on OAF personalization in apps. To Assign these Profile Options, we need to go to System Administrator or Functional Administrator Responsibility.

Navigation:
System Administrator -> Profile -> System
Functional Administrator -> Core Services -> Profiles

Profile Name
Profile Value
FND: Diagnostics
Yes
Personalize Self-Service Defn
Yes
FND: Personalization Region Link Enabled
Yes / Minimal
Disable Self-Service Personal
No

Meanings:

1. FND: Diagnostics
Setting the FND: Diagnostics (FND_DIAGNOSTICS) profile option to “Yes” will enable the diagnostics global button to be rendered on the screen.

2. Personalize Self-Service Defn
Set the value to Yes to allow ‘Personalize’ link to appear at the right top of the OAF page.

3. FND: Personalization Region Link Enabled
It renders the “Personalize  Region” links above each  region in a page. Each link  takes you first to the Choose Personalization Context page, then to the Page Hierarchy Personalization page with focus on the region node from which  you selected the “Personalize  Region” link.

Two Options
Set the value to Yes to display all the personalization links above each OAF page region.
Set the value to Minimal to display key regional links.

4. Disable Self-Service Personal
Set the value to No will enable all OAF personalizations on all pages at all levels (Function, Site, Responsibility, User)


/by
, , , , , , ,

R12 – How to Get Chart of Accounts Segment Descriptions

In R12, We Can’t just directly get the Code Combination ( i.e Accounting Flex Field) Description from a single table. But Oracle has provided a package, which will help to get the description easily.

Script:
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 CODE_COMIBINATION_ID = :P_ID

Where: P_ID, you can pass Code Combination Id to get description for particular Accounting Combination.

/by
, , , ,

R12 – Relation between Ledger, Legal entity, Operating Unit

–Information can be retrieved from the table:
XLE_LE_OU_LEDGER_V

–Query for Ledger, Operating Unit, Legal Entity, balancing segment:
select HRL.COUNTRY,
  HROUTL_BG.name BG,
  HROUTL_BG.ORGANIZATION_ID,
  LEP.LEGAL_ENTITY_ID,
  LEP.name LEGAL_ENTITY,
  HROUTL_OU.name OU_NAME,
  HROUTL_OU.ORGANIZATION_ID ORG_ID,
  HRL.LOCATION_ID,
  HRL.LOCATION_CODE,
  GLEV.FLEX_SEGMENT_VALUE
from XLE_ENTITY_PROFILES LEP,
  XLE_REGISTRATIONS REG,
  HR_LOCATIONS_ALL HRL,
  HZ_PARTIES HZP,
  FND_TERRITORIES_VL TER,
  HR_OPERATING_UNITS HRO,
  HR_ALL_ORGANIZATION_UNITS_TL HROUTL_BG,
  HR_ALL_ORGANIZATION_UNITS_TL HROUTL_OU,
  HR_ORGANIZATION_UNITS GLOPERATINGUNITSEO,
  GL_LEGAL_ENTITIES_BSVS GLEV
where LEP.TRANSACTING_ENTITY_FLAG      = ‘Y’
and LEP.PARTY_ID                       = HZP.PARTY_ID
and LEP.LEGAL_ENTITY_ID                = REG.SOURCE_ID
and REG.SOURCE_TABLE                   = ‘XLE_ENTITY_PROFILES’
and HRL.LOCATION_ID                    = REG.LOCATION_ID
and REG.IDENTIFYING_FLAG               = ‘Y’
and TER.TERRITORY_CODE                 = HRL.COUNTRY
and LEP.LEGAL_ENTITY_ID                = HRO.DEFAULT_LEGAL_CONTEXT_ID
and GLOPERATINGUNITSEO.ORGANIZATION_ID = HRO.ORGANIZATION_ID
and HROUTL_BG.ORGANIZATION_ID          = HRO.BUSINESS_GROUP_ID
and HROUTL_OU.ORGANIZATION_ID          = HRO.ORGANIZATION_ID
and GLEV.LEGAL_ENTITY_ID               = LEP.LEGAL_ENTITY_ID;

/by