ap_invoices_all a, ap_invoice_lines_all b, ap_invoice_distributions_all c, ap_invoice_payments_all d ,ap_checks_all e Joining's - Oracle EBS R12

https://aporaclepayables.blogspot.com/2018/09/apinvoicesall-apinvoicelinesall-b.html

select count(*) from ap_invoices_all a, ap_invoice_lines_all b, ap_invoice_distributions_all c,
ap_invoice_payments_all d ,ap_checks_all e
where a.INVOICE_ID=b.INVOICE_ID
and b.INVOICE_ID=c.INVOICE_ID
and a.INVOICE_ID=c.INVOICE_ID
and b.LINE_NUMBER=c.INVOICE_LINE_NUMBER
and d.INVOICE_ID=c.INVOICE_ID
and d.CHECK_ID=e.CHECK_id
and d.ORG_ID=c.ORG_ID
and a.ORG_ID=b.ORG_ID
and b.ORG_ID=c.ORG_ID
and a.ORG_ID=c.ORG_ID
and d.ORG_ID=e.ORG_ID
and c.ORG_ID=82
and a.ORG_ID=82
and b.ORG_ID=82
and d.ORG_ID=82
and e.ORG_ID=82
and d.REVERSAL_FLAG='N'

PLSQL Query for Tax Detail with Certificate - Oracle EBS R12

https://aporaclepayables.blogspot.com/2018/08/plsql-query-for-tax-detail-with.html

PLSQL Query for Tax Detail with Certificate


--select * from hr_operating_units

SELECT
null ID_TYPE
,vnd.vat_registration_num SUPNTN
,null CNIC
,null PASSPORT
,null MOBILE
,vnd.vendor_name
,null BUSINESSNAME
,aidl1.DIST_CODE_COMBINATION_ID
,vnds.address_line1||' '||vnds.address_line2||' '||vnds.address_line3||' '||vnds.city ADDRESS
,atg.description TAXNAME
,null PAYMENTSECTION
,to_char(aca.cleared_date,'yyyymmdd') CLEAREDDATE
,SUM (nvl (aidl1.base_amount, aidl1.amount)) TAXABLE_AMOUNT,
aidl1.Description Line_Description
,null TAXRATE
,SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (nvl (aidl.base_amount, aidl.amount)))) TAX_AMOUNT

,case when nvl(SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (nvl (aidl.base_amount, aidl.amount)))),0)>0 then
'Y'
 ELSE
'N' END TAX_DEDUCTED

,case when nvl(SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (nvl (aidl.base_amount, aidl.amount)))),0)>0 then
'R'
 ELSE
'NR' END R_NR

,SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (nvl (aidl.base_amount, aidl.amount)))) TAX_DEPOSITED

,null DEPOSITDATE
,null CPR_NO
,null PROVISION

/*,APTAX.CERTIFICATE_NUMBER CERTIFICATE_NO
,APTAX.START_DATE CERTIFICATE_DATE
,APTAX.ATTRIBUTE1 CERTIFICATE_AUTHORITY
*/
,(select CERTIFICATE_NUMBER from AP_AWT_TAX_RATES_ALL
  where
  -- (start_date between :p_date_from and :p_date_to or end_date between :p_date_from and :p_date_to)
  org_id  =376--:P_ORG_ID   --  added by IACS ( IMRAN 27-july-2015 )
  and aca.cleared_date BETWEEN START_DATE AND NVL(END_DATE,'31-DEC-2099')
  and vendor_id=aia.vendor_id and vendor_site_id=aia.vendor_site_id and RATE_TYPE='CERTIFICATE'
  and tax_name=atg.tax_name) CERTIFICATE_NO

,(select START_DATE from AP_AWT_TAX_RATES_ALL
  where
  -- (start_date between :p_date_from and :p_date_to or end_date between :p_date_from and :p_date_to)
   org_id  =376--:P_ORG_ID   --  added by IACS ( IMRAN 27-july-2015 )
  and  aca.cleared_date BETWEEN START_DATE AND NVL(END_DATE,'31-DEC-2099')
  and vendor_id=aia.vendor_id and vendor_site_id=aia.vendor_site_id and RATE_TYPE='CERTIFICATE'
  and tax_name=atg.tax_name) CERTIFICATE_DATE

,(select ATTRIBUTE1 from AP_AWT_TAX_RATES_ALL
  where
  -- (start_date between :p_date_from and :p_date_to or end_date between :p_date_from and :p_date_to)
   org_id  =376--:P_ORG_ID   --  added by IACS ( IMRAN 27-july-2015 )
   AND aca.cleared_date BETWEEN START_DATE AND NVL(END_DATE,'31-DEC-2099')
  and vendor_id=aia.vendor_id and vendor_site_id=aia.vendor_site_id and RATE_TYPE='CERTIFICATE'
  and tax_name=atg.tax_name) CERTIFICATE_AUTHORITY
