ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D470A151A9E05362FB09050512 SELECT i.org_id org_id, decode(substr(:P_GROUP_FIELD,1,1),'T',fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),I.PAY_GROUP_LOOKUP_CODE) PayGroup, i.payment_currency_code InvoiceCurrency, substr(v.vendor_name,1,25)||' '||substr(vs.vendor_site_code,1,15)||' '||v.segment1 Vendor, i.doc_sequence_value Invoice_VoucherNum, i.invoice_num InvoiceNum,i.invoice_date, i.description , min(decode(id.match_status_flag,'A',:C_YES,:C_NO)) Approve, min(decode(id.accrual_posted_flag,'Y',:C_YES,:C_NO)) Posted_flag, min(nvl(id.period_name,'*')) invoice_period, i.invoice_amount, ps.hold_flag hold_flag2, nvl(i.exchange_rate,1) exchange_rate, i.exchange_rate exchange_rate2, ps.payment_cross_rate, nvl(i.base_amount,i.invoice_amount) invoice_base_amount, i.invoice_id, sum(nvl(id.amount,0)) dist_amount, sum(nvl(id.base_amount,nvl(id.amount,0))) dist_base_amount, i.invoice_type_lookup_code, i.cancelled_date, i.cancelled_amount, JG_JGZZSRCR_XMLP_PKG.C_INV_PAY_BASEFORMULA(i.INVOICE_ID,:C_INV_PAY_BASE0) C_INV_PAY_BASE, JG_JGZZSRCR_XMLP_PKG.CF_ORGANIZATIONFORMULA(i.ORG_ID) CF_ORGANIZATION, JG_JGZZSRCR_XMLP_PKG.CF_FLEXPROMPTFORMULA CF_FLEXPROMPT, JG_JGZZSRCR_XMLP_PKG.C_BAL_FACTORFORMULA(sum(nvl(id.base_amount,nvl(id.amount,0))),i.INVOICE_ID) C_BAL_FACTOR, JG_JGZZSRCR_XMLP_PKG.C_INV_DISCOUNT_AVAILABLEFORMUL(:C_INV_DISC_AVAIL0,JG_JGZZSRCR_XMLP_PKG.C_BAL_FACTORFORMULA(sum(nvl(id.base_amount,nvl(id.amount,0))),i.INVOICE_ID)) C_INV_DISCOUNT_AVAILABLE, &C_INV_disc_taken_base0 C_INV_discount_taken_base, &C_INV_gainloss0 C_INV_gainloss, JG_JGZZSRCR_XMLP_PKG.C_INV_OPEN_AMOUNTFORMULA(JG_JGZZSRCR_XMLP_PKG.C_INV_GROSS_AMOUNTFORMULA(i.CANCELLED_DATE ,:C_INV_GROSS_AMOUNT1,i.CANCELLED_AMOUNT ,:C_INV_GROSS_AMOUNT0),:C_INV_PAY_AMOUNT0 ,:C_INV_DISCOUNT_TAKEN0,:C_INV_PAY_AMOUNT,:C_INV_DISCOUNT_TAKEN) C_INV_OPEN_AMOUNT, JG_JGZZSRCR_XMLP_PKG.C_INV_OPEN_BASEFORMULA(:C_INV_OPEN_AMOUNT ,nvl(i.exchange_rate,1), ps.payment_cross_rate) C_INV_OPEN_BASE, --JG_JGZZSRCR_XMLP_PKG.CF_INV_CHECK_GL_DATEFORMULA(:C_INV_CHECK_GL_DATE) CF_INV_check_gl_date, JG_JGZZSRCR_XMLP_PKG.C_INV_GROSS_AMOUNTFORMULA(i.CANCELLED_DATE ,:C_INV_GROSS_AMOUNT1,i.CANCELLED_AMOUNT ,:C_INV_GROSS_AMOUNT0) C_INV_GROSS_AMOUNT, JG_JGZZSRCR_XMLP_PKG.C_INV_GROSS_BASEFORMULA(i.CANCELLED_DATE,:C_INV_GROSS_BASE0 ,i.CANCELLED_AMOUNT ,nvl(i.exchange_rate,1)) C_INV_GROSS_BASE, JG_JGZZSRCR_XMLP_PKG.C_APPROVE_FLAGFORMULA(i.INVOICE_ID) C_APPROVE_FLAG, --JG_JGZZSRCR_XMLP_PKG.CF_C_INV_DISCOUNT_DATEFORMULA(:C_INV_DISCOUNT_DATE) CF_C_INV_DISCOUNT_DATE, --JG_JGZZSRCR_XMLP_PKG.C_INV_DISCOUNT_TAKEN_BASEFORMU(:C_INV_DISC_TAKEN_BASE0) C_INV_DISCOUNT_TAKEN_BASE, --JG_JGZZSRCR_XMLP_PKG.C_INV_GAINLOSSFORMULA(:C_INV_GAINLOSS0) C_INV_GAINLOSS, JG_JGZZSRCR_XMLP_PKG.CP_INV_DUE_DATE_FMT_P CP_INV_due_date_fmt, JG_JGZZSRCR_XMLP_PKG.CP_INVPP_OPEN_BASE_P CP_INVPP_OPEN_BASE, JG_JGZZSRCR_XMLP_PKG.CP_invpp_open_amount_p CP_invpp_open_amount, JG_JGZZSRCR_XMLP_PKG.CP_invpp_gl_date_p CP_invpp_gl_date, 'X' FROM gl_code_combinations gcc, ap_invoices_all i, ap_invoice_distributions_all id, po_vendors v, po_vendor_sites_all vs, ap_payment_schedules_all ps, ap_accounting_events_all aae1, ap_ae_headers_all ach1 WHERE gcc.code_combination_id = nvl(id.accts_pay_code_combination_id,i.accts_pay_code_combination_id) AND i.vendor_id = v.vendor_id and i.vendor_site_id = vs.vendor_site_id and v.vendor_id = vs.vendor_id AND i.invoice_id = id.invoice_id(+) AND ps.invoice_id = i.invoice_id AND i.invoice_id = aae1.source_id(+) AND aae1.source_table(+) = 'AP_INVOICES' AND aae1.accounting_event_id = ach1.accounting_event_id(+) AND i.cancelled_date is null &SQL_PAYMENT_GROUP_WH &SQL_INVOICE_CURRENCY &SQL_VENDOR_ID &SQL_DISTRIBUTIONS &SQL_ONLY_PAST_FLAG &SQL_PAYMENTS1 &P_ORG_WHERE_I &P_ORG_WHERE_ID &P_ORG_WHERE_VS &P_ORG_WHERE_PS &P_ORG_WHERE_AAE1 &P_ORG_WHERE_ACH1 GROUP BY i.org_id, decode(substr(:P_GROUP_FIELD,1,1),'T',fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),I.PAY_GROUP_LOOKUP_CODE), i.payment_currency_code, substr(v.vendor_name,1,25)||' '||substr(vs.vendor_site_code,1,15)||' '||v.segment1, i.doc_sequence_value, i.invoice_num,i.invoice_date, i.description, nvl(i.exchange_rate,1), i.exchange_rate, ps.payment_cross_rate, i.invoice_amount, nvl(i.base_amount,i.invoice_amount), i.invoice_id, i.invoice_type_lookup_code, ps.hold_flag, i.cancelled_date, i.cancelled_amount ORDER BY 1 ASC,2 ASC,3 ASC,4 ASC Y JG JGZZSRCR_XML US JG Regional Cash Requirement Report- Not Supported: Reserved For Future Use Imported from BI Publisher Description: Regional Cash Requirement Report Application: Regional Localizations Source: Regional Cash Requirement Report (XML) - Not Supported: Reserved For Future Use Short Name: JGZZSRCR_XML DB package: JG_JGZZSRCR_XMLP_PKG BI Publisher &c_inv_disc_taken_base0 &c_inv_gainloss0 &p_org_where_aae1 &p_org_where_ach1 &p_org_where_i &p_org_where_id &p_org_where_ps &p_org_where_vs &sql_distributions &sql_invoice_currency &sql_only_past_flag &sql_payment_group_wh &sql_payments1 &sql_vendor_id :c_all :c_approval_status :c_coai :c_flexdata :c_funct_curr :c_hold_flag :c_inv_disc_avail0 :c_inv_discount_taken :c_inv_discount_taken0 :c_inv_gross_amount0 :c_inv_gross_amount1 :c_inv_gross_base0 :c_inv_open_amount :c_inv_pay_amount :c_inv_pay_amount0 :c_inv_pay_base0 :c_no :c_precision :c_sob :c_summary_level :c_title :c_yes :cp_inv_due_date_fmt :cp_invpp_gl_date :cp_invpp_open_amount :cp_invpp_open_base :cp_partial_ledger_msg :p_check_ledger_in_sp :p_check_voucher :p_checkrun_name :p_conc_request_id :p_confirmed :p_cut_date :p_cut_date_v :p_debug_switch :p_group_field :p_inv_posted_flag :p_invoice_currency :p_invoice_period :p_match_status_flag :p_match_status_flag_v :p_only_open_invoices :p_only_open_invoices_v :p_only_paid_invoices :p_only_paid_invoices_v :p_only_past_flag :p_org_where_aae :p_org_where_aae1 :p_org_where_ach :p_org_where_ach1 :p_org_where_c :p_org_where_i :p_org_where_id :p_org_where_ip :p_org_where_ps :p_org_where_sp :p_org_where_vs :p_partial_ledger_msg :p_pay_posted_flag :p_payment_group :p_payment_period :p_reporting_context :p_reporting_entity_id :p_reporting_entity_name :p_reporting_level :p_reporting_level_name :p_summarize_all_ou :p_summary_level :p_vendor_id :sql_distributions :sql_invoice_currency :sql_only_past_flag :sql_only_past_flag2 :sql_payment_group :sql_payment_group_wh :sql_payments1 :sql_payments2 :sql_payments3 :sql_vendor_id 1 -60 :p_group_field LOV Oracle JGZZ_AP_INVOICE_SORT_OPTION Y select lookup_code id, meaning value, description description from fnd_lookups where lookup_type='JGZZ_AP_INVOICE_GROUP' order by value,description select meaning from fnd_lookups where lookup_type = 'JGZZ_AP_INVOICE_GROUP' and lookup_code = 'M' Y US Invoice Sort Option = By Pay Group 2 -50 :p_coai Char select chart_of_accounts_id from gl_sets_of_books sb, ap_system_parameters sp where sb.set_of_books_id=sp.set_of_books_id Y US chart_of_accounts_id 3 -40 :p_only_past_flag LOV Oracle AP_SRS_YES_NO_MAND Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='YES_NO' order by value,description select meaning from fnd_lookups where lookup_type = 'YES_NO' and lookup_code = 'Y' Y US Only Past Due Invoices =Yes 4 -30 :p_only_open_invoices LOV Oracle AP_SRS_YES_NO_MAND Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='YES_NO' order by value,description select meaning from fnd_lookups where lookup_type = 'YES_NO' and lookup_code = 'Y' Y US Open Invoices =Yes 5 -20 :p_only_paid_invoices LOV Oracle AP_SRS_YES_NO_MAND Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='YES_NO' order by value,description select meaning from fnd_lookups where lookup_type = 'YES_NO' and lookup_code = 'N' Y US Paid Invoices =No 6 10 :p_reporting_level LOV Oracle FND_MO_REPORTING_LEVEL Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='FND_MO_REPORTING_LEVEL' and lookup_code in (1000,3000) order by value,description select meaning from fnd_product_groups p, fnd_lookups l where l.lookup_type = 'FND_MO_REPORTING_LEVEL' and l.lookup_code = decode (NVL(p.multi_org_flag, 'N') , 'N' , '1000' , '3000') US Reporting Level Reporting Level 7 20 :p_reporting_entity_id LOV Oracle FND_MO_REPORTING_ENTITY Y select entity_id id, entity_name value, null description from fnd_mo_reporting_entities_v where reporting_level=:$flex$.fnd_mo_reporting_level order by value,description SELECT DECODE(:$flex$.reporting_level,'3000',o.name,'2000',o.name,'1000',g.name),g.name FROM gl_sets_of_books g,hr_organization_units o,hr_organization_information o2,hr_organization_information o3,fnd_product_groups pg WHERE o.organization_id = DECODE(:$flex$.reporting_level,'2000',TO_NUMBER(o3.org_information2),o3.organization_id) AND o3.org_information3 = g.set_of_books_id AND o2.organization_id = o3.organization_id AND o2.org_information_context || '' = 'CLASS' AND o3.org_information_context = 'Operating Unit Information' AND o2.org_information1 = 'OPERATING_UNIT' AND o2.org_information2 ='Y' AND o2.organization_id = fnd_profile.value_wnps('ORG_ID') AND NVL(pg.multi_org_flag,'N') = 'Y' UNION SELECT sob.name, sob.name FROM gl_sets_of_books sob,fnd_product_groups pg WHERE set_of_books_id = fnd_profile.value_wnps('GL_SET_OF_BKS_ID') AND NVL(pg.multi_org_flag,'N') = 'N' US Reporting Context Reporting Context 8 30 :p_summarize_all_ou LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description select meaning from fnd_lookups where lookup_type = 'YES_NO' and lookup_code = 'N' US Summarize all OU Summarize Output for all Operating Units 9 40 :p_summary_level LOV Oracle JGZZ_AP_REPORT_FORMAT Y select lookup_code id, meaning value, description description from fnd_lookups where lookup_type='JGZZ_AP_SUMMARY_LEVEL' order by value,description select meaning from fnd_lookups where lookup_type = 'JGZZ_AP_SUMMARY_LEVEL' and lookup_code = '3' US Report Format 10 50 :p_payment_group LOV Oracle AP_SRS_PAYGROUP Y select lookup_code id, lookup_code value, null description from po_lookup_codes where nvl(inactive_date,sysdate+1)>sysdate and lookup_type='PAY GROUP' order by value,description US Pay Group 11 60 :p_invoice_currency LOV Oracle AP_SRS_CURRENCY Y select currency_code id, currency_code value, description description from fnd_currencies_vl where currency_flag='Y' and enabled_flag in ('Y','N') order by currency_code US Currency 12 70 :p_vendor_id LOV Oracle AP_SRS_VENDOR_NAME Y select vendor_id id, vendor_name value, null description from po_vendors order by value,description US Supplier Name 13 80 :p_cut_date Date sysdate Y US As of Date 14 90 :p_match_status_flag LOV Oracle JGZZ_INVOICE_VALIDATION_STATUS Y select lookup_code id, meaning value, description description from fnd_lookups where lookup_type='JGZZ_INVOICE_VALIDATION_STATUS' and lookup_code!='*' order by value,description select meaning from fnd_lookups where lookup_type = 'JGZZ_INVOICE_VALIDATION_STATUS' and lookup_code = 'V' US Validation Status