oracle apps technical : ADSS Journal Details Report Required Package

/* Following Package To Retrieve Operating Unit Name and Prepare ID means Who Created PO,invoice means That Record*/

/* Package Specification*/

CREATE OR REPLACE PACKAGE APPS.xx_ads_gl_pkg AS

FUNCTION xx_ads_gl_prepareid (P_JE_SOURCE VARCHAR2,
P_JE_CATEGORY VARCHAR2,
p_entity_code VARCHAR2,
P_ENTITY_ID NUMBER,
P_EVENT_ID NUMBER,
P_JE_HEADER_ID NUMBER) RETURN VARCHAR2 RESULT_CACHE;

FUNCTION xx_ads_gl_ouname (P_JE_SOURCE VARCHAR2,
P_JE_CATEGORY VARCHAR2,
p_entity_code VARCHAR2,
P_ENTITY_ID NUMBER,
P_EVENT_ID NUMBER,
P_JE_HEADER_ID NUMBER)
RETURN VARCHAR2 RESULT_CACHE ;
END xx_ads_gl_pkg;

/*Package Body*/

CREATE OR REPLACE PACKAGE BODY APPS.xx_ads_gl_pkg
AS
FUNCTION xx_ads_gl_prepareid (P_JE_SOURCE VARCHAR2,
P_JE_CATEGORY VARCHAR2,
p_entity_code VARCHAR2,
P_ENTITY_ID NUMBER,
P_EVENT_ID NUMBER,
P_JE_HEADER_ID NUMBER)
RETURN VARCHAR2
RESULT_CACHE
IS
v_user_name VARCHAR2 (100);
BEGIN
IF ( UPPER (P_JE_SOURCE) = 'PAYABLES'
AND p_entity_code = 'AP_PAYMENTS'
AND UPPER (P_JE_CATEGORY) != 'PAYABLES MANUAL ENTRIES')
THEN
SELECT DISTINCT USER_NAME
INTO v_user_name
FROM ap_invoice_payments_all aip,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu
WHERE xte.application_id = xe.application_id
--AND aip.invoice_id = '166014'
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.source_id_int_1 = aip.check_id
AND xte.entity_id = xe.entity_id
AND aip.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --58097
AND xe.EVENT_ID = P_EVENT_ID
AND (SELECT MAX (aip1.ACCOUNTING_EVENT_ID)
FROM ap_invoice_payments_all aip1
WHERE aip1.check_id = aip.check_id) =
aip.ACCOUNTING_EVENT_ID; --101230
ELSIF ( UPPER (P_JE_SOURCE) = 'PAYABLES'
AND p_entity_code = 'AP_INVOICES'
AND UPPER (P_JE_CATEGORY) != 'PAYABLES MANUAL ENTRIES')
THEN
SELECT DISTINCT USER_NAME
INTO v_user_name
FROM ap_invoices_all aia,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu
WHERE xte.application_id = xe.application_id
--AND ai.invoice_id = '166014'
AND xte.entity_code = 'AP_INVOICES'
AND xte.source_id_int_1 = aia.invoice_id
AND xte.entity_id = xe.entity_id
AND aia.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --58097
AND xe.EVENT_ID = P_EVENT_ID; --101230
ELSIF ( UPPER (P_JE_SOURCE) = 'PAYABLES'
AND p_entity_code = 'MANUAL'
AND UPPER (P_JE_CATEGORY) = 'PAYABLES MANUAL ENTRIES')
THEN
SELECT DISTINCT USER_NAME
INTO v_user_name
FROM xla_events xe, xla.xla_transaction_entities xte, fnd_user fu
WHERE xte.application_id = xe.application_id
--AND ai.invoice_id = '166014'
AND xte.entity_code = 'MANUAL'
AND xte.entity_id = xe.entity_id
AND xte.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --58097
AND xe.EVENT_ID = P_EVENT_ID; --101230
ELSIF ( UPPER (P_JE_SOURCE) = 'PAYABLES'
AND p_entity_code = 'MANUAL'
AND UPPER (P_JE_CATEGORY) = 'PAYMENTS')
THEN
SELECT DISTINCT USER_NAME
INTO v_user_name
FROM ap_invoice_payments_all aip,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu
WHERE xte.application_id = xe.application_id
--AND aip.invoice_id = '166014'
AND xte.entity_code = 'MANUAL'
AND xte.source_id_int_1 = aip.check_id
AND xte.entity_id = xe.entity_id
AND aip.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --58097
AND xe.EVENT_ID = P_EVENT_ID;
ELSIF ( UPPER (P_JE_SOURCE) = 'PAYABLES'
AND p_entity_code = 'MANUAL'
AND UPPER (P_JE_CATEGORY) = 'RECONCILED PAYMENTS')
THEN
SELECT DISTINCT USER_NAME
INTO v_user_name
FROM ap_invoice_payments_all aip,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu
WHERE xte.application_id = xe.application_id
--AND aip.invoice_id = '166014'
AND xte.entity_code = 'MANUAL'
AND xte.source_id_int_1 = aip.check_id
AND xte.entity_id = xe.entity_id
AND aip.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --58097
AND xe.EVENT_ID = P_EVENT_ID;
ELSIF ( UPPER (P_JE_SOURCE) = 'PAYABLES'
AND p_entity_code = 'MANUAL'
AND UPPER (P_JE_CATEGORY) = 'OTHER')
THEN
SELECT DISTINCT USER_NAME
INTO v_user_name
FROM xla_events xe, xla.xla_transaction_entities xte, fnd_user fu
WHERE xte.application_id = xe.application_id
--AND ai.invoice_id = '166014'
AND xte.entity_code = 'MANUAL'
AND xte.entity_id = xe.entity_id
AND xte.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --58097
AND xe.EVENT_ID = P_EVENT_ID; --101230
ELSIF (UPPER (P_JE_SOURCE) = 'PURCHASING'
AND p_entity_code = 'PURCHASE_ORDER')
THEN
SELECT DISTINCT USER_NAME
INTO v_user_name
FROM po_headers_all pha,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu
WHERE xte.application_id = xe.application_id
AND xte.entity_code = 'PURCHASE_ORDER'
AND xte.source_id_int_1 = pha.PO_HEADER_ID
AND xte.entity_id = xe.entity_id
AND pha.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --20162
AND xe.EVENT_ID = P_EVENT_ID;
ELSIF (UPPER (P_JE_SOURCE) = 'PURCHASING' AND p_entity_code = 'RELEASE')
THEN
SELECT DISTINCT USER_NAME
INTO v_user_name
FROM po_releases_all pra,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu
WHERE xte.application_id = xe.application_id
AND xte.entity_code = 'RELEASE'
AND xte.source_id_int_1 = pra.PO_RELEASE_ID
AND xte.entity_id = xe.entity_id
AND pra.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --26215
AND xe.EVENT_ID = P_EVENT_ID; --59003
ELSIF (UPPER (P_JE_SOURCE) = 'PURCHASING'
AND p_entity_code = 'REQUISITION')
THEN
SELECT DISTINCT USER_NAME
INTO v_user_name
FROM po_requisition_headers_all prha,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu
WHERE xte.application_id = xe.application_id
AND xte.entity_code = 'REQUISITION'
AND xte.source_id_int_1 = prha.REQUISITION_HEADER_ID
AND xte.entity_id = xe.entity_id
AND prha.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --20129
AND xe.EVENT_ID = P_EVENT_ID; --50181
ELSIF (UPPER (P_JE_SOURCE) = 'RECEIVABLES'
AND p_entity_code = 'TRANSACTIONS')
THEN
SELECT DISTINCT USER_NAME
INTO v_user_name
FROM ra_customer_trx_all rcta,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu
WHERE xte.application_id = xe.application_id
AND xte.entity_code = 'TRANSACTIONS'
AND xte.source_id_int_1 = rcta.customer_trx_id
AND xte.entity_id = xe.entity_id
AND rcta.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --30054
AND xe.EVENT_ID = P_EVENT_ID; --63761
ELSIF (UPPER (P_JE_SOURCE) = 'RECEIVABLES'
AND p_entity_code = 'RECEIPTS')
THEN
SELECT DISTINCT USER_NAME
INTO v_user_name
FROM ar_cash_receipts_all acra,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu
WHERE xte.application_id = xe.application_id
AND xte.entity_code = 'RECEIPTS'
AND xte.source_id_int_1 = acra.CASH_RECEIPT_ID
AND xte.entity_id = xe.entity_id
AND acra.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --30054
AND xe.EVENT_ID = P_EVENT_ID; --63761
ELSIF (UPPER (P_JE_SOURCE) = 'CASH MANAGEMENT'
AND p_entity_code = 'CE_CASHFLOWS')
THEN
SELECT DISTINCT USER_NAME
INTO v_user_name
FROM CE_CASHFLOWS cc,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu
WHERE xte.application_id = xe.application_id
AND xte.entity_code = 'CE_CASHFLOWS'
AND xte.source_id_int_1 = cc.CASHFLOW_ID
AND xte.entity_id = xe.entity_id
AND cc.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --30054
AND xe.EVENT_ID = P_EVENT_ID; --63761
ELSIF (UPPER (P_JE_SOURCE) = 'ASSETS'
AND p_entity_code = 'TRANSACTIONS')
THEN
SELECT DISTINCT USER_NAME
INTO v_user_name
FROM FA_TRANSACTION_HEADERS fth,
FA_ADDITIONS_B fab,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu
WHERE fth.asset_id = fab.asset_id
AND xte.application_id = xe.application_id
AND xte.entity_code = 'TRANSACTIONS'
AND xte.source_id_int_1 = fth.TRANSACTION_HEADER_ID
AND xte.entity_id = xe.entity_id
AND fab.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --30054
AND xe.EVENT_ID = P_EVENT_ID; --63761
ELSIF ( UPPER (P_JE_SOURCE) = 'ASSETS'
AND p_entity_code = 'MANUAL'
AND P_JE_CATEGORY != 'Depreciation')
THEN
-- For JE_CATEGORY is Addition

