DECLARE
var_rows number(5);
BEGIN
UPDATE employee SET salary = salary + 1000;
IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
END IF;
END;
In the above PL/SQL Block, the salaries of all the employees in the ‘employee’ table are updated.
If none of the employee’s salary are updated we get a message 'None of the salaries where updated'.
Else we get a message like for example, 'Salaries for 1000 employees are updated' if there are 1000 rows in ‘employee’ table.
Ref Cursor ?
A cursor variable is a pointer that distinguishes the current row in a resultset from a multi-row query.
Cursor variables have several advantages over explicit cursors
including: Cursor variables can point to a variety of queries provided the queries have a suitable return type.
In contrast, explicit cursors are tied to individual queries.
Cursor variables allow cursors to be opened independently of being processed.
Cursor variables can be passed as parameters between application layers, as well as between server side components.
Cursor variables can be used to reduce client-server network traffic by allowing several cursors to be opened on the server in a single round trip.
The following sections illustrate ways in which cursor variables can be used in PL/SQL code.
A thorough understanding of these features will allow programmers to build more efficient PL/SQL APIs and may prove useful as a replacement for passing
large collections as parameters between PL/SQL.
The example code in this chapter relies on the cursor_variable_test table which is created with the cursor_variable_test.sql script listed below.
cursor_variable_test.sql
-- Create and populate a test table.
CREATE TABLE
cursor_variable_test ( id NUMBER(10), description VARCHAR2(50) );
INSERT INTO cursor_variable_test (id, description) VALUES (1, 'One');
INSERT INTO cursor_variable_test (id, description) VALUES (2, 'Two');
INSERT INTO cursor_variable_test (id, description) VALUES (3, 'Three');
COMMIT;
The examples in this chapter regularly make use of the SYS_REFCURSOR type which became available in Oracle9i.
To make this script backwards compatible, create the package specification listed in the cursor_api.sql
script and replace any reference to “SYS_REFCURSOR” with “cursor_api.refcursor”.
cursor_api.sql -- *****************************************************************
-- Copyright 2005 by Rampant TechPress Inc.
-- Free for non-commercial use! To license, e-mail info@rampant.cc --
*****************************************************************
CREATE OR REPLACE PACKAGE
cursor_api AS TYPE refcursor IS REF CURSOR;
END cursor_ api;
/ Defining Cursor Variables Cursor variables are defined using a REF CURSOR type.
The type is defined in one of two ways: Strongly Typed
- The REF CURSOR type is restricted to an individual return type using the RETURN clause.
Although this reduces the chances of runtime errors since column mismatches are detected at compilation time, it also limits the overall flexibility of the type.
Weakly Typed - The RETURN clause is omitted allowing the type to reference any return type.
This gives greater flexibility, but it increases the likelihood of runtime errors because column mismatches are not picked up at compile time.
The cursor_variable_definitions.sql script shows how strongly and weakly typed cursor variables are defined.
Cursor and refcursor:
differences.
1. (i) . Cursor is Static(no matter how many times you run that block).
(ii). Ref Cursor is Dynamic (opened based on logic).The ref cursor can be anything
2. (i) . Cursor will not return any value,but Ref cursor will return.
(ii) . a ref cursor can be passed from subroutine to subroutine -- a cursor cannot be
3. cursor can be global -- a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function).
4. Static sql (not using a ref cursor) is much more efficient then using ref cursors and that use of ref cursors should be limited to
- returning result sets to clients - when there is NO other efficient/effective means of achieving the goal
Thursday, January 24, 2013
Sunday, November 25, 2012
R12 Sandry Creditors Report Query
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;
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;
Subscribe to:
Posts (Atom)