http://aporaclepayables.blogspot.com/2016/07/form-personalization-disable-create.html
Please Copy Paste the Below URL in your Browser
http://orclapp.blogspot.com/2013/05/form-personalization-disable-create.html
Tuesday, 26 July 2016
Tuesday, 5 July 2016
To check Unaccounted Invoices (Query)
http://aporaclepayables.blogspot.com/2016/07/to-check-unaccounted-invoices-query.html
select distinct tt.doc_sequence_value, tt.invoice_num, tt.gl_date, tt.invoice_amount, tt.approval_status_lookup_code
from ap_invoices_V tt, ap_invoice_distributions_all kk
where tt.invoice_id=kk.INVOICE_ID(+)
and tt.posting_flag!='Y'
and
(tt.approval_status_lookup_code in ('CANCELLED')
and kk.DISTRIBUTION_LINE_NUMBER is not null
or tt.approval_status_lookup_code in ('NEVER APPROVED','APPROVED','NEEDS REAPPROVAL')
)
and tt.org_id=80
order by tt.doc_sequence_value desc
-----
begin
mo_global.set_policy_context('S',85);
end;
select distinct tt.doc_sequence_value, tt.invoice_num, tt.gl_date, tt.invoice_amount, --tt.approval_status_lookup_code
DECODE
(tt.approval_status_lookup_code
,
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'APPROVED', 'Validated',
'CANCELLED', 'Cancelled',
'AVAILABLE PREPAYMENT', 'Available Prepayment',
'AVAILABLE', 'Available'
) invoice_status
from ap_invoices_V tt, ap_invoice_distributions_all kk
where tt.invoice_id=kk.INVOICE_ID(+)
and tt.posting_flag!='Y'
and
(tt.approval_status_lookup_code in ('CANCELLED')
and kk.DISTRIBUTION_LINE_NUMBER is not null
or tt.approval_status_lookup_code in ('NEVER APPROVED','APPROVED','NEEDS REAPPROVAL')
)
and tt.org_id=85
order by tt.doc_sequence_value desc
--------------------------------------------------------------------------------------------------------------------------
For My Case When Withholding Deduction is at Payment Time
/*
select *
from xla_distribution_links l
where l.AE_HEADER_ID = 341206*/
select
/* aeh.ae_header_id , l.ae_header_id ,
l.applied_to_source_id_num_1 , i.invoice_id */
i.invoice_id,
i.invoice_num, --
i.invoice_date,
i.gl_date, --
i.invoice_amount, --
i.doc_sequence_value APN_NUMBER, --
i.terms_date APN_DATE, --
i.payment_currency_code,
hsv.vendor_id,
hsv.vendor_name, --
hsv.vendor_number, --
'N' gl_transfer_status_code,
null gl_transfer_date,
U.USER_NAME,
ap_invoices_pkg.get_approval_status (i.invoice_id, i.invoice_amount, i.payment_status_flag, i.invoice_type_lookup_code) STATUS --
from
ap_invoices_all i,
HUBPO_SUPPLIERS_V HSV,
fnd_user u
where
i.org_id= :P_ORG_ID --- added IACS( imran ) 24-JULY-2015 --
and
(
(
--================= Unposted DIST PRESENT =====================
exists (
select 1
from ap_invoice_distributions_all d
where
d.INVOICE_ID = i.invoice_id and
d.LINE_TYPE_LOOKUP_CODE != 'AWT' and
nvl(d.ACCRUAL_POSTED_FLAG , 'N') = 'N'
) and
--=================AND DIST NOT PRESENT IF CANCELLED INVOICE=====================
i.INVOICE_ID not in
(
select aia.INVOICE_ID from ap_invoices_all aia
where
not exists ( select 1 from ap_invoice_distributions_all d where d.INVOICE_ID = aia.INVOICE_ID )
and Decode(AP_INVOICES_PKG.GET_APPROVAL_STATUS( I.INVOICE_ID,
I.INVOICE_AMOUNT, I.PAYMENT_STATUS_FLAG,
I.INVOICE_TYPE_LOOKUP_CODE),'CANCELLED','Cancelled','NEEDS REAPPROVAL','Needs Revalidation','APPROVED','Validated','NEVER APPROVED','Never Validated',AP_INVOICES_PKG.GET_APPROVAL_STATUS( I.INVOICE_ID,
I.INVOICE_AMOUNT, I.PAYMENT_STATUS_FLAG,
I.INVOICE_TYPE_LOOKUP_CODE)) = 'Cancelled'
)
)
OR
--================= OR DIST Not PRESENT =====================
(
not exists
(
select 1 from ap_invoice_distributions_all d
where d.INVOICE_ID = I.INVOICE_ID
)
and Decode(AP_INVOICES_PKG.GET_APPROVAL_STATUS( I.INVOICE_ID,
I.INVOICE_AMOUNT, I.PAYMENT_STATUS_FLAG,
I.INVOICE_TYPE_LOOKUP_CODE),'CANCELLED','Cancelled','NEEDS REAPPROVAL','Needs Revalidation','APPROVED','Validated','NEVER APPROVED','Never Validated',AP_INVOICES_PKG.GET_APPROVAL_STATUS( I.INVOICE_ID,
I.INVOICE_AMOUNT, I.PAYMENT_STATUS_FLAG,
I.INVOICE_TYPE_LOOKUP_CODE)) != 'Cancelled'
)
)
and
i.VENDOR_ID = HSV.VENDOR_ID and
i.vendor_site_id = HSV.vendor_site_id and
-- i.gl_date between :P_DATE1 and :P_DATE2 AND
-- i.gl_date between trunc(TO_DATE(:P_PERIOD,'MON-RR'),'MONTH') AND LAST_DAY(TO_DATE(:P_PERIOD,'MON-RR')) AND
i.gl_date between NVL(:P_DATE1,i.gl_date) and NVL(:P_DATE2,i.gl_date) and
i.gl_date between NVL(trunc(TO_DATE(:P_PERIOD,'MON-RR'),'MONTH'),i.gl_date) AND NVL(LAST_DAY(TO_DATE(:P_PERIOD,'MON-RR')),i.gl_date) AND
hsv.vendor_name=nvl(:P_VENDER_NAME,hsv.vendor_name)
--i.DOC_SEQUENCE_VALUE = '16000633' -- 12
and u.USER_ID=i.CREATED_BY
and U.user_id=NVL(:P_USER_ID,U.USER_ID)
order by i.doc_sequence_value
select distinct tt.doc_sequence_value, tt.invoice_num, tt.gl_date, tt.invoice_amount, tt.approval_status_lookup_code
from ap_invoices_V tt, ap_invoice_distributions_all kk
where tt.invoice_id=kk.INVOICE_ID(+)
and tt.posting_flag!='Y'
and
(tt.approval_status_lookup_code in ('CANCELLED')
and kk.DISTRIBUTION_LINE_NUMBER is not null
or tt.approval_status_lookup_code in ('NEVER APPROVED','APPROVED','NEEDS REAPPROVAL')
)
and tt.org_id=80
order by tt.doc_sequence_value desc
-----
begin
mo_global.set_policy_context('S',85);
end;
select distinct tt.doc_sequence_value, tt.invoice_num, tt.gl_date, tt.invoice_amount, --tt.approval_status_lookup_code
DECODE
(tt.approval_status_lookup_code
,
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'APPROVED', 'Validated',
'CANCELLED', 'Cancelled',
'AVAILABLE PREPAYMENT', 'Available Prepayment',
'AVAILABLE', 'Available'
) invoice_status
from ap_invoices_V tt, ap_invoice_distributions_all kk
where tt.invoice_id=kk.INVOICE_ID(+)
and tt.posting_flag!='Y'
and
(tt.approval_status_lookup_code in ('CANCELLED')
and kk.DISTRIBUTION_LINE_NUMBER is not null
or tt.approval_status_lookup_code in ('NEVER APPROVED','APPROVED','NEEDS REAPPROVAL')
)
and tt.org_id=85
order by tt.doc_sequence_value desc
--------------------------------------------------------------------------------------------------------------------------
For My Case When Withholding Deduction is at Payment Time
/*
select *
from xla_distribution_links l
where l.AE_HEADER_ID = 341206*/
select
/* aeh.ae_header_id , l.ae_header_id ,
l.applied_to_source_id_num_1 , i.invoice_id */
i.invoice_id,
i.invoice_num, --
i.invoice_date,
i.gl_date, --
i.invoice_amount, --
i.doc_sequence_value APN_NUMBER, --
i.terms_date APN_DATE, --
i.payment_currency_code,
hsv.vendor_id,
hsv.vendor_name, --
hsv.vendor_number, --
'N' gl_transfer_status_code,
null gl_transfer_date,
U.USER_NAME,
ap_invoices_pkg.get_approval_status (i.invoice_id, i.invoice_amount, i.payment_status_flag, i.invoice_type_lookup_code) STATUS --
from
ap_invoices_all i,
HUBPO_SUPPLIERS_V HSV,
fnd_user u
where
i.org_id= :P_ORG_ID --- added IACS( imran ) 24-JULY-2015 --
and
(
(
--================= Unposted DIST PRESENT =====================
exists (
select 1
from ap_invoice_distributions_all d
where
d.INVOICE_ID = i.invoice_id and
d.LINE_TYPE_LOOKUP_CODE != 'AWT' and
nvl(d.ACCRUAL_POSTED_FLAG , 'N') = 'N'
) and
--=================AND DIST NOT PRESENT IF CANCELLED INVOICE=====================
i.INVOICE_ID not in
(
select aia.INVOICE_ID from ap_invoices_all aia
where
not exists ( select 1 from ap_invoice_distributions_all d where d.INVOICE_ID = aia.INVOICE_ID )
and Decode(AP_INVOICES_PKG.GET_APPROVAL_STATUS( I.INVOICE_ID,
I.INVOICE_AMOUNT, I.PAYMENT_STATUS_FLAG,
I.INVOICE_TYPE_LOOKUP_CODE),'CANCELLED','Cancelled','NEEDS REAPPROVAL','Needs Revalidation','APPROVED','Validated','NEVER APPROVED','Never Validated',AP_INVOICES_PKG.GET_APPROVAL_STATUS( I.INVOICE_ID,
I.INVOICE_AMOUNT, I.PAYMENT_STATUS_FLAG,
I.INVOICE_TYPE_LOOKUP_CODE)) = 'Cancelled'
)
)
OR
--================= OR DIST Not PRESENT =====================
(
not exists
(
select 1 from ap_invoice_distributions_all d
where d.INVOICE_ID = I.INVOICE_ID
)
and Decode(AP_INVOICES_PKG.GET_APPROVAL_STATUS( I.INVOICE_ID,
I.INVOICE_AMOUNT, I.PAYMENT_STATUS_FLAG,
I.INVOICE_TYPE_LOOKUP_CODE),'CANCELLED','Cancelled','NEEDS REAPPROVAL','Needs Revalidation','APPROVED','Validated','NEVER APPROVED','Never Validated',AP_INVOICES_PKG.GET_APPROVAL_STATUS( I.INVOICE_ID,
I.INVOICE_AMOUNT, I.PAYMENT_STATUS_FLAG,
I.INVOICE_TYPE_LOOKUP_CODE)) != 'Cancelled'
)
)
and
i.VENDOR_ID = HSV.VENDOR_ID and
i.vendor_site_id = HSV.vendor_site_id and
-- i.gl_date between :P_DATE1 and :P_DATE2 AND
-- i.gl_date between trunc(TO_DATE(:P_PERIOD,'MON-RR'),'MONTH') AND LAST_DAY(TO_DATE(:P_PERIOD,'MON-RR')) AND
i.gl_date between NVL(:P_DATE1,i.gl_date) and NVL(:P_DATE2,i.gl_date) and
i.gl_date between NVL(trunc(TO_DATE(:P_PERIOD,'MON-RR'),'MONTH'),i.gl_date) AND NVL(LAST_DAY(TO_DATE(:P_PERIOD,'MON-RR')),i.gl_date) AND
hsv.vendor_name=nvl(:P_VENDER_NAME,hsv.vendor_name)
--i.DOC_SEQUENCE_VALUE = '16000633' -- 12
and u.USER_ID=i.CREATED_BY
and U.user_id=NVL(:P_USER_ID,U.USER_ID)
order by i.doc_sequence_value
AP Header, Line and Distribution GL Period same (Query Check)
http://aporaclepayables.blogspot.com/2016/07/ap-header-and-distribution-gl-date-same.html
Invoice Header and Distrubution
select pp.LINE_TYPE_LOOKUP_CODE,
kk.DOC_sequence_value,
kk.INVOICE_NUM,
kk.ORG_ID,
kk.GL_DATE,
kk.INVOICE_ID,
kk.Header_period,
pp.Distribution_period,
kk.CANCELLED_DATE
from (select aia.DOC_sequence_value,
aia.INVOICE_NUM,
aia.ORG_ID,
aia.GL_DATE,
aia.INVOICE_ID, aia.cancelled_date,
to_char(aia.GL_DATE, 'MON-YY') Header_period
from ap_invoices_all aia) kk,
(select aida.LINE_TYPE_LOOKUP_CODE,
aida.ACCOUNTING_DATE,
aida.INVOICE_ID,
to_char(aida.ACCOUNTING_DATE, 'MON-YY') Distribution_period
from ap_invoice_distributions_all aida) pp
where kk.invoice_id = pp.invoice_id
and Header_period <> Distribution_period
and pp.LINE_TYPE_LOOKUP_CODE in ('ITEM', 'MISCELLANEOUS')
and kk.CANCELLED_DATE is null
and kk.gl_date > '01-JAN-16'
---------------------------------------------
Invoice Header, Line and Distrubution
select pp.LINE_TYPE_LOOKUP_CODE,
kk.DOC_sequence_value,
kk.INVOICE_NUM,
kk.ORG_ID,
kk.GL_DATE,
kk.INVOICE_ID,
kk.Header_period,
ll.Line_period,
pp.Distribution_period,
kk.CANCELLED_DATE
from (select aia.DOC_sequence_value,
aia.INVOICE_NUM,
aia.ORG_ID,
aia.GL_DATE,
aia.INVOICE_ID,
aia.cancelled_date,
to_char(aia.GL_DATE, 'MON-YY') Header_period
from ap_invoices_all aia) kk,
(select aida.LINE_TYPE_LOOKUP_CODE,
aida.ACCOUNTING_DATE,
aida.INVOICE_ID,
aida.invoice_line_number,
to_char(aida.ACCOUNTING_DATE, 'MON-YY') Distribution_period
from ap_invoice_distributions_all aida) pp,
(select aila.invoice_id,
aila.line_number,
to_char(aila.accounting_DATE, 'MON-YY') Line_period
from ap_invoice_lines_all aila) ll
where kk.invoice_id = pp.invoice_id
and kk.invoice_id = ll.invoice_id
and ll.invoice_id = pp.invoice_id
and ll.line_number = pp.invoice_line_number
and (Header_period <> Distribution_period or
Header_period <> Line_period or Line_period <> Distribution_period)
and pp.LINE_TYPE_LOOKUP_CODE in ('ITEM', 'MISCELLANEOUS')
and kk.CANCELLED_DATE is null
and kk.gl_date > '01-JAN-16'
--and kk.org_id = 185
Invoice Header and Distrubution
select pp.LINE_TYPE_LOOKUP_CODE,
kk.DOC_sequence_value,
kk.INVOICE_NUM,
kk.ORG_ID,
kk.GL_DATE,
kk.INVOICE_ID,
kk.Header_period,
pp.Distribution_period,
kk.CANCELLED_DATE
from (select aia.DOC_sequence_value,
aia.INVOICE_NUM,
aia.ORG_ID,
aia.GL_DATE,
aia.INVOICE_ID, aia.cancelled_date,
to_char(aia.GL_DATE, 'MON-YY') Header_period
from ap_invoices_all aia) kk,
(select aida.LINE_TYPE_LOOKUP_CODE,
aida.ACCOUNTING_DATE,
aida.INVOICE_ID,
to_char(aida.ACCOUNTING_DATE, 'MON-YY') Distribution_period
from ap_invoice_distributions_all aida) pp
where kk.invoice_id = pp.invoice_id
and Header_period <> Distribution_period
and pp.LINE_TYPE_LOOKUP_CODE in ('ITEM', 'MISCELLANEOUS')
and kk.CANCELLED_DATE is null
and kk.gl_date > '01-JAN-16'
---------------------------------------------
Invoice Header, Line and Distrubution
select pp.LINE_TYPE_LOOKUP_CODE,
kk.DOC_sequence_value,
kk.INVOICE_NUM,
kk.ORG_ID,
kk.GL_DATE,
kk.INVOICE_ID,
kk.Header_period,
ll.Line_period,
pp.Distribution_period,
kk.CANCELLED_DATE
from (select aia.DOC_sequence_value,
aia.INVOICE_NUM,
aia.ORG_ID,
aia.GL_DATE,
aia.INVOICE_ID,
aia.cancelled_date,
to_char(aia.GL_DATE, 'MON-YY') Header_period
from ap_invoices_all aia) kk,
(select aida.LINE_TYPE_LOOKUP_CODE,
aida.ACCOUNTING_DATE,
aida.INVOICE_ID,
aida.invoice_line_number,
to_char(aida.ACCOUNTING_DATE, 'MON-YY') Distribution_period
from ap_invoice_distributions_all aida) pp,
(select aila.invoice_id,
aila.line_number,
to_char(aila.accounting_DATE, 'MON-YY') Line_period
from ap_invoice_lines_all aila) ll
where kk.invoice_id = pp.invoice_id
and kk.invoice_id = ll.invoice_id
and ll.invoice_id = pp.invoice_id
and ll.line_number = pp.invoice_line_number
and (Header_period <> Distribution_period or
Header_period <> Line_period or Line_period <> Distribution_period)
and pp.LINE_TYPE_LOOKUP_CODE in ('ITEM', 'MISCELLANEOUS')
and kk.CANCELLED_DATE is null
and kk.gl_date > '01-JAN-16'
--and kk.org_id = 185
Friday, 1 July 2016
AP: Supplier Site DFF Data Not Saved
http://aporaclepayables.blogspot.com/2016/06/ap-supplier-site-dff-data-not-saved.html
R12 AP: Supplier Site DFF Data Not Saved (Doc ID 1571273.1)
R12 AP: Supplier Site DFF Data Not Saved (Doc ID 1571273.1)
Wednesday, 29 June 2016
FRM-40742: Illegal Status Conversion On Record 1: New To Changed (Financial Options AP)
http://aporaclepayables.blogspot.com/2016/06/frm-40742-illegal-status-conversion-on.html
Wednesday, 22 June 2016
Tax Code link with Invoices Tables (Query)
http://aporaclepayables.blogspot.com/2016/06/tax-code-query.html
select DOC_SEQUENCE_VALUE, tax_name from(
select aia.DOC_SEQUENCE_VALUE,(select vv.TAX_NAME from AP_AWT_GROUP_TAXES_all vv where vv.GROUP_ID=aida.PAY_AWT_GROUP_ID) tax_name
from ap_invoices_all aia, ap_invoice_lines_all aila, ap_invoice_distributions_all aida
--,AP_AWT_GROUP_TAXES_all vv
--,ap_invoices_V aiv
where aia.invoice_id=aila.invoice_id
and aia.invoice_id=aida.invoice_id
and aila.invoice_id=aida.invoice_id
and aila.line_number=aida.invoice_line_number
--and vv.GROUP_ID(+)=aida.PAY_AWT_GROUP_ID
--and vv.ORG_ID=aida.ORG_ID
--and aiv.invoice_id=aia.invoice_id
--and aiv.invoice_id=aila.invoice_id
--and aiv.invoice_id=aida.invoice_id
--and aia.org_id=?
--and aida.invoice_id=10305 ---249912
and aia.DOC_SEQUENCE_VALUE = 200900972
)
--------------------------------------------------------------------------------------------------------------------------
select aia.INVOICE_AMOUNT,--aia.DOC_SEQUENCE_VALUE, vv.tax_name, aida.AMOUNT, ee.TAX_RATE,
sum(aida.AMOUNT*ee.TAX_RATE/100) WHT
, aia.INVOICE_AMOUNT- sum(aida.AMOUNT*ee.TAX_RATE/100) Paid
from ap_invoices_all aia,
ap_invoice_distributions_all aida,
AP_AWT_GROUP_TAXES_all vv,
ap_awt_tax_rates_all ee
--,AP_AWT_GROUP_TAXES_all vv
--,ap_invoices_V aiv
where aia.invoice_id = aida.invoice_id
and vv.GROUP_ID=aida.PAY_AWT_GROUP_ID
and vv.ORG_ID=aia.ORG_ID
and aia.ORG_ID =aida.ORG_ID
and ee.TAX_NAME = vv.TAX_NAME
and ee.ORG_ID=vv.ORG_ID
and ee.END_DATE is null
--and vv.GROUP_ID(+)=aida.PAY_AWT_GROUP_ID
--and vv.ORG_ID=aida.ORG_ID
--and aiv.invoice_id=aia.invoice_id
--and aiv.invoice_id=aila.invoice_id
--and aiv.invoice_id=aida.invoice_id
--and aia.org_id=?
--and aida.invoice_id=10305 ---249912
and aia.DOC_SEQUENCE_VALUE =15012198
group by aia.INVOICE_AMOUNT
--------------------------------------------------------------------------------------------------------------------------
select distinct INV, SUM(WHT),INV-sum(WHT)PAID from (select
-- round(abs(sum(vv.AMOUNT*cc.tax_rate/100)),2)
vv.invoice_amount INV,
sum(abs(vv.AMOUNT*cc.TAX_RATE/100)) WHT
--sum(abs(round(round(vv.AMOUNT,2)*cc.TAX_RATE,0)/100)) WHT
from
(select aa.TAX_RATE, ff.GROUP_ID from ap_awt_group_taxes_all ff,
ap_awt_tax_rates_all aa
where ff.TAX_NAME = aa.TAX_NAME
and aa.END_DATE is null
and aa.ORG_ID = ff.ORG_ID
-- and aa.ORG_ID = :ORG_ID
group by ff.GROUP_ID , aa.TAX_RATE
) cc,
( select sum(tt.AMOUNT) Amount, tt.PAY_AWT_GROUP_ID, gg.DOC_SEQUENCE_VALUE, gg.INVOICE_AMOUNT, tt.org_id, tt.invoice_id
from ap_invoice_distributions_all tt, ap_invoices_all gg
where tt.INVOICE_ID = gg.INVOICE_ID
and tt.org_id = gg.org_id
--and gg.ORG_ID = :ORG_ID
group by tt.PAY_AWT_GROUP_ID, gg.DOC_SEQUENCE_VALUE,gg.INVOICE_AMOUNT ,tt.org_id, tt.invoice_id) vv
where cc.group_id = vv.pay_awt_group_id
and vv.DOC_SEQUENCE_VALUE = 16009616
-- and vv.invoice_id = :INVOICE_ID
and vv.org_id = 82
group by vv.PAY_AWT_GROUP_ID, vv.invoice_amount
)
group by INV
select DOC_SEQUENCE_VALUE, tax_name from(
select aia.DOC_SEQUENCE_VALUE,(select vv.TAX_NAME from AP_AWT_GROUP_TAXES_all vv where vv.GROUP_ID=aida.PAY_AWT_GROUP_ID) tax_name
from ap_invoices_all aia, ap_invoice_lines_all aila, ap_invoice_distributions_all aida
--,AP_AWT_GROUP_TAXES_all vv
--,ap_invoices_V aiv
where aia.invoice_id=aila.invoice_id
and aia.invoice_id=aida.invoice_id
and aila.invoice_id=aida.invoice_id
and aila.line_number=aida.invoice_line_number
--and vv.GROUP_ID(+)=aida.PAY_AWT_GROUP_ID
--and vv.ORG_ID=aida.ORG_ID
--and aiv.invoice_id=aia.invoice_id
--and aiv.invoice_id=aila.invoice_id
--and aiv.invoice_id=aida.invoice_id
--and aia.org_id=?
--and aida.invoice_id=10305 ---249912
and aia.DOC_SEQUENCE_VALUE = 200900972
)
--------------------------------------------------------------------------------------------------------------------------
select aia.INVOICE_AMOUNT,--aia.DOC_SEQUENCE_VALUE, vv.tax_name, aida.AMOUNT, ee.TAX_RATE,
sum(aida.AMOUNT*ee.TAX_RATE/100) WHT
, aia.INVOICE_AMOUNT- sum(aida.AMOUNT*ee.TAX_RATE/100) Paid
from ap_invoices_all aia,
ap_invoice_distributions_all aida,
AP_AWT_GROUP_TAXES_all vv,
ap_awt_tax_rates_all ee
--,AP_AWT_GROUP_TAXES_all vv
--,ap_invoices_V aiv
where aia.invoice_id = aida.invoice_id
and vv.GROUP_ID=aida.PAY_AWT_GROUP_ID
and vv.ORG_ID=aia.ORG_ID
and aia.ORG_ID =aida.ORG_ID
and ee.TAX_NAME = vv.TAX_NAME
and ee.ORG_ID=vv.ORG_ID
and ee.END_DATE is null
--and vv.GROUP_ID(+)=aida.PAY_AWT_GROUP_ID
--and vv.ORG_ID=aida.ORG_ID
--and aiv.invoice_id=aia.invoice_id
--and aiv.invoice_id=aila.invoice_id
--and aiv.invoice_id=aida.invoice_id
--and aia.org_id=?
--and aida.invoice_id=10305 ---249912
and aia.DOC_SEQUENCE_VALUE =15012198
group by aia.INVOICE_AMOUNT
--------------------------------------------------------------------------------------------------------------------------
-- round(abs(sum(vv.AMOUNT*cc.tax_rate/100)),2)
vv.invoice_amount INV,
sum(abs(vv.AMOUNT*cc.TAX_RATE/100)) WHT
--sum(abs(round(round(vv.AMOUNT,2)*cc.TAX_RATE,0)/100)) WHT
from
(select aa.TAX_RATE, ff.GROUP_ID from ap_awt_group_taxes_all ff,
ap_awt_tax_rates_all aa
where ff.TAX_NAME = aa.TAX_NAME
and aa.END_DATE is null
and aa.ORG_ID = ff.ORG_ID
-- and aa.ORG_ID = :ORG_ID
group by ff.GROUP_ID , aa.TAX_RATE
) cc,
( select sum(tt.AMOUNT) Amount, tt.PAY_AWT_GROUP_ID, gg.DOC_SEQUENCE_VALUE, gg.INVOICE_AMOUNT, tt.org_id, tt.invoice_id
from ap_invoice_distributions_all tt, ap_invoices_all gg
where tt.INVOICE_ID = gg.INVOICE_ID
and tt.org_id = gg.org_id
--and gg.ORG_ID = :ORG_ID
group by tt.PAY_AWT_GROUP_ID, gg.DOC_SEQUENCE_VALUE,gg.INVOICE_AMOUNT ,tt.org_id, tt.invoice_id) vv
where cc.group_id = vv.pay_awt_group_id
and vv.DOC_SEQUENCE_VALUE = 16009616
-- and vv.invoice_id = :INVOICE_ID
and vv.org_id = 82
group by vv.PAY_AWT_GROUP_ID, vv.invoice_amount
)
group by INV
Saturday, 18 June 2016
Site, Location and Code Combination Checking in AP
http://aporaclepayables.blogspot.com/2016/06/for-invoice-header-select-ps.html
For Invoice Header
select aia.DOC_SEQUENCE_VALUE,aia.GL_DATE,ps.VENDOR_SITE_CODE, aia.ACCTS_PAY_CODE_COMBINATION_ID,gcc.SEGMENT1, gcc.SEGMENT2, gcc.SEGMENT4
from po_vendors pv, po_vendor_sites_all ps, ap_invoices_all aia, ap_invoice_distributions_all ai, gl_code_combinations gcc
where pv.VENDOR_ID=ps.VENDOR_ID
and aia.VENDOR_ID=pv.VENDOR_ID
and ps.VENDOR_ID=aia.VENDOR_ID
and ps.VENDOR_SITE_ID=aia.VENDOR_SITE_ID
and ps.ORG_ID=aia.ORG_ID
and ai.INVOICE_ID=aia.INVOICE_ID
and ai.ORG_ID=aia.ORG_ID
and ps.ORG_ID=ai.ORG_ID
and gcc.CODE_COMBINATION_ID=aia.ACCTS_PAY_CODE_COMBINATION_ID
and ps.ACCTS_PAY_CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
*/
For Invoice Header
select aia.DOC_SEQUENCE_VALUE,aia.GL_DATE,ps.VENDOR_SITE_CODE, aia.ACCTS_PAY_CODE_COMBINATION_ID,gcc.SEGMENT1, gcc.SEGMENT2, gcc.SEGMENT4
from po_vendors pv, po_vendor_sites_all ps, ap_invoices_all aia, ap_invoice_distributions_all ai, gl_code_combinations gcc
where pv.VENDOR_ID=ps.VENDOR_ID
and aia.VENDOR_ID=pv.VENDOR_ID
and ps.VENDOR_ID=aia.VENDOR_ID
and ps.VENDOR_SITE_ID=aia.VENDOR_SITE_ID
and ps.ORG_ID=aia.ORG_ID
and ai.INVOICE_ID=aia.INVOICE_ID
and ai.ORG_ID=aia.ORG_ID
and ps.ORG_ID=ai.ORG_ID
and gcc.CODE_COMBINATION_ID=aia.ACCTS_PAY_CODE_COMBINATION_ID
and ps.VENDOR_SITE_CODE='CH'
and gcc.SEGMENT2!=00
/*and gcc.CODE_COMBINATION_ID=ai.DIST_CODE_COMBINATION_IDand ps.ACCTS_PAY_CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
*/
For Invoice Distribution
select aia.DOC_SEQUENCE_VALUE,aia.GL_DATE,ps.VENDOR_SITE_CODE, aia.ACCTS_PAY_CODE_COMBINATION_ID,gcc.SEGMENT1, gcc.SEGMENT2, gcc.SEGMENT4
from po_vendors pv, po_vendor_sites_all ps, ap_invoices_all aia, ap_invoice_distributions_all ai, gl_code_combinations gcc
where pv.VENDOR_ID=ps.VENDOR_ID
and aia.VENDOR_ID=pv.VENDOR_ID
and ps.VENDOR_ID=aia.VENDOR_ID
and ps.VENDOR_SITE_ID=aia.VENDOR_SITE_ID
and ps.ORG_ID=aia.ORG_ID
and ai.INVOICE_ID=aia.INVOICE_ID
and ai.ORG_ID=aia.ORG_ID
and ps.ORG_ID=ai.ORG_ID
--and gcc.CODE_COMBINATION_ID=aia.ACCTS_PAY_CODE_COMBINATION_ID
and ps.VENDOR_SITE_CODE='HO'
and gcc.SEGMENT2!=05
and aia.GL_DATE>'01-JAN-2015'
and gcc.CODE_COMBINATION_ID=ai.DIST_CODE_COMBINATION_ID
--and ps.ACCTS_PAY_CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
from po_vendors pv, po_vendor_sites_all ps, ap_invoices_all aia, ap_invoice_distributions_all ai, gl_code_combinations gcc
where pv.VENDOR_ID=ps.VENDOR_ID
and aia.VENDOR_ID=pv.VENDOR_ID
and ps.VENDOR_ID=aia.VENDOR_ID
and ps.VENDOR_SITE_ID=aia.VENDOR_SITE_ID
and ps.ORG_ID=aia.ORG_ID
and ai.INVOICE_ID=aia.INVOICE_ID
and ai.ORG_ID=aia.ORG_ID
and ps.ORG_ID=ai.ORG_ID
--and gcc.CODE_COMBINATION_ID=aia.ACCTS_PAY_CODE_COMBINATION_ID
and ps.VENDOR_SITE_CODE='HO'
and gcc.SEGMENT2!=05
and aia.GL_DATE>'01-JAN-2015'
and gcc.CODE_COMBINATION_ID=ai.DIST_CODE_COMBINATION_ID
--and ps.ACCTS_PAY_CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
Subscribe to:
Posts (Atom)