Wednesday, 14 November 2012

Example Query related to supplier/Payables in 11i/R12


select
v.invoice_id,
v.invoice_amount,
p.amount  Payment_Amount,
c.amount  Check_Amount,
c.check_id,
c.check_date,
c.bank_account_id,
b.bank_name,
b.bank_branch_name,
b.city
from
Ap_Checks_All           c,
AP_Invoice_Payments_all  p,
Ap_Invoices_All          v,
Ap_Bank_Branches         b,
Ap_Bank_Accounts_All     ba
where
           b.bank_branch_id=ba.bank_branch_id
and        c.bank_account_id=ba.bank_account_id
and        c.check_id=p.check_id
and        v.invoice_id=p.invoice_id
and        v.invoice_date between '1-JAN-2009' and '30-JAN-2010'
and v.vendor_id=0000

---------------------------------------------------------------------------------
Account Payables

--AP SupplierWise  Supplier Ledger Query
select a.vid,a.acc_date,a.invoice_date,a.gl_date,
        a.particula,
        a.reference2,
        a.supplie,
        a.REFERENCE4,
        a.entered_dr,
        a.entered_cr, 
        a.des,
        'Jul 2006',
        'AP' MODULE,
        'June 2006' acct_year,
        a.vno,
        a.new_vno
from(
(select  distinct
        pv.vendor_id vid,
        xah.accounting_date acc_date,
        aia.invoice_date,
        aia.gl_date,
        xah.JE_CATEGORY_NAME ||' '|| xal.ACCOUNTING_CLASS_CODE particula,
        aia.doc_sequence_value reference2,
        pv.vendor_name supplie,
        ' - '   REFERENCE4,
        xal.entered_dr entered_dr,
        xal.entered_cr entered_cr, 
        xal.description des,
        'Jul 2006',
        'AP' MODULE,
        'June 2006' acct_year,
        aia.doc_sequence_value vno,
        SUBSTR(aia.invoice_type_lookup_code,1,3)||'-'||aia.doc_sequence_value new_vno
from    ap_invoices_all aia
        ,po_vendors pv
        ,xla.xla_transaction_entities xte   
        ,xla_ae_headers xah
        ,xla_ae_lines xal
where   aia.VENDOR_ID           =   pv.VENDOR_ID
and     xte.SOURCE_ID_INT_1     =   aia.INVOICE_ID
and     xte.ENTITY_ID           =   xah.ENTITY_ID             
and     xte.APPLICATION_ID      =   xah.APPLICATION_ID    
and     xal.AE_HEADER_ID        =   xah.AE_HEADER_ID
and     xal.ACCOUNTING_CLASS_CODE   not in ('ITEM EXPENSE')
AND     xal.ACCOUNTING_CLASS_CODE   NOT IN ('CHARGE' , 'AWT','RECOVERABLE TAX','IPV','NONRECOVERABLE TAX','RTAX','NRTAX')
and     xah.BALANCE_TYPE_CODE       =   'A'
and     xah.JE_CATEGORY_NAME        not in ('Reconciled Payments')
AND     pv.vendor_name=NVL(&Sup_name,pv.vendor_name)
and     pv.segment1 between nvl(&sup_no_from,pv.segment1) and nvl(&sup_no_to,pv.segment1)
and     xah.accounting_date between NVL(&date_from,xah.accounting_date) and NVL(&date_to,xah.accounting_date)
and     aia.set_of_books_id=1003
and     aia.org_id=302
UNION ALL
select  distinct
        pv.vendor_id vid,
        xah.accounting_date acc_date,
        aia.invoice_date,
        aia.gl_date,
        xah.JE_CATEGORY_NAME ||' '|| xal.ACCOUNTING_CLASS_CODE particula,
        aca.doc_sequence_value reference2,
        pv.vendor_name supplie,
        to_char(aca.CHECK_NUMBER)  REFERENCE4,
        xal.entered_dr entered_dr,
        xal.entered_cr entered_cr, 
        xal.description des,
        'Jul 2006',
        'AP' MODULE,
        'June 2006' acct_year,
        aia.doc_sequence_value vno,
        SUBSTR(aia.invoice_type_lookup_code,1,3)||'-'||aia.doc_sequence_value new_vno
from    xla_ae_lines xal,
        xla_ae_headers xah,
        ap_checks_all aca,
        ap_invoice_payments_all aipa   
        ,xla.xla_transaction_entities xte
        ,ap_invoices_all aia
        ,po_vendors pv     
where   xte.application_id      = 200
and     xal.application_id      = xah.application_id
and     xte.application_id      = xah.application_id
and     xal.ae_header_id        = xah.ae_header_id
and     xte.entity_code         = 'AP_PAYMENTS'
and     xte.source_id_int_1     = aipa.CHECK_ID
and     xte.entity_id           = xah.entity_id
and     aia.INVOICE_ID          =   aipa.INVOICE_ID
and     aia.VENDOR_ID           =   pv.VENDOR_ID
and     aipa.CHECK_ID           =   aca.CHECK_ID
--and     aia.DOC_SEQUENCE_VALUE  =40173
and     xal.ACCOUNTING_CLASS_CODE   not in ('ITEM EXPENSE')
AND     xal.ACCOUNTING_CLASS_CODE   NOT IN ('CHARGE' , 'AWT','RECOVERABLE TAX','IPV','NONRECOVERABLE TAX','CASH_CLEARING')
and     xah.JE_CATEGORY_NAME        not in ('Reconciled Payments')
and     xah.BALANCE_TYPE_CODE       =   'A'
AND     pv.vendor_name=NVL(&Sup_name,pv.vendor_name)
and     pv.segment1 between nvl(&sup_no_from,pv.segment1) and nvl(&sup_no_to,pv.segment1)
and     xah.accounting_date between NVL(&date_from,xah.accounting_date) and NVL(&date_to,xah.accounting_date))) a
--ORDER BY xah.ACCOUNTING_DATE

