How to find encumbrance, budget and actual from backend

select

sum(case when gb.actual_flag='B'
then (SUM(NVL(PERIOD_NET_DR,0)) - SUM(NVL(PERIOD_NET_CR,0)))
else 0 end) budget,





sum(case when gb.actual_flag='A'
then (SUM(NVL(PERIOD_NET_DR,0)) - SUM(NVL(PERIOD_NET_CR,0)))
else 0 end) actual,
sum(case when gb.actual_flag='E'
then (SUM(NVL(PERIOD_NET_DR,0)) - SUM(NVL(PERIOD_NET_CR,0)))
else 0 end) encumb
from apps.gl_balances gb,
apps.gl_code_combinations gcc
where





gcc.CODE_COMBINATION_ID = gb.CODE_COMBINATION_ID
and gb.LEDGER_ID = 6045
and gb.PERIOD_YEAR = '2018'
and gb.PERIOD_NUM <= 3 -- March
and gb.CURRENCY_CODE = 'AZN'
and gcc.code_combination_id = 2133476

How to select PO Match Approval Level from PO_LINE_LOCATIONS_ALL

SELECT

DECODE (INSPECTION_REQUIRED_FLAG || RECEIPT_REQUIRED_FLAG,





'NN', '2-Way',

'NY', '3-Way',

'YY', '4-Way',

'Not Specified')

"Match Approval Level"






FROM PO_LINE_LOCATIONS_ALL PLL

How to get AP invoice payments using XLA events

with event as (
select /*+ materialize */
entity_id ,
max(xe.event_date) event_date
from
xla_events xe

where xe.event_date <= sysdate
and xe.event_status_code= decode(xe.event_type_code, 'PAYMENT CANCELLED', xe.event_status_code, 'P')
and xe.process_status_code='P'
group by entity_id




)
select
aip.invoice_id,
sum(aip.amount) payment_amount






from
xla_events xe inner join ap_invoice_payments_all aip
on xe.event_id=aip.accounting_event_id
inner join event e
on xe.entity_id=e.entity_id
and xe.event_date<=e.event_date
inner join ap_checks_all ca
on aip.check_id=ca.check_id


group by aip.invoice_id

How to get invoice and purchase orders (po) / relations between invoices and purchase orders (pos)

select invoice_id,
LISTAGG(po_number, '; ') WITHIN GROUP (ORDER BY po_number ) po_number,
LISTAGG(PO_creator_name, '; ') WITHIN GROUP (ORDER BY po_number ) PO_creator_name,
LISTAGG(requestor_name, '; ') WITHIN GROUP (ORDER BY po_number ) requestor_name




from(

select
ail.invoice_id,
ail.po_header_id,
pha.segment1 po_number,
he1.full_name PO_creator_name,
pr.requestor_name,
row_number() over (partition by ail.invoice_id, ail.po_header_id,pha.segment1
,he1.full_name, pr.requestor_name order by ail.invoice_id) row_id





from ap_invoice_lines_all ail inner join po_headers_all pha on ail.po_header_id=pha.po_header_id
left join fnd_user fuv1 on pha.created_by=fuv1.user_id
left join hr_employees he1 on fuv1.employee_id=he1.employee_id
left join xxcorp_po_requestor pr on pha.po_header_id=pr.po_header_id





) where row_id=1
group by invoice_id

How Payable Invoices related Payment Data is stored in Oracle Apps R12? (Oracle Payments, Oracle Payables)

In this post, we will find the tables involved in storing the Payment Data related to the Payable INVOICE ( Invoice_id = 166014 ). All the queries given in this post and their related posts were tested in R12.1.1 Instance.

AP_TERMS
SELECT *
FROM AP_TERMS
WHERE term_id IN
( SELECT DISTINCT terms_id
FROM AP_INVOICES_ALL
WHERE invoice_id = '166014'
);

AP_TERMS_LINES
SELECT *
FROM AP_TERMS_LINES
WHERE term_id IN
( SELECT DISTINCT terms_id
FROM AP_INVOICES_ALL
WHERE invoice_id = '166014'
);

AP_PAYMENT_SCHEDULES_ALL
SELECT
amount_remaining,
batch_id,
due_date,
gross_amount,
hold_flag,
invoice_id,
payment_num,
SUBSTR(payment_status_flag,1,1) payment_status_flag,
org_id
FROM
AP_PAYMENT_SCHEDULES_ALL
WHERE
invoice_id = '166014';

AP_INVOICE_PAYMENTS_ALL
SELECT
check_id,
SUBSTR(invoice_payment_id,1,15) invoice_payment_id,
amount,
payment_base_amount,
invoice_base_amount,
accounting_date,
period_name,
posted_flag,
accounting_event_id,
invoice_id,
org_id
FROM
AP_INVOICE_PAYMENTS_ALL
WHERE
invoice_id = '166014'
ORDER BY check_id ASC;

