ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 55084D92170CDB1CF4B96641D6E0F5F4Y 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 Initial version - migrated from the Oracle Discoverer worksheet on APPS.HDFC_AP_NEVER_VALID_COMBINED_V. Single exact GL Date parameter; the Discoverer Day/Month/Quarter/Year date buckets and the SUM(Available Amount)/SUM(Dist Line Num) aggregation over all other columns are retained. :GL_Date is not null and :GL_Date_From is not null or :GL_Date_To is not null US AP Never Validated Invoices 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). Custom Reports HDFC 1=1 1 10 1=1 hanvcv.gl_date=:gl_date Date US GL Date 2 20 1=1 hanvcv.gl_date>=:gl_date_from Date US GL Date From 3 30 1=1 hanvcv.gl_date<:gl_date_to+1 Date last_day(:$flex$.gl_date_from) US GL Date To