,atg.GROUP_NAME TAXCD
,aca.doc_sequence_value inv_apv_num
,aia.DOC_SEQUENCE_VALUE inv_apn_num
,aca.bank_account_name BANK
,aidl.WITHHOLDING_TAX_CODE_ID, atg.TAX_ID, -- ADDED ON 10-JUN-15
gcc.SEGMENT4 Account,
gl_flexfields_pkg.get_description_sql 
                                      (gcc.CHART_OF_ACCOUNTS_ID ,--- chart of account id 
                                       4,----- Position of segment 
                                       gcc.segment4 ---- Segment value 
                                      )  ACC_DESC
/*,(selectgcc.SEGMENT4 from ap_invoice_distributions_all al ,gl_code_combinations gcc where gcc.CODE_COMBINATION_ID = al.DIST_CODE_COMBINATION_ID
and al.INVOICE_ID = aia.INVOICE_ID ) ACC*/
FROM ( select * from ap_invoice_distributions_all
       WHERE  org_id  =376/*:P_ORG_ID*/   and  NVL(WITHHOLDING_TAX_CODE_ID,-1) NOT IN (
                                                     SELECT TAX_ID
                                                     FROM Ap_Tax_Codes_All
                                                     WHERE org_id  = 376--:P_ORG_ID   --  added by IACS ( IMRAN 27-july-2015 )
                                                     AND DESCRIPTION LIKE 'GST-SRO98'
                                                   )
     ) aidl
,    ap_invoice_distributions_all aidl1
--,    ap_awt_groups atg
/*
,      (SELECT
      AG.GROUP_ID, AG.NAME GROUP_NAME, AG.DESCRIPTION, AGT.TAX_NAME
      FROM
      AP_AWT_GROUPS AG,
      AP_AWT_GROUP_TAXES_ALL AGT
      WHERE 1=1
      AND AG.GROUP_ID = AGT.GROUP_ID) atg*/ -- 10-JUN-14

,     (SELECT
      AG.GROUP_ID, AG.NAME GROUP_NAME, AG.DESCRIPTION, AGT.TAX_NAME, TCA.TAX_ID
      FROM
      AP_AWT_GROUPS AG,
      AP_AWT_GROUP_TAXES_ALL AGT,
      AP_TAX_CODES_ALL TCA
      WHERE 1=1
      AND  AGT.org_id  =376--:P_ORG_ID    --  added by IACS ( IMRAN 27-july-2015 )
      AND AG.GROUP_ID = AGT.GROUP_ID
      AND AGT.TAX_NAME = TCA.NAME
      --AND AG.NAME IN ('2E8PST0','2E8')
      ) atg


,    ap_invoices_all aia
,    ap_suppliers vnd
,    ap_supplier_sites_all vnds
,    ap_invoice_payments_all aipa
,    ap_checks_all aca
,gl_code_combinations gcc

/*,    ( select * from AP_AWT_TAX_RATES_ALL
       where (  :p_date_from   BETWEEN START_DATE AND END_DATE OR :p_date_to BETWEEN START_DATE AND END_DATE )
     ) aptax
*/
WHERE 1=1
 AND  aia.org_id  =376--:P_ORG_ID    --  added by IACS ( IMRAN 27-july-2015 )
     AND aidl1.invoice_distribution_id = aidl.awt_related_id
     AND aidl1.PAY_awt_group_id = atg.group_id -- PAY_AWT_GROUP_ID column is used in R12.2.4 and AWT_GROUP_ID column is used in R12.0.6
     AND aidl.WITHHOLDING_TAX_CODE_ID=atg.TAX_ID -- ADDED ON 10-JUN-15
     AND aia.invoice_id = aidl.invoice_id
     --and gcc.CODE_COMBINATION_ID=aidl.DIST_CODE_COMBINATION_ID
     and gcc.CODE_COMBINATION_ID=aidl1.DIST_CODE_COMBINATION_ID
     AND vnd.vendor_id = aia.vendor_id
     AND vnd.vendor_id = vnds.vendor_id
     AND aia.vendor_site_id = vnds.vendor_site_id
     AND aipa.invoice_id = aia.invoice_id
     AND aca.check_id = aipa.check_id
--     AND aia.vendor_id= aptax.vendor_id(+)
--     AND aia.vendor_site_id=aptax.vendor_site_id(+)
     AND aca.cleared_date IS NOT NULL
     AND (aipa.reversal_flag = 'N' OR aipa.reversal_flag is null)
     AND (aidl.reversal_flag = 'N' or aidl.reversal_flag is null)
     AND (aidl1.reversal_flag = 'N' or aidl1.reversal_flag is null)
    AND trunc(aca.cleared_date) BETWEEN '01-JUL-2016'and'30-JUN-2017 '--nvl (:p_date_from, aca.cleared_date) AND nvl (:p_date_to, aca.cleared_date)                               --  '01-MAR-2014'  and  '31-MAR-2014'    --
     --AND vnd.vendor_name BETWEEN nvl (:cf_from_vendor_dsp, vnd.vendor_name) AND nvl(:cf_to_vendor_dsp, vnd.vendor_name)
