Query For Standard and Blanket Purchase Order Details

SELECT poh.segment1 po_number
     , poh.type_lookup_code
     , pr.release_num
     , poh.creation_date
     , pv.vendor_name supplier
     , pvs.vendor_site_code supplier_site_code
     , hl.location_code ship_to_location_code
     , pb.agent_name buyer_name
     , msi.segment1 item_number
     , msi.description item_desc
     , msi.inventory_item_status_code item_status
     , pll.quantity
     , pll.quantity_received
     , pll.quantity_cancelled
     , pll.quantity_billed
     , pol.unit_price
     , mp.organization_code receiving_org_code
     , (SELECT mc.concatenated_segments
        FROM mtl_categories_kfv mc, mtl_item_categories mic, mtl_category_sets mcs
        WHERE mcs.category_set_name = 'PURCHASING'
          AND mcs.category_set_id = mic.category_set_id
          AND mic.inventory_item_id = msi.inventory_item_id
          AND mic.organization_id = msi.organization_id
          AND mic.category_id = mc.category_id)
          po_category
FROM po_headers_all poh
   , po_lines_all pol
   , po_line_locations_all pll
   , po_releases_all pr
   , mtl_system_items msi
   , org_organization_definitions mp
   , po_vendors pv
   , po_vendor_sites_all pvs
   , po_agents_v pb
   , hr_locations hl
   , hr_operating_units hou
WHERE poh.type_lookup_code IN ('BLANKET', 'STANDARD')
  AND msi.inventory_item_id = pol.item_id
  AND msi.organization_id = pll.ship_to_organization_id
  AND mp.organization_id = msi.organization_id
  AND poh.po_header_id = pol.po_header_id
  AND pol.po_line_id = pll.po_line_id
  AND pr.po_header_id(+) = poh.po_header_id
  AND NVL (pll.po_release_id, 1) = NVL (pr.po_release_id, 1)
  AND poh.vendor_id = pv.vendor_id
  AND poh.vendor_site_id = pvs.vendor_site_id
  AND pvs.vendor_id = pv.vendor_id
  AND pb.agent_id = poh.agent_id
  AND hl.location_id = poh.ship_to_location_id
  AND poh.org_id = hou.organization_id
  AND hou.short_code = 'VIS-US'
ORDER BY poh.segment1, pr.release_num

Query For Drop Ship Order

SELECT h.order_number
     , l.line_number so_line_number
     , ph.segment1 po_number
     , l.ordered_item
     , l.ordered_quantity
     , por.release_num
     , pl.line_num po_line_number
     , ph.authorization_status
     , prh.interface_source_code
     , prh.segment1 requisition_number
     , prl.line_num requisition_line_number
     , ph.closed_date
     , ph.closed_code
     , ods.header_id so_header_id
     , ods.line_id so_line_id
     , prh.requisition_header_id
     , prl.requisition_line_id
     , ph.po_header_id
     , pl.po_line_id
     , pll.line_location_id po_line_location_id
     , por.po_release_id
FROM oe_drop_ship_sources ods
   , oe_order_headers_all h
   , oe_order_lines_all l
   , po_line_locations_all pll
   , po_lines_all pl
   , po_headers_all ph
   , po_requisition_headers_all prh
   , po_requisition_lines_all prl
   , po_releases_all por
WHERE h.header_id = l.header_id
  AND h.header_id = ods.header_id
  AND l.line_id = ods.line_id
  AND por.po_release_id(+) = ods.po_release_id
  AND ods.line_location_id = pll.line_location_id(+)
  AND ods.po_header_id = pl.po_header_id(+)
  AND ods.po_line_id = pl.po_line_id(+)
  AND ph.po_header_id(+) = pl.po_header_id
  AND prl.requisition_header_id(+) = ods.requisition_header_id
  AND prl.requisition_line_id(+) = ods.requisition_line_id
  AND prh.requisition_header_id(+) = prl.requisition_header_id
  AND h.order_number = :sales_order;