SELECT DISTINCT USER_NAME
INTO v_user_name
FROM FA_ASSET_HISTORY fdh,
FA_ADDITIONS_B fab,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu
WHERE fdh.asset_id = fab.asset_id
AND xte.application_id = xe.application_id
AND xte.entity_code = 'MANUAL'
AND xte.source_id_int_1 = fdh.TRANSACTION_HEADER_ID_IN
AND xte.entity_id = xe.entity_id
AND fab.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --30054
AND xe.EVENT_ID = P_EVENT_ID; --63761
ELSIF (UPPER (P_JE_SOURCE) = 'ASSETS'
AND p_entity_code = 'DEPRECIATION')
THEN
/* SELECT DISTINCT USER_NAME
INTO v_user_name
FROM FA_DEPRN_DETAIL_H fdh,
FA_ADDITIONS_B fab,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu
WHERE fdh.asset_id= fab.asset_id
and xte.application_id = xe.application_id
AND xte.entity_code = 'DEPRECIATION'
AND xte.source_id_int_1 =fdh.distribution_id
AND xte.entity_id = xe.entity_id
AND fab.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --30054
AND xe.EVENT_ID = P_EVENT_ID; */
--63761

/* SELECT DISTINCT USER_NAME
INTO v_user_name
FROM FA_TRANSACTION_HEADERS fth,
FA_ADDITIONS_B fab,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu
WHERE fth.asset_id = fab.asset_id
AND xte.application_id = xe.application_id
AND xte.entity_code = 'DEPRECIATION'
AND xte.source_id_int_1 = fth.TRANSACTION_HEADER_ID
AND xte.entity_id = xe.entity_id
AND fab.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --30054
AND xe.EVENT_ID = P_EVENT_ID; --63761*/