GROUP BY
         atg.GROUP_NAME
,        atg.tax_name
,        atg.description
,        vnd.vendor_name
,        vnd.segment1
,        vnds.address_line1
,        vnds.address_line2
,        vnds.address_line3
,        vnds.city
,        vnd.vat_registration_num
,        aia.doc_sequence_value
,        aca.doc_sequence_value
,        aca.bank_account_name
,        aia.invoice_currency_code
,        aca.cleared_date
,        aia.invoice_id
,        aia.vendor_id, aia.vendor_site_id,gcc.SEGMENT4, aidl1.DIST_CODE_COMBINATION_ID
,aidl.WITHHOLDING_TAX_CODE_ID, atg.TAX_ID -- ADDED ON 10-JUN-15
,aidl1.Description,gcc.CHART_OF_ACCOUNTS_ID
--,        APTAX.TAX_NAME, APTAX.START_DATE, APTAX.CERTIFICATE_NUMBER, APTAX.ATTRIBUTE1
ORDER BY
        TAXCD,
       aca.cleared_date,
       aia.doc_sequence_value

ASC

PLSQL Query for Withholding Tax Detail - Oracle EBS R12

https://aporaclepayables.blogspot.com/2018/08/plsql-query-for-withholding-tax-detail.html


PLSQL Query for Withholding Tax Detail 


SELECT aia.invoice_id,
       gcc.SEGMENT2 location
     
       ------------------------ Aggregating multiple invoice distribution lines using SUM/Decode Combination -----------------------------------------------------------------------------
     
      ,
       atg.description          Payment_Section,
       vnd.vat_registration_num TaxPayer_NTN,
       vnd.attribute13          TaxPayer_CNIC
     
       --,        SUM (aidl1.amount)  - SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (aidl.amount))) dist_net_amount_ent
       --,        SUM (nvl (aidl1.base_amount, aidl1.amount)) - SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (nvl (aidl.base_amount, aidl.amount)))) dist_net_amount_fnc
     
       --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      ,
       aidl.IRS_NO,NVL(TAX_TYPE,'No Tax type')TAX_TYPE,Section,Tax_exemption,
       --,        aia.invoice_currency_code inv_currency
     
       vnd.vendor_name Taxpayer_name
       --,        vnd.segment1 vendor_code
      ,
       vnds.city TaxPayer_City,
       vnds.address_line1 || ' ' || vnds.address_line2 || ' ' ||
       vnds.address_line3 TaxPayer_Address,
       vnds.attribute14 TaxPayer_Status,
       vnds.attribute15 TaxPayer_Business_Name
       --,        SUM (aidl1.amount) dist_amount_ent
      ,
       SUM(nvl(aidl1.base_amount, aidl1.amount)) Taxable_Amount,
       aidl1.DESCRIPTION Line_Description,
       --,        SUM (decode (aidl.line_type_lookup_code, 'AWT', abs(aidl.amount))) dist_tax_amount_ent
     
       SUM(decode(aidl.line_type_lookup_code,
                  'AWT',
                  abs(nvl(aidl.base_amount, aidl.amount)))) Tax_Amount,
       vnd.segment1 Supplier_No,
       aca.doc_sequence_value APV_NO,
       aca.cleared_date APV_POSTED_DATE,
       aia.doc_sequence_value APN_NO,
       atg.NAME Tax_Code,
       aca.CURRENCY_CODE
       --,        atg.description Tax_Group_Desc
      ,
       aca.bank_account_name,
       U.USER_NAME,
       gcc.SEGMENT4 Account,
