Create Accounting or SLA Exception Queries

Leave a reply

SLA Exception Queries

AR Create Accounting Errors:

SELECT led.name ledger_name,

ae.encoded_msg,

xe.event_type_code,

xe.event_status_code,

xe.process_status_code,

xte.entity_code,

hro.name ou_name,

xte.transaction_number,

xe.event_date,

xte.entity_code,

xte.source_id_int_1

FROM xla_events xe,

xla_accounting_errors ae,

xla.xla_transaction_entities xte,

gl_ledgers led,

hr_operating_units hro

WHERE ae.application_id = 222

AND led.ledger_id = xte.ledger_id

AND ae.event_id = xe.event_id

AND xte.entity_id = xe.entity_id

AND xte.ledger_id = ae.ledger_id

AND hro.organization_id(+) = xte.security_id_int_1;

Payables Create Accounting Errors:

SELECT led.name ledger_name,

ae.encoded_msg,

xe.event_type_code,

xe.event_status_code,

xe.process_status_code,

xte.entity_code,

hro.name OU_NAME,

xte.transaction_number ,

xe.event_date,

xte.entity_code,

xte.source_id_int_1

FROM XLA_EVENTS xe,

XLA_ACCOUNTING_ERRORS ae,

xla.XLA_TRANSACTION_ENTITIES xte,

GL_LEDGERS led,

HR_OPERATING_UNITS hro

WHERE ae.application_id = 200

AND led.ledger_id = xte.ledger_id

AND ae.event_id = xe.event_id

AND xte.entity_id = xe.entity_id

AND xte.ledger_id = ae.ledger_id

AND hro.organization_id(+) = xte.security_id_int_1;

FA Create Accounting Errors:

SELECT led.name ledger_name,

ae.encoded_msg,

xe.event_type_code,

xe.event_status_code,

xe.process_status_code,

xte.entity_code,

hro.name OU_NAME,

xte.transaction_number ,

xe.event_date,

xte.entity_code,

xte.source_id_int_1

FROM XLA_EVENTS xe,

XLA_ACCOUNTING_ERRORS ae,

xla.XLA_TRANSACTION_ENTITIES xte,

GL_LEDGERS led,

HR_OPERATING_UNITS hro

WHERE ae.application_id = 140

AND led.ledger_id = xte.ledger_id

AND ae.event_id = xe.event_id

AND xte.entity_id = xe.entity_id

AND xte.ledger_id = ae.ledger_id

AND hro.organization_id(+) = xte.security_id_int_1;

Cost Management Create Accounting Errors:

SELECT led.name ledger_name,

ae.encoded_msg,

xe.event_type_code,

xe.event_status_code,

xe.process_status_code,

xte.entity_code,

hro.name ORG_NAME,

xte.transaction_number ,

xe.event_date,

xte.entity_code,

xte.source_id_int_1

FROM xla_events xe,

xla_accounting_errors ae,

xla.xla_transaction_entities xte,

gl_ledgers led,

hr_all_organization_units hro

WHERE ae.application_id = 707

AND led.ledger_id = xte.ledger_id

AND ae.event_id = xe.event_id

AND xte.entity_id = xe.entity_id

AND xte.ledger_id = ae.ledger_id

AND hro.organization_id(+) = xte.security_id_int_1 ;

Channel Revenue Management Create Accounting Errors:

SELECT led.name ledger_name,

oca.claim_number "CLAIM_NUMBER/OFFER_CODE",

NULL OFFER_NAME,

ae.encoded_msg,

xe.event_type_code,

xe.event_status_code,

xe.process_status_code,

xte.entity_code,

hro.name OU_NAME,

xte.transaction_number "UTILIZATION_ID/CLAIM_ID",

xe.event_date,

oca.creation_date "CLAIM/accrual_date",

oca.acctd_amount Amount,

oca.currency_code Currency_code,

xte.entity_code

FROM XLA_EVENTS xe,

XLA_ACCOUNTING_ERRORS ae,

xla.XLA_TRANSACTION_ENTITIES xte,

GL_LEDGERS led,

HR_OPERATING_UNITS hro,

OZF_CLAIMS_ALL oca

WHERE ae.application_id = 682

AND led.ledger_id = xte.ledger_id

AND ae.event_id = xe.event_id

AND xte.entity_id = xe.entity_id

AND xte.ledger_id = ae.ledger_id

AND hro.organization_id(+) = xte.security_id_int_1

AND xte.entity_code = 'CLAIM_SETTLEMENT'

AND oca.claim_id = xte.source_id_int_1

UNION ALL

SELECT led.name ledger_name,

oz.name,

oz.description OFFER_NAME,

ae.encoded_msg,

xe.event_type_code,

xe.event_status_code,

xe.process_status_code,

xte.entity_code,

hro.name OU_NAME,

xte.transaction_number "UTILIZATION_ID/CLAIM_ID",

xe.event_date,

ou.creation_date "CLAIM/accrual_date",

ou.plan_curr_amount Amount,

ou.plan_currency_code Currency_code,

xte.entity_code

FROM XLA_EVENTS xe,

XLA_ACCOUNTING_ERRORS ae,

xla.XLA_TRANSACTION_ENTITIES xte,

GL_LEDGERS led,