------


--q1 this query provides the vendor id to the above query.

select distinct a.vid,a.vendor_id vendor_id,a.vendor_name from
(select  distinct
        pv.vendor_id vid,
        pv.VENDOR_ID vendor_id,
        pv.VENDOR_NAME
from    ap_invoices_all aia
        ,po_vendors pv
        ,xla.xla_transaction_entities xte   
        ,xla_ae_headers xah
where   aia.VENDOR_ID           =   pv.VENDOR_ID
and     xte.SOURCE_ID_INT_1     =   aia.INVOICE_ID
and     xte.ENTITY_ID           =   xah.ENTITY_ID             
and     xte.APPLICATION_ID      =   xah.APPLICATION_ID
AND     pv.vendor_name=NVL(&Sup_name,pv.vendor_name)
and     pv.segment1 between nvl(&sup_no_from,pv.segment1) and nvl(&sup_no_to,pv.segment1)
and     xah.accounting_date between NVL(&date_from,xah.accounting_date) and NVL(&date_to,xah.accounting_date)
and     aia.set_of_books_id=1003
and     aia.org_id=302
--order by pv.vendor_name
union all
select  distinct
        pv.vendor_id vid,
        pv.VENDOR_ID vendor_id,
        pv.VENDOR_NAME
from    xla_ae_lines xal,
        xla_ae_headers xah,
        ap_checks_all aca,
        ap_invoice_payments_all aipa   
        ,xla.xla_transaction_entities xte
        ,ap_invoices_all aia
        ,po_vendors pv     
where   xte.application_id      = 200
and     xal.application_id      = xah.application_id
and     xte.application_id      = xah.application_id
and     xal.ae_header_id        = xah.ae_header_id
and     xte.entity_code         = 'AP_PAYMENTS'
and     xte.source_id_int_1     = aipa.CHECK_ID
and     xte.entity_id           = xah.entity_id
and     aia.INVOICE_ID          =   aipa.INVOICE_ID
and     aia.VENDOR_ID           =   pv.VENDOR_ID
and     aipa.CHECK_ID           =   aca.CHECK_ID
and     xal.ACCOUNTING_CLASS_CODE   not in ('ITEM EXPENSE')
AND     xal.ACCOUNTING_CLASS_CODE   NOT IN ('CHARGE' , 'AWT','RECOVERABLE TAX','IPV','NONRECOVERABLE TAX','CASH_CLEARING')
and     xah.JE_CATEGORY_NAME        not in ('Reconciled Payments')
and     xah.BALANCE_TYPE_CODE       =   'A'
AND     pv.vendor_name=NVL(&Sup_name,pv.vendor_name)
and     pv.segment1 between nvl(&sup_no_from,pv.segment1) and nvl(&sup_no_to,pv.segment1)
and     xah.accounting_date between NVL(&date_from,xah.accounting_date) and NVL(&date_to,xah.accounting_date))a
order by a.vendor_name
--Supplier Payables
select  pv.VENDOR_NAME,
        pvsa.VENDOR_SITE_CODE,
        pv.SEGMENT1 as vendor_number,
        aia.INVOICE_TYPE_LOOKUP_CODE invoice_type,
        aia.INVOICE_DATE,
        aia.INVOICE_NUM,
        aia.INVOICE_CURRENCY_CODE,
        aia.INVOICE_AMOUNT,
        nvl(aia.AMOUNT_PAID,0)+nvl(ap_invoices_pkg.get_amount_withheld (aia.invoice_id),0) paid_amount,
        nvl(aia.INVOICE_AMOUNT,0)-(nvl(aia.AMOUNT_PAID,0)+nvl(ap_invoices_pkg.get_amount_withheld (aia.invoice_id),0)) balance,
        ap_prepay_utils_pkg.get_prepaid_amount(aia.invoice_id) prepaid_amount,
        ap_invoices_pkg.get_amount_withheld (aia.invoice_id) amount_withheld,
        aia.GL_DATE,
        aia.DESCRIPTION,
        aia.DOC_SEQUENCE_VALUE vno,
        wtc_mb_invoiceid_to_po_chq(aia.invoice_id,'po') purchasees,
        wtc_mb_invoiceid_to_po_chq(aia.invoice_id,'payments') Cheques
from    ap_invoices_all aia
        ,po_vendors pv   
        ,po_vendor_sites_all pvsa