SELECT Statement to retrieve Oracle AOL Descriptive Flexfield Segment(DFF) Details

SELECT Statement to retrieve Oracle AOL Descriptive Flexfield Segment(DFF) Details

-- Application Developer > Navigate > Flexfields > Descriptive > Segments
-- Search for relevant Title, second half of the screen, under "Context Field Values"
-- lists the main parts of the Flexfield
-- Click into a name on the "Context Field Values" section in the lower part of the
-- screen, and click "Segments"
-- This lists the bits users see in Core Applications when they click into the DFF
-- plus shows if there is a LOV linked to the field
-- Following SELECT statement retrieves DFF details related to an application,DFF
-- title, DFF Context or
-- the entire application if no parameter is passed

-- Optional Parameters:-
-- P_TITLE -- DFF Title
-- P_APPLICATION -- Application Name
-- P_DESC_FLEX_CD, DFF Context 


  SELECT fat.application_name
       , fdfv.title
       , fdfcv.descriptive_flex_context_code context
       , fdfcuv.column_seq_num num
       , fdfcuv.end_user_column_name name
       , fdfcuv.application_column_name column_
       , ffvs.flex_value_set_name value_set
       , ffvs.description value_set_description
       , fdfcuv.required_flag
       , fdfcuv.display_flag
       , fdfcuv.enabled_flag
       , fdfcuv.security_enabled_flag
       , fdfcuv.default_value
    FROM apps.fnd_descriptive_flexs_vl fdfv
       , applsys.fnd_application_tl fat
       , apps.fnd_descr_flex_contexts_vl fdfcv
       , apps.fnd_descr_flex_col_usage_vl fdfcuv
       , applsys.fnd_flex_value_sets ffvs
   WHERE fdfv.application_id = fat.application_id
     AND fdfcv.descriptive_flexfield_name = fdfv.descriptive_flexfield_name
     AND fdfcv.descriptive_flexfield_name = fdfcuv.descriptive_flexfield_name
     AND fdfcv.descriptive_flex_context_code =   
                                       fdfcuv.descriptive_flex_context_code
     AND fdfcuv.flex_value_set_id = ffvs.flex_value_set_id(+)
     AND fdfv.title = NVL(:P_TITLE,fdfv.title)
     AND fat.application_name = NVL(:P_APPLICATION,fat.application_name)
     AND fdfcv.descriptive_flex_context_code = 
                         NVL(:P_DESC_FLEX_CD,fdfcv.descriptive_flex_context_code)

ORDER BY fat.application_name

       , fdfv.title

       , fdfcv.descriptive_flex_context_code

       , fdfcuv.column_seq_num;

How to enable the MOAC parameter “Operating Unit” for a Concurrent Program in Oracle EBS R12

How to enable the MOAC parameter “Operating Unit” for a Concurrent Program in Oracle EBS R12

You can notice that the new field “Operating Unit” which is new field in R12 and by default is in disabled mode:-

With the introduction of multi-org access control in release 12, an applications responsibility
can access multiple operating units. Some concurrent programs have been enhanced to process multiple operating units simultaneously, while for other requests the operating unit must be specified when you run the program.

To support this, concurrent programs are defined with an operating unit mode of 'S' for single operating unit or 'M'(concurrent program will be in this mode by default) for multiple operating units. If the 'Operating Unit Mode' is not set for the concurrent program it will fail.

The “Operating Unit” field/parameter is known as “Reporting Context” in MOAC(Multi Org Access Control) terminology.

For a concurrent program though there may be a need to only run for Single Operating Unit, though the Responsibility has access to Multiple Operating Units’ data based on the “MO: Security Profile” profile option.

Running a particular concurrent program for only a Single Operating Unit can be achieved by a simple update statement or from the front-end application:-

--Execute the following update statement in the database for the particular concurrent program you want set to run for "Single Operating Unit"

UPDATE fnd_concurrent_programs

   SET multi_org_category = 'S'

 WHERE concurrent_program_name = '<your program name>';

Now we will see how we can achieve from the Oracle Applications front-end.

