Scripts For Oracle Applications: GL_IMPORT

Please find a package that will provide you various transaction details when drilling down from GL

Thanks
Anil Passi

CREATE OR REPLACE PACKAGE BODY xx_gl_to_modue_trx_details_pkg
AS
FUNCTION get_transaction_date (
p_source IN VARCHAR2,
p_category IN VARCHAR2,
p_actual_flag IN VARCHAR2,
p_je_header_id IN NUMBER,
p_je_line_num IN NUMBER,
p_reference_1 IN VARCHAR2,
p_reference_2 IN VARCHAR2,
p_reference_3 IN VARCHAR2,
p_reference_4 IN VARCHAR2,
p_reference_5 IN VARCHAR2,
p_reference_6 IN VARCHAR2,
p_reference_7 IN VARCHAR2,
p_reference_10 IN VARCHAR2,
p_gl_line_effective_date IN DATE
)
RETURN DATE
IS
l_txn_date DATE := NULL;
l_txn_type_name VARCHAR2 (30) := NULL;

CURSOR cu_ap_inv_date
IS
SELECT ai.invoice_date
FROM ap_invoices_all ai
WHERE ai.invoice_id = TO_NUMBER (p_reference_2);

CURSOR cu_ap_chk_date
IS
SELECT c.check_date
FROM ap_checks_all c
WHERE c.check_id = TO_NUMBER (p_reference_3);

CURSOR cu_ap_clr_date
IS
SELECT c.cleared_date
FROM ap_checks_all c
WHERE c.check_id = TO_NUMBER (p_reference_3);

CURSOR cu_po_app_date
IS
SELECT poh.approved_date
FROM po_headers_all poh
WHERE poh.po_header_id = TO_NUMBER (p_reference_2);

CURSOR cu_poreq_enc_date
IS
SELECT reqd.gl_encumbered_date
FROM po_req_distributions_all reqd
WHERE reqd.distribution_id = TO_NUMBER (p_reference_3);

CURSOR cu_inv_tx_type
IS
SELECT mtt.transaction_type_name
FROM mtl_material_transactions mt, mtl_transaction_types mtt
WHERE mtt.transaction_type_id = mt.transaction_type_id
AND mt.transaction_id = TO_NUMBER (p_reference_3);

CURSOR cu_inv_po_rcpt_date
IS
SELECT rsh.creation_date /* this = rcv_vrc_hds_v.receipt_date */
FROM rcv_shipment_headers rsh,
rcv_transactions rt,
mtl_material_txns_val_v mt
WHERE rt.shipment_header_id = rsh.shipment_header_id
AND rt.transaction_id = mt.rcv_transaction_id
AND mt.transaction_source_type_name = 'Purchase order'
AND mt.transaction_type_name = 'Purchase order receipt'
AND mt.transaction_id = TO_NUMBER (p_reference_3);

CURSOR cu_inv_misc_rcpt_date
IS
SELECT mt.creation_date
FROM mtl_material_txns_val_v mt
WHERE mt.transaction_source_type_name = 'Inventory'
AND mt.transaction_type_name = 'Miscellaneous receipt'
AND mt.transaction_id = TO_NUMBER (p_reference_3);

CURSOR cu_inv_po_req_date
IS
SELECT MAX (reqd.gl_encumbered_date)
FROM po_req_distributions_all reqd,
po_requisition_lines_all reql,
po_requisition_headers_all reqh,
so_headers_all soh,
mtl_material_txns_val_v mt
WHERE reqd.requisition_line_id = reql.requisition_line_id
AND reql.requisition_header_id = reqh.requisition_header_id
AND reqh.segment1 = soh.original_system_reference
AND soh.header_id = mt.transaction_reference
AND mt.transaction_source_type_name = 'Internal order'
AND mt.transaction_type_name = 'Internal order issue'
AND mt.transaction_id = TO_NUMBER (p_reference_3);

CURSOR cu_ar_adj_date
IS
SELECT adj.apply_date
FROM ar_adjustments_all adj
WHERE adj.adjustment_id = TO_NUMBER (p_reference_3);

CURSOR cu_ar_misc_cash_rcpt_date
IS
SELECT cr.receipt_date
FROM ar_cash_receipts_all cr
WHERE cr.cash_receipt_id = TO_NUMBER (p_reference_2);

CURSOR cu_ar_rx_app_apply_date
IS
SELECT ra.apply_date
FROM ar_receivable_applications_all ra
WHERE ra.receivable_application_id = TO_NUMBER (p_reference_3);

CURSOR cu_ar_cash_rcpt_date
IS
SELECT cr.receipt_date
FROM ar_cash_receipts_all cr, ar_cash_receipt_history_all crh
WHERE cr.cash_receipt_id = crh.cash_receipt_id
AND crh.cash_receipt_id = TO_NUMBER (p_reference_2);

CURSOR cu_ar_cust_trx_date
IS
SELECT ct.trx_date
FROM ra_customer_trx_all ct
WHERE ct.customer_trx_id = TO_NUMBER (p_reference_2);
/* CURSOR cu_gl_effective_date IS
SELECT gl.effective_date
FROM gl_je_lines gl
WHERE gl.je_header_id =
p_je_header_id
AND gl.je_line_num =
p_je_line_num;*/

--Added by Khavi on 21-Aug-07 as part of fixed assets implementation
CURSOR cu_fa_trx_date
IS
SELECT th.transaction_date_entered
FROM fa_transaction_headers th
WHERE th.asset_id = TO_NUMBER (p_reference_2);
BEGIN
IF p_source = 'Payables'
THEN
IF (UPPER (p_reference_6) = 'AP INVOICES' AND p_actual_flag = 'A')
THEN
-- get invoice date
OPEN cu_ap_inv_date;

FETCH cu_ap_inv_date
INTO l_txn_date;

CLOSE cu_ap_inv_date;
ELSIF p_reference_6 IN
('AP Reconciled Payments', 'AP PAYMENTS JUST RECONCILED')
THEN
-- get cleared date
OPEN cu_ap_clr_date;

FETCH cu_ap_clr_date
INTO l_txn_date;

CLOSE cu_ap_clr_date;
ELSIF ( ( p_reference_6 IS NULL
OR UPPER (p_reference_6) = 'AP INVOICES'
)
AND p_category = 'Purchase Invoices'
AND p_actual_flag = 'E'
)
THEN
/* non PO invoice encumbrance */
-- get invoice date
OPEN cu_ap_inv_date;

FETCH cu_ap_inv_date
INTO l_txn_date;

CLOSE cu_ap_inv_date;
ELSIF UPPER (p_reference_6) = 'AP PAYMENTS'
THEN
-- moved to bottom of if statement else all logic will not get processed
-- get check date
OPEN cu_ap_chk_date;

FETCH cu_ap_chk_date
INTO l_txn_date;

CLOSE cu_ap_chk_date;
END IF;
ELSIF p_source = 'Purchasing'
THEN
IF p_reference_1 = 'PO'
THEN
-- get date PO approved
OPEN cu_po_app_date;

FETCH cu_po_app_date
INTO l_txn_date;

CLOSE cu_po_app_date;
ELSIF p_reference_1 = 'REQ'
THEN
-- get date req line reserved (encumbered)
OPEN cu_poreq_enc_date;

FETCH cu_poreq_enc_date
INTO l_txn_date;

CLOSE cu_poreq_enc_date;
END IF;
ELSIF p_source = 'Inventory'
THEN
-- get inventory transaction type name
OPEN cu_inv_tx_type;

FETCH cu_inv_tx_type
INTO l_txn_type_name;

CLOSE cu_inv_tx_type;

IF l_txn_type_name = 'Purchase order receipt'
THEN
-- get receipt date
OPEN cu_inv_po_rcpt_date;

FETCH cu_inv_po_rcpt_date
INTO l_txn_date;

CLOSE cu_inv_po_rcpt_date;
ELSIF l_txn_type_name = 'Miscellaneous receipt'
THEN
-- get receipt date
OPEN cu_inv_misc_rcpt_date;

FETCH cu_inv_misc_rcpt_date
INTO l_txn_date;

CLOSE cu_inv_misc_rcpt_date;
ELSIF l_txn_type_name = 'Internal order issue'
THEN
-- get requisition date
OPEN cu_inv_po_req_date;

FETCH cu_inv_po_req_date
INTO l_txn_date;

CLOSE cu_inv_po_req_date;
END IF;
ELSIF p_source = 'Receivables'
THEN
IF p_reference_10 = 'AR_ADJUSTMENTS'
THEN
-- get adjustment creation date
OPEN cu_ar_adj_date;

FETCH cu_ar_adj_date
INTO l_txn_date;

CLOSE cu_ar_adj_date;
ELSIF p_reference_10 = 'AR_MISC_CASH_DISTRIBUTIONS'
THEN
-- get receipt date
OPEN cu_ar_misc_cash_rcpt_date;

FETCH cu_ar_misc_cash_rcpt_date
INTO l_txn_date;

CLOSE cu_ar_misc_cash_rcpt_date;
ELSIF p_reference_10 = 'AR_RECEIVABLE_APPLICATIONS'
THEN
-- get application date
OPEN cu_ar_rx_app_apply_date;

FETCH cu_ar_rx_app_apply_date
INTO l_txn_date;

CLOSE cu_ar_rx_app_apply_date;
ELSIF p_reference_10 = 'AR_CASH_RECEIPT_HISTORY'
THEN
-- get receipt date
OPEN cu_ar_cash_rcpt_date;

FETCH cu_ar_cash_rcpt_date
INTO l_txn_date;

