Protected by Copyscape Web Copyright Protection Software

Search This Blog

Monday, June 24, 2013

Sample Report Requirement



hi this is the Sample requirement.

Solution
----------

For this i used below query.


SELECT DISTINCT
    pv.vendor_name C_VENDOR,
    hou.name Operating_Unit,
    pv.vendor_id C_VENDOR_ID,
    pvs.vendor_site_code C_VENDOR_SITE,
    i.invoice_num C_INVOICE_NUMBER,
    i.invoice_id C_INVOICE_ID1,
    i.invoice_date C_TRANSACTION_DATE,
    i.invoice_type_lookup_code C_TRANSACTION_TYPE,
    alc.displayed_field C_TRANSACTION_TYPE_FIELD,
    i.payment_currency_code C_CURR1,
    DECODE (i.doc_sequence_value, NULL,i.voucher_num, i.doc_sequence_value) C_DOC_SEQUENCE_NUMBER,
    f2.name C_DOC_SEQUENCE_NAME,
    DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', nvl(ap_utilities_pkg.ap_round_currency(i.invoice_amount* i.payment_cross_rate , i.payment_currency_code) ,0), nvl(ap_invoices_pkg.Get_Line_Total_Incl_Prepay(i.invoice_id),
           nvl(nvl(i.pay_curr_invoice_amount, i.invoice_amount),0))) - nvl(ap_invoices_pkg.get_amount_withheld(i.invoice_id),0) C_TOTAL_INVOICE_AMT,
    ck.doc_sequence_value C_DOC_SEQUENCE_NUMBER1,
    ck.check_number C_TRANSACTION_NUMBER,
    f.name C_DOC_SEQUENCE_NAME1,
    aip.accounting_date C_TRANSACTION_DATE2,
    ael.accounting_class_code C_LINE_TYPE_CODE,
    DECODE(ael.accounting_class_code, 'CASH', 'Cash',
                                      'DISCOUNT','Discount',
                                      'GAIN','Gain',
                                      'LOSS','Loss',
                                      'CASH_CLEARING','Cash Clearing' ) C_TRANSACTION_TYPE2,
    ck.currency_code C_CURR2,
    DECODE(ael.accounting_class_code, 'CASH',-1*aip.amount,
                                      'CASH_CLEARING',-1*aip.amount,
                                      'GAIN',nvl(aip.invoice_base_amount,aip.amount)- nvl(aip.payment_base_amount,aip.amount),
                                      'LOSS',nvl(aip.invoice_base_amount,aip.amount)- nvl(aip.payment_base_amount,aip.amount),                                       'DISCOUNT',ap_utilities_pkg.ap_round_currency(-1*nvl(aip.discount_taken,0)*i.payment_cross_rate ,i.payment_currency_code)
)C_TRANSACTION_AMT ,
invoice_payment_id     C_INVOICE_PAYMENT_ID
FROM po_vendors pv,
    po_vendor_sites pvs,
    ap_invoices i,
    ap_checks ck,
    fnd_document_sequences f,
    fnd_document_sequences f2,
    ap_invoice_payments aip,
    xla_transaction_entities ATE,
    xla_events AAE ,
    xla_ae_headers AEH,
    xla_ae_lines AEL,
    ap_lookup_codes alc ,
    ap_payment_history aph,
    ap_system_parameters ASP,
    hr_operating_units hou
