CREATE OR REPLACE PACKAGE BODY APPS.SI_CON_AP_TRIAL_BAL_RPT_PKG
AS
PROCEDURE aa_con_ap_trial_bal_rpt(p_err_buf OUT VARCHAR2
,p_ret_code OUT NUMBER
,p_application_id IN NUMBER
,p_security_id_int_1 IN NUMBER--org_id
,p_definition_code IN VARCHAR2
--,p_as_of_date IN DATE
,p_as_of_date IN VARCHAR2
,p_third_party_id IN NUMBER
,p_chart_of_accounts_id IN NUMBER
,p_ccid IN NUMBER
,p_aging_basis IN VARCHAR2
,p_conversion_type IN VARCHAR2
)
IS
CURSOR cur_ledger_details
IS
SELECT hou.organization_id org_id
,hou.name ou_name
,ledger.ledger_id
,ledger.name ledger_name
,ledger.currency_code
,ledger.configuration_id
,ledger.chart_of_accounts_id
,xla_tb_def.definition_code
FROM hr_operating_units hou
,gl_ledgers ledger
,xla_tb_definitions_vl xla_tb_def
WHERE hou.organization_id = NVL(p_security_id_int_1, hou.organization_id)
AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
AND TRUNC(NVL(hou.date_to, SYSDATE)) >= TRUNC(SYSDATE)
AND hou.set_of_books_id = ledger.ledger_id
AND ledger.ledger_id = xla_tb_def.ledger_id
AND xla_tb_def.definition_code = NVL(p_definition_code, xla_tb_def.definition_code)
--AND hou.organization_id IN (2815, 2816, 3021, 3122, 3321)
AND EXISTS(SELECT 1
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'AA CONSOLE OPCO APTB'
AND flv.lookup_code = hou.organization_id
AND flv.language = USERENV('LANG')
AND flv.enabled_flag = 'Y'
AND TRUNC(NVL(end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
)
AND xla_tb_def.enabled_flag = 'Y'
AND (xla_tb_def.definition_code IN (
SELECT xtd.definition_code
FROM xla_tb_defn_je_sources xtd
,xla_subledgers xs
WHERE xtd.je_source_name = xs.je_source_Name
AND xs.application_id = p_application_id--200 for Payables
)
OR DECODE(p_application_id,101,'GL','OTHERS') = 'GL'
)
/*AND (
(NVL(fnd_profile.value('XLA_USE_LEDGER_SECURITY'), 'N') = 'N')
OR
(NVL(fnd_profile.value('XLA_USE_LEDGER_SECURITY'), 'N') = 'Y'
AND EXISTS(SELECT 1
FROM gl_access_sets gas
,gl_access_set_assignments asa
WHERE gas.access_set_id = asa.access_set_id
AND asa.ledger_id = xla_tb_def.ledger_id
AND (gas.access_set_id = NVL(fnd_profile.value('GL_ACCESS_SET_ID'), '-1')
OR gas.access_set_id = NVL(fnd_profile.value('XLA_GL_SECONDARY_ACCESS_SET_ID'), '-1')
)
)
)
)*/
ORDER BY hou.organization_id
;
CURSOR cur_con_trial_bal_dtls(p_ledger_id IN NUMBER
,p_chart_of_accounts_id IN NUMBER
,p_org_id IN NUMBER
,pp_definition_code IN VARCHAR2
)
IS
SELECT main_query.invoice_id
,main_query.exchange_rate
,main_query.business_unit
,main_query.liability_account
,main_query.vendor_name
,main_query.vendor_number
,main_query.vendor_site_code
,main_query.vendor_type_lookup_code
,main_query.pay_group_lookup_code
,main_query.invoice_num
,main_query.invoice_date
,main_query.invoice_received_date
,main_query.attribute8
,main_query.creation_date
,main_query.gl_date
,main_query.invoice_curr
,main_query.invoice_amt_entered
,main_query.invoice_amt_functional
,main_query.amount_paid
,main_query.remaining_amount
,REPLACE(REPLACE(main_query.invoice_description,CHR(10),''),CHR(13),'') invoice_description
,main_query.invoice_amt_wht_functional
,CASE
WHEN main_query.amount_paid = 0
THEN 'Unpaid'
WHEN main_query.remaining_amount = 0
THEN 'Paid'
WHEN main_query.remaining_amount <> 0
THEN 'Partially Paid'
END invoice_payment_status
,main_query.invoice_type
,main_query.source
,main_query.voucher_num
,main_query.vendor_id
,main_query.ledger_id
,DECODE(p_aging_basis,'Invoice Date', TO_DATE(fnd_date.canonical_to_date(p_as_of_date),'dd-mon-yyyy') - TO_DATE(main_query.invoice_date,'dd-mon-yyyy')
,'GL Date', TO_DATE(fnd_date.canonical_to_date(p_as_of_date),'dd-mon-yyyy') - TO_DATE(main_query.gl_date,'dd-mon-yyyy')
,'Invoice Received Date', TO_DATE(fnd_date.canonical_to_date(p_as_of_date),'dd-mon-yyyy') - TO_DATE(main_query.invoice_received_date,'dd-mon-yyyy')
) +1 aging_days
FROM(SELECT aia.invoice_id
,aia.exchange_rate
,gcc.segment1 business_unit
,gcc.segment3 liability_account
,asu.vendor_name
,asu.segment1 vendor_number
,assa.vendor_site_code
,asu.vendor_type_lookup_code
,aia.pay_group_lookup_code
,aia.invoice_num
,aia.invoice_date
,NVL(TO_DATE(TO_DATE(aia.attribute8,'MM/DD/YYYY'),'DD-Mon-YY') ,invoice_received_date) invoice_received_date
,TO_DATE(TO_DATE(aia.attribute8,'MM/DD/YYYY'),'DD-Mon-YY') attribute8
,aia.creation_date
,aia.gl_date
,aia.invoice_currency_code invoice_curr
,aia.invoice_amount invoice_amt_entered
,aia.invoice_amount * NVL ( aia.exchange_rate, 1 ) invoice_amt_functional
,ROUND(((aia.invoice_amount- NVL ( ap_invoices_utility_pkg.get_amount_withheld ( aia.invoice_id ), 0 )) * NVL(aia.exchange_rate, 1)),2)-NVL((SUM(NVL(tb.acctd_rounded_cr, 0)) - SUM(NVL(tb.acctd_rounded_dr, 0))),0) amount_paid
,SUM((NVL(tb.acctd_rounded_cr, 0)-NVL(tb.acctd_rounded_dr, 0))) remaining_amount
,aia.description invoice_description
,NVL (ap_invoices_utility_pkg.get_amount_withheld ( aia.invoice_id ), 0 ) * NVL ( aia.exchange_rate, 1 ) invoice_amt_wht_functional
,(SELECT flvv.meaning
FROM fnd_lookup_values_vl flvv
WHERE flvv.lookup_code = ap_invoices_utility_pkg.get_payment_status(aia.invoice_id)
AND FLVV.lookup_type = 'INVOICE PAYMENT STATUS'
) invoice_payment_status
,flvv.meaning invoice_type
,aia.source
,aia.doc_sequence_value voucher_num
,aia.vendor_id
,aia.set_of_books_id ledger_id
FROM xla.xla_transaction_entities xte
,ap_invoices_all aia
,gl_ledgers gl
,ap_suppliers asu
,ap_supplier_sites_all assa
,gl_code_combinations gcc
,xla.xla_ae_headers xah
,fnd_lookup_values_vl flvv
,(SELECT /*+ parallel(xtb) leading(xtb) NO_MERGE */
xtb.definition_code
,NVL(xtb.applied_to_entity_id,xtb.source_entity_id) entity_id
,xtb.code_combination_id
,xtb.gl_date
,xtb.ae_header_id
,xtb.source_application_id
,xtb.ledger_id
,xtb.party_id
,xtb.balancing_segment_value
,xtb.natural_account_segment_value
,xtb.cost_center_segment_value
,xtb.intercompany_segment_value
,xtb.management_segment_value
,xtb.event_class_code
,xtb.acctd_rounded_cr
,xtb.acctd_rounded_dr
FROM xla.xla_trial_balances xtb
WHERE --xtb.definition_code = p_definition_code
xtb.definition_code = pp_definition_code
AND xtb.source_application_id = 200
AND xtb.ledger_id = p_ledger_id
AND xtb.gl_date <= fnd_date.canonical_to_date(p_as_of_date)
) tb
WHERE tb.entity_id = xte.entity_id
AND tb.source_application_id = 200
AND xte.entity_code = 'AP_INVOICES'
AND xte.application_id = tb.source_application_id
AND NVL(xte.source_id_int_1,-99) = aia.invoice_id
AND tb.ledger_id = gl.ledger_id
AND gl.ledger_id = p_ledger_id
AND aia.vendor_id = asu.vendor_id
AND aia.vendor_site_id = assa.vendor_site_id
AND asu.vendor_id = assa.vendor_id
AND tb.ae_header_id = xah.ae_header_id
AND gcc.code_combination_id = tb.code_combination_id
AND gcc.chart_of_accounts_id = p_chart_of_accounts_id--Added by Nagaraju Gunti
AND gcc.code_combination_id = NVL(p_ccid, gcc.code_combination_id)
--AND xte.security_id_int_1 = NVL(p_security_id_int_1, xte.security_id_int_1)
AND xte.security_id_int_1 = NVL(p_org_id, xte.security_id_int_1)
AND aia.vendor_id = NVL(p_third_party_id, aia.vendor_id)
--AND aia.invoice_date > p_exl_inv
AND flvv.view_application_id = xte.application_id
AND flvv.lookup_code = aia.invoice_type_lookup_code
AND flvv.lookup_type = 'INVOICE TYPE'
GROUP BY aia.invoice_id
,aia.exchange_rate
,gcc.segment1
,gcc.segment3
,asu.vendor_name
,asu.segment1
,assa.vendor_site_code
,asu.vendor_type_lookup_code
,aia.pay_group_lookup_code
,aia.invoice_num
,aia.invoice_date
,aia.creation_date
,aia.gl_date
,aia.INVOICE_CURRENCY_CODE
,aia.description
,aia.invoice_amount
,aia.invoice_received_date
,aia.attribute8
,NVL(TO_DATE(TO_DATE(aia.attribute8,'MM/DD/YYYY'),'DD-Mon-YY') ,invoice_received_date)
,flvv.meaning
,aia.source
,aia.doc_sequence_value
,aia.vendor_id
,aia.set_of_books_id
HAVING SUM((NVL(tb.acctd_rounded_cr, 0)- NVL(tb.acctd_rounded_dr, 0))) <> 0
) main_query
;
l_reporting_level xla_tb_definitions_vl.name%TYPE;
l_ou_name hr_operating_units.name%TYPE;
l_supplier_name ap_suppliers.vendor_name%TYPE;
l_user_id fnd_user.user_id%TYPE;
l_user_name fnd_user.user_name%TYPE;
l_employee_id fnd_user.employee_id%TYPE;
l_person_party_id fnd_user.person_party_id%TYPE;
l_employee_name per_all_people_f.full_name%TYPE;
l_party_name hz_parties.party_name%TYPE;
l_requested_by VARCHAR2(240);
l_concatenated_po_nums VARCHAR2(2000);
l_concatenated_rcpt_nums VARCHAR2(2000);
l_aging1 NUMBER := 0;
l_aging2 NUMBER := 0;
l_aging3 NUMBER := 0;
l_aging4 NUMBER := 0;
l_aging5 NUMBER := 0;
l_aging6 NUMBER := 0;
l_aging7 NUMBER := 0;
l_aging8 NUMBER := 0;
l_aging9 NUMBER := 0;
l_aging10 NUMBER := 0;
l_aging11 NUMBER := 0;
l_outstanding_entered NUMBER;
l_closing_rate NUMBER;
l_outstanding_functional NUMBER;
l_unrealized_gain_loss NUMBER;
l_net_balance NUMBER;
l_gl_date DATE;
l_conversion_rate gl_daily_rates.conversion_rate%TYPE;
BEGIN
fnd_file.put_line(fnd_file.log, 'Program Started at ' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
l_reporting_level := NULL;
l_ou_name := NULL;
l_supplier_name := NULL;
l_person_party_id := NULL;
l_employee_id := NULL;
l_employee_name := NULL;
l_party_name := NULL;
l_requested_by := NULL;
fnd_file.put_line(fnd_file.log, 'XLA_USE_LEDGER_SECURITY: '||fnd_profile.value('XLA_USE_LEDGER_SECURITY'));
fnd_file.put_line(fnd_file.log, 'GL_ACCESS_SET_ID: '||fnd_profile.value('GL_ACCESS_SET_ID'));
fnd_file.put_line(fnd_file.log, 'XLA_GL_SECONDARY_ACCESS_SET_ID: '||fnd_profile.value('XLA_GL_SECONDARY_ACCESS_SET_ID'));
IF p_definition_code IS NOT NULL THEN
BEGIN
SELECT name
INTO l_reporting_level
FROM xla_tb_definitions_vl
WHERE definition_code = p_definition_code
;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log, 'Error while fetcing Reporting Level Parameter Value.'||SQLERRM);
END;
END IF;
IF p_security_id_int_1 IS NOT NULL THEN
BEGIN
SELECT name
INTO l_ou_name
FROM hr_operating_units
WHERE organization_id = p_security_id_int_1
AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log, 'Error while fetcing Operating Unit Name Parameter Value.'||SQLERRM);
END;
END IF;
IF p_third_party_id IS NULL THEN
l_supplier_name := 'ALL';
ELSE
BEGIN
SELECT vendor_name
INTO l_supplier_name
FROM ap_suppliers
WHERE vendor_id = p_third_party_id
;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log, 'Error while fetcing Supplier Name Parameter Value.'||SQLERRM);
END;
END IF;
l_user_id := fnd_global.user_id;
l_user_name := fnd_global.user_name;
IF l_user_id IS NOT NULL THEN
BEGIN
SELECT fu.person_party_id
,fu.employee_id
INTO l_person_party_id
,l_employee_id
FROM fnd_user fu
WHERE fu.user_id = l_user_id
;
EXCEPTION
WHEN OTHERS THEN
l_person_party_id := NULL;
l_employee_id := NULL;
fnd_file.put_line(fnd_file.log, 'Error while fetcing User Details for logged in User.'||SQLERRM);
END;
IF l_employee_id IS NOT NULL
THEN
BEGIN
SELECT papf.full_name
INTO l_employee_name
FROM per_all_people_f papf
WHERE papf.person_id = l_employee_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(papf.effective_start_date)
AND TRUNC(papf.effective_end_date)
;
EXCEPTION
WHEN OTHERS THEN
l_employee_name := NULL;
fnd_file.put_line(fnd_file.log, 'Error while fetcing Employee Details for logged in User.'||SQLERRM);
END;
ELSIF l_person_party_id IS NOT NULL
THEN
BEGIN
SELECT hp.party_name
INTO l_party_name
FROM hz_parties hp
WHERE hp.party_id = l_person_party_id
;
EXCEPTION
WHEN OTHERS THEN
l_party_name := NULL;
fnd_file.put_line(fnd_file.log, 'Error while fetcing Party Details for logged in User.'||SQLERRM);
END;
/*ELSE
l_requested_by := l_user_name;*/
END IF;
IF l_employee_name IS NOT NULL THEN
l_requested_by := l_employee_name;
ELSIF l_party_name IS NOT NULL THEN
l_requested_by := l_party_name;
ELSE
l_requested_by := l_user_name;
END IF;
END IF;
fnd_file.put_line(fnd_file.output, 'Console AP Trial Balance Report');
fnd_file.put_line(fnd_file.output, 'Report Parameters: ');
fnd_file.put_line(fnd_file.output, 'Reporting Level :^' ||l_reporting_level);
fnd_file.put_line(fnd_file.output, 'Operating Unit Name:^' ||l_ou_name);
fnd_file.put_line(fnd_file.output, 'Supplier Name:^' ||l_supplier_name);
fnd_file.put_line(fnd_file.output, 'As of Date:^' ||fnd_date.canonical_to_date(p_as_of_date));
fnd_file.put_line(fnd_file.output, 'Aging:^' ||p_aging_basis);
fnd_file.put_line(fnd_file.output, 'Submitted Date:^' ||TO_CHAR(SYSDATE, 'DD-MON-RRRR'));
fnd_file.put_line(fnd_file.output, 'Submitted By:^' ||l_requested_by);
--fnd_file.put_line(fnd_file.output, 'Business Unit$Liability Account$Supplier Name$Supplier No$Supplier Site$Supplier Type$Pay Group$Source$Invoice Type$Invoice No$Invoice Creation Date$Invoice Date$Invoice Received Date$Voucher Number$Barcode Number$Purchase Order Number$Receipt Number$Attribute8$GL Date$Currency$Exchange Rate$Invoice Amount - Entered$Invoice Amount - Functional$WHT - Functional$Total Paid Amount - Functional$Remaining Amount - Functional$Payment Status$Invoice Description$Aging Days$0-30 Days$31-60 Days$61-90 Days$91-180 Days$181-365 Days$1-2 Years$2-3 Years$3-4 Years$4-5 Years$5-6 Years$>6 Years$Incountry GL Date$Outstanding Amount(Entered Currency)$Closing Rate(Monthly Closing)$Outstanding Amount(Functional Currency)$Unrealized Gain / Loss$Net Balance (incl.) Revaluation Amount');
fnd_file.put_line(fnd_file.output, 'Business Unit^Liability Account^Supplier Name^Supplier No^Supplier Site^Supplier Type^Pay Group^Source^Invoice Type^Invoice No^Invoice Creation Date^Invoice Date^Invoice Received Date^Voucher Number^Barcode Number^Purchase Order Number^Receipt Number^Attribute8^GL Date^Currency^Exchange Rate^Invoice Amount - Entered^Invoice Amount - Functional^WHT - Functional^Total Paid Amount - Functional^Remaining Amount - Functional^Payment Status^Invoice Description^Aging Days^0-30 Days^31-60 Days^61-90 Days^91-180 Days^181-365 Days^1-2 Years^2-3 Years^3-4 Years^4-5 Years^5-6 Years^>6 Years^Incountry GL Date^Outstanding Amount(Entered Currency)^Closing Rate(Monthly Closing)^Outstanding Amount(Functional Currency)^Unrealized Gain / Loss^Net Balance (incl.) Revaluation Amount');
FOR rec_ledger_details IN cur_ledger_details
LOOP
fnd_file.put_line(fnd_file.log, 'p_ledger_id: '||rec_ledger_details.ledger_id);
fnd_file.put_line(fnd_file.log, 'p_chart_of_accounts_id: '||rec_ledger_details.chart_of_accounts_id);
fnd_file.put_line(fnd_file.log, 'p_org_id: '||rec_ledger_details.org_id);
fnd_file.put_line(fnd_file.log, 'pp_definition_code: '||rec_ledger_details.definition_code);
FOR rec_con_trial_bal_dtls IN cur_con_trial_bal_dtls(rec_ledger_details.ledger_id
,rec_ledger_details.chart_of_accounts_id
,rec_ledger_details.org_id
,rec_ledger_details.definition_code
)
LOOP
l_concatenated_po_nums := NULL;
l_concatenated_rcpt_nums := NULL;
l_aging1 := 0;
l_aging2 := 0;
l_aging3 := 0;
l_aging4 := 0;
l_aging5 := 0;
l_aging6 := 0;
l_aging7 := 0;
l_aging8 := 0;
l_aging9 := 0;
l_aging10 := 0;
l_aging11 := 0;
l_outstanding_entered := NULL;
l_closing_rate := NULL;
l_outstanding_functional:= NULL;
l_unrealized_gain_loss := 0;
l_net_balance := 0;
l_gl_date := NULL;
l_conversion_rate := NULL;
BEGIN
FOR rec_po_nums
IN (SELECT poh.segment1 po_number
FROM ap_invoices_all api
,ap_invoice_distributions_all aid
,ap_invoice_lines_all ail
,po_distributions_all pod
,po_headers_all poh
WHERE api.invoice_id = ail.invoice_id
AND aid.invoice_id = ail.invoice_id
AND api.vendor_id = rec_con_trial_bal_dtls.vendor_id
AND aid.invoice_line_number = ail.line_number
AND aid.po_distribution_id = pod.po_distribution_id
AND pod.po_header_id = poh.po_header_id
AND api.invoice_id = rec_con_trial_bal_dtls.invoice_id
AND api.invoice_amount != 0
AND api.org_id = rec_ledger_details.org_id
AND api.org_id = aid.org_id
AND ail.discarded_flag <> 'Y'
UNION
SELECT aid.po_number po_number
FROM ap_invoices_interface api
,ap_invoice_lines_interface aid
WHERE api.invoice_id = aid.invoice_id
AND api.vendor_id = rec_con_trial_bal_dtls.vendor_id
AND api.invoice_num = rec_con_trial_bal_dtls.invoice_id
AND api.invoice_amount != 0
AND aid.po_number IS NOT NULL
AND api.org_id = rec_ledger_details.org_id
AND api.org_id = aid.org_id
)
LOOP
l_concatenated_po_nums := l_concatenated_po_nums || ',' ||' '|| rec_po_nums.po_number;
END LOOP;
l_concatenated_po_nums := RTRIM(LTRIM (l_concatenated_po_nums, ','),',');
EXCEPTION
WHEN OTHERS THEN
l_concatenated_po_nums := NULL;
fnd_file.put_line(fnd_file.log, 'Error while fetcing PO Number.'||SUBSTR(SQLERRM, 500));
END;
BEGIN
FOR rec_rcpt_nums IN
(SELECT DISTINCT rsh.receipt_num
FROM rcv_shipment_headers rsh
,rcv_transactions rt
,ap_invoice_lines_all aila
,ap_invoice_distributions_all aida
WHERE rt.shipment_header_id = rsh.shipment_header_id
AND rt.transaction_id = aida.rcv_transaction_id
AND aida.invoice_id = aila.invoice_id
AND aida.invoice_line_number = aila.line_number
AND aila.invoice_id = rec_con_trial_bal_dtls.invoice_id
AND aida.org_id = rec_ledger_details.org_id
AND aila.discarded_flag <> 'Y'
AND aida.distribution_line_number = (SELECT MIN (aid1.distribution_line_number)
FROM ap_invoice_distributions_all aid1
WHERE aid1.invoice_id = rec_con_trial_bal_dtls.invoice_id
)
)
LOOP
l_concatenated_rcpt_nums := l_concatenated_rcpt_nums||', '||rec_rcpt_nums.receipt_num;
END LOOP;
l_concatenated_rcpt_nums := rtrim(ltrim (l_concatenated_rcpt_nums, ','),',');
EXCEPTION
WHEN OTHERS THEN
l_concatenated_po_nums := NULL;
fnd_file.put_line(fnd_file.log, 'Error while fetcing PO Number.'||SUBSTR(SQLERRM, 500));
END;
BEGIN
IF rec_con_trial_bal_dtls.aging_days >= 0
AND rec_con_trial_bal_dtls.aging_days <= 30
THEN
l_aging1 := rec_con_trial_bal_dtls.remaining_amount;
ELSIF rec_con_trial_bal_dtls.aging_days > 30
AND rec_con_trial_bal_dtls.aging_days <= 60
THEN
l_aging2 := rec_con_trial_bal_dtls.remaining_amount;
ELSIF rec_con_trial_bal_dtls.aging_days > 60
AND rec_con_trial_bal_dtls.aging_days <= 90
THEN
l_aging3 := rec_con_trial_bal_dtls.remaining_amount;
ELSIF rec_con_trial_bal_dtls.aging_days > 90
AND rec_con_trial_bal_dtls.aging_days <= 180
THEN
l_aging4 := rec_con_trial_bal_dtls.remaining_amount;
ELSIF rec_con_trial_bal_dtls.aging_days > 180
AND rec_con_trial_bal_dtls.aging_days <= 365
THEN
l_aging5 := rec_con_trial_bal_dtls.remaining_amount;
ELSIF rec_con_trial_bal_dtls.aging_days > 365
AND rec_con_trial_bal_dtls.aging_days <= 730
THEN
l_aging6 := rec_con_trial_bal_dtls.remaining_amount;
ELSIF rec_con_trial_bal_dtls.aging_days > 730
AND rec_con_trial_bal_dtls.aging_days <= 1095
THEN
l_aging7 := rec_con_trial_bal_dtls.remaining_amount;
ELSIF rec_con_trial_bal_dtls.aging_days > 1095
AND rec_con_trial_bal_dtls.aging_days <= 1460
THEN
l_aging8 := rec_con_trial_bal_dtls.remaining_amount;
ELSIF rec_con_trial_bal_dtls.aging_days > 1460
AND rec_con_trial_bal_dtls.aging_days <= 1825
THEN
l_aging9 := rec_con_trial_bal_dtls.remaining_amount;
ELSIF rec_con_trial_bal_dtls.aging_days > 1825
AND rec_con_trial_bal_dtls.aging_days <= 2190
THEN
l_aging10 := rec_con_trial_bal_dtls.remaining_amount;
ELSIF rec_con_trial_bal_dtls.aging_days > 2190
THEN
l_aging11 := rec_con_trial_bal_dtls.remaining_amount;
END IF;
END;
BEGIN
IF rec_ledger_details.currency_code = rec_con_trial_bal_dtls.invoice_curr THEN
l_conversion_rate := 1;
ELSE
BEGIN
SELECT dr.conversion_rate
INTO l_conversion_rate
FROM gl_daily_rates dr
,gl_daily_conversion_types dct
WHERE dr.conversion_type = dct.conversion_type
AND dct.user_conversion_type = p_conversion_type
AND dr.to_currency = rec_ledger_details.currency_code
AND dr.from_currency = rec_con_trial_bal_dtls.invoice_curr
AND TRUNC(dr.conversion_date) = TRUNC(fnd_date.canonical_to_date(p_as_of_date))
;
EXCEPTION
WHEN OTHERS THEN
l_conversion_rate := 0;
fnd_file.put_line(fnd_file.log, 'Error while fetcing Conversion Rate.'||SUBSTR(SQLERRM, 500));
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
l_conversion_rate := 0;
END;
IF rec_ledger_details.currency_code = rec_con_trial_bal_dtls.invoice_curr THEN
l_outstanding_entered := 1;
l_closing_rate := 1;
l_outstanding_functional := rec_con_trial_bal_dtls.remaining_amount;
l_unrealized_gain_loss := 0;
l_net_balance := l_outstanding_functional;
ELSE
l_outstanding_entered := rec_con_trial_bal_dtls.remaining_amount/rec_con_trial_bal_dtls.exchange_rate;
l_closing_rate := l_conversion_rate;
l_outstanding_functional := l_outstanding_entered * l_conversion_rate;
l_unrealized_gain_loss := l_outstanding_functional - rec_con_trial_bal_dtls.remaining_amount;
l_net_balance := l_unrealized_gain_loss + rec_con_trial_bal_dtls.remaining_amount;
END IF;
BEGIN
SELECT af.gl_date
INTO l_gl_date
FROM asi_forex_t af
,gl_code_combinations gcc
,ap_invoices_all aia
WHERE segment1 = af.business_unit
AND business_unit = rec_con_trial_bal_dtls.business_unit
AND aia.invoice_id = rec_con_trial_bal_dtls.invoice_id
AND aia.org_id = rec_ledger_details.org_id
AND liability_account = segment3
AND liability_account = rec_con_trial_bal_dtls.liability_account
AND (aia.invoice_num = af.invoice_num OR '0'||af.invoice_num = aia.invoice_num)
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS THEN
l_gl_date := NULL;
END;
fnd_file.put_line(fnd_file.output, rec_con_trial_bal_dtls.business_unit
||'^'||rec_con_trial_bal_dtls.liability_account
||'^'||rec_con_trial_bal_dtls.vendor_name
||'^'||rec_con_trial_bal_dtls.vendor_number
||'^'||rec_con_trial_bal_dtls.vendor_site_code
||'^'||rec_con_trial_bal_dtls.vendor_type_lookup_code
||'^'||rec_con_trial_bal_dtls.pay_group_lookup_code
||'^'||rec_con_trial_bal_dtls.source
||'^'||rec_con_trial_bal_dtls.invoice_type
||'^'||rec_con_trial_bal_dtls.invoice_num
||'^'||rec_con_trial_bal_dtls.creation_date
||'^'||rec_con_trial_bal_dtls.invoice_date
||'^'||rec_con_trial_bal_dtls.invoice_received_date
||'^'||rec_con_trial_bal_dtls.voucher_num
||'^'||rec_con_trial_bal_dtls.invoice_id
||'^'||l_concatenated_po_nums
||'^'||l_concatenated_rcpt_nums
||'^'||rec_con_trial_bal_dtls.attribute8
||'^'||rec_con_trial_bal_dtls.gl_date
||'^'||rec_con_trial_bal_dtls.invoice_curr
||'^'||rec_con_trial_bal_dtls.exchange_rate
||'^'||rec_con_trial_bal_dtls.invoice_amt_entered
||'^'||rec_con_trial_bal_dtls.invoice_amt_functional
||'^'||rec_con_trial_bal_dtls.invoice_amt_wht_functional
||'^'||rec_con_trial_bal_dtls.amount_paid
||'^'||rec_con_trial_bal_dtls.remaining_amount
||'^'||rec_con_trial_bal_dtls.invoice_payment_status
||'^'||REPLACE(REPLACE(REPLACE(REPLACE(regexp_replace(rec_con_trial_bal_dtls.invoice_description,'([^[:graph:]|^[:blank:]])',' '),CHR(10),''),CHR(13),''),'"',''),'|',' ')
||'^'||rec_con_trial_bal_dtls.aging_days
||'^'||l_aging1
||'^'||l_aging2
||'^'||l_aging3
||'^'||l_aging4
||'^'||l_aging5
||'^'||l_aging6
||'^'||l_aging7
||'^'||l_aging8
||'^'||l_aging9
||'^'||l_aging10
||'^'||l_aging11
||'^'||l_gl_date
||'^'||l_outstanding_entered
||'^'||l_closing_rate
||'^'||l_outstanding_functional
||'^'||l_unrealized_gain_loss
||'^'||l_net_balance
);
END LOOP;
END LOOP;
fnd_file.put_line(fnd_file.log, 'Program Ended at ' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log, 'Main Error in asi_con_ap_trial_bal_rpt_pkg.aa_con_ap_trial_bal_rpt procedure. Error Message: '||SQLERRM);
END aa_con_ap_trial_bal_rpt;
END SI_CON_AP_TRIAL_BAL_RPT_PKG;
/
