LIST OF USEFUL QUERIES IN ORACLE APPLICATIONS

Get the Account Description:

SELECT apps.gl_flexfields_pkg.get_description_sql(&char_of_account_id,

1,

gcc.segment1) seg1,

gl_flexfields_pkg.get_description_sql(&char_of_account_id,

2,

gcc.segment2) seg2,

gl_flexfields_pkg.get_description_sql(&char_of_account_id,

3,

gcc.segment3) seg3,

gl_flexfields_pkg.get_description_sql(&char_of_account_id,

4,

gcc.segment4) seg4,

gl_flexfields_pkg.get_description_sql(&char_of_account_id,

5,

gcc.segment5) seg5

FROM gl_code_combinations gcc

WHERE gcc.segment4 = '25130' 

--gcc.code_combination_id = &code_combination_id;

Get inventory accounting entries linked to GL:

select mta.transaction_id,

mmt.organization_id,

msi.segment1,

mta.transaction_date,

mta.primary_quantity,

gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' ||

gcc.segment4 || '.' || gcc.segment5 account,

DECODE(SIGN(mta.transaction_value),

1,

mta.transaction_value,

0,

0,

NULL,

DECODE(SIGN(mta.base_transaction_value),

1,

mta.base_transaction_value,

NULL)) Entered_Dr,

DECODE(SIGN(mta.transaction_value),

-1,

(-1 * mta.transaction_value),

0,

0,

NULL,

DECODE(SIGN(mta.base_transaction_value),

-1,

(-1 * mta.base_transaction_value))) Entered_Cr,

DECODE(SIGN(mta.base_transaction_value),

1,

mta.base_transaction_value,

0,

0,

NULL) Accounted_Dr,

DECODE(SIGN(mta.base_transaction_value),

-1,

(-1 * mta.base_transaction_value),

0,

0,

NULL) Accounted_Cr,

gh.currency_code,

mtt.transaction_type_name,

decode(mta.gl_batch_id, -1, 'N', 'Y') "Transfered_Flag",

mta.gl_batch_id,

gh.je_header_id

from inv.mtl_material_transactions mmt,

inv.mtl_transaction_types mtt,

inv.mtl_system_items_b msi,

inv.mtl_transaction_accounts mta,

gl.gl_code_combinations gcc,

gl.gl_je_batches gb,

gl.gl_je_headers gh,

gl.gl_je_lines gl,

gl.gl_import_references gr

where mmt.organization_id = msi.organization_id

and msi.inventory_item_id = mmt.inventory_item_id

and mmt.transaction_id = mta.transaction_id

and gcc.code_combination_id = mta.reference_account

and mtt.transaction_type_id = mmt.transaction_type_id

and gb.je_batch_id = gh.je_batch_id

and gh.je_header_id = gl.je_header_id

and gl.code_combination_id = mta.reference_account

and mta.gl_batch_id =

to_number(substr(gb.name, 1, instr(gb.name, ' ') - 1))

and gh.je_Category = 'MTL'

and gh.je_source = 'Inventory'

and gh.name = 'XXX' ---REPLACE XXX WITH NAME

and gl.je_line_num = gr.je_line_num

and gr.je_header_id = gl.je_header_id

and gr.je_line_num = gl.je_line_num

and mta.gl_batch_id = gr.reference_1

and gh.period_name = '&period_name' -- ENTER THE PERIOD

and upper(gb.name) like upper('%&gl_batch_name%')

order by 1;

 

General Ledger and Account Payables connected via SLA module:

All SLA transactions queries is linked using XLA_TRANSACTION_ENTITIES table.

TRANSACTIONS, RECEIPTS, ADJUSTMENTS, PURCHASE_ORDER, AP_INVOICES,AP_PAYMENTS. This demonstrates this is a common table for linking Subledger Transaction such as AR Transactions through to their accounting entries.

All tables used in this activity

xla.xla_transaction_entities ,

 xla.xla_events ,

 xla_ae_headers , 

xla_ae_lines , 

gl_import_references

AP to GL data transfer:

SELECT glcc.segment1 "Company"

, gjjlv.period_name "PERIOD"

, gjb.name "JOURNAL BATCH NAME"

, gjjlv.header_name "JOURNAL SOURCE"

, gjjlv.line_reference_1 "SUPPLIER NAME"

, gjjlv.currency_code "CURRENCY"

, invoice_type_lookup_code "TRANSACTION TYPE"

, gjjlv.line_reference_5 "TRANSACTION NUMBER"

, aia.invoice_date "TRANSACTION DATE"

-- , gjjlv.je_source "SOURCE"

, gjjlv.line_entered_dr "ENTERED DEBIT"

, gjjlv.line_entered_cr "ENTERED CREDIT"

, gjjlv.line_accounted_dr "ACCOUNTED_DEBIT"

, gjjlv.line_accounted_cr "ACCOUNTED_CREDIT"

,glcc.concatenated_segments "CHARGE ACCOUNT"

FROM apps.GL_JE_JOURNAL_LINES_V gjjlv

, gl_je_lines gjl

