Example Usage for Formula&Placeholder Column in a report

Procedure That will return 4 Variables Example

Procedure That will take vendor id from calling environment and return 4 other variables like V_invamt,V_taxamt,V_vname,V_vntn to the calling environment.

create or replace procedure PrintInvoiceInfo(V_venid in number, V_invamt out number,V_taxamt out number,V_vname out varchar2,V_vntn out varchar2)

is

begin

SELECT sum(v.invoice_amount),sum(idv.amount*awt.tax_rate/100),pov.vendor_name,pov.num_1099 into V_invamt,V_taxamt,V_vname,V_vntn

       FROM

       Ap_Invoices_All v,

       AP_INVOICE_DISTRIBUTIONS_V idv,

       AP_AWT_TAX_RATES_ALL        awt,

       Po_Vendors                  pov

       WHERE

       awt.tax_name(+)=idv.awt_group_name

       and v.invoice_id=idv.invoice_id

       and pov.vendor_id=v.vendor_id

       and v.invoice_date between '1-jul-2009' and '30-Jun-2010'

       and v.vendor_id=V_venid

       and v.invoice_type_lookup_code='STANDARD'

       group by v.vendor_id,pov.vendor_name,pov.num_1099;

end PrintInvoiceInfo;

Test window-  The Calling Environment

declare

invamt number;

taxamt number;

vname  varchar2(30);

vntn   varchar2(10);

begin

dbms_application_info.set_client_info(000);

PrintInvoiceInfo(0000,invamt,taxamt,vname,vntn);

dbms_output.put_line(invamt||taxamt||vname||vntn);

end;

How to define Cursor with report

DECLARE

    V_venid  ap_invoices_all.vendor_id%type;

    V_invid   ap_invoices_all.invoice_id%type;

    V_invamt  ap_invoices_all.invoice_amount%type;

    V_taxamt  number;

   

   

    CURSOR StdInv_cursor IS  

    SELECT v.vendor_id,sum(v.invoice_amount),sum(idv.amount*awt.tax_rate/100)

       FROM

       Ap_Invoices_All v,

       AP_INVOICE_DISTRIBUTIONS_V idv,

       AP_AWT_TAX_RATES_ALL        awt

       WHERE

       awt.tax_name(+)=idv.awt_group_name

       and v.invoice_id=idv.invoice_id

       and v.invoice_date between '1-jul-2009' and '30-Jun-2010'

       and v.vendor_id=0000

       and v.invoice_type_lookup_code='STANDARD'

       group by v.vendor_id;

   

 BEGIN

 OPEN StdInv_cursor;

          loop

          FETCH StdInv_cursor INTO V_venid,V_invamt,V_taxamt;

          exit when StdInv_cursor%NOTFound;

          dbms_output.put_line (V_venid||'    '||'      '||V_invamt||'     '||V_taxamt);

          END Loop;

   CLOSE StdInv_cursor;

 END;

EXAMPLE Excluding the Tax Invoices of Particular vendor form AP_Invoices_ALL Table using the following Query.

SELECT
v.vendor_id,
v.invoice_id,
v.invoice_amount,
idv.amount*awt.tax_rate/100 tax_amount
--c.check_id  cid,
--c.amount,  
--p.check_id pid,
--p.amount   pamt
FROM
Ap_Invoices_All             v,
AP_INVOICE_DISTRIBUTIONS_V  idv,
AP_AWT_TAX_RATES_ALL        awt
--Ap_checks_all               c,
--Ap_Invoice_Payments_All     p
WHERE
--c.check_id=p.check_id
awt.tax_name(+)=idv.awt_group_name
and v.invoice_id=idv.invoice_id
and v.invoice_date between '1-jul-2009' and '30-Jun-2010'
and v.vendor_id='1'
and v.invoice_type_lookup_code='AWT'
and to_number(trim(substr(v.invoice_num,19,6)),'999999') in(SELECT v.invoice_id FROM
Ap_Invoices_All v
WHERE
v.invoice_date between '1-jul-2009' and '30-July-2010'
and v.vendor_id='0000'
and v.invoice_type_lookup_code='STANDARD'
)

Example Using Having , and condition with Builting function For AWT

SELECT
to_number(trim(substr(v.invoice_num,19,6)),'999999'),
sum(v.invoice_amount)
FROM
Ap_Invoices_All v
WHERE
v.invoice_date between '1-jul-2009' and '30-Jun-2010'
and v.vendor_id=1
and v.invoice_type_lookup_code='AWT'
group by to_number(trim(substr(v.invoice_num,19,6)),'999999')
having to_number(trim(substr(v.invoice_num,19,6)),'999999') in ('000000','000000','000000','000000','000000')

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)

     --)