Query to write Custom Invoice And Payment Report that matches with GL Liability Account Balance, GL Prepayment Account Balance, Expense Accounts etc.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 |
SELECT aipa.check_number, aipa.check_id, aia.payment_method_code, aia.payment_status_flag, aipa.status_lookup_code, aipa.reversal_flag, aia.wfapproval_status, haout.name, asup.vendor_name, asup.vendor_id, aia.invoice_num, aia.invoice_id, aida.invoice_line_number, aida.distribution_line_number, TO_CHAR(aia.invoice_date,'MM/DD/YYYY') invoice_date, TO_CHAR(aia.gl_date,'MM/DD/YYYY') inv_gl_date, TO_CHAR(aida.accounting_date,'MM/DD/YYYY') dist_acct_date, aida.period_name dist_period_name, aida.accrual_posted_flag, aida.cash_posted_flag, aida.posted_flag, aida.reversal_flag, adsa.distribution_set_name, gcc.concatenated_segments coa_segments, gcc.segment1 company, cb_company.description company_desc, gcc.segment2 account, cb_account.description account_desc, gcc.segment3 business_line, cb_business_line.description business_line_desc, gcc.segment4 counterparty, cb_counterparty.description counterparty_desc, gcc.segment5 deal, cb_deal.description deal_desc, gcc.segment6 project, cb_project.description proj_desc, gcc.segment7 partner, cb_partner.description partner_desc, gcc.segment8 ic_company, cb_ic_company.description ic_comp_desc, gcc.segment9 qb_legacy, cb_qb_legacy.description qb_legacy_desc, gcc.segment10 future, cb_future.description future_desc, aida.attribute1, aida.attribute2, aida.description, aia.invoice_currency_code, aia.exchange_rate, aia.invoice_amount, aida.amount dist_amt, nvl(aida.base_amount,aida.amount) dist_accounted_amt, aida.attribute4 ar_trx_num, assa.vendor_site_code vendor_site_name, aia.reference_key1 exp_rpt_id, aipa.check_amt, TO_CHAR(aipa.check_date,'MM/DD/YYYY') check_date, cbb.bank_name bank_name, DECODE(aipa.paycard_reference_id,NULL,cba.bank_account_name,aipa.bank_account_name) current_bank_account_name, TO_CHAR(aipa.void_date,'MM/DD/YYYY') void_date, aida.line_type_lookup_code dist_type FROM (SELECT ffv.flex_value seg_value,ffv.description FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs WHERE fvs.flex_value_set_name = 'CB_FUTURE' AND fvs.flex_value_set_id = ffv.flex_value_set_id) cb_future, (SELECT ffv.flex_value seg_value,ffv.description FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs WHERE fvs.flex_value_set_name = 'CB_QB_LEGACY' AND fvs.flex_value_set_id = ffv.flex_value_set_id) cb_qb_legacy, (SELECT ffv.flex_value seg_value,ffv.description FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs WHERE fvs.flex_value_set_name = 'CB_COMPANY' AND fvs.flex_value_set_id = ffv.flex_value_set_id) cb_ic_company, (SELECT ffv.flex_value seg_value,ffv.description FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs WHERE fvs.flex_value_set_name = 'CB_PARTNER' AND fvs.flex_value_set_id = ffv.flex_value_set_id) cb_partner, (SELECT ffv.flex_value seg_value,ffv.description FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs WHERE fvs.flex_value_set_name = 'CB_PROJECT' AND fvs.flex_value_set_id = ffv.flex_value_set_id) cb_project, (SELECT ffv.flex_value seg_value,ffv.description FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs WHERE fvs.flex_value_set_name = 'CB_DEAL' AND fvs.flex_value_set_id = ffv.flex_value_set_id) cb_deal, (SELECT ffv.flex_value seg_value,ffv.description FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs WHERE fvs.flex_value_set_name = 'CB_COUNTERPARTY' AND fvs.flex_value_set_id = ffv.flex_value_set_id) cb_counterparty, (SELECT ffv.flex_value seg_value,ffv.description FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs WHERE fvs.flex_value_set_name = 'CB_BUSINESS_LINE' AND fvs.flex_value_set_id = ffv.flex_value_set_id) cb_business_line, (SELECT ffv.flex_value seg_value,ffv.description FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs WHERE fvs.flex_value_set_name = 'CB_ACCOUNT' AND fvs.flex_value_set_id = ffv.flex_value_set_id) cb_account, (SELECT ffv.flex_value seg_value,ffv.description FROM fnd_flex_values_vl ffv,fnd_flex_value_sets fvs WHERE fvs.flex_value_set_name = 'CB_COMPANY' AND fvs.flex_value_set_id = ffv.flex_value_set_id) cb_company, --ap_checks_all aca, (SELECT DISTINCT aipa1.check_id check_id, aipa1.invoice_id invoice_id, nvl(aipa1.reversal_flag,'N') reversal_flag, aca1.check_number check_number, aca1.status_lookup_code status_lookup_code, aca1.amount check_amt, aca1.check_date check_date, aca1.paycard_reference_id paycard_reference_id, aca1.bank_account_name bank_account_name, aca1.void_date void_date, aca1.ce_bank_acct_use_id ce_bank_acct_use_id FROM ap_invoice_payments_all aipa1,ap_checks_all aca1 WHERE aipa1.check_id = aca1.check_id) aipa, gl_code_combinations_kfv gcc, hr_all_organization_units_tl haout, ap_suppliers asup, ap_supplier_sites_all assa, ap_distribution_sets_all adsa, ap_invoice_distributions_all aida, ap_invoices_all aia, ce_bank_branches_v cbb, ce_bank_accounts cba, ce_bank_acct_uses_all cbau WHERE aia.invoice_id = aida.invoice_id AND aia.vendor_id = asup.vendor_id AND aia.vendor_site_id = assa.vendor_site_id AND aia.org_id = haout.organization_id AND aia.distribution_set_id = adsa.distribution_set_id (+) AND aida.dist_code_combination_id = gcc.code_combination_id AND gcc.segment1 = cb_company.seg_value (+) AND gcc.segment2 = cb_account.seg_value (+) AND gcc.segment3 = cb_business_line.seg_value (+) AND gcc.segment4 = cb_counterparty.seg_value (+) AND gcc.segment5 = cb_deal.seg_value (+) AND gcc.segment6 = cb_project.seg_value (+) AND gcc.segment7 = cb_partner.seg_value (+) AND gcc.segment8 = cb_ic_company.seg_value (+) AND gcc.segment9 = cb_qb_legacy.seg_value (+) AND gcc.segment10 = cb_future.seg_value (+) AND aia.invoice_id = aipa.invoice_id (+) --AND aipa.check_id = aca.check_id (+) AND cbb.branch_party_id (+) = cba.bank_branch_id AND aipa.ce_bank_acct_use_id = cbau.bank_acct_use_id (+) AND cbau.bank_account_id = cba.bank_account_id (+) &gc_where_clause ORDER BY aia.invoice_num |
–Supporting Package for report query
— Package Specification
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
CREATE OR REPLACE PACKAGE APPS.xxcb_apinv_inquiry_rpt_pkg AUTHID CURRENT_USER AS --======================================================================+ -- Report Lexical Parameters --======================================================================+ gc_where_clause VARCHAR2(32000) := ' AND 1 = 1'; --======================================================================+ -- Report Input Parameters | --======================================================================+ P_FROM_INV_DATE DATE := NULL; P_TO_INV_DATE DATE := NULL; P_FROM_PAYMENT_DATE DATE := NULL; P_TO_PAYMENT_DATE DATE := NULL; P_SUPPLIER_ID NUMBER := NULL; P_FROM_GL_DATE DATE := NULL; P_TO_GL_DATE DATE := NULL; P_FROM_DIST_GL_DATE DATE := NULL; P_TO_DIST_GL_DATE DATE := NULL; P_GL_ACCOUNT VARCHAR2(10) := NULL; P_COMPANY VARCHAR2(75) := NULL; P_COUNTERPARTY VARCHAR2(75) := NULL; P_DEAL_CODE VARCHAR2(75) := NULL; P_PROJECT_CODE VARCHAR2(75) := NULL; P_FROM_VOID_DATE DATE := NULL; P_TO_VOID_DATE DATE := NULL; P_INCLUDE_VOID_FLAG VARCHAR2(10) := NULL; P_BUSINESS_LINE VARCHAR2(10) := NULL; P_CHECK_NUMBER NUMBER := NULL; P_CHECK_NUMBER_TO NUMBER := NULL; P_AR_TRX_NUM VARCHAR2(15) := NULL; P_RPT_OPTION VARCHAR2(25) := NULL; P_BANK_ACCOUNT NUMBER := NULL; --======================================================================+ -- Report Functions --======================================================================+ FUNCTION before_report RETURN BOOLEAN; END xxcb_apinv_inquiry_rpt_pkg; / |
— Package Body
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 |
CREATE OR REPLACE PACKAGE BODY APPS.xxcb_apinv_inquiry_rpt_pkg AS C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT; C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE; C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT; C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION; C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR; C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED; C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99; C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'plsql.xxcb_apinv_inquiry_rpt_pkg'; g_log_level NUMBER; g_log_enabled BOOLEAN; /********************************************************************************************* ** PRIVATE PROCEDURES AND FUNCTIONS *********************************************************************************************/ /* ------------------------------------------------------------------------------------------ ** Procedure: trace ** Description: --------------------------------------------------------------------------------------------*/ PROCEDURE trace(p_msg IN VARCHAR2, p_level IN NUMBER, p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE) IS BEGIN IF (p_msg IS NULL AND p_level >= g_log_level) THEN fnd_log.message(p_level, p_module); ELSIF p_level >= g_log_level THEN fnd_log.string(p_level, p_module, p_msg); END IF; EXCEPTION WHEN OTHERS THEN RAISE; END trace; /* ------------------------------------------------------------------------------------------ ** Procedure: print_logfile ** Description: Prints concurrent request logs --------------------------------------------------------------------------------------------*/ PROCEDURE print_logfile(p_msg IN VARCHAR2) IS BEGIN fnd_file.put_line(fnd_file.log, p_msg); EXCEPTION WHEN OTHERS THEN RAISE; END print_logfile; /********************************************************************************************* ** PUBLIC PROCEDURES AND FUNCTIONS *********************************************************************************************/ /* ------------------------------------------------------------------------------------------ ** Function: before_report ** Description: called before the report is run --------------------------------------------------------------------------------------------*/ FUNCTION before_report RETURN BOOLEAN IS l_log_module VARCHAR2(240); BEGIN IF g_log_enabled THEN l_log_module := C_DEFAULT_MODULE||'.before_report'; END IF; IF (C_LEVEL_PROCEDURE >= g_log_level) THEN trace(p_msg => 'BEGIN of before_report', p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); END IF; IF (C_LEVEL_STATEMENT >= g_log_level) THEN trace(p_msg => 'P_FROM_INV_DATE = ' || P_FROM_INV_DATE, p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); trace(p_msg => 'P_TO_INV_DATE = ' || P_TO_INV_DATE, p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); trace(p_msg => 'P_SUPPLIER_ID = ' || p_supplier_id, p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); trace(p_msg => 'P_FROM_GL_DATE = ' || P_FROM_GL_DATE, p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); trace(p_msg => 'P_TO_GL_DATE = ' || P_TO_GL_DATE, p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); trace(p_msg => 'P_FROM_DIST_GL_DATE = ' || P_FROM_DIST_GL_DATE, p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); trace(p_msg => 'P_TO_DIST_GL_DATE = ' || P_TO_DIST_GL_DATE, p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); trace(p_msg => 'P_INCLUDE_VOID_FLAG = ' || P_INCLUDE_VOID_FLAG, p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); trace(p_msg => 'P_FROM_VOID_DATE = ' || P_FROM_VOID_DATE, p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); trace(p_msg => 'P_TO_VOID_DATE = ' || P_TO_VOID_DATE, p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); trace(p_msg => 'P_GL_ACCOUNT = ' || p_gl_account, p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); trace(p_msg => 'P_CHECK_NUMBER = ' || p_check_number, p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); trace(p_msg => 'P_AR_TRX_NUM = ' || p_ar_trx_num, p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); trace(p_msg => 'P_COMPANY = ' || P_COMPANY, p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); trace(p_msg => 'P_COUNTERPARTY = ' || P_COUNTERPARTY, p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); trace(p_msg => 'P_DEAL_CODE = ' || P_DEAL_CODE, p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); trace(p_msg => 'P_PROJECT_CODE = ' || P_PROJECT_CODE, p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); trace(p_msg => 'P_RPT_OPTION = ' || p_rpt_option, p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); END IF; print_logfile('>> get_where_clause'); IF P_FROM_INV_DATE IS NOT NULL THEN gc_where_clause := ' AND TRUNC(aia.invoice_date) >= '''||P_FROM_INV_DATE ||''' '; END IF; IF P_TO_INV_DATE IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND TRUNC(aia.invoice_date) <= '''||P_TO_INV_DATE ||''' '; END IF; --Added by dliu@20150601 adding Parameters from IF P_FROM_PAYMENT_DATE IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND TRUNC(aipa.check_date) >= '''||P_FROM_PAYMENT_DATE ||''' '; END IF; IF P_TO_PAYMENT_DATE IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND TRUNC(aipa.check_date) <= '''||P_TO_PAYMENT_DATE ||''' '; END IF; if P_INCLUDE_VOID_FLAG = 'Only' then gc_where_clause := gc_where_clause ||' AND aipa.status_lookup_code (+) = '''||'VOIDED'||''' '; end if; if nvl(P_INCLUDE_VOID_FLAG,'No') = 'No' then gc_where_clause := gc_where_clause ||' AND aipa.status_lookup_code (+) != '''||'VOIDED'||''' AND nvl(aipa.reversal_flag(+), '''||'N'||''') != '''||'Y'||''' '; end if; IF P_FROM_VOID_DATE IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND TRUNC(aipa.void_date) >= '''||P_FROM_VOID_DATE ||''' '; END IF; IF P_TO_VOID_DATE IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND TRUNC(aipa.void_date) <= '''||P_TO_VOID_DATE ||''' '; END IF; IF P_BUSINESS_LINE IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND gcc.segment3 = '''||P_BUSINESS_LINE ||''' '; END IF; --Added by dliu@20150601 adding Parameters from IF p_supplier_id IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND asup.vendor_id = '||p_supplier_id ||' '; END IF; -- This is to match report with gl liability account activity report and for audit reason IF P_FROM_GL_DATE IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND TRUNC(aida.accounting_date) >= '''||P_FROM_GL_DATE ||''' '; END IF; IF P_TO_GL_DATE IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND TRUNC(aida.accounting_date) <= '''||P_TO_GL_DATE ||''' '; END IF; IF P_FROM_DIST_GL_DATE IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND TRUNC(aida.accounting_date) >= '''||P_FROM_DIST_GL_DATE ||''' '; END IF; IF P_TO_DIST_GL_DATE IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND TRUNC(aida.accounting_date) <= '''||P_TO_DIST_GL_DATE ||''' '; END IF; IF p_gl_account IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND gcc.segment2 = '''||p_gl_account ||''' '; END IF; IF p_check_number IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND aipa.check_number >= '||p_check_number ||' '; END IF; IF p_check_number_to IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND aipa.check_number <= '||p_check_number_to ||' '; END IF; IF p_ar_trx_num IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND aida.attribute4 = '''|| p_ar_trx_num ||''' '; END IF; IF P_COMPANY IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND gcc.segment1 = '''||substr(P_COMPANY,1,2) ||''' '; END IF; IF P_COUNTERPARTY IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND gcc.segment4 = '''||substr(P_COUNTERPARTY,1,3) ||''' '; END IF; IF P_DEAL_CODE IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND gcc.segment5 = '''||substr(P_DEAL_CODE,1,3) ||''' '; END IF; IF P_PROJECT_CODE IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND gcc.segment6 = '''||substr(P_PROJECT_CODE,1,2) ||''' '; END IF; IF P_BANK_ACCOUNT IS NOT NULL THEN gc_where_clause := gc_where_clause ||' AND aipa.CE_BANK_ACCT_USE_ID = '||P_BANK_ACCOUNT ||' '; END IF; IF p_rpt_option = 'NO_EXP_RPT' THEN gc_where_clause := gc_where_clause ||' AND aia.invoice_num NOT LIKE ''EXP%'' '; ELSIF p_rpt_option = 'EXP_RPT' THEN gc_where_clause := gc_where_clause ||' AND aia.invoice_num LIKE ''EXP%'' '; END IF; print_logfile('gc_where_clause: '|| gc_where_clause); print_logfile('<< get_where_clause'); IF (C_LEVEL_PROCEDURE >= g_log_level) THEN trace(p_msg => 'END of before_report ', p_level => C_LEVEL_PROCEDURE, p_module => l_log_module); END IF; RETURN TRUE; EXCEPTION WHEN OTHERS THEN RAISE; END before_report; /*********** Initialization routine **********/ /* ------------------------------------------------------------------------------------------ ** Following code is executed when the package body is referenced for the first time --------------------------------------------------------------------------------------------*/ BEGIN g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL; g_log_enabled := fnd_log.test(log_level => g_log_level, MODULE => C_DEFAULT_MODULE); IF NOT g_log_enabled THEN g_log_level := C_LEVEL_LOG_DISABLED; END IF; END xxcb_apinv_inquiry_rpt_pkg; / |
Regards Yogesh
Enterprise Resource Planning and Integrations BlogSpot 