ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN B7E9098EE6581441BC4D17655DA75D29Y 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 US AP Consolidated Invoices 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). Custom Reports HDFC 1=1 1 10 1=1 hcv.gl_date=:gl_date Date Y US GL Date