--CREATE OR REPLACE VIEW MVL_FA_LISTING_V
(BOOK_TYPE_CODE, ASSET_NUMBER, ASSET_ID, TAG_NUMBER, DATE_PLACED_IN_SERVICE,
DESCRIPTION, ASSET_CATEGORY, ASSET_CATEGORY1, ASSET_COST, ORIGINAL_ASSET_COST,
ASSET_LIFE, ACCUM_DEPRECIATION, YTD_DEPRECIATION, DEP_THIS_RUN, DEPARTMENT_NO,
CUSTODIAN, EMPLOYEE_NUMBER, PO_NUMBER, INVOICE_NUMBER, VENDOR_NUMBER,
VENDOR_NAME, SERIAL_NUMBER, LOCATION_FLEXFIELD, TAX_MAJOR_CATEGORY, TAX_MINOR_CATEGORY,
RETIREMENT_TYPE, ASSET_KEY, PERIOD_NAME, ACQUISITION_DATE, MANUFACTURER_NAME,
RETIREMENT_PENDING_FLAG)
AS
SELECT fbv.book_type_code
,fab.asset_number,fab.asset_id
,fab.tag_number
,fbv.date_placed_in_service
,fab.description
,fcb.segment1||'.'||fcb.segment2 asset_category
,fcb.segment1||'.'||fcb.segment2 asset_category1
,fbv.cost asset_cost
,fbv.original_cost original_asset_cost
,fbv.life_in_months asset_life
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'ACCUM') accum_depreciation
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'YTD') ytd_depreciation
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'RUN') dep_this_run
,gcc.segment2 department_no
,(SELECT ppx.full_name FROM per_people_x ppx WHERE ppx.person_id = fdh.assigned_to AND ROWNUM = 1) custodian
,(SELECT ppx.employee_number FROM per_people_x ppx WHERE ppx.person_id = fdh.assigned_to AND ROWNUM = 1) employee_number
,(SELECT po_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) po_number
,(SELECT invoice_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) invoice_number
,(SELECT vendor_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) vendor_number
,(SELECT vendor_name FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) vendor_name
,fab.serial_number
,fl.segment3||'.'||fl.segment4 location_flexfield
,fab.attribute1 tax_major_category
,fab.attribute2 tax_minor_category
,(SELECT retirement_type_code FROM fa_retirements WHERE retirement_id = fdh.retirement_id AND ROWNUM = 1) retirement_type
,(SELECT segment1||'.'||segment2 FROM fa_asset_keywords WHERE code_combination_id = fab.asset_key_ccid AND ROWNUM = 1) asset_key
,fdp.period_name
,fab.attribute3 acquisition_date
,fab.manufacturer_name
,fbv.retirement_pending_flag
FROM apps.fa_additions_v fab
,apps.fa_books_v fbv
,apps.fa_categories_b fcb
,apps.fa_deprn_periods fdp
,apps.fa_distribution_history fdh
,apps.gl_code_combinations gcc
,apps.fa_locations fl
WHERE 1=1
AND fab.asset_id = fbv.asset_id
AND fcb.category_id = fab.asset_category_id
AND fbv.transaction_header_id_out IS NULL
AND fdp.book_type_code = fbv.book_type_code
AND fdh.asset_id = fbv.asset_id
AND fdh.code_combination_id = gcc.code_combination_id
AND fdh.location_id=fl.location_id
AND fbv.transaction_header_id_out IS NULL
AND fdh.transaction_header_id_out IS NULL
/
***************************************PACKAGE Body***********************************
CREATE OR REPLACE PACKAGE BODY APPS.mvl_discoverer_fin_support IS
FUNCTION get_po_category(p_po_line_id NUMBER
,p_invoice_id NUMBER) RETURN VARCHAR2 IS
l_category_name VARCHAR2(200):= NULL;
BEGIN
SELECT segment1||'.'||segment2
INTO l_category_name
FROM mtl_categories_b mcb
,po_lines_all pla
WHERE mcb.category_id = pla.category_id
AND pla.po_line_id = p_po_line_id;
RETURN l_category_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT segment1||'.'||segment2
INTO l_category_name
FROM ap_invoice_distributions_all aid
,po_distributions_all pd
,po_lines_all pl
,mtl_categories_b mcb
WHERE aid.invoice_id = p_invoice_id
AND aid.po_distribution_id = pd.po_distribution_id
AND pd.po_line_id = pl.po_line_id
AND mcb.category_id = pl.category_id
AND ROWNUM = 1;
RETURN l_category_name;
WHEN OTHERS THEN
RETURN NULL;
END get_po_category;
--/* ------------------------------------------------------------------- */
-- FUNCTION get_depreciation_dtls
--/* ------------------------------------------------------------------- */
FUNCTION get_depreciation_dtls(p_asset_id NUMBER
,p_booktype_code VARCHAR2
,p_distribution_id NUMBER
,p_period_counter NUMBER
,p_type VARCHAR2) RETURN NUMBER IS
l_deprn_reserve NUMBER;
BEGIN
SELECT DECODE(p_type,'ACCUM',deprn_reserve,'YTD',ytd_deprn,'RUN',deprn_amount)
INTO l_deprn_reserve
FROM apps.fa_deprn_summary fdd
WHERE fdd.asset_id = p_asset_id
AND fdd.book_type_code = p_booktype_code
AND fdd.period_counter = p_period_counter;
RETURN l_deprn_reserve;
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT DECODE(p_type,'ACCUM',deprn_reserve,'YTD',ytd_deprn,'RUN',0)
INTO l_deprn_reserve
FROM apps.fa_deprn_summary fdd
WHERE fdd.asset_id = p_asset_id
AND fdd.book_type_code = p_booktype_code
AND fdd.period_counter = (SELECT MAX(period_counter)
FROM apps.fa_deprn_detail fdd2
WHERE fdd2.asset_id = p_asset_id
AND fdd2.book_type_code = p_booktype_code);
RETURN l_deprn_reserve;
WHEN OTHERS THEN
RETURN 0;
END get_depreciation_dtls;
--/* ------------------------------------------------------------------- */
-- SCR#12634
-- FUNCTION get_invpo_match_rqstr_id
--/* ------------------------------------------------------------------- */
FUNCTION get_invpo_match_rqstr_id(p_invoice_id NUMBER) RETURN VARCHAR2 IS
ln_requestor_id PO_DISTRIBUTIONS_ALL.deliver_to_person_id%TYPE;
BEGIN
SELECT DISTINCT PDA2.deliver_to_person_id
INTO ln_requestor_id
FROM po_distributions_all PDA2
,ap_invoice_distributions_all AIDA2
WHERE AIDA2.po_distribution_id = PDA2.po_distribution_id
AND AIDA2.invoice_id = p_invoice_id
AND PDA2.deliver_to_person_id IS NOT NULL;
RETURN(ln_requestor_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN(NULL);
END get_invpo_match_rqstr_id;
--/* ------------------------------------------------------------------- */
-- SCR#12634
-- FUNCTION get_invpo_lnematch_rqstr_id
--/* ------------------------------------------------------------------- */
FUNCTION get_invpo_lnematch_rqstr_id(p_invoice_id NUMBER) RETURN VARCHAR2 IS
ln_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
ln_requestor_id PO_DISTRIBUTIONS_ALL.deliver_to_person_id%TYPE;
BEGIN
BEGIN
SELECT DISTINCT POD.po_header_id
INTO ln_po_header_id
FROM ap_invoice_distributions_all APID
,po_distributions_all POD
WHERE APID.po_distribution_id = POD.po_distribution_id
AND APID.invoice_id = p_invoice_id
AND rownum = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ln_requestor_id := NULL;
END;
IF ln_po_header_id IS NOT NULL THEN
BEGIN
SELECT DISTINCT deliver_to_person_id
INTO ln_requestor_id
FROM po_distributions_all
WHERE po_header_id = ln_po_header_id
AND deliver_to_person_id IS NOT NULL
AND rownum = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ln_requestor_id := NULL;
END;
END IF;
RETURN(ln_requestor_id);
END get_invpo_lnematch_rqstr_id;
FUNCTION get_wfr_rqstr(p_po_header_id PO_HEADERS_ALL.po_header_id%TYPE) RETURN VARCHAR2 IS
CURSOR cur_wfr_rqstr(p_po_header_id PO_HEADERS_ALL.po_header_id%TYPE) IS
SELECT DISTINCT POD.creation_date, POD.deliver_to_person_id, POA.agent_name
FROM po_distributions_all POD
,po_agents_v POA
WHERE POD.deliver_to_person_id = POA.agent_id(+)
AND POD.po_header_id = p_po_header_id
ORDER BY POD.creation_date;
ld_creation_date PO_DISTRIBUTIONS_ALL.creation_date%TYPE;
ln_deliver_to_person_id PO_DISTRIBUTIONS_ALL.deliver_to_person_id%TYPE;
lc_agent_name PO_AGENTS_V.agent_name%TYPE;
ln_ctr NUMBER:=0;
BEGIN
BEGIN
SELECT DISTINCT POD.deliver_to_person_id, POA.agent_name
INTO ln_deliver_to_person_id, lc_agent_name
FROM po_distributions_all POD
,po_agents_v POA
WHERE POD.deliver_to_person_id = POA.agent_id(+)
AND POD.po_header_id = p_po_header_id;
RETURN(lc_agent_name);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
FOR rec_wfr_rqstr IN cur_wfr_rqstr(p_po_header_id) LOOP
ln_ctr := ln_ctr + 1;
lc_agent_name := rec_wfr_rqstr.agent_name;
IF ln_ctr = 1 THEN
EXIT;
END IF;
END LOOP;
RETURN(lc_agent_name);
END;
END get_wfr_rqstr;
END mvl_discoverer_fin_support;
/
*****************************************Package Header*********************
CREATE OR REPLACE PACKAGE APPS.mvl_discoverer_fin_support IS
FUNCTION get_po_category(p_po_line_id NUMBER,p_invoice_id NUMBER) RETURN VARCHAR2;
FUNCTION get_depreciation_dtls(p_asset_id NUMBER
,p_booktype_code VARCHAR2
,p_distribution_id NUMBER
,p_period_counter NUMBER
,p_type VARCHAR2) RETURN NUMBER;
FUNCTION get_invpo_match_rqstr_id(p_invoice_id NUMBER) RETURN VARCHAR2;
FUNCTION get_invpo_lnematch_rqstr_id(p_invoice_id NUMBER) RETURN VARCHAR2;
FUNCTION get_wfr_rqstr(p_po_header_id PO_HEADERS_ALL.po_header_id%TYPE) RETURN VARCHAR2;
END mvl_discoverer_fin_support;
/