SQLize | PHPize | SQLtest

/**************************************************************************** DATA MODEL NAME : LMG_ACCOUNT_PAYABLE_TRIAL_BALANCE_DM REPORT NAME : LMG Account Payable Trial Balance Report Change History: Version Name Date Comments ----------------------------------------------------------------------------------------------- V1 Manasa Panigrahi 3-April-2025 Initial Version -----------------------------------------------------------------------------------------------*/ SELECT DISTINCT CASE WHEN Substr(vl.legal_entity_name, -4) = '_FIN' THEN Substr(vl.legal_entity_name, 1, Length(vl.legal_entity_name) - 4) ELSE vl.legal_entity_name END "Entity Name", CASE WHEN Substr(bu.bu_name, -4) = '_FIN' THEN Substr(bu.bu_name, 1, Length(bu.bu_name) - 4) ELSE bu.bu_name END "Business Unit" , aia.source "Invoice Source", NULL "COGNIX URN", --aia.voucher_num "Voucher Number", aia.doc_sequence_value "Voucher Number", NULL "Department", ps.vendor_type_lookup_code "Supplier Classification", hp.party_name "Supplier Name" , ps.segment1 "Supplier Number", pss.vendor_site_code "Invoice Supplier Site", aia.invoice_type_lookup_code "Invoice Type", aia.invoice_num "Invoice Number", Initcap(To_char(aia.invoice_received_date, 'DD-MON-YY', 'nls_date_language=english')) "Invoice Received Date", Initcap(To_char(aia.invoice_date, 'DD-MON-YY', 'nls_date_language=english')) "Invoice Date", Initcap(To_char(aphis.creation_date, 'DD-MON-YY', 'nls_date_language=english')) "Invoice Approval Date", --NULL "Invoice Approval Date", Initcap(To_char(aia.gl_date, 'DD-MON-YY', 'nls_date_language=english')) "GL Accounting Date", aia.po_header_id "PO Number", NULL "ASN Number", NULL "Customs Invoice Number", NULL "Accounting Invoice Number", aia.invoice_currency_code "Transaction Currency", CASE WHEN aia.payment_status_flag = 'P' and AP_INVOICES_PKG.GET_POSTING_STATUS( aia.INVOICE_ID ) = 'Y' THEN aia.invoice_amount - aia.amount_paid WHEN aia.payment_status_flag = 'P' and AP_INVOICES_PKG.GET_POSTING_STATUS( aia.INVOICE_ID ) = 'P' THEN aia.invoice_amount ELSE aia.invoice_amount END "Transaction Amount Exc VAT", aia.total_tax_amount "Transaction VAT Amount", CASE WHEN aia.payment_status_flag = 'P' and AP_INVOICES_PKG.GET_POSTING_STATUS( aia.INVOICE_ID ) = 'Y' THEN (aia.invoice_amount - aia.amount_paid)+ aia.total_tax_amount WHEN aia.payment_status_flag = 'P' and AP_INVOICES_PKG.GET_POSTING_STATUS( aia.INVOICE_ID ) = 'P' THEN aia.invoice_amount + aia.total_tax_amount ELSE aia.invoice_amount + aia.total_tax_amount END "Transaction Amount Inc VAT", aia.exchange_rate_type "Exchange Rate Type", aia.exchange_rate "Exchange Rate" , gl.currency_code "Local Currency", CASE WHEN aia.payment_status_flag = 'P' and AP_INVOICES_PKG.GET_POSTING_STATUS( aia.INVOICE_ID ) = 'Y' THEN (aia.invoice_amount - aia.amount_paid) * aia.exchange_rate WHEN aia.payment_status_flag = 'P' and AP_INVOICES_PKG.GET_POSTING_STATUS( aia.INVOICE_ID ) = 'P' THEN aia.invoice_amount * aia.exchange_rate ELSE aia.invoice_amount * aia.exchange_rate END "Local Amount Exc VAT", aia.total_tax_amount * aia.exchange_rate "Local VAT Amount", CASE WHEN aia.payment_status_flag = 'P' and AP_INVOICES_PKG.GET_POSTING_STATUS( aia.INVOICE_ID ) = 'Y' THEN ((aia.invoice_amount - aia.amount_paid)+ aia.total_tax_amount) * aia.exchange_rate WHEN aia.payment_status_flag = 'P' and AP_INVOICES_PKG.GET_POSTING_STATUS( aia.INVOICE_ID ) = 'P' THEN ( aia.invoice_amount + aia.total_tax_amount ) * aia.exchange_rate ELSE ( aia.invoice_amount + aia.total_tax_amount ) * aia.exchange_rate END "Local Amount Inc VAT", aila.description "Invoice Line Description", Decode(aida.match_status_flag, 'A', 'Validated') "Invoice Status", aia.created_by "Invoice Created HRMS ID", (SELECT DISTINCT ppnf.display_name --ppnf.display_name,pu.person_id FROM per_users pu, per_person_names_f ppnf WHERE pu.person_id = ppnf.person_id AND pu.username = aia.created_by) "Invoice Created By Name", gcc.segment1 "Segment1", gcc.segment2 "Segment2", gcc.segment3 "Segment3", gcc.segment4 "Segment4", gcc.segment5 "Segment5", gcc.segment6 "Segment6", gcc.segment7 "Segment7", gcc.segment8 "Segment8", aps.amount_remaining * aia.exchange_rate "Remaining Amount (Local Currency)", NULL "KYC Status", Trunc(SYSDATE - aia.invoice_date) "Aging from Invoice Date", Trunc(SYSDATE - aia.invoice_received_date) "Aging from Invoice Received Date", Trunc(SYSDATE - aia.gl_date) "Aging from GL Date", --(select ater.name from AP_TERMS_TL ater where ater.term_id = aia.TERMS_ID and ater.SOURCE_LANG = 'US') "Payment Terms", ater.name "Payment Terms" , CASE WHEN aps.due_date < SYSDATE THEN 'DUE' ELSE 'Not Due' END "Due Status", --Decode(aida.posted_flag, 'Y', 'Accounted') DECODE(AP_INVOICES_PKG.GET_POSTING_STATUS( aia.INVOICE_ID ), 'P', 'Partially Accounted', 'N', 'Unaccounted', 'Y', 'Accounted') "Invoice Accounting Status", aia.approval_status "Invoice Approval Status", aia.payment_amount_total "Payment Amount", CASE WHEN aila.LINE_TYPE_LOOKUP_CODE IN ('TAX','PREPAY') THEN ( -1 ) * sum(( AILA.amount - Nvl(AILA.included_tax_amount, 0))) --nvl(AILA.amount,0) -Nvl(AILA.included_tax_amount, 0) END "Prepayment Applied Amount", --nvl(aia.amount_paid,0) -- NULL "Prepayment Applied Amount", aps.amount_remaining "Remaining Amount (Transaction Currency)", aila.retained_amount "Retention Amount" FROM gl_ledgers gl, gl_je_headers gjh, gl_je_lines gjl, gl_code_combinations gcc, gl_import_references gir, xla_ae_lines xal, xla_ae_headers xah, xla_events xev, ap_invoice_distributions_all aida, ap_invoice_lines_all aila, ap_invoices_all aia, fusion.fun_all_business_units_v bu, vrm_legal_entities_v vl, ap_terms_tl ater, poz_suppliers ps, hz_parties hp, poz_supplier_sites_all_m pss, ap_inv_aprvl_hist_all aphis, ap_payment_schedules_all aps WHERE 1 = 1 AND bu.legal_entity_id = vl.legal_entity_id AND gl.ledger_id = gjh.ledger_id AND gjh.je_header_id = gjl.je_header_id --AND gjh.status = 'P' AND gcc.code_combination_id = gjl.code_combination_id AND gir.je_header_id = gjl.je_header_id AND gir.je_line_num = gjl.je_line_num AND XAL.gl_sl_link_id = gir.gl_sl_link_id AND xal.gl_sl_link_table = gir.gl_sl_link_table AND xal.code_combination_id = gcc.code_combination_id AND xah.ae_header_id = XAL.ae_header_id --AND xah.gl_transfer_status_code = 'Y' AND xev.event_id = xah.event_id AND aida.accounting_event_id = xev.event_id AND aila.line_number = aida.distribution_line_number AND aia.invoice_id = aila.invoice_id AND aia.invoice_id = aida.invoice_id AND ater.term_id = aia.terms_id AND ater.source_lang = 'US' AND ps.vendor_id = aia.vendor_id AND ps.party_id = hp.party_id AND pss.vendor_id = aia.vendor_id AND aia.vendor_site_id = pss.vendor_site_id AND aps.invoice_id = aia.invoice_id AND aia.payment_status_flag IN ( 'N', 'P' ) AND aida.posted_flag <> 'N' AND aia.cancelled_date IS NULL AND aphis.invoice_id = aia.invoice_id and aia.invoice_type_lookup_code IN ('STANDARD', 'CREDIT NOTE', 'DEBIT NOTE', 'PREPAYMENT') --Added on 4/3/25 --AND aila.prepay_invoice_id IS NOT NULL --AND aila.amount <0 --AND NVL(aila.DISCARDED_FLAG,'N') <> 'Y' AND vl.legal_entity_name = :P_Legal_Entity AND aia.invoice_date = Nvl(:P_As_On_Date, aia.invoice_date) --AND bu.bu_name IN ( :P_Business_Unit ) AND ( bu.bu_name IN ( :P_Business_Unit ) OR 'All' IN ( :P_Business_Unit || 'All' ) ) and aia.invoice_num = 'SETUP12' GROUP BY --vl.legal_entity_name, --bu.bu_name, aia.source, aia.voucher_num, aia.invoice_type_lookup_code, aia.invoice_num, aia.invoice_received_date, aia.po_header_id, aia.invoice_currency_code, aia.invoice_amount, aia.total_tax_amount, aia.exchange_rate_type, aia.exchange_rate, aila.description, gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment4, gcc.segment5, gcc.segment6, gcc.segment7, gcc.segment8, --ater.name, aia.approval_status, aia.payment_amount_total, aila.retained_amount, aia.gl_date, aia.invoice_date, aida.match_status_flag, aida.posted_flag, ps.vendor_type_lookup_code, hp.party_name, ps.segment1, pss.vendor_site_code, aia.created_by, aphis.creation_date, gl.currency_code, aia.amount_paid, aps.amount_remaining, aps.due_date , aila.AMOUNT, aila.INCLUDED_TAX_AMOUNT, aia.doc_sequence_value , CASE WHEN Substr(vl.legal_entity_name, -4) = '_FIN' THEN Substr(vl.legal_entity_name, 1, Length(vl.legal_entity_name) - 4) ELSE vl.legal_entity_name END , CASE WHEN Substr(bu.bu_name, -4) = '_FIN' THEN Substr(bu.bu_name, 1, Length(bu.bu_name) - 4) ELSE bu.bu_name END , CASE WHEN aia.payment_status_flag = 'P' and AP_INVOICES_PKG.GET_POSTING_STATUS( aia.INVOICE_ID ) = 'Y' THEN aia.invoice_amount - aia.amount_paid WHEN aia.payment_status_flag = 'P' and AP_INVOICES_PKG.GET_POSTING_STATUS( aia.INVOICE_ID ) = 'P' THEN aia.invoice_amount ELSE aia.invoice_amount END, CASE WHEN aia.payment_status_flag = 'P' and AP_INVOICES_PKG.GET_POSTING_STATUS( aia.INVOICE_ID ) = 'Y' THEN (aia.invoice_amount - aia.amount_paid)+ aia.total_tax_amount WHEN aia.payment_status_flag = 'P' and AP_INVOICES_PKG.GET_POSTING_STATUS( aia.INVOICE_ID ) = 'P' THEN aia.invoice_amount + aia.total_tax_amount ELSE aia.invoice_amount + aia.total_tax_amount END , CASE WHEN aia.payment_status_flag = 'P' and AP_INVOICES_PKG.GET_POSTING_STATUS( aia.INVOICE_ID ) = 'Y' THEN (aia.invoice_amount - aia.amount_paid) * aia.exchange_rate WHEN aia.payment_status_flag = 'P' and AP_INVOICES_PKG.GET_POSTING_STATUS( aia.INVOICE_ID ) = 'P' THEN aia.invoice_amount * aia.exchange_rate ELSE aia.invoice_amount * aia.exchange_rate END , CASE WHEN aia.payment_status_flag = 'P' and AP_INVOICES_PKG.GET_POSTING_STATUS( aia.INVOICE_ID ) = 'Y' THEN ((aia.invoice_amount - aia.amount_paid)+ aia.total_tax_amount) * aia.exchange_rate WHEN aia.payment_status_flag = 'P' and AP_INVOICES_PKG.GET_POSTING_STATUS( aia.INVOICE_ID ) = 'P' THEN ( aia.invoice_amount + aia.total_tax_amount ) * aia.exchange_rate ELSE ( aia.invoice_amount + aia.total_tax_amount ) * aia.exchange_rate END, CASE WHEN aps.due_date < SYSDATE THEN 'DUE' ELSE 'Not Due' END