JG Regional Cash Requirement - draft

Description
Categories: BI Publisher
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
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
Parameter Name SQL text Validation
Paid Invoices
 
LOV Oracle
Open Invoices
 
LOV Oracle
Only Past Due Invoices
 
LOV Oracle
chart_of_accounts_id
 
Invoice Sort Option
 
LOV Oracle
Validation Status
 
LOV Oracle
As of Date
 
Date
Supplier Name
 
LOV Oracle
Currency
 
LOV Oracle
Pay Group
 
LOV Oracle
Report Format
 
LOV Oracle
Summarize all OU
 
LOV Oracle
Reporting Context
 
LOV Oracle
Reporting Level
 
LOV Oracle