gl_flexfields_pkg.get_description_sql 
                                      (gcc.CHART_OF_ACCOUNTS_ID ,--- chart of account id 
                                       4,----- Position of segment 
                                       gcc.segment4 ---- Segment value 
                                      )  ACC_DESC
  FROM -- ap_invoice_distributions_all aidl                                                               -- used for tax grouping     --- Marked on 15 September 2010 By Muhammad Raheem (Saad)
       -- Distribution line calculating tax on sables basis, that create multiple TAX lines so these lines where sum to make single TAX line
        (SELECT sum(amount) amount,
                sum(base_amount) base_amount,
                line_type_lookup_code,
                PAY_awt_group_id, -- PAY_AWT_GROUP_ID column is used in R12.2.4 and AWT_GROUP_ID column is used in R12.0.6
                reversal_flag,
                invoice_id,
                awt_related_id,(SELECT TCA.ATTRIBUTE6
                   FROM Ap_Tax_Codes_All TCA
                  where TCA.TAX_ID = NVL(WITHHOLDING_TAX_CODE_ID, -1)) Tax_exemption,
                (SELECT TCA.ATTRIBUTE5
                   FROM Ap_Tax_Codes_All TCA
                  where TCA.TAX_ID = NVL(WITHHOLDING_TAX_CODE_ID, -1)) Section,
                (SELECT TCA.ATTRIBUTE4
                   FROM Ap_Tax_Codes_All TCA
                  where TCA.TAX_ID = NVL(WITHHOLDING_TAX_CODE_ID, -1)) IRS_NO
                  ,
                  (SELECT TCA.ATTRIBUTE3
                   FROM Ap_Tax_Codes_All TCA
                  where TCA.TAX_ID = NVL(WITHHOLDING_TAX_CODE_ID, -1)) TAX_TYPE
           FROM ap_invoice_distributions_all
          WHERE org_id = 376
                 ------------------------ CHANGE TAX TYPE PARAMETER ------------------------------------------------------ 
                 AND  NVL(WITHHOLDING_TAX_CODE_ID,-1)
                 IN
                 (
                  SELECT TAX_ID
                 FROM Ap_Tax_Codes_All  TCA
                 WHERE  ORG_ID=   376
                 --AND    nvl(TCA.ATTRIBUTE3,'NA') = nvl( :P_TAX_TYPE , nvl(TCA.ATTRIBUTE3,'NA')  )
                 )
               -------------------------------------------------------------------------------------------
                 AND  NVL(WITHHOLDING_TAX_CODE_ID,-1)
                 NOT IN
                 (                                               --- ADDED ON 28 FEB -- --  added org_id by IACS ( IMRAN 27-july-2015 )
                 SELECT TAX_ID FROM Ap_Tax_Codes_All
                 WHERE  org_id  =376
                 AND  DESCRIPTION LIKE 'GST-SRO98'
                 )       --- ADDED ON 28 FEB  --  added org_id by IACS ( IMRAN 27-july-2015 )
                 group by PAY_awt_group_id, reversal_flag, line_type_lookup_code, invoice_id, awt_related_id,attribute3,WITHHOLDING_TAX_CODE_ID
                 ) aidl    -- used for tax grouping   
,        ap_invoice_distributions_all aidl1                                                                        -- used for item grouping
,        ap_awt_groups atg
,        ap_invoices_all aia
,        ap_suppliers vnd
,        ap_supplier_sites_all vnds
,        ap_invoice_payments_all aipa
,        ap_checks_all aca
,        fnd_user u
,        gl_code_combinations  gcc
   WHERE
--
-- -------* key join codition *------------
                 aidl1.org_id  =376-- ADDED ON 31-JUL-15
     and aia.org_id=376-- ADDED ON 31-JUL-15
     and aipa.org_id=376-- ADDED ON 31-JUL-15
     and aca.org_id=376  -- ADDED ON 31-JUL-15
     and aia.org_id=aidl1.org_id -- ADDED ON 31-JUL-15
     and aia.org_id=aipa.org_id -- ADDED ON 31-JUL-15
     and aia.org_id=aca.org_id  -- ADDED ON 31-JUL-15
     AND     aidl1.invoice_distribution_id = aidl.awt_related_id
-----------------------------------------------------
     AND aidl1.PAY_awt_group_id = atg.group_id -- PAY_AWT_GROUP_ID column is used in R12.2.4 and AWT_GROUP_ID column is used in R12.0.6
     AND aia.invoice_id = aidl.invoice_id
     AND vnd.vendor_id = aia.vendor_id
     AND vnd.vendor_id = vnds.vendor_id
     AND aia.vendor_site_id = vnds.vendor_site_id
     AND aipa.invoice_id = aia.invoice_id
     AND aca.check_id = aipa.check_id
    -- and  gcc.SEGMENT2  between  nvl(:P_FROM_LOCATION  ,gcc.SEGMENT2 )  and  nvl( :P_TO_LOCATION ,gcc.SEGMENT2 ) 
     AND aca.cleared_date IS NOT NULL
     AND aidl.amount <> '0'                                                                                       -- do not print zero value tax lines
     AND (aipa.reversal_flag = 'N' OR aipa.reversal_flag is null)
     AND (aidl.reversal_flag = 'N' or aidl.reversal_flag is null)
     AND (aidl1.reversal_flag = 'N' or aidl1.reversal_flag is null)
-- -----------Parameters-------------------
     AND trunc(aca.cleared_date) BETWEEN '01-JUL-2016' and '30-JUN-2017' --nvl (:p_date_from, aca.cleared_date) AND nvl (:p_date_to, aca.cleared_date)
     --AND atg.NAME BETWEEN nvl (:p_tax_code_from, atg.NAME) AND nvl (:p_tax_code_to, atg.NAME)
