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

FRM-40742: Illegal Status Conversion On Record 1: New To Changed (Doc ID 1431987.1)

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

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.VENDOR_SITE_CODE='CH'

and gcc.SEGMENT2!=00

/*and gcc.CODE_COMBINATION_ID=ai.DIST_CODE_COMBINATION_ID
and 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