, , , , , ,

Query for Link Between SLA and Opm Accounting preprocessor Tables in R12

SELECT GEH.REFERENCE_NO,
  GEH.TRANSACTION_ID,
  GEH.TRANSACTION_DATE,
  gel.line_number,
  gel.journal_line_type,
  gel.entered_amount,
  gel.accounted_amount,
  FND_FLEX_EXT.GET_SEGS(‘SQLGL’, ‘GL#’,gcc.chart_of_accounts_id,gcc.code_combination_id) account,
  GL_FLEXFIELDS_PKG.get_concat_description( gcc.chart_of_accounts_id, gcc.code_combination_id) acc_description
FROM gl_code_combinations gcc,
  xla_ae_lines al,
  xla_distribution_links dl,
  gmf_xla_extract_headers geh,
  gmf_xla_extract_lines gel
WHERE gcc.code_combination_id       = al.code_combination_id
AND al.ae_header_id                 = dl.ae_header_id
AND al.ae_line_num                  = dl.ae_line_num
AND dl.event_id                     = geh.event_id
AND dl.application_id               = 555
AND dl.source_distribution_type     = geh.entity_code
AND dl.source_distribution_id_num_1 = gel.line_id
AND geh.header_id                   = gel.header_id
and geh.event_id                    = gel.event_id
–AND gel.journal_line_type          IN (‘COGS’,’DCOG’)
AND geh.transaction_date           >= TO_DATE(‘&&fromDate’
  ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’) –period start date 01/01/2011
AND geh.transaction_date <= TO_DATE(‘&&toDate’
  ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’) –Period end date 01/31/2011 
/by
, , , , , , , , , , , , ,

R12 – Period End Closing Checklist in Oracle Apps

Product family
Product Code
Step No
Step name






1
Order Management (OM):




Order Management
ONT
1.1
Complete the sales orders
Order Management
ONT
1.2
Import Sales Order
Order Management
ONT
1.3
Pre-Billing Acceptance
Shipping
WSH
1.4
Complete the shipping transactions
Order Management
ONT
1.5
Prepare the link with AR




Order Management
ONT
1.5.1
Check run of Workflow background engine
Receivables
AR
1.5.2
Run the Invoice Interface/Autoinvoice
Receivables
AR
1.5.3
Verify the Autoinvoice Exceptions






2
Receivables (AR):




Receivables
AR
2.1
Complete all Receivables transactions for the period being closed
Subledger Accounting
SLA
2.2
Create accounting
Subledger Accounting
SLA
2.3
Transfer to GL
Receivables
AR
2.4
Manage the Receivables Period






3
Purchasing (PO):




Purchasing
PO
3.1
Complete all transactions for Oracle Purchasing
Subledger Accounting
SLA
3.2
Create accounting for Payables
Subledger Accounting
SLA
3.3
Transfer to GL
Purchasing/Payables
PO/AP
3.4
Purchasing Period management




Payables
AP
3.4.1
Close the Payables period
Purchasing
PO
3.4.2
Run the Receipt Accruals – Period-End Report
Purchasing
PO
3.4.3
Manage the PO Periods
Payables
AP
3.4.4
Open next AP Period






4
Transactions control:






4.1
Enter all transactions in eBS:




Inventory
INV
4.1.1
Enter the pending cycle counts
Inventory
INV
4.1.2
Check run of the Material Transaction Manager
Inventory
INV
4.1.3
Check run of the Move Transaction Manager 
Inventory
INV
4.1.4
Check run of the Lot Move Transaction Manager    
Inventory
INV
4.1.5
Check run of the Cost manager (mandatory)




Inventory
INV
4.2
Check the pending or in error transactions
Inventory
INV
4.3
Unprocessed Material (resolution required)
Inventory
INV
4.4
Uncosted material transactions (resolution required)
Costing
CST
4.5
Pending LCM Interface Transactions (resolution required)
Work In Process
WIP
4.6
Pending WIP Costing transactions (resolution required)
Shop Floor Management
WSM
4.7
Pending WSM interface (resolution required)






4.7.1
Run the Import WIP Lot Transactions


4.7.2
Run the (WSM) Pending Transactions Report




Shipping
WSH
4.8
Unprocessed Shipping Transactions (by default, resolution required)




Shipping
WSH
4.8.1
Search delivery line with the status Shipped
Shipping
WSH
4.8.2
Run the Interface Trip Stop SRS




Inventory
INV
4.9
Pending material transactions for this period (resolution recommended)
Work In process
WIP
4.10
Pending Shop Floor Move (resolution recommended)
Purchasing
PO
4.11
Pending Receiving Transactions (resolution recommended)
Project Manufacturing
PJM
4.12
Project Manudacturing – Cost Collector




Project Manufacturing
PJM
4.12.1
Run the Cost Collection Manager
Project Manufacturing
PJM
4.12.2
Import the transactions in Projects






5
Cost Management:




Costing
CST
5.1
Cost Of Goods Sold




Costing
CST
5.1.1
Record the Order Management Transactions
Costing
CST
5.1.2
Collect the Revenue Recognition information
Costing
CST
5.1.3
Generate the COGS Recognition events




Costing
CST
5.2
Create accounting
Costing
CST
5.3
Transfer to GL






6
 Inventory Open / Close period:




Inventory
INV
6.1
Open the next period
Inventory
INV
6.2
Close the period
Inventory
INV
6.3
Open or Close several periods in the same time




Inventory
INV
6.3.1
Hierarchy of Organizations
Costing
CST
6.3.2
Open several periods
Costing
CST
6.3.3
Close several periods
/by
, , ,

R12 – Difference between Draft, Final, etc in Create Accounting

Part -I: Online Accounting: (For single entity)

DRAFT will create Journal Entries, which are NOT final, which means they are NOT ready to be transferred to GL.
  • You can see the accounting in XLA_AE_HEADERS and XLA_AE_LINES.

         XLA_AE_HEADERS.accounting_entry_status_code is ‘D’
         XLA_EVENTS.process_status_code is ‘D’
         XLA_EVENTS.event_status_code is ‘U’ 

  • You can run create accounting on this transaction again and again, which will delete the old journal entries and create new ones.
  • You can’t transfer these journal entries to GL.
FINAL will create journal entries, which can be transferred to GL.

  • You can see the accounting in XLA_AE_HEADERS and XLA_AE_LINES.

         XLA_AE_HEADERS.accounting_entry_status_code is ‘F’
         XLA_EVENTS.process_status_code is ‘P’
         XLA_EVENTS.event_status_code is ‘P’

  • Once it is finally accounted you can NOT run create accounting on the particular transaction (specifically on that event).
  • You can transfer them to GL using Transfer Journal Entries to GL program.
FINAL POST will create journal entries in Final Mode, Transfer them to GL and Post them.
  • You can see the accounting in XLA_AE_HEADERS and XLA_AE_LINES.

         XLA_AE_HEADERS.accounting_entry_status_code is ‘F’
         XLA_EVENTS.process_status_code is ‘P’
         XLA_EVENTS.event_status_code is ‘P’

  • Once it is finally accounted you can NOT run create accounting on the particular transaction (specifically on that event).
  • It will transfer the journal entries to GL using Journal Import and you can find the data in GL_JE_HEADERS and GL_JE_LINES.

         XLA_AE_HEADERS.transfer_status_code is Y.
         It will post to gl_balances also (GL_JE_HEADERS.status is ‘P’).

Part -II: Create Accounting (Concurrent Program): (For more entities)

1. Accounting Mode: Draft
It is same as Draft online accounting.

2. Accounting Mode: Final, Transfer to GL: No
It is same as Final online accounting.

3. Accounting Mode: Final, Transfer to GL: Yes, Post to GL: No

  • It will create journal entries in Final mode, transfer them to GL.
  • You can see the accounting in XLA_AE_HEADERS and XLA_AE_LINES.
  • Once it is finally accounted you can NOT run create accounting on the particular transaction (specifically on that event).

         XLA_AE_HEADERS.accounting_entry_status_code is ‘F’
         XLA_EVENTS.process_status_code is ‘P’
         XLA_EVENTS.event_status_code is ‘P’

  • It will transfer the journal entries to GL using Journal Import and you can find the data in GL_JE_HEADERS and GL_JE_LINES.

         XLA_AE_HEADERS.transfer_status_code is ‘Y’
         GL_JE_HEADERS.status is ‘U’.

4. Accounting Mode: Final, Transfer to GL: Yes, Post to GL: Yes


It is same as Final Post online accounting.
/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
, , , , , , ,

R12 SLA Tables join conditions to AR, AP, INV,Payments and Receiving

Here I am trying to describe R12 SLA(Sub Ledger Accounting) Procedure.
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.

Hope this will help you.
/by