--     AND aia.doc_sequence_value = nvl (:p_apn_num, aia.doc_sequence_value)
     --AND vnd.vendor_id BETWEEN nvl (:p_vendor_from, vnd.vendor_id) AND nvl(:p_vendor_to, vnd.vendor_id)
     --and aca.CURRENCY_CODE between nvl (:CCY_from,aca.CURRENCY_CODE) and nvl (:CCY_to,aca.CURRENCY_CODE)
     and u.USER_ID=aia.CREATED_BY
    -- and U.user_id=NVL(:P_USER_ID,U.USER_ID)
     and  aidl1.DIST_CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
   
-- ----------------------------------------------
GROUP BY
         atg.NAME
,        atg.description
,        vnd.vendor_name
,        vnd.segment1
,        vnds.city
,        vnd.vat_registration_num
,        aia.doc_sequence_value
,        aca.doc_sequence_value
,        aia.invoice_currency_code
,        aca.cleared_date
,        aia.invoice_id
,        aca.currency_code
,        vnds.address_line1
,        vnds.address_line2
,        vnds.address_line3
,        vnds.attribute14
,        vnds.attribute15
,        vnd.attribute13
,        aca.bank_account_name
,        aidl.IRS_NO,TAX_TYPE,U.USER_NAME,Section,Tax_exemption
,gcc.SEGMENT2, gcc.SEGMENT4,aidl1.DESCRIPTION,gcc.CHART_OF_ACCOUNTS_ID

ORDER BY
       Tax_Code,
       aca.cleared_date,
       aia.doc_sequence_value
ASC;

PLSQL Query for Payables Subledger Account Analysis Report - Oracle EBS R12

https://aporaclepayables.blogspot.com/2018/07/p.html

Subledger Account Analysis Report PLSQL Query for Payables

---Begning Balance 

   select sum(b.ACCOUNTED_DR) , sum(b.ACCOUNTED_CR) ,  sum(b.ACCOUNTED_DR) - sum(b.ACCOUNTED_CR) from xla_ae_headers a, xla_ae_lines b, gl_code_combinations c
    where a.AE_HEADER_ID=b.AE_HEADER_ID
    and a.LEDGER_ID=b.LEDGER_ID
    and a.APPLICATION_ID=b.APPLICATION_ID
    and b.CODE_COMBINATION_ID=c.CODE_COMBINATION_ID
    and a.JE_CATEGORY_NAME in ('Purchase Invoices' , 'Reconciled Payments')
    and a.APPLICATION_ID = 200
    and a.LEDGER_ID = 2031
    and b.ACCOUNTING_DATE < = '30-JUN-2017'
    and c.SEGMENT1 = '02'
    and c.SEGMENT2 = '002'
    and c.SEGMENT3 = '000'
    and c.SEGMENT4 = '248111100'
 

 --Period Debit Credit  ----   Depends on the Accounting date you provide   

    select a.DOC_SEQUENCE_VALUE , a.JE_CATEGORY_NAME , b.ACCOUNTING_DATE, b.ACCOUNTED_DR , b.ACCOUNTED_CR from xla_ae_headers a, xla_ae_lines b, gl_code_combinations c
    where a.AE_HEADER_ID=b.AE_HEADER_ID
    and a.LEDGER_ID=b.LEDGER_ID
    and a.APPLICATION_ID=b.APPLICATION_ID
    and b.CODE_COMBINATION_ID=c.CODE_COMBINATION_ID
    and a.JE_CATEGORY_NAME in ('Purchase Invoices' , 'Reconciled Payments')
    and a.APPLICATION_ID = 200
    and a.LEDGER_ID = 2031
    and b.ACCOUNTING_DATE between '01-JUL-17' and '31-JUL-17'
    and c.SEGMENT1 = '02'
    and c.SEGMENT2 = '002'
    and c.SEGMENT3 = '000'
    and c.SEGMENT4 = '248111100'

 -------------------------------------------------------------------------------------------------------------------

Summary Report

select    BR.PERIOD_NAME, BEG  , DR , CR  , sum(BEG-CR+DR) END   from  

 

( select  /*a.DOC_SEQUENCE_VALUE DOC1 , a.JE_CATEGORY_NAME  J1, b.ACCOUNTING_DATE A1, */ c.SEGMENT4, c.SEGMENT2,c.SEGMENT1,c.SEGMENT3, a.LEDGER_ID ,sum(b.ACCOUNTED_DR) - sum( b.ACCOUNTED_CR) BEG

from xla_ae_headers a, xla_ae_lines b, gl_code_combinations c

where a.AE_HEADER_ID=b.AE_HEADER_ID

and a.LEDGER_ID=b.LEDGER_ID

and a.APPLICATION_ID=b.APPLICATION_ID

and b.CODE_COMBINATION_ID=c.CODE_COMBINATION_ID

and a.JE_CATEGORY_NAME in ('Purchase Invoices' , 'Reconciled Payments')

and a.APPLICATION_ID = 200

and a.LEDGER_ID = 2031

and b.ACCOUNTING_DATE < = '30-SEP-2017'

and c.SEGMENT1 = '02'

and c.SEGMENT2 = '002'

and c.SEGMENT3 = '000'

and c.SEGMENT4 = '248111100'

group by c.SEGMENT4, c.SEGMENT2,c.SEGMENT1,c.SEGMENT3, a.LEDGER_ID 

)  AR

