AP Consolidated Invoices

➜ Back to Library
Description
Categories: Custom Reports HDFC
AP invoices and distributions from the HDFC consolidated view, with the full set of invoice, distribution, payment, travel/expense, account, cost centre, branch, banking, TDS, WCT, ESI and GST attributes.

Migrated from the Oracle Discoverer worksheet built on HDFC.HDFC_CONSOLIDATED_VW. Invoice Line Number, Available Amount, CESS Amount, Total Tax Amount and TDS Rate are summed for each uniq ...  AP invoices and distributions from the HDFC consolidated view, with the full set of invoice, distribution, payment, travel/expense, account, cost centre, branch, banking, TDS, WCT, ESI and GST attributes.

Migrated from the Oracle Discoverer worksheet built on HDFC.HDFC_CONSOLIDATED_VW. Invoice Line Number, Available Amount, CESS Amount, Total Tax Amount and TDS Rate are summed for each unique combination of all other columns, and the Discoverer Year/Quarter/Month/Day date buckets for Invoice GL Date, Created Date, Prepaid Invoice Date, Hold Date, Release Date, GL Date and Payment Date are retained.

Enter a GL Date to report on a single accounting day (exact match).   more 
select
hcv.crs_number,
hcv.system_inputter,
hcv.system_inputter_desc,
hcv.trx_source,
hcv.inputter,
hcv.invoice_classification,
hcv.invoice_type,
hcv.receipt_date,
hcv.tat_date,
hcv.requestor_number,
hcv.requestor_name,
hcv.requestor_location,
hcv.approver_number,
hcv.approver_name,
hcv.requestor_zone,
hcv.send_back_to,
hcv.send_back_reason,
hcv.send_back_date,
hcv.pod_number,
hcv.courier_agent,
hcv.crs_total,
hcv.type,
hcv.vendor_code,
hcv.vendor,
hcv.vendor_site,
hcv.remit_to_name,
hcv.payable_location,
hcv.invoice_num,
hcv.invoice_date,
hcv.invoice_currency,
hcv.invoice_type1,
hcv."serv.taxnumber WCT number",
hcv.service_tax_number,
hcv.tax,
hcv.invoice_amount,
hcv.tax1,
hcv.amount1,
hcv.tax2,
hcv.amount2,
hcv.prepay_invoice_number1,
hcv.inward_number,
hcv.requestor,
hcv.approver,
hcv.invoice_number,
hcv.tds_amount1,
hcv.set_off_invoice,
hcv.amount_paid,
hcv.paid_on_date,
hcv.cheque_number,
hcv.payment_bank_account,
hcv.ramco_reference_name,
hcv.pv_number,
hcv.narration,
hcv."Expense_Report_Inputter",
hcv."Petty_Cash_Approver",
hcv."Employee",
hcv."Tour Code",
hcv."Ticket Jacket Number",
hcv."Basic Fare",
hcv."Travel Sector",
hcv."Period of Travel",
hcv."Apex / Non Apex",
hcv."Travel Agent Name",
hcv."Commission Amount (Agent)",
hcv."Credit Card no.",
hcv."Hotel Name",
hcv."Location Visited",
hcv."No. of Days",
hcv."Room Charges per day",
hcv.tax3,
hcv.amount3,
hcv.tax4,
hcv.amount4,
hcv.invoice_amount invoice_amount_1,
hcv.tds_amount,
hcv.wct_amount,
hcv.prepaid_amount,
hcv.net_amount,
hcv.invoice_sequence,
hcv.invoice_gl_date,
hcv.invoice_description,
hcv.created_date,
hcv.payment_group,
hcv.bank_name,
hcv.terms,
hcv.distribution_set,
hcv.prepay_invoice_number,
hcv.prepaid_invoice_date,
hcv.prepaid_invoice_amount_applied,
hcv.invoice_status,
hcv.accounted,
hcv.payment_status,
hcv.hold_name,
hcv.hold_reason,
hcv.held_by,
hcv.hold_date,
hcv.release_name,
hcv.release_reason,
hcv.release_date,
hcv.release_by,
hcv.invoice_id,
hcv.invoice_dist_id,
hcv.distribution_amount,
hcv.gl_date,
hcv.account,
hcv.account_description,
hcv.branch,
hcv.branch_description,
hcv.cost_centre,
hcv.cost_centre_description,
hcv.mis2,
hcv.mis2_description,
hcv.product,
hcv.product_description,
hcv.catergory,
hcv.catergory_description,
hcv.project,
hcv.project_description,
hcv.currency,
hcv.currency_description,
hcv.description,
hcv.expense_type,
hcv.expense_from,
hcv.expense_to,
hcv.future1,
hcv.future2,
hcv.quantity,
hcv.expense_report_remark,
hcv."context",
hcv.tds_code_name,
hcv.tds_code_number,
hcv.wct_tax_code_name,
hcv.wct_tax_code_number,
hcv.esi_tax_code_name,
hcv.esi_tax_code_number,
hcv.default_tds,
hcv.applicable_tds_exemption,
hcv.tds_vendor_type,
hcv."description at distribution",
hcv.payment_type,
hcv.bank_account_name,
hcv.check_amount,
hcv.document_number,
hcv.bank_account_number,
hcv.payment_voucher_number,
hcv.pay_group,
hcv.payment_date,
hcv.reverse_check_number,
hcv.payment_batch_name,
hcv.status,
hcv.file_status,
hcv."Context",
hcv."check deposit date",
hcv."challan number",
hcv."AR Number",
hcv.transaction_type,
hcv.vendor_site_state,
hcv.vendor_registration_no,
hcv.bank_gstn_no,
hcv.pop,
hcv.pos,
hcv.hsn_code,
hcv.cgst_amount,
hcv.cgst_rate,
hcv.sgst_amount,
hcv.sgst_rate,
hcv.igst_amount,
hcv.igst_rate,
hcv.utgst_amount,
hcv.utgst_rate,
hcv.cess_rate,
hcv.context_value,
hcv.tax_type,
hcv.vendor_pan,
hcv.tax_id_tds,
hcv.taxable_amount,
hcv.asp_flag,
decode(hcv.invoice_gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.invoice_gl_date,'yyyy'),'yyyy')||'01','yyyymm')) invoice_gl_date_year,
decode(hcv.invoice_gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.invoice_gl_date,'q'),'mm')||'1900','mmyyyy')) invoice_gl_date_quarter,
decode(hcv.invoice_gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.invoice_gl_date,'mm'),'mm')||'1900','mmyyyy')) invoice_gl_date_month,
decode(hcv.invoice_gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.invoice_gl_date,'dd'),'dd')||'190001','ddyyyymm')) invoice_gl_date_day,
decode(hcv.created_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.created_date,'yyyy'),'yyyy')||'01','yyyymm')) created_date_year,
decode(hcv.created_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.created_date,'q'),'mm')||'1900','mmyyyy')) created_date_quarter,
decode(hcv.created_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.created_date,'mm'),'mm')||'1900','mmyyyy')) created_date_month,
decode(hcv.created_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.created_date,'dd'),'dd')||'190001','ddyyyymm')) created_date_day,
decode(hcv.prepaid_invoice_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.prepaid_invoice_date,'yyyy'),'yyyy')||'01','yyyymm')) prepaid_invoice_date_year,
decode(hcv.prepaid_invoice_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.prepaid_invoice_date,'q'),'mm')||'1900','mmyyyy')) prepaid_invoice_date_quarter,
decode(hcv.prepaid_invoice_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.prepaid_invoice_date,'mm'),'mm')||'1900','mmyyyy')) prepaid_invoice_date_month,
decode(hcv.prepaid_invoice_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.prepaid_invoice_date,'dd'),'dd')||'190001','ddyyyymm')) prepaid_invoice_date_day,
decode(hcv.hold_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.hold_date,'yyyy'),'yyyy')||'01','yyyymm')) hold_date_year,
decode(hcv.hold_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.hold_date,'q'),'mm')||'1900','mmyyyy')) hold_date_quarter,
decode(hcv.hold_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.hold_date,'mm'),'mm')||'1900','mmyyyy')) hold_date_month,
decode(hcv.hold_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.hold_date,'dd'),'dd')||'190001','ddyyyymm')) hold_date_day,
decode(hcv.release_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.release_date,'yyyy'),'yyyy')||'01','yyyymm')) release_date_year,
decode(hcv.release_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.release_date,'q'),'mm')||'1900','mmyyyy')) release_date_quarter,
decode(hcv.release_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.release_date,'mm'),'mm')||'1900','mmyyyy')) release_date_month,
decode(hcv.release_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.release_date,'dd'),'dd')||'190001','ddyyyymm')) release_date_day,
decode(hcv.gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.gl_date,'yyyy'),'yyyy')||'01','yyyymm')) gl_date_year,
decode(hcv.gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.gl_date,'q'),'mm')||'1900','mmyyyy')) gl_date_quarter,
decode(hcv.gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.gl_date,'mm'),'mm')||'1900','mmyyyy')) gl_date_month,
decode(hcv.gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.gl_date,'dd'),'dd')||'190001','ddyyyymm')) gl_date_day,
decode(hcv.payment_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.payment_date,'yyyy'),'yyyy')||'01','yyyymm')) payment_date_year,
decode(hcv.payment_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.payment_date,'q'),'mm')||'1900','mmyyyy')) payment_date_quarter,
decode(hcv.payment_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.payment_date,'mm'),'mm')||'1900','mmyyyy')) payment_date_month,
decode(hcv.payment_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.payment_date,'dd'),'dd')||'190001','ddyyyymm')) payment_date_day,
hcv.uqc_code,
hcv.uqc_quantity,
hcv.reference_number,
hcv.txn_source,
hcv.original_line_number,
sum(hcv.invoice_line_num) invoice_line_num,
sum(hcv.available_amount) available_amount,
sum(hcv.cess_amount) cess_amount,
sum(hcv.total_tax_amount) total_tax_amount,
sum(hcv.tds_rate) tds_rate
from
hdfc.hdfc_consolidated_vw hcv
where
1=1
group by
hcv.crs_number,
hcv.system_inputter,
hcv.system_inputter_desc,
hcv.trx_source,
hcv.inputter,
hcv.invoice_classification,
hcv.invoice_type,
hcv.receipt_date,
hcv.tat_date,
hcv.requestor_number,
hcv.requestor_name,
hcv.requestor_location,
hcv.approver_number,
hcv.approver_name,
hcv.requestor_zone,
hcv.send_back_to,
hcv.send_back_reason,
hcv.send_back_date,
hcv.pod_number,
hcv.courier_agent,
hcv.crs_total,
hcv.type,
hcv.vendor_code,
hcv.vendor,
hcv.vendor_site,
hcv.remit_to_name,
hcv.payable_location,
hcv.invoice_num,
hcv.invoice_date,
hcv.invoice_currency,
hcv.invoice_type1,
hcv."serv.taxnumber WCT number",
hcv.service_tax_number,
hcv.tax,
hcv.invoice_amount,
hcv.tax1,
hcv.amount1,
hcv.tax2,
hcv.amount2,
hcv.prepay_invoice_number1,
hcv.inward_number,
hcv.requestor,
hcv.approver,
hcv.invoice_number,
hcv.tds_amount1,
hcv.set_off_invoice,
hcv.amount_paid,
hcv.paid_on_date,
hcv.cheque_number,
hcv.payment_bank_account,
hcv.ramco_reference_name,
hcv.pv_number,
hcv.narration,
hcv."Expense_Report_Inputter",
hcv."Petty_Cash_Approver",
hcv."Employee",
hcv."Tour Code",
hcv."Ticket Jacket Number",
hcv."Basic Fare",
hcv."Travel Sector",
hcv."Period of Travel",
hcv."Apex / Non Apex",
hcv."Travel Agent Name",
hcv."Commission Amount (Agent)",
hcv."Credit Card no.",
hcv."Hotel Name",
hcv."Location Visited",
hcv."No. of Days",
hcv."Room Charges per day",
hcv.tax3,
hcv.amount3,
hcv.tax4,
hcv.amount4,
hcv.invoice_amount,
hcv.tds_amount,
hcv.wct_amount,
hcv.prepaid_amount,
hcv.net_amount,
hcv.invoice_sequence,
hcv.invoice_gl_date,
hcv.invoice_description,
hcv.created_date,
hcv.payment_group,
hcv.bank_name,
hcv.terms,
hcv.distribution_set,
hcv.prepay_invoice_number,
hcv.prepaid_invoice_date,
hcv.prepaid_invoice_amount_applied,
hcv.invoice_status,
hcv.accounted,
hcv.payment_status,
hcv.hold_name,
hcv.hold_reason,
hcv.held_by,
hcv.hold_date,
hcv.release_name,
hcv.release_reason,
hcv.release_date,
hcv.release_by,
hcv.invoice_id,
hcv.invoice_dist_id,
hcv.distribution_amount,
hcv.gl_date,
hcv.account,
hcv.account_description,
hcv.branch,
hcv.branch_description,
hcv.cost_centre,
hcv.cost_centre_description,
hcv.mis2,
hcv.mis2_description,
hcv.product,
hcv.product_description,
hcv.catergory,
hcv.catergory_description,
hcv.project,
hcv.project_description,
hcv.currency,
hcv.currency_description,
hcv.description,
hcv.expense_type,
hcv.expense_from,
hcv.expense_to,
hcv.future1,
hcv.future2,
hcv.quantity,
hcv.expense_report_remark,
hcv."context",
hcv.tds_code_name,
hcv.tds_code_number,
hcv.wct_tax_code_name,
hcv.wct_tax_code_number,
hcv.esi_tax_code_name,
hcv.esi_tax_code_number,
hcv.default_tds,
hcv.applicable_tds_exemption,
hcv.tds_vendor_type,
hcv."description at distribution",
hcv.payment_type,
hcv.bank_account_name,
hcv.check_amount,
hcv.document_number,
hcv.bank_account_number,
hcv.payment_voucher_number,
hcv.pay_group,
hcv.payment_date,
hcv.reverse_check_number,
hcv.payment_batch_name,
hcv.status,
hcv.file_status,
hcv."Context",
hcv."check deposit date",
hcv."challan number",
hcv."AR Number",
hcv.transaction_type,
hcv.vendor_site_state,
hcv.vendor_registration_no,
hcv.bank_gstn_no,
hcv.pop,
hcv.pos,
hcv.hsn_code,
hcv.cgst_amount,
hcv.cgst_rate,
hcv.sgst_amount,
hcv.sgst_rate,
hcv.igst_amount,
hcv.igst_rate,
hcv.utgst_amount,
hcv.utgst_rate,
hcv.cess_rate,
hcv.context_value,
hcv.tax_type,
hcv.vendor_pan,
hcv.tax_id_tds,
hcv.taxable_amount,
hcv.asp_flag,
decode(hcv.invoice_gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.invoice_gl_date,'yyyy'),'yyyy')||'01','yyyymm')),
decode(hcv.invoice_gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.invoice_gl_date,'q'),'mm')||'1900','mmyyyy')),
decode(hcv.invoice_gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.invoice_gl_date,'mm'),'mm')||'1900','mmyyyy')),
decode(hcv.invoice_gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.invoice_gl_date,'dd'),'dd')||'190001','ddyyyymm')),
decode(hcv.created_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.created_date,'yyyy'),'yyyy')||'01','yyyymm')),
decode(hcv.created_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.created_date,'q'),'mm')||'1900','mmyyyy')),
decode(hcv.created_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.created_date,'mm'),'mm')||'1900','mmyyyy')),
decode(hcv.created_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.created_date,'dd'),'dd')||'190001','ddyyyymm')),
decode(hcv.prepaid_invoice_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.prepaid_invoice_date,'yyyy'),'yyyy')||'01','yyyymm')),
decode(hcv.prepaid_invoice_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.prepaid_invoice_date,'q'),'mm')||'1900','mmyyyy')),
decode(hcv.prepaid_invoice_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.prepaid_invoice_date,'mm'),'mm')||'1900','mmyyyy')),
decode(hcv.prepaid_invoice_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.prepaid_invoice_date,'dd'),'dd')||'190001','ddyyyymm')),
decode(hcv.hold_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.hold_date,'yyyy'),'yyyy')||'01','yyyymm')),
decode(hcv.hold_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.hold_date,'q'),'mm')||'1900','mmyyyy')),
decode(hcv.hold_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.hold_date,'mm'),'mm')||'1900','mmyyyy')),
decode(hcv.hold_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.hold_date,'dd'),'dd')||'190001','ddyyyymm')),
decode(hcv.release_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.release_date,'yyyy'),'yyyy')||'01','yyyymm')),
decode(hcv.release_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.release_date,'q'),'mm')||'1900','mmyyyy')),
decode(hcv.release_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.release_date,'mm'),'mm')||'1900','mmyyyy')),
decode(hcv.release_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.release_date,'dd'),'dd')||'190001','ddyyyymm')),
decode(hcv.gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.gl_date,'yyyy'),'yyyy')||'01','yyyymm')),
decode(hcv.gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.gl_date,'q'),'mm')||'1900','mmyyyy')),
decode(hcv.gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.gl_date,'mm'),'mm')||'1900','mmyyyy')),
decode(hcv.gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.gl_date,'dd'),'dd')||'190001','ddyyyymm')),
decode(hcv.payment_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.payment_date,'yyyy'),'yyyy')||'01','yyyymm')),
decode(hcv.payment_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.payment_date,'q'),'mm')||'1900','mmyyyy')),
decode(hcv.payment_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.payment_date,'mm'),'mm')||'1900','mmyyyy')),
decode(hcv.payment_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hcv.payment_date,'dd'),'dd')||'190001','ddyyyymm')),
hcv.uqc_code,
hcv.uqc_quantity,
hcv.reference_number,
hcv.txn_source,
hcv.original_line_number
Parameter NameSQL textValidation
GL Date
hcv.gl_date=:gl_date
Date
Download
Blitz Report™