CLOSE cu_ar_cash_rcpt_date;
ELSIF p_reference_10 = 'RA_CUST_TRX_LINE_GL_DIST'
THEN
-- get invoice/credit memo etc. date
OPEN cu_ar_cust_trx_date;

FETCH cu_ar_cust_trx_date
INTO l_txn_date;

CLOSE cu_ar_cust_trx_date;
END IF;

ELSIF p_source = 'Assets'
THEN
OPEN cu_fa_trx_date;
FETCH cu_fa_trx_date
INTO l_txn_date;
CLOSE cu_fa_trx_date;
END IF;

IF l_txn_date IS NULL
THEN
l_txn_date := p_gl_line_effective_date;
END IF;

RETURN l_txn_date;
EXCEPTION
WHEN INVALID_NUMBER
THEN
RETURN NULL;
END get_transaction_date;

FUNCTION fn_sel_txn_desc (
p_source IN VARCHAR2,
p_category IN VARCHAR2,
p_actual_flag IN VARCHAR2,
p_je_header_id IN NUMBER,
p_je_line_num IN NUMBER,
p_reference_1 IN VARCHAR2,
p_reference_2 IN VARCHAR2,
p_reference_3 IN VARCHAR2,
p_reference_4 IN VARCHAR2,
p_reference_5 IN VARCHAR2,
p_reference_6 IN VARCHAR2,
p_reference_7 IN VARCHAR2,
p_reference_10 IN VARCHAR2,
p_je_description IN VARCHAR2 --DEFAULT NULL
)
RETURN VARCHAR2
IS
l_txn_desc VARCHAR2 (240) := NULL;
l_txn_type_name VARCHAR2 (30) := NULL;

CURSOR cu_ap_inv_line_desc
IS
SELECT aid.description
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = TO_NUMBER (p_reference_2)
AND aid.distribution_line_number = TO_NUMBER (p_reference_3);

CURSOR cu_po_line_desc
IS
SELECT pol.item_description
FROM po_lines_all pol, po_distributions_all pod
WHERE pod.po_distribution_id = TO_NUMBER (p_reference_3)
AND pol.po_line_id = pod.po_line_id
AND pol.po_header_id = TO_NUMBER (p_reference_2);

CURSOR cu_poreq_line_desc
IS
SELECT reql.item_description
FROM po_requisition_lines_all reql, po_req_distributions_all reqd
WHERE reqd.distribution_id = TO_NUMBER (p_reference_3)
AND reql.requisition_line_id = reqd.requisition_line_id
AND reql.requisition_header_id = TO_NUMBER (p_reference_2);

CURSOR cu_inv_tx_type
IS
SELECT mtt.transaction_type_name
FROM mtl_material_transactions mt, mtl_transaction_types mtt
WHERE mtt.transaction_type_id = mt.transaction_type_id
AND mt.transaction_id = TO_NUMBER (p_reference_3);

CURSOR cu_inv_misc_rcpt_desc
IS
SELECT NVL (m.reason_name, '-')
|| '-'
|| NVL (m.transaction_reference, '-')
FROM mtl_material_txns_val_v m
WHERE m.transaction_source_type_name = 'Inventory'
AND m.transaction_type_name = 'Miscellaneous receipt'
AND m.transaction_id = TO_NUMBER (p_reference_3);

CURSOR cu_ar_adj_cmmt
IS
SELECT adj.comments
FROM ar_adjustments_all adj
WHERE adj.adjustment_id = TO_NUMBER (p_reference_3);
CURSOR cu_ar_misc_cash_act_type
IS
SELECT comments
FROM ar_misc_cash_distributions_all
WHERE cash_receipt_id = TO_NUMBER (p_reference_2);

CURSOR cu_ar_rx_app_applied_to_trx
IS
SELECT ct_app.trx_number
FROM ra_customer_trx_all ct_app, ar_receivable_applications_all ra
WHERE ra.receivable_application_id = TO_NUMBER (p_reference_3)
AND ct_app.customer_trx_id = ra.applied_customer_trx_id;

CURSOR cu_ar_cash_rcpt_type
IS
SELECT DECODE (cr.TYPE,
'MISC', 'Miscellaneous',
'CASH', 'Cash',
NULL
)
FROM ar_cash_receipts_all cr, ar_cash_receipt_history_all crh
WHERE cr.cash_receipt_id = crh.cash_receipt_id
AND crh.cash_receipt_id = TO_NUMBER (p_reference_2);

CURSOR cu_ar_cust_trx_line_desc
IS
SELECT NVL (ctl.description, INITCAP (ctl.line_type))
FROM ra_customer_trx_lines_all ctl,
ra_cust_trx_line_gl_dist_all ctlgd
WHERE ctl.customer_trx_line_id = ctlgd.customer_trx_line_id
AND ctlgd.cust_trx_line_gl_dist_id = TO_NUMBER (p_reference_3);

CURSOR misc_receipts_comments
IS
SELECT mcd.comments
FROM ar_gl_misc_cash_distributions mcd
WHERE mcd.je_header_id = p_je_header_id
AND mcd.je_line_num = p_je_line_num;

--Added by Khavi on 21-Aug-07 as part of fixed assets implementation
CURSOR cu_fa_asset_desc
IS
SELECT fa.description
FROM fa_additions_tl fa
WHERE fa.asset_id = TO_NUMBER (p_reference_2);

BEGIN
IF p_source = 'Payables'
THEN
IF (UPPER (p_reference_6) = 'AP INVOICES' AND p_actual_flag = 'A')
THEN
-- get invoice line desc
OPEN cu_ap_inv_line_desc;

FETCH cu_ap_inv_line_desc
INTO l_txn_desc;

CLOSE cu_ap_inv_line_desc;
ELSIF UPPER (p_reference_6) = 'AP PAYMENTS'
THEN
-- get check number
l_txn_desc := p_reference_4;
ELSIF p_reference_6 IN
('AP Reconciled Payments', 'AP PAYMENTS JUST RECONCILED')
THEN
l_txn_desc := NULL;
ELSIF ( ( p_reference_6 IS NULL
OR UPPER (p_reference_6) = 'AP INVOICES'
)
AND p_category = 'Purchase Invoices'
AND p_actual_flag = 'E'
)
THEN
/* non PO invoice encumbrance */
-- get invoice line desc
OPEN cu_ap_inv_line_desc;

FETCH cu_ap_inv_line_desc
INTO l_txn_desc;

CLOSE cu_ap_inv_line_desc;
END IF;
ELSIF p_source = 'Purchasing'
THEN
IF p_reference_1 = 'PO'
THEN
-- get po line desc
OPEN cu_po_line_desc;

FETCH cu_po_line_desc
INTO l_txn_desc;

CLOSE cu_po_line_desc;
ELSIF p_reference_1 = 'REQ'
THEN
-- get po req line desc
OPEN cu_poreq_line_desc;

FETCH cu_poreq_line_desc
INTO l_txn_desc;

CLOSE cu_poreq_line_desc;
END IF;
ELSIF p_source = 'Inventory'
THEN
-- get inventory transaction type name
OPEN cu_inv_tx_type;

FETCH cu_inv_tx_type
INTO l_txn_type_name;

CLOSE cu_inv_tx_type;

IF l_txn_type_name = 'Purchase order receipt'
THEN
l_txn_desc := NULL;
ELSIF l_txn_type_name = 'Miscellaneous receipt'
THEN
-- get rcpt reason and referencce
OPEN cu_inv_misc_rcpt_desc;

FETCH cu_inv_misc_rcpt_desc
INTO l_txn_desc;

CLOSE cu_inv_misc_rcpt_desc;
ELSIF l_txn_type_name = 'Internal order issue'
THEN
l_txn_desc := NULL;
END IF;
ELSIF p_source = 'Receivables'
THEN
IF p_category = 'Misc Receipts'
THEN
-- (JJS:11/09/00) get misc receipts comments column (SUP-00309)
OPEN misc_receipts_comments;

FETCH misc_receipts_comments
INTO l_txn_desc;

CLOSE misc_receipts_comments;
ELSIF p_reference_10 = 'AR_ADJUSTMENTS'
THEN
-- get adjustment comments
OPEN cu_ar_adj_cmmt;

FETCH cu_ar_adj_cmmt
INTO l_txn_desc;

CLOSE cu_ar_adj_cmmt;
ELSIF p_reference_10 = 'AR_MISC_CASH_DISTRIBUTIONS'
THEN
-- get activity type
OPEN cu_ar_misc_cash_act_type;

FETCH cu_ar_misc_cash_act_type
INTO l_txn_desc;

CLOSE cu_ar_misc_cash_act_type;
ELSIF p_reference_10 = 'AR_RECEIVABLE_APPLICATIONS'
THEN
-- get trx number to which application was made
OPEN cu_ar_rx_app_applied_to_trx;

FETCH cu_ar_rx_app_applied_to_trx
INTO l_txn_desc;

CLOSE cu_ar_rx_app_applied_to_trx;
ELSIF p_reference_10 = 'AR_CASH_RECEIPT_HISTORY'
THEN
-- get receipt type
OPEN cu_ar_cash_rcpt_type;

FETCH cu_ar_cash_rcpt_type
INTO l_txn_desc;

CLOSE cu_ar_cash_rcpt_type;
ELSIF p_reference_10 = 'RA_CUST_TRX_LINE_GL_DIST'
THEN
-- get invoice/credit memo etc. line desc
OPEN cu_ar_cust_trx_line_desc;

FETCH cu_ar_cust_trx_line_desc
INTO l_txn_desc;

