JE Finnish Payables Account by Detail- Not Supported: Reserved For Future Use

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Finnish Payables Account by Detail
Application: European Localizations
Source: Finnish Payables Account by Detail (XML) - Not Supported: Reserved For Future Use
Short Name: JEFILPIP_XML
DB package: JE_JEFILPIP_XMLP_PKG
SELECT	&C_FLEXDATA PayGroup,
	        i.invoice_currency_code InvoiceCurrency,
            i.payment_currency_code PaymentCurrency,
         	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 payment_cross_rate1,
          	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,
				   nvl(i.cancelled_amount,0) cancelled_amount1,
                   ps.invoice_id,
                   ps.payment_num,
                   ps.gross_amount,
                   ps.hold_flag,
                   DECODE(sign(nvl(ps.discount_date, sysdate-1) - sysdate),-1, ps.due_date,0, ps.discount_date, ps.discount_date)   discount_date,
                   round(nvl(ps.inv_curr_gross_amount,ps.gross_amount) * (nvl(i.exchange_rate,1)), :C_PRECISION) gross_base,
                   ps.due_date,
                   nvl(ps.discount_amount_available*(nvl(i.exchange_rate,1)), 0) discount_available,
                   nvl(ps.inv_curr_gross_amount,ps.gross_amount) inv_curr_gross_amount,
                   nvl(ps.inv_curr_gross_amount,ps.gross_amount) inv_curr_ga2,
                   ip.invoice_id,
                   ip.payment_num,
                   c.check_id,
                  'N' fdp_flag, 
	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') C_FLEXFIELD, 
	round(nvl(i.cancelled_amount,0) * nvl(i.exchange_rate,1),:C_PRECISION) C_VALUE,
	case when (nvl(i.cancelled_date,:P_CUT_DATE) > :P_CUT_DATE) then 'Y' else 'N' end C_INV_FLG,	
	JE_JEFILPIP_XMLP_PKG.c_bal_factorformula(sum ( nvl ( id.base_amount , nvl ( id.amount , 0 ) ) ), i.invoice_id) C_bal_factor,
--	JE_JEFILPIP_XMLP_PKG.c_inv_discount_availableformul(:C_INV_disc_avail0, :C_bal_factor) C_INV_discount_available, 
	&C_INV_disc_taken_base0 C_INV_discount_taken_base, 
	&C_INV_gainloss0 C_INV_gainloss, 
--	JE_JEFILPIP_XMLP_PKG.c_inv_pay_baseformula(i.invoice_id, :C_INV_pay_base0) C_INV_pay_base, 
--	JE_JEFILPIP_XMLP_PKG.c_inv_open_amountformula(:C_INV_gross_amount, :C_INV_pay_amount0, :C_INV_discount_taken0) C_INV_open_amount, 
--	JE_JEFILPIP_XMLP_PKG.c_inv_open_baseformula(:C_INV_open_amount, nvl ( i.exchange_rate , 1 )) C_INV_open_base, 
--	JE_JEFILPIP_XMLP_PKG.cf_inv_check_gl_dateformula(:C_INV_check_gl_date) CF_INV_check_gl_date, 
--	JE_JEFILPIP_XMLP_PKG.c_inv_gross_amountformula(i.cancelled_date, :C_INV_gross_amount1, i.cancelled_amount) C_INV_gross_amount, 
--	JE_JEFILPIP_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, 
	JE_JEFILPIP_XMLP_PKG.c_approve_flagformula(i.invoice_id) C_approve_flag, 
--	JE_JEFILPIP_XMLP_PKG.cf_c_inv_discount_dateformula(:C_INV_discount_date) CF_C_INV_discount_date,
	JE_JEFILPIP_XMLP_PKG.CP_inv_pay_amount_p CP_inv_pay_amount,
	JE_JEFILPIP_XMLP_PKG.CP_invpp_open_amount_p CP_invpp_open_amount,
	JE_JEFILPIP_XMLP_PKG.CP_invpp_gl_date_p CP_invpp_gl_date
--	JE_JEFILPIP_XMLP_PKG.c_sch_open_amountformula(ps.gross_amount, :C_SCH_pay_amount, :C_SCH_discount_taken) C_SCH_open_amount, 
--	JE_JEFILPIP_XMLP_PKG.c_sch_open_baseformula(:C_SCH_open_amount, nvl ( i.exchange_rate , 1 )) C_SCH_open_base
FROM	gl_code_combinations gcc, ap_invoices i, ap_invoice_distributions id,  
	po_vendors v, po_vendor_sites vs, ap_payment_schedules ps,
                xla_transaction_entities aae1, xla_ae_headers ach1,
                   ap_invoice_payments ip,
                   ap_checks c
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.cancelled_date is null
                  or
                 (:P_CUT_DATE >= id.accounting_date and
                 :P_CUT_DATE < (select accounting_date from ap_invoice_distributions id1
                                            where id1.invoice_id=i.invoice_id and id1.cancellation_flag='Y'
                                             and rownum=1) ) )
