select aa.set_of_books_id,
aa.invoice_amount ,
aa.invoice_id ,
--aa.invoice_date,
bb.accounting_date,
aa.panno ,
aa.sertax ,
aa.vendor_name ,
aa.vendor_number ,
--aa.panno,
--aa.sertax,
aa.vendor_type ,
aa.liabulity_account ,
aa.invoice_num ,
aa.vocher_number ,
aa.invoice_date ,
aa.gl_date ,
aa.invoice_type_lookup_code ,
aa.payment_currency_code ,
aa.segment2 ,
aa.currency_code ,
(nvl(aa.entered_dr,0) +nvl(bb.entered_dr,0)) entered_dr ,
(nvl(aa.entered_cr,0) +nvl(bb.entered_cr,0)) entered_cr ,
(nvl(aa.accounted_dr,0) +nvl(bb.accounted_dr,0)) accounted_dr ,
(nvl(aa.accounted_cr,0) +nvl(bb.accounted_cr,0)) accounted_cr ,
((nvl(aa.accounted_dr,0)+nvl(bb.accounted_dr,0))-(nvl(aa.accounted_cr,0)+nvl(bb.accounted_cr,0))) remining_amount,
(select nvl(sum(aip.amount),0)
from ap_invoice_lines_all aip
where aip.invoice_id =aa.invoice_id
and aip.line_type_lookup_code='awt'
) tax_amt ,
aa.exchange_rate ,
aa.terms_date
from
(select cc.vendor_name,
cc.vendor_number ,
cc.panno ,
cc.sertax ,
cc.vendor_type ,
cc.liabulity_account,
cc.invoice_num ,
--cc.invoice_date,
cc.vocher_number ,
cc.invoice_id ,
cc.invoice_date ,
cc.invoice_amount ,
cc.gl_date ,
cc.invoice_type_lookup_code ,
cc.payment_currency_code ,
cc.reference_2 ,
cc.v_source ,
cc.set_of_books_id ,
cc.segment ,
cc.segment2 ,
cc.currency_code ,
sum(nvl(cc.entered_dr,0)) entered_dr ,
sum(nvl(cc.entered_cr,0)) entered_cr ,
sum(nvl(cc.accounted_dr,0)) accounted_dr,
sum(nvl(cc.accounted_cr,0)) accounted_cr,
cc.description ,
null accounting_date ,
cc.exchange_rate ,
cc.terms_date
from
(select aps.vendor_name ,
aps.segment1 vendor_number ,
aps.global_attribute20 panno ,
aps.global_attribute18 sertax ,
aps.vendor_type_lookup_code vendor_type ,
cc.concatenated_segments liabulity_account ,
aia.invoice_num ,
nvl(aia.voucher_num,aia.doc_sequence_value) vocher_number,
aia.invoice_id ,
aia.invoice_date ,
aia.invoice_amount ,
aia.gl_date ,
aia.invoice_type_lookup_code ,
aia.payment_currency_code ,
gjl.reference_2 ,
gjh.je_source v_source ,
gjh.ledger_id set_of_books_id ,
cc.concatenated_segments segment ,
cc.concatenated_segments segment2 ,
aia.invoice_currency_code currency_code ,
sum(nvl(xdl.unrounded_entered_dr,0)) entered_dr ,
sum(nvl(xdl.unrounded_entered_cr,0)) entered_cr ,
sum(nvl(xdl.unrounded_accounted_dr,0)) accounted_dr ,
sum(nvl(xdl.unrounded_accounted_cr,0)) accounted_cr ,
aia.description ,
aia.exchange_rate ,
aia.terms_date
-- ,cc.segment1 company
from ap_invoices_all aia ,
ap_invoice_lines_all aila ,
ap_invoice_distributions_all aida,
ap_prepay_app_dists apad ,
xla_distribution_links xdl ,
xla_ae_lines xal ,
xla_ae_headers xah ,
gl_import_references gir ,
gl_je_lines gjl ,
gl_je_headers gjh ,
gl_code_combinations_kfv cc ,
gl_period_statuses gps ,
apps.ap_suppliers aps
where aia.invoice_id=aila.invoice_id
and aila.invoice_id =aida.invoice_id
and aila.line_number =aida.invoice_line_number
--and aia.invoice_id=88734
and aida.line_type_lookup_code ='prepay'
and aida.invoice_distribution_id = apad.prepay_app_distribution_id
and apad.amount <>0
and apad.prepay_app_dist_id =xdl.source_distribution_id_num_1
and xdl.applied_to_source_id_num_1 in (aila.prepay_invoice_id,aila.invoice_id)
and xdl.ae_header_id =xal.ae_header_id
and xdl.ae_line_num =xal.ae_line_num
and xal.ae_header_id =xah.ae_header_id
and xal.gl_sl_link_id = gir.gl_sl_link_id
and xal.gl_sl_link_table = gir.gl_sl_link_table
and cc.code_combination_id =xal.code_combination_id
and cc.code_combination_id =gjl.code_combination_id
and aia.vendor_id =aps.vendor_id
and gir.je_header_id =gjl.je_header_id
and gir.je_line_num =gjl.je_line_num
and gjl.je_header_id =gjh.je_header_id
and gjh.ledger_id =gps.ledger_id
and 101 =gps.application_id
and gps.set_of_books_id =gjh.ledger_id
and gjh.je_source ='payables'
and gjh.ledger_id not in ( 1014,1015,1016 )
and gjh.period_name = gps.period_name
--and cc.segment5 in (:p_min_flex,:p_max_flex)
and ( nvl(xal.accounted_cr,0)<>0
or nvl(xal.accounted_dr,0) <>0)
and trunc(xah.accounting_date) between to_date('01-sep-1099') and to_date(:p_date)
--and aia.org_id=:p_org
and xah.je_category_name='purchase invoices'
-- and ( nvl (gps.effective_period_num, 0) >=:cf_period_from
-- and (nvl (gps.effective_period_num, 0) <=:cf_period_to)
-- )
and ( nvl (cc.segment5, 0) >= :p_account_from
and (nvl (cc.segment5, 0) <= :p_account_to) )
and ( nvl (cc.segment1, 0) >=:p_company_from
and (nvl (cc.segment1, 0) <= :p_company_to) )
and aia.payment_currency_code =decode(:p_currency_code,'inr','inr', 'all',aia.payment_currency_code, 'non inr',decode(aia.payment_currency_code,'inr','###',aia.payment_currency_code))
--and aia.invoice_num='us pro'
--and aia.doc_sequence_value=31207105
group by
--gps.period_name,
gjl.reference_2 ,
aia.invoice_num ,
aia.gl_date ,
aps.vendor_name ,
aps.segment1 ,
gjh.je_source ,
gjh.ledger_id ,
cc.concatenated_segments ,
--cc.concatenated_segments ,
aia.payment_currency_code ,
aia.invoice_currency_code ,
--gir.je_header_id,
--gir.je_line_num,
--cc.code_combination_id,
aia.doc_sequence_value ,
aia.voucher_num ,
aia.gl_date ,
aia.description ,
aps.global_attribute20 ,
aps.global_attribute18 ,
aps.vendor_type_lookup_code,
aia.invoice_date ,
aia.invoice_amount ,
aia.invoice_id ,
aia.exchange_rate ,
aia.terms_date ,
--aia.gl_date,
aia.invoice_type_lookup_code
union all
select aps.vendor_name ,
aps.segment1 vendor_number ,
aps.global_attribute20 panno ,
aps.global_attribute18 sertax ,
aps.vendor_type_lookup_code vendor_type ,
cc.concatenated_segments liabulity_account ,
aia.invoice_num ,
nvl(aia.voucher_num,aia.doc_sequence_value) vocher_number,
aia.invoice_id ,
-- aia.doc_sequence_value vocher_number,
aia.invoice_date ,
aia.invoice_amount ,
aia.gl_date ,
aia.invoice_type_lookup_code,
aia.payment_currency_code ,
--gps.period_name,
gjl.reference_2,
--(select distinct pha.segment1 from ap_invoice_lines_all ailla, po_headers_all pha where ailla.invoice_id=aia.invoice_id and ailla.po_header_id=pha.po_header_id) po_order_number,
--
--(select distinct rsh.receipt_num
--from rcv_shipment_headers rsh, rcv_shipment_lines rsl,rcv_transactions rt, ap_invoice_lines_all aila
--where rsh.shipment_header_id=rsl.shipment_header_id
--and rsl.shipment_header_id=rt.shipment_header_id
--and rsl.shipment_line_id=rt.shipment_line_id
--and rt.transaction_id=(select distinct rcv_transaction_id from ap_invoice_lines_all aila where aila.invoice_id=aia.invoice_id and rownum=1 and rcv_transaction_id is not null) ) "receipt number",
--(select distinct rt.transaction_date
--from rcv_shipment_headers rsh, rcv_shipment_lines rsl,rcv_transactions rt, ap_invoice_lines_all aila
--where rsh.shipment_header_id=rsl.shipment_header_id
--and rsl.shipment_header_id=rt.shipment_header_id
--and rsl.shipment_line_id=rt.shipment_line_id
--and rt.transaction_id=(select distinct rcv_transaction_id from ap_invoice_lines_all aila where aila.invoice_id=aia.invoice_id and rownum=1 and rcv_transaction_id is not null)) "receipt date",
-- aia.invoice_num "invoice number",
-- aia.gl_date "invoice date",
gjh.je_source v_source ,
gjh.ledger_id set_of_books_id ,
cc.concatenated_segments segment ,
cc.concatenated_segments segment2 ,
aia.invoice_currency_code currency_code,
-- xal.entered_dr,
-- xal.entered_cr,
-- xal.accounted_dr,
-- xal.accounted_cr,
sum(nvl(xdl.unrounded_entered_dr,0)) entered_dr ,
sum(nvl(xdl.unrounded_entered_cr,0)) entered_cr ,
sum(nvl(xdl.unrounded_accounted_dr,0)) accounted_dr,
sum(nvl(xdl.unrounded_accounted_cr,0)) accounted_cr
-- gir.je_header_id,
-- gir.je_line_num,
-- cc.code_combination_id,
--gps.application_id,
--
-- aia.doc_sequence_value vocher_number
-- ,aia.gl_date
-- ,null "staff number"
-- ,null "project"
-- ,null "customer num/name"
,
aia.description ,
aia.exchange_rate ,
aia.terms_date
from ap_invoices_all aia ,
ap_invoice_lines_all aila ,
ap_invoice_distributions_all aida,
--ap_prepay_app_dists apad,
xla_distribution_links xdl ,
xla_ae_lines xal ,
xla_ae_headers xah ,
gl_import_references gir ,
gl_je_lines gjl ,
gl_je_headers gjh ,
gl_code_combinations_kfv cc,
gl_period_statuses gps ,
apps.ap_suppliers aps
where aia.invoice_id=aila.invoice_id
and aila.invoice_id =aida.invoice_id
and aila.line_number =aida.invoice_line_number
--and aia.invoice_id=88734
and aida.line_type_lookup_code<>'prepay'
--and aida.invoice_distribution_id= apad.prepay_app_distribution_id
--and apad.amount<>0
and aida.invoice_distribution_id =xdl.source_distribution_id_num_1
and xdl.applied_to_source_id_num_1 in (aila.prepay_invoice_id,aila.invoice_id)
and xdl.ae_header_id =xal.ae_header_id
and xdl.ae_line_num =xal.ae_line_num
and xal.ae_header_id =xah.ae_header_id
and xal.gl_sl_link_id = gir.gl_sl_link_id
and xal.gl_sl_link_table = gir.gl_sl_link_table
and cc.code_combination_id =xal.code_combination_id
and cc.code_combination_id =gjl.code_combination_id
and aia.vendor_id =aps.vendor_id
and gir.je_header_id =gjl.je_header_id
and gir.je_line_num =gjl.je_line_num
and gjl.je_header_id =gjh.je_header_id
and gjh.ledger_id =gps.ledger_id
and 101 =gps.application_id
and gps.set_of_books_id =gjh.ledger_id
and gjh.je_source ='payables'
and gjh.ledger_id not in ( 1014,1015,1016 )
and gjh.period_name = gps.period_name
--and cc.segment5 in (:p_min_flex,:p_max_flex)
and ( nvl(xal.accounted_cr,0)<>0
or nvl(xal.accounted_dr,0) <>0)
and trunc(xah.accounting_date) between to_date('01-sep-1099') and to_date(:p_date)
--and aia.org_id=:p_org
and xah.je_category_name='purchase invoices'
-- and ( nvl (gps.effective_period_num, 0) >=:cf_period_from
-- and (nvl (gps.effective_period_num, 0) <=:cf_period_to)
-- )
and ( nvl (cc.segment5, 0) >= :p_account_from
and (nvl (cc.segment5, 0) <= :p_account_to) )
and ( nvl (cc.segment1, 0) >=:p_company_from
and (nvl (cc.segment1, 0) <= :p_company_to) )
and aia.payment_currency_code =decode(:p_currency_code,'inr','inr', 'all',aia.payment_currency_code, 'non inr',decode(aia.payment_currency_code,'inr','###',aia.payment_currency_code))
--and aia.invoice_num='010'
--and aia.doc_sequence_value=31207105
group by
--gps.period_name,
gjl.reference_2 ,
aia.invoice_num ,
aia.gl_date ,
aps.vendor_name ,
aps.segment1 ,
gjh.je_source ,
gjh.ledger_id ,
cc.concatenated_segments ,
--cc.concatenated_segments ,
aia.payment_currency_code ,
aia.invoice_currency_code ,
--gir.je_header_id,
--gir.je_line_num,
--cc.code_combination_id,
aia.doc_sequence_value ,
aia.gl_date ,
aia.description ,
aps.global_attribute20 ,
aps.global_attribute18 ,
aps.vendor_type_lookup_code,
aia.invoice_date ,
aia.invoice_amount ,
aia.invoice_id ,
aia.voucher_num ,
--aia.gl_date,
aia.invoice_type_lookup_code ,
aia.exchange_rate ,
aia.terms_date
) cc
group by cc.vendor_name ,
cc.vendor_number ,
cc.panno ,
cc.sertax ,
cc.vendor_type ,
cc.liabulity_account ,
cc.invoice_num ,
cc.vocher_number ,
cc.invoice_date ,
cc.invoice_amount ,
cc.invoice_id ,
cc.gl_date ,
cc.invoice_type_lookup_code,
cc.payment_currency_code ,
cc.reference_2 ,
cc.v_source ,
cc.set_of_books_id ,
cc.segment ,
cc.segment2 ,
cc.currency_code ,
--sum(nvl(cc.entered_dr,0)),
--sum(nvl(cc.entered_cr,0)),
--sum(nvl(cc.accounted_dr,0)),
--sum(nvl(cc.accounted_cr,0)),
cc.description ,
cc.exchange_rate ,
cc.terms_date
) aa,
--select bb.vendor_name,bb.vendor_number,bb.panno,bb.sertax,bb.vendor_type,bb.liabulity_account,bb.invoice_num,bb.vocher_number,bb.invoice_date,bb.gl_date,bb.invoice_type_lookup_code,bb.payment_currency_code,bb.segment2,bb.currency_code,(bb.entered_dr) entered_dr,(bb.entered_cr) entered_cr,(bb.accounted_dr) accounted_dr,(bb.accounted_cr) accounted_cr from (
(
select aps.vendor_name ,
aps.segment1 vendor_number ,
aps.global_attribute20 panno ,
aps.global_attribute18 sertax ,
aps.vendor_type_lookup_code vendor_type ,
cc.concatenated_segments liabulity_account,
aia.invoice_num ,
aia.doc_sequence_value vocher_number ,
aia.invoice_id ,
aia.invoice_date ,
aia.invoice_amount ,
aia.gl_date ,
aia.invoice_type_lookup_code ,
aia.payment_currency_code ,
--gps.period_name,
--gjl.reference_2,
--(select distinct pha.segment1 from ap_invoice_lines_all ailla, po_headers_all pha where ailla.invoice_id=aia.invoice_id and ailla.po_header_id=pha.po_header_id) po_order_number,
--
--(select distinct rsh.receipt_num
--from rcv_shipment_headers rsh, rcv_shipment_lines rsl,rcv_transactions rt, ap_invoice_lines_all aila
--where rsh.shipment_header_id=rsl.shipment_header_id
--and rsl.shipment_header_id=rt.shipment_header_id
--and rsl.shipment_line_id=rt.shipment_line_id
--and rt.transaction_id=(select distinct rcv_transaction_id from ap_invoice_lines_all aila where aila.invoice_id=aia.invoice_id and rownum=1 and rcv_transaction_id is not null) ) "receipt number",
--(select distinct rt.transaction_date
--from rcv_shipment_headers rsh, rcv_shipment_lines rsl,rcv_transactions rt, ap_invoice_lines_all aila
--where rsh.shipment_header_id=rsl.shipment_header_id
--and rsl.shipment_header_id=rt.shipment_header_id
--and rsl.shipment_line_id=rt.shipment_line_id
--and rt.transaction_id=(select distinct rcv_transaction_id from ap_invoice_lines_all aila where aila.invoice_id=aia.invoice_id and rownum=1 and rcv_transaction_id is not null)) "receipt date",
-- aia.invoice_num "invoice number",
aia.gl_date "invoice date",
-- gjh.je_source "source",
xah.ledger_id set_of_books_id ,
cc.concatenated_segments segment ,
cc.concatenated_segments segment2 ,
aia.invoice_currency_code currency_code,
-- xal.entered_dr,
-- xal.entered_cr,
-- xal.accounted_dr,
-- xal.accounted_cr,
sum(nvl(xdl.unrounded_entered_dr,0)) entered_dr ,
sum(nvl(xdl.unrounded_entered_cr,0)) entered_cr ,
sum(nvl(xdl.unrounded_accounted_dr,0)) accounted_dr,
sum(nvl(xdl.unrounded_accounted_cr,0)) accounted_cr,
-- gir.je_header_id,
-- gir.je_line_num,
-- cc.code_combination_id,
--gps.application_id,
--
aia.doc_sequence_value "vocher number" ,
aia.gl_date "gl_date"
-- ,null "staff number"
-- ,null "project"
-- ,null "customer num/name"
,
aia.description "description" ,
max(apha.accounting_date) accounting_date ,
aia.exchange_rate ,
aia.terms_date
from xla_ae_headers xah ,
xla_ae_lines xla ,
xla_distribution_links xdl ,
ap_payment_hist_dists aphd ,
ap_invoice_payment_history_v apha,
ap_invoices_all aia ,
ap_suppliers aps ,
gl_code_combinations_kfv cc
where xah.ae_header_id =xla.ae_header_id
and xdl.ae_header_id =xla.ae_header_id
and xdl.ae_line_num =xla.ae_line_num
and xah.je_category_name ='payments'
and xdl.source_distribution_id_num_1= aphd.payment_hist_dist_id
and aphd.invoice_payment_id =apha.invoice_payment_id
--and apha.invoice_num='010'
and apha.invoice_id =aia.invoice_id
and aia.vendor_id =aps.vendor_id
and xla.code_combination_id=cc.code_combination_id
and trunc(xah.accounting_date) between to_date('01-sep-1099') and to_date(:p_date)
and ( nvl (cc.segment5, 0) >= :p_account_from
and (nvl (cc.segment5, 0) <= :p_account_to) )
and ( nvl (cc.segment1, 0) >=:p_company_from
and (nvl (cc.segment1, 0) <= :p_company_to) )
and aia.payment_currency_code =decode(:p_currency_code,'inr','inr', 'all',aia.payment_currency_code, 'non inr',decode(aia.payment_currency_code,'inr','###',aia.payment_currency_code))
--and aia.doc_sequence_value=31207105
group by aps.vendor_name ,
aps.segment1 ,
aps.global_attribute20 ,
aps.global_attribute18 ,
aps.vendor_type_lookup_code ,
cc.concatenated_segments ,
aia.invoice_num ,
aia.doc_sequence_value ,
aia.invoice_id ,
aia.invoice_date ,
aia.invoice_amount ,
aia.gl_date ,
aia.invoice_type_lookup_code,
aia.payment_currency_code ,
aia.invoice_currency_code ,
aia.doc_sequence_value ,
aia.gl_date ,
xah.ledger_id
-- ,null "staff number"
-- ,null "project"
-- ,null "customer num/name"
,
aia.description ,
aia.exchange_rate ,
aia.terms_date
) bb
where aa.vendor_number=bb.vendor_number(+)
and aa.invoice_num =bb.invoice_num(+)
-- and aa.vocher_number=bb.vocher_number
-- and aa.vocher_number=31211651
order by 2,7 ;
******************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************
select aa.vendor_name ,
aa.vendor_number ,
aa.panno ,
aa.sertax ,
aa.vendor_type ,
aa.liabulity_account ,
aa.invoice_num ,
aa.vocher_number ,
aa.invoice_date ,
aa.gl_date ,
aa.invoice_type_lookup_code ,
aa.payment_currency_code ,
aa.segment2 ,
aa.currency_code ,
(nvl(aa.entered_dr,0) +nvl(bb.entered_dr,0)) entered_dr ,
(nvl(aa.entered_cr,0) +nvl(bb.entered_cr,0)) entered_cr ,
(nvl(aa.accounted_dr,0)+nvl(bb.accounted_dr,0)) accounted_dr,
(nvl(aa.accounted_cr,0)+nvl(bb.accounted_cr,0)) accounted_cr
from
(select cc.vendor_name ,
cc.vendor_number ,
cc.panno ,
cc.sertax ,
cc.vendor_type ,
cc.liabulity_account ,
cc.invoice_num ,
cc.vocher_number ,
cc.invoice_date ,
cc.gl_date ,
cc.invoice_type_lookup_code ,
cc.payment_currency_code ,
cc.reference_2 ,
cc.v_source ,
cc.set_of_books_id ,
cc.segment ,
cc.segment2 ,
cc.currency_code ,
sum(nvl(cc.entered_dr,0)) entered_dr ,
sum(nvl(cc.entered_cr,0)) entered_cr ,
sum(nvl(cc.accounted_dr,0)) accounted_dr,
sum(nvl(cc.accounted_cr,0)) accounted_cr,
cc.description
from
(select aps.vendor_name ,
aps.segment1 vendor_number ,
aps.global_attribute20 panno ,
aps.global_attribute18 sertax ,
aps.vendor_type_lookup_code vendor_type ,
cc.concatenated_segments liabulity_account ,
aia.invoice_num ,
aia.doc_sequence_value vocher_number ,
aia.invoice_date ,
aia.gl_date ,
aia.invoice_type_lookup_code ,
aia.payment_currency_code ,
gjl.reference_2 ,
gjh.je_source v_source ,
gjh.ledger_id set_of_books_id ,
cc.concatenated_segments segment ,
cc.concatenated_segments segment2 ,
aia.payment_currency_code currency_code ,
sum(nvl(xdl.unrounded_entered_dr,0)) entered_dr ,
sum(nvl(xdl.unrounded_entered_cr,0)) entered_cr ,
sum(nvl(xdl.unrounded_accounted_dr,0)) accounted_dr ,
sum(nvl(xdl.unrounded_accounted_cr,0)) accounted_cr ,
aia.description
from ap_invoices_all aia ,
ap_invoice_lines_all aila ,
ap_invoice_distributions_all aida,
ap_prepay_app_dists apad ,
xla_distribution_links xdl ,
xla_ae_lines xal ,
xla_ae_headers xah ,
gl_import_references gir ,
gl_je_lines gjl ,
gl_je_headers gjh ,
gl_code_combinations_kfv cc ,
gl_period_statuses gps ,
apps.ap_suppliers aps
where aia.invoice_id=aila.invoice_id
and aila.invoice_id =aida.invoice_id
and aila.line_number =aida.invoice_line_number
--and aia.invoice_id=88734
and aida.line_type_lookup_code ='prepay'
and aida.invoice_distribution_id = apad.prepay_app_distribution_id
and apad.amount <>0
and apad.prepay_app_dist_id =xdl.source_distribution_id_num_1
and xdl.applied_to_source_id_num_1 in (aila.prepay_invoice_id,aila.invoice_id)
and xdl.ae_header_id =xal.ae_header_id
and xdl.ae_line_num =xal.ae_line_num
and xal.ae_header_id =xah.ae_header_id
and xal.gl_sl_link_id = gir.gl_sl_link_id
and xal.gl_sl_link_table = gir.gl_sl_link_table
and cc.code_combination_id =xal.code_combination_id
and cc.code_combination_id =gjl.code_combination_id
and aia.vendor_id =aps.vendor_id
and gir.je_header_id =gjl.je_header_id
and gir.je_line_num =gjl.je_line_num
and gjl.je_header_id =gjh.je_header_id
and gjh.ledger_id =gps.ledger_id
and 101 =gps.application_id
and gps.set_of_books_id =gjh.ledger_id
and gjh.je_source ='payables'
and gjh.ledger_id not in ( 1014,1015,1016 )
and gjh.period_name = gps.period_name
--and cc.segment5 in (:p_min_flex,:p_max_flex)
and ( nvl(xal.accounted_cr,0)<>0
or nvl(xal.accounted_dr,0) <>0)
and trunc(xah.accounting_date) between to_date('01-sep-1099') and to_date(:p_date)
--and aia.org_id=:p_org
and xah.je_category_name='purchase invoices'
-- and ( nvl (gps.effective_period_num, 0) >=:cf_period_from
-- and (nvl (gps.effective_period_num, 0) <=:cf_period_to)
-- )
and ( nvl (cc.segment5, 0) >= :p_account_from
and (nvl (cc.segment5, 0) <= :p_account_to) )
and ( nvl (cc.segment1, 0) >=:p_company_from
and (nvl (cc.segment1, 0) <= :p_company_to) )
and aia.payment_currency_code =decode(:p_currency_code,'inr','inr', 'all',aia.payment_currency_code, 'non inr',decode(aia.payment_currency_code,'inr','###',aia.payment_currency_code))
--and aia.invoice_num='us pro'
--and aia.doc_sequence_value=31207105
group by
--gps.period_name,
gjl.reference_2 ,
aia.invoice_num ,
aia.gl_date ,
aps.vendor_name ,
aps.segment1 ,
gjh.je_source ,
gjh.ledger_id ,
cc.concatenated_segments ,
--cc.concatenated_segments ,
aia.payment_currency_code ,
--gir.je_header_id,
--gir.je_line_num,
--cc.code_combination_id,
aia.doc_sequence_value ,
aia.gl_date ,
aia.description ,
aps.global_attribute20 ,
aps.global_attribute18 ,
aps.vendor_type_lookup_code,
aia.invoice_date ,
--aia.gl_date,
aia.invoice_type_lookup_code
union all
select aps.vendor_name ,
aps.segment1 vendor_number ,
aps.global_attribute20 panno ,
aps.global_attribute18 sertax ,
aps.vendor_type_lookup_code vendor_type ,
cc.concatenated_segments liabulity_account,
aia.invoice_num ,
aia.doc_sequence_value vocher_number ,
aia.invoice_date ,
aia.gl_date ,
aia.invoice_type_lookup_code ,
aia.payment_currency_code ,
--gps.period_name,
gjl.reference_2,
--(select distinct pha.segment1 from ap_invoice_lines_all ailla, po_headers_all pha where ailla.invoice_id=aia.invoice_id and ailla.po_header_id=pha.po_header_id) po_order_number,
--
--(select distinct rsh.receipt_num
--from rcv_shipment_headers rsh, rcv_shipment_lines rsl,rcv_transactions rt, ap_invoice_lines_all aila
--where rsh.shipment_header_id=rsl.shipment_header_id
--and rsl.shipment_header_id=rt.shipment_header_id
--and rsl.shipment_line_id=rt.shipment_line_id
--and rt.transaction_id=(select distinct rcv_transaction_id from ap_invoice_lines_all aila where aila.invoice_id=aia.invoice_id and rownum=1 and rcv_transaction_id is not null) ) "receipt number",
--(select distinct rt.transaction_date
--from rcv_shipment_headers rsh, rcv_shipment_lines rsl,rcv_transactions rt, ap_invoice_lines_all aila
--where rsh.shipment_header_id=rsl.shipment_header_id
--and rsl.shipment_header_id=rt.shipment_header_id
--and rsl.shipment_line_id=rt.shipment_line_id
--and rt.transaction_id=(select distinct rcv_transaction_id from ap_invoice_lines_all aila where aila.invoice_id=aia.invoice_id and rownum=1 and rcv_transaction_id is not null)) "receipt date",
-- aia.invoice_num "invoice number",
-- aia.gl_date "invoice date",
gjh.je_source v_source ,
gjh.ledger_id set_of_books_id ,
cc.concatenated_segments segment ,
cc.concatenated_segments segment2 ,
aia.payment_currency_code currency_code,
-- xal.entered_dr,
-- xal.entered_cr,
-- xal.accounted_dr,
-- xal.accounted_cr,
sum(nvl(xdl.unrounded_entered_dr,0)) entered_dr ,
sum(nvl(xdl.unrounded_entered_cr,0)) entered_cr ,
sum(nvl(xdl.unrounded_accounted_dr,0)) accounted_dr,
sum(nvl(xdl.unrounded_accounted_cr,0)) accounted_cr
-- gir.je_header_id,
-- gir.je_line_num,
-- cc.code_combination_id,
--gps.application_id,
--
-- aia.doc_sequence_value vocher_number
-- ,aia.gl_date
-- ,null "staff number"
-- ,null "project"
-- ,null "customer num/name"
,
aia.description
from ap_invoices_all aia ,
ap_invoice_lines_all aila ,
ap_invoice_distributions_all aida,
--ap_prepay_app_dists apad,
xla_distribution_links xdl ,
xla_ae_lines xal ,
xla_ae_headers xah ,
gl_import_references gir ,
gl_je_lines gjl ,
gl_je_headers gjh ,
gl_code_combinations_kfv cc,
gl_period_statuses gps ,
apps.ap_suppliers aps
where aia.invoice_id=aila.invoice_id
and aila.invoice_id =aida.invoice_id
and aila.line_number =aida.invoice_line_number
--and aia.invoice_id=88734
and aida.line_type_lookup_code<>'prepay'
--and aida.invoice_distribution_id= apad.prepay_app_distribution_id
--and apad.amount<>0
and aida.invoice_distribution_id =xdl.source_distribution_id_num_1
and xdl.applied_to_source_id_num_1 in (aila.prepay_invoice_id,aila.invoice_id)
and xdl.ae_header_id =xal.ae_header_id
and xdl.ae_line_num =xal.ae_line_num
and xal.ae_header_id =xah.ae_header_id
and xal.gl_sl_link_id = gir.gl_sl_link_id
and xal.gl_sl_link_table = gir.gl_sl_link_table
and cc.code_combination_id =xal.code_combination_id
and cc.code_combination_id =gjl.code_combination_id
and aia.vendor_id =aps.vendor_id
and gir.je_header_id =gjl.je_header_id
and gir.je_line_num =gjl.je_line_num
and gjl.je_header_id =gjh.je_header_id
and gjh.ledger_id =gps.ledger_id
and 101 =gps.application_id
and gps.set_of_books_id =gjh.ledger_id
and gjh.je_source ='payables'
and gjh.ledger_id not in ( 1014,1015,1016 )
and gjh.period_name = gps.period_name
--and cc.segment5 in (:p_min_flex,:p_max_flex)
and ( nvl(xal.accounted_cr,0)<>0
or nvl(xal.accounted_dr,0) <>0)
and trunc(xah.accounting_date) between to_date('01-sep-1099') and to_date(:p_date)
--and aia.org_id=:p_org
and xah.je_category_name='purchase invoices'
-- and ( nvl (gps.effective_period_num, 0) >=:cf_period_from
-- and (nvl (gps.effective_period_num, 0) <=:cf_period_to)
-- )
and ( nvl (cc.segment5, 0) >= :p_account_from
and (nvl (cc.segment5, 0) <= :p_account_to) )
and ( nvl (cc.segment1, 0) >=:p_company_from
and (nvl (cc.segment1, 0) <= :p_company_to) )
and aia.payment_currency_code =decode(:p_currency_code,'inr','inr', 'all',aia.payment_currency_code, 'non inr',decode(aia.payment_currency_code,'inr','###',aia.payment_currency_code))
--and aia.invoice_num='010'
--and aia.doc_sequence_value=31207105
group by
--gps.period_name,
gjl.reference_2 ,
aia.invoice_num ,
aia.gl_date ,
aps.vendor_name ,
aps.segment1 ,
gjh.je_source ,
gjh.ledger_id ,
cc.concatenated_segments ,
--cc.concatenated_segments ,
aia.payment_currency_code ,
--gir.je_header_id,
--gir.je_line_num,
--cc.code_combination_id,
aia.doc_sequence_value ,
aia.gl_date ,
aia.description ,
aps.global_attribute20 ,
aps.global_attribute18 ,
aps.vendor_type_lookup_code,
aia.invoice_date ,
--aia.gl_date,
aia.invoice_type_lookup_code
) cc
group by cc.vendor_name ,
cc.vendor_number ,
cc.panno ,
cc.sertax ,
cc.vendor_type ,
cc.liabulity_account ,
cc.invoice_num ,
cc.vocher_number ,
cc.invoice_date ,
cc.gl_date ,
cc.invoice_type_lookup_code,
cc.payment_currency_code ,
cc.reference_2 ,
cc.v_source ,
cc.set_of_books_id ,
cc.segment ,
cc.segment2 ,
cc.currency_code ,
--sum(nvl(cc.entered_dr,0)),
--sum(nvl(cc.entered_cr,0)),
--sum(nvl(cc.accounted_dr,0)),
--sum(nvl(cc.accounted_cr,0)),
cc.description
) aa,
--select bb.vendor_name,bb.vendor_number,bb.panno,bb.sertax,bb.vendor_type,bb.liabulity_account,bb.invoice_num,bb.vocher_number,bb.invoice_date,bb.gl_date,bb.invoice_type_lookup_code,bb.payment_currency_code,bb.segment2,bb.currency_code,(bb.entered_dr) entered_dr,(bb.entered_cr) entered_cr,(bb.accounted_dr) accounted_dr,(bb.accounted_cr) accounted_cr from (
(
select aps.vendor_name ,
aps.segment1 vendor_number ,
aps.global_attribute20 panno ,
aps.global_attribute18 sertax ,
aps.vendor_type_lookup_code vendor_type ,
cc.concatenated_segments liabulity_account,
aia.invoice_num ,
aia.doc_sequence_value vocher_number ,
aia.invoice_date ,
aia.gl_date ,
aia.invoice_type_lookup_code ,
aia.payment_currency_code ,
--gps.period_name,
--gjl.reference_2,
--(select distinct pha.segment1 from ap_invoice_lines_all ailla, po_headers_all pha where ailla.invoice_id=aia.invoice_id and ailla.po_header_id=pha.po_header_id) po_order_number,
--
--(select distinct rsh.receipt_num
--from rcv_shipment_headers rsh, rcv_shipment_lines rsl,rcv_transactions rt, ap_invoice_lines_all aila
--where rsh.shipment_header_id=rsl.shipment_header_id
--and rsl.shipment_header_id=rt.shipment_header_id
--and rsl.shipment_line_id=rt.shipment_line_id
--and rt.transaction_id=(select distinct rcv_transaction_id from ap_invoice_lines_all aila where aila.invoice_id=aia.invoice_id and rownum=1 and rcv_transaction_id is not null) ) "receipt number",
--(select distinct rt.transaction_date
--from rcv_shipment_headers rsh, rcv_shipment_lines rsl,rcv_transactions rt, ap_invoice_lines_all aila
--where rsh.shipment_header_id=rsl.shipment_header_id
--and rsl.shipment_header_id=rt.shipment_header_id
--and rsl.shipment_line_id=rt.shipment_line_id
--and rt.transaction_id=(select distinct rcv_transaction_id from ap_invoice_lines_all aila where aila.invoice_id=aia.invoice_id and rownum=1 and rcv_transaction_id is not null)) "receipt date",
-- aia.invoice_num "invoice number",
aia.gl_date "invoice date",
-- gjh.je_source "source",
-- gjh.ledger_id "set_of_books_id" ,
cc.concatenated_segments segment ,
cc.concatenated_segments segment2 ,
aia.payment_currency_code currency_code,
-- xal.entered_dr,
-- xal.entered_cr,
-- xal.accounted_dr,
-- xal.accounted_cr,
sum(nvl(xdl.unrounded_entered_dr,0)) entered_dr ,
sum(nvl(xdl.unrounded_entered_cr,0)) entered_cr ,
sum(nvl(xdl.unrounded_accounted_dr,0)) accounted_dr,
sum(nvl(xdl.unrounded_accounted_cr,0)) accounted_cr,
-- gir.je_header_id,
-- gir.je_line_num,
-- cc.code_combination_id,
--gps.application_id,
--
aia.doc_sequence_value "vocher number" ,
aia.gl_date "gl_date"
-- ,null "staff number"
-- ,null "project"
-- ,null "customer num/name"
,
aia.description "description"
from xla_ae_headers xah ,
xla_ae_lines xla ,
xla_distribution_links xdl ,
ap_payment_hist_dists aphd ,
ap_invoice_payment_history_v apha,
ap_invoices_all aia ,
ap_suppliers aps ,
gl_code_combinations_kfv cc
where xah.ae_header_id =xla.ae_header_id
and xdl.ae_header_id =xla.ae_header_id
and xdl.ae_line_num =xla.ae_line_num
and xah.je_category_name ='payments'
and xdl.source_distribution_id_num_1= aphd.payment_hist_dist_id
and aphd.invoice_payment_id =apha.invoice_payment_id
--and apha.invoice_num='010'
and apha.invoice_id =aia.invoice_id
and aia.vendor_id =aps.vendor_id
and xla.code_combination_id=cc.code_combination_id
and trunc(xah.accounting_date) between to_date('01-sep-1099') and to_date(:p_date)
and ( nvl (cc.segment5, 0) >= :p_account_from
and (nvl (cc.segment5, 0) <= :p_account_to) )
and ( nvl (cc.segment1, 0) >=:p_company_from
and (nvl (cc.segment1, 0) <= :p_company_to) )
and aia.payment_currency_code =decode(:p_currency_code,'inr','inr', 'all',aia.payment_currency_code, 'non inr',decode(aia.payment_currency_code,'inr','###',aia.payment_currency_code))
--and aia.doc_sequence_value=31207105
group by aps.vendor_name ,
aps.segment1 ,
aps.global_attribute20 ,
aps.global_attribute18 ,
aps.vendor_type_lookup_code ,
cc.concatenated_segments ,
aia.invoice_num ,
aia.doc_sequence_value ,
aia.invoice_date ,
aia.gl_date ,
aia.invoice_type_lookup_code,
aia.payment_currency_code ,
aia.doc_sequence_value ,
aia.gl_date
-- ,null "staff number"
-- ,null "project"
-- ,null "customer num/name"
,
aia.description
) bb
where aa.vendor_number=bb.vendor_number(+)
and aa.invoice_num =bb.invoice_num(+)
-- and aa.vocher_number=bb.vocher_number
-- and aa.vocher_number=31211651
order by 2,7 ;
--
-- begin
-- mo_global.set_policy_context('s',87);
-- end;