CLOSE cu_ar_cust_trx_line_desc;
END IF;

--Added by Khavi on 21-Aug-07 as part of fixed assets implementation
ELSIF p_source = 'Assets'
THEN
OPEN cu_fa_asset_desc;
FETCH cu_fa_asset_desc
INTO l_txn_desc;
CLOSE cu_fa_asset_desc;

END IF;

-- if the txn isnt covered by above (l_txn_desc is null)
-- then get gl journal line description
IF l_txn_desc IS NULL
AND ( NVL (p_reference_6, 'aa') NOT IN
('AP Reconciled Payments', 'AP PAYMENTS JUST RECONCILED')
AND NVL (l_txn_type_name, 'aa') != 'Purchase order receipt'
AND NVL (l_txn_type_name, 'aa') != 'Internal order issue'
)
THEN
/* OPEN cu_gl_line_desc;
FETCH cu_gl_line_desc
INTO l_txn_desc;
CLOSE cu_gl_line_desc;*/
--Sep06, apassi returning description as is
l_txn_desc := p_je_description;
END IF;

RETURN l_txn_desc;
EXCEPTION
WHEN INVALID_NUMBER
THEN
RETURN NULL;
END fn_sel_txn_desc;

/*--------------------------------------------------------------------*/
FUNCTION fn_sel_txn_no (
p_source IN VARCHAR2,
p_category IN VARCHAR2,
p_actual_flag IN VARCHAR2,
p_je_header_id IN NUMBER,
p_je_line_num IN NUMBER,
p_reference_1 IN VARCHAR2,
p_reference_2 IN VARCHAR2,
p_reference_3 IN VARCHAR2,
p_reference_4 IN VARCHAR2,
p_reference_5 IN VARCHAR2,
p_reference_6 IN VARCHAR2,
p_reference_7 IN VARCHAR2,
p_reference_10 IN VARCHAR2
)
RETURN VARCHAR2
IS
l_txn_no VARCHAR2 (240) := NULL;
l_txn_type_name VARCHAR2 (30) := NULL;

CURSOR cu_ap_inv_voucher_no
IS
SELECT ai.doc_sequence_value
FROM ap_invoices_all ai
WHERE ai.invoice_id = TO_NUMBER (p_reference_2);

CURSOR cu_ap_chk_voucher_no
IS
SELECT ai.doc_sequence_value
FROM ap_invoices_all ai
WHERE ai.invoice_id = TO_NUMBER (p_reference_2);

CURSOR cu_inv_tx_type
IS
SELECT mtt.transaction_type_name
FROM mtl_material_transactions mt, mtl_transaction_types mtt
WHERE mtt.transaction_type_id = mt.transaction_type_id
AND mt.transaction_id = TO_NUMBER (p_reference_3);

CURSOR cu_inv_po_rcpt_no
IS
SELECT rsh.receipt_num
FROM rcv_shipment_headers rsh,
rcv_transactions rt,
mtl_material_txns_val_v mt
WHERE mt.transaction_id = TO_NUMBER (p_reference_3)
AND mt.transaction_source_type_name = 'Purchase order'
AND mt.transaction_type_name = 'Purchase order receipt'
AND rt.transaction_id = mt.rcv_transaction_id
AND rt.shipment_header_id = rsh.shipment_header_id;

CURSOR cu_inv_poreq_no
IS
SELECT reqh.segment1
FROM po_requisition_headers_all reqh,
so_headers_all soh,
mtl_material_txns_val_v mt
WHERE reqh.segment1 = soh.original_system_reference
AND soh.header_id = mt.transaction_reference
AND mt.transaction_source_type_name = 'Internal order'
AND mt.transaction_type_name = 'Internal order issue'
AND mt.transaction_id = TO_NUMBER (p_reference_3);

/* Note that whilst some of the AR cursors are the same
they have been declared separately to make code easier
to maintain in case the data to be retrieved changes
for certain tx's */
CURSOR cu_ar_adj_no
IS
SELECT adj.adjustment_number
FROM ar_adjustments_all adj
WHERE adj.adjustment_id = TO_NUMBER (p_reference_3);

CURSOR cu_ar_misc_cash_rcpt_no
IS
SELECT cr.receipt_number
FROM ar_cash_receipts_all cr
WHERE cr.cash_receipt_id = TO_NUMBER (p_reference_2);

CURSOR cu_ar_cust_trx_no
IS
SELECT ct.trx_number
FROM ra_customer_trx_all ct, ra_cust_trx_line_gl_dist_all ctlgd
WHERE ct.customer_trx_id = ctlgd.customer_trx_id
AND ctlgd.cust_trx_line_gl_dist_id = TO_NUMBER (p_reference_3);

CURSOR cu_po_number
IS
SELECT NVL (segment1, NULL)
FROM po_headers_all poh
WHERE poh.po_header_id = TO_NUMBER (p_reference_2);

--Added by Khavi on 21-Aug-07 as part of fixed assets implementation
CURSOR cu_fa_asset_number
IS
SELECT fa.asset_number
FROM fa_additions_b fa
WHERE fa.asset_id = TO_NUMBER (p_reference_2);

BEGIN
IF p_source = 'Payables'
THEN
IF (UPPER (p_reference_6) = 'AP INVOICES' AND p_actual_flag = 'A')
THEN
-- get invoice voucher no
OPEN cu_ap_inv_voucher_no;

FETCH cu_ap_inv_voucher_no
INTO l_txn_no;

CLOSE cu_ap_inv_voucher_no;
ELSIF UPPER (p_reference_6) = 'AP PAYMENTS'
THEN
-- get invoice voucher no of paid invoice
OPEN cu_ap_chk_voucher_no;

FETCH cu_ap_chk_voucher_no
INTO l_txn_no;

CLOSE cu_ap_chk_voucher_no;
ELSIF p_reference_6 IN
('AP Reconciled Payments', 'AP PAYMENTS JUST RECONCILED')
THEN
-- get check no for rec'd pmt
l_txn_no := p_reference_4;
ELSIF ( ( p_reference_6 IS NULL
OR UPPER (p_reference_6) = 'AP INVOICES'
)
AND p_category = 'Purchase Invoices'
AND p_actual_flag = 'E'
)
THEN
/* non PO invoice encumbrance */
-- get invoice voucher no
OPEN cu_ap_inv_voucher_no;

FETCH cu_ap_inv_voucher_no
INTO l_txn_no;

CLOSE cu_ap_inv_voucher_no;
END IF;
ELSIF p_source = 'Purchasing'
THEN
-- get po number
IF p_reference_1 = 'PO' AND p_reference_2 IS NOT NULL
THEN
OPEN cu_po_number;

FETCH cu_po_number
INTO l_txn_no;

CLOSE cu_po_number;

IF l_txn_no IS NULL
THEN
l_txn_no := p_reference_4;
END IF;
ELSIF p_reference_1 = 'PO'
THEN
----Changed Amars SUP-447
l_txn_no := p_reference_4; ----Changed Amars SUP-447
ELSIF p_reference_1 = 'REQ'
THEN
-- get po req no
l_txn_no := p_reference_4;
END IF;
ELSIF p_source = 'Inventory'
THEN
-- get inventory transaction type name
OPEN cu_inv_tx_type;

FETCH cu_inv_tx_type
INTO l_txn_type_name;

CLOSE cu_inv_tx_type;

IF l_txn_type_name = 'Purchase order receipt'
THEN
-- get receipt no
OPEN cu_inv_po_rcpt_no;

FETCH cu_inv_po_rcpt_no
INTO l_txn_no;

CLOSE cu_inv_po_rcpt_no;
ELSIF l_txn_type_name = 'Miscellaneous receipt'
THEN
-- get inventory transaction id
l_txn_no := p_reference_3;
ELSIF l_txn_type_name = 'Internal order issue'
THEN
-- get original PO requisition no
OPEN cu_inv_poreq_no;

FETCH cu_inv_poreq_no
INTO l_txn_no;

CLOSE cu_inv_poreq_no;
END IF;
ELSIF p_source = 'Receivables'
THEN
IF p_reference_10 = 'AR_ADJUSTMENTS'
THEN
-- get adjustment no
OPEN cu_ar_adj_no;

FETCH cu_ar_adj_no
INTO l_txn_no;

CLOSE cu_ar_adj_no;
ELSIF p_reference_10 = 'AR_MISC_CASH_DISTRIBUTIONS'
THEN
-- get receipt no
OPEN cu_ar_misc_cash_rcpt_no;

FETCH cu_ar_misc_cash_rcpt_no
INTO l_txn_no;

CLOSE cu_ar_misc_cash_rcpt_no;
ELSIF p_reference_10 = 'AR_RECEIVABLE_APPLICATIONS'
THEN
-- get receipt or cm (etc) no from which application was made
-- use ref_4 as receivable appln. maybe for a cash receipt or a
-- trx item being applied
l_txn_no := p_reference_4;
ELSIF p_reference_10 = 'AR_CASH_RECEIPT_HISTORY'
THEN
-- get misc rcpt or trade receipt no
l_txn_no := p_reference_4;
ELSIF p_reference_10 = 'RA_CUST_TRX_LINE_GL_DIST'
THEN
-- get invoice/credit memo etc. number
OPEN cu_ar_cust_trx_no;

FETCH cu_ar_cust_trx_no
INTO l_txn_no;

CLOSE cu_ar_cust_trx_no;
END IF;

--Added by Khavi on 21-Aug-07 as part of fixed assets implementation
ELSIF p_source = 'Assets'
THEN
OPEN cu_fa_asset_number;
FETCH cu_fa_asset_number
INTO l_txn_no;
CLOSE cu_fa_asset_number;