HR_OPERATING_UNITS hro,

OZF_FUNDS_UTILIZED_ALL_B ou,

OZF_OFFERS_V oz

WHERE ae.application_id = 682

AND led.ledger_id = xte.ledger_id

AND ae.event_id = xe.event_id

AND xte.entity_id = xe.entity_id

AND xte.ledger_id = ae.ledger_id

AND hro.organization_id(+) = xte.security_id_int_1

AND ou.plan_id = oz.list_header_id

AND ou.utilization_id = xte.source_id_int_1

AND xte.entity_code = 'ACCRUAL';

copyright@www.alloracleapps.com

SLA Methods Query

Useful Month End Process Queries

1 Reply

Useful Month End Process Queries – Operations

1. Transaction Struck in Material Transactions Open Interface: Continue reading →

Setting Up Multi Org Structure in R12 (MOAC)

Assemble To Order

Form Functions attached to a Responsibility

Leave a reply

Form Functions attached to a Responsibility

Using the below Query we can get all the form functions that are attached to a Responsibility including Sub Menus and Excluding Menu Exclusions. Continue reading →

+125

Concurrent Request Details

SLA Methods Query

Leave a reply

SLAM Details till Journal Line Definitions

SELECT acct_mtd.NAME,

act_mtd_rule.application_name,

act_mtd_rule.product_rule_name,

act_mtd_rule.start_date_active,

ldf.event_class_name,

ldf.event_type_name,

ldf.validation_status,

aad_lda.line_definition_name,

aad_lda.line_definition_desc,

djlt.accounting_line_name,

djlt.accounting_line_owner,

jldadr.flexfield_segment_name,

jldadr.segment_rule_name,

jldadr.segment_rule_owner,

jldadr.segment_rule_desc,

srf.NAME,

DECODE ( srf.segment_rule_type_code,

'S', 'Source',

'C', 'Account Derivation Rule',

srf.segment_rule_type_code

) "Value Type"

FROM xla_acctg_methods_fvl acct_mtd,

xla_acctg_method_rules_fvl act_mtd_rule,

xla_product_rules_fvl prod_rule,

xla_prod_acct_headers_fvl prod_acct,

xla_aad_line_defn_assgns_f_v aad_lda,

xla_line_definitions_f_v ldf,

xla_line_defn_jlt_assgns_f_v djlt,

xla_line_defn_adr_assgns_f_v jldadr,

xla_seg_rules_fvl srf,

xla_seg_rule_details srd

WHERE 1 = 1

AND acct_mtd.accounting_method_code = act_mtd_rule.accounting_method_code

AND prod_rule.product_rule_code = act_mtd_rule.product_rule_code

AND prod_rule.product_rule_code = prod_acct.product_rule_code

AND prod_acct.product_rule_code = aad_lda.product_rule_code

AND prod_acct.event_type_code = aad_lda.event_type_code

AND aad_lda.line_definition_code = ldf.line_definition_code

AND aad_lda.event_type_code = ldf.event_type_code

AND ldf.line_definition_code = djlt.line_definition_code

AND djlt.accounting_line_code = jldadr.accounting_line_code

AND jldadr.line_definition_code = djlt.line_definition_code

AND srf.segment_rule_code = jldadr.segment_rule_code

AND srf.segment_rule_code = srd.segment_rule_code

AND acct_mtd.NAME = <Accounting Method Name>

AND act_mtd_rule.product_rule_name = <Product Rule Name>

ORDER BY flexfield_segment_name;

+73

Create Accounting or SLA Exception Queries

Employee and Supervisor Details

Leave a reply

Employee and Assignment Details

Using the below script we can find the Active Employee, Assignments and his Supervisor details.

SELECT fu.user_name,

per.full_name,

per.employee_number,

per1.full_name supervisor_name,

per1.employee_number supervisor_number,

gcc.concatenated_segments Supervisor_default_expense_account

FROM fnd_user fu,

per_all_people_f per ,

per_all_assignments_f pf ,

per_all_people_f per1,

per_all_assignments_f pf1,

gl_code_combinations_kfv gcc

WHERE fu.employee_id = per.person_id

AND SYSDATE BETWEEN per.effective_start_date AND per.effective_end_date

AND SYSDATE BETWEEN pf.effective_start_date AND pf.effective_end_date

AND pf.person_id = per.person_id

AND pf.supervisor_id = per1.person_id

AND per1.person_id = pf1.person_id

AND SYSDATE BETWEEN per1.effective_start_date AND per1.effective_end_date

AND SYSDATE BETWEEN pf1.effective_start_date AND pf1.effective_end_date

AND pf1.default_code_comb_id = gcc.code_combination_id

+82

FNDLOAD and WFLOAD

Send Email using UTL_SMTP

Query to Find Schema and its status

Trace File Location for Request ID

Current Running SQL for a Concurrent Program

ALL ORACLE APPS

All Oracle Apps

Create Accounting or SLA Exception Queries

SLA Exception Queries

Related posts:

Useful Month End Process Queries

Useful Month End Process Queries – Operations

Related posts:

Form Functions attached to a Responsibility

Form Functions attached to a Responsibility

Related posts:

SLA Methods Query

Related posts:

Employee and Supervisor Details

Employee and Assignment Details

Related posts: