Scripts For Oracle Applications

GL_IMPORT_REFERENCES

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;

testing workflow

in response to a query raised on below URL

http://oracle.anilpassi.com/index.php?option=content&task=view&id=27

Self Service HR Script 3 to allocate responsibilities and create users

set scan on ;
set scan off ;

CREATE OR REPLACE PACKAGE BODY xx_sshr_allocate_resp_pkg IS
--DO NOT RUN THIS WITHOUT CHANGING XXPRD
--REPLACE XXPRD BY RESULT OF BELOW SQL FROM PRODUCTION
--select instance_name from v$instance ;

--Created in Nov 06 by Anil Passi
/*
When By Why
-----------------------------------------------
29Nov06 AnilPassi To allocate responsibilities
01Dec06 Anil Passi To create new users too
 Send emails to new users with their password etc
 Send emails to existing users that they now have sshr
*/
g_instance_name VARCHAR2(100) := 'JUNK';
g_debug_procedure_context VARCHAR2(50);
g_debug_header_context CONSTANT VARCHAR2(80) := 'xxxx_sshr_allocate_resp_pkg.';

PROCEDURE debug_begin_procedure IS
BEGIN

 fnd_log.STRING(log_level => fnd_log.level_statement
 ,module => g_debug_header_context ||
 g_debug_procedure_context
 ,message => 'Begin ' || g_debug_procedure_context);

 IF fnd_global.conc_request_id > 0 AND
 fnd_profile.VALUE('AFLOG_ENABLED') = 'Y'
 THEN
 fnd_file.put_line(which => fnd_file.log
 ,buff => 'Begin ' || g_debug_procedure_context);
 END IF;

END debug_begin_procedure;

PROCEDURE debug_stmt(p_msg IN VARCHAR2) IS
BEGIN

 fnd_log.STRING(log_level => fnd_log.level_statement
 ,module => g_debug_header_context ||
 g_debug_procedure_context
 ,message => p_msg);
 IF fnd_global.conc_request_id > 0
 THEN
 fnd_file.put_line(which => fnd_file.log
 ,buff => p_msg);
 END IF;
END debug_stmt;

PROCEDURE debug_end_procedure IS
BEGIN
 fnd_log.STRING(log_level => fnd_log.level_statement
 ,module => g_debug_header_context ||
 g_debug_procedure_context
 ,message => 'End ' || g_debug_procedure_context);
 IF fnd_global.conc_request_id > 0 AND
 fnd_profile.VALUE('AFLOG_ENABLED') = 'Y'
 THEN
 fnd_file.put_line(which => fnd_file.log
 ,buff => 'End ' || g_debug_procedure_context);
 END IF;

END debug_end_procedure;

PROCEDURE set_debug_context(p_procedure_name IN VARCHAR2) IS
BEGIN
 g_debug_procedure_context := p_procedure_name;
 debug_begin_procedure;
END set_debug_context;

FUNCTION is_user_creation_possible(p_person_id IN INTEGER
 ,p_xxdp OUT xx_windows_logon_table%ROWTYPE)
 RETURN VARCHAR2 IS
 CURSOR c_check IS
 SELECT xxdp.*
 FROM per_people_x ppx, xx_windows_logon_table xxdp
 WHERE ltrim(ppx.employee_number
 ,'0') = ltrim(xxdp.emp_no
 ,'0')
 AND ppx.person_id = p_person_id;
 p_check c_check%ROWTYPE;
BEGIN
 OPEN c_check;
 FETCH c_check
 INTO p_check;
 CLOSE c_check;
 p_xxdp := p_check;
 IF p_check.emp_no IS NULL
 THEN
 RETURN 'No emp_no record in Network Login Table';
 ELSIF p_check.nt_login IS NULL
 THEN
 RETURN 'No NT Login Available for this Person in Network Login Table';
 ELSIF p_check.college_email_address IS NULL
 THEN
 RETURN 'No Email Address for this Person in Network Login Table';
 END IF;
 RETURN NULL;
END is_user_creation_possible;

