Delivery Tax Accounting entry extract query
SELECT C.Name, C.JE_BATCH_ID, j.trx_id transaction_id,j.trx_number GRN,c.je_source, c.je_category, a.effective_date AS accounting_date,
a.creation_date AS creation_date,a.PERIOD_NAME,b.segment5,
(SELECT ffvl.description
FROM fnd_flex_values_vl ffvl
WHERE 1 = 1
AND flex_value_set_id = 1015213
AND flex_value = b.segment5) acc_desc, a.accounted_dr,
a.accounted_cr, gcc.concatenated_segments acct_code_combination,
a.description, a.je_line_num
FROM gl_je_lines a,
gl.gl_code_combinations b,
gl_je_headers c,
gl_code_combinations_kfv gcc,
jai_tax_det_factors j
where 1=1
AND a.code_combination_id = b.code_combination_id
AND a.je_header_id = c.je_header_id
AND a.code_combination_id = gcc.code_combination_id
AND a.reference_3= det_factor_id||''
and trx_id in ('4560827') ;
Select * from GL_JE_BATCHES where JE_BATCH_ID='2822890'
Schedule Payment vs Receivable Amount Mismatch
SELECT r.trx_number, r.trx_date, r.org_id,h.name,c.customer_name, t.TYPE, amount receivable_amount,
p.amount_due_original schedule_amount
FROM ra_cust_trx_line_gl_dist_all d,
ra_customer_trx_all r,
ar_payment_schedules_all p,
ra_cust_trx_types_all t,
hr_operating_units h,
ar_customers c
WHERE d.customer_trx_id = r.customer_trx_id
AND r.customer_trx_id = p.customer_trx_id
AND d.account_class = 'REC'
AND r.trx_date >= '01-Jul-2017'
AND t.cust_trx_type_id = r.cust_trx_type_id
AND amount <> amount_due_original
AND p.customer_id=c.customer_id
AND r.org_id=h.ORGANIZATION_ID
Tax Mismatch between OM delivery and AR taxes for India Localization taxes
SELECT CUSTOMER_TRX_ID TRX_ID,TRX_NUMBER,ORG_ID,GL_DATE,EVENT_ID FROM (
SELECT DISTINCT a.customer_trx_id, a.org_id, a.trx_number, c.gl_date,
c.event_id, b.interface_line_attribute3 delivery_id,
a.creation_date
FROM ra_customer_trx_all a,
ra_customer_trx_lines_all b,
ra_cust_trx_line_gl_dist_all c
WHERE 1 = 1
AND a.customer_trx_id = b.customer_trx_id
AND b.customer_trx_id = c.customer_trx_id
AND c.customer_trx_id = a.customer_trx_id
AND c.account_class = 'REC'
AND NOT EXISTS (SELECT '1'
FROM jai_tax_lines
WHERE trx_id = a.customer_trx_id)
AND trx_date BETWEEN :p_from_date AND :p_to_date
AND EXISTS (SELECT '1'
FROM jai_tax_lines
WHERE trx_id || '' = b.interface_line_attribute3
AND ACTUAL_TAX_RATE <> 0))
Accounting entry for ship confirmaton(Delivery Level)
Query the Internal Order Transaction from Material Transaction window from Inventory Responsibility. Fetch all transaction ID from that window. Run the below query..
Select (Select CONCATENATED_SEGMENTS from GL_CODE_COMBINATIONS_KFV where CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID) CODECOM, XAL.CODE_COMBINATION_ID, XAL.ACCOUNTING_CLASS_CODE, XAL.ACCOUNTING_DATE, XAL.ACCOUNTED_DR, XAL.ACCOUNTED_CR
from xla.XLA_TRANSACTION_ENTITIES XTE, XLA_EVENTS XE, XLA_AE_HEADERS XAH, XLA_AE_LINES XAL
WHERE XTE.ENTITY_ID=XE.ENTITY_ID AND
XE.ENTITY_ID=XAH.ENTITY_ID AND
XAH.AE_HEADER_ID=XAL.AE_HEADER_ID and
XTE.TRANSACTION_NUMBER in ('216321703',
'216321702',
'216321701',
'216321700')
AR Receipt Refund Query
Select * from AR_RECEIVABLE_APPLICATIONS_ALL AB, AR_PAYMENT_SCHEDULES_ALL CD
Where AB.Payment_Schedule_ID=CD.Payment_Schedule_ID and
AB.Application_ref_type='AP_REFUND_REQUEST' and
AB.Creation_date between '15-Jan-19' and '15-jan-19' and
CD.Invoice_currency_Code='USD'
Concurrent Program Query
select requestor,request_id,REQUESTED_START_DATE,status_code,phase_code, to_char (ACTUAL_START_DATE,'DD-MON-YYYY:HH24:MI:SS'),(systimestamp - ACTUAL_START_DATE) "running_time",
ACTUAL_COMPLETION_DATE,user_concurrent_program_name,argument_text
from fnd_conc_req_summary_v where user_concurrent_program_name like '%AP Single Transaction Data Validation Analyzer%'
order by ACTUAL_START_DATE desc;
SELECT
distinct user_concurrent_program_name,
responsibility_name,
request_date,
argument_text,
request_id,
user_name
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl fr,
fnd_user fu
WHERE
fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
--and user_name = upper('HIMSINGH')
and user_concurrent_program_name in
'AP Single Transaction Data Validation Analyzer'
-- and Phase_code='P'
ORDER BY REQUEST_DATE DESC;
Customer details fetch query:
SELECT hp.party_name, hca.cust_account_id, hcas.org_id, hp.party_id,
hcas.cust_acct_site_id, hps.location_id, hl.address1, hl.address2,
hl.address3, hl.address4, hl.city, hl.postal_code, hl.state, hp.PARTY_NUMBER, hps.PARTY_SITE_NUMBER, hca.ACCOUNT_NUMBER, hcsu.LOCATION
FROM hz_parties hp,
hz_party_sites hps,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu,
hz_locations hl
WHERE hp.party_id = hca.party_id
AND hca.party_id = hps.party_id
AND hps.party_site_id = hcas.party_site_id
AND hca.cust_account_id = hcas.cust_account_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcsu.site_use_code = 'BILL_TO'
AND hl.location_id = hps.location_id
AND hcas.status = 'A'
AND hcsu.status = 'A'
AND hp.party_name='ABC TANNERS'
and hcas.org_id='1011'
--- AND hl.location_id = 890912
---AND hca.cust_account_id = 9656782
Diff betwen Receivable Amount & Orginal Invoice Amount(AR_Payment_Schedules_all)
SELECT rcta.trx_number, rcta.customer_trx_id, rcta.trx_date,
SUM (rctla.extended_amount) invoice_amt,
arsa.amount_due_original amount_due_original
FROM ra_customer_trx_all rcta,
ra_customer_trx_lines_all rctla,
ar_payment_schedules_all arsa
WHERE rcta.customer_trx_id = rctla.customer_trx_id
--AND rcta.trx_number = 'AF7TX17180001022'
AND arsa.customer_trx_id = rcta.customer_trx_id
AND NVL (arsa.amount_due_remaining, 0) = 0
AND arsa.status = 'CL'
AND arsa.CLASS = 'INV'
GROUP BY rcta.trx_number,
arsa.amount_due_original,
rcta.customer_trx_id,
rcta.trx_date
HAVING ROUND (SUM (rctla.extended_amount)) <>
ROUND (NVL (arsa.amount_due_original, 0))
For IRISO Transaction across OU, Wrong Accounting is getting generated.
==========================================
SELECT distinct ooha.order_number,ooha.creation_Date,
(select segment1 from po_requisition_headers_All where REQUISITION_HEADER_ID=ooha.source_document_id) IR_NO
,mmt.TRX_SOURCE_DELIVERY_ID delivery_id
,wdl.booking_office invoice
,xal.ACCOUNTING_CLASS_CODE
,(select name from hr_operating_units where organization_id=ooha.org_id) from_ou
,(select name from org_organization_definitions ood,hr_operating_units hou where hou.organization_id=ood.operating_unit and ood.organization_id=mmt.transfer_organization_id) to_ou
,(select organization_code from org_organization_definitions where organization_id=mmt.organization_id) from_inv_org
,(select organization_code from org_organization_definitions where organization_id=mmt.transfer_organization_id) to_inv_org
FROM oe_order_headers_all ooha
, oe_order_lines_all oola
, mtl_material_transactions mmt
,GMF_XLA_EXTRACT_HEADERS gxh
,xla_ae_headers xah
,xla_ae_lines xal
,wsh_delivery_legs wdl
where 1=1
and wdl.delivery_id=mmt.TRX_SOURCE_DELIVERY_ID
and ooha.header_id=oola.header_id
and mmt.TRX_SOURCE_LINE_ID=oola.line_id
and gxh.transaction_id=mmt.transaction_id
and gxh.EVENT_ID=xah.EVENT_ID
and xah.ae_header_id=xal.ae_header_id
and gxh.ENTITY_CODE='INVENTORY'
and gxh.EVENT_CLASS_CODE='FOB_SHIP_RECIPIENT_SHIP'
and xah.EVENT_TYPE_CODE='FOB_SHIP_RECIPIENT_SHIP_TP'
and ooha.CANCELLED_FLAG<>'Y'
and mmt.transaction_type_id=62
and ooha.order_source_id=10
and xal.ACCOUNTING_CLASS_CODE ='INTERORG_PAYABLES'
and trunc(ooha.creation_Date)>'01-JAN-19'
I Expenses data validation query
SELECT papf.employee_number, papf.full_name,
(SELECT DISTINCT full_name
FROM per_all_people_f papf1
WHERE papf1.person_id = paaf.supervisor_id
AND TRUNC (SYSDATE) BETWEEN papf1.effective_start_date
AND NVL
(papf1.effective_end_date,
TRUNC (SYSDATE)
)) supervisor,
gcc.concatenated_segments,
(SELECT ffvl.description
FROM fnd_flex_values_vl ffvl
WHERE 1 = 1
AND flex_value_set_id = 1015404
AND flex_value = gcc.segment1) bsv,
(SELECT ffvl.description
FROM fnd_flex_values_vl ffvl
WHERE 1 = 1
AND flex_value_set_id = 1015212
AND flex_value = gcc.segment4) cost_centre,
(select LISTAGG(responsibility_name, ', ') WITHIN GROUP (ORDER BY responsibility_name) from (SELECT DISTINCT frt.responsibility_name
FROM fnd_user_resp_groups_direct fur,
fnd_responsibility_tl frt,
fnd_user fu
WHERE fu.employee_id = papf.person_id
AND fu.user_id = fur.user_id
AND frt.responsibility_id = fur.responsibility_id
and trunc(sysdate) between fur.start_date
AND NVL (fur.end_date,
TRUNC (SYSDATE))
AND UPPER (responsibility_name) LIKE '%I%EXPENSE%'))
responsibility_name
FROM per_all_people_f papf,
per_all_assignments_f paaf,
gl_code_combinations_kfv gcc
WHERE TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND NVL (papf.effective_end_date,
TRUNC (SYSDATE))
AND person_type_id = 2121
AND papf.person_id = paaf.person_id
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
AND NVL (paaf.effective_end_date,
TRUNC (SYSDATE))
AND gcc.code_combination_id(+) = paaf.default_code_comb_id
GROUP BY papf.employee_number,
paaf.supervisor_id,
gcc.concatenated_segments,
papf.full_name,
gcc.segment1,
gcc.segment4,
papf.person_id
ORDER BY 1
Query to fetch User password
SELECT usr.user_name,
get_pwd.decrypt
((SELECT (SELECT get_pwd.decrypt
(fnd_web_sec.get_guest_username_pwd,
usertable.encrypted_foundation_password
)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name =
(SELECT SUBSTR
(fnd_web_sec.get_guest_username_pwd,
1,
INSTR
(fnd_web_sec.get_guest_username_pwd,
'/'
)
- 1
)
FROM DUAL)),
usr.encrypted_user_password
) PASSWORD
FROM fnd_user usr
WHERE usr.user_name = '&USER_NAME';
Query to identify the mismatch between invoice amount and schedule amount
SELECT r.trx_number, r.trx_date, r.org_id, t.TYPE, amount receivable_amount,
p.amount_due_original schedule_amount
FROM ra_cust_trx_line_gl_dist_all d,
ra_customer_trx_all r,
ar_payment_schedules_all p,
ra_cust_trx_types_all t
WHERE d.customer_trx_id = r.customer_trx_id
AND r.customer_trx_id = p.customer_trx_id
AND d.account_class = 'REC'
AND r.trx_date >= '01-Jul-2017'
AND t.cust_trx_type_id = r.cust_trx_type_id
AND amount <> amount_due_original
Reco Query- AR Aging to TB Balance
Note: Incase Receipt Date is in current period and Application date is not in current period. Insuch case aging balance and TB Balance for Respective Receivable code combination will not match. So, the below will be used to indentify those transactions.
Select * from AR_RECEIVABLE_APPLICATIONS_ALL where CODE_COMBINATION_ID='46901' AND GL_DATE between '01-feb-19' and '28-feb-19' and
APPLY_DATE not between '01-feb-19' and '28-feb-19'
Reconcilation Query- To fetch Receipt Info based on CCID - By Mr Vinod
SELECT acra.receipt_number, rcta.trx_number, araa.amount_applied
FROM ar_cash_receipts_all acra,
ar_payment_schedules_all apsa,
ar_receivable_applications_all araa,
ra_customer_trx_all rcta
WHERE acra.cash_receipt_id = apsa.cash_receipt_id
AND acra.cash_receipt_id = araa.cash_receipt_id
AND araa.code_combination_id = 1139381
AND araa.applied_customer_trx_id = rcta.customer_trx_id
AND araa.gl_date BETWEEN p_from_date AND p_to_date
Saleperson ID for Receipt Upload
Select * from jtf_rs_salesreps jrs,jtf_rs_defresources_v jrret,hr_operating_units h
where jrs.resource_id = jrret.resource_id
and jrs.org_id=h.organization_id and
jrs.name like '%Swapna%'
order by jrret.resource_name
AP INTERFACE
SELECT a.invoice_id, a.invoice_num, a.invoice_date,a.creation_date,
a.invoice_type_lookup_code, b.reject_lookup_code error_description,
a.vendor_id, a.vendor_site_id, a.invoice_amount, a.terms_id, a.status,
a.SOURCE, a.gl_date, a.org_id
FROM ap_invoices_interface a, ap_interface_rejections b
WHERE NVL(a.status,'X') <> 'PROCESSED'
AND a.invoice_id = b.parent_id(+)
AND (a.invoice_date) >= '01-APR-2017';
--To find ap responsibility--
Select b.RESPONSIBILITY_NAME, a.request_id from fnd_concurrent_requests a,Fnd_responsibility_tl b
where a.RESPONSIBILITY_ID=b.RESPONSIBILITY_ID
and a.request_id in ('57995821','57995838')
TAX ERROR IN AR
SELECT DISTINCT ems_standard.get_organization_name (org_id) ou_name,
customer_trx_id,
(SELECT trx_number
FROM ra_customer_trx_all a
WHERE a.customer_trx_id = t.customer_trx_id) trx_number,
error_flag, err_mesg, creation_date
FROM jai_ar_trx_ins_lines_t t
WHERE error_flag = 'R'
ar interface
SELECT org_id, ems_standard.get_organization_name (org_id) ou_name,
creation_date, interface_line_id, interface_line_context,
interface_line_attribute1, interface_line_attribute2,
batch_source_name, line_type, description, currency_code, amount,
cust_trx_type_id,PRIMARY_SALESREP_ID,header_attribute_category,
(SELECT NAME
FROM ra_cust_trx_types_all t
WHERE t.cust_trx_type_id = l.cust_trx_type_id) cust_trx_type,
orig_system_bill_customer_id, gl_date, ship_date_actual, trx_number,
sales_order, purchase_order, warehouse_id,
ems_standard.get_inventory_code (warehouse_id) inv_org,PRIMARY_SALESREP_ID,header_attribute_category,
(SELECT distinct RTRIM(
REGEXP_REPLACE(
(listagg(MESSAGE_TEXT||':'||INVALID_VALUE,'-')
WITHIN GROUP (ORDER BY MESSAGE_TEXT||':'||INVALID_VALUE) OVER ()),
'([^-]*)(-\1)+($|-)',
'\1\3'),
'-') Num2s
FROM RA_INTERFACE_ERRORS_ALL e
where l.interface_line_id = e.interface_line_id) err_msg
FROM ra_interface_lines_all l
ap new query
SELECT ems_standard.get_organization_name (i.org_id) ou, i.SOURCE,
i.invoice_id, i.invoice_num, i.invoice_type_lookup_code,
i.invoice_date, i.status, r.reject_lookup_code error_description,
i.vendor_id, i.vendor_site_id, i.invoice_amount, i.terms_id,
i.gl_date, l.line_number, l.line_type_lookup_code, l.description,
l.amount, i.creation_date
FROM ap_interface_rejections r,
ap_invoices_interface i,
ap_invoice_lines_interface l
WHERE i.invoice_id = l.invoice_id
AND NVL (i.status, 'X') <> 'PROCESSED'
AND i.invoice_date >= '01-APR-2017'
AND DECODE (r.parent_table,
'AP_INVOICES_INTERFACE', i.invoice_id,
l.invoice_line_id
) = r.parent_id
ORDER BY ems_standard.get_organization_name (i.org_id),
i.invoice_num,
l.line_number
gl INTERFACE
SELECT user_je_category_name, user_je_source_name, status,
COUNT (*), min(ACCOUNTING_DATE), min(DATE_CREATED)
FROM gl_interface
GROUP BY user_je_category_name, user_je_source_name, status;
