Query for Find Outstanding Invoice Payables

Post date: Feb 08, 2017 1:30:27 PM

--Query for find total summary APTB

/* Formatted on 2017/01/07 16:04 (Formatter Plus v4.8.8) */

SELECT SUM (NVL (xtb.acctd_rounded_dr, 0) - NVL (xtb.acctd_rounded_cr, 0)) total,

xtb.code_combination_id,

gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' || gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6 || '.' || gcc.segment7 || '.' || gcc.segment8 ACCOUNT

FROM xla_trial_balances xtb,

gl_code_combinations gcc

WHERE 1 = 1

AND gcc.code_combination_id = xtb.code_combination_id

-- AND xtb.definition_code = 'SAK_DIBAYAR_LAIN_LAIN'

-- AND xtb.definition_code = 'ODI_BPS'

and xtb.DEFINITION_CODE='TRC_HUTANG_USAHA_AUDIT'

AND xtb.gl_date <= '31-Dec-2016'

-- AND xtb.natural_account_segment_value = '2003000000'

--and intercompany_segment_value='000'

GROUP BY xtb.code_combination_id, gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' || gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6 || '.' || gcc.segment7 || '.' || gcc.segment8

select distinct definition_code

from xla_trial_balances

select *

from ap_invoices_all

where invoice_num='PMS-PDG/16/XII/0255'

--query start here for find detail APTB

SELECT a.invoice_id

, a.invoice_num

, a.invoice_type_lookup_code

, a.invoice_date

, a.vendor_id

, c.vendor_name

, b.vendor_site_code

, a.vendor_site_id

, a.invoice_amount + nvl(awt.witholding,0) - NVL (a.amount_paid, 0 ) REMAINING_AMOUNT

, awt.witholding

, ((a.invoice_amount - NVL (a.amount_paid, 0 )) * nvl(a.exchange_rate,1)) AMOUNT_IDR

, a.invoice_currency_code

, a.exchange_rate

, a.exchange_rate_type

, a.exchange_date

, a.terms_id

, d.NAME "TERMS_NAME"

, a.description

, a.attribute_category

, a.attribute1

, a.SOURCE

, a.invoice_received_date

, a.gl_date

, a.accts_pay_code_combination_id

, gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7||'.'||gcc.segment8 account

, a.org_id

, e.NAME "OU NAME"

, a.terms_date

, a.payment_method_code

, a.creation_date

, ap_invoices_pkg.get_approval_status

(a.invoice_id,

a.invoice_amount,

a.payment_status_flag,

a.invoice_type_lookup_code

) inv_status

FROM ap.ap_invoices_all a,

ap.ap_supplier_sites_all b,

ap.ap_suppliers c,

apps.ap_terms_tl d,

gl.gl_code_combinations gcc,

hr.hr_all_organization_units e, (select invoice_id, sum (amount) WITHOLDING

from ap.ap_invoice_lines_all

WHERE line_type_lookup_code = 'AWT'

AND CANCELLED_FLAG = 'N'

group by invoice_id) AWT

WHERE 1 = 1

and gcc.CODE_COMBINATION_ID=a.accts_pay_code_combination_id

AND a.invoice_id = awt.invoice_id(+)

AND a.vendor_id = b.vendor_id(+)

AND a.vendor_site_id = b.vendor_site_id(+)

AND b.vendor_id = c.vendor_id(+)

AND a.terms_id = d.term_id

AND a.org_id = e.organization_id

AND a.org_id = b.org_id(+)

AND a.payment_status_flag <> 'Y'

AND a.cancelled_date IS NULL

AND a.gl_date <= :pdate

-- AND a.invoice_num = '18001'

AND a.invoice_amount <> 0

AND e.NAME LIKE 'Bina%'

and gcc.segment2='2003000000'

-- and gcc.segment2='2001000008'

-- and a.INVOICE_NUM='SAK -TIMPEHPDG/13/X/0275'

--and a.ACCTS_PAY_CODE_COMBINATION_ID=511960

AND ap_invoices_pkg.get_approval_status (a.invoice_id,

a.invoice_amount,

a.payment_status_flag,

a.invoice_type_lookup_code

) = 'APPROVED'

-- and a.ACCTS_PAY_CODE_COMBINATION_ID=3004

select *

from hr_all_organization_units