where   aia.vendor_id   =   pv.VENDOR_ID
and     pvsa.VENDOR_ID  =   pv.VENDOR_ID
and     aia.VENDOR_SITE_ID  =   pvsa.VENDOR_SITE_ID
and     pvsa.ORG_ID     =   aia.ORG_ID
and     aia.gl_date between &From_Date and &To_date
and     to_number(pv.SEGMENT1) between nvl(&Vendor_From,pv.SEGMENT1) and nvl(&Vendor_To,pv.SEGMENT1)
and     aia.INVOICE_TYPE_LOOKUP_CODE    in  ('STANDARD','DEBIT MEMO','CREDIT')
and     ap_invoices_pkg.get_posting_status (aia.invoice_id) = 'Y'
and     ap_invoices_pkg.get_approval_status
                     (aia.invoice_id,
                      aia.invoice_amount,
                      aia.payment_status_flag,
                      aia.invoice_type_lookup_code
                     ) <> 'CANCELLED'
order by aia.INVOICE_TYPE_LOOKUP_CODE,aia.gl_date,aia.invoice_date

--Supplier Advance
select  * from
(
(
select distinct
pv.VENDOR_NAME,
pvsa.VENDOR_SITE_CODE,
pv.SEGMENT1 vendor_number,
aia.INVOICE_TYPE_LOOKUP_CODE invoice_type,
aia.INVOICE_DATE,
aia.INVOICE_NUM,
aia.DOC_SEQUENCE_VALUE vno,
aca.CHECK_NUMBER,
aia.INVOICE_CURRENCY_CODE,
aia.INVOICE_AMOUNT,
ap_prepay_utils_pkg.get_prepaid_amount(aia.invoice_id)prepaid_amount,
aia.GL_DATE,
aca.EXCHANGE_DATE payment_rate_date,
aca.EXCHANGE_RATE,
aca.EXCHANGE_RATE_TYPE,
aia.DESCRIPTION
from
ap_invoices_all aia,
po_vendors pv,
po_vendor_sites_all pvsa,
ap_lookup_codes alc1,
ap_checks_all aca,
ap_invoice_payments_all aipa
where pv.VENDOR_ID=aia.VENDOR_ID
and pvsa.VENDOR_ID=pv.VENDOR_ID
and aca.CHECK_ID(+)=aipa.CHECK_ID
and aipa.INVOICE_ID(+)=aia.INVOICE_ID
and aia.VENDOR_SITE_ID=pvsa.VENDOR_SITE_ID
and ap_invoices_pkg.get_posting_status (aia.invoice_id)='Y'
and aia.GL_DATE between &From_date and &To_date
and to_number(pv.SEGMENT1) between nvl(&Vendor_From,pv.SEGMENT1) and nvl(&Vendor_To,pv.SEGMENT1)
and ap_invoices_pkg.get_approval_status
                     (aia.invoice_id,
                      aia.invoice_amount,
                      aia.payment_status_flag,
                      aia.invoice_type_lookup_code
                     )<>'CANCELLED'
and aia.INVOICE_TYPE_LOOKUP_CODE in ('PREPAYMENT')
and aia.payment_status_flag='Y'
and aca.STATUS_LOOKUP_CODE(+) <> 'VOIDED'
)
union all
(
select distinct
pv.VENDOR_NAME,
pvsa.VENDOR_SITE_CODE,
pv.SEGMENT1 vendor_number,
aia.INVOICE_TYPE_LOOKUP_CODE invoice_type,
aia.INVOICE_DATE,
aia.INVOICE_NUM,
aia.DOC_SEQUENCE_VALUE vno,
aca.CHECK_NUMBER,
aia.INVOICE_CURRENCY_CODE,
aia.INVOICE_AMOUNT,
ap_prepay_utils_pkg.get_prepaid_amount(aia.invoice_id)prepaid_amount,
aia.GL_DATE,
aca.EXCHANGE_DATE payment_rate_date,
aca.EXCHANGE_RATE,
aca.EXCHANGE_RATE_TYPE,
aia.DESCRIPTION
from
ap_invoices_all aia,
po_vendors pv,
po_vendor_sites_all pvsa,
ap_checks_all aca,
ap_invoice_payments_all aipa
where aia.VENDOR_ID=pv.VENDOR_ID
and pvsa.VENDOR_ID=pv.VENDOR_ID
and aipa.CHECK_ID=aca.CHECK_ID(+)
and aipa.INVOICE_ID(+)=aia.INVOICE_ID
and aia.VENDOR_SITE_ID=pvsa.VENDOR_SITE_ID
and ap_invoices_pkg.get_posting_status (aia.invoice_id)='Y'
and aia.GL_DATE between &From_date and &To_date
and to_number(pv.SEGMENT1) between nvl(&Vendor_From,pv.SEGMENT1) and nvl(&Vendor_To,pv.SEGMENT1)
and ap_invoices_pkg.get_approval_status
                     (aia.invoice_id,
                      aia.invoice_amount,
                      aia.payment_status_flag,
                      aia.invoice_type_lookup_code
                     )<>'CANCELLED'
and aia.INVOICE_TYPE_LOOKUP_CODE in ('STANDARD')
and aia.payment_status_flag in ('Y','P')
and aca.STATUS_LOOKUP_CODE(+) <> 'VOIDED'
and ap_prepay_utils_pkg.get_prepayments_applied_flag (aia.invoice_id) ='Y'
and aia.ORG_ID=302
and aia.SET_OF_BOOKS_ID=1003
and nvl(aipa.REVERSAL_FLAG,'null')<>'Y'
)
)a
order by a.invoice_type,a.gl_date,a.invoice_date