and             i.invoice_id = aae1.source_id_int_1
and             aae1.ENTITY_CODE = 'AP_INVOICES'            
and             ach1.ENTITY_ID = aae1.ENTITY_ID
and             ps.invoice_id = ip.invoice_id(+)
and             ps.payment_num = ip.payment_num(+) 
and             ip.check_id=c.check_id(+) 
and not (c.void_date is not null and nvl(ps.amount_remaining,0)= 0)
and &SQL_PAYMENT_GROUP
&SQL_INVOICE_CURRENCY
&SQL_VENDOR_ID
&SQL_DISTRIBUTIONS
&SQL_PAYMENTS3
GROUP BY &C_FLEXDATA,
	i.invoice_currency_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,
                   ps.invoice_id,
                   ps.payment_num,
                   ps.gross_amount,
                   ps.hold_flag,	  
                   DECODE(sign(nvl(ps.discount_date, sysdate-1) - sysdate),
                                                                -1, ps.due_date,
                                                                  0, ps.discount_date,
                                                                      ps.discount_date),
                   round(nvl(ps.inv_curr_gross_amount,ps.gross_amount) * (nvl(i.exchange_rate,1)), :C_PRECISION),
                   ps.due_date,
                   nvl(ps.discount_amount_available*(nvl(i.exchange_rate,1)), 0),
                   nvl(ps.inv_curr_gross_amount,ps.gross_amount),
	nvl(ps.inv_curr_gross_amount,ps.gross_amount),
                   ps.inv_curr_gross_amount,
                   ip.invoice_id,
                   ip.payment_num,
                   c.check_id,
                   'N',
				   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'),
				   &C_INV_disc_taken_base0, 
                   &C_INV_gainloss0 ,
				    nvl(i.cancelled_date,:P_CUT_DATE),
					 nvl(i.cancelled_amount,0)
UNION
SELECT      &C_FLEXDATA PayGroup,
                   i.invoice_currency_code InvoiceCurrency,
                   i.payment_currency_code PaymentCurrency,
                   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 payment_cross_rate1,
                   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,
                   0 cancelled_amount,
				   0 cancelled_amount1,
                   ps.invoice_id,
                   ps.payment_num,
                   ps.gross_amount,
                   ps.hold_flag,
                   DECODE(sign(nvl(ps.discount_date, sysdate-1) - sysdate),-1, ps.due_date, 0, ps.discount_date, ps.discount_date)   discount_date,
                   0 gross_base,
                   ps.due_date,
                  nvl(ps.discount_amount_available*(nvl(i.exchange_rate,1)), 0) discount_available,
                   0 inv_curr_gross_amount,
                   nvl(ps.inv_curr_gross_amount,ps.gross_amount) inv_curr_ga2,
                   ip.invoice_id,
                   ip.payment_num,
                   c.check_id,
                  'Y' fdp_flag, 
	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') C_FLEXFIELD, 
	round(0,:C_PRECISION) C_VALUE, 
	case when (nvl(i.cancelled_date,:P_CUT_DATE) > :P_CUT_DATE) then 'Y' else 'N' end C_INV_FLG,
	JE_JEFILPIP_XMLP_PKG.c_bal_factorformula(sum ( nvl ( id.base_amount , nvl ( id.amount , 0 ) ) ), i.invoice_id) C_bal_factor, 
--	JE_JEFILPIP_XMLP_PKG.c_inv_discount_availableformul(:C_INV_disc_avail0, :C_bal_factor) C_INV_discount_available, 
	&C_INV_disc_taken_base0 C_INV_discount_taken_base, 
	&C_INV_gainloss0 C_INV_gainloss, 
--	JE_JEFILPIP_XMLP_PKG.c_inv_pay_baseformula(i.invoice_id, :C_INV_pay_base0) C_INV_pay_base, 
--	JE_JEFILPIP_XMLP_PKG.c_inv_open_amountformula(:C_INV_gross_amount, :C_INV_pay_amount0, :C_INV_discount_taken0) C_INV_open_amount, 
--	JE_JEFILPIP_XMLP_PKG.c_inv_open_baseformula(:C_INV_open_amount, nvl ( i.exchange_rate , 1 )) C_INV_open_base, 
--	JE_JEFILPIP_XMLP_PKG.cf_inv_check_gl_dateformula(:C_INV_check_gl_date) CF_INV_check_gl_date, 
--	JE_JEFILPIP_XMLP_PKG.c_inv_gross_amountformula(i.cancelled_date, :C_INV_gross_amount1, i.cancelled_amount) C_INV_gross_amount,
--	JE_JEFILPIP_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, 
	JE_JEFILPIP_XMLP_PKG.c_approve_flagformula(i.invoice_id) C_approve_flag, 