END IF;

-- if the txn isnt covered by above (l_txn_no is null)
-- then get gl journal line number
IF l_txn_no IS NULL
THEN
l_txn_no := p_je_line_num;
END IF;

RETURN l_txn_no;
EXCEPTION
WHEN INVALID_NUMBER
THEN
RETURN NULL;
END fn_sel_txn_no;

/*----------------------------------------------------------------------*/
FUNCTION fn_sel_txn_cust_or_vendor (
p_source IN VARCHAR2,
p_category IN VARCHAR2,
p_actual_flag IN VARCHAR2,
p_je_header_id IN NUMBER,
p_je_line_num IN NUMBER,
p_reference_1 IN VARCHAR2,
p_reference_2 IN VARCHAR2,
p_reference_3 IN VARCHAR2,
p_reference_4 IN VARCHAR2,
p_reference_5 IN VARCHAR2,
p_reference_6 IN VARCHAR2,
p_reference_7 IN VARCHAR2,
p_reference_10 IN VARCHAR2
)
RETURN VARCHAR2
IS
l_txn_cust_or_vendor VARCHAR2 (240) := NULL;
l_txn_type_name VARCHAR2 (30) := NULL;

CURSOR cu_ap_recon_vendor
IS
SELECT pov.vendor_name
FROM po_vendors pov, ap_checks_all apc
WHERE apc.check_id = TO_NUMBER (p_reference_3)
AND apc.vendor_id = pov.vendor_id;

CURSOR cu_po_vendor
IS
SELECT pov.vendor_name
FROM po_vendors pov, po_headers_all poh
WHERE poh.po_header_id = TO_NUMBER (p_reference_2)
AND poh.vendor_id = pov.vendor_id;

CURSOR cu_poreq_vendor
IS
SELECT pov.vendor_name
FROM po_vendors pov,
po_requisition_lines_all reql,
po_req_distributions_all reqd
WHERE reqd.distribution_id = TO_NUMBER (p_reference_3)
AND reqd.requisition_line_id = reql.requisition_line_id
AND reql.vendor_id = pov.vendor_id;

CURSOR cu_inv_tx_type
IS
SELECT mtt.transaction_type_name
FROM mtl_material_transactions mt, mtl_transaction_types mtt
WHERE mtt.transaction_type_id = mt.transaction_type_id
AND mt.transaction_id = TO_NUMBER (p_reference_3);

CURSOR cu_inv_po_rcpt_vendor
IS
SELECT pov.vendor_name
FROM po_vendors pov, po_headers poh, mtl_material_txns_val_v mt
WHERE mt.transaction_source_type_name = 'Purchase order'
AND mt.transaction_type_name = 'Purchase order receipt'
AND mt.transaction_id = TO_NUMBER (p_reference_3)
AND mt.transaction_source_id = poh.po_header_id
AND poh.vendor_id = pov.vendor_id;

CURSOR cu_inv_misc_rcpt_source
IS
SELECT m.transaction_source_name_db
FROM mtl_material_txns_val_v m
WHERE m.transaction_source_type_name = 'Inventory'
AND m.transaction_type_name = 'Miscellaneous receipt'
AND m.transaction_id = TO_NUMBER (p_reference_3);

CURSOR cu_inv_poreq_approver
IS
SELECT u.user_name
FROM fnd_user u,
po_requisition_headers_all reqh,
so_headers_all soh,
mtl_material_txns_val_v mt
WHERE u.user_id = reqh.last_updated_by
AND reqh.segment1 = soh.original_system_reference
AND soh.header_id = mt.transaction_reference
AND mt.transaction_source_type_name = 'Internal order'
AND mt.transaction_type_name = 'Internal order issue'
AND mt.transaction_id = TO_NUMBER (p_reference_3);

/* Note that whilst some of the AR cursors are the same
they have been declared separately to make code easier
to maintain in case the data to be retrieved changes
for certain tx's */
CURSOR cu_ar_cust_name
IS
SELECT c.customer_name
FROM ra_customers c
WHERE c.customer_id = TO_NUMBER (p_reference_7);
BEGIN
IF p_source = 'Payables'
THEN
IF (UPPER (p_reference_6) = 'AP INVOICES' AND p_actual_flag = 'A')
THEN
-- get vendor for invoice
l_txn_cust_or_vendor := p_reference_1;
ELSIF UPPER (p_reference_6) = 'AP PAYMENTS'
THEN
-- get check number
l_txn_cust_or_vendor := p_reference_1;
ELSIF p_reference_6 IN
('AP Reconciled Payments', 'AP PAYMENTS JUST RECONCILED')
THEN
OPEN cu_ap_recon_vendor;

FETCH cu_ap_recon_vendor
INTO l_txn_cust_or_vendor;

CLOSE cu_ap_recon_vendor;
ELSIF ( ( p_reference_6 IS NULL
OR UPPER (p_reference_6) = 'AP INVOICES'
)
AND p_category = 'Purchase Invoices'
AND p_actual_flag = 'E'
)
THEN
/* non PO invoice encumbrance */
-- get vendor for invoice
l_txn_cust_or_vendor := p_reference_1;
END IF;
ELSIF p_source = 'Purchasing'
THEN
IF p_reference_1 = 'PO'
THEN
-- get po vendor
OPEN cu_po_vendor;

FETCH cu_po_vendor
INTO l_txn_cust_or_vendor;

CLOSE cu_po_vendor;
ELSIF p_reference_1 = 'REQ'
THEN
-- get po req vendor
OPEN cu_poreq_vendor;

FETCH cu_poreq_vendor
INTO l_txn_cust_or_vendor;

CLOSE cu_poreq_vendor;
END IF;
ELSIF p_source = 'Inventory'
THEN
-- get inventory transaction type name
OPEN cu_inv_tx_type;

FETCH cu_inv_tx_type
INTO l_txn_type_name;

CLOSE cu_inv_tx_type;

IF l_txn_type_name = 'Purchase order receipt'
THEN
-- get vendor for po which is received against
OPEN cu_inv_po_rcpt_vendor;

FETCH cu_inv_po_rcpt_vendor
INTO l_txn_cust_or_vendor;

CLOSE cu_inv_po_rcpt_vendor;
ELSIF l_txn_type_name = 'Miscellaneous receipt'
THEN
-- get rcpt source
OPEN cu_inv_misc_rcpt_source;

FETCH cu_inv_misc_rcpt_source
INTO l_txn_cust_or_vendor;

CLOSE cu_inv_misc_rcpt_source;
ELSIF l_txn_type_name = 'Internal order issue'
THEN
-- get internal requisition approver
OPEN cu_inv_poreq_approver;

FETCH cu_inv_poreq_approver
INTO l_txn_cust_or_vendor;

CLOSE cu_inv_poreq_approver;
END IF;
ELSIF p_source = 'Receivables'
THEN
-- get customer
OPEN cu_ar_cust_name;

FETCH cu_ar_cust_name
INTO l_txn_cust_or_vendor;

CLOSE cu_ar_cust_name;
END IF;

RETURN l_txn_cust_or_vendor;
EXCEPTION
WHEN INVALID_NUMBER
THEN
RETURN NULL;
END fn_sel_txn_cust_or_vendor;

/*------------------------------------------------------------------------*/
FUNCTION fn_sel_txn_cid (
p_source IN VARCHAR2,
p_category IN VARCHAR2,
p_actual_flag IN VARCHAR2,
p_je_header_id IN NUMBER,
p_je_line_num IN NUMBER,
p_reference_1 IN VARCHAR2,
p_reference_2 IN VARCHAR2,
p_reference_3 IN VARCHAR2,
p_reference_4 IN VARCHAR2,
p_reference_5 IN VARCHAR2,
p_reference_6 IN VARCHAR2,
p_reference_7 IN VARCHAR2,
p_reference_10 IN VARCHAR2
)
RETURN VARCHAR2
IS
l_txn_cid VARCHAR2 (240) := NULL;
l_txn_type_name VARCHAR2 (30) := NULL;

CURSOR cu_ap_inv_vendor
IS
SELECT pov.segment1
FROM po_vendors pov, ap_invoices_all api
WHERE api.vendor_id = pov.vendor_id
AND api.invoice_id = TO_NUMBER (p_reference_2);

CURSOR cu_ap_chk_vendor
IS
SELECT pov.segment1
FROM po_vendors pov, ap_checks_all apc
WHERE apc.check_id = TO_NUMBER (p_reference_3)
AND apc.vendor_id = pov.vendor_id;

CURSOR cu_ap_recon_vendor
IS
SELECT pov.segment1
FROM po_vendors pov, ap_checks_all apc
WHERE apc.check_id = TO_NUMBER (p_reference_3)
AND apc.vendor_id = pov.vendor_id;

CURSOR cu_po_vendor
IS
SELECT pov.segment1
FROM po_vendors pov, po_headers_all poh
WHERE poh.po_header_id = TO_NUMBER (p_reference_2)
AND poh.vendor_id = pov.vendor_id;

CURSOR cu_poreq_vendor
IS
SELECT pov.segment1
FROM po_vendors pov,
po_requisition_lines_all reql,
po_req_distributions_all reqd
WHERE reqd.distribution_id = TO_NUMBER (p_reference_3)
AND reqd.requisition_line_id = reql.requisition_line_id
AND reql.vendor_id = pov.vendor_id;

CURSOR cu_inv_tx_type
IS
SELECT mtt.transaction_type_name
FROM mtl_material_transactions mt, mtl_transaction_types mtt
WHERE mtt.transaction_type_id = mt.transaction_type_id
AND mt.transaction_id = TO_NUMBER (p_reference_3);