SELECT DISTINCT USER_NAME
INTO v_user_name
FROM fa_deprn_detail fdd,
FA_ADDITIONS_B fab,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu
WHERE fdd.asset_id = fab.asset_id
AND xte.application_id = xe.application_id
AND xte.entity_code = 'DEPRECIATION'
AND fdd.EVENT_ID = xe.EVENT_ID
AND xte.entity_id = xe.entity_id
AND fab.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --99905
AND xe.EVENT_ID =P_EVENT_ID;
/*IN
(SELECT DISTINCT event_id
FROM xla_distribution_links
WHERE ae_header_id IN
(SELECT DISTINCT ae_header_id
FROM xla_ae_lines
WHERE gl_sl_link_id IN
(SELECT DISTINCT gl_sl_link_id
FROM gl_import_references
WHERE je_header_id =
P_JE_HEADER_ID-- AND je_line_num IN (1, 2)
)));*/ --P_EVENT_ID; --161817
ELSIF (UPPER (P_JE_SOURCE) = 'ASSETS' AND p_entity_code = 'MANUAL')
THEN
-- For JE_CATEGORY is Depreciation
SELECT DISTINCT USER_NAME
INTO v_user_name
FROM FA_ADDITIONS_B fab,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu
WHERE xte.application_id = xe.application_id
AND xte.entity_code = 'MANUAL'
AND xte.source_id_int_1 = fab.asset_id
AND xte.entity_id = xe.entity_id
AND fab.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --30054
AND xe.EVENT_ID = P_EVENT_ID; --63761
ELSIF ( UPPER (P_JE_SOURCE) = 'ASSETS'
AND p_entity_code = 'INTER_ASSET_TRANSACTIONS'
AND UPPER (P_JE_CATEGORY) = 'ADJUSTMENT')
THEN
SELECT DISTINCT USER_NAME
INTO v_user_name
FROM FA_TRANSACTION_HEADERS fth,
FA_ADDITIONS_B fab,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu
WHERE fth.asset_id = fab.asset_id
AND xte.application_id = xe.application_id
AND xte.entity_code = 'INTER_ASSET_TRANSACTIONS'
AND xte.source_id_int_1 = fth.TRX_REFERENCE_ID
AND xte.entity_id = xe.entity_id
AND fab.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --30054
AND xe.EVENT_ID = P_EVENT_ID;
ELSIF (UPPER (P_JE_SOURCE) = 'COST MANAGEMENT'
AND p_entity_code = 'RCV_ACCOUNTING_EVENTS')
THEN
SELECT DISTINCT USER_NAME
INTO v_user_name
FROM RCV_ACCOUNTING_EVENTS rav,
po_headers_all pha,
fnd_user fu,
xla_events xe,
xla.xla_transaction_entities xte
WHERE rav.PO_HEADER_ID = pha.PO_HEADER_ID
AND pha.CREATED_BY = fu.user_id
AND xte.application_id = xe.application_id
AND xte.entity_code = 'RCV_ACCOUNTING_EVENTS'
AND xte.SOURCE_ID_INT_2 = rav.ACCOUNTING_EVENT_ID
AND xte.ENTITY_ID = P_ENTITY_ID --30054
AND xe.EVENT_ID = P_EVENT_ID;
-- and rav.ACCOUNTING_EVENT_ID= 11104
--and pha.po_header_id=2538