,

    

( select/* a.DOC_SEQUENCE_VALUE DOC2 , a.JE_CATEGORY_NAME  J2, b.ACCOUNTING_DATE A2,*/ a.PERIOD_NAME,c.SEGMENT4, c.SEGMENT2,c.SEGMENT1,c.SEGMENT3, a.LEDGER_ID ,sum(b.ACCOUNTED_DR) DR , sum( b.ACCOUNTED_CR )  CR 

from xla_ae_headers a, xla_ae_lines b, gl_code_combinations c

where a.AE_HEADER_ID=b.AE_HEADER_ID

and a.LEDGER_ID=b.LEDGER_ID

and a.APPLICATION_ID=b.APPLICATION_ID

and b.CODE_COMBINATION_ID=c.CODE_COMBINATION_ID

and a.JE_CATEGORY_NAME in ('Purchase Invoices' , 'Reconciled Payments')

and a.APPLICATION_ID = 200

and a.LEDGER_ID = 2031

and b.ACCOUNTING_DATE between '01-OCT-17' and '31-OCT-17'

and c.SEGMENT1 = '02'

and c.SEGMENT2 = '002'

and c.SEGMENT3 = '000'

and c.SEGMENT4 = '248111100'

group by a.PERIOD_NAME,c.SEGMENT4, c.SEGMENT2,c.SEGMENT1,c.SEGMENT3, a.LEDGER_ID 

)  BR

where AR.SEGMENT4 = BR.SEGMENT4

and AR.SEGMENT2 = BR.SEGMENT2

and AR.SEGMENT1 = BR.SEGMENT1

and AR.SEGMENT3 = BR.SEGMENT3

and AR.LEDGER_ID = BR.LEDGER_ID

group by  BR.PERIOD_NAME,BEG  , DR , CR 

PLSQL Query for Supplier Exemption Certificate Active/InActive - Oracle EBS R12

https://aporaclepayables.blogspot.com/2018/07/plsql-query-for-supplier-exemption.html

Supplier Exemption Certificate Active/InActive

select

 d.name,
 b.VENDOR_NAME,
 b.VENDOR_ID,
 b.SEGMENT1 "Supplier Number",
 c.VENDOR_SITE_CODE,
 a.TAX_NAME,
 a.CERTIFICATE_NUMBER,
 a.RATE_TYPE,
 a.PRIORITY,
 a.TAX_RATE,
 /* nvl(a.START_DATE,sysdate - 365000) st,
 nvl(a.END_DATE,sysdate + 365000) ed,*/
 case
   when trunc(nvl(a.END_DATE, sysdate + 365000)) < trunc(sysdate) then
    'N'
   when trunc(nvl(a.START_DATE, sysdate - 365000)) > trunc(sysdate) then
    'N'
   else
    'Y'
 end Status,
 to_char(a.START_DATE, 'DD-MON-YYYY') "FROM",
 a.START_DATE,
 to_char(a.END_DATE, 'DD-MON-YYYY') "TO",
 a.END_DATE,
 a.COMMENTS,
 a.ATTRIBUTE1 "Issuing Autority"

  from AP_AWT_TAX_RATES_all  a,
       ap_suppliers          b,
       ap_supplier_sites_all c,
       hr_operating_units    d
 where b.VENDOR_ID = c.VENDOR_ID
   and a.RATE_TYPE = 'CERTIFICATE'
   and b.VENDOR_ID = a.VENDOR_ID
   and a.VENDOR_SITE_ID = c.VENDOR_SITE_ID
   and a.ORG_ID = c.ORG_ID
   and a.ORG_ID = d.organization_id
   and a.ORG_ID = NVL(:P_ORG_ID, a.ORG_ID)
   and b.segment1 = NVL(:SEGMENT1, b.SEGMENT1)
   and c.VENDOR_SITE_CODE = NVL(:VENDOR_SITE_CODE, c.VENDOR_SITE_CODE)
   
      /*
      and ((( nvl(a.START_DATE,sysdate - 365000) between
      to_date(nvl(:P_FROM_DATE,
      to_char(sysdate - 365000, 'DD-MON-YYYY')   ),
      'DD-MON-YYYY') AND
      to_date(nvl(:P_TO_DATE,
      to_char(sysdate + 365000, 'DD-MON-YYYY')),
      'DD-MON-YYYY')) or
      (nvl(a.END_DATE,sysdate + 365000) between
      to_date(nvl(:P_FROM_DATE,
      to_char(sysdate - 365000, 'DD-MON-YYYY')),
      'DD-MON-YYYY') and
      to_date(nvl(:P_TO_DATE,
      to_char(sysdate + 365000, 'DD-MON-YYYY')),
      'DD-MON-YYYY'))) OR
      ((to_date(nvl(:P_FROM_DATE,
      to_char(sysdate - 365000, 'DD-MON-YYYY')),
      'DD-MON-YYYY') between nvl(a.START_DATE,sysdate - 365000) AND nvl(a.END_DATE,sysdate + 365000)) or
      (to_date(nvl(:P_TO_DATE,
      to_char(sysdate + 365000, 'DD-MON-YYYY')),
      'DD-MON-YYYY') between nvl(a.START_DATE,sysdate - 365000) and nvl(a.END_DATE,sysdate + 365000)))) 
      */
   
   and (
     
        ((nvl(a.START_DATE, sysdate - 365000) between
        nvl(:P_FROM_DATE, sysdate - 365000) AND
        nvl(:P_TO_DATE, sysdate + 365000)) or
        (nvl(a.END_DATE, sysdate + 365000) between
        nvl(:P_FROM_DATE, sysdate - 365000) and
        nvl(:P_TO_DATE, sysdate + 365000))) OR
        ((nvl(:P_FROM_DATE, sysdate - 365000) between
        nvl(a.START_DATE, sysdate - 365000) AND
        nvl(a.END_DATE, sysdate + 365000)) or
        (nvl(:P_TO_DATE, sysdate + 365000) between
        nvl(a.START_DATE, sysdate - 365000) and
        nvl(a.END_DATE, sysdate + 365000)))
     
       )
   
   and NVL(case
             when trunc(nvl(a.END_DATE, sysdate + 365000)) < trunc(sysdate) then
              'N'
             when trunc(nvl(a.START_DATE, sysdate - 365000)) > trunc(sysdate) then
              'N'
             else
              'Y'
           end,
           'NA') = NVL(:P_STATUS,
                       NVL(case
                             when trunc(nvl(a.END_DATE, sysdate + 365000)) < trunc(sysdate) then
                              'N'
                             when trunc(nvl(a.START_DATE, sysdate - 365000)) > trunc(sysdate) then
                              'N'
                             else
                              'Y'
                           end,
                           'NA'))
