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

Concurrent Program with Parameter Details

Concurrent Program Scripts

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: