Scripts For Oracle Applications

GL_IMPORT_REFERENCES

2007-09-01T09:43:00.000-07:00

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

2006-12-23T00:36:00.000-08:00

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

2006-12-09T19:47:00.000-08:00

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;

Self Service HR rollout Script 2 to create users

2006-12-09T19:46:00.000-08:00

CREATE OR REPLACE PACKAGE xx_sshr_allocate_resp_pkg IS
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_cid                         IN VARCHAR2
               ,p_read_only_flag              IN VARCHAR2
               ,p_test_ceration_email_address IN VARCHAR2);

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);

PROCEDURE create_fnd_user_for_cid(p_user_name          IN VARCHAR2
                                   ,p_person_id          IN INTEGER
                                   ,p_email_address      IN VARCHAR2
                                   ,p_person_description IN VARCHAR2
                                   ,p_password           OUT VARCHAR2);

FUNCTION get_fu_name(p_nt_login IN VARCHAR2) RETURN VARCHAR2;

PROCEDURE send_email_to_new_user(p_icdp       IN ic_coll_dir_pull%ROWTYPE
                                  ,p_user_name IN VARCHAR2
                                  ,p_password   IN VARCHAR2
                                  ,p_test_email IN VARCHAR2);
END xx_sshr_allocate_resp_pkg;

SSHR Rollout Script 1 to Create Users

2006-12-09T19:45:00.000-08:00

YOU HAVE REACHED HERE FROM ORACLE.ANILPASSI.COM

set scan on ;
conn xxschema/&2 ;
--drop table xx_sshr_allocate_resp ;
create table xx_sshr_allocate_resp
(
sshr_allocate_resp_id INTEGER
,person_id INTEGER
,future_dated_employee_flag VARCHAR2(1)
,responsibillity_name VARCHAR2(500)
,error_during_resp_allocation VARCHAR2(4000)
,fnd_user_id VARCHAR2(100)
,fnd_request_id INTEGER
,email_address VARCHAR2(500)
,responsibility_alloc_date DATE
,new_fnd_user_flag VARCHAR2(1)
,messsage_code VARCHAR2(500)
) ;

--DROP sequence xx_sshr_allocate_resp_s ;
create sequence xx_sshr_allocate_resp_s start with 1000 ;

grant all on xx_sshr_allocate_resp to apps;
grant all on xx_sshr_allocate_resp to discover ;

conn apps/&1 ;

create or replace synonym xx_sshr_allocate_resp for xxschema.xx_sshr_allocate_resp ;
create or replace synonym xx_sshr_allocate_resp_s for xxschema.xx_sshr_allocate_resp_s ;

create or replace view xx_per_all_people_x AS
SELECT *
FROM per_all_people_f
WHERE
trunc(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

create or replace view xx_per_all_asg_x AS
SELECT *
FROM per_all_assignments_f
WHERE
trunc(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

create or replace view xx_sshr_allocate_resp_v AS
SELECT isar.*, frv.responsibility_name, frv.start_date, fu.user_name
FROM fnd_responsibility_vl frv
,fnd_user fu
,xx_per_all_people_x ppx
,xx_sshr_allocate_resp isar
WHERE
isar.person_id = ppx.person_id
AND fu.user_id(+) = isar.fnd_user_id
AND frv.responsibility_name(+) = isar.responsibillity_name
;

create or replace view xx_per_all_people_eot AS
SELECT person_id, email_address, employee_number
FROM per_all_people_f
WHERE to_date('31-12-4712'
,'DD-MM-YYYY') BETWEEN effective_start_date AND
effective_end_date
GROUP BY person_id, email_address,employee_number;

create or replace view xx_PER_PERSON_TYPE_USAGES_EOT AS
SELECT person_type_usage_id, person_id, person_type_id
FROM per_person_type_usages_f
WHERE to_date('31-12-4712'
,'DD-MM-YYYY') BETWEEN effective_start_date AND
effective_end_date
GROUP BY person_type_usage_id, person_id, person_type_id;

SELECT *
FROM xx_sshr_allocate_resp_v
WHERE fnd_request_id = fnd_global.conc_request_id;

create index xx_sshr_allocate_resp_U1 ON xx_sshr_allocate_resp ( sshr_allocate_resp_id ) ;
create index xx_sshr_allocate_resp_N1 ON xx_sshr_allocate_resp ( person_id, fnd_request_id ) ;

2006-10-06T16:49:00.000-07:00

Fast Refresh Materialized View GL_CODE_COMBINATIONS with Description

This was my third attempt to get the Fast Refresh Materialized views to work on GL_CODE_COMBINATIONS by tricking Oracle 10g. The descriptions are being fetched by using a function. This function is blanked out to return null just prior to the creation of snapshot.

Surely, the function being used to fetch the description ain't complicated. The comments are below with code. So Finally with this, you can make your GL Queries run very quickly even if you desire to list the Description of GL Codes.

conn apps/&1 ;
PROMPT materialized view log on GL.GL_CODE_COMBINATIONS
create materialized view log on GL.GL_CODE_COMBINATIONS
including new values
;

conn gl/&4;
GRANT ALL ON GL.GL_CODE_COMBINATIONS TO xxschema WITH GRANT OPTION;
GRANT ALL ON GL.MLOG$_GL_CODE_COMBINATIONS TO xxschema ;

conn apps/&1 ;
--First create the proper function such that we can Pre-Build Table
PROMPT FUNCTION xx_valueset_code_description
CREATE OR REPLACE FUNCTION xx_valueset_code_description(
p_code IN VARCHAR2
,p_vs_id IN INTEGER) RETURN VARCHAR2 IS
CURSOR c_get IS
SELECT ffv.description
FROM fnd_flex_values_vl ffv
WHERE flex_value_set_id = p_vs_id
AND flex_value = p_code;
p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;
RETURN p_get.description;
END xx_valueset_code_description;
/

GRANT ALL ON xx_valueset_code_description TO xxschema ;
GRANT EXECUTE ON xx_valueset_code_description TO xxschema ;

--Create the Pre-Build table
PROMPT table xxschema.IC_GL_CODE_COMBINATIONS_MV
create table xxschema.IC_GL_CODE_COMBINATIONS_MV AS
SELECT cc.segment2 costcentre
,apps.xx_valueset_code_description(cc.segment2, 1002651) AS costcentre_desc
,cc.segment3 project
,apps.xx_valueset_code_description(cc.segment3,1002652) project_desc
,cc.segment4 expensetype
,apps.xx_valueset_code_description(cc.segment4,1002653) AS expensetype_desc
,cc.code_combination_id
FROM gl.gl_code_combinations cc
/*WHERE LAST_UPDATE_DATE > SYSDATE - 100*/;

--Here is the trick of fooling Oracle. We make the function to be used in MV look simple
PROMPT FUNCTION xx_valueset_code_description
CREATE OR REPLACE FUNCTION xx_valueset_code_description(
p_code IN VARCHAR2
,p_vs_id IN INTEGER) RETURN VARCHAR2 IS
BEGIN
RETURN NULL ;
END xx_valueset_code_description;
/

create or replace synonym IC_GL_CODE_COMBINATIONS_MV for xxschema.IC_GL_CODE_COMBINATIONS_MV ;

--Now create the snapshot of MV, poor Oracle thinks its going to use a simple Function
PROMPT materialized view xxschema.IC_GL_CODE_COMBINATIONS_MV
create materialized view xxschema.IC_GL_CODE_COMBINATIONS_MV
on prebuilt table
refresh fast
on commit
as
SELECT
cc.segment2 costcentre
--replace 10001/2/3 by your respective value set id's for GL Segments
,apps.xx_valueset_code_description(cc.segment2, 10001) AS costcentre_desc
,cc.segment3 project
,apps.xx_valueset_code_description(cc.segment3,10002) project_desc
,cc.segment4 expensetype
,apps.xx_valueset_code_description(cc.segment4,10003) AS expensetype_desc
,cc.code_combination_id
FROM gl.gl_code_combinations cc ;

--Now put the proper function back to where it was
PROMPT FUNCTION xx_valueset_code_description
CREATE OR REPLACE FUNCTION xx_valueset_code_description(
p_code IN VARCHAR2
,p_vs_id IN INTEGER) RETURN VARCHAR2 IS
CURSOR c_get IS
SELECT ffv.description
FROM fnd_flex_values_vl ffv
WHERE flex_value_set_id = p_vs_id
AND flex_value = p_code;
p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;
RETURN p_get.description;
END xx_valueset_code_description;
/

--Ensure that the MV is compiled
ALTER materialized view xxschema.IC_GL_CODE_COMBINATIONS_MV COMPILE ;

Make sure that you create all the required indexes on the Materialized view, depending upon the structure of your GL Accounting Flexfield.

2006-10-06T04:22:00.000-07:00

ORA-12054 for Materialized View on GL_CODE_COMBINATIONS

The below script was my second attempt to create a fast refresh Materialized View in Oracle. The last step in the scipt failed because Oracle is unable to create a Fast Materialized view referencing a package function because it deems this to be complicated.

Anyway, in the third attempt, we will do a trickery to fool Oracle...

conn apps/&1 ;
create materialized view log on GL.GL_CODE_COMBINATIONS
with rowid (
CODE_COMBINATION_ID
,CHART_OF_ACCOUNTS_ID
,DETAIL_POSTING_ALLOWED_FLAG
,DETAIL_BUDGETING_ALLOWED_FLAG
,ACCOUNT_TYPE
,ENABLED_FLAG
,SUMMARY_FLAG
,SEGMENT1
,SEGMENT2
,SEGMENT3
,SEGMENT4
,TEMPLATE_ID
,START_DATE_ACTIVE
,END_DATE_ACTIVE
)
including new values
;

conn gl/&4;
GRANT SELECT ON GL.GL_CODE_COMBINATIONS TO xxschema WITH GRANT OPTION;
GRANT ALL ON GL.MLOG$_GL_CODE_COMBINATIONS TO xxschema ;

conn apps/&1 ;

CREATE OR REPLACE PACKAGE xx_mv_util AS
FUNCTION get_vs_code_description(p_code IN VARCHAR2
,p_vs_id IN INTEGER) RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(get_vs_code_description
,WNDS
,WNPS
,RNPS);
END xx_mv_util;
/

GRANT ALL ON xx_mv_util TO xxschema ;

CREATE OR REPLACE PACKAGE BODY xx_mv_util AS
FUNCTION get_vs_code_description(p_code IN VARCHAR2
,p_vs_id IN INTEGER) RETURN VARCHAR2 IS
CURSOR c_get IS
SELECT ffv.description
FROM fnd_flex_values_vl ffv
WHERE flex_value_set_id = p_vs_id
AND flex_value = p_code;
p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;
RETURN p_get.description;
END get_vs_code_description;
END xx_mv_util;
/

create table xxschema.IC_GL_CODE_COMBINATIONS_MV AS
SELECT cc.segment2 costcentre
,xx_mv_util.get_vs_code_description(cc.segment2, 1002651) AS costcentre_desc
,cc.segment3 activity
,xx_mv_util.get_vs_code_description(cc.segment3,1002652) activity_desc
,cc.segment4 analysis
,cc.segment4 new_analysis
,xx_mv_util.get_vs_code_description(cc.segment4,1002653) AS analysis_desc
,cc.code_combination_id
FROM gl.gl_code_combinations cc ;

create materialized view IC_GL_CODE_COMBINATIONS_MV
on prebuilt table
refresh fast
on commit
as
SELECT
cc.segment2 costcentre
,xx_mv_util.get_vs_code_description(cc.segment2, 1002651) AS costcentre_desc
,cc.segment3 activity
,xx_mv_util.get_vs_code_description(cc.segment3,1002652) activity_desc
,cc.segment4 analysis
,cc.segment4 new_analysis
,xx_mv_util.get_vs_code_description(cc.segment4,1002653) AS analysis_desc
,cc.code_combination_id
FROM gl.gl_code_combinations cc ;

This errors with
ERROR at line 13:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

2006-09-30T15:37:00.000-07:00

Scripts for Oracle Workflow Training Lesson 3

The wft file for Oracle Workflow Training Lesson 3 can be downloaded by right clicking here

It is assumed that you have already worked upon the scripts from Training lesson 2.

--Create the table in which transaction progress will be tracked
create table xx_po_wf_progress
(
po_id INTEGER
,p_wf_action_stage VARCHAR2(200)
,p_wf_action_name VARCHAR2(200)
) ;

--Create the pl/sql package
CREATE OR REPLACE PACKAGE xxxx_po_wf_training_pkg IS
PROCEDURE is_po_valid(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2);

PROCEDURE set_wf_approver_role(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2);

PROCEDURE set_wf_status_to_validated(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2);

PROCEDURE start_training_wf(p_po_id IN INTEGER);

PROCEDURE xx_validate_response(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2);

PROCEDURE log_status_of_action(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2);

END xxxx_po_wf_training_pkg;
/

--create the package body
CREATE OR REPLACE PACKAGE BODY xxxx_po_wf_training_pkg IS

PROCEDURE debug(p_text IN VARCHAR2) IS
BEGIN
-- apassi1(p_text);
NULL;
END debug;

FUNCTION is_po_validated(p_po_id IN INTEGER) RETURN BOOLEAN IS
BEGIN
IF p_po_id > 0
THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END is_po_validated;

PROCEDURE is_po_valid(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
BEGIN
IF (funcmode != 'RUN')
THEN
RETURN;
END IF;
--Just a pseudo validation
--call a function here, and either assign COMPLETE:Y or COMPLETE:N

IF is_po_validated(p_po_id => wf_engine.getitemattrnumber(itemtype => itemtype
,itemkey => itemkey
,aname => 'PO_ID'))
THEN
RESULT := 'COMPLETE:Y';
ELSE
RESULT := 'COMPLETE:N';
END IF;
END is_po_valid;

PROCEDURE set_wf_approver_role(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
v_role_email xxxx_po_headers.send_email_to%TYPE;
n_ctr INTEGER := 0;
BEGIN
v_role_email := upper(wf_engine.getitemattrtext(itemtype => itemtype
,itemkey => itemkey
,aname => 'SEND_TO_EMAIL'));
SELECT COUNT(*)
INTO n_ctr
FROM wf_local_roles
WHERE NAME = v_role_email;

IF n_ctr = 0
THEN
wf_directory.createadhocrole(role_name => v_role_email
,role_display_name => v_role_email
,role_description => v_role_email
,notification_preference => 'MAILHTML'
,email_address => v_role_email
,status => 'ACTIVE'
,expiration_date => NULL);
END IF;
wf_engine.setitemattrtext(itemtype => itemtype
,itemkey => itemkey
,aname => 'SEND_TO_ROLE'
,avalue => v_role_email);
RESULT := 'COMPLETE:Y';

RESULT := 'COMPLETE:Y';
END set_wf_approver_role;

PROCEDURE set_wf_status_to_validated(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
BEGIN
UPDATE xxxx_po_headers
SET po_status = 'VALIDATED'
WHERE po_id = wf_engine.getitemattrnumber(itemtype
,itemkey
,'PO_ID');
RESULT := 'COMPLETE:Y';
END set_wf_status_to_validated;

PROCEDURE start_training_wf(p_po_id IN INTEGER) IS
l_itemtype VARCHAR2(30) := 'XXXXPTR';
l_itemkey VARCHAR2(300) := 'TRAINING-' || p_po_id;
CURSOR c_get IS
SELECT * FROM xxxx_po_headers WHERE po_id = p_po_id;
p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;

wf_engine.createprocess(l_itemtype
,l_itemkey
,'MAIN_TRAINING_PROCESS');

wf_engine.setitemuserkey(itemtype => l_itemtype
,itemkey => l_itemkey
,userkey => 'USERKEY: ' || l_itemkey);
wf_engine.setitemowner(itemtype => l_itemtype
,itemkey => l_itemkey
,owner => 'SYSADMIN');

wf_engine.setitemattrnumber(itemtype => l_itemtype
,itemkey => l_itemkey
,aname => 'PO_ID'
,avalue => p_po_id);

wf_engine.setitemattrtext(itemtype => l_itemtype
,itemkey => l_itemkey
,aname => 'SEND_TO_EMAIL'
,avalue => p_get.send_email_to);
wf_engine.setitemattrtext(itemtype => l_itemtype
,itemkey => l_itemkey
,aname => 'PO_DESCRIPTION'
,avalue => p_get.po_description);
wf_engine.startprocess(l_itemtype
,l_itemkey);

END start_training_wf;

PROCEDURE xx_validate_response(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
l_nid NUMBER;
l_activity_result_code VARCHAR2(200);
v_response_reason VARCHAR2(50);
BEGIN
IF (funcmode IN ('RESPOND'))
THEN
l_nid := wf_engine.context_nid;
l_activity_result_code := wf_notification.getattrtext(l_nid
,'XX_RESPONSE_ACTION');
v_response_reason := wf_notification.getattrtext(l_nid
,'XX_RESPONSE_REASON');
IF l_activity_result_code = 'XX_REJECTED' AND
v_response_reason IS NULL
THEN
RESULT := 'ERROR: You must enter rejection reason if rejecting.';
RETURN;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
RESULT := SQLERRM;
END xx_validate_response;

FUNCTION get_request_id_from_item_key(p_item_key IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
RETURN substr(p_item_key
,instr(p_item_key
,'-') + 1);
END get_request_id_from_item_key;

PROCEDURE log_status_of_action(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
n_po_id INTEGER;
v_action_name VARCHAR2(200);
v_action_stage VARCHAR2(200);
BEGIN
IF funcmode = 'RUN'
THEN
n_po_id := get_request_id_from_item_key(itemkey);

v_action_stage := wf_engine.getactivityattrtext(itemtype => itemtype
,itemkey => itemkey
,actid => actid
,aname => 'ACTION_STAGE');

v_action_name := wf_engine.getactivityattrtext(itemtype => itemtype
,itemkey => itemkey
,actid => actid
,aname => 'ACTION_NAME');
INSERT INTO xx_po_wf_progress
(po_id
,p_wf_action_stage
,p_wf_action_name)
VALUES
(n_po_id
,v_action_stage
,v_action_name);
END IF;
RESULT := 'COMPLETE:Y';
END log_status_of_action;

END xxxx_po_wf_training_pkg;
/

--initiate the workflow now
DECLARE
n_new_po_id INTEGER;
BEGIN
SELECT oe_order_headers_s.NEXTVAL INTO n_new_po_id FROM dual;
INSERT INTO xxxx_po_headers
(po_id
,vendor_id
,suggested_vendor_id
,po_description
,po_status
,comments_from_approver
,send_email_to)
VALUES
(n_new_po_id --po_id
,10000 --vendor_id
,NULL --suggested_vendor_id
,'This is PO Training Description' --po_description
,'INITIAL' --po_status
,NULL --comments_from_approver
,'ANILPASSI@GMAIL.com' --send_email_to
);
xxxx_po_wf_training_pkg.start_training_wf(p_po_id => n_new_po_id);
COMMIT;
END;
/

2006-09-30T07:48:00.000-07:00

Scripts for Workflow Training Lesson 2

This page contains all the required Scripts and Workflow files prepared for Workflow Training Lesson 2. Click here if you wish to return to Oracle Workflow Training Lesson 2

Workflow .wft File For Training Lesson 2
Rightclick to download the above file and save this to database.

--create the table
Create table xxxx_po_headers
(
po_id INTEGER
,vendor_id INTEGER
,suggested_vendor_id INTEGER
,po_description VARCHAR2(250)
,po_status VARCHAR2(30)
,comments_from_approver VARCHAR2(2000)
,send_email_to VARCHAR2(300)
) ;

-----------------
--Create the package
-----------------
CREATE OR REPLACE PACKAGE xxxx_po_wf_training_pkg IS
PROCEDURE is_po_valid
(
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2
);

PROCEDURE set_wf_approver_role
(
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2
);

PROCEDURE set_wf_status_to_validated
(
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2
);

PROCEDURE start_training_wf(p_po_id IN INTEGER);

PROCEDURE xx_validate_response (
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2
);

END xxxx_po_wf_training_pkg;
/

--------------------
--Create the package body
--------------------
CREATE OR REPLACE PACKAGE BODY xxxx_po_wf_training_pkg IS

PROCEDURE debug(p_text IN VARCHAR2) IS
BEGIN
-- apassi1(p_text);
NULL;
END debug;

FUNCTION is_po_validated(p_po_id IN INTEGER) RETURN BOOLEAN IS
BEGIN
IF p_po_id > 0
THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END is_po_validated;

PROCEDURE is_po_valid(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
BEGIN
IF (funcmode != 'RUN')
THEN
RETURN;
END IF;
--Just a pseudo validation
--call a function here, and either assign COMPLETE:Y or COMPLETE:N

IF is_po_validated(p_po_id => wf_engine.getitemattrnumber(itemtype => itemtype
,itemkey => itemkey
,aname => 'PO_ID'))
THEN
RESULT := 'COMPLETE:Y';
ELSE
RESULT := 'COMPLETE:N';
END IF;
END is_po_valid;

PROCEDURE set_wf_approver_role(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
v_role_email xxxx_po_headers.send_email_to%TYPE;
n_ctr INTEGER := 0;
BEGIN
v_role_email := upper(wf_engine.getitemattrtext(itemtype => itemtype
,itemkey => itemkey
,aname => 'SEND_TO_EMAIL'));
SELECT COUNT(*)
INTO n_ctr
FROM wf_local_roles
WHERE NAME = v_role_email;

IF n_ctr = 0
THEN
wf_directory.createadhocrole(role_name => v_role_email
,role_display_name => v_role_email
,role_description => v_role_email
,notification_preference => 'MAILHTML'
,email_address => v_role_email
,status => 'ACTIVE'
,expiration_date => NULL);
END IF;
wf_engine.setitemattrtext(itemtype => itemtype
,itemkey => itemkey
,aname => 'SEND_TO_ROLE'
,avalue => v_role_email);
RESULT := 'COMPLETE:Y';

RESULT := 'COMPLETE:Y';
END set_wf_approver_role;

PROCEDURE set_wf_status_to_validated(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
BEGIN
UPDATE xxxx_po_headers
SET po_status = 'VALIDATED'
WHERE po_id = wf_engine.getitemattrnumber(itemtype
,itemkey
,'PO_ID');
RESULT := 'COMPLETE:Y';
END set_wf_status_to_validated;

PROCEDURE start_training_wf(p_po_id IN INTEGER) IS
l_itemtype VARCHAR2(30) := 'XXXXPTR';
l_itemkey VARCHAR2(300) := 'TRAINING-' || p_po_id;
CURSOR c_get IS
SELECT * FROM xxxx_po_headers WHERE po_id = p_po_id;
p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;

wf_engine.createprocess(l_itemtype
,l_itemkey
,'MAIN_TRAINING_PROCESS');

wf_engine.setitemuserkey(itemtype => l_itemtype
,itemkey => l_itemkey
,userkey => 'USERKEY: ' || l_itemkey);
wf_engine.setitemowner(itemtype => l_itemtype
,itemkey => l_itemkey
,owner => 'SYSADMIN');

wf_engine.setitemattrnumber(itemtype => l_itemtype
,itemkey => l_itemkey
,aname => 'PO_ID'
,avalue => p_po_id);

wf_engine.setitemattrtext(itemtype => l_itemtype
,itemkey => l_itemkey
,aname => 'SEND_TO_EMAIL'
,avalue => p_get.send_email_to);
wf_engine.setitemattrtext(itemtype => l_itemtype
,itemkey => l_itemkey
,aname => 'PO_DESCRIPTION'
,avalue => p_get.po_description);
wf_engine.startprocess(l_itemtype
,l_itemkey);

END start_training_wf;

PROCEDURE xx_validate_response(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
l_nid NUMBER;
l_activity_result_code VARCHAR2(200);
v_response_reason VARCHAR2(50);
BEGIN
debug('funcmode is ' || funcmode);
IF (funcmode IN ('RESPOND'))
THEN
l_nid := wf_engine.context_nid;

l_activity_result_code := wf_notification.getattrtext(l_nid
,'XX_RESPONSE_ACTION');

debug('l_activity_result_code is ' || l_activity_result_code);
v_response_reason := wf_notification.getattrtext(l_nid
,'XX_RESPONSE_REASON');
debug('v_response_reason is ' || v_response_reason);
IF l_activity_result_code = 'XX_REJECTED' AND
v_response_reason IS NULL
THEN
RESULT := 'ERROR: You must enter rejection reason if rejecting.';
RETURN;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
RESULT := SQLERRM;
END xx_validate_response;
END xxxx_po_wf_training_pkg;
/

------
--Now Run the Workflow
--Ensure that wft file has been loaded
------
DECLARE
n_new_po_id INTEGER;
BEGIN
SELECT oe_order_headers_s.NEXTVAL INTO n_new_po_id FROM dual;
INSERT INTO xxxx_po_headers
(po_id
,vendor_id
,suggested_vendor_id
,po_description
,po_status
,comments_from_approver
,send_email_to)
VALUES
(n_new_po_id --po_id
,10000 --vendor_id
,NULL --suggested_vendor_id
,'This is PO Training Description' --po_description
,'INITIAL' --po_status
,NULL --comments_from_approver
,'ANILPASSI@GMAIL.com' --send_email_to
);
xxxx_po_wf_training_pkg.start_training_wf(p_po_id => n_new_po_id);
COMMIT;
END;
/

2006-09-26T07:44:00.000-07:00

Oracle Apps FND User name Locks Record

Ever wondered how to find the person in Oracle Apps that locks a specific record?
Its simple, I find this SQL quite useful. Blaah, you will know the user that is sleeping over the record by locking it. This SQL joins the locking session to FND_USER, so as to display the APPS User Name of the person that locks the record.

In the example below, lets say we need to find the list of people that are locking tables in Purchasing. Aslo, lets assume we need to find the screen/conc process that locks the record.

SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_user_name
,fl.start_time
,vs.module
,vs.machine
,vs.osuser
,vs.sid
,vp.pid
,vp.spid AS os_process_to_kill
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins fl
,fnd_user fu
,v$locked_object vlocked
,v$process vp
,v$session vs
,dba_objects c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE 'PO%';

OWNER PO
OBJECT_NAME PO_HEADERS_ALL
OBJECT_TYPE TABLE
LOCKING_USER_NAME PASSIANIL
START_TIME 26-SEP-2006
MODULE POXPOEPO
MACHINE xxappsserver01
OSUSER Xxtstuser
SID 2785
PID 661
OS_PROCESS_TO_KILL 27975
SERIAL# 5518
STATUS INACTIVE
SADDR 000000077DD814F0
AUDSID 13482253
PROCESS 3563

2006-09-26T07:30:00.000-07:00

Oracle APPS Warn Bounce of Environment to Recent Users

Very often, we find ourselves in a need to bounce the development or test environment. This takes some developers/testers etc by surprise.
Hence I developed a simple script to list the list of recent users that use the Oracle Apps instance. We can then email the people returned by this list, to inform them of the system unavailability.

SELECT ppx.full_name
,fu.user_name
,nvl(ppx.email_address
,fu.email_address) AS email_address
FROM per_people_x ppx, fnd_user fu, fnd_logins fl
WHERE fl.start_time > SYSDATE - 2
AND fu.user_id = fl.user_id
AND ppx.person_id(+) = fu.employee_id
AND fu.user_name NOT IN ('INTERFACE', 'SYSADMIN', 'GUEST')
AND fu.user_name != 'PASSIA' --exclude anil
GROUP BY ppx.full_name
,fu.user_name
,nvl(ppx.email_address
,fu.email_address)
ORDER BY 2

2006-09-26T04:14:00.000-07:00

Oracle Activity on FND LOGINS FND USER
My client asked me to write a quick SQL to find out the load on the Production system, in terms of User activity. This was required for be broken down by External Logins(iSourcing, Students etc) and Internal Employees.

Use the SQL below
===============
SELECT COUNT(fl.login_id) count_of_logins
,trunc(fl.start_time) start_date
,COUNT(DISTINCT fl.user_id) count_users
,decode(fu.employee_id
,NULL
,'EXTERNAL'
,'INTERNAL') AS emp_type
FROM fnd_user fu, fnd_logins fl
WHERE fl.start_time > SYSDATE - 10
AND fl.user_id NOT IN (6, 0, -1)
AND fu.user_id = fl.user_id
GROUP BY trunc(start_time)
,decode(fu.employee_id
,NULL
,'EXTERNAL'
,'INTERNAL')
ORDER BY 2

In fact after running this, I discovered that OAM gives a better breakdown, i.e. it will list the breakdown at Module level.
That list will be available from Sitemap Tab, after having logged into OAM ( Oracle Applications Manager ).

2006-09-19T22:57:00.000-07:00

Oracle API for GL Interface Package Body

CREATE OR REPLACE PACKAGE BODY xx_import_xfer_gl_iface_api IS
/* $Header: XXXXXXXX.pkh, Version 1.0, 07-FEB-2003 UKEXPI $
***********************************************************************
* *
* History Log *
* *
***********************************************************************
* *
* App/Release : Oracle e-Business Suite RXXX *
* Oracle App : SHORTNAME - FULL_NAME *
* Module : SHORTNAME - DESCRIPTION *
* Author : Anil Passi(Go 4 Gold), *
* Version : 1.0 *
* Revised : 11-SEP-2005 *
* Description : This package delivers GL interface API *
* *
* Change History Log *
* ================== *
* *
* Version Date Author Change *
* ======= =========== ============ ================================*
*
* 1.0 10-OCT-2002 A. Passi Initial Version
* 1.2 12-OCT-2002 A. Passi Added ability to "transfer_to_gl" such that
both the credit and debit can either happen
in single API call, or multiple calls(when an
amount is distributed).
* 1.3 24-Jan-2006 A. Passi Add the trx_number to the batch details.
This is so because the recurring journal will
have incremental trx_number for each period,
with the batch number remaining the same.
* 1.4 08-Sep-2005 A. Passi Move the journal to next
period if current period is closed
**********************************************************************/

g_sob_id INTEGER := fnd_profile.VALUE('GL_SET_OF_BKS_ID');
g_source_name gl_je_headers.je_source%TYPE;
g_error_flag VARCHAR2(1) := NULL;

g_xx_transaction_source CONSTANT VARCHAR2(80) := 'XX_COST_XFER';

--move these three into the config screen.
--$?$
g_gl_user_je_source_name CONSTANT VARCHAR2(30) := nvl(fnd_profile.VALUE('XX_OVERRIDE_GL_JE_SOURCE_NAME')
,'Manual');
--$?$
g_gl_user_je_category_name CONSTANT VARCHAR2(30) := nvl(fnd_profile.VALUE('XX_OVERRIDE_JE_CATEGORY_NAME')
,'Miscellaneous Transaction');
--$?$
g_conversion_type VARCHAR2(30) := nvl(fnd_profile.VALUE('XX_OVERRIDE_GL_RATE_CONVERSION_TYPE')
,'Corporate');

g_gl_batch_prefix VARCHAR2(30) := nvl(fnd_profile.VALUE('XX_OVERRIDE_GL_BATCH_PREFIX')
,'XX_GL_FEED_NAME:');

g_gl_appl_name CONSTANT VARCHAR2(30) := 'SQLGL';
g_conversion_type_code VARCHAR2(30);

g_debug_procedure_context VARCHAR2(30);
g_debug_header_context CONSTANT VARCHAR2(80) := 'xx_gl_feed.plsql.xx_import_xfer_gl_iface_api.';

--Programming variables
--g_group_id and g_interface_run_id will be set each time separately for a batch
g_group_id NUMBER;
g_interface_run_id NUMBER;

PROCEDURE new_gl_interface_batch IS
BEGIN
g_group_id := NULL;
g_interface_run_id := NULL;
END new_gl_interface_batch;

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 AND
fnd_profile.VALUE('AFLOG_ENABLED') = 'Y'
THEN
fnd_file.put_line(which => fnd_file.log
,buff => p_msg);
END IF;

END debug_stmt;

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);
END debug_begin_procedure;

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);
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;

PROCEDURE log_error(p_batch_header_id IN INTEGER
,p_trx_header_id IN INTEGER
,p_error_message IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF fnd_global.conc_request_id > 0
THEN
fnd_file.put_line(fnd_file.output
,'ERROR::' || p_error_message);
g_error_flag := 'Y';
END IF;
--log error into your error logging table
/* INSERT INTO xx_gl_api_error_logs
(error_log_id
,batch_header_id
,trx_header_id
,error_message
,creation_date
,fnd_request_id)
VALUES
(xx_error_log_s.NEXTVAL -- x_error_log_id
,p_batch_header_id
,p_trx_header_id
,substr(p_error_message, 1, 2000)
,SYSDATE --creation_date
,fnd_global.conc_request_id --fnd_request_id
);
*/
debug_stmt('ERROR: ' || p_error_message);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
debug_stmt('ERROR: in Log_error');
ROLLBACK;
END log_error;

FUNCTION get_sob_id RETURN INTEGER IS
BEGIN
set_debug_context('get_sob_id');
debug_stmt('get_sob_id() Returning g_sob_id=>' || g_sob_id);
RETURN g_sob_id;
debug_end_procedure;
END get_sob_id;

PROCEDURE set_sob_id(p_sob_id IN INTEGER) IS
BEGIN
set_debug_context('set_sob_id');

g_sob_id := p_sob_id;
debug_stmt('set_sob_id() Setting g_sob_id=>' || g_sob_id);
debug_end_procedure;
END set_sob_id;

PROCEDURE set_source_name(p_source_name IN VARCHAR2) IS
BEGIN
set_debug_context('set_source_name');

g_source_name := p_source_name;
debug_stmt('set_source_name() Setting g_source_name=>' ||
g_source_name);
debug_end_procedure;
END set_source_name;

FUNCTION get_source_name RETURN VARCHAR2 IS
BEGIN
RETURN g_source_name;
END get_source_name;

FUNCTION get_xx_transaction_source RETURN VARCHAR2 IS
BEGIN
RETURN g_xx_transaction_source;
END get_xx_transaction_source;

PROCEDURE populate_interface_control(p_user_je_source_name IN VARCHAR2
,p_group_id IN OUT NUMBER
,p_set_of_books_id NUMBER
,p_interface_run_id IN OUT NUMBER) IS
re_use_gl_interface_control EXCEPTION;
BEGIN
set_debug_context('populate_interface_control');

IF g_group_id IS NOT NULL AND g_interface_run_id IS NOT NULL
THEN
p_group_id := g_group_id;
p_interface_run_id := g_interface_run_id;
RAISE re_use_gl_interface_control;
END IF;
gl_journal_import_pkg.populate_interface_control(user_je_source_name => p_user_je_source_name
,group_id => p_group_id
,set_of_books_id => p_set_of_books_id
,interface_run_id => p_interface_run_id);

g_group_id := p_group_id;
g_interface_run_id := p_interface_run_id;

debug_stmt('Returning New Group p_group_id=>' || p_group_id ||
' p_interface_run_id=>' || p_interface_run_id);

debug_end_procedure;
EXCEPTION
WHEN re_use_gl_interface_control THEN
debug_stmt('Returning Global Group g_group_id=>' || g_group_id ||
' g_interface_run_id=>' || g_interface_run_id);
debug_end_procedure;
END populate_interface_control;

PROCEDURE interface_record IS
n_group_id INTEGER;
n_sob_id INTEGER;
n_interface_run_id INTEGER;
BEGIN
set_debug_context('interface_record');

n_sob_id := get_sob_id;
populate_interface_control(p_user_je_source_name => g_source_name
,p_group_id => n_group_id
,p_set_of_books_id => n_sob_id
,p_interface_run_id => n_interface_run_id);
debug_end_procedure;
END interface_record;

PROCEDURE insert_statement(p_gl_int_rec IN OUT NOCOPY g_gl_int_type_rec) IS
BEGIN
set_debug_context('insert_statement');

INSERT INTO gl_interface
(reference_date
,attribute20
,CONTEXT
,context2
,invoice_date
,tax_code
,invoice_identifier
,invoice_amount
,context3
,ussgl_transaction_code
,descr_flex_error_message
,jgzz_recon_ref
,segment23
,segment24
,segment25
,segment26
,segment27
,segment28
,segment29
,segment30
,entered_dr
,entered_cr
,accounted_dr
,accounted_cr
,transaction_date
,reference1
,reference2
,reference3
,reference4
,reference5
,reference6
,reference7
,reference8
,reference9
,reference10
,reference11
,reference12
,reference13
,reference14
,reference15
,reference16
,reference17
,reference18
,reference19
,reference20
,reference21
,reference22
,reference23
,reference24
,reference25
,reference26
,reference27
,reference28
,reference29
,reference30
,je_batch_id
,period_name
,je_header_id
,je_line_num
,chart_of_accounts_id
,functional_currency_code
,code_combination_id
,date_created_in_gl
,warning_code
,status_description
,stat_amount
,group_id
,request_id
,subledger_doc_sequence_id
,subledger_doc_sequence_value
,attribute1
,attribute2
,gl_sl_link_id
,gl_sl_link_table
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,status
,set_of_books_id
,accounting_date
,currency_code
,date_created
,created_by
,actual_flag
,user_je_category_name
,user_je_source_name
,currency_conversion_date
,encumbrance_type_id
,budget_version_id
,user_currency_conversion_type
,currency_conversion_rate
,average_journal_flag
,originating_bal_seg_value
,segment1
,segment2
,segment3
,segment4
,segment5
,segment6
,segment7
,segment8
,segment9
,segment10
,segment11
,segment12
,segment13
,segment14
,segment15
,segment16
,segment17
,segment18
,segment19
,segment20
,segment21
,segment22)
VALUES
(p_gl_int_rec.reference_date -- reference_date
,p_gl_int_rec.attribute20 -- attribute20
,p_gl_int_rec.CONTEXT -- context
,p_gl_int_rec.context2 -- context2
,p_gl_int_rec.invoice_date -- invoice_date
,p_gl_int_rec.tax_code -- tax_code
,p_gl_int_rec.invoice_identifier -- invoice_identifier
,p_gl_int_rec.invoice_amount -- invoice_amount
,p_gl_int_rec.context3 -- context3
,p_gl_int_rec.ussgl_transaction_code -- ussgl_transaction_code
,p_gl_int_rec.descr_flex_error_message -- descr_flex_error_message
,p_gl_int_rec.jgzz_recon_ref -- jgzz_recon_ref
,p_gl_int_rec.segment23 -- segment23
,p_gl_int_rec.segment24 -- segment24
,p_gl_int_rec.segment25 -- segment25
,p_gl_int_rec.segment26 -- segment26
,p_gl_int_rec.segment27 -- segment27
,p_gl_int_rec.segment28 -- segment28
,p_gl_int_rec.segment29 -- segment29
,p_gl_int_rec.segment30 -- segment30
,p_gl_int_rec.entered_dr -- entered_dr
,p_gl_int_rec.entered_cr -- entered_cr
,p_gl_int_rec.accounted_dr -- accounted_dr
,p_gl_int_rec.accounted_cr -- accounted_cr
,p_gl_int_rec.transaction_date -- transaction_date
,p_gl_int_rec.reference1 -- reference1
,p_gl_int_rec.reference2 -- reference2
,p_gl_int_rec.reference3 -- reference3
,p_gl_int_rec.reference4 -- reference4
,p_gl_int_rec.reference5 -- reference5
,p_gl_int_rec.reference6 -- reference6
,p_gl_int_rec.reference7 -- reference7
,p_gl_int_rec.reference8 -- reference8
,p_gl_int_rec.reference9 -- reference9
,p_gl_int_rec.reference10 -- reference10
,p_gl_int_rec.reference11 -- reference11
,p_gl_int_rec.reference12 -- reference12
,p_gl_int_rec.reference13 -- reference13
,p_gl_int_rec.reference14 -- reference14
,p_gl_int_rec.reference15 -- reference15
,p_gl_int_rec.reference16 -- reference16
,p_gl_int_rec.reference17 -- reference17
,p_gl_int_rec.reference18 -- reference18
,p_gl_int_rec.reference19 -- reference19
,p_gl_int_rec.reference20 -- reference20
,p_gl_int_rec.reference21 -- reference21
,p_gl_int_rec.reference22 -- reference22
,p_gl_int_rec.reference23 -- reference23
,p_gl_int_rec.reference24 -- reference24
,p_gl_int_rec.reference25 -- reference25
,p_gl_int_rec.reference26 -- reference26
,p_gl_int_rec.reference27 -- reference27
,p_gl_int_rec.reference28 -- reference28
,p_gl_int_rec.reference29 -- reference29
,p_gl_int_rec.reference30 -- reference30
,p_gl_int_rec.je_batch_id -- je_batch_id
,p_gl_int_rec.period_name -- period_name
,p_gl_int_rec.je_header_id -- je_header_id
,p_gl_int_rec.je_line_num -- je_line_num
,p_gl_int_rec.chart_of_accounts_id -- chart_of_accounts_id
,p_gl_int_rec.functional_currency_code -- functional_currency_code
,p_gl_int_rec.code_combination_id -- code_combination_id
,p_gl_int_rec.date_created_in_gl -- date_created_in_gl
,p_gl_int_rec.warning_code -- warning_code
,p_gl_int_rec.status_description -- status_description
,p_gl_int_rec.stat_amount -- stat_amount
,p_gl_int_rec.group_id -- group_id
,p_gl_int_rec.request_id -- request_id
,p_gl_int_rec.subledger_doc_sequence_id
-- subledger_doc_sequence_id
,p_gl_int_rec.subledger_doc_sequence_value
-- subledger_doc_sequence_value
,p_gl_int_rec.attribute1 -- attribute1
,p_gl_int_rec.attribute2 -- attribute2
,p_gl_int_rec.gl_sl_link_id -- gl_sl_link_id
,p_gl_int_rec.gl_sl_link_table -- gl_sl_link_table
,p_gl_int_rec.attribute3 -- attribute3
,p_gl_int_rec.attribute4 -- attribute4
,p_gl_int_rec.attribute5 -- attribute5
,p_gl_int_rec.attribute6 -- attribute6
,p_gl_int_rec.attribute7 -- attribute7
,p_gl_int_rec.attribute8 -- attribute8
,p_gl_int_rec.attribute9 -- attribute9
,p_gl_int_rec.attribute10 -- attribute10
,p_gl_int_rec.attribute11 -- attribute11
,p_gl_int_rec.attribute12 -- attribute12
,p_gl_int_rec.attribute13 -- attribute13
,p_gl_int_rec.attribute14 -- attribute14
,p_gl_int_rec.attribute15 -- attribute15
,p_gl_int_rec.attribute16 -- attribute16
,p_gl_int_rec.attribute17 -- attribute17
,p_gl_int_rec.attribute18 -- attribute18
,p_gl_int_rec.attribute19 -- attribute19
,p_gl_int_rec.status -- status
,p_gl_int_rec.set_of_books_id -- set_of_books_id
,p_gl_int_rec.accounting_date -- accounting_date
,p_gl_int_rec.currency_code -- currency_code
,p_gl_int_rec.date_created -- date_created
,p_gl_int_rec.created_by -- created_by
,p_gl_int_rec.actual_flag -- actual_flag
,p_gl_int_rec.user_je_category_name -- user_je_category_name
,p_gl_int_rec.user_je_source_name -- user_je_source_name
,p_gl_int_rec.currency_conversion_date -- currency_conversion_date
,p_gl_int_rec.encumbrance_type_id -- encumbrance_type_id
,p_gl_int_rec.budget_version_id -- budget_version_id
,p_gl_int_rec.user_currency_conversion_type
-- user_currency_conversion_type
,p_gl_int_rec.currency_conversion_rate -- currency_conversion_rate
,p_gl_int_rec.average_journal_flag -- average_journal_flag
,p_gl_int_rec.originating_bal_seg_value
-- originating_bal_seg_value
,p_gl_int_rec.segment1 -- segment1
,p_gl_int_rec.segment2 -- segment2
,p_gl_int_rec.segment3 -- segment3
,p_gl_int_rec.segment4 -- segment4
,p_gl_int_rec.segment5 -- segment5
,p_gl_int_rec.segment6 -- segment6
,p_gl_int_rec.segment7 -- segment7
,p_gl_int_rec.segment8 -- segment8
,p_gl_int_rec.segment9 -- segment9
,p_gl_int_rec.segment10 -- segment10
,p_gl_int_rec.segment11 -- segment11
,p_gl_int_rec.segment12 -- segment12
,p_gl_int_rec.segment13 -- segment13
,p_gl_int_rec.segment14 -- segment14
,p_gl_int_rec.segment15 -- segment15
,p_gl_int_rec.segment16 -- segment16
,p_gl_int_rec.segment17 -- segment17
,p_gl_int_rec.segment18 -- segment18
,p_gl_int_rec.segment19 -- segment19
,p_gl_int_rec.segment20 -- segment20
,p_gl_int_rec.segment21 -- segment21
,p_gl_int_rec.segment22 -- segment22
);
debug_stmt(SQL%ROWCOUNT || ' records inserted into GL_INTERFACE');
debug_end_procedure;
END insert_statement;

FUNCTION check_ccid(p_sob_id IN INTEGER
,p_ccid IN INTEGER) RETURN BOOLEAN IS
CURSOR c_check IS
SELECT 'x'
FROM gl_code_combinations gcc, gl_sets_of_books gsob
WHERE gsob.set_of_books_id = p_sob_id
AND gcc.code_combination_id = p_ccid
AND gcc.chart_of_accounts_id = gsob.chart_of_accounts_id
AND gcc.enabled_flag = 'Y';

p_check c_check%ROWTYPE;
BEGIN
set_debug_context('check_ccid');
debug_stmt('p_sob_id=>' || p_sob_id || ' p_ccid=>' || p_ccid);
OPEN c_check;
FETCH c_check
INTO p_check;

IF c_check%NOTFOUND
THEN
CLOSE c_check;
debug_stmt('check_ccid() RETURNED FALSE;');
RETURN FALSE;
END IF;

CLOSE c_check;
debug_stmt('check_ccid() RETURNED TRUE;');
RETURN TRUE;
debug_end_procedure;
END check_ccid;

FUNCTION get_gl_application_id(p_appl_name IN VARCHAR2) RETURN INTEGER IS
CURSOR c_check IS
SELECT application_id
FROM fnd_application_vl
WHERE application_short_name = p_appl_name;

p_check c_check%ROWTYPE;
BEGIN
OPEN c_check;
FETCH c_check
INTO p_check;
CLOSE c_check;
RETURN p_check.application_id;
END get_gl_application_id;

FUNCTION check_conversion_type RETURN BOOLEAN IS
CURSOR c_check IS
SELECT 'x'
FROM gl_daily_conversion_types
WHERE user_conversion_type = g_conversion_type;

p_check c_check%ROWTYPE;
BEGIN
set_debug_context('check_conversion_type');

debug_stmt('g_conversion_type=>' || g_conversion_type);
OPEN c_check;
FETCH c_check
INTO p_check;

IF c_check%NOTFOUND
THEN
CLOSE c_check;
debug_stmt('check_conversion_type() RETURNED FALSE;');
RETURN FALSE;
END IF;

CLOSE c_check;
debug_stmt('check_conversion_type() RETURNED TRUE;');
RETURN TRUE;
END check_conversion_type;

FUNCTION check_gl_source(p_gl_user_je_source_name IN VARCHAR2)
RETURN BOOLEAN IS
CURSOR c_check IS
SELECT 'x'
FROM gl_je_sources
WHERE user_je_source_name =
nvl(p_gl_user_je_source_name
,g_gl_user_je_source_name);

p_check c_check%ROWTYPE;
BEGIN
set_debug_context('check_gl_source');
debug_stmt('g_gms_cost_xfer_source=>' || g_gl_user_je_source_name);
OPEN c_check;
FETCH c_check
INTO p_check;

IF c_check%NOTFOUND
THEN
CLOSE c_check;
debug_stmt('check_gl_source() RETURNED FALSE;');
RETURN FALSE;
END IF;

CLOSE c_check;
debug_stmt('check_gl_source() RETURNED TRUE;');
RETURN TRUE;
END check_gl_source;

FUNCTION get_next_available_open_date(p_accounting_date IN DATE
,p_gl_appl_id IN NUMBER
,p_sob_id IN NUMBER)
RETURN DATE IS
CURSOR c_get IS
SELECT gps.start_date
FROM gl_period_statuses gps
WHERE gps.application_id = p_gl_appl_id
AND gps.set_of_books_id = p_sob_id
AND gps.closing_status IN ('O', 'F')
AND trunc(gps.start_date) > p_accounting_date
ORDER BY trunc(gps.start_date);
p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;
RETURN p_get.start_date;
END get_next_available_open_date;

FUNCTION validate_accounting_date(p_accounting_date IN OUT DATE
,p_gl_appl_id IN NUMBER
,p_sob_id IN NUMBER
,p_batch_header_id IN NUMBER
,p_trx_header_id IN NUMBER)
RETURN BOOLEAN IS
v_count NUMBER := 0;
v_accounting_date DATE;
BEGIN
set_debug_context('validate_accounting_date');
v_accounting_date := p_accounting_date;
debug_stmt('p_accounting_date=>' || p_accounting_date ||
' p_gl_appl_id=>' || p_gl_appl_id || ' p_sob_id=>' ||
p_sob_id);
SELECT COUNT(*)
INTO v_count
FROM gl_period_statuses gps
WHERE gps.application_id = p_gl_appl_id
AND gps.set_of_books_id = p_sob_id
AND gps.closing_status IN ('O', 'F')
AND trunc(p_accounting_date) BETWEEN
nvl(trunc(gps.start_date)
,trunc(p_accounting_date)) AND
nvl(trunc(gps.end_date)
,trunc(p_accounting_date));

IF v_count > 0
THEN
debug_stmt('validate_accounting_date() RETURNED TRUE');
RETURN TRUE;
ELSE
debug_stmt('validate_accounting_date() Cant find open Period for ' ||
p_accounting_date);
--try to find next available date now
p_accounting_date := get_next_available_open_date(p_accounting_date => v_accounting_date
,p_gl_appl_id => p_gl_appl_id
,p_sob_id => p_sob_id);
IF p_accounting_date IS NULL
THEN
log_error(p_batch_header_id => p_batch_header_id
,p_trx_header_id => p_trx_header_id
,p_error_message => 'Accounting Date ' ||
to_char(v_accounting_date
,'DD-MON-YYYY') ||
' does not belong to a Open Period');

/* --!!!!!Abort the process here
raise_application_error(-20001
,v_accounting_date ||
' Can not find open/future period for this date. Batch Number ' ||
p_batch_header_id
||
' . There must be at least one future/open period for this process to run'
);
*/
RETURN FALSE;
ELSE
debug_stmt('validate_accounting_date() Using next available open date ' ||
p_accounting_date);
RETURN TRUE;
END IF;
END IF;

END validate_accounting_date;

-- Function to validate currency code
FUNCTION validate_currency(p_currency_code IN VARCHAR2
,p_accounting_date IN DATE) RETURN BOOLEAN IS
v_count NUMBER := 0;
BEGIN
set_debug_context('validate_currency');
SELECT COUNT(*)
INTO v_count
FROM fnd_currencies fc
WHERE fc.currency_code = p_currency_code
AND enabled_flag = 'Y'
AND p_accounting_date BETWEEN
nvl(start_date_active
,p_accounting_date) AND
nvl(end_date_active
,p_accounting_date);

IF v_count > 0
THEN
debug_stmt('validate_currency() RETURNED TRUE');
RETURN TRUE;
ELSE
debug_stmt('validate_currency() RETURNED FLASE');
RETURN FALSE;
END IF;
END validate_currency;

PROCEDURE set_conversion_type_code IS
CURSOR c_get IS
SELECT conversion_type
FROM gl_daily_conversion_types
WHERE user_conversion_type = g_conversion_type;
BEGIN
set_debug_context('set_conversion_type_code');
debug_stmt('g_conversion_type=>' || g_conversion_type);
OPEN c_get;
FETCH c_get
INTO g_conversion_type_code;
CLOSE c_get;
debug_stmt('g_conversion_type_code=>' || g_conversion_type_code);
debug_end_procedure;
END set_conversion_type_code;

FUNCTION get_batch_employee_number(p_batch_header_id IN INTEGER)
RETURN VARCHAR2 IS
CURSOR c_get IS
SELECT ppx.employee_number
FROM per_people_x ppx
--,"your own table here for the feed " bhdr
--WHERE ppx.person_id = bhdr.employee_id
;
p_get c_get%ROWTYPE;
BEGIN
--change this function as per your need
--i am returning a constant value
RETURN '101010';
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;
RETURN p_get.employee_number;
END get_batch_employee_number;

FUNCTION get_gl_je_reference1(p_batch_header_id IN INTEGER
,p_trx_number IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
RETURN g_gl_batch_prefix || p_batch_header_id || '-' || p_trx_number || ':' || get_batch_employee_number(p_batch_header_id) || ':';
END get_gl_je_reference1;

FUNCTION get_sob_currency(p_sob_id IN INTEGER) RETURN VARCHAR2 IS
CURSOR c_check IS
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = p_sob_id;

p_check c_check%ROWTYPE;
BEGIN
OPEN c_check;
FETCH c_check
INTO p_check;
CLOSE c_check;
RETURN p_check.currency_code;
END get_sob_currency;

/**************************************************
Main code for gms_cost_xfer GL Interface begins here
**************************************************/
PROCEDURE transfer_to_gl(p_sob_id IN INTEGER
,p_batch_header_id IN INTEGER
,p_trx_header_id IN INTEGER
,p_trx_line_id IN INTEGER
,p_date IN OUT DATE
,p_currency_code IN VARCHAR2
,p_amount IN NUMBER
,p_dr_account IN INTEGER
,p_cr_account IN INTEGER
,p_journal_name IN VARCHAR2
,p_header_description IN VARCHAR2
,p_line_description IN VARCHAR2
,p_success_flag OUT BOOLEAN
,p_first_record_in_batch IN VARCHAR2
,p_submit_gl_interface IN VARCHAR2
,p_gl_user_je_source_name IN VARCHAR2
,p_gl_user_je_category_name IN VARCHAR2
,p_trx_number IN VARCHAR2) IS
v_gl_appl_id NUMBER;
l_gl_int_type_rec g_gl_int_type_rec;
v_conc_id INTEGER;
conversion_rate_does_not_exist EXCEPTION;
invalid_conversion_type EXCEPTION;
invalid_dr_ccid EXCEPTION;
invalid_cr_ccid EXCEPTION;
invalid_currency_code EXCEPTION;
not_in_open_period EXCEPTION;
invalid_gl_source EXCEPTION;
v_func_curr VARCHAR2(30);
v_group_id NUMBER;
v_interface_run_id NUMBER;

v_ret_val BOOLEAN;
v_request_id INTEGER;
phase VARCHAR2(100);
status VARCHAR2(100);
dev_phase VARCHAR2(100);
dev_status VARCHAR2(100);
v_message VARCHAR2(100);
v_bool BOOLEAN;
v_old_status VARCHAR2(30);

BEGIN
set_debug_context('transfer_to_gl');

SAVEPOINT gms_cost_xfer_gl_int;

set_conversion_type_code;

/* Get the GL Application ID */
v_gl_appl_id := get_gl_application_id(p_appl_name => g_gl_appl_name);

IF p_first_record_in_batch = 'Y'
THEN
debug_stmt('p_first_record_in_batch is Y. Hence will reset global variables to initiate new batch');
new_gl_interface_batch;
ELSE
debug_stmt('Not being the first record, accumulating this line to existing batch.');
END IF;
/* May be GL team forgets to create source for gms_cost_xfer, lets see...*/
IF NOT check_gl_source(p_gl_user_je_source_name)
THEN
RAISE invalid_gl_source;
END IF;

/* During the design phase in Swiss Re the conversion type has already been changed once
Lets not take any chances and check is this is a valid conversion type */
IF NOT check_conversion_type
THEN
RAISE invalid_conversion_type;
END IF;

/* Lets validate the accounting date.
Although gms_cost_xfer almost always passes the current date.
But still this is a good to have validation. */
IF NOT validate_accounting_date(p_accounting_date => p_date
,p_gl_appl_id => v_gl_appl_id
,p_sob_id => p_sob_id
,p_batch_header_id => p_batch_header_id
,p_trx_header_id => p_trx_header_id)
THEN
RAISE not_in_open_period;
END IF;

/* Lets validate the currency code.
Although gms_cost_xfer will certainly pass correct curr code*/
IF NOT validate_currency(p_accounting_date => p_date
,p_currency_code => p_currency_code)
THEN
RAISE invalid_currency_code;
END IF;

v_func_curr := get_sob_currency(p_sob_id);

IF nvl(v_func_curr
,'XX') != nvl(p_currency_code
,'XX')
THEN
IF 'N' =
gl_currency_api.rate_exists(x_from_currency => p_currency_code
,x_to_currency => v_func_curr
,x_conversion_date => p_date
,x_conversion_type => g_conversion_type_code)
THEN
RAISE conversion_rate_does_not_exist;
ELSE
l_gl_int_type_rec.user_currency_conversion_type := g_conversion_type;
l_gl_int_type_rec.currency_conversion_date := p_date;
END IF;
END IF;

set_sob_id(p_sob_id => p_sob_id);
set_source_name(p_source_name => nvl(p_gl_user_je_source_name
,g_gl_user_je_source_name));
populate_interface_control(p_user_je_source_name => nvl(p_gl_user_je_source_name
,g_gl_user_je_source_name)
,p_group_id => v_group_id
,p_set_of_books_id => p_sob_id
,p_interface_run_id => v_interface_run_id);

--batch name; part of the je batch name
l_gl_int_type_rec.reference1 := get_gl_je_reference1(p_batch_header_id => p_batch_header_id
,p_trx_number => p_trx_number);

--je batch description
l_gl_int_type_rec.reference2 := p_batch_header_id || ': ' ||
p_header_description || '-' ||
p_trx_number;

--part of the je header name
l_gl_int_type_rec.reference4 := p_journal_name || '-' || p_trx_number;

--je header description will be the description of the gms_cost_xfer receipt
l_gl_int_type_rec.reference5 := p_batch_header_id || ': ' ||
nvl(p_header_description
,p_journal_name) || '-' ||
p_trx_number;
--if the description is left blank, then use the Journal Name instead

--je lines description will be the description of the gms_cost_xfer receipt
l_gl_int_type_rec.reference10 := p_line_description;

l_gl_int_type_rec.reference_date := p_date;
l_gl_int_type_rec.reference21 := p_header_description;
l_gl_int_type_rec.group_id := v_group_id;
l_gl_int_type_rec.set_of_books_id := p_sob_id;
l_gl_int_type_rec.user_je_source_name := nvl(p_gl_user_je_source_name
,g_gl_user_je_source_name);
l_gl_int_type_rec.user_je_category_name := nvl(p_gl_user_je_category_name
,g_gl_user_je_category_name);
l_gl_int_type_rec.accounting_date := p_date;
l_gl_int_type_rec.transaction_date := p_date;
l_gl_int_type_rec.currency_code := p_currency_code;
l_gl_int_type_rec.date_created := p_date;
l_gl_int_type_rec.created_by := fnd_global.user_id;
l_gl_int_type_rec.actual_flag := 'A';
l_gl_int_type_rec.status := 'S';
l_gl_int_type_rec.attribute1 := p_trx_header_id;
l_gl_int_type_rec.attribute2 := p_trx_line_id;
l_gl_int_type_rec.attribute3 := p_trx_number;
l_gl_int_type_rec.attribute4 := get_xx_transaction_source;
l_gl_int_type_rec.attribute5 := p_batch_header_id;
IF p_dr_account IS NOT NULL AND
(NOT check_ccid(p_sob_id => p_sob_id
,p_ccid => p_dr_account))
THEN
RAISE invalid_dr_ccid;
END IF;

IF p_cr_account IS NOT NULL AND
(NOT check_ccid(p_sob_id => p_sob_id
,p_ccid => p_cr_account))
THEN
RAISE invalid_cr_ccid;
END IF;

/* First the gms_cost_xfer Debit Line */
l_gl_int_type_rec.entered_dr := p_amount;
l_gl_int_type_rec.entered_cr := NULL;
l_gl_int_type_rec.code_combination_id := p_dr_account;
debug_stmt('p_dr_account ' || l_gl_int_type_rec.code_combination_id);

IF p_dr_account IS NOT NULL
THEN
insert_statement(p_gl_int_rec => l_gl_int_type_rec);
END IF;
l_gl_int_type_rec.entered_dr := NULL;
l_gl_int_type_rec.entered_cr := p_amount;
l_gl_int_type_rec.code_combination_id := p_cr_account;
IF p_cr_account IS NOT NULL
THEN
insert_statement(p_gl_int_rec => l_gl_int_type_rec);
debug_stmt('p_cr_account ' || l_gl_int_type_rec.code_combination_id);
END IF;

IF p_submit_gl_interface = 'Y'
THEN
debug_stmt('p_submit_gl_interface=>' || p_submit_gl_interface);
/* After inset, submit the request*/
COMMIT;
v_conc_id := fnd_request.submit_request(application => 'SQLGL'
,program => 'GLLEZL'
,description => NULL
,start_time => SYSDATE
,sub_request => FALSE
,argument1 => v_interface_run_id
,argument2 => p_sob_id
,argument3 => 'N'
,argument4 => NULL
,argument5 => NULL
,argument6 => 'N'
,argument7 => 'W');

SELECT status_code
INTO v_old_status
FROM fnd_concurrent_requests
WHERE request_id = fnd_global.conc_request_id;

UPDATE fnd_concurrent_requests
SET status_code = 'W'
WHERE request_id = fnd_global.conc_request_id;

COMMIT;
v_bool := fnd_concurrent.wait_for_request(v_conc_id
,5
,1000
,phase
,status
,dev_phase
,dev_status
,v_message);
UPDATE fnd_concurrent_requests
SET status_code = v_old_status
WHERE request_id = fnd_global.conc_request_id;
COMMIT;

debug_stmt('Request id is ' || v_conc_id);

IF (v_conc_id = 0)
THEN
/* If request not submitted, return false */
ROLLBACK TO gms_cost_xfer_gl_int;
p_success_flag := FALSE;
debug_stmt(' Returning false as request could not be submitted');
RETURN;
END IF;
END IF;
p_success_flag := TRUE;
EXCEPTION
WHEN conversion_rate_does_not_exist THEN
ROLLBACK TO gms_cost_xfer_gl_int;
p_success_flag := FALSE;
fnd_message.set_name('XTR'
,'XTR_2207');
fnd_message.set_token('CURR1'
,v_func_curr);
fnd_message.set_token('CURR2'
,p_currency_code);
fnd_message.set_token('XCHG_DATE'
,to_char(p_date));
fnd_message.set_token('C_TYPE'
,g_conversion_type);
WHEN invalid_currency_code THEN
ROLLBACK TO gms_cost_xfer_gl_int;
p_success_flag := FALSE;
fnd_message.set_name('SQLGL'
,'R_PPOS0026');
WHEN invalid_gl_source THEN
ROLLBACK TO gms_cost_xfer_gl_int;
p_success_flag := FALSE;
fnd_message.set_name('SQLGL'
,'SHRD0152');
WHEN invalid_conversion_type THEN
ROLLBACK TO gms_cost_xfer_gl_int;
p_success_flag := FALSE;
fnd_message.set_name('SQLGL'
,'GL_JE_INVALID_CONVERSION_TYPE');
WHEN not_in_open_period THEN
ROLLBACK TO gms_cost_xfer_gl_int;
p_success_flag := FALSE;
fnd_message.set_name('SQLGL'
,'GL_JE_NOT_OPEN_OR_FUTURE_ENT');
WHEN invalid_cr_ccid THEN
ROLLBACK TO gms_cost_xfer_gl_int;
p_success_flag := FALSE;
fnd_message.set_name('AR'
,'AR_AAPI_INVALID_CCID');
fnd_message.set_token('CCID'
,p_cr_account);
WHEN invalid_dr_ccid THEN
ROLLBACK TO gms_cost_xfer_gl_int;
p_success_flag := FALSE;
fnd_message.set_name('AR'
,'AR_AAPI_INVALID_CCID');
fnd_message.set_token('CCID'
,p_dr_account);
WHEN OTHERS THEN
ROLLBACK TO gms_cost_xfer_gl_int;
p_success_flag := FALSE;
fnd_message.set_name('FND'
,'FS-UNKNOWN');
fnd_message.set_token('ERROR'
,SQLERRM);
END transfer_to_gl;

END xx_import_xfer_gl_iface_api;

2006-09-19T22:56:00.000-07:00

Oracle API for GL Interface Package Header

CREATE OR REPLACE PACKAGE xx_import_xfer_gl_iface_api
/* $Header: XXXXXXXX.pkh, Version 1.0, 07-FEB-2002 UKEXPI $
***********************************************************************
* *
* History Log *
* *
***********************************************************************
* *
* App/Release : Oracle e-Business Suite RXXX *
* Oracle App : SHORTNAME - FULL_NAME *
* Module : SHORTNAME - DESCRIPTION *
* Author : Anil Passi(Go 4 Gold) *
* Version : 1.0 *
* Created : 11-OCT-2002 *
* Description : This package gives API for GL interface calls *
* *
* Change History Log *
* ================== *
* *
* Version Date Author Change *
* ======= =========== ============ ================================*
* *
* 1.0 11-OCT-2002 A. Passi Initial Version *
* *
* Parameters : *
* *
**********************************************************************/
AUTHID CURRENT_USER IS
PROCEDURE set_sob_id(p_sob_id IN INTEGER);

PROCEDURE set_source_name(p_source_name IN VARCHAR2);

PROCEDURE populate_interface_control(p_user_je_source_name IN VARCHAR2
,p_group_id IN OUT NUMBER
,p_set_of_books_id NUMBER
,p_interface_run_id IN OUT NUMBER);

TYPE g_gl_int_type_rec IS RECORD(
reference_date DATE
,attribute20 VARCHAR2(150)
,CONTEXT VARCHAR2(150)
,context2 VARCHAR2(150)
,invoice_date DATE
,tax_code VARCHAR2(15)
,invoice_identifier VARCHAR2(20)
,invoice_amount NUMBER
,context3 VARCHAR2(150)
,ussgl_transaction_code VARCHAR2(30)
,descr_flex_error_message VARCHAR2(240)
,jgzz_recon_ref VARCHAR2(240)
,segment23 VARCHAR2(25)
,segment24 VARCHAR2(25)
,segment25 VARCHAR2(25)
,segment26 VARCHAR2(25)
,segment27 VARCHAR2(25)
,segment28 VARCHAR2(25)
,segment29 VARCHAR2(25)
,segment30 VARCHAR2(25)
,entered_dr NUMBER
,entered_cr NUMBER
,accounted_dr NUMBER
,accounted_cr NUMBER
,transaction_date DATE
,reference1 VARCHAR2(100)
,reference2 VARCHAR2(240)
,reference3 VARCHAR2(100)
,reference4 VARCHAR2(100)
,reference5 VARCHAR2(240)
,reference6 VARCHAR2(100)
,reference7 VARCHAR2(100)
,reference8 VARCHAR2(100)
,reference9 VARCHAR2(100)
,reference10 VARCHAR2(240)
,reference11 VARCHAR2(100)
,reference12 VARCHAR2(100)
,reference13 VARCHAR2(100)
,reference14 VARCHAR2(100)
,reference15 VARCHAR2(100)
,reference16 VARCHAR2(100)
,reference17 VARCHAR2(100)
,reference18 VARCHAR2(100)
,reference19 VARCHAR2(100)
,reference20 VARCHAR2(100)
,reference21 VARCHAR2(240)
,reference22 VARCHAR2(240)
,reference23 VARCHAR2(240)
,reference24 VARCHAR2(240)
,reference25 VARCHAR2(240)
,reference26 VARCHAR2(240)
,reference27 VARCHAR2(240)
,reference28 VARCHAR2(240)
,reference29 VARCHAR2(240)
,reference30 VARCHAR2(240)
,je_batch_id NUMBER
,period_name VARCHAR2(15)
,je_header_id NUMBER
,je_line_num NUMBER
,chart_of_accounts_id NUMBER
,functional_currency_code VARCHAR2(15)
,code_combination_id NUMBER
,date_created_in_gl DATE
,warning_code VARCHAR2(4)
,status_description VARCHAR2(240)
,stat_amount NUMBER
,group_id NUMBER
,request_id NUMBER
,subledger_doc_sequence_id NUMBER
,subledger_doc_sequence_value NUMBER
,attribute1 VARCHAR2(150)
,attribute2 VARCHAR2(150)
,gl_sl_link_id NUMBER
,gl_sl_link_table VARCHAR2(30)
,attribute3 VARCHAR2(150)
,attribute4 VARCHAR2(150)
,attribute5 VARCHAR2(150)
,attribute6 VARCHAR2(150)
,attribute7 VARCHAR2(150)
,attribute8 VARCHAR2(150)
,attribute9 VARCHAR2(150)
,attribute10 VARCHAR2(150)
,attribute11 VARCHAR2(150)
,attribute12 VARCHAR2(150)
,attribute13 VARCHAR2(150)
,attribute14 VARCHAR2(150)
,attribute15 VARCHAR2(150)
,attribute16 VARCHAR2(150)
,attribute17 VARCHAR2(150)
,attribute18 VARCHAR2(150)
,attribute19 VARCHAR2(150)
,status VARCHAR2(50)
,set_of_books_id NUMBER
,accounting_date DATE
,currency_code VARCHAR2(15)
,date_created DATE
,created_by NUMBER
,actual_flag VARCHAR2(1)
,user_je_category_name VARCHAR2(25)
,user_je_source_name VARCHAR2(25)
,currency_conversion_date DATE
,encumbrance_type_id NUMBER
,budget_version_id NUMBER
,user_currency_conversion_type VARCHAR2(30)
,currency_conversion_rate NUMBER
,average_journal_flag VARCHAR2(1)
,originating_bal_seg_value VARCHAR2(25)
,segment1 VARCHAR2(25)
,segment2 VARCHAR2(25)
,segment3 VARCHAR2(25)
,segment4 VARCHAR2(25)
,segment5 VARCHAR2(25)
,segment6 VARCHAR2(25)
,segment7 VARCHAR2(25)
,segment8 VARCHAR2(25)
,segment9 VARCHAR2(25)
,segment10 VARCHAR2(25)
,segment11 VARCHAR2(25)
,segment12 VARCHAR2(25)
,segment13 VARCHAR2(25)
,segment14 VARCHAR2(25)
,segment15 VARCHAR2(25)
,segment16 VARCHAR2(25)
,segment17 VARCHAR2(25)
,segment18 VARCHAR2(25)
,segment19 VARCHAR2(25)
,segment20 VARCHAR2(25)
,segment21 VARCHAR2(25)
,segment22 VARCHAR2(25));

PROCEDURE insert_statement(p_gl_int_rec IN OUT NOCOPY g_gl_int_type_rec);

PROCEDURE transfer_to_gl(p_sob_id IN INTEGER
,p_batch_header_id IN INTEGER
,p_trx_header_id IN INTEGER
,p_trx_line_id IN INTEGER
,p_date IN OUT DATE
,p_currency_code IN VARCHAR2
,p_amount IN NUMBER
,p_dr_account IN INTEGER
,p_cr_account IN INTEGER
,p_journal_name IN VARCHAR2
,p_header_description IN VARCHAR2
,p_line_description IN VARCHAR2
,p_success_flag OUT BOOLEAN
,p_first_record_in_batch IN VARCHAR2
,p_submit_gl_interface IN VARCHAR2
,p_gl_user_je_source_name IN VARCHAR2
,p_gl_user_je_category_name IN VARCHAR2
,p_trx_number IN VARCHAR2);
PROCEDURE new_gl_interface_batch;

FUNCTION get_gl_je_reference1(p_batch_header_id IN INTEGER
,p_trx_number IN VARCHAR2)
RETURN VARCHAR2;

FUNCTION get_gl_application_id(p_appl_name IN VARCHAR2) RETURN INTEGER;

END xx_import_xfer_gl_iface_api;