--  and a.START_DATE is null

 order by a.ORG_ID, b.VENDOR_NAME, c.VENDOR_SITE_CODE, a.START_DATE

PLSQL Query to get WHT and GST Amounts separately on Each Item Line

https://aporaclepayables.blogspot.com/2018/06/plsql-query-to-get-wht-and-gst-amounts.html

select g.DOC_SEQUENCE_VALUE    APN_No,
       g.INVOICE_NUM,
       g.INVOICE_DATE,
       d.ACCOUNTING_DATE       Distribution_GL_DATE,
       d.description,
       e.cleared_date,
       b.VENDOR_NAME,
       d.line_type_lookup_code Line_Type,
   
       (case
         when d.ATTRIBUTE3 is null then
          gc.SEGMENT1 || '-' || gc.SEGMENT2 || '-' || gc.SEGMENT3 || '-' ||
          gc.SEGMENT4 || '-' || gc.SEGMENT5 || '-' || gc.SEGMENT6 || '-' ||
          gc.SEGMENT7 || '-' || gc.SEGMENT8
         when d.ATTRIBUTE3 is not null then
          (select gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||
                  gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 || '-' ||
                  gcc.segment7 || '-' || gcc.segment8
             from gl_code_combinations gcc
            where gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||
                  gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 || '-' ||
                  gcc.segment7 || '-' || gcc.segment8 = d.ATTRIBUTE3)
       end
   
       ) Account,
   
       NVL(d.base_amount, d.amount) Amount,

------Please Modify the below Section according to your Tax Setup (For my Case)----------

       (select SUM(NVL(d2.base_amount, d2.amount))
          from ap_invoice_distributions_all d2
         where d2.awt_related_id = d.invoice_distribution_id
           and d2.line_type_lookup_code = 'AWT'
           and d2.DESCRIPTION <> 'GST-SRO98'
           and d2.invoice_id = d.invoice_id) WHT,
   
       (select SUM(NVL(d2.base_amount, d2.amount))
          from ap_invoice_distributions_all d2
         where d2.awt_related_id = d.invoice_distribution_id
           and d2.line_type_lookup_code = 'AWT'
           and d2.DESCRIPTION = 'GST-SRO98'
           and d2.invoice_id = d.invoice_id) GST,