1. Login into application with System Administration responsibility (NOT System
     Administrator)
2. Navigate: Concurrent -> Programs

3. Query for short name of the concurrent program

4. Click on Update pencil icon of your program 

5. Under 'Update Concurrent Program' region, select Request tab
6. Under 'Request Setting' region, select 'Single' from the drop down of 'Operating Unit
     Mode' field
7. Save changes by clicking on 'Apply' button

8. Change responsibility to the responsibility where the “Concurrent Program” can be run, in

    this example “TESTOU” is assigned to “AR Super User” responsibility                              

9. Select the “Concurrent Program” from the “SRS Window”, now you can see that “Operating 

    Unit” field is enabled and you can see the Operating Units in the LOV

10. This particular selected Operating Unit value can be accessed using the standard MOAC API:MO_GLOBAL.get_current_org_id

Note:- The return value for the “Operating Unit” reporting context is corresponding “ORG_ID” for the selected “Operating Unit Name”

R12 SLA Tables connection to AP, AR, INV,Payments, Receiving

R12 SLA (Sub ledger Accounting)

1) All accounting performed before transfer to the GL. Accounting data generated and stored in “Accounting Events” tables prior to transfer to GL

2) Run “Create Accounting” to populate accounting events (SLA) tables. User can “View Accounting” only after “Create Accounting” is run. Create Accounting process

– Applies accounting rules

– Loads SLA tables, GL tables

– Creates detailed data per accounting rules, stores in SLA “distribution links” table

3) Below are the key tables for SLA in R12

XLA_AE_HEADERS xah

XLA_AE_LINES xal

XLA_TRANSACTION_ENTITIES xte

XLA_DISTRIBUTION_LINKS xdl

GL_IMPORT_REFERENCES gir

Below are the possible joins between these XLA Tables

xah.ae_header_id = xal.ae_header_id

xah.application_id = xal.application_id

xal.application_id = xte.application_id

xte.application_id = xdl.application_id

xah.entity_id = xte.entity_id

xah.ae_header_id = xdl.ae_header_id

xah.event_id = xdl.event_id

xal.gl_sl_link_id = gir.gl_sl_link_id

xal.gl_sl_link_table = gir.gl_sl_link_table

xah.application_id = (Different value based on Module)

xte.entity_code =

'TRANSACTIONS' or

'RECEIPTS' or

'ADJUSTMENTS' or

'PURCHASE_ORDER' or

'AP_INVOICES' or

'AP_PAYMENTS' or

'MTL_ACCOUNTING_EVENTS' or

'WIP_ACCOUNTING_EVENTS'

xte.source_id_int_1 =

'INVOICE_ID' or

'CHECK_ID' or

'TRX_NUMBER'

XLA_DISTRIBUTION_LINKS table join based on Source Distribution Types

xdl.source_distribution_type = 'AP_PMT_DIST'

and xdl.source_distribution_id_num_1 = AP_PAYMENT_HIST_DISTS.payment_hist_dist_id

---------------

xdl.source_distribution_type = 'AP_INV_DIST'

and xdl.source_distribution_id_num_1 = AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id

---------------

xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'

and xdl.source_distribution_id_num_1 = AR_DISTRIBUTIONS_ALL.line_id

and AR_DISTRIBUTIONS_ALL.source_id = AR_RECEIVABLE_APPLICATIONS_ALL.receivable_application_id

---------------

xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'

and xdl.source_distribution_id_num_1 = RA_CUST_TRX_LINE_GL_DIST_ALL.cust_trx_line_gl_dist_id

---------------

xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'

and xdl.source_distribution_id_num_1 = MTL_TRANSACTION_ACCOUNTS.inv_sub_ledger_id

---------------

xdl.source_distribution_type = 'WIP_TRANSACTION_ACCOUNTS'

and xdl.source_distribution_id_num_1 = WIP_TRANSACTION_ACCOUNTS.wip_sub_ledger_id

---------------

xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'

and xdl.source_distribution_id_num_1 = RCV_RECEIVING_SUB_LEDGER.rcv_sub_ledger_id