--Payable Journal Voucher
select 
 ab.batch_name,
     -- xAL.AE_LINE_NUM,
     -- xal.reference3,
       API.INVOICE_ID,
       xal.description des,
       api.created_by,
       api.invoice_currency_code,
      -- xal.reference2 ,
       xah.period_name,
       substr(api.invoice_type_lookup_code,1,3)||' - '||api.doc_sequence_value jv_no,
       substr(api.invoice_type_lookup_code,1,3)||' - '||api.doc_sequence_value||'  -'  jv,
       ' '||api.invoice_num inv_num,
       api.invoice_date,
       api.doc_sequence_value,
       xal.ae_line_num,
       round(xal.entered_dr,0) entered_dr,
       round(xal.entered_cr,0) entered_cr,
      'AP' MODULE,
       initcap(asupp.vendor_name) supplier,
       'Jun-20'||substr(xah.period_name,-2,2) acct_year,
       xal.description,
       xah.accounting_date,
       cc.segment1 Company,
       cc.segment2 Busincess,
       cc.segment3 Cost_center,
       cc.segment4 Departement,
       cc.segment5 Natural_acc,
       cc.segment6 Product,
       cc.segment7 Customer,
       cc.segment8 Spare1,
       cc.segment9 Spare2,
      -- W_DEV_PKG.GET_COMPANY_DESC(CC.CODE_COMBINATION_ID)||'-'||W_DEV_PKG.GET_BUSINESS_DESC(CC.CODE_COMBINATION_ID)||'-'|
      ' '|| W_DEV_PKG.GET_COST_CENTER_DESC(CC.CODE_COMBINATION_ID)||'-'||
       W_DEV_PKG.GET_NATURAL_DESC(CC.CODE_COMBINATION_ID)||'-'||W_DEV_PKG.GET_Product_DESC(CC.CODE_COMBINATION_ID)||'-'||W_DEV_PKG.GET_CUSTOMER_DESC(CC.CODE_COMBINATION_ID)
       fLEXdESCRIPTION
from
       --(select aid.accounting_event_id,aid.invoice_id from ap_invoice_distributions_all aid  group by aid.accounting_event_id,aid.invoice_id ) p,
    ap_batches_all ab,
    ap_invoices_all api,
--    ap_invoice_distributions_all aida,
xla.xla_transaction_entities xte
   -- ap_ae_headers_all aeh,
  --  ap_ae_lines_all ael,
    ,xla_ae_headers xah ,
    xla_ae_lines xal ,
    gl_code_combinations cc ,
    ap_suppliers asupp
   
where xte.SOURCE_ID_INT_1= api.invoice_id
AND xah.AE_HEADER_ID = xal.AE_HEADER_ID
AND xte.ENTITY_ID=   xah.ENTITY_ID
and api.set_of_books_id=1003
and api.org_id=302
and xah.JE_CATEGORY_NAME = 'Purchase Invoices'
and api.batch_id=ab.batch_id
and cc.code_combination_id=xal.CODE_COMBINATION_ID   
and TRUNC(xah.accounting_date) between NVL(&p_from_date,xah.accounting_date) and NVL(&p_to_date,xah.accounting_date)
and asupp.vendor_id = api.vendor_id
and asupp.vendor_name = NVL(&Sup_name,asupp.vendor_name)
and ab.batch_name=NVL(&p_batch,ab.batch_name)
and  ( nvl(api.doc_sequence_value,-9999) between nvl(&from_voucher,-9999) and nvl(&To_voucher,-9999)
       or api.doc_sequence_value between nvl(&from_voucher,api.doc_sequence_value) and nvl(&To_voucher,api.doc_sequence_value)
     )
--and  api.invoice_num =nvl(&invoice_number,api.invoice_num)
--and xah.period_name=nvl(&accounting_period,xah.period_name)
--and api.INVOICE_TYPE_LOOKUP_CODE=nvl(&invoice_type,api.INVOICE_TYPE_LOOKUP_CODE)
--and api.created_by=nvl(&created_by,api.created_by)
AND xah.BALANCE_TYPE_CODE = 'A'

--Invoice Distribution  Journal Entries

select  distinct
                ab.batch_name,
                -- AEL.AE_LINE_NUMBER
AEL.ae_LINE_NUM,
                 AEH.ACCOUNTING_DATE,
--                 substr(api.invoice_type_lookup_code,1,3)||' - '||api.doc_sequence_value jv_no,
                 substr(api.invoice_type_lookup_code,1,3)||' - '||api.invoice_num jv_no,
                 cc.segment3||'.'||cc.segment4||'.'||cc.segment5  AC_NO,
                  W_DEV_PKG.GET_COST_CENTER_DESC(CC.CODE_COMBINATION_ID)||'-'||
                       W_DEV_PKG.GET_NATURAL_DESC(CC.CODE_COMBINATION_ID)||'-'||
                       W_DEV_PKG.GET_Product_DESC(CC.CODE_COMBINATION_ID)  AC_Head,
                 ael.description Particular, 
                 round(ael.entered_dr,0) entered_dr,
                 round(ael.entered_cr,0) entered_cr, 
                 u.user_name