WHERE ASP.when_to_account_pmt = 'CLEARING ONLY'
    AND ASP.org_id = i.org_id
    AND pv.vendor_id = pvs.vendor_id
    AND i.vendor_id = pv.vendor_id
    AND i.vendor_site_id = pvs.vendor_site_id
    AND i.invoice_id = aip.invoice_id (+)
    AND i.invoice_type_lookup_code = alc.lookup_code
    AND alc.lookup_type = 'INVOICE TYPE' 
    AND aip.check_id = aph.check_id (+)
    AND aph.accounting_event_id = AEH.event_id(+)
    AND NVL( AAE.event_status_code , 'X' ) <> 'N'
    AND AEH.event_id = AAE.event_id(+)
    AND AAE.entity_id = ATE.entity_id(+)
    AND AEH.ae_header_id = AEL.ae_header_id(+)
    AND ATE.entity_code(+) = 'AP_PAYMENTS'
    AND aeh.ledger_id(+) = :P_BOOk
    AND ck.check_id (+)= aip.check_id
    AND f.doc_sequence_id (+) = ck.doc_sequence_id
    AND f2.doc_sequence_id(+)= i.doc_sequence_id
    AND DECODE(i.invoice_type_lookup_code, 'PREPAYMENT',NVL(aip.invoice_payment_type, 'X'),1)              <>  DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', 'PREPAY' , 2)
    AND  alc.displayed_field not in ('PREPAYMENT','CASH','Prepayment','Cash')
    AND hou.organization_id=i.org_id
    AND hou.set_of_books_id=i.set_of_books_id
    AND i.invoice_date<:P_AS_OF_DATE
   &LP_VENDOR_ID
   &LP_VENDOR_SITE
   &LP_INVOICES
   &LP_INVOICES_NUMBER_FROM
   &LP_INVOICES_NUMBER_TO
   &LP_DOC_SEQUENCE_NAME
   &LP_DOC_SEQUENCE_NUMBER_FROM
   &LP_DOC_SEQUENCE_NUMBER_TO
  -- &LP_INVOICE_DATE_FROM
  -- &LP_INVOICE_DATE_TO 

UNION


SELECT DISTINCT
    pv.vendor_name C_VENDOR,
    hou.name Operating_Unit,
    pv.vendor_id C_VENDOR_ID,
    pvs.vendor_site_code C_VENDOR_SITE,
    i.invoice_num C_INVOICE_NUMBER,
    i.invoice_id C_INVOICE_ID1, 
    i.invoice_date C_TRANSACTION_DATE,
    i.invoice_type_lookup_code C_TRANSACTION_TYPE,
    alc.displayed_field C_TRANSACTION_TYPE_FIELD,
    i.payment_currency_code C_CURR,
    DECODE (i.doc_sequence_value, NULL,i.voucher_num, i.doc_sequence_value) C_DOC_SEQUENCE_NUMBER,
    f2.name C_DOC_SEQUENCE_NAME,
    DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', nvl(ap_utilities_pkg.ap_round_currency(i.invoice_amount* i.payment_cross_rate , i.payment_currency_code) ,0), nvl(ap_invoices_pkg.Get_Line_Total_Incl_Prepay(i.invoice_id),
           nvl(nvl(i.pay_curr_invoice_amount, i.invoice_amount),0))) - nvl(ap_invoices_pkg.get_amount_withheld(i.invoice_id),0) C_TOTAL_INVOICE_AMT,  
    ck.doc_sequence_value C_DOC_SEQUENCE_NUMBER1,
    ck.check_number C_TRANSACTION_NUMBER,
    f.name C_DOC_SEQUENCE_NAME1,
    aip.accounting_date C_TRANSACTION_DATE2,
    ael.accounting_class_code C_LINE_TYPE_CODE,
    DECODE(ael.accounting_class_code, 'CASH', 'Cash',
                                      'DISCOUNT','Discount',
                                      'GAIN','Gain',
                                      'LOSS','Loss',
                                      'CASH_CLEARING','Cash Clearing' ) C_TRANSACTION_TYPE2,
    ck.currency_code C_CURR2,
    DECODE(ael.accounting_class_code, 'CASH',-1*aip.amount,
                                      'CASH_CLEARING',-1*aip.amount,
                                      'GAIN',nvl(aip.invoice_base_amount,aip.amount)- nvl(aip.payment_base_amount,aip.amount),
                                      'LOSS',nvl(aip.invoice_base_amount,aip.amount)- nvl(aip.payment_base_amount,aip.amount),                                       'DISCOUNT',ap_utilities_pkg.ap_round_currency(-1*nvl(aip.discount_taken,0)*i.payment_cross_rate ,i.payment_currency_code)
--Bug2909396 Rounding the DISCOUNT amount
)C_TRANSACTION_AMT ,
invoice_payment_id     C_INVOICE_PAYMENT_ID  
FROM po_vendors pv,
    po_vendor_sites pvs,
    ap_invoices i,
    ap_checks ck,
    fnd_document_sequences f,
    fnd_document_sequences f2,
    ap_invoice_payments aip,
    xla_transaction_entities ATE,
    xla_events AAE ,
    xla_ae_headers AEH,
    xla_ae_lines AEL,
    ap_lookup_codes alc,
    ap_system_parameters ASP,
    hr_operating_units hou