Queries:

In this post, we will check the Data related to the   Payable INVOICE ( Invoice_id = 166014 ) in Sub-Ledger Accounting (XLA). All the queries given in this post and their related posts were tested in R12.1.1 Instance.

XLA_EVENTS

SELECT DISTINCT xe.*

FROM   ap_invoices_all ai,

       xla_events xe,

       xla.xla_transaction_entities xte

WHERE  xte.application_id = 200

AND    xte.application_id   = xe.application_id

AND    ai.invoice_id        = '166014'

AND    xte.entity_code      = 'AP_INVOICES'

AND    xte.source_id_int_1  = ai.invoice_id

AND    xte.entity_id        = xe.entity_id

ORDER BY

       xe.entity_id,

       xe.event_number;

XLA_AE_HEADERS 

 

SELECT DISTINCT xeh.*

FROM   xla_ae_headers xeh,

       ap_invoices_all ai,

       xla.xla_transaction_entities xte

WHERE  xte.application_id = 200

AND    xte.application_id   = xeh.application_id

AND    ai.invoice_id        = '166014'

AND    xte.entity_code      = 'AP_INVOICES'

AND    xte.source_id_int_1  = ai.invoice_id

AND    xte.entity_id        = xeh.entity_id

ORDER BY

       xeh.event_id,

       xeh.ae_header_id ASC; 

XLA_AE_LINES

SELECT DISTINCT xel.*,

       fnd_flex_ext.get_segs('SQLGL','GL#', '50577' , xel.code_combination_id) "Account"

FROM   xla_ae_lines xel,

       xla_ae_headers xeh,

       ap_invoices_all ai,

       xla.xla_transaction_entities xte

WHERE  xte.application_id = 200

AND    xel.application_id   = xeh.application_id

AND    xte.application_id   = xeh.application_id

AND    ai.invoice_id        = '166014'

AND    xel.ae_header_id     = xeh.ae_header_id

AND    xte.entity_code      = 'AP_INVOICES'

AND    xte.source_id_int_1  = ai.invoice_id

AND    xte.entity_id        = xeh.entity_id

ORDER BY

       xel.ae_header_id,

       xel.ae_line_num ASC;

XLA_DISTRIBUTION_LINKS

SELECT DISTINCT xdl.*

FROM   xla_distribution_links xdl,

       xla_ae_headers xeh,

       ap_invoices_all ai,

       xla.xla_transaction_entities xte

WHERE  xte.application_id = 200

AND    xdl.application_id   = xeh.application_id

AND    xte.application_id   = xeh.application_id

AND    ai.invoice_id        = '166014'

AND    xdl.ae_header_id     = xeh.ae_header_id

AND    xte.entity_code      = 'AP_INVOICES'

AND    xte.source_id_int_1  = ai.invoice_id

AND    xte.entity_id        = xeh.entity_id

ORDER BY

       xdl.event_id,

       xdl.a_header_id,

       xdl.ae_line_num ASC;

XLA_TRANSACTION_ENTITIES 

SELECT DISTINCT xte.*

FROM   ap_invoices_all ai,

       xla.xla_transaction_entities xte

WHERE  xte.application_id = 200

AND    ai.invoice_id        = '166014'

AND    xte.entity_code      = 'AP_INVOICES'

AND    xte.source_id_int_1  = ai.invoice_id;

XLA_ACCOUNTING_ERRORS

SELECT DISTINCT xae.*

FROM   ap_invoices_all ai,

       xla_events xe,

       xla.xla_transaction_entities xte,

       xla_accounting_errors xae

WHERE  xte.application_id = 200

AND    xae.application_id   = xte.application_id

AND    xte.application_id   = xe.application_id

AND    ai.invoice_id        = '166014'

AND    xe.event_id          = xae.event_id

AND    xte.entity_code      = 'AP_INVOICES'

AND    xte.source_id_int_1  = ai.invoice_id

AND    xte.entity_id        = xe.entity_id;