--	JE_JEFILPIP_XMLP_PKG.cf_c_inv_discount_dateformula(:C_INV_discount_date) CF_C_INV_discount_date,
	JE_JEFILPIP_XMLP_PKG.CP_inv_pay_amount_p CP_inv_pay_amount,
	JE_JEFILPIP_XMLP_PKG.CP_invpp_open_amount_p CP_invpp_open_amount,
	JE_JEFILPIP_XMLP_PKG.CP_invpp_gl_date_p CP_invpp_gl_date 
--	JE_JEFILPIP_XMLP_PKG.c_sch_open_amountformula(ps.gross_amount, :C_SCH_pay_amount, :C_SCH_discount_taken) C_SCH_open_amount, 
--	JE_JEFILPIP_XMLP_PKG.c_sch_open_baseformula(:C_SCH_open_amount, nvl ( i.exchange_rate , 1 )) C_SCH_open_base
FROM        gl_code_combinations gcc, ap_invoices i, ap_invoice_distributions id,
                  po_vendors v, po_vendor_sites vs, ap_payment_schedules ps,
                 xla_transaction_entities aae1, xla_ae_headers ach1,
                  ap_invoice_payments ip,
                  ap_checks c
WHERE      gcc.code_combination_id = ip.future_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.cancelled_date is null
                  or
                 (:P_CUT_DATE >= id.accounting_date and
                 :P_CUT_DATE < (select accounting_date from ap_invoice_distributions id1
                                            where id1.invoice_id=i.invoice_id and id1.cancellation_flag='Y'
                                             and rownum=1) ) )
and             i.invoice_id = aae1.source_id_int_1
and             aae1.ENTITY_CODE = 'AP_INVOICES'            
and             ach1.ENTITY_ID = aae1.ENTITY_ID
and             ps.invoice_id = ip.invoice_id(+)
and             ps.payment_num = ip.payment_num(+)
and             ip.check_id=c.check_id(+) 
and             c.future_pay_due_date is not null
and             c.status_lookup_code = 'ISSUED'
and             exists (select 'X'
                                from ap_system_parameters
                                where nvl(when_to_account_pmt, 'X') <> 'CLEARING ONLY')
and             nvl(:P_CONFIRMED, 'X') <> 'Y'
and not (c.void_date is not null and nvl(ps.amount_remaining,0)= 0)
and &SQL_PAYMENT_GROUP
&SQL_INVOICE_CURRENCY
&SQL_VENDOR_ID
&SQL_DISTRIBUTIONS
&SQL_PAYMENTS_FDP
         and exists(
                            SELECT  'X' 
                            FROM  ap_invoice_payments ip,ap_checks c, 
    xla_transaction_entities aae, xla_ae_headers ach, xla_events xev
                            WHERE ip.check_id=c.check_id
                            AND     ip.invoice_id = ps.invoice_id
                            AND     c.void_date is null
                           and           c.check_id = aae.source_id_int_1(+)
                           and           aae.ENTITY_CODE = 'AP_PAYMENTS'            
                           and           aae.ENTITY_ID = ach.ENTITY_ID
                           and           xev.ENTITY_ID = aae.ENTITY_ID
                           and           xev.EVENT_TYPE_CODE in ('PAYMENT CREATED', 'PAYMENT CLEARED', 'REFUND RECORDED')
                            &SQL_PAYMENTS2)
GROUP BY &C_FLEXDATA,
                 i.invoice_currency_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, 
                  0,
                  ps.invoice_id,
                  ps.payment_num,
                  ps.gross_amount,
                  ps.hold_flag,
                  DECODE(sign(nvl(ps.discount_date, sysdate-1) - sysdate),
                                                           -1, ps.due_date,
                                                             0, ps.discount_date,
                                                                 ps.discount_date),
                  0,
                  ps.due_date,
                  nvl(ps.discount_amount_available*(nvl(i.exchange_rate,1)), 0),
                  0,
	nvl(ps.inv_curr_gross_amount,ps.gross_amount),
                  ip.invoice_id,
                  ip.payment_num,
                  c.check_id,
                  'Y',
				  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'),
				  &C_INV_disc_taken_base0,
				  &C_INV_gainloss0,
				  nvl(i.cancelled_date,:P_CUT_DATE),
				 nvl(i.cancelled_amount,0)