ELSIF (UPPER (P_JE_SOURCE) = 'AUTOCOPY')
THEN
v_user_name := 'CHECK AUTOCOPY';
ELSIF (UPPER (P_JE_SOURCE) = 'PAYROLL')
THEN
v_user_name := 'CHECK PAYROLL';
ELSIF (UPPER (P_JE_SOURCE) = 'MANUAL')
THEN
SELECT DISTINCT fu.USER_NAME
INTO v_user_name
FROM gl_je_headers gjh, fnd_user fu
WHERE gjh.JE_HEADER_ID = P_JE_HEADER_ID --101
AND gjh.CREATED_BY = fu.user_id;
-- v_user_name := 'CHECK MANUAL';
ELSIF (UPPER (P_JE_SOURCE) = 'BUDGET JOURNAL')
THEN
v_user_name := 'CHECK BUDGET JOURNAL';
ELSIF (UPPER (P_JE_SOURCE) = 'SPREADSHEET')
THEN
v_user_name := 'CHECK SPREADSHEET';
ELSIF (UPPER (P_JE_SOURCE) = 'REVALUATION')
THEN
v_user_name := 'CHECK REVALUATION';
END IF;

RETURN v_user_name;
EXCEPTION
WHEN OTHERS
THEN
v_user_name :=
'Errror In Source'
|| P_JE_SOURCE
|| ' and entity code '
|| p_entity_code
|| ' And Category '
|| P_JE_CATEGORY;
RETURN v_user_name;
END xx_ads_gl_prepareid;