WHERE ASP.when_to_account_pmt <> 'CLEARING ONLY' 
    AND ASP.org_id = i.org_id  
    AND pv.vendor_id = pvs.vendor_id
    AND i.vendor_id = pv.vendor_id
    AND i.vendor_site_id = pvs.vendor_site_id
    AND i.invoice_id = aip.invoice_id (+)
    AND i.invoice_type_lookup_code = alc.lookup_code
    AND alc.lookup_type = 'INVOICE TYPE' 
    AND aip.accounting_event_id = AEH.event_id(+)
    AND AEH.event_id = AAE.event_id(+)
    AND AAE.entity_id = ATE.entity_id(+)
    AND AEH.ae_header_id = AEL.ae_header_id(+)
    AND ATE.entity_code(+) = 'PAYMENTS'
    AND aeh.ledger_id(+) = :P_BOOk
    AND ck.check_id (+)= aip.check_id
    AND f.doc_sequence_id (+) = ck.doc_sequence_id
    AND f2.doc_sequence_id(+)= i.doc_sequence_id
    AND DECODE(i.invoice_type_lookup_code, 'PREPAYMENT',NVL(aip.invoice_payment_type, 'X'),1)              <>  DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', 'PREPAY' , 2)
    AND  alc.displayed_field not in ('PREPAYMENT','CASH','Prepayment','Cash')  
   AND hou.organization_id=i.org_id
    AND hou.set_of_books_id=i.set_of_books_id
   AND i.invoice_date<:P_AS_OF_DATE
&LP_VENDOR_ID
   &LP_VENDOR_SITE
   &LP_INVOICES
   &LP_INVOICES_NUMBER_FROM
   &LP_INVOICES_NUMBER_TO
   &LP_DOC_SEQUENCE_NAME
   &LP_DOC_SEQUENCE_NUMBER_FROM
   &LP_DOC_SEQUENCE_NUMBER_TO
   --&LP_INVOICE_DATE_FROM
   --&LP_INVOICE_DATE_TO 
  
UNION   

SELECT DISTINCT
    pv.vendor_name C_VENDOR,
    hou.name Operating_Unit,
    pv.vendor_id C_VENDOR_ID,
    pvs.vendor_site_code C_VENDOR_SITE,
    i.invoice_num C_INVOICE_NUMBER,
    i.invoice_id C_INVOICE_ID1,  
    i.invoice_date C_TRANSACTION_DATE,
    i.invoice_type_lookup_code C_TRANSACTION_TYPE,
    alc.displayed_field C_TRANSACTION_TYPE_FIELD,
    i.payment_currency_code C_CURR,
    DECODE (i.doc_sequence_value, NULL,i.voucher_num, i.doc_sequence_value) C_DOC_SEQUENCE_NUMBER,  
    f2.name C_DOC_SEQUENCE_NAME,
    DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', nvl(ap_utilities_pkg.ap_round_currency(i.invoice_amount* i.payment_cross_rate , i.payment_currency_code) ,0),  nvl(ap_invoices_pkg.Get_Line_Total_Incl_Prepay(i.invoice_id),         nvl(nvl(i.pay_curr_invoice_amount, i.invoice_amount),0))) - nvl(ap_invoices_pkg.get_amount_withheld(i.invoice_id),0) C_TOTAL_INVOICE_AMT, 
    ck.doc_sequence_value C_DOC_SEQUENCE_NUMBER1,
    ck.check_number C_TRANSACTION_NUMBER,
    f.name C_DOC_SEQUENCE_NAME1,
    aip.accounting_date C_TRANSACTION_DATE2,
    ael.accounting_class_code C_LINE_TYPE_CODE,
    DECODE(ael.accounting_class_code, 'CASH', 'Cash',
                                      'DISCOUNT','Discount',
                                      'GAIN','Gain',
                                      'LOSS','Loss',
                                      'CASH_CLEARING','Cash Clearing' ) C_TRANSACTION_TYPE2,
    ck.currency_code C_CURR2,
    DECODE(ael.accounting_class_code, 'CASH',-1*aip.amount,
                                      'CASH_CLEARING',-1*aip.amount,
                                      'GAIN',nvl(aip.invoice_base_amount,aip.amount)- nvl(aip.payment_base_amount,aip.amount),
                                      'LOSS',nvl(aip.invoice_base_amount,aip.amount)- nvl(aip.payment_base_amount,aip.amount),                                       'DISCOUNT',ap_utilities_pkg.ap_round_currency(-1*nvl(aip.discount_taken,0)*i.payment_cross_rate ,i.payment_currency_code)
--Bug2909396 Rounding the DISCOUNT amount
)C_TRANSACTION_AMT ,
invoice_payment_id     C_INVOICE_PAYMENT_ID
FROM po_vendors pv,
    po_vendor_sites pvs,
    ap_invoices i,
    ap_checks ck,
    fnd_document_sequences f,
    fnd_document_sequences f2,
    ap_invoice_payments aip,
    xla_transaction_entities ATE,
    xla_events AAE ,
    xla_ae_headers AEH,
    xla_ae_lines AEL,
    ap_lookup_codes alc,
    ap_payment_history aph,
   hr_operating_units hou