FUNCTION get_email_from_emp_no(p_emp_no_email IN VARCHAR2
 ,p_test_email IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN

 IF g_instance_name = 'XXPRD'
 THEN

 RETURN p_emp_no_email;
 ELSE
 RETURN p_test_email;
 END IF;
END get_email_from_emp_no;

FUNCTION does_fu_exist(p_fu_name IN VARCHAR2) RETURN BOOLEAN IS
 CURSOR c_check IS
 SELECT 'x' FROM fnd_user fu WHERE fu.user_name = upper(p_fu_name);

 p_check c_check%ROWTYPE;
BEGIN
 OPEN c_check;
 FETCH c_check
 INTO p_check;
 IF c_check%FOUND
 THEN
 CLOSE c_check;
 RETURN TRUE;
 END IF;
 CLOSE c_check;
 RETURN FALSE;
END does_fu_exist;

PROCEDURE send_email_to_new_user(p_xxdp IN xx_windows_logon_table%ROWTYPE
 ,p_user_name IN VARCHAR2
 ,p_password IN VARCHAR2
 ,p_test_email IN VARCHAR2) IS
BEGIN
 DECLARE
 BEGIN
 send_html_email(p_to => get_email_from_emp_no(p_xxdp.college_email_address
 ,p_test_email)
 ,p_from => nvl(p_test_email
 ,'xxmail@gmail.com')
 ,p_subject => 'Welcome to Self Service HR'
 ,p_text => 'Welcome to Self Service HR'
 ,p_html => 'Your User Name : ' ||
 p_user_name ||
 ' Your Password : ' ||
 p_password || ' ' ||
 'This user name and password gives you access the new Self Service HR.' ||
 ' Self Service HR enables Company staff to view and update their own personal data. The information is current and any changes made will be implemented immediately.' ||
 ' Please go to Spectrum following this link <a href="http://anilpassi.com">http://anilpassi.com</a>' ||
 ' where you can log into Self Service HR, find out more and read the FAQs.'
 ,p_smtp_hostname => 'localhost'
 ,p_smtp_portnum => '25');

 END;
END send_email_to_new_user;

PROCEDURE send_email_to_existing_user(p_xxdp IN xx_windows_logon_table%ROWTYPE
 ,p_test_email IN VARCHAR2) IS
BEGIN
 DECLARE
 BEGIN
 send_html_email(p_to => get_email_from_emp_no(p_xxdp.college_email_address
 ,p_test_email)
 ,p_from => nvl(p_test_email
 ,'xxmail@gmail.com')
 ,p_subject => 'Welcome to Self Service HR'
 ,p_text => 'Welcome to Self Service HR'
 ,p_html => 'We are writing to let you know that the next time you log into Oracle Apps you will see a new responsibility, XX HR Employee Self Service. This responsibility gives you access the new Self Service HR feature in Oracle Apps.' ||
 'Self Service HR enables staff to view and update their own personal data.' ||
 'Please go to this link <a href="http://anilpassi.com">http://anilpassi.com</a> to find out more and read the FAQs.' ||
 ' ' || 'Regards' ||
 ' SSHR Rollout Team' ||
 ' ' ||
 'HR Dept'
 ,p_smtp_hostname => 'localhost'
 ,p_smtp_portnum => '25');

 END;
END send_email_to_existing_user;

FUNCTION get_latest_fu(p_proposed_fu_name IN VARCHAR2
 ,p_proposed_offset IN INTEGER) RETURN VARCHAR2 IS
BEGIN
 IF does_fu_exist(p_proposed_fu_name || p_proposed_offset)
 THEN
 RETURN get_latest_fu(p_proposed_fu_name
 ,p_proposed_offset + 1);
 END IF;

 RETURN upper(p_proposed_fu_name || p_proposed_offset);
END get_latest_fu;

FUNCTION get_fu_name(p_nt_login IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
 IF NOT does_fu_exist(p_nt_login)
 THEN
 RETURN upper(p_nt_login);
 END IF;

 IF NOT does_fu_exist(p_nt_login)
 THEN
 RETURN upper(p_nt_login);
 END IF;

 RETURN get_latest_fu(p_nt_login
 ,1);
END get_fu_name;

FUNCTION get_user_name_from_fu_per_id(p_person_id IN VARCHAR2)
 RETURN VARCHAR2 IS
 CURSOR c_get IS
 SELECT fu.user_name
 FROM fnd_user fu
 WHERE fu.employee_id = p_person_id;

 p_get c_get%ROWTYPE;
BEGIN
 OPEN c_get;

 FETCH c_get
 INTO p_get;

 CLOSE c_get;

 RETURN p_get.user_name;
END get_user_name_from_fu_per_id;

FUNCTION get_random_password RETURN VARCHAR2 IS
BEGIN
 RETURN lower(dbms_random.STRING('X'
 ,8));
END get_random_password;

FUNCTION get_person_name(p_person_id IN INTEGER) RETURN VARCHAR2 IS
 CURSOR c_get IS
 SELECT full_name
 FROM per_all_people_f
 WHERE person_id = p_person_id
 ORDER BY effective_start_date DESC;
 p_get c_get%ROWTYPE;

BEGIN
 OPEN c_get;
 FETCH c_get
 INTO p_get;
 CLOSE c_get;
 RETURN p_get.full_name;
END get_person_name;

PROCEDURE create_fnd_user_for_emp_no(p_user_name IN VARCHAR2
 ,p_person_id IN INTEGER
 ,p_email_address IN VARCHAR2
 ,p_person_description IN VARCHAR2
 ,p_password OUT VARCHAR2) IS
 v_session_id VARCHAR2(200);
 v_password VARCHAR2(100) := get_random_password;
BEGIN
 p_password := v_password;
 fnd_user_pkg.createuser(x_user_name => p_user_name
 ,x_owner => ''
 ,x_unencrypted_password => v_password
 ,x_description => p_person_description
 ,x_password_lifespan_days => 180
 ,x_employee_id => p_person_id
 ,x_email_address => p_email_address);

END create_fnd_user_for_emp_no;

FUNCTION get_fu_id(p_fu_name IN VARCHAR2) RETURN VARCHAR2 IS
 CURSOR c_get IS
 SELECT user_id FROM fnd_user WHERE user_name = p_fu_name;
 p_get c_get%ROWTYPE;
BEGIN
 OPEN c_get;
 FETCH c_get
 INTO p_get;
 CLOSE c_get;
 RETURN p_get.user_id;
END get_fu_id;

FUNCTION create_fnd_user(p_person_id IN INTEGER
 ,p_xxdp IN xx_windows_logon_table%ROWTYPE
 ,p_new_fnd_user_name OUT VARCHAR2
 ,p_new_fnd_user_password OUT VARCHAR2)
 RETURN INTEGER IS
 v_user_name fnd_user.user_name%TYPE;
 v_password VARCHAR2(200);
 v_err VARCHAR2(2000);
BEGIN
 v_user_name := get_fu_name(p_nt_login => p_xxdp.nt_login);
 debug_stmt('For p_xxdp.nt_login=>' || p_xxdp.nt_login ||
 ' the username is ' || v_user_name);
 create_fnd_user_for_emp_no(p_user_name => p_xxdp.nt_login
 ,p_person_id => p_person_id
 ,p_email_address => p_xxdp.college_email_address
 ,p_person_description => p_xxdp.title || ' ' ||
 p_xxdp.first_name || ' ' ||
 p_xxdp.last_name
 ,p_password => v_password);
 p_new_fnd_user_name := v_user_name;
 p_new_fnd_user_password := v_password;
 RETURN get_fu_id(p_fu_name => v_user_name);
EXCEPTION
 WHEN OTHERS THEN
 v_err := substr(SQLERRM
 ,1
 ,2000);
 debug_stmt(v_err);
 RETURN NULL;
END create_fnd_user;

PROCEDURE send_html_email(p_to IN VARCHAR2
 ,p_from IN VARCHAR2
 ,p_subject IN VARCHAR2
 ,p_text IN VARCHAR2 DEFAULT NULL
 ,p_html IN VARCHAR2 DEFAULT NULL
 ,p_smtp_hostname IN VARCHAR2
 ,p_smtp_portnum IN VARCHAR2) IS
 l_boundary VARCHAR2(255) DEFAULT 'a1b2c3d4e3f2g1';
 l_connection utl_smtp.connection;
 l_body_html CLOB := empty_clob; --This LOB will be the email message
 l_offset NUMBER;
 l_ammount NUMBER;
 l_temp VARCHAR2(32767) DEFAULT NULL;
BEGIN
 /* Usage......
 html_email(p_to => 'a.passi@Company.ac.uk'
 ,p_from => 'anilpassi@gmail.com'
 ,p_subject => 'Testing from anil'
 ,p_text => 'ABCD'
 ,p_html => 'IJKLM Testing for the HTML Format of the email'
 ,p_smtp_hostname => 'localhost'
 ,p_smtp_portnum => '25');
 */
 l_connection := utl_smtp.open_connection(p_smtp_hostname
 ,p_smtp_portnum);
 utl_smtp.helo(l_connection
 ,p_smtp_hostname);
 utl_smtp.mail(l_connection
 ,p_from);
 utl_smtp.rcpt(l_connection
 ,p_to);

 l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
 l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
 l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
 l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
 l_temp := l_temp || 'Reply-To: ' || p_from || chr(13) || chr(10);
 l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
 chr(34) || l_boundary || chr(34) || chr(13) || chr(10);

 ----------------------------------------------------
 -- Write the headers
 dbms_lob.createtemporary(l_body_html
 ,FALSE
 ,10);
 dbms_lob.WRITE(l_body_html
 ,length(l_temp)
 ,1
 ,l_temp);

 ----------------------------------------------------
 -- Write the text boundary
 l_offset := dbms_lob.getlength(l_body_html) + 1;
 l_temp := '--' || l_boundary || chr(13) || chr(10);
 l_temp := l_temp || 'content-type: text/plain; charset=us-ascii' ||
 chr(13) || chr(10) || chr(13) || chr(10);
 dbms_lob.WRITE(l_body_html
 ,length(l_temp)
 ,l_offset
 ,l_temp);

 ----------------------------------------------------
 -- Write the plain text portion of the email
 l_offset := dbms_lob.getlength(l_body_html) + 1;
 dbms_lob.WRITE(l_body_html
 ,length(p_text)
 ,l_offset
 ,p_text);

 ----------------------------------------------------
 -- Write the HTML boundary
 l_temp := chr(13) || chr(10) || chr(13) || chr(10) || '--' ||
 l_boundary || chr(13) || chr(10);
 l_temp := l_temp || 'content-type: text/html;' || chr(13) || chr(10) ||
 chr(13) || chr(10);
 l_offset := dbms_lob.getlength(l_body_html) + 1;
 dbms_lob.WRITE(l_body_html
 ,length(l_temp)
 ,l_offset
 ,l_temp);

 ----------------------------------------------------
 -- Write the HTML portion of the message
 l_offset := dbms_lob.getlength(l_body_html) + 1;
 dbms_lob.WRITE(l_body_html
 ,length(p_html)
 ,l_offset
 ,p_html);

 ----------------------------------------------------
 -- Write the final html boundary
 l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);
 l_offset := dbms_lob.getlength(l_body_html) + 1;
 dbms_lob.WRITE(l_body_html
 ,length(l_temp)
 ,l_offset
 ,l_temp);

 ----------------------------------------------------
 -- Send the email in 1900 byte chunks to UTL_SMTP
 l_offset := 1;
 l_ammount := 1900;
 utl_smtp.open_data(l_connection);
 WHILE l_offset < dbms_lob.getlength(l_body_html)
 LOOP
 utl_smtp.write_data(l_connection
 ,dbms_lob.substr(l_body_html
 ,l_ammount
 ,l_offset));
 l_offset := l_offset + l_ammount;
 l_ammount := least(1900
 ,dbms_lob.getlength(l_body_html) - l_ammount);
 END LOOP;
 utl_smtp.close_data(l_connection);
 utl_smtp.quit(l_connection);
 dbms_lob.freetemporary(l_body_html);
END send_html_email;

PROCEDURE excel_output(p_msg IN VARCHAR2) IS
BEGIN
 fnd_file.put_line(fnd_file.output
 ,p_msg);
END excel_output;

FUNCTION get_user_name(p_user_id IN INTEGER) RETURN VARCHAR2 IS
 CURSOR c_get IS
 SELECT user_name FROM fnd_user WHERE user_id = p_user_id;
 p_get c_get%ROWTYPE;
BEGIN
 OPEN c_get;
 FETCH c_get
 INTO p_get;
 CLOSE c_get;
 RETURN p_get.user_name;
END get_user_name;

FUNCTION get_emp_no(p_person_id IN INTEGER) RETURN VARCHAR2 IS
 CURSOR c_get IS
 SELECT employee_number
 FROM xx_per_all_people_x
 WHERE person_id = p_person_id;
 p_get c_get%ROWTYPE;
BEGIN
 OPEN c_get;
 FETCH c_get
 INTO p_get;
 CLOSE c_get;
 RETURN p_get.employee_number;
END get_emp_no;

FUNCTION get_cost_centre_group(p_person_id IN INTEGER) RETURN VARCHAR2 IS
 CURSOR c_get IS
 SELECT hrou1.attribute5
 FROM hr_all_organization_units hrou1
 ,hr_all_organization_units hrou
 ,xx_per_all_asg_x ass
 ,xx_per_all_people_x ppx
 WHERE ppx.person_id = p_person_id
 AND ass.person_id = ppx.person_id
 AND ass.assignment_number IS NOT NULL
 AND ass.primary_flag = 'Y'
 AND hrou.organization_id = ass.organization_id
 AND hrou1.NAME = primaryhro_pkg.fn_get_primaryhro(ass.organization_id);
 p_get c_get%ROWTYPE;
BEGIN
 OPEN c_get;
 FETCH c_get
 INTO p_get;
 CLOSE c_get;
 RETURN p_get.attribute5;
END get_cost_centre_group;

FUNCTION get_parent_org(p_person_id IN INTEGER) RETURN VARCHAR2 IS
 CURSOR c_get IS
 SELECT primaryhro_pkg.fn_get_primaryhro(ass.organization_id) parent_org
 FROM hr_all_organization_units hrou
 ,xx_per_all_asg_x ass
 ,xx_per_all_people_x ppx
 WHERE ppx.person_id = p_person_id
 AND ass.person_id = ppx.person_id
 AND ass.assignment_number IS NOT NULL
 AND ass.primary_flag = 'Y'
 AND hrou.organization_id = ass.organization_id;
 p_get c_get%ROWTYPE;
BEGIN
 OPEN c_get;
 FETCH c_get
 INTO p_get;
 CLOSE c_get;
 RETURN p_get.parent_org;
END get_parent_org;

FUNCTION get_grade(p_person_id IN INTEGER) RETURN VARCHAR2 IS
 CURSOR c_get IS
 SELECT pg.NAME
 FROM per_grade_definitions pgd
 ,per_grades pg
 ,xx_per_all_asg_x ass
 ,xx_per_all_people_x ppx
 WHERE ppx.person_id = p_person_id
 AND ass.person_id = ppx.person_id
 AND ass.assignment_number IS NOT NULL
 AND ass.primary_flag = 'Y'
 AND pg.grade_id = ass.grade_id
 AND pgd.grade_definition_id = pg.grade_definition_id;
 p_get c_get%ROWTYPE;
BEGIN
 OPEN c_get;
 FETCH c_get
 INTO p_get;
 CLOSE c_get;
 RETURN p_get.NAME;
END get_grade;

PROCEDURE run(errbuf OUT VARCHAR2
 ,retcode OUT VARCHAR2
 ,p_responsibility_name IN VARCHAR2
 ,p_person_type IN VARCHAR2
 ,p_cost_centre_group_1 IN VARCHAR2
 ,p_cost_centre_group_2 IN VARCHAR2
 ,p_parent_org_1 IN VARCHAR2
 ,p_parent_org_2 IN VARCHAR2
 ,p_emp_no IN VARCHAR2
 ,p_read_only_flag IN VARCHAR2
 ,p_test_ceration_email_address IN VARCHAR2) IS
 n_count INTEGER;
 v_sqlerrm VARCHAR2(2000);
 can_not_fnd_create_user EXCEPTION;
 error_in_fnd_user_pkg EXCEPTION;
 v_fnd_user_name VARCHAR2(100);
 CURSOR c_get IS
 SELECT *
 FROM fnd_responsibility_vl
 WHERE responsibility_name =
 nvl('XX HR Employee Self Service'
 ,p_responsibility_name);

 p_get c_get%ROWTYPE;

 duplicate_responsibility EXCEPTION;
 PRAGMA EXCEPTION_INIT(duplicate_responsibility
 ,-20001);
 v_hard_password VARCHAR2(1) := fnd_profile.VALUE('SIGNON_PASSWORD_HARD_TO_GUESS');
 l_xxdp xx_windows_logon_table%ROWTYPE;
 b_new_user_created BOOLEAN;
 v_fnd_user_password VARCHAR2(100);
BEGIN
 set_debug_context('run');

 SELECT instance_name INTO g_instance_name FROM v$instance;
 debug_stmt('g_instance_name=>' || g_instance_name);
 fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
 ,val => 'N');

 OPEN c_get;
 FETCH c_get
 INTO p_get;
 CLOSE c_get;

 --lets dump the records first into the temp table,
 --this will be followed by
 --a. see which people do not have Sign On
 --b. Which people already have Responsibility
 INSERT INTO xx_sshr_allocate_resp
 (sshr_allocate_resp_id
 ,person_id
 ,future_dated_employee_flag
 ,responsibillity_name
 ,error_during_resp_allocation
 ,fnd_user_id
 ,fnd_request_id
 ,email_address)
 (SELECT xx_sshr_allocate_resp_s.NEXTVAL
 ,ppx.person_id --PERSON_ID
 ,'N' --FUTURE_DATED_EMPLOYEE_FLAG
 ,p_responsibility_name --responsibillity_name
 ,NULL --ERROR_DURING_RESP_ALLOCATION
 ,NULL --FND_USER_ID
 ,fnd_global.conc_request_id --FND_REQUEST_ID
 ,ppx.email_address
 FROM per_person_types ppt
 ,per_person_type_usages_x pptux
 ,xx_per_all_people_x ppx
 WHERE ppx.person_id = pptux.person_id
 AND ppt.person_type_id = pptux.person_type_id
 AND ppx.employee_number = nvl(p_emp_no
 ,ppx.employee_number)
 AND ppt.system_person_type = 'EMP'
 AND ppt.user_person_type = p_person_type
 AND ppt.business_group_id =
 fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
 AND EXISTS (SELECT 'x'
 FROM hr_all_organization_units hrou1
 ,hr_all_organization_units hrou
 ,xx_per_all_asg_x pax
 WHERE p_cost_centre_group_1 IS NOT NULL
 AND pax.person_id = ppx.person_id
 AND pax.primary_flag = 'Y'
 AND pax.assignment_number IS NOT NULL
 AND hrou.organization_id = pax.organization_id
 AND hrou1.NAME =
 primaryhro_pkg.fn_get_primaryhro(pax.organization_id)
 AND hrou1.attribute5 IN
 (nvl(p_cost_centre_group_1
 ,'XXXX'), nvl(p_cost_centre_group_2
 ,'XXXX'))
 UNION ALL
 SELECT 'x'
 FROM dual
 WHERE (p_cost_centre_group_1 IS NULL AND p_cost_centre_group_2 IS NULL))
 AND EXISTS
 (SELECT 'x'
 FROM hr_all_organization_units hrou, xx_per_all_asg_x pax
 WHERE p_parent_org_1 IS NOT NULL
 AND pax.person_id = ppx.person_id
 AND pax.primary_flag = 'Y'
 AND pax.assignment_number IS NOT NULL
 AND hrou.organization_id = pax.organization_id
 AND primaryhro_pkg.fn_get_primaryhro(pax.organization_id) IN
 (nvl(p_parent_org_1
 ,'XXXX'), nvl(p_parent_org_2
 ,'XXXX'))
 UNION ALL
 SELECT 'x'
 FROM dual
 WHERE (p_parent_org_1 IS NULL AND p_parent_org_2 IS NULL)));
 n_count := SQL%ROWCOUNT;

 debug_stmt(n_count ||
 ' Records inserted into Temp Table based on Eligibility Criteria');

 INSERT INTO xx_sshr_allocate_resp
 (sshr_allocate_resp_id
 ,person_id
 ,future_dated_employee_flag
 ,responsibillity_name
 ,error_during_resp_allocation
 ,fnd_user_id
 ,fnd_request_id
 ,email_address)
 (SELECT xx_sshr_allocate_resp_s.NEXTVAL
 ,ppx.person_id --PERSON_ID
 ,'Y' --FUTURE_DATED_EMPLOYEE_FLAG
 ,p_responsibility_name --responsibillity_name
 ,'Employee Is a Future Starter' --ERROR_DURING_RESP_ALLOCATION
 ,NULL --FND_USER_ID
 ,fnd_global.conc_request_id --FND_REQUEST_ID
 ,ppx.email_address
 FROM per_person_types ppt
 ,xx_per_person_type_usages_eot pptux
 ,xx_per_all_people_eot ppx
 WHERE NOT EXISTS
 (SELECT 'x'
 FROM xx_sshr_allocate_resp iar
 WHERE iar.person_id = ppx.person_id
 AND fnd_request_id = fnd_global.conc_request_id)
 AND ppx.person_id = pptux.person_id
 AND ppt.person_type_id = pptux.person_type_id
 AND ppx.employee_number = nvl(p_emp_no
 ,ppx.employee_number)
 AND ppt.system_person_type = 'EMP'
 AND ppt.user_person_type = p_person_type
 AND ppt.business_group_id =
 fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
 AND EXISTS (SELECT 'x'
 FROM hr_all_organization_units hrou1
 ,hr_all_organization_units hrou
 ,xx_per_all_asg_x pax
 WHERE p_cost_centre_group_1 IS NOT NULL
 AND pax.person_id = ppx.person_id
 AND pax.primary_flag = 'Y'
 AND pax.assignment_number IS NOT NULL
 AND hrou.organization_id = pax.organization_id
 AND hrou1.NAME =
 primaryhro_pkg.fn_get_primaryhro(pax.organization_id)
 AND hrou1.attribute5 IN
 (nvl(p_cost_centre_group_1
 ,'XXXX'), nvl(p_cost_centre_group_2
 ,'XXXX'))
 UNION ALL
 SELECT 'x'
 FROM dual
 WHERE (p_cost_centre_group_1 IS NULL AND p_cost_centre_group_2 IS NULL))
 AND EXISTS
 (SELECT 'x'
 FROM hr_all_organization_units hrou, xx_per_all_asg_x pax
 WHERE p_parent_org_1 IS NOT NULL
 AND pax.person_id = ppx.person_id
 AND pax.primary_flag = 'Y'
 AND pax.assignment_number IS NOT NULL
 AND hrou.organization_id = pax.organization_id
 AND primaryhro_pkg.fn_get_primaryhro(pax.organization_id) IN
 (nvl(p_parent_org_1
 ,'XXXX'), nvl(p_parent_org_2
 ,'XXXX'))
 UNION ALL
 SELECT 'x'
 FROM dual
 WHERE (p_parent_org_1 IS NULL AND p_parent_org_2 IS NULL)));
 n_count := SQL%ROWCOUNT;
 debug_stmt(n_count ||
 ' Records inserted into Temp Table that aer eligible but Future Dated');
 --Commenting the below, as we need to create User Accounts for these folks
 /* UPDATE xx_sshr_allocate_resp isar
 SET error_during_resp_allocation = 'Employee Is Not a User'
 WHERE isar.fnd_request_id = fnd_global.conc_request_id
 AND error_during_resp_allocation IS NULL
 AND NOT EXISTS
 (SELECT 'x' FROM fnd_user fu WHERE fu.employee_id = isar.person_id);
 n_count := SQL%ROWCOUNT;
 put_log(n_count || ' Records errored due to them not being Employee');
 */
 UPDATE xx_sshr_allocate_resp isar
 SET fnd_user_id = (SELECT user_id
 FROM fnd_user
 WHERE employee_id = isar.person_id
 AND rownum < 2)
 WHERE isar.fnd_request_id = fnd_global.conc_request_id
 AND error_during_resp_allocation IS NULL;

 UPDATE xx_sshr_allocate_resp isar
 SET responsibility_alloc_date = (SELECT start_date
 FROM fnd_user_resp_groups_direct
 WHERE user_id = isar.fnd_user_id
 AND responsibility_id =
 p_get.responsibility_id
 AND rownum < 2)
 WHERE isar.fnd_request_id = fnd_global.conc_request_id;

 n_count := SQL%ROWCOUNT;
 debug_stmt(n_count ||
 ' Records were attempted to be assigned existing responsibility_alloc_date');

 UPDATE xx_sshr_allocate_resp isar
 SET error_during_resp_allocation = 'Responsibility Already Allocated on ' ||
 to_char(responsibility_alloc_date
 ,'DD-MON-YYYY')
 WHERE isar.fnd_request_id = fnd_global.conc_request_id
 AND responsibility_alloc_date IS NOT NULL;
 n_count := SQL%ROWCOUNT;
 debug_stmt(n_count ||
 ' Records errored as they already have the responsibility');

 /* UPDATE xx_sshr_allocate_resp isar
 SET error_during_resp_allocation = 'Employees User Record is Terminated'
 WHERE isar.fnd_request_id = fnd_global.conc_request_id
 AND error_during_resp_allocation IS NULL
 AND EXISTS (SELECT 'x'
 FROM fnd_user fu
 WHERE fu.employee_id = isar.person_id
 AND NOT (trunc(SYSDATE) BETWEEN
 nvl(fu.start_date
 ,trunc(SYSDATE)) AND
 nvl(fu.start_date
 ,trunc(SYSDATE))));
 n_count := SQL%ROWCOUNT;
 put_log(n_count || ' Records errored as their FND_USER is end dated');
 */

 UPDATE xx_sshr_allocate_resp isar
 SET error_during_resp_allocation = 'No Email Address'
 WHERE isar.fnd_request_id = fnd_global.conc_request_id
 AND isar.email_address IS NULL
 AND error_during_resp_allocation IS NULL;
 n_count := SQL%ROWCOUNT;
 debug_stmt(n_count ||
 ' Records errored as they have no email address in HRMS');

 UPDATE xx_sshr_allocate_resp isar
 SET fnd_user_id = (SELECT user_id
 FROM fnd_user
 WHERE employee_id = isar.person_id
 AND rownum < 2)
 WHERE isar.fnd_request_id = fnd_global.conc_request_id
 AND error_during_resp_allocation IS NULL;

 n_count := SQL%ROWCOUNT;
 debug_stmt(n_count ||
 ' Records aer unerrored, and hence will be processed further');

 excel_output('Action' || chr(9) || 'UserName' || chr(9) || 'emp_no' ||
 chr(9) || 'Person Full Name' || chr(9) ||
 'Allocation Date' || chr(9) || 'Error' || chr(9) ||
 'cost_centre_group' || chr(9) || 'parent_org' || chr(9) || 'Grade');

 FOR p_rec IN (SELECT *
 FROM xx_sshr_allocate_resp isar
 WHERE isar.fnd_request_id = fnd_global.conc_request_id
 AND error_during_resp_allocation IS NULL)
 LOOP
 BEGIN
 l_xxdp := NULL;
 v_fnd_user_password := NULL;
 b_new_user_created := FALSE;
 v_fnd_user_name := NULL;
 v_sqlerrm := is_user_creation_possible(p_person_id => p_rec.person_id
 ,p_xxdp => l_xxdp);
 debug_stmt('p_rec.fnd_user_id =>' || p_rec.fnd_user_id);
 debug_stmt('Is user creation possible returned => ' || v_sqlerrm);
 IF p_rec.fnd_user_id IS NULL AND v_sqlerrm IS NOT NULL
 THEN

 RAISE can_not_fnd_create_user;
 END IF;

 IF NOT (p_read_only_flag = 'Y')
 THEN
 debug_stmt('Not read only');
 IF p_rec.fnd_user_id IS NULL
 THEN
 debug_stmt('Looks like new user is needed');

 p_rec.fnd_user_id := create_fnd_user(p_person_id => p_rec.person_id
 ,p_xxdp => l_xxdp
 ,p_new_fnd_user_name => v_fnd_user_name
 ,p_new_fnd_user_password => v_fnd_user_password);
 IF p_rec.fnd_user_id IS NULL
 THEN
 RAISE error_in_fnd_user_pkg;
 ELSE
 UPDATE xx_sshr_allocate_resp ir
 SET ir.fnd_user_id = p_rec.fnd_user_id
 ,new_fnd_user_flag = 'Y'
 ,messsage_code = v_fnd_user_password
 WHERE ir.sshr_allocate_resp_id = p_rec.sshr_allocate_resp_id;
 b_new_user_created := TRUE;
 END IF;
 END IF;
 fnd_user_resp_groups_api.insert_assignment(user_id => p_rec.fnd_user_id
 ,responsibility_id => p_get.responsibility_id
 ,responsibility_application_id => p_get.application_id
 ,security_group_id => 0
 ,start_date => trunc(SYSDATE)
 ,end_date => NULL
 ,description => 'Auto Allocation for SSHR');
 UPDATE xx_sshr_allocate_resp
 SET responsibility_alloc_date = SYSDATE
 WHERE sshr_allocate_resp_id = p_rec.sshr_allocate_resp_id;
 IF b_new_user_created
 THEN
 excel_output('Allocated[With New User]' || chr(9) ||
 get_user_name(p_rec.fnd_user_id) || chr(9) ||
 get_emp_no(p_rec.person_id) || chr(9) ||
 get_person_name(p_rec.person_id) || chr(9) ||
 to_char(trunc(SYSDATE)
 ,'DD-MON-YYYY') || chr(9) || '' || chr(9) ||
 get_cost_centre_group(p_rec.person_id) || chr(9) ||
 get_parent_org(p_rec.person_id) || chr(9) ||
 get_grade(p_rec.person_id));
 send_email_to_new_user(p_xxdp => l_xxdp
 ,p_user_name => v_fnd_user_name
 ,p_password => v_fnd_user_password
 ,p_test_email => p_test_ceration_email_address);
 ELSE
 excel_output('Allocated' || chr(9) ||
 get_user_name(p_rec.fnd_user_id) || chr(9) ||
 get_emp_no(p_rec.person_id) || chr(9) ||
 get_person_name(p_rec.person_id) || chr(9) ||
 to_char(trunc(SYSDATE)
 ,'DD-MON-YYYY') || chr(9) || '' || chr(9) ||
 get_cost_centre_group(p_rec.person_id) || chr(9) ||
 get_parent_org(p_rec.person_id) || chr(9) ||
 get_grade(p_rec.person_id));
 send_email_to_existing_user(p_xxdp => l_xxdp
 ,p_test_email => p_test_ceration_email_address);
 END IF;
 COMMIT;
 ELSE
 IF p_rec.fnd_user_id IS NULL
 THEN
 excel_output('Eligible [New User Will Be Created]' || chr(9) ||
 nvl(get_user_name(p_rec.fnd_user_id)
 ,get_fu_name(l_xxdp.nt_login)) || chr(9) ||
 get_emp_no(p_rec.person_id) || chr(9) ||
 get_person_name(p_rec.person_id) || chr(9) ||
 chr(9) || chr(9) || get_cost_centre_group(p_rec.person_id) ||
 chr(9) || get_parent_org(p_rec.person_id) || chr(9) ||
 get_grade(p_rec.person_id));
 ELSE
 excel_output('Eligible' || chr(9) ||
 get_user_name(p_rec.fnd_user_id) || chr(9) ||
 get_emp_no(p_rec.person_id) || chr(9) ||
 get_person_name(p_rec.person_id) || chr(9) ||
 chr(9) || chr(9) || get_cost_centre_group(p_rec.person_id) ||
 chr(9) || get_parent_org(p_rec.person_id) || chr(9) ||
 get_grade(p_rec.person_id));
 END IF;
 END IF;
 EXCEPTION
 WHEN can_not_fnd_create_user THEN
 UPDATE xx_sshr_allocate_resp ir
 SET ir.error_during_resp_allocation = v_sqlerrm
 WHERE sshr_allocate_resp_id = p_rec.sshr_allocate_resp_id;
 WHEN error_in_fnd_user_pkg THEN
 UPDATE xx_sshr_allocate_resp ir
 SET ir.error_during_resp_allocation = 'Error while creating FND User. Please see Concurrent Log file for details'
 WHERE sshr_allocate_resp_id = p_rec.sshr_allocate_resp_id;
 WHEN OTHERS THEN
 v_sqlerrm := SQLERRM;
 UPDATE xx_sshr_allocate_resp
 SET error_during_resp_allocation = substr(v_sqlerrm
 ,1
 ,2000)
 WHERE sshr_allocate_resp_id = p_rec.sshr_allocate_resp_id;

 END;
 END LOOP;

 FOR p_recx IN (SELECT *
 FROM xx_sshr_allocate_resp isar
 WHERE isar.fnd_request_id = fnd_global.conc_request_id
 AND error_during_resp_allocation IS NOT NULL)
 LOOP
 excel_output('Error' || chr(9) || get_user_name(p_recx.fnd_user_id) ||
 chr(9) || get_emp_no(p_recx.person_id) || chr(9) ||
 get_person_name(p_recx.person_id) || chr(9) ||
 to_char(p_recx.responsibility_alloc_date
 ,'DD-MON-YYYY') || chr(9) ||
 p_recx.error_during_resp_allocation || chr(9) ||
 get_cost_centre_group(p_recx.person_id) || chr(9) ||
 get_parent_org(p_recx.person_id) || chr(9) ||
 get_grade(p_recx.person_id));
 END LOOP;
 fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
 ,val => v_hard_password);
 debug_end_procedure;
EXCEPTION
 WHEN OTHERS THEN
 fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
 ,val => v_hard_password);
 RAISE;
END run;

END xx_sshr_allocate_resp_pkg;

Scripts For Oracle Applications

About Me

Previous Posts

Archives

GL_IMPORT_REFERENCES

Saturday, December 23, 2006

testing workflow

Saturday, December 09, 2006

Self Service HR Script 3 to allocate responsibilities and create users