AP_PAYMENT_DISTRIBUTIONS_ALL
SELECT tab.*
FROM AP_INVOICE_PAYMENTS_ALL aip,
AP_PAYMENT_DISTRIBUTIONS_ALL tab
WHERE aip.invoice_payment_id = tab.invoice_payment_id
AND aip.invoice_id = '166014';

AP_CHECKS_ALL
SELECT
check_id,
check_number,
vendor_site_code,
amount,
base_amount,
checkrun_id,
checkrun_name,
check_date,
SUBSTR(status_lookup_code,1,15) status_lookup_code,
void_date,
org_id
FROM
AP_CHECKS_ALL
WHERE check_id IN
( SELECT DISTINCT check_id
FROM AP_INVOICE_PAYMENTS_ALL
WHERE invoice_id = '166014'
);

AP_PAYMENT_HISTORY_ALL
SELECT
payment_history_id,
check_id,
accounting_date,
SUBSTR(transaction_type,1,20) transaction_type,
posted_flag,
SUBSTR(accounting_event_id,1,10) accounting_event_id,
rev_pmt_hist_id,
org_id
FROM
AP_PAYMENT_HISTORY_ALL
WHERE check_id IN
(SELECT DISTINCT check_id
FROM AP_INVOICE_PAYMENTS_ALL
WHERE invoice_id = '166014'
)
ORDER BY payment_history_id ASC;

AP_PAYMENT_HIST_DISTS
SELECT aphd.*
FROM AP_INVOICE_DISTRIBUTIONS_ALL aid,
AP_PAYMENT_HIST_DISTS aphd,
AP_PAYMENT_HISTORY_ALL aph
WHERE aid.invoice_id = '166014'
AND aid.invoice_distribution_id = aphd.invoice_distribution_id
AND aph.payment_history_id = aphd.payment_history_id;


AP_RECON_DISTRIBUTIONS_ALL
SELECT *
FROM AP_RECON_DISTRIBUTIONS_ALL
WHERE check_id IN
( SELECT check_id
FROM AP_INVOICE_PAYMENTS_ALL
WHERE invoice_id = '166014'
);

AP_DOCUMENTS_PAYABLE
SELECT
pay_proc_trxn_type_code,
calling_app_doc_unique_ref1 check_id,
calling_app_doc_unique_ref2 invoice_id,
calling_app_doc_unique_ref4 invoice_payment_id,
calling_app_doc_ref_number invoice_number,
payment_function,
payment_date,
document_date,
document_type,
payment_currency_code,
payment_amount,
payment_method_code
FROM
AP_DOCUMENTS_PAYABLE
WHERE calling_app_id = 200 -- Application id for Payables
AND calling_app_doc_unique_ref2 = '166014';

IBY_DOCS_PAYABLE_ALL
SELECT *
FROM IBY_DOCS_PAYABLE_ALL
WHERE calling_app_id = 200
AND calling_app_doc_unique_ref2 = '166014';

IBY_PAYMENTS_ALL
SELECT *
FROM IBY_PAYMENTS_ALL
WHERE payment_id IN
(SELECT payment_id
FROM IBY_DOCS_PAYABLE_ALL
WHERE calling_app_id = 200
AND calling_app_doc_unique_ref2 = '166014'
);

IBY_PAY_INSTRUCTIONS_ALL
SELECT *
FROM IBY_PAY_INSTRUCTIONS_ALL
WHERE payment_instruction_id IN
(SELECT payment_instruction_id
FROM IBY_PAYMENTS_ALL
WHERE payment_id IN
(SELECT payment_id
FROM IBY_DOCS_PAYABLE_ALL
WHERE calling_app_id = 200
AND calling_app_doc_unique_ref2 = '166014'
);
);

How to join GL tables to XLA tables

select
      *
from




      gl_je_headers gh,
      gl_je_lines jl,
      xla_ae_lines xl,
      xla_ae_headers xh
     
where
      gh.je_header_id=jl.je_header_id
      and xl.gl_sl_link_id=jl.gl_sl_link_id
      and xl.gl_sl_link_table=jl.gl_sl_link_table
      and jl.gl_sl_link_table='XLAJEL'
      and xh.ae_header_id=xl.ae_header_id
      and xh.ae_header_id=461566960

How to find user id,responsibility id, application id for FND_GLOBAL.APPS_INITIALIZE

How to find USER_ID:

select user_id from fnd_user where user_name='test';


How to find  RESPONSIBILITY_ID:

select responsibility_id from fnd_responsibility_vl where responsibility_name like 'AP Accountant';

How to get APPLICATION_ID:
select * from fnd_responsibility_vl  where responsibility_id=223;

How to find a word with different endings in the string

SELECT

REGEXP_SUBSTR('I have earned 100 dollars today ','earn((ing)|(ed))')

FROM dual;

result : earned

If you change the string

SELECT

REGEXP_SUBSTR('I have been earning 100 dollars today ','earn((ing)|(ed))')

FROM dual;

result: earning