WHERE pv.vendor_id = pvs.vendor_id
    AND i.vendor_id = pv.vendor_id
    AND i.vendor_site_id = pvs.vendor_site_id
    AND i.invoice_id = aip.invoice_id 
    AND i.invoice_type_lookup_code = alc.lookup_code
    AND alc.lookup_type = 'INVOICE TYPE' 
    AND aip.accounting_event_id = AEH.event_id
    AND AEH.event_id = AAE.event_id
    AND AAE.entity_id = ATE.entity_id
    AND AEH.ae_header_id = AEL.ae_header_id
    AND ATE.entity_code = 'PAYMENTS'
    AND aeh.ledger_id = :P_BOOk
    AND ck.check_id = aip.check_id
    AND f.doc_sequence_id (+) = ck.doc_sequence_id
    AND f2.doc_sequence_id(+)= i.doc_sequence_id
    AND DECODE(i.invoice_type_lookup_code, 'PREPAYMENT',NVL(aip.invoice_payment_type, 'X'),1)
    <>  DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', 'PREPAY' , 2)
    AND   aph.accounting_event_id = aeh.event_id
    AND   aip.check_id = aph.check_id
    AND   aph.transaction_type = 'PAYMENT MATURITY'
   AND  alc.displayed_field not in ('PREPAYMENT','CASH','Prepayment','Cash')
   AND hou.organization_id=i.org_id
    AND hou.set_of_books_id=i.set_of_books_id
   AND i.invoice_date<:P_AS_OF_DATE
   &LP_VENDOR_ID
   &LP_VENDOR_SITE
   &LP_INVOICES
   &LP_INVOICES_NUMBER_FROM
   &LP_INVOICES_NUMBER_TO
   &LP_DOC_SEQUENCE_NAME
   &LP_DOC_SEQUENCE_NUMBER_FROM
   &LP_DOC_SEQUENCE_NUMBER_TO
   --&LP_INVOICE_DATE_FROM
  -- &LP_INVOICE_DATE_TO 
ORDER BY C_VENDOR,
    C_VENDOR_SITE,
    C_INVOICE_NUMBER,
    C_TRANSACTION_DATE,
    C_TRANSACTION_TYPE,
    C_TRANSACTION_DATE2,
    C_LINE_TYPE_CODE,
    C_INVOICE_PAYMENT_ID  


rdf look like this



And the sample layout shown below

Invoice History Report

Operating Unit
:
OPUNIT
As of Date
:
DATE




Operating Unit
Vendor Name
Invoice Number
Sequence Name
Voucher No
Transaction Date
Activity
Inv Currency
Transaction Amt
G_OU OPERATING_UNIT
G_Ven C_VENDOR
G_inv C_INVOICE_NUMBER
C_DOC_SEQUENCE_NAME
C_DOC_SEQUENCE_NUMBER
C_TRANSACTION_DATE
C_TRANSACTION_TYPE_FIELD
C_CURR
amt end inv





C_VENDOR  Total:
0.00
end vs







OU Total:
0.00

end ou



Here i took 3 groups operating unit,vendor name and invoice number. 



Hope this will help you.