from
    ap_batches_all ab,
    ap_invoices_all api,
    ap_invoice_distributions_all aid,
    xla_ae_headers aeh,
    xla_ae_lines ael,
    --ap_ae_headers_all aeh,
    --ap_ae_lines_all ael,
    gl_code_combinations cc,
    fnd_user u


where api.invoice_id=aid.invoice_id
--and   aid.accounting_event_id=aeh.accounting_event_id
--and   aid.accounting_event_id=aeh.accounting_event_id
and   aid.accounting_event_id=aeh.event_id
and api.set_of_books_id=1003
and api.org_id=302
and   aeh.ae_header_id=ael.ae_header_id
and api.batch_id=ab.batch_id
and cc.code_combination_id=ael.code_combination_id    
and u.user_id=api.created_by
--and API.GL_DATE ='07-JUN-06'
--and aeh.accounting_date='07-JUN-06'


and aeh.accounting_date between NVL(&from_date,aeh.accounting_date) and NVL(&To_date,aeh.accounting_date)
--and api.created_by=nvl(:created_by1,api.created_by)
and u.user_name =nvl(&uname,u.user_name)
and api.INVOICE_TYPE_LOOKUP_CODE=nvl(&invoice_type,api.INVOICE_TYPE_LOOKUP_CODE)
--and aeh.ae_category='Purchase Invoices'
and aeh.JE_CATEGORY_NAME='Purchase Invoices'

--Payables Banks Payment Voucher

select  distinct
        nvl(aps.amount_remaining,0) remaining,
       initcap(apc.status_lookup_code) p_status,
        apc.doc_sequence_value,
        ai.validated_tax_amount,
      'Jun-20'||substr(aip.period_name,-2,2) acct_year,
      ai.invoice_id invoice_id,
       apc.checkrun_name,
       ' '||ai.invoice_num  invoice_num,
       round(ai.invoice_amount,0) inv_amnt,
      aip.period_name,
      aip.accounting_date,
      aip.creation_date,
      aip.last_update_date,
      'AP' Module,
      initcap(brnch.BANK_NAME) BANK_NAME,
      bnk.bank_account_id,
      bnk.BANK_BRANCH_ID,
      bnk.bank_account_NAME bnk_ac_num,
      apc.created_by,
      apc.CHECK_NUMBER,
      to_date(apc.CHECK_DATE,'dd-Mon-RRRR') check_date,
      substr(apc.VENDOR_NAME,1,45)||'-'||initcap(apc.vendor_site_code) vendor_name,
              (CASE when apc.status_lookup_code='VOIDED' and aip.reversal_inv_pmt_id is not null then (-1)*round(apc.amount,0) else  round(apc.amount,0) end ) chk_amnt,
     apc.CURRENCY_CODE  curr_code               
from
       ap_checks_all apc,
       ap_batches_all ab,
       ap_invoice_payments_all aip,
       po_vendors pov,
    --  ap_bank_accounts_all aba,
    --  ap_bank_branches     abb,
        CEFV_BANK_BRANCHES BRNCH,
        CE_BANK_ACCOUNTS BNK,
        CE_BANK_ACCT_USES_ALL SITE,
        ap_invoices_all ai,
        ap_payment_schedules_all aps

where    aip.check_id=apc.check_id
--and ai.set_of_books_id=aip.set_of_books_id
--and aip.set_of_books_id=aba.set_of_books_id
--and     aba.BANK_BRANCH_ID = abb.BANK_BRANCH_ID
--AND     APC.BANK_ACCOUNT_ID=ABA.BANK_ACCOUNT_ID
--AND     APC.CE_BANK_ACCT_USE_ID=ABA.BANK_ACCOUNT_ID
and brnch.BANK_BRANCH_ID=bnk.BANK_BRANCH_ID
and bnk.BANK_ACCOUNT_ID=site.BANK_ACCOUNT_ID
and apc.CE_BANK_ACCT_USE_ID=site.BANK_ACCT_USE_ID
AND      apc.vendor_id=pov.vendor_id
and     aip.invoice_id=ai.invoice_id
and     ai.batch_id=ab.batch_id
and     AIP.POSTED_FLAG='Y'
and aip.set_of_books_id=1003
and aip.org_id=302
and     ai.invoice_id = aps.invoice_id
and aip.accounting_date between NVL(&from_date,aip.accounting_date) and NVL(&To_date,aip.accounting_date)
and apc.vendor_name=NVL(&Sup_name,apc.vendor_name)
and ab.batch_name=NVL(&p_batch,ab.batch_name)
and  ( nvl(apc.doc_sequence_value,-9999) between nvl(&from_voucher,-9999) and nvl(&To_voucher,-9999)
             or apc.doc_sequence_value between nvl(&from_voucher,apc.doc_sequence_value) and nvl(&To_voucher,apc.doc_sequence_value)
     )

--and  NVL(apc.payment_method_lookup_code,'~')=   NVL(nvl(&payment_method,apc.payment_method_lookup_code),'~')
and aip.period_name=nvl(&accounting_period,aip.period_name)
--and bnk.bank_account_name=nvl(&bank_account,bnk.BANK_ACCOUNT_NAME)
and  apc.created_by=nvl(&created_by,apc.created_by)-- 64390

