WITH FUNCTION get_ap_lookup_meaning (
p_lookup_type IN VARCHAR2,
p_lookup_code IN VARCHAR2
)
RETURN VARCHAR2
IS
lv_meaning fnd_lookup_values.meaning%TYPE;
BEGIN
SELECT meaning
INTO lv_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
AND view_application_id = 200
AND enabled_flag = 'Y';
RETURN lv_meaning;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
FUNCTION get_payment_det (p_return_type IN VARCHAR2, p_invoice_id IN NUMBER)
RETURN DATE
IS
lv_return_date DATE;
BEGIN
SELECT MIN (DECODE (p_return_type,
'PAYMENT_DATE', aca.check_date,
'CLEARED_DATE', aca.cleared_date
)
)
INTO lv_return_date
FROM ap_invoice_payments_all aipa, ap_checks_all aca
WHERE 1 = 1
AND aipa.check_id = aca.check_id
AND aipa.invoice_id = p_invoice_id;
RETURN lv_return_date;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
FUNCTION get_prepayment_det (p_invoice_id IN NUMBER)
RETURN DATE
IS
lv_return_date DATE;
BEGIN
SELECT MIN (aid.accounting_date)
INTO lv_return_date
FROM ap_invoice_distributions_all aid, ap_invoice_lines_all ail
WHERE ail.invoice_id = p_invoice_id
AND ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND aid.line_type_lookup_code = 'PREPAY'
AND aid.prepay_distribution_id IS NOT NULL
AND NVL (ail.invoice_includes_prepay_flag, 'N') = 'N';
RETURN lv_return_date;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
get_ap_inv_det AS
(SELECT distinct aia.invoice_num invoice_number,
TO_CHAR (aia.invoice_date,
'dd-Mon-yy',
'NLS_DATE_LANGUAGE=AMERICAN'
) invoice_date,
(aia.invoice_id) invoice_id,
SUBSTR (REGEXP_REPLACE (aia.description,
'[ '
|| CHR (10)
|| CHR (13)
|| CHR (9)
|| '|'
|| '"'
|| ']'
),
1,
100
) invoice_description,
hp.party_name vendor_name, ps.segment1 vendor_number,
hl.country vendor_site_country, pssam.vendor_site_code,
aia.exchange_date currency_conversion_date,
aia.exchange_rate curr_conv_rate,
ad.description dist_description, aia.invoice_currency_code,
(NVL (l.unrounded_entered_dr, 0))
- (NVL (l.unrounded_entered_cr, 0)) entered_net,
(NVL (l.unrounded_accounted_dr, 0))
- (NVL (l.unrounded_accounted_cr, 0)) accounted_net,
SIGN (aia.invoice_amount) sign_invoice_amount, gjh.je_header_id,
gjl.je_line_num, aia.invoice_amount ap_inv_amount,
aia.amount_paid ap_inv_amount_paid,
get_ap_lookup_meaning
(p_lookup_type => DECODE (aia.invoice_type_lookup_code,
'PREPAYMENT', 'PREPAY STATUS',
'NLS TRANSLATION'
),
p_lookup_code => ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
)
) ap_inv_status,
get_payment_det
(p_return_type => 'PAYMENT_DATE',
p_invoice_id => aia.invoice_id
) ap_inv_payment_date,
get_prepayment_det
(p_invoice_id => aia.invoice_id)
ap_inv_prepayment_date,
get_payment_det
(p_return_type => 'CLEARED_DATE',
p_invoice_id => aia.invoice_id
) ap_inv_cleared_date
--,proj.project_name --added by anu
-- ,proj.project_number
,ppa.segment1 project_number
,ppa.name project_name
,pt.task_number
,PCT.CLASS_CODE
,ppa.PROJECT_ID
,(select DISTINCT PHA.SEGMENT1 from PO_HEADERS_ALL PHA
where pha.PO_HEADER_ID=aia.PO_HEADER_ID) PURCHASE_ORDER
-- ,(select distinct aip.INVOICE_PAYMENT_ID from AP_INVOICE_PAYMENTS_ALL aip where aia.INVOICE_ID=aip.INVOICE_ID ) Payment_ID
,(select distinct
aca.check_number
from
AP_INVOICE_PAYMENTS_ALL aip,
ap_checks_all aca
where aip.INVOICE_ID=aia.INVOICE_ID
and aip.check_id = aca.check_id) PAYMENT_NUM
,(SELECT
PPSET.NAME
FROM
PJF_PROJECT_SETS_TL PPSET,
PJF_PROJECT_SETS_B PPSB,
PJF_PROJECT_SET_LINES PPSL
--PJF_PROJECTS_ALL_VL PJA
WHERE
1=1
AND PPSET.LANGUAGE = USERENV('LANG')
AND PPSB.PROJECT_SET_ID(+) = PPSET.PROJECT_SET_ID
AND PPSL.PROJECT_SET_ID = PPSB.PROJECT_SET_ID
AND PPSL.PROJECT_ID=ppa.PROJECT_ID) PROJECT_SET,
pla.line_num,ad.pjc_project_id,xte.transaction_number
FROM xla_ae_headers xah,
xla_ae_lines xal,
xla_events xe,
xla_transaction_entities xte,
xla_distribution_links l,
ap_invoices_all aia,
ap_invoice_distributions_all ad,
pjf_projects_all_vl ppa, --Project details added by SreenivasaChary
pjf_tasks_v pt,
PJF_CLASS_CATEGORIES_TL PCC,
PJF_CLASS_CODES_TL PCT,
PJF_PROJECT_CLASSES PPC,
poz_suppliers ps,
hz_parties hp,
poz_supplier_sites_all_m pssam,
hz_locations hl,
gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir,
po_distributions_all pda,
po_lines_all pla
/* ,(select ppb.segment1 project_number
,ppat.name project_name
,peia.original_header_id invoice_id
from pjc_exp_items_all peia
,pjf_projects_all_b ppb
,pjf_projects_all_tl ppat
,pjf_txn_sources_tl ptst
where peia.transaction_source_id = ptst.transaction_source_id
AND ptst.language = userenv('LANG')
AND ptst.user_transaction_source = 'Oracle Fusion Payables'
AND peia.project_id = ppb.project_id
AND ppb.project_id = ppat.project_id
AND ppat.language = userenv('LANG')
) proj --Added by Anu*/
WHERE 1 = 1
AND ppa.project_id(+) = ad.pjc_project_id
AND ppa.project_id=pt.project_id(+)
and ad.pjc_task_id=pt.task_id(+)
AND PPC.CLASS_CATEGORY_ID = PCC.CLASS_CATEGORY_ID(+)
AND PPC.PROJECT_ID(+) = PPA.PROJECT_ID
AND PPC.CLASS_CODE_ID = PCT.CLASS_CODE_ID(+)
AND PCC.CLASS_CATEGORY='OPEX Type'
AND NVL(PCC.LANGUAGE,'US') ='US'
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
AND l.ae_header_id = xal.ae_header_id
AND l.ae_line_num = xal.ae_line_num
AND l.source_distribution_type(+) = 'AP_INV_DIST'
AND l.source_distribution_id_num_1 = ad.invoice_distribution_id(+)
AND ad.invoice_id(+) = aia.invoice_id
and ad.PO_DISTRIBUTION_ID = pda.PO_DISTRIBUTION_ID
and pda.po_line_id = pla.po_line_id
AND xte.entity_code = 'AP_INVOICES'
AND xte.source_id_int_1 = aia.invoice_id(+)
AND aia.vendor_site_id = pssam.vendor_site_id(+)
AND pssam.vendor_id = ps.vendor_id(+)
AND aia.party_id = hp.party_id
AND pssam.location_id = hl.location_id(+)
AND gjh.je_header_id = gjl.je_header_id
AND gjh.actual_flag = 'A'
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjh.je_source = 'Payables'
AND NVL (gjh.status, 'P') = 'P'
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND xal.gl_sl_link_table = gir.gl_sl_link_table
-- AND aia.invoice_id = proj.invoice_id(+) -- Added by Anu
AND (( TO_CHAR (TO_DATE (gjl.period_name,
'Mon-yy',
'NLS_DATE_LANGUAGE=AMERICAN'
),
'yyyymm'
) >= :p_period_entered_from
AND TO_CHAR (TO_DATE (gjl.period_name,
'Mon-yy',
'NLS_DATE_LANGUAGE=AMERICAN'
),
'yyyymm'
) <= :p_period_entered_to
)
)
-- AND to_char(gjl.period_name) = :p_period_entered_from
--AND to_char(gjl.period_name) = :p_period_entered_to
/* AND (( TO_CHAR (TO_DATE (gjl.period_name,
'Mon-yy',
'NLS_DATE_LANGUAGE=AMERICAN'
),
'yyyymm'
) >= (select TO_CHAR (TO_DATE (:p_period_entered_from, 'Mon-yy', 'NLS_DATE_LANGUAGE=AMERICAN'), 'yyyymm') from dual)
AND TO_CHAR (TO_DATE (gjl.period_name,
'Mon-yy',
'NLS_DATE_LANGUAGE=AMERICAN'
),
'yyyymm'
) <= (select TO_CHAR (TO_DATE (:p_period_entered_to, 'Mon-yy', 'NLS_DATE_LANGUAGE=AMERICAN'), 'yyyymm') from dual)
)
)*/
)
SELECT TO_CHAR (TO_DATE (jel.period_name,
'Mon-yy',
'NLS_DATE_LANGUAGE=AMERICAN'
),
'yyyymm'
) period,
gjs.user_je_source_name, gjc.user_je_category_name, b.NAME batch_name,
b.description batch_description, h.NAME je_name,
h.description je_description,
DECODE (gjc.user_je_category_name,
'Purchase Invoices', get_inv_det.invoice_description,
''
) je_invoice_description,
h.doc_sequence_value, NVL (ppnf.display_name, h.created_by) created_by,
h.creation_date,
TO_CHAR (h.creation_date,
'dd-Mon-yy',
'NLS_DATE_LANGUAGE=AMERICAN'
) conv_creation_date,
TO_CHAR (h.posted_date,
'dd-Mon-yy',
'NLS_DATE_LANGUAGE=AMERICAN'
) conv_posted_date,
h.posted_date, jel.description line_description,
h.status journal_status,
DECODE (gjc.user_je_category_name,
'Purchase Invoices', get_inv_det.vendor_name,
''
) vendor_name,
DECODE (gjc.user_je_category_name,
'Purchase Invoices', get_inv_det.invoice_number,
''
) invoice_number,
DECODE (gjc.user_je_category_name,
'Purchase Invoices', get_inv_det.invoice_date,
''
) invoice_date,
DECODE (gjc.user_je_category_name,
'Purchase Invoices', get_inv_det.dist_description,
''
) dist_line_description,
gcc.segment1 le_code,
gl_flexfields_pkg.get_description_sql
(gcc.chart_of_accounts_id,
1,
gcc.segment1
) le_name,
gcc.segment2 cc_code,
gl_flexfields_pkg.get_description_sql
(gcc.chart_of_accounts_id,
2,
gcc.segment2
) cc_name,
gcc.segment3 acct_code,
gl_flexfields_pkg.get_description_sql
(gcc.chart_of_accounts_id,
3,
gcc.segment3
) acct_name,
gcc.segment4 service_type_code,
gl_flexfields_pkg.get_description_sql
(gcc.chart_of_accounts_id,
4,
gcc.segment4
) service_type_name,
gcc.segment5 div_code,
gl_flexfields_pkg.get_description_sql
(gcc.chart_of_accounts_id,
5,
gcc.segment5
) div_name,
gcc.segment6 ic_code,
gl_flexfields_pkg.get_description_sql
(gcc.chart_of_accounts_id,
6,
gcc.segment6
) ic_name,
gcc.segment7 future_code,
gl_flexfields_pkg.get_description_sql
(gcc.chart_of_accounts_id,
7,
gcc.segment7
) future_name,
gcc.segment8,
DECODE
(gcc.segment8,
NULL, NULL,
gl_flexfields_pkg.get_sd_description_sql (gcc.chart_of_accounts_id,
1,
8,
gcc.segment8
)
) segment8_name,
NVL (jel.currency_code, gl.currency_code) currency_code,
DECODE
(NVL (jel.currency_code, gl.currency_code),
gl.currency_code, NULL,
TO_CHAR (DECODE (gjc.user_je_category_name,
'Purchase Invoices', get_inv_det.currency_conversion_date,
jel.currency_conversion_date
),
'dd-Mon-yy',
'NLS_DATE_LANGUAGE=AMERICAN'
)
) currency_conversion_date,
DECODE (NVL (jel.currency_code, gl.currency_code),
gl.currency_code, NULL,
DECODE (gjc.user_je_category_name,
'Purchase Invoices', get_inv_det.curr_conv_rate,
jel.currency_conversion_rate
)
) currency_conversion_rate,
DECODE (gjc.user_je_category_name,
'Purchase Invoices', get_inv_det.entered_net,
((NVL (jel.entered_dr, 0)) - (NVL (jel.entered_cr, 0))
)
) entered_net,
DECODE (gjc.user_je_category_name,
'Purchase Invoices', get_inv_det.accounted_net,
((NVL (jel.accounted_dr, 0)) - (NVL (jel.accounted_cr, 0))
)
) accounted_net,
DECODE (sign_invoice_amount,
SIGN (DECODE (gjc.user_je_category_name,
'Purchase Invoices', get_inv_det.entered_net,
( (NVL (jel.entered_dr, 0))
- (NVL (jel.entered_cr, 0))
)
)
), DECODE (gjc.user_je_category_name,
'Purchase Invoices', get_inv_det.entered_net,
( (NVL (jel.entered_dr, 0))
- (NVL (jel.entered_cr, 0))
)
),
0
) ap_entered_net,
(jel.accounted_cr), (jel.accounted_dr), (jel.entered_cr),
(jel.entered_dr), get_inv_det.vendor_site_country,
get_inv_det.vendor_site_code, h.doc_sequence_id, h.je_header_id,
jel.period_name, jel.je_line_num,
LPAD (jel.je_line_num, 10, '0') lpad_je_line_num,
jel.code_combination_id, get_inv_det.invoice_id,
get_inv_det.vendor_number,
(SELECT ffv.attribute2
FROM fnd_flex_values_vl ffv,
fnd_flex_value_sets ffvs
WHERE 1 = 1
AND ffv.flex_value_set_id = ffvs.flex_value_set_id
AND ffv.flex_value = gcc.segment1
AND ffvs.flex_value_set_name = 'IFS Legal Entity') le_short_name,
get_inv_det.ap_inv_amount, get_inv_det.ap_inv_amount_paid,
get_inv_det.ap_inv_status,
TO_CHAR
(LEAST (NVL (get_inv_det.ap_inv_payment_date,
get_inv_det.ap_inv_prepayment_date
),
NVL (get_inv_det.ap_inv_prepayment_date,
get_inv_det.ap_inv_payment_date
)
),
'dd-Mon-yy',
'NLS_DATE_LANGUAGE=AMERICAN'
) ap_inv_payment_date,
TO_CHAR (get_inv_det.ap_inv_cleared_date,
'dd-Mon-yy',
'NLS_DATE_LANGUAGE=AMERICAN'
) ap_inv_cleared_date,
--,get_inv_det.project_name --added by anu
-- ,get_inv_det.project_number
get_inv_det.project_number,
get_inv_det.project_name,
get_inv_det.task_number,
get_inv_det.class_code opex_type,
get_inv_det.PROJECT_ID,
get_inv_det.Purchase_order,
get_inv_det.line_num Purchase_order_line,
--get_inv_det.Payment_ID
get_inv_det.PAYMENT_NUM,
get_inv_det.PROJECT_SET,
get_inv_det.transaction_number,
get_inv_det.pjc_project_id
FROM gl_code_combinations gcc,
gl_ledgers gl,
gl_je_batches b,
gl_je_headers h,
gl_je_lines jel,
gl_je_lines_recon rec,
gl_je_sources_tl gjs,
gl_je_categories_tl gjc,
per_users pu,
per_person_names_f ppnf,
get_ap_inv_det get_inv_det
WHERE 1 = 1
AND gl.ledger_id = h.ledger_id
AND h.je_batch_id + 0 = b.je_batch_id + 0
AND (h.display_alc_journal_flag = 'Y' OR h.display_alc_journal_flag IS NULL
)
AND gcc.code_combination_id(+) = jel.code_combination_id
AND rec.je_header_id(+) = jel.je_header_id
AND rec.je_line_num(+) = jel.je_line_num
AND (jel.je_header_id = h.je_header_id)
AND h.ledger_id in (:p_ledger_id)
--AND h.ledger_id in nvl(:p_ledger_id,h.ledger_id) --added y chary
--AND h.ledger_id in (:p_ledger_id) OR 'All' IN (: p_ledger_id || 'All') --chary
AND (h.actual_flag = 'A')
AND (h.created_by = pu.username(+))
AND (pu.person_id = ppnf.person_id(+))
AND (TRUNC (SYSDATE) BETWEEN ppnf.effective_start_date(+) AND ppnf.effective_end_date(+))
AND (ppnf.name_type(+) = 'GLOBAL')
AND (gjs.je_source_name = h.je_source)
AND (gjc.je_category_name = h.je_category)
AND (gjs.LANGUAGE = 'US')
AND (gjc.LANGUAGE = 'US')
AND (( TO_CHAR (TO_DATE (jel.period_name,
'Mon-yy',
'NLS_DATE_LANGUAGE=AMERICAN'
),
'yyyymm'
) >= :p_period_entered_from
AND TO_CHAR (TO_DATE (jel.period_name,
'Mon-yy',
'NLS_DATE_LANGUAGE=AMERICAN'
),
'yyyymm'
) <= :p_period_entered_to
)
)
--AND to_char(jel.period_name) >= :p_period_entered_from
--AND to_char(jel.period_name) <= :p_period_entered_to
/* AND (( TO_CHAR (TO_DATE (jel.period_name,
'Mon-yy',
'NLS_DATE_LANGUAGE=AMERICAN'
),
'yyyymm'
) > = (select TO_CHAR (TO_DATE (:p_period_entered_from, 'Mon-yy', 'NLS_DATE_LANGUAGE=AMERICAN'), 'yyyymm') from dual)
AND TO_CHAR (TO_DATE (jel.period_name,
'Mon-yy',
'NLS_DATE_LANGUAGE=AMERICAN'
),
'yyyymm'
) <= (select TO_CHAR (TO_DATE (:p_period_entered_to, 'Mon-yy', 'NLS_DATE_LANGUAGE=AMERICAN'), 'yyyymm') from dual)
)
)*/
--AND (h.status in (:p_journal_status) OR 'All' IN (:p_journal_status || 'All'))
AND h.status in (:p_journal_status)
--AND (jel.currency_code in :p_currency_code or :p_currency_code is null)
AND (jel.currency_code in (:p_currency_code) OR 'All' IN (:p_currency_code||'All'))
AND (h.doc_sequence_value =:p_doc_sequence_value or :p_doc_sequence_value is null)
AND (h.doc_sequence_value >= :p_from_doc_sequence_value or :p_from_doc_sequence_value is null) and (h.doc_sequence_value <= :p_to_doc_sequence_value or :p_to_doc_sequence_value is null)
--AND (h.doc_sequence_value between nvl(:p_from_doc_sequence_value,doc_sequence_value) and nvl(:p_to_doc_sequence_value,doc_sequence_value))
--AND get_inv_det.project_number in nvl(:p_project_num,get_inv_det.project_number)
AND (get_inv_det.project_number in (:p_project_num) OR 'All' IN (:p_project_num||'All'))
-- AND ((gcc.segment1||'-'||gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5||'-'||gcc.segment6)
-- IN
--(:p_from_account) or :p_from_account is null)
--and (gcc.code_combination_id IN (:p_from_account) or LEAST(:p_from_account) is null)
AND (gcc.segment1 not in (:p_excluding_le) OR 'All' IN (:p_excluding_le||'All'))
AND (gcc.segment1 in (:p_Including_le) OR 'All' IN (:p_Including_le||'All'))
AND (gcc.segment3 >= nvl(:p_from_account,gcc.segment3) or :p_from_account is null) AND (gcc.segment3 <= nvl(:p_to_account,gcc.segment3) or :p_to_account is null)
AND (gcc.segment4 in (:p_service_from) OR 'All' IN (:p_service_from||'All'))
AND (gcc.segment5 in (:p_div_from) OR 'All' IN (:p_div_from||'All'))
AND (gcc.segment6 in (:p_ic_from) OR 'All' IN (:p_ic_from||'All'))
AND (gcc.segment7 in (:p_future_from) OR 'All' IN (:p_future_from||'All'))
--AND (gcc.segment4,10,'0') >=lpad(:p_service_from,10,'0') or :p_service_from is null) and (lpad(gcc.segment4,10,'0') <=lpad(:p_service_to,10,'0') or :p_service_to is null))
--AND ((lpad(gcc.segment5,10,'0') >=lpad(:p_div_from,10,'0') or :p_div_from is null) and (lpad(gcc.segment5,10,'0') <=lpad(:p_div_to,10,'0') or :p_div_to is null))
--AND ((lpad(gcc.segment6,10,'0') >=lpad(:p_ic_from,10,'0') or :p_ic_from is null) and (lpad(gcc.segment6,10,'0') <=lpad(:p_ic_to,10,'0') or :p_ic_to is null))
--AND ((lpad(gcc.segment7,10,'0') >=lpad(:p_future_from,10,'0') or :p_future_from is null) and (lpad(gcc.segment7,10,'0') <=lpad(:p_future_to,10,'0') or :p_future_to is null))
--AND gcc.segment3 <=(nvl(:p_to_account,gcc.segment3) or p_to_account is null)
--AND (gcc.segment1 between SUBSTR(nvl(:p_from_account,gcc.segment1),1,3) and SUBSTR(nvl(:p_to_account,gcc.segment1),1,3))
--AND (gcc.segment2 between SUBSTR(nvl(:p_from_account,gcc.segment2),5,5) and SUBSTR(nvl(:p_to_account,gcc.segment2),5,5))
-- AND (gcc.segment3 between SUBSTR(nvl(:p_from_account,gcc.segment3),11,6) and SUBSTR(nvl(:p_to_account,gcc.segment3),11,6))
-- AND (gcc.segment4 between SUBSTR(nvl(:p_from_account,gcc.segment4),18,4) and SUBSTR(nvl(:p_to_account,gcc.segment4),18,4))
--AND (gcc.segment5 between SUBSTR(nvl(:p_from_account,gcc.segment5),23,2) and SUBSTR(nvl(:p_to_account,gcc.segment5),23,2))
-- AND (gcc.segment6 between SUBSTR(nvl(:p_from_account,gcc.segment6),26,3) and SUBSTR(nvl(:p_to_account,gcc.segment6),26,3))
/*AND (gcc.segment1||''||gcc.segment2||''||gcc.segment3||''||gcc.segment4||''||gcc.segment5||''||gcc.segment6) BETWEEN
(NVL(replace(:p_from_account,'-',''),replace(gcc.segment1||''||gcc.segment2||''||gcc.segment3||''||gcc.segment4||''||gcc.segment5||''||gcc.segment6,'-',''))) and
(NVL(replace(:p_to_account,'-',''),replace(gcc.segment1||''||gcc.segment2||''||gcc.segment3||''||gcc.segment4||''||gcc.segment5||''||gcc.segment6,'-','')))*/
/*(SUBSTR(nvl(:p_from_account,gcc.segment1),1,3)||''||SUBSTR(nvl(:p_from_account,gcc.segment2),5,5)||''||SUBSTR(nvl(:p_from_account,gcc.segment3),11,6)||''||
SUBSTR(nvl(:p_from_account,gcc.segment4),18,4)||''||SUBSTR(nvl(:p_from_account,gcc.segment5),23,2)||''||SUBSTR(nvl(:p_from_account,gcc.segment6),26,3))
AND
(SUBSTR(nvl(:p_to_account,gcc.segment1),1,3)||''||SUBSTR(nvl(:p_to_account,gcc.segment2),5,5)||''||SUBSTR(nvl(:p_to_account,gcc.segment3),11,6)||''||
SUBSTR(nvl(:p_to_account,gcc.segment4),18,4)||''||SUBSTR(nvl(:p_to_account,gcc.segment5),23,2)||''||SUBSTR(nvl(:p_to_account,gcc.segment6),26,3))
/*AND ((lpad(gcc.segment1,10,'0') >=lpad(SUBSTR(nvl(:p_from_account,gcc.segment1),1,3),10,'0') or SUBSTR(nvl(:p_from_account,gcc.segment1),1,3) is null) and (lpad(gcc.segment1,10,'0') <=lpad(SUBSTR(nvl(:p_to_account,gcc.segment1),1,3),10,'0') or SUBSTR(nvl(:p_to_account,gcc.segment1),1,3) is null))
AND ((lpad(gcc.segment2,10,'0') >=lpad(SUBSTR(nvl(:p_from_account,gcc.segment2),5,5),10,'0') or SUBSTR(nvl(:p_from_account,gcc.segment2),5,5) is null) and (lpad(gcc.segment2,10,'0') <=lpad(SUBSTR(nvl(:p_to_account,gcc.segment2),5,5),10,'0') or SUBSTR(nvl(:p_to_account,gcc.segment2),5,5) is null))
AND ((lpad(gcc.segment3,10,'0') >=lpad(SUBSTR(nvl(:p_from_account,gcc.segment3),11,6),10,'0') or SUBSTR(nvl(:p_from_account,gcc.segment3),11,6) is null) and (lpad(gcc.segment3,10,'0') <=lpad(SUBSTR(nvl(:p_to_account,gcc.segment3),11,6),10,'0') or SUBSTR(nvl(:p_to_account,gcc.segment3),11,6) is null))
AND ((lpad(gcc.segment4,10,'0') >=lpad(SUBSTR(nvl(:p_from_account,gcc.segment4),18,4),10,'0') or SUBSTR(nvl(:p_from_account,gcc.segment4),18,4) is null) and (lpad(gcc.segment4,10,'0') <=lpad(SUBSTR(nvl(:p_to_account,gcc.segment4),18,4),10,'0') or SUBSTR(nvl(:p_to_account,gcc.segment4),18,4) is null))
AND ((lpad(gcc.segment5,10,'0') >=lpad(SUBSTR(nvl(:p_from_account,gcc.segment5),23,2),10,'0') or SUBSTR(nvl(:p_from_account,gcc.segment5),23,2) is null) and (lpad(gcc.segment5,10,'0') <=lpad(SUBSTR(nvl(:p_to_account,gcc.segment5),23,2),10,'0') or SUBSTR(nvl(:p_to_account,gcc.segment5),23,2) is null))
AND ((lpad(gcc.segment6,10,'0') >=lpad(SUBSTR(nvl(:p_from_account,gcc.segment6),26,3),10,'0') or SUBSTR(nvl(:p_from_account,gcc.segment6),26,3) is null) and (lpad(gcc.segment6,10,'0') <=lpad(SUBSTR(nvl(:p_to_account,gcc.segment6),26,3),10,'0') or SUBSTR(nvl(:p_to_account,gcc.segment6),26,3) is null))*/
/* AND ((lpad(gcc.segment1,10,'0') >=lpad(:p_le_from,10,'0') or :p_le_from is null) and (lpad(gcc.segment1,10,'0') <=lpad(:p_le_to,10,'0') or :p_le_to is null))
AND ((lpad(gcc.segment2,10,'0') >=lpad(:p_cc_from,10,'0') or :p_cc_from is null) and (lpad(gcc.segment2,10,'0') <=lpad(:p_cc_to,10,'0') or :p_cc_to is null))
AND (gcc.segment1 not in (:p_excluding_le) OR 'All' IN (:p_excluding_le||'All'))
AND ((lpad(gcc.segment3,10,'0') >=lpad(:p_account_from,10,'0') or :p_account_from is null) and (lpad(gcc.segment3,10,'0') <=lpad(:p_account_to,10,'0') or :p_account_to is null))
AND ((lpad(gcc.segment4,10,'0') >=lpad(:p_service_from,10,'0') or :p_service_from is null) and (lpad(gcc.segment4,10,'0') <=lpad(:p_service_to,10,'0') or :p_service_to is null))
AND ((lpad(gcc.segment5,10,'0') >=lpad(:p_div_from,10,'0') or :p_div_from is null) and (lpad(gcc.segment5,10,'0') <=lpad(:p_div_to,10,'0') or :p_div_to is null))
AND ((lpad(gcc.segment6,10,'0') >=lpad(:p_ic_from,10,'0') or :p_ic_from is null) and (lpad(gcc.segment6,10,'0') <=lpad(:p_ic_to,10,'0') or :p_ic_to is null))
AND ((lpad(gcc.segment7,10,'0') >=lpad(:p_future_from,10,'0') or :p_future_from is null) and (lpad(gcc.segment7,10,'0') <=lpad(:p_future_to,10,'0') or :p_future_to is null))
*/ AND h.je_header_id = get_inv_det.je_header_id(+)
AND jel.je_line_num = get_inv_det.je_line_num(+)
and gjs.user_je_source_name='Projects'
No comments:
Post a Comment