------------------------------------------------------------------------------------------------

       /*
       (select ''''||GRP.NAME
       from AP_AWT_GROUPS GRP
       where GRP.GROUP_ID = D.AWT_ORIGIN_GROUP_ID) AWT_GROUP,
       */
       (select '''' || grp2.name
          from AP_AWT_GROUPS GRP2
         where grp2.group_id = d.pay_awt_group_id) ITEM_GROUP,
       
         (select  sum(gpr4.TAX_RATE)
          from AP_AWT_GROUPS GRP2, AP_AWT_GROUP_TAXES_ALL GPR3,   AP_AWT_TAX_RATES_all gpr4
         where grp2.GROUP_ID = gpr3.GROUP_ID
         and gpr3.TAX_NAME=gpr4.TAX_NAME
         and gpr4.ORG_ID =82
         and gpr4.RATE_TYPE = 'STANDARD'
         and gpr3.ORG_ID=82
         and gpr4.END_DATE is null
         and grp2.group_id = d.pay_awt_group_id
         and gpr3.GROUP_ID=d.PAY_AWT_GROUP_ID
        -- and grp2.Description like '%GST%'
        ) Tax_Rate,
       
        -- select * from AP_AWT_TAX_RATES_ALL
     
        NVL(d.base_amount, d.amount) +  NVL((select SUM(NVL(d2.base_amount, d2.amount))
          from ap_invoice_distributions_all d2
         where d2.awt_related_id = d.invoice_distribution_id
           and d2.line_type_lookup_code = 'AWT'
           and d2.DESCRIPTION <> 'GST-SRO98'
           and d2.invoice_id = d.invoice_id),0) +   NVL((select SUM(NVL(d2.base_amount, d2.amount))
          from ap_invoice_distributions_all d2
         where d2.awt_related_id = d.invoice_distribution_id
           and d2.line_type_lookup_code = 'AWT'
           and d2.DESCRIPTION = 'GST-SRO98'
           and d2.invoice_id = d.invoice_id),0) Amount_Paid_Each_Item,
         
       g.AMOUNT_PAID Total_Amount_Paid,
       g.GL_DATE HEADER_GL_DATE

  from ap_invoice_distributions_All d,
       AP_INVOICE_LINES_All         L,
       ap_invoices_all              g,
       ap_suppliers                 b,
       AP_INVOICE_PAYMENTS_ALL      c,
       ap_checks_all                e,
       gl_code_combinations         gc
 WHERE D.INVOICE_ID = L.INVOICE_ID
   AND D.INVOICE_LINE_NUMBER = L.LINE_NUMBER
   and g.INVOICE_ID = L.INVOICE_ID
   and g.INVOICE_ID = d.INVOICE_ID
   and g.INVOICE_ID = c.INVOICE_ID
   and l.INVOICE_ID = c.INVOICE_ID
   and d.INVOICE_ID = c.INVOICE_ID
   and e.CHECK_ID = c.CHECK_ID
   and gc.CODE_COMBINATION_ID = d.DIST_CODE_COMBINATION_ID
   
   and g.ORG_ID = L.org_id
   and L.org_id = d.Org_id
   and l.ORG_ID = c.ORG_ID
   and c.ORG_ID = g.org_id
   and e.ORG_ID = c.ORG_ID
   and b.VENDOR_ID = g.VENDOR_ID
   and g.org_id = 82
   and c.REVERSAL_FLAG = 'N'
   and trunc(e.CLEARED_DATE) between NVL('01-JUL-14', e.CLEARED_DATE) and
       NVL('30-JUN-18', e.CLEARED_DATE)
      -- and b.VENDOR_NAME  = 'Distributors'
    --   and g.DOC_SEQUENCE_VALUE =16011198
       and d.line_type_lookup_code = 'ITEM'
--and g.INVOICE_ID=115693
--  and g.GL_DATE between '01-JUN-2017' and '30-JUN-2017'
--and d.invoice_id = 94273 -- 3511993--3547454

 order by g.DOC_SEQUENCE_VALUE

How to Change AP Invoice Number

https://aporaclepayables.blogspot.com/2018/06/ap-invoice-number-is-saved-in-following.html


Please apply it on TEST Instance first.

AP Invoice Number is stored in the below tables till Payment Accounting. Please check your Invoice_Id in all tables below


select * from AP_INVOICES_ALL a WHERE invoice_id = '149806' --Invoice_Num


select * from AP_DOCUMENTS_PAYABLE where calling_app_doc_unique_ref2 = '149806'  --Calling_App_Doc_Ref_Number (If AP_INVOICES_ALL  table is updated for Invoice_Num. It will automatically changed in this table)
   

select * from ZX_LINES_DET_FACTORS where trx_id = '149806' --TRX_NUMBER
 
      
SELECT * FROM IBY_DOCS_PAYABLE_ALL idp WHERE idp.CALLING_APP_DOC_UNIQUE_REF2 = '149806' --Calling_App_Doc_Ref_Number

                
select * from xla_transaction_entities_upg xte where xte.SOURCE_ID_INT_1 = '149806'  --Transaction_Number
   
                     
select * from xla_ae_headers where ae_header_id = 466374 --If found in Description (Please search here for ae_header_id)
 
                               
select * from gl_je_headers where je_header_id = 560651  --If found in Description (Please search here for je_header_id)