--Expenses
---q1
select t.description,t.week_end_date,

        substr(T.description,instr(T.description,'-')+1) empname,
        substr(t.description,1,instr(T.DESCRIPTION,'-')-1) empno,
        (select distinct pxa.segment2
                                    from
                                           PAY_EXTERNAL_ACCOUNTS PXA,
                                           PAY_PERSONAL_PAYMENT_METHODS_F PPPM,
                                           PAY_ORG_PAYMENT_METHODS_F OPM,
                                           per_all_assignments_f f
                                    where
                                   
                                           pppm.assignment_id=f.assignment_id
                                           and  pppm.external_account_id=pxa.external_account_id(+)
                                           and opm.ORG_PAYMENT_METHOD_ID(+) = pppm.ORG_PAYMENT_METHOD_ID
                                           and f.assignment_number=rtrim(substr (T.description,1,instr(T.description,'-')-1))
                                           and pxa.segment1 is not null
                                           and pppm.effective_end_date='31-dec-4712'
                                           ) Accounts,
        l.item_description,
        GCC.SEGMENT4 DEPTNO,     
        W_DEV_PKG.GET_NATURAL_DESC(L.CODE_COMBINATION_ID) DEPT_DESC,
        t.invoice_num,t.total Paid_amnt,
        (select apc.check_number from ap_checks_all apc,
                     
                      ap_invoice_payments_v  v
                 where apc.check_id=v.CHECK_ID
                 and v.INVOICE_NUM=t.invoice_num
                 and v.SET_OF_BOOKS_ID=1003
                 and apc.status_lookup_code<>'VOIDED'
                 and apc.vendor_name='Claims, Expense') check_no


from

     ap_expense_report_headers_all t,
     ap_expense_report_lines_all   l,
     GL_CODE_COMBINATIONS GCC,
ap_exp_report_dists_all a    
where
          t.week_end_date between nvl(:From_Date,t.week_end_date)  and nvl(:To_Date,t.week_end_date)     
--t.week_end_date between '1-apr-07' and '19-apr-07'
and t.report_header_id=l.report_header_id
AND a.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
and a.REPORT_LINE_ID=l.REPORT_LINE_ID

-----and TRIM(substr(t.description,1,instr(T.DESCRIPTION,'-')-1)) = TRIM(nvl(:emp_no,substr(t.description,1,instr(T.DESCRIPTION,'-')-1)))
---added nvl to return data in case of null
and nvl(TRIM(substr(t.description,1,instr(T.DESCRIPTION,'-')-1)),'~') = TRIM(nvl(:emp_no,nvl(TRIM(substr(t.description,1,instr(T.DESCRIPTION,'-')-1)),'~')))
and gcc.segment4=nvl(:dept,gcc.segment4)

and l.amount <> 0




--Q2

select         accounts,

              sum(paid_amnt)

from


     (

                                        select
                                                (select distinct substr( pxa.segment2,1,4)
                                                                            from
                                                                                   PAY_EXTERNAL_ACCOUNTS PXA,
                                                                                   PAY_PERSONAL_PAYMENT_METHODS_F PPPM,
                                                                                   PAY_ORG_PAYMENT_METHODS_F OPM,
                                                                                   per_all_assignments_f f
                                                                            where
                                                                           
                                                                                   pppm.assignment_id=f.assignment_id
                                                                                   and  pppm.external_account_id=pxa.external_account_id(+)
                                                                                   and opm.ORG_PAYMENT_METHOD_ID(+) = pppm.ORG_PAYMENT_METHOD_ID
                                                                                   and f.assignment_number=rtrim(substr (T.description,1,instr(T.description,'-')-1))
                                                                                   and pxa.segment1 is not null
                                                                                   and pppm.effective_end_date='31-dec-4712'
                                                                                   ) Accounts,
                                                                                   t.total Paid_amnt,l.item_description exp_desc,
                                                                                   W_DEV_PKG.GET_NATURAL_DESC(L.CODE_COMBINATION_ID) DEPT_DESC
                                       
                                        from
                                       
                                             ap_expense_report_headers_all t,
                                             ap_expense_report_lines_all   l,
                                             GL_CODE_COMBINATIONS GCC,
                                           ap_exp_report_dists_all a
                                            
                                        where
                                                 t.week_end_date between nvl(:From_Date,t.week_end_date)  and nvl(:To_Date,t.week_end_date)     
                                       

                                       and t.report_header_id=l.report_header_id
                                        AND a.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
                                        and a.REPORT_LINE_ID=l.REPORT_LINE_ID
                                        --t.week_end_date between '1-apr-07' and '19-apr-07'
                                        --and t.report_header_id=l.report_header_id
                                       -- AND L.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
                                       
                                        ----and TRIM(substr(t.description,1,instr(T.DESCRIPTION,'-')-1)) = TRIM(nvl(:emp_no,substr(t.description,1,instr(T.DESCRIPTION,'-')-1)))
                                        ---added nvl to return data in case of null
                                        and nvl(TRIM(substr(t.description,1,instr(T.DESCRIPTION,'-')-1)),'~') = TRIM(nvl(:emp_no,nvl(TRIM(substr(t.description,1,instr(T.DESCRIPTION,'-')-1)),'~')))
                                        and gcc.segment4=nvl(:dept,gcc.segment4)
                                       
                                        and l.amount <> 0

        )
       
            group by accounts


--Q3

select      

             exp_desc,

              sum(paid_amnt) exp_paid_amnt