/********** Function To Get Operating Unit Name *************************/

FUNCTION xx_ads_gl_ouname (P_JE_SOURCE VARCHAR2,
P_JE_CATEGORY VARCHAR2,
p_entity_code VARCHAR2,
P_ENTITY_ID NUMBER,
P_EVENT_ID NUMBER,
P_JE_HEADER_ID NUMBER)
RETURN VARCHAR2
RESULT_CACHE
IS
v_ou_name VARCHAR2 (100);
BEGIN
IF ( UPPER (P_JE_SOURCE) = 'PAYABLES'
AND p_entity_code = 'AP_PAYMENTS'
AND UPPER (P_JE_CATEGORY) != 'PAYABLES MANUAL ENTRIES')
THEN
SELECT DISTINCT hou.NAME
INTO v_ou_name
FROM ap_invoice_payments_all aip,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu,
hr_operating_units hou
WHERE xte.application_id = xe.application_id
--AND aip.invoice_id = '166014'
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.source_id_int_1 = aip.check_id
AND xte.entity_id = xe.entity_id
AND aip.CREATED_BY = fu.user_id
AND aip.ORG_ID = hou.ORGANIZATION_ID
AND xte.ENTITY_ID = P_ENTITY_ID --58097
AND xe.EVENT_ID = P_EVENT_ID
AND (SELECT MAX (aip1.ACCOUNTING_EVENT_ID)
FROM ap_invoice_payments_all aip1
WHERE aip1.check_id = aip.check_id) =
aip.ACCOUNTING_EVENT_ID; --101230
ELSIF ( UPPER (P_JE_SOURCE) = 'PAYABLES'
AND p_entity_code = 'AP_INVOICES'
AND UPPER (P_JE_CATEGORY) != 'PAYABLES MANUAL ENTRIES')
THEN
SELECT DISTINCT hou.NAME
INTO v_ou_name
FROM ap_invoices_all aia,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu,
hr_operating_units hou
WHERE xte.application_id = xe.application_id
--AND ai.invoice_id = '166014'
AND xte.entity_code = 'AP_INVOICES'
AND xte.source_id_int_1 = aia.invoice_id
AND xte.entity_id = xe.entity_id
AND aia.CREATED_BY = fu.user_id
AND aia.ORG_ID = hou.ORGANIZATION_ID
AND xte.ENTITY_ID = P_ENTITY_ID --58097
AND xe.EVENT_ID = P_EVENT_ID; --101230
ELSIF ( UPPER (P_JE_SOURCE) = 'PAYABLES'
AND p_entity_code = 'MANUAL'
AND UPPER (P_JE_CATEGORY) = 'PAYABLES MANUAL ENTRIES')
THEN
/* SELECT DISTINCT hou.NAME
INTO v_ou_name
FROM
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu
WHERE xte.application_id = xe.application_id
--AND ai.invoice_id = '166014'
AND xte.entity_code = 'MANUAL'
AND xte.entity_id = xe.entity_id
AND xte.CREATED_BY = fu.user_id
AND xte.ENTITY_ID = P_ENTITY_ID --58097
AND xe.EVENT_ID = P_EVENT_ID; */
--101230