CURSOR cu_inv_po_rcpt_vendor
IS
SELECT pov.segment1
FROM po_vendors pov, po_headers poh, mtl_material_txns_val_v mt
WHERE mt.transaction_source_type_name = 'Purchase order'
AND mt.transaction_type_name = 'Purchase order receipt'
AND mt.transaction_id = TO_NUMBER (p_reference_3)
AND mt.transaction_source_id = poh.po_header_id
AND poh.vendor_id = pov.vendor_id;

/* Note that whilst some of the AR cursors are the same
they have been declared separately to make code easier
to maintain in case the data to be retrieved changes
for certain tx's */
CURSOR cu_ar_cust_cid
IS
SELECT c.customer_number
FROM ra_customers c
WHERE c.customer_id = TO_NUMBER (p_reference_7);
BEGIN
IF p_source = 'Payables'
THEN
IF (UPPER (p_reference_6) = 'AP INVOICES' AND p_actual_flag = 'A')
THEN
-- get vendor cid for invoice
OPEN cu_ap_inv_vendor;

FETCH cu_ap_inv_vendor
INTO l_txn_cid;

CLOSE cu_ap_inv_vendor;
ELSIF UPPER (p_reference_6) = 'AP PAYMENTS'
THEN
-- get vendor cid for check
OPEN cu_ap_chk_vendor;

FETCH cu_ap_chk_vendor
INTO l_txn_cid;

CLOSE cu_ap_chk_vendor;
ELSIF p_reference_6 IN
('AP Reconciled Payments', 'AP PAYMENTS JUST RECONCILED')
THEN
OPEN cu_ap_recon_vendor;

FETCH cu_ap_recon_vendor
INTO l_txn_cid;

CLOSE cu_ap_recon_vendor;
ELSIF ( ( p_reference_6 IS NULL
OR UPPER (p_reference_6) = 'AP INVOICES'
)
AND p_category = 'Purchase Invoices'
AND p_actual_flag = 'E'
)
THEN
/* non PO invoice encumbrance */
-- get vendor cid for invoice
OPEN cu_ap_inv_vendor;

FETCH cu_ap_inv_vendor
INTO l_txn_cid;

CLOSE cu_ap_inv_vendor;
END IF;
ELSIF p_source = 'Purchasing'
THEN
IF p_reference_1 = 'PO'
THEN
-- get po vendor
OPEN cu_po_vendor;

FETCH cu_po_vendor
INTO l_txn_cid;

CLOSE cu_po_vendor;
ELSIF p_reference_1 = 'REQ'
THEN
-- get po req vendor
OPEN cu_poreq_vendor;

FETCH cu_poreq_vendor
INTO l_txn_cid;

CLOSE cu_poreq_vendor;
END IF;
ELSIF p_source = 'Inventory'
THEN
-- get inventory transaction type name
OPEN cu_inv_tx_type;

FETCH cu_inv_tx_type
INTO l_txn_type_name;

CLOSE cu_inv_tx_type;

IF l_txn_type_name = 'Purchase order receipt'
THEN
-- get vendor for po which is received against
OPEN cu_inv_po_rcpt_vendor;

FETCH cu_inv_po_rcpt_vendor
INTO l_txn_cid;

CLOSE cu_inv_po_rcpt_vendor;
ELSIF l_txn_type_name = 'Miscellaneous receipt'
THEN
l_txn_cid := NULL;
ELSIF l_txn_type_name = 'Internal order issue'
THEN
l_txn_cid := NULL;
END IF;
ELSIF p_source = 'Receivables'
THEN
-- get customer cid
OPEN cu_ar_cust_cid;

FETCH cu_ar_cust_cid
INTO l_txn_cid;

CLOSE cu_ar_cust_cid;
END IF;

RETURN l_txn_cid;
EXCEPTION
WHEN INVALID_NUMBER
THEN
RETURN NULL;
END fn_sel_txn_cid;

/*------------------------------------------------------------------------------*/
FUNCTION fn_sel_po_num (
p_source IN VARCHAR2,
p_category IN VARCHAR2,
p_actual_flag IN VARCHAR2,
p_je_header_id IN NUMBER,
p_je_line_num IN NUMBER,
p_reference_1 IN VARCHAR2,
p_reference_2 IN VARCHAR2,
p_reference_3 IN VARCHAR2,
p_reference_4 IN VARCHAR2,
p_reference_5 IN VARCHAR2,
p_reference_6 IN VARCHAR2,
p_reference_7 IN VARCHAR2,
p_reference_10 IN VARCHAR2
)
RETURN VARCHAR2
IS
l_txn_po_num VARCHAR2 (50) := NULL;
l_txn_dist_id NUMBER (15) := NULL;

CURSOR cu_ap_podist_id
IS
SELECT aid.po_distribution_id
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = TO_NUMBER (p_reference_2)
ORDER BY aid.distribution_line_number;

CURSOR cu_get_po_num
IS
SELECT poh.segment1
FROM po_headers_all poh, po_distributions_all pod
WHERE poh.po_header_id = pod.po_header_id
AND pod.po_distribution_id = l_txn_dist_id;
BEGIN
IF p_source = 'Payables' AND p_reference_2 IS NOT NULL
THEN
OPEN cu_ap_podist_id;

FETCH cu_ap_podist_id
INTO l_txn_dist_id;

CLOSE cu_ap_podist_id;

IF l_txn_dist_id IS NOT NULL
THEN
OPEN cu_get_po_num;

FETCH cu_get_po_num
INTO l_txn_po_num;

CLOSE cu_get_po_num;
ELSE
l_txn_po_num := NULL;
END IF;
END IF;

RETURN l_txn_po_num;
EXCEPTION
WHEN INVALID_NUMBER
THEN
RETURN NULL;
END fn_sel_po_num;

/*--------------------------------------------------------------------*/
FUNCTION fn_sel_txn_amt (
p_source IN VARCHAR2,
p_category IN VARCHAR2,
p_actual_flag IN VARCHAR2,
p_je_header_id IN NUMBER,
p_je_line_num IN NUMBER,
p_reference_1 IN VARCHAR2,
p_reference_2 IN VARCHAR2,
p_reference_3 IN VARCHAR2,
p_reference_4 IN VARCHAR2,
p_reference_5 IN VARCHAR2,
p_reference_6 IN VARCHAR2,
p_reference_7 IN VARCHAR2,
p_reference_8 IN VARCHAR2,
p_reference_9 IN VARCHAR2,
p_reference_10 IN VARCHAR2,
p_gl_sl_link_id IN NUMBER,
p_accounted_dr IN NUMBER,
p_accounted_cr IN NUMBER,
p_batch_name IN VARCHAR2,
p_encumbrance_type_id IN NUMBER
)
RETURN NUMBER
IS
l_txn_amt NUMBER := NULL;
l_txn_type_name VARCHAR2 (30) := NULL;
l_batch_name VARCHAR2 (100) := NULL;
l_encumbrance_type VARCHAR2 (30) := NULL;

CURSOR cu_ap_inv_dist_amt
IS
SELECT NVL (accounted_dr, -1 * accounted_cr)
FROM ap_ae_lines_all
WHERE gl_sl_link_id = p_gl_sl_link_id;

-- SUP 473/472+ 30-Oct-2000
CURSOR cu_ap_inv_dist_amt2
IS
SELECT SUM (DECODE (aid.invoice_price_variance,
NULL, DECODE (ai.invoice_currency_code,
'GBP', -aid.amount,
-aid.base_amount
),
NULL
)
)
FROM ap_invoices_all ai, ap_invoice_distributions_all aid
WHERE ai.invoice_id = aid.invoice_id
AND aid.invoice_id = TO_NUMBER (p_reference_2)
AND NVL (aid.je_uk_vat_reclaim_type, 'N') = 'N'
AND NVL (aid.je_uk_vat_link_to_line_num,
aid.distribution_line_number
) = TO_NUMBER (p_reference_3)
AND EXISTS (
SELECT 'x'
FROM ap_invoice_distributions_all aid2
WHERE aid2.je_uk_vat_link_to_line_num IS NULL
AND aid2.invoice_price_variance IS NULL
AND aid.invoice_id = aid2.invoice_id
AND NVL (aid2.je_uk_vat_link_to_line_num,
aid2.distribution_line_number
) =
NVL (aid.je_uk_vat_link_to_line_num,
aid.distribution_line_number
));

/* Code prior to SUP-472+ 30-Oct-2000 SELECT DECODE(aid.invoice_price_variance,
NULL, DECODE(ai.invoice_currency_code,'GBP',aid.amount,aid.base_amount),
NULL
)
FROM ap_invoices_all ai,
ap_invoice_distributions_all aid
WHERE ai.invoice_id = aid.invoice_id
AND aid.invoice_id = TO_NUMBER(p_reference_2)
AND aid.distribution_line_number = TO_NUMBER(p_reference_3) */
/*---12-Jun-2000 Change---*/
-- CURSOR cu_ap_inv_dist_amt2 IS
-- SELECT DECODE(p_reference_10, 'IPV',
-- DECODE(ai.invoice_currency_code,'GBP',
-- aid.invoice_price_variance,aid.base_invoice_price_variance
-- ),
-- DECODE(ai.invoice_currency_code,'GBP',aid.amount,aid.base_amount)
-- )
-- FROM ap_invoices_all ai,
-- ap_invoice_distributions_all aid
-- WHERE ai.invoice_id = aid.invoice_id
-- AND aid.invoice_id = TO_NUMBER(p_reference_2)
-- AND aid.distribution_line_number = TO_NUMBER(p_reference_3);
CURSOR cu_ap_chk_inv_dist_amt
IS
SELECT pd.base_amount
FROM ap_payment_distributions pd, ap_invoice_payments aip
WHERE aip.invoice_id = TO_NUMBER (p_reference_2)
AND aip.check_id = TO_NUMBER (p_reference_3)
AND aip.invoice_payment_id = TO_NUMBER (p_reference_9)
AND pd.invoice_payment_id = aip.invoice_payment_id
AND pd.invoice_distr_line_number = TO_NUMBER (p_reference_8);