from


     (

                                        select
                                                (select distinct substr( pxa.segment2,1,4)
                                                                            from
                                                                                   PAY_EXTERNAL_ACCOUNTS PXA,
                                                                                   PAY_PERSONAL_PAYMENT_METHODS_F PPPM,
                                                                                   PAY_ORG_PAYMENT_METHODS_F OPM,
                                                                                   per_all_assignments_f f
                                                                            where
                                                                           
                                                                                   pppm.assignment_id=f.assignment_id
                                                                                   and  pppm.external_account_id=pxa.external_account_id(+)
                                                                                   and opm.ORG_PAYMENT_METHOD_ID(+) = pppm.ORG_PAYMENT_METHOD_ID
                                                                                   and f.assignment_number=rtrim(substr (T.description,1,instr(T.description,'-')-1))
                                                                                   and pxa.segment1 is not null
                                                                                   and pppm.effective_end_date='31-dec-4712'
                                                                                   ) Accounts,
                                                                                   t.total Paid_amnt,l.item_description exp_desc,
                                                                                   W_DEV_PKG.GET_NATURAL_DESC(L.CODE_COMBINATION_ID) DEPT_DESC
                                       
                                        from
                                       
                                             ap_expense_report_headers_all t,
                                             ap_expense_report_lines_all   l,
                                             GL_CODE_COMBINATIONS GCC,
                                               ap_exp_report_dists_all a
                                            
                                        where
                                                 t.week_end_date between nvl(:From_Date,t.week_end_date)  and nvl(:To_Date,t.week_end_date)     
                                      
                                        --t.week_end_date between '1-apr-07' and '19-apr-07'
                                       and t.report_header_id=l.report_header_id
                                        AND a.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
                                        and a.REPORT_LINE_ID=l.REPORT_LINE_ID                                      
 --and t.report_header_id=l.report_header_id
                                      -- AND L.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
                                       
                                        ----and TRIM(substr(t.description,1,instr(T.DESCRIPTION,'-')-1)) = TRIM(nvl(:emp_no,substr(t.description,1,instr(T.DESCRIPTION,'-')-1)))
                                        ---added nvl to return data in case of null
                                        and nvl(TRIM(substr(t.description,1,instr(T.DESCRIPTION,'-')-1)),'~') = TRIM(nvl(:emp_no,nvl(TRIM(substr(t.description,1,instr(T.DESCRIPTION,'-')-1)),'~')))
                                        and gcc.segment4=nvl(:dept,gcc.segment4)
                                       
                                        and l.amount <> 0

        )
        
            group by exp_desc





--Payables Batch Payment Summary

select  distinct
        apc.check_date,
       
        aip.accounting_date,
        apc.doc_sequence_value,
        apc.checkrun_name payment_batch,
        pov.vendor_name,
        pov.vendor_name_alt,
        apc.vendor_site_code,
        --ai.invoice_id,
        pov.num_1099 NTN_No,
        pov.standard_industry_class NIC_No,
       apc.amount,
        pov.vat_registration_num,
        ai.validated_tax_amount,
      'Jun-20'||substr(aip.period_name,-2,2) acct_year,
--      ai.invoice_id invoice_id,
       ab.batch_name,
       apc.checkrun_name payment_batch,
  --     ' '||ai.invoice_num  invoice_num,
    --   round(ai.invoice_amount,0) inv_amnt,
      aip.period_name,
      aip.accounting_date,
      'AP' Module,
      initcap(brnch.BANK_NAME) BANK_NAME,
      bnk.bank_account_id,
      bnk.BANK_BRANCH_ID,
      bnk.bank_account_NAME bnk_ac_num,
      vendor_name_alt,
      apc.created_by,
      apc.CHECK_NUMBER,
       ( select chk.name
          from ap_check_stocks_all chk
         where chk.check_stock_id=apc.check_stock_id) Document_Name,

      to_date(apc.CHECK_DATE,'dd-Mon-RRRR') check_date,
      substr(apc.VENDOR_NAME,1,45)||'-'||initcap(apc.vendor_site_code) vendor_name,
       --       (CASE when apc.status_lookup_code='VOIDED' and aip.reversal_inv_pmt_id is not null then (-1)*round(apc.amount,0) else  round(apc.amount,0) end ) chk_amnt,
     apc.CURRENCY_CODE  curr_code                

from
       ap_checks_all apc,
       ap_batches_all ab,
       ap_invoice_payments_all aip,
       po_vendors pov,
     -- ap_bank_accounts_all aba,
     -- ap_bank_branches     abb,
     CEFV_BANK_BRANCHES BRNCH,
CE_BANK_ACCOUNTS BNK,
 CE_BANK_ACCT_USES_ALL SITE,
      ap_invoices_all ai,
       ap_payment_schedules_all aps

     -- (select a.invoice_id,max(a.accounting_event_id) aeid from ap_invoice_payments_all a group by a.invoice_id ) ae