select gl.NAME
INTO v_ou_name
from gl_je_headers gjh,gl_ledgers gl
where gjh.LEDGER_ID=gl.LEDGER_ID
and gjh.JE_HEADER_ID=P_JE_HEADER_ID;

ELSIF ( UPPER (P_JE_SOURCE) = 'PAYABLES'
AND p_entity_code = 'MANUAL'
AND UPPER (P_JE_CATEGORY) = 'PAYMENTS')
THEN
SELECT DISTINCT hou.NAME
INTO v_ou_name
FROM ap_invoice_payments_all aip,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu,
hr_operating_units hou
WHERE xte.application_id = xe.application_id
--AND aip.invoice_id = '166014'
AND xte.entity_code = 'MANUAL'
AND xte.source_id_int_1 = aip.check_id
AND xte.entity_id = xe.entity_id
AND aip.CREATED_BY = fu.user_id
AND aip.ORG_ID = hou.ORGANIZATION_ID
AND xte.ENTITY_ID = P_ENTITY_ID --58097
AND xe.EVENT_ID = P_EVENT_ID;
ELSIF ( UPPER (P_JE_SOURCE) = 'PAYABLES'
AND p_entity_code = 'MANUAL'
AND UPPER (P_JE_CATEGORY) = 'RECONCILED PAYMENTS')
THEN
SELECT DISTINCT hou.NAME
INTO v_ou_name
FROM ap_invoice_payments_all aip,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu,
hr_operating_units hou
WHERE xte.application_id = xe.application_id
--AND aip.invoice_id = '166014'
AND xte.entity_code = 'MANUAL'
AND xte.source_id_int_1 = aip.check_id
AND xte.entity_id = xe.entity_id
AND aip.CREATED_BY = fu.user_id
AND aip.ORG_ID = hou.ORGANIZATION_ID
AND xte.ENTITY_ID = P_ENTITY_ID --58097
AND xe.EVENT_ID = P_EVENT_ID;
ELSIF ( UPPER (P_JE_SOURCE) = 'PAYABLES'
AND p_entity_code = 'MANUAL'
AND UPPER (P_JE_CATEGORY) = 'OTHER')
THEN
SELECT gl.NAME
INTO v_ou_name
FROM gl_je_headers gjh, gl_ledgers gl
WHERE gjh.LEDGER_ID = gl.LEDGER_ID
AND gjh.JE_HEADER_ID = P_JE_HEADER_ID;
ELSIF (UPPER (P_JE_SOURCE) = 'PURCHASING'
AND p_entity_code = 'PURCHASE_ORDER')
THEN
SELECT DISTINCT hou.NAME
INTO v_ou_name
FROM po_headers_all pha,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu,
hr_operating_units hou
WHERE xte.application_id = xe.application_id
AND xte.entity_code = 'PURCHASE_ORDER'
AND xte.source_id_int_1 = pha.PO_HEADER_ID
AND xte.entity_id = xe.entity_id
AND pha.CREATED_BY = fu.user_id
AND pha.ORG_ID = hou.ORGANIZATION_ID
AND xte.ENTITY_ID = P_ENTITY_ID --20162
AND xe.EVENT_ID = P_EVENT_ID;
ELSIF (UPPER (P_JE_SOURCE) = 'PURCHASING' AND p_entity_code = 'RELEASE')
THEN
SELECT DISTINCT hou.NAME
INTO v_ou_name
FROM po_releases_all pra,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu,
hr_operating_units hou
WHERE xte.application_id = xe.application_id
AND xte.entity_code = 'RELEASE'
AND xte.source_id_int_1 = pra.PO_RELEASE_ID
AND xte.entity_id = xe.entity_id
AND pra.CREATED_BY = fu.user_id
AND pra.ORG_ID = hou.ORGANIZATION_ID
AND xte.ENTITY_ID = P_ENTITY_ID --26215
AND xe.EVENT_ID = P_EVENT_ID; --59003
ELSIF (UPPER (P_JE_SOURCE) = 'PURCHASING'
AND p_entity_code = 'REQUISITION')
THEN
SELECT DISTINCT hou.NAME
INTO v_ou_name
FROM po_requisition_headers_all prha,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu,
hr_operating_units hou
WHERE xte.application_id = xe.application_id
AND xte.entity_code = 'REQUISITION'
AND xte.source_id_int_1 = prha.REQUISITION_HEADER_ID
AND xte.entity_id = xe.entity_id
AND prha.CREATED_BY = fu.user_id
AND prha.ORG_ID = hou.ORGANIZATION_ID
AND xte.ENTITY_ID = P_ENTITY_ID --20129
AND xe.EVENT_ID = P_EVENT_ID; --50181
ELSIF (UPPER (P_JE_SOURCE) = 'RECEIVABLES'
AND p_entity_code = 'TRANSACTIONS')
THEN
SELECT DISTINCT hou.NAME
INTO v_ou_name
FROM ra_customer_trx_all rcta,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu,
hr_operating_units hou
WHERE xte.application_id = xe.application_id
AND xte.entity_code = 'TRANSACTIONS'
AND xte.source_id_int_1 = rcta.customer_trx_id
AND xte.entity_id = xe.entity_id
AND rcta.CREATED_BY = fu.user_id
AND rcta.ORG_ID = hou.ORGANIZATION_ID
AND xte.ENTITY_ID = P_ENTITY_ID --30054
AND xe.EVENT_ID = P_EVENT_ID; --63761
ELSIF (UPPER (P_JE_SOURCE) = 'RECEIVABLES'
AND p_entity_code = 'RECEIPTS')
THEN
SELECT DISTINCT hou.NAME
INTO v_ou_name
FROM ar_cash_receipts_all acra,
xla_events xe,
xla.xla_transaction_entities xte,
fnd_user fu,
hr_operating_units hou
WHERE xte.application_id = xe.application_id
AND xte.entity_code = 'RECEIPTS'
AND xte.source_id_int_1 = acra.CASH_RECEIPT_ID
AND xte.entity_id = xe.entity_id
AND acra.CREATED_BY = fu.user_id
AND acra.ORG_ID = hou.ORGANIZATION_ID
AND xte.ENTITY_ID = P_ENTITY_ID --30054
AND xe.EVENT_ID = P_EVENT_ID; --63761
ELSIF (UPPER (P_JE_SOURCE) = 'CASH MANAGEMENT'
AND p_entity_code = 'CE_CASHFLOWS')
THEN
SELECT gl.NAME
INTO v_ou_name
FROM gl_je_headers gjh, gl_ledgers gl
WHERE gjh.LEDGER_ID = gl.LEDGER_ID
AND gjh.JE_HEADER_ID = P_JE_HEADER_ID;
ELSIF (UPPER (P_JE_SOURCE) = 'ASSETS'
AND p_entity_code = 'TRANSACTIONS')
THEN
SELECT gl.NAME
INTO v_ou_name
FROM gl_je_headers gjh, gl_ledgers gl
WHERE gjh.LEDGER_ID = gl.LEDGER_ID
AND gjh.JE_HEADER_ID = P_JE_HEADER_ID;
ELSIF ( UPPER (P_JE_SOURCE) = 'ASSETS'
AND p_entity_code = 'MANUAL'
AND P_JE_CATEGORY != 'Depreciation')
THEN
SELECT gl.NAME
INTO v_ou_name
FROM gl_je_headers gjh, gl_ledgers gl
WHERE gjh.LEDGER_ID = gl.LEDGER_ID
AND gjh.JE_HEADER_ID = P_JE_HEADER_ID;
ELSIF (UPPER (P_JE_SOURCE) = 'ASSETS'
AND p_entity_code = 'DEPRECIATION')
THEN
SELECT gl.NAME
INTO v_ou_name
FROM gl_je_headers gjh, gl_ledgers gl
WHERE gjh.LEDGER_ID = gl.LEDGER_ID
AND gjh.JE_HEADER_ID = P_JE_HEADER_ID;
ELSIF (UPPER (P_JE_SOURCE) = 'ASSETS' AND p_entity_code = 'MANUAL')
THEN
SELECT gl.NAME
INTO v_ou_name
FROM gl_je_headers gjh, gl_ledgers gl
WHERE gjh.LEDGER_ID = gl.LEDGER_ID
AND gjh.JE_HEADER_ID = P_JE_HEADER_ID;
ELSIF ( UPPER (P_JE_SOURCE) = 'ASSETS'
AND p_entity_code = 'INTER_ASSET_TRANSACTIONS'
AND UPPER (P_JE_CATEGORY) = 'ADJUSTMENT')
THEN
SELECT gl.NAME
INTO v_ou_name
FROM gl_je_headers gjh, gl_ledgers gl
WHERE gjh.LEDGER_ID = gl.LEDGER_ID
AND gjh.JE_HEADER_ID = P_JE_HEADER_ID;
ELSIF (UPPER (P_JE_SOURCE) = 'COST MANAGEMENT'
AND p_entity_code = 'RCV_ACCOUNTING_EVENTS')
THEN
SELECT DISTINCT hou.NAME
INTO v_ou_name
FROM RCV_ACCOUNTING_EVENTS rav,
po_headers_all pha,
fnd_user fu,
xla_events xe,
xla.xla_transaction_entities xte,
hr_operating_units hou
WHERE rav.PO_HEADER_ID = pha.PO_HEADER_ID
AND pha.CREATED_BY = fu.user_id
AND xte.application_id = xe.application_id
AND xte.entity_code = 'RCV_ACCOUNTING_EVENTS'
AND xte.SOURCE_ID_INT_2 = rav.ACCOUNTING_EVENT_ID
AND pha.ORG_ID = hou.ORGANIZATION_ID
AND xte.ENTITY_ID = P_ENTITY_ID --30054
AND xe.EVENT_ID = P_EVENT_ID;
-- and rav.ACCOUNTING_EVENT_ID= 11104
--and pha.po_header_id=2538

