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;
I'm certainly very happy to read this blog site posts which carries plenty of helpful data, thanks for providing such information. freecreditreport
ReplyDeleteIt’s an outstanding article in favor of all the web guests; they will get benefit from it I am confident. sky blue credit repair reviews
ReplyDeleteI really enjoy reading and also appreciate your work. thecreditpeople
ReplyDelete