, ap_ae_lines_all aala

, gl_je_headers gjh

, gl_je_batches gjb

, ap_invoices_all aia

, apps.gl_code_combinations_KFV glcc

, po_vendors pv

WHERE gjl.gl_sl_link_table = 'APECL'

AND gjl.period_name ='NOV-2008'--period

--and gjjlv.currency_code = --currency code

AND gjb.je_batch_id = gjh.je_batch_id

AND gjh.je_header_id = gjl.je_header_id

AND gjh.period_name = gjl.period_name

AND gjh.set_of_books_id = gjl.set_of_books_id

AND glcc.code_combination_id = gjl.code_combination_id

AND gjjlv.je_batch_id = gjh.je_batch_id

AND gjjlv.je_header_id = gjh.je_header_id

AND gjjlv.line_je_line_num = gjl.je_line_num

AND gjh.period_name = gjjlv.period_name

AND gjh.set_of_books_id = gjjlv.set_of_books_id

AND glcc.code_combination_id = gjjlv.line_code_combination_id

AND aala.code_combination_id = gjl.code_combination_id

AND aala.gl_sl_link_id = gjl.gl_sl_link_id

AND aala.reference5 = aia.invoice_num

AND gjh.set_of_books_id = aia.set_of_books_id

AND pv.vendor_id = aia.vendor_id

AND gjjlv.line_reference_1 = pv.vendor_name

ORDER BY aia.invoice;

Sub ledger transfer to GL:

SELECT gjjlv.period_name period_name

, gjb.name batch_name

, gjjlv.header_name Journal_Entry

, gjjlv.je_source Source

, gjjlv.line_entered_dr Entered_Debit

, gjjlv.line_entered_cr Entered_credit

, gjjlv.line_accounted_dr Accounted_Debit

, gjjlv.line_accounted_cr Accounted_Credit

, gjjlv.currency_code Currency

, fasv.TRX_TYPE_NAME Trans_Type

, fasv.TRX_NUMBER_DISPLAYED Transaction_Number

, fasv.TRX_DATE Transaction_Date

, fasv.ASSET_NUMBER Reference

, glcc.CONCATENATED_SEGMENTS

,gjjlv.created_by

FROM apps.GL_JE_JOURNAL_LINES_V gjjlv

, gl_je_lines gje

, apps.fa_ael_gl_v fasv

, gl_je_headers gjh

, gl_je_batches gjb

, apps.gl_code_combinations_kfv glcc

WHERE gjh.period_name BETWEEN 'SEP-2008' AND 'OCT-2008'

AND glcc.code_combination_id = gje.code_combination_id

AND glcc.code_combination_id = fasv.code_combination_id

AND gjh.JE_BATCH_ID = gjb.JE_BATCH_ID

AND gjh.JE_HEADER_ID = gje.JE_HEADER_ID

AND gjh.period_name = gjb.default_period_name

AND gjh.period_name = gje.period_name

AND gjjlv.period_name = gjh.period_name

AND gjjlv.je_batch_id = gjh.je_batch_id

AND gjjlv.je_header_id = gjh.je_header_id

AND gjjlv.LINE_JE_LINE_NUM = gje.je_line_num

AND gjjlv.je_header_id = fasv.je_header_id

AND glcc.segment1='22';

Drilldown from GL to  receiving transactions:

SELECT b.NAME je_batch_name,

b.description je_batch_description,

b.running_total_accounted_dr je_batch_total_dr,

b.running_total_accounted_cr je_batch_total_cr,

b.status je_batch_status,

b.default_effective_date je_batch_effective_date,

b.default_period_name je_batch_period_name,

b.creation_date je_batch_creation_date,

u.user_name je_batch_created_by,

h.je_category je_header_category,

h.je_source je_header_source,

h.period_name je_header_period_name,

h.NAME je_header_journal_name,

h.status je_header_journal_status,

h.creation_date je_header_created_date,

u1.user_name je_header_created_by,

h.description je_header_description,

h.running_total_accounted_dr je_header_total_acctd_dr,

h.running_total_accounted_cr je_header_total_acctd_cr,

l.je_line_num je_lines_line_number,

l.ledger_id je_lines_ledger_id,

glcc.concatenated_segments je_lines_ACCOUNT,

l.entered_dr je_lines_entered_dr,

l.entered_cr je_lines_entered_cr,

l.accounted_dr je_lines_accounted_dr,

l.accounted_cr je_lines_accounted_cr,

l.description je_lines_description,

glcc1.concatenated_segments xla_lines_account,

xlal.accounting_class_code xla_lines_acct_class_code,

xlal.accounted_dr xla_lines_accounted_dr,

xlal.accounted_cr xla_lines_accounted_cr,

xlal.description xla_lines_description,

xlal.accounting_date xla_lines_accounting_date,

xlate.entity_code xla_trx_entity_code,

xlate.source_id_int_1 xla_trx_source_id_int_1,

xlate.source_id_int_2 xla_trx_source_id_int_2,

xlate.source_id_int_3 xla_trx_source_id_int_3,

