AP Never Validated Invoices

➜ Back to Library
Description
Categories: Custom Reports HDFC
AP invoices and distributions that were never validated, sourced from the HDFC combined view, with the full set of invoice, distribution, account, cost centre, branch, banking, requestor, expense, TDS and GST attributes.

Migrated from the Oracle Discoverer worksheet built on APPS.HDFC_AP_NEVER_VALID_COMBINED_V. Available Amount and Distribution Line Number are summed for each unique combina ...  AP invoices and distributions that were never validated, sourced from the HDFC combined view, with the full set of invoice, distribution, account, cost centre, branch, banking, requestor, expense, TDS and GST attributes.

Migrated from the Oracle Discoverer worksheet built on APPS.HDFC_AP_NEVER_VALID_COMBINED_V. Available Amount and Distribution Line Number are summed for each unique combination of all other columns, and the Discoverer Day/Month/Quarter/Year date buckets for Distribution Accounting Date, GL Date and Terms Date are retained.

Enter a GL Date to report on a single accounting day (exact match).   more 
select
hanvcv.accounting_period,
hanvcv.account_code,
hanvcv.account_description,
hanvcv.applicable_tds_exemptions,
hanvcv.approver_name,
hanvcv.approver_number,
hanvcv.attribute1,
hanvcv.attribute10,
hanvcv.attribute11,
hanvcv.attribute12,
hanvcv.attribute13,
hanvcv.attribute14,
hanvcv.attribute15,
hanvcv.attribute2,
hanvcv.attribute3,
hanvcv.attribute4,
hanvcv.attribute5,
hanvcv.attribute6,
hanvcv.attribute7,
hanvcv.attribute8,
hanvcv.attribute9,
hanvcv.bank_name,
hanvcv.bank_number,
hanvcv.batch_date,
hanvcv.batch_type,
hanvcv.branch_code,
hanvcv.branch_description,
hanvcv.cost_center_code,
hanvcv.cost_center_description,
hanvcv.crs_amount,
hanvcv.crs_number,
hanvcv.distribution_accounting_date,
hanvcv.distribution_amount,
hanvcv.distribution_description,
hanvcv.distribution_status,
hanvcv.dist_attribute_category,
hanvcv.expense_from,
hanvcv.expense_report_remarks,
hanvcv.expense_to,
hanvcv.expense_type,
hanvcv.gl_date,
hanvcv.hold_reason,
hanvcv.inputter_name,
hanvcv.inputter_vendor_name,
hanvcv.inputter_vendor_number,
hanvcv.invoice_amount,
hanvcv.invoice_classification,
hanvcv.invoice_date,
hanvcv.invoice_description,
hanvcv.invoice_number,
hanvcv.invoice_type,
hanvcv.inv_attribute_category,
hanvcv.inwarder_vendor_name,
hanvcv.inwarder_vendor_number,
hanvcv.line_number,
hanvcv.payment_currency_code,
hanvcv.payment_lookup_code,
hanvcv.pay_group,
hanvcv.pod_details,
hanvcv.quantity,
hanvcv.receipt_date,
hanvcv.requestor_location,
hanvcv.requestor_name,
hanvcv.requestor_number,
hanvcv.requestor_zone,
hanvcv.send_back_date,
hanvcv.tat_date,
hanvcv.tax_id,
hanvcv.tax_name,
hanvcv.tds_section,
hanvcv.tds_vendor_type,
hanvcv.terms_date,
hanvcv.terms_name,
hanvcv.vendor_site,
hanvcv.wct_id,
hanvcv.wct_tax_name,
decode(hanvcv.distribution_accounting_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.distribution_accounting_date,'dd'),'dd')||'190001','ddyyyymm')) distribution_accounting_date_day,
decode(hanvcv.distribution_accounting_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.distribution_accounting_date,'mm'),'mm')||'1900','mmyyyy')) distribution_accounting_date_month,
decode(hanvcv.distribution_accounting_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.distribution_accounting_date,'q'),'mm')||'1900','mmyyyy')) distribution_accounting_date_quarter,
decode(hanvcv.distribution_accounting_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.distribution_accounting_date,'yyyy'),'yyyy')||'01','yyyymm')) distribution_accounting_date_year,
decode(hanvcv.gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.gl_date,'dd'),'dd')||'190001','ddyyyymm')) gl_date_day,
decode(hanvcv.gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.gl_date,'mm'),'mm')||'1900','mmyyyy')) gl_date_month,
decode(hanvcv.gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.gl_date,'q'),'mm')||'1900','mmyyyy')) gl_date_quarter,
decode(hanvcv.gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.gl_date,'yyyy'),'yyyy')||'01','yyyymm')) gl_date_year,
decode(hanvcv.terms_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.terms_date,'dd'),'dd')||'190001','ddyyyymm')) terms_date_day,
decode(hanvcv.terms_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.terms_date,'mm'),'mm')||'1900','mmyyyy')) terms_date_month,
decode(hanvcv.terms_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.terms_date,'q'),'mm')||'1900','mmyyyy')) terms_date_quarter,
decode(hanvcv.terms_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.terms_date,'yyyy'),'yyyy')||'01','yyyymm')) terms_date_year,
hanvcv.pop,
hanvcv.pos,
hanvcv.supplier_tax_invoice_number,
hanvcv.supplier_tax_invoice_date,
hanvcv.line_type_lookup_code,
hanvcv.hsn_code,
hanvcv.gstn_bank,
hanvcv.sac_code,
hanvcv.original_line_number,
hanvcv.tax_percentage,
hanvcv.tax_type,
hanvcv.gstn_invoice_number,
hanvcv.nature_of_import,
hanvcv.nature_of_service,
hanvcv.itc_status,
hanvcv.pos_code,
hanvcv.reference_number,
hanvcv.release_reason,
hanvcv.supplier_pan_number,
hanvcv.supplier_gstn_number,
hanvcv.supplier_invoice_number,
sum(hanvcv.available_amount) available_amount,
sum(hanvcv.dist_line_num) dist_line_num
from
hdfc_ap_never_valid_combined_v hanvcv
where
1=1
group by
hanvcv.accounting_period,
hanvcv.account_code,
hanvcv.account_description,
hanvcv.applicable_tds_exemptions,
hanvcv.approver_name,
hanvcv.approver_number,
hanvcv.attribute1,
hanvcv.attribute10,
hanvcv.attribute11,
hanvcv.attribute12,
hanvcv.attribute13,
hanvcv.attribute14,
hanvcv.attribute15,
hanvcv.attribute2,
hanvcv.attribute3,
hanvcv.attribute4,
hanvcv.attribute5,
hanvcv.attribute6,
hanvcv.attribute7,
hanvcv.attribute8,
hanvcv.attribute9,
hanvcv.bank_name,
hanvcv.bank_number,
hanvcv.batch_date,
hanvcv.batch_type,
hanvcv.branch_code,
hanvcv.branch_description,
hanvcv.cost_center_code,
hanvcv.cost_center_description,
hanvcv.crs_amount,
hanvcv.crs_number,
hanvcv.distribution_accounting_date,
hanvcv.distribution_amount,
hanvcv.distribution_description,
hanvcv.distribution_status,
hanvcv.dist_attribute_category,
hanvcv.expense_from,
hanvcv.expense_report_remarks,
hanvcv.expense_to,
hanvcv.expense_type,
hanvcv.gl_date,
hanvcv.hold_reason,
hanvcv.inputter_name,
hanvcv.inputter_vendor_name,
hanvcv.inputter_vendor_number,
hanvcv.invoice_amount,
hanvcv.invoice_classification,
hanvcv.invoice_date,
hanvcv.invoice_description,
hanvcv.invoice_number,
hanvcv.invoice_type,
hanvcv.inv_attribute_category,
hanvcv.inwarder_vendor_name,
hanvcv.inwarder_vendor_number,
hanvcv.line_number,
hanvcv.payment_currency_code,
hanvcv.payment_lookup_code,
hanvcv.pay_group,
hanvcv.pod_details,
hanvcv.quantity,
hanvcv.receipt_date,
hanvcv.requestor_location,
hanvcv.requestor_name,
hanvcv.requestor_number,
hanvcv.requestor_zone,
hanvcv.send_back_date,
hanvcv.tat_date,
hanvcv.tax_id,
hanvcv.tax_name,
hanvcv.tds_section,
hanvcv.tds_vendor_type,
hanvcv.terms_date,
hanvcv.terms_name,
hanvcv.vendor_site,
hanvcv.wct_id,
hanvcv.wct_tax_name,
decode(hanvcv.distribution_accounting_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.distribution_accounting_date,'dd'),'dd')||'190001','ddyyyymm')),
decode(hanvcv.distribution_accounting_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.distribution_accounting_date,'mm'),'mm')||'1900','mmyyyy')),
decode(hanvcv.distribution_accounting_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.distribution_accounting_date,'q'),'mm')||'1900','mmyyyy')),
decode(hanvcv.distribution_accounting_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.distribution_accounting_date,'yyyy'),'yyyy')||'01','yyyymm')),
decode(hanvcv.gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.gl_date,'dd'),'dd')||'190001','ddyyyymm')),
decode(hanvcv.gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.gl_date,'mm'),'mm')||'1900','mmyyyy')),
decode(hanvcv.gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.gl_date,'q'),'mm')||'1900','mmyyyy')),
decode(hanvcv.gl_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.gl_date,'yyyy'),'yyyy')||'01','yyyymm')),
decode(hanvcv.terms_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.terms_date,'dd'),'dd')||'190001','ddyyyymm')),
decode(hanvcv.terms_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.terms_date,'mm'),'mm')||'1900','mmyyyy')),
decode(hanvcv.terms_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.terms_date,'q'),'mm')||'1900','mmyyyy')),
decode(hanvcv.terms_date,null,to_date(null,'mmddyyyy'),to_date(to_char(trunc(hanvcv.terms_date,'yyyy'),'yyyy')||'01','yyyymm')),
hanvcv.pop,
hanvcv.pos,
hanvcv.supplier_tax_invoice_number,
hanvcv.supplier_tax_invoice_date,
hanvcv.line_type_lookup_code,
hanvcv.hsn_code,
hanvcv.gstn_bank,
hanvcv.sac_code,
hanvcv.original_line_number,
hanvcv.tax_percentage,
hanvcv.tax_type,
hanvcv.gstn_invoice_number,
hanvcv.nature_of_import,
hanvcv.nature_of_service,
hanvcv.itc_status,
hanvcv.pos_code,
hanvcv.reference_number,
hanvcv.release_reason,
hanvcv.supplier_pan_number,
hanvcv.supplier_gstn_number,
hanvcv.supplier_invoice_number
Parameter NameSQL textValidation
GL Date
hanvcv.gl_date=:gl_date
Date
GL Date From
hanvcv.gl_date>=:gl_date_from
Date
GL Date To
hanvcv.gl_date<:gl_date_to+1
Date
Download
Related Blitz Reports
CE Bank Branches AP Invoices and Lines COPY OF: AP Invoices and Lines AP Invoices and Lines - Test APL: AP Invoices and Lines LSE: AP Invoices and Lines(Test) AP TDS Details Report GL MoFU KPI Dashboard Exceptions GL MoFU KPI Dashboard
Blitz Report™