CURSOR cu_ap_recon_amt
IS
/*SELECT ard.recon_distribution_amount
FROM ap_gl_recon_distributions_v ard
WHERE ard.check_id = TO_NUMBER(p_reference_3);*/
SELECT rd.amount
FROM ap_lookup_codes l,
ap_recon_distributions rd,
ap_checks c,
ap_bank_accounts bc,
ce_statement_lines sl,
ce_statement_reconciliations sr,
ce_lookups cl,
ce_statement_headers sh,
po_vendors v
--,gl_import_references r
WHERE l.lookup_code = rd.line_type_lookup_code
AND l.lookup_type = 'RECON_DIST_TYPES'
AND rd.check_id = c.check_id
AND rd.posted_flag || '' = 'Y'
AND sl.trx_type = 'DEBIT'
AND sl.statement_header_id = sh.statement_header_id
AND sr.statement_line_id = sl.statement_line_id
AND cl.lookup_type(+) = 'TRX_TYPE'
AND cl.lookup_code(+) = sr.reference_type
AND sr.reference_id = p_reference_3
AND v.vendor_id = c.vendor_id
AND bc.bank_account_id = c.bank_account_id
AND c.check_id = TO_NUMBER (p_reference_3)
AND c.check_number = TO_NUMBER (p_reference_4)
AND p_reference_10 = rd.line_type_lookup_code;

/* SUP-522 fix to exclude distributions relating to Closed lines */
CURSOR cu_po_dist_amt
IS
SELECT pod.encumbered_amount
FROM po_distributions_all pod, po_lines_all pol
WHERE pod.po_line_id = pol.po_line_id
AND pol.closed_date IS NULL
AND pod.po_distribution_id = TO_NUMBER (p_reference_3);

CURSOR cu_poreq_dist_amt
IS
SELECT reqd.encumbered_amount
FROM po_req_distributions_all reqd
WHERE reqd.distribution_id = TO_NUMBER (p_reference_3);

--apassi Commenting this code on Sep06, as max date in mtl_transaction_types is 24Jan04
/* CURSOR cu_inv_tx_type IS
SELECT mtt.transaction_type_name
FROM mtl_material_transactions mt
,mtl_transaction_types mtt
WHERE mtt.transaction_type_id =
mt.transaction_type_id
AND mt.transaction_id =
to_number(p_reference_3);*/
/* Note that there is a problem in trying to report the
inventory amounts. The reason for this is that only
the transaction id is passed to GL_IMPORT_REFERENCES
not the distribution (held in MTL_TRANSACTION_ACCOUNTS).
This means that if more than one line within a transaction id
has the same acount code the amount reported will be wrong. I
dont know if this situation will ever arise - it hasnt during
test even when we receive 2 diferent orders, same item, same account
on the same receipt */
--apassi Mta now redundant for ICIS, hence commenting below
--select max(creation_date) from mtl_transaction_accounts ; returns 29-JAN-04
/* CURSOR cu_inv_po_rcpt_amt IS
SELECT SUM(ta.base_transaction_value)
FROM gl_je_headers jeh
,gl_je_lines jel
,mtl_transaction_accounts ta
,gl_import_references gir
,mtl_material_txns_val_v mt
WHERE mt.transaction_id =
to_number(p_reference_3)
AND mt.transaction_source_type_name =
'Purchase order'
AND mt.transaction_type_name =
'Purchase order receipt'
AND mt.transaction_id =
ta.transaction_id
AND ta.reference_account =
jel.code_combination_id
AND gir.reference_3 =
mt.transaction_id
AND gir.je_line_num =
to_number(p_je_line_num)
AND gir.je_header_id =
to_number(p_je_header_id)
AND jel.je_line_num =
gir.je_line_num
AND jel.je_header_id =
gir.je_header_id
AND jeh.je_header_id =
jel.je_header_id
AND nvl(ta.encumbrance_type_id, -987) =
nvl(jeh.encumbrance_type_id, -987);*/
/* CURSOR cu_inv_misc_rcpt_amt IS
SELECT SUM(ta.base_transaction_value)
FROM gl_je_headers jeh
,gl_je_lines jel
,mtl_transaction_accounts ta
,gl_import_references gir
,mtl_material_txns_val_v mt
WHERE mt.transaction_id =
to_number(p_reference_3)
AND mt.transaction_source_type_name =
'Inventory'
AND mt.transaction_type_name =
'Miscellaneous receipt'
AND mt.transaction_id =
ta.transaction_id
AND ta.reference_account =
jel.code_combination_id
AND gir.reference_3 =
mt.transaction_id
AND gir.je_line_num =
to_number(p_je_line_num)
AND gir.je_header_id =
to_number(p_je_header_id)
AND jel.je_line_num =
gir.je_line_num
AND jel.je_header_id =
gir.je_header_id
AND jeh.je_header_id =
jel.je_header_id
AND nvl(ta.encumbrance_type_id, -987) =
nvl(jeh.encumbrance_type_id, -987);*/
/* CURSOR cu_inv_poreq_issue_amt IS
SELECT SUM(ta.base_transaction_value)
FROM gl_je_headers jeh
,gl_je_lines jel
,mtl_transaction_accounts ta
,gl_import_references gir
,mtl_material_txns_val_v mt
WHERE mt.transaction_id =
to_number(p_reference_3)
AND mt.transaction_source_type_name =
'Internal order'
AND mt.transaction_type_name =
'Internal order issue'
AND mt.transaction_id =
ta.transaction_id
AND ta.reference_account =
jel.code_combination_id
AND gir.reference_3 =
mt.transaction_id
AND gir.je_line_num =
to_number(p_je_line_num)
AND gir.je_header_id =
to_number(p_je_header_id)
AND jel.je_line_num =
gir.je_line_num
AND jel.je_header_id =
gir.je_header_id
AND jeh.je_header_id =
jel.je_header_id
AND nvl(ta.encumbrance_type_id, -987) =
nvl(jeh.encumbrance_type_id, -987);*/
/* CURSOR cu_inv_other_tx_amt IS
SELECT SUM(ta.base_transaction_value)
FROM gl_je_headers jeh
,gl_je_lines jel
,mtl_transaction_accounts ta
,gl_import_references gir
,mtl_material_txns_val_v mt
WHERE mt.transaction_id =
to_number(p_reference_3)
AND ltrim(rtrim(mt.transaction_source_type_name)) || '-' ||
ltrim(rtrim(mt.transaction_type_name)) NOT IN
('Internal order-Internal order issue',
'Purchase order-Purchase order receipt',
'Inventory-Miscellaneous receipt')
AND mt.transaction_id =
ta.transaction_id
AND ta.reference_account =
jel.code_combination_id
AND gir.reference_3 =
mt.transaction_id
AND gir.je_line_num =
to_number(p_je_line_num)
AND gir.je_header_id =
to_number(p_je_header_id)
AND jel.je_line_num =
gir.je_line_num
AND jel.je_header_id =
gir.je_header_id
AND jeh.je_header_id =
jel.je_header_id
AND nvl(ta.encumbrance_type_id, -987) =
nvl(jeh.encumbrance_type_id, -987)
UNION \* non material transactions *\
SELECT SUM(d.base_transaction_value)
FROM gl_je_lines jel
,gl_import_references gir
,cst_inv_distribution_v d
WHERE d.transaction_id =
to_number(p_reference_3)
AND ltrim(rtrim(d.transaction_source_type_name)) || '-' ||
ltrim(rtrim(d.transaction_type_name)) NOT IN
('Internal order-Internal order issue',
'Purchase order-Purchase order receipt',
'Inventory-Miscellaneous receipt')
AND d.reference_account =
jel.code_combination_id
AND gir.reference_3 =
d.transaction_id
AND gir.je_line_num =
to_number(p_je_line_num)
AND gir.je_header_id =
to_number(p_je_header_id)
AND jel.je_line_num =
gir.je_line_num
AND jel.je_header_id =
gir.je_header_id; */
/* Note that whilst some of the AR cursors are the same
they have been declared separately to make code easier
to maintain in case the data to be retrieved changes
for certain tx's */
CURSOR cu_ar_adj_amt
IS
SELECT adj.acctd_amount -- T.Percival 15/5/03 272.212
FROM ar_adjustments_all adj
-- WHERE adj.adjustment_id = TO_NUMBER(p_reference_3); -- R.Pattni, 14-Jun-2002, Reference column changed in 11i
-- Reference 2 is now used to store the adjustment id , see below.
WHERE adj.adjustment_id = TO_NUMBER (p_reference_2);

/*CURSOR cu_ar_misc_cash_rcpt_amt IS
SELECT cr.amount
FROM ar_cash_receipts_all cr
WHERE cr.cash_receipt_id = TO_NUMBER(p_reference_2);
*/
-- New report J code (29/02/01) to show miscellaneous receipt amounts at distribution level
CURSOR cu_ar_misc_cash_rcpt_amt
IS
SELECT acctd_amount -- T.Percival 15/5/03 272.212
FROM ar_misc_cash_distributions_all
WHERE misc_cash_distribution_id = TO_NUMBER (p_reference_5);
-- R.Pattni, 19-Jun-2002, Reference column changed from 3 to 5