where      aip.check_id=apc.check_id
--and ai.set_of_books_id=aip.set_of_books_id
and brnch.BANK_BRANCH_ID=bnk.BANK_BRANCH_ID
and bnk.BANK_ACCOUNT_ID=site.BANK_ACCOUNT_ID
and apc.CE_BANK_ACCT_USE_ID=site.BANK_ACCT_USE_ID
--and aip.set_of_books_id=aba.set_of_books_id
--and     aba.BANK_BRANCH_ID = abb.BANK_BRANCH_ID
--AND     APC.BANK_ACCOUNT_ID=ABA.BANK_ACCOUNT_ID
--and        apc.BANK_ACCOUNT_Name = aba.BANK_ACCOUNT_Name
AND        apc.vendor_id=pov.vendor_id
and     aip.invoice_id=ai.invoice_id
and     ai.batch_id=ab.batch_id
and     AIP.POSTED_FLAG='Y'
and aip.set_of_books_id=1003
and aip.org_id=302

and     ai.invoice_id = aps.invoice_id

and aip.accounting_date between NVL(&from_date,aip.accounting_date) and NVL(&To_date,aip.accounting_date)
and bnk.bank_account_name=nvl(&bank_account,bnk.bank_account_name)
and apc.check_number between nvl(&Doc_number_from,apc.check_number) and nvl(&Doc_number_to,apc.check_number)
and  ( nvl(apc.checkrun_name,-9999) = nvl(&p_batch,-9999)
            or apc.checkrun_name= nvl(&p_batch,apc.checkrun_name)
     )
AND apc.status_lookup_code<>'VOIDED'
ORDER BY  TO_NUMBER(apc.CHECK_NUMBER)



--Payable Batch Payments

select  distinct
        nvl(aps.amount_remaining,0) remaining,
       initcap(apc.status_lookup_code) p_status,
        apc.doc_sequence_value,
        ai.validated_tax_amount,
      'Jun-20'||substr(aip.period_name,-2,2) acct_year,
      ai.invoice_id invoice_id,
        apc.checkrun_name,
       ' '||ai.invoice_num  invoice_num,
       round(ai.invoice_amount,0) inv_amnt,
      aip.period_name,
      aip.accounting_date,
      aip.creation_date,
      aip.last_update_date,
      'AP' Module,
      initcap(brnch.BANK_NAME) BANK_NAME,
      bnk.BANK_ACCOUNT_ID,
      bnk.BANK_BRANCH_ID,
      bnk.BANK_ACCOUNT_NAME bnk_ac_num,
      vendor_name_alt,
      apc.created_by,
      apc.CHECK_NUMBER,
       ( select chk.name
          from ap_check_stocks_all chk
         where chk.check_stock_id=apc.check_stock_id) Document_Name,

      to_date(apc.CHECK_DATE,'dd-Mon-RRRR') check_date,
      substr(apc.VENDOR_NAME,1,45)||'-'||initcap(apc.vendor_site_code) vendor_name,
              (CASE when apc.status_lookup_code='VOIDED' and aip.reversal_inv_pmt_id is not null then (-1)*round(apc.amount,0) else  round(apc.amount,0) end ) chk_amnt,
     apc.CURRENCY_CODE  curr_code               
from
       ap_checks_all apc,
       ap_batches_all ab,
       ap_invoice_payments_all aip,
       po_vendors pov,
     -- ap_bank_accounts_all aba,
     -- ap_bank_branches     abb,
      ap_invoices_all ai,
       ap_payment_schedules_all aps,
       CE_BANK_ACCOUNTS BNK,
         CE_BANK_ACCT_USES_ALL SITE,
         CEFV_BANK_BRANCHES BRNCH
where      aip.check_id=apc.check_id
--and ai.set_of_books_id=aip.set_of_books_id
and BNK.BANK_BRANCH_ID=brnch.BANK_BRANCH_ID
and bnk.BANK_ACCOUNT_ID=site.BANK_ACCOUNT_ID
and apc.CE_BANK_ACCT_USE_ID=site.BANK_ACCT_USE_ID
--and aip.set_of_books_id=aba.set_of_books_id
--and     aba.BANK_BRANCH_ID = abb.BANK_BRANCH_ID
--AND     APC.BANK_ACCOUNT_ID=ABA.BANK_ACCOUNT_ID
AND        apc.vendor_id=pov.vendor_id
and     aip.invoice_id=ai.invoice_id
and     ai.batch_id=ab.batch_id
and     AIP.POSTED_FLAG='Y'
and aip.set_of_books_id=1003
and aip.org_id=302
and     ai.invoice_id = aps.invoice_id
and aip.accounting_date between NVL(&from_date,aip.accounting_date) and NVL(&To_date,aip.accounting_date)
--and  ( nvl(apc.checkrun_name,-9999) = nvl(&p_batch,-9999)
    --        or apc.checkrun_name= nvl(&p_batch,apc.checkrun_name)
  --   )
--and    pov.vendor_name_alt=nvl(&sup_name,pov.vendor_name_alt)
--and bnk.bank_account_name=nvl(&bank_account,bnk.bank_account_name)
--and  apc.check_stock_id=nvl(:PDocument_Name ,apc.check_stock_id)
--and apc.check_number between nvl(&Doc_number_from,apc.check_number) and nvl(&Doc_number_to,apc.check_number)
--and  apc.created_by=nvl(&created_by,apc.created_by)
--and  ( nvl(apc.doc_sequence_value,-9999) between nvl(&from_voucher,-9999) and nvl(&To_voucher,-9999)
  ---          or apc.doc_sequence_value between nvl(&from_voucher,apc.doc_sequence_value) and nvl(&To_voucher,apc.doc_sequence_value)
     --)




No comments:

Post a Comment