xlate.security_id_int_1 xla_trx_security_id_int_1,

xlate.security_id_int_2 xla_trx_security_id_int_2,

xlate.transaction_number xla_trx_transaction_number,

rcvt.transaction_type rcv_trx_transaction_type,

rcvt.transaction_date rcv_trx_transaction_date,

rcvt.quantity rcv_trx_quantity,

rcvt.shipment_header_id rcv_trx_shipment_header_id,

rcvt.shipment_line_id rcv_trx_shipment_line_id,

rcvt.destination_type_code rcv_trx_destination_type_code,

rcvt.po_header_id rcv_trx_po_header_id,

rcvt.po_line_id rcv_trx_po_line_id,

rcvt.po_line_location_id rcv_trx_po_line_location_id,

rcvt.po_distribution_id rcv_trx_po_distribution_id,

rcvt.vendor_id rcv_trx_vendor_id,

rcvt.vendor_site_id rcv_trx_vendor_site_id

FROM

gl_je_batches b,

gl_je_headers h,

gl_je_lines l,

fnd_user u,

fnd_user u1,

gl_code_combinations_kfv glcc,

gl_code_combinations_kfv glcc1,

gl_import_references gir,

xla_ae_lines xlal,

xla_ae_headers xlah,

xla_events xlae,

xla.xla_transaction_entities xlate,

rcv_transactions rcvt

WHERE

b.created_by = u.user_id

AND h.created_by = u1.user_id

AND b.je_batch_id = h.je_batch_id

AND h.je_header_id = l.je_header_id

AND l.code_combination_id = glcc.code_combination_id

AND l.je_header_id = gir.je_header_id

AND l.je_line_num = gir.je_line_num

AND gir.gl_sl_link_table = xlal.gl_sl_link_table

AND gir.gl_sl_link_id = xlal.gl_sl_link_id

AND xlal.application_id = xlah.application_id

AND xlal.ae_header_id = xlah.ae_header_id

AND xlal.code_combination_id = glcc1.code_combination_id

AND xlah.application_id = xlae.application_id

AND xlah.event_id = xlae.event_id

AND xlae.application_id = xlate.application_id

AND xlae.entity_id = xlate.entity_id

AND xlate.source_id_int_1 = rcvt.transaction_id

AND h.je_category = 'Receiving'

AND b.default_period_name = '01-JUN-2010'

ORDER BY h.je_category;

AR to Gl data transfer:

SELECT

wnd.delivery_id,

wnd.name delivery_name,

wdd.source_header_number

so_order_number,

oola.line_number so_line_number,

wdd.source_header_id so_header_id,

wdd.source_line_id so_line_id,

wdd.shipping_instructions,

wdd.inventory_item_id,

wdd.requested_quantity_uom,

msi.description item_description,

msi.revision_qty_control_code ,

wdd.ship_method_code carrier,

wdd.shipment_priority_code priority,

wdd.organization_id,

wnd.initial_pickup_location_id,

wdd.released_status,

wdd.source_code

FROM mtl_system_items_vl msi,

oe_order_lines_all oola,

wsh_delivery_details wdd,

wsh_delivery_assignments Wda,

wsh_new_deliveries wnd

WHERE wnd.delivery_id =18910

AND wda.delivery_id = wnd.delivery_id(+)

AND wdd.delivery_detail_id = wda.delivery_detail_id

AND wdd.inventory_item_id = msi.inventory_item_id(+)

AND wdd.organization_id = msi.organization_id(+)

AND wdd.source_line_id = oola.line_id

AND wdd.source_header_id = oola.header_id

 

Payroll is moved to GL:

This is simple 10 steps process:

1) record get inserted in the following parameters in PAY_ACTION_PARAMETERS

insert into PAY_ACTION_PARAMETERS values ('TGL_DATE_USED','E');

insert into PAY_ACTION_PARAMETERS values ('TGL_REVB_ACC_DATE','Y');

insert into PAY_ACTION_PARAMETERS values ('TGL_GROUP_ID','Y');

2) Setup your costing flexfield qualifiers.

3) Create an element.

4) Link the element.

Check the costing radio button.

Check the transfer to GL checkbox.

Enter the values for the costing/balancing field.

5) Run the quickpay/pre-payments/costing process

6) Track the ASSIGNMENT_ACTION_ID and PAYROLL_ACTION_ID navigating to

View-->Payroll Process Results/Assignment Process Results

7) Verify that the costing details are created in PAY_COSTS table.

SELECT * FROM PAY_COSTS WHERE ASSIGNMENT_ACTION_ID = &assignment_action_id;

Also Refer Note:294553.1

8) Run the Payroll transfer to GL process

9) Verifity that the process has created data in gl_interfaces

select * from gl_interface

where set_of_books_id = &set_of_books_id

and group_id =&enter_group_id

10) Import the same in GL by navigating to

Journal-->Import.

Relation between order number and contract number:

Relation between Order Number (From Order Management) and Contract Number (From Service Contracts module).

The relationship is maintained in the table okc_k_rel_objs. following is the sql that will give you the contract number when provided order number.