CURSOR cu_ar_rx_app_applied_amt
IS
SELECT ra.acctd_amount_applied_from -- T.Percival 15/5/03 272.212
FROM ar_receivable_applications_all ra
WHERE ra.receivable_application_id = TO_NUMBER (p_reference_2);
-- R.Pattni, 19-Jun-2002, Reference column changed from 3 -- to 2

CURSOR cu_ar_cash_rcpt_amt
IS
SELECT cr.amount
FROM ar_cash_receipts_all cr
WHERE cr.cash_receipt_id = TO_NUMBER (p_reference_2);

CURSOR cu_ar_cust_trx_amt
IS
SELECT ctlgd.acctd_amount
FROM ra_cust_trx_line_gl_dist_all ctlgd
WHERE ctlgd.cust_trx_line_gl_dist_id = TO_NUMBER (p_reference_3);

/* CURSOR cu_gl_line_amt IS
SELECT nvl(gl.accounted_dr, 0) -
nvl(gl.accounted_cr, 0)
FROM gl_je_lines gl
WHERE gl.je_header_id =
p_je_header_id
AND gl.je_line_num =
p_je_line_num;*/
CURSOR cu_gl_batch_name
IS
/*SELECT b.name, e.encumbrance_type
FROM gl_encumbrance_types e,
gl_je_batches b,
gl_je_headers h
WHERE b.je_batch_id = h.je_batch_id
AND h.je_header_id = p_je_header_id
AND h.encumbrance_type_id = e.encumbrance_type_id;
*/
SELECT e.encumbrance_type
FROM gl_encumbrance_types e
WHERE e.encumbrance_type_id = p_encumbrance_type_id;
BEGIN
l_batch_name := p_batch_name;

IF p_source = 'Payables'
THEN
IF (UPPER (p_reference_6) = 'AP INVOICES' AND p_actual_flag = 'A')
THEN
-- get invoice dist amt
OPEN cu_ap_inv_dist_amt;

FETCH cu_ap_inv_dist_amt
INTO l_txn_amt;

CLOSE cu_ap_inv_dist_amt;
-- nb. ref10=LIABILITY would be -1*amt
-- other ref10 = ok
ELSIF UPPER (p_reference_6) = 'AP PAYMENTS'
THEN
-- get amt paid against this invoice dist
OPEN cu_ap_chk_inv_dist_amt;

FETCH cu_ap_chk_inv_dist_amt
INTO l_txn_amt;

CLOSE cu_ap_chk_inv_dist_amt;
ELSIF p_reference_6 IN
('AP Reconciled Payments', 'AP PAYMENTS JUST RECONCILED')
THEN
-- get amt reconciled
--
/*
* J.Pallot:
* This fetch has had to be blocked in order to handle the errors
* that arise when the reference_4 column in the gl_import_references table has
* been populated with characters which cannot implicitly be converted to numbers
* e.g.: 'K JLD 991129-29.07' cannot be converted to a number whereas '139527' can.
*
* The ap_gl_recon_distributions_v view (see cursor definition) joins the reference_4
* column - which is varchar2 - to the to the ap_checks.check_number_id column - which
* is number.
*
* It is possible that this error arises from the employee to vendor interface but
* this is not yet confirmed.
*
*/
BEGIN
OPEN cu_ap_recon_amt;

FETCH cu_ap_recon_amt
INTO l_txn_amt;

CLOSE cu_ap_recon_amt;
EXCEPTION
WHEN OTHERS
THEN
l_txn_amt := NULL;
END;
ELSIF ( ( p_reference_6 IS NULL
OR UPPER (p_reference_6) = 'AP INVOICES'
)
AND p_category = 'Purchase Invoices'
AND p_actual_flag = 'E'
)
THEN
/* AP invoice related encumbrance */
-- get invoice dist amt
--Commented by Khavi, satyam on 22-Jan-04 to fix the encumbrance bug : 13493
/*OPEN cu_ap_inv_dist_amt2;
FETCH cu_ap_inv_dist_amt2 INTO l_txn_amt;
CLOSE cu_ap_inv_dist_amt2;*/
--Added by Khavi, satyam on 22-Jan-04 to fix the encumbrance bug : 13493
--OPEN cu_gl_line_amt;
--FETCH cu_gl_line_amt INTO l_txn_amt;
--CLOSE cu_gl_line_amt;
l_txn_amt := NVL (p_accounted_dr, 0) - NVL (p_accounted_cr, 0);

/* If batch is as a result of the GL program
"Program - Create Journals" which creates the PO encumbrances
get gl batch name and encumbrance type.
The program creates batches where the name starts with CJE.
The encumbrances are the either debit encumbrance for the AP
invoice or credits for the PO encumbrance being reversed
as the result of a match to an invoice in AP.
Commitment types are as per PO set up for Purchase Orders
- for us the CJE encumbrances are
PO requisitions = Commitment (no longer used)
PO encumbrances = Obligation
Invoice encumbrance = Invoice
Any other entries from are credits (reducing the encumbrance)
as a result of approved and posted invoices (these credits are
created during the AP to GL transfer) */
-- get gl batch name and encumbrance type
OPEN cu_gl_batch_name;

--FETCH cu_gl_batch_name INTO l_batch_name, l_encumbrance_type;
FETCH cu_gl_batch_name
INTO l_encumbrance_type;

CLOSE cu_gl_batch_name;

IF l_batch_name NOT LIKE 'CJE%'
THEN
l_txn_amt := -1 * l_txn_amt;
END IF;

IF ( ( l_batch_name LIKE 'CJE%'
OR l_batch_name LIKE 'Payables%E%'
)
AND l_encumbrance_type IN
('Obligation', 'Commitment', 'Invoice')
)
AND l_txn_amt IS NULL
THEN
/* get gl journal line amount as this ensures that any
reclaimed VAT (OPSF Gross Based VAT localisation) is included
to ensure s-ledger amounts = gl amounts`
The reason for this is that GBV encumbrances are grossed up in GL lines,
ie. they include the reclaimable amount. However the gl_import_refs
table only includes the audit trail to the invoice item distribution
which doesnt include the reclaimable VAT amount.
Using the gl_line amount ensures correct results and is safe as OPSF
posts from AP to GL at detail level.
Note that it is only for this case that a problem with the audit trail
was found. If you want to check select the invoice distribution amount
using the view (ICGL_0213_SUB_LEDGER_DETAIL_V) and also display the
gl line amount,... then things may/should become clear */
--OPEN cu_gl_line_amt;
--FETCH cu_gl_line_amt INTO l_txn_amt;
--CLOSE cu_gl_line_amt;
l_txn_amt := NVL (p_accounted_dr, 0) - NVL (p_accounted_cr, 0);
END IF;
END IF;
ELSIF p_source = 'Purchasing'
THEN
/* All purchasing entries are encumbrances except for receipts */
IF (p_category = 'Receiving' AND p_actual_flag = 'A')
THEN
-- get receipt amounts (use the amount on GL line
-- as we dont know which receipt this is!!!)
--OPEN cu_gl_line_amt;
--FETCH cu_gl_line_amt INTO l_txn_amt;
--CLOSE cu_gl_line_amt;
l_txn_amt := NVL (p_accounted_dr, 0) - NVL (p_accounted_cr, 0);
ELSIF p_reference_1 = 'PO'
THEN
-- get distribution amt -- Commented by Ankush Shinde against marval log 6773 to fetch the value from GL instead of PO distributions
-- OPEN cu_po_dist_amt;
-- FETCH cu_po_dist_amt INTO l_txn_amt;
-- CLOSE cu_po_dist_amt;
--OPEN cu_gl_line_amt;
--FETCH cu_gl_line_amt INTO l_txn_amt;
--CLOSE cu_gl_line_amt;
l_txn_amt := NVL (p_accounted_dr, 0) - NVL (p_accounted_cr, 0);

-- get gl batch name
OPEN cu_gl_batch_name;

--FETCH cu_gl_batch_name INTO l_batch_name, l_encumbrance_type;
FETCH cu_gl_batch_name
INTO l_encumbrance_type;

CLOSE cu_gl_batch_name;

/* If batch is as a result of the GL program
"Program - Create Journals" which creates the PO encumbrances
get gl batch name and encumbrance type.
The program creates batches where the name starts with CJE.
The encumbrances are the debit encumbrance entries for the PO
or requisition, the commitment type is as per PO set up for
Purchase Orders (for us Obligation). Any other entries from purchasing
are credits so multiply amount by -1 */
IF ( l_batch_name LIKE 'CJE%'
AND l_encumbrance_type NOT IN ('Obligation', 'Commitment')
)
THEN
l_txn_amt := -1 * l_txn_amt;
END IF;
/*-----AmarS: If PO txn_amount is NULL after above then retrieve from GL.*/
-- Commented by ankush as we are directly fetching the value from GL , there is no point in calling the same twice
-- IF (l_txn_amt IS NULL ) THEN
-- OPEN cu_gl_line_amt;
-- FETCH cu_gl_line_amt INTO l_txn_amt;
-- CLOSE cu_gl_line_amt;
-- END IF;
ELSIF p_reference_1 = 'REQ'
THEN
-- Added By Ankush Shinde to display the requisition values from JE lines instead of po distribution
-- Marval Log 141720
-- get distribution amt
--OPEN cu_gl_line_amt;
--FETCH cu_gl_line_amt INTO l_txn_amt;
--CLOSE cu_gl_line_amt;
l_txn_amt := NVL (p_accounted_dr, 0) - NVL (p_accounted_cr, 0);