ELSIF (UPPER (P_JE_SOURCE) = 'AUTOCOPY')
THEN
v_ou_name := '';
ELSIF (UPPER (P_JE_SOURCE) = 'PAYROLL')
THEN
v_ou_name := '';
ELSIF (UPPER (P_JE_SOURCE) = 'MANUAL')
THEN
SELECT gl.NAME
INTO v_ou_name
FROM gl_je_headers gjh, gl_ledgers gl
WHERE gjh.LEDGER_ID = gl.LEDGER_ID
AND gjh.JE_HEADER_ID = P_JE_HEADER_ID;
-- v_ou_name := 'CHECK MANUAL';
ELSIF (UPPER (P_JE_SOURCE) = 'BUDGET JOURNAL')
THEN
v_ou_name := 'CHECK BUDGET JOURNAL';
ELSIF (UPPER (P_JE_SOURCE) = 'SPREADSHEET')
THEN
v_ou_name := 'CHECK SPREADSHEET';
ELSIF (UPPER (P_JE_SOURCE) = 'REVALUATION')
THEN
v_ou_name := 'CHECK REVALUATION';
END IF;

RETURN v_ou_name;
EXCEPTION
WHEN OTHERS
THEN
v_ou_name :=
'Errror In Source'
|| P_JE_SOURCE
|| ' and entity code '
|| p_entity_code
|| ' And Category '
|| P_JE_CATEGORY;
RETURN v_ou_name;
END xx_ads_gl_ouname;
END XX_ADS_GL_PKG;
/

oracle apps technical

Amazon

ADSS Journal Details Report Required Package

No comments:

Post a Comment