-- OPEN cu_poreq_dist_amt;
-- FETCH cu_poreq_dist_amt INTO l_txn_amt;
-- CLOSE cu_poreq_dist_amt;
-- get gl batch name
OPEN cu_gl_batch_name;

--FETCH cu_gl_batch_name INTO l_batch_name, l_encumbrance_type;
FETCH cu_gl_batch_name
INTO l_encumbrance_type;

CLOSE cu_gl_batch_name;

-- See note for PO's above
IF ( l_batch_name LIKE 'CJE%'
AND l_encumbrance_type NOT IN
('Obligation', 'Commitment', 'Requisitions')
)
THEN
l_txn_amt := -1 * l_txn_amt;
END IF;
END IF;
ELSIF p_source = 'Inventory'
THEN
RETURN 0;

-- get inventory transaction type name
/* OPEN cu_inv_tx_type;
FETCH cu_inv_tx_type
INTO l_txn_type_name;
CLOSE cu_inv_tx_type;*/
IF l_txn_type_name = 'Purchase order receipt'
THEN
-- get tx amt
/* OPEN cu_inv_po_rcpt_amt;
FETCH cu_inv_po_rcpt_amt
INTO l_txn_amt;
CLOSE cu_inv_po_rcpt_amt;*/
NULL;
ELSIF l_txn_type_name = 'Miscellaneous receipt'
THEN
-- get tx amt
/* OPEN cu_inv_misc_rcpt_amt;
FETCH cu_inv_misc_rcpt_amt
INTO l_txn_amt;
CLOSE cu_inv_misc_rcpt_amt;*/
NULL;
ELSIF l_txn_type_name = 'Internal order issue'
THEN
-- get tx amt
/* OPEN cu_inv_poreq_issue_amt;
FETCH cu_inv_poreq_issue_amt
INTO l_txn_amt;
CLOSE cu_inv_poreq_issue_amt;*/
NULL;
ELSE
/* other inventory transaction */
NULL;
/* OPEN cu_inv_other_tx_amt;
FETCH cu_inv_other_tx_amt
INTO l_txn_amt;
CLOSE cu_inv_other_tx_amt;*/
END IF;
ELSIF p_source = 'Receivables'
THEN
IF p_reference_10 = 'AR_ADJUSTMENTS'
THEN
-- get adjustment amt
OPEN cu_ar_adj_amt;

FETCH cu_ar_adj_amt
INTO l_txn_amt;

CLOSE cu_ar_adj_amt;

l_txn_amt := l_txn_amt;
-- +10000; removed by J.K.Pallot to obtain correct transaction amount

IF p_reference_9 = 'ADJ_ADJ'
THEN
l_txn_amt := -1 * l_txn_amt;
END IF;
ELSIF p_reference_10 = 'AR_MISC_CASH_DISTRIBUTIONS'
THEN
-- get receipt no
OPEN cu_ar_misc_cash_rcpt_amt;

FETCH cu_ar_misc_cash_rcpt_amt
INTO l_txn_amt;

CLOSE cu_ar_misc_cash_rcpt_amt;

l_txn_amt := -1 * l_txn_amt;
ELSIF p_reference_10 = 'AR_RECEIVABLE_APPLICATIONS'
THEN
-- get receipt or cm (etc) no from which application was made
OPEN cu_ar_rx_app_applied_amt;

FETCH cu_ar_rx_app_applied_amt
INTO l_txn_amt;

CLOSE cu_ar_rx_app_applied_amt;

IF p_reference_9 IN
('TRADE_APP',
'TRADE_DISCAPP',
'TRADE_GL',
'CMAPP_APP',
'CMAPP_GL'
)
THEN
l_txn_amt := -1 * l_txn_amt;
END IF;
ELSIF p_reference_10 = 'AR_CASH_RECEIPT_HISTORY'
THEN
-- get receipt no
OPEN cu_ar_cash_rcpt_amt;

FETCH cu_ar_cash_rcpt_amt
INTO l_txn_amt;

CLOSE cu_ar_cash_rcpt_amt;

IF p_reference_9 IN
('TRADE_REV', 'TRADE_BANKREV', 'MISC_BANKREV')
THEN
l_txn_amt := -1 * l_txn_amt;
END IF;
ELSIF p_reference_10 = 'RA_CUST_TRX_LINE_GL_DIST'
THEN
-- get invoice/credit memo etc. number
OPEN cu_ar_cust_trx_amt;

FETCH cu_ar_cust_trx_amt
INTO l_txn_amt;

CLOSE cu_ar_cust_trx_amt;

IF p_reference_9 NOT IN
('CM_REC', 'DM_REC', 'CB_REC', 'INV_REC')
THEN
l_txn_amt := -1 * l_txn_amt;
END IF;
END IF;
END IF;

-- if the txn isnt covered by above (l_txn_amt is null)
-- then get gl journal line amount
IF l_txn_amt IS NULL
THEN
--OPEN cu_gl_line_amt;
--FETCH cu_gl_line_amt INTO l_txn_amt;
--CLOSE cu_gl_line_amt;
l_txn_amt := NVL (p_accounted_dr, 0) - NVL (p_accounted_cr, 0);
/* OPEN cu_gl_line_amt;
FETCH cu_gl_line_amt INTO l_txn_amt;
CLOSE cu_gl_line_amt;
/*IF (l_batch_name LIKE 'CJE%'
AND l_encumbrance_type NOT IN ('Obligation','Commitment')) THEN
l_txn_amt := -1*l_txn_amt;
END IF;*/
END IF;

RETURN l_txn_amt;
--
-- Code added by Ayyappan to trap the error INVALID_NUMBER for Management Reports,
-- reported by Brian McVeigh.
--
EXCEPTION
WHEN INVALID_NUMBER
THEN
RETURN (0);
--
-- End of code
--
END fn_sel_txn_amt;

FUNCTION fn_sel_spec_instructions (
p_source IN VARCHAR2,
p_category IN VARCHAR2,
p_actual_flag IN VARCHAR2,
p_je_header_id IN NUMBER,
p_je_line_num IN NUMBER,
p_reference_1 IN VARCHAR2,
p_reference_2 IN VARCHAR2,
p_reference_3 IN VARCHAR2,
p_reference_4 IN VARCHAR2,
p_reference_5 IN VARCHAR2,
p_reference_6 IN VARCHAR2,
p_reference_7 IN VARCHAR2,
p_reference_10 IN VARCHAR2
)
RETURN VARCHAR2
IS
l_text VARCHAR2 (2000) := NULL;

/* Note that whilst some of the AR cursors are the same
they have been declared separately to make code easier
to maintain in case the data to be retrieved changes
for certain tx's */
CURSOR cu_ar_cust_trx_no
IS
SELECT ct.internal_notes
FROM ra_customer_trx_all ct, ra_cust_trx_line_gl_dist_all ctlgd
WHERE ct.customer_trx_id = ctlgd.customer_trx_id
AND ctlgd.cust_trx_line_gl_dist_id = TO_NUMBER (p_reference_3);
BEGIN
IF p_source = 'Receivables'
THEN
IF p_reference_10 = 'RA_CUST_TRX_LINE_GL_DIST'
THEN
-- get invoice/credit memo etc. number
OPEN cu_ar_cust_trx_no;

FETCH cu_ar_cust_trx_no
INTO l_text;

CLOSE cu_ar_cust_trx_no;
END IF;
END IF;

RETURN l_text;
EXCEPTION
WHEN INVALID_NUMBER
THEN
RETURN NULL;
END fn_sel_spec_instructions;

FUNCTION fn_sel_comments (
p_source IN VARCHAR2,
p_category IN VARCHAR2,
p_actual_flag IN VARCHAR2,
p_je_header_id IN NUMBER,
p_je_line_num IN NUMBER,
p_reference_1 IN VARCHAR2,
p_reference_2 IN VARCHAR2,
p_reference_3 IN VARCHAR2,
p_reference_4 IN VARCHAR2,
p_reference_5 IN VARCHAR2,
p_reference_6 IN VARCHAR2,
p_reference_7 IN VARCHAR2,
p_reference_10 IN VARCHAR2
)
RETURN VARCHAR2
IS
l_text VARCHAR2 (2000) := NULL;

/* Note that whilst some of the AR cursors are the same
they have been declared separately to make code easier
to maintain in case the data to be retrieved changes
for certain tx's */
CURSOR cu_ar_cust_trx_no
IS
SELECT ct.comments
FROM ra_customer_trx_all ct, ra_cust_trx_line_gl_dist_all ctlgd
WHERE ct.customer_trx_id = ctlgd.customer_trx_id
AND ctlgd.cust_trx_line_gl_dist_id = TO_NUMBER (p_reference_3);
BEGIN
IF p_source = 'Receivables'
THEN
IF p_reference_10 = 'RA_CUST_TRX_LINE_GL_DIST'
THEN
-- get invoice/credit memo etc. number
OPEN cu_ar_cust_trx_no;

FETCH cu_ar_cust_trx_no
INTO l_text;

CLOSE cu_ar_cust_trx_no;
END IF;
END IF;

RETURN l_text;
EXCEPTION
WHEN INVALID_NUMBER
THEN
RETURN NULL;
END fn_sel_comments;
END xx_gl_to_modue_trx_details_pkg;

Scripts For Oracle Applications

About Me

Previous Posts

GL_IMPORT_REFERENCES