BOM ITEM CONVERSION

CREATE TABLE XXOIC_CV_BOM_STG
(
RECORD_NO NUMBER,
ORGANIZATION_CODE VARCHAR2(3 BYTE),
ITEM_NAME VARCHAR2(81 BYTE),
ALTERNATE_BOM VARCHAR2(10 BYTE),
OPERATION_SEQUENCE NUMBER,
COMPONENT_ITEM VARCHAR2(81 BYTE),
COMPONENT_QUANTITY NUMBER,
COMPONENT_YIELD NUMBER,
INCLUDED_IN_COST_ROLLUP VARCHAR2(3 BYTE),
COMPONENT_SUPPLY_TYPE VARCHAR2(80 BYTE),
SUPPLY_SUBINVENTORY VARCHAR2(10 BYTE),
ERROR_CODE VARCHAR2(20 BYTE),
PROCESS_FLAG VARCHAR2(2 BYTE) DEFAULT 'R',
ERROR_MSG VARCHAR2(2000 BYTE),
DATA_FILE_NAME VARCHAR2(200 BYTE),
DATE_RUN DATE,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
CREATION_DATE DATE,
CREATED_BY NUMBER,
LAST_UPDATE_LOGIN NUMBER
)

/*USING BELOW CTL FILE TO INSERT THE DATA TO THE ABOVE TABLE*/

--Control file

-- *****************************************************************************

-- *****************************************************************************
-- ----------------------------------------------------------------------------+
-- Description : Control file for Bill of Material.

-- Written on : 29-sept-2008
-- -----------------------------------------------------------------------------
OPTIONS (skip=1)
LOAD DATA
INFILE '&data_file_name'
APPEND
INTO TABLE XXOIC.XXOIC_CV_BOM_STG
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
record_no sequence(MAX,1),
organization_code ,
item_name ,
alternate_bom ,
operation_sequence ,
component_item ,
component_quantity ,
component_yield ,
included_in_cost_rollup ,
component_supply_type ,
supply_subinventory ,
data_file_name ,
date_run SYSDATE,
last_update_date SYSDATE,
last_updated_by "FND_GLOBAL.USER_ID",
creation_date SYSDATE,
created_by "FND_GLOBAL.USER_ID",
last_update_login "FND_GLOBAL.LOGIN_ID"
)

/* Move the above CTL File & CSV file (with data)into the Below Path
/prod01/app/applprod/prodappl/XXOIC/bin */

/*After inserting the data to the above custom table...we have to validate the data,using below script
For this we have to registerd the concurrent program
in this program pls give the parameter : /prod01/app/applprod/prodappl/XXOIC/bin/xxxx.CSV*/

select text from user_source where name='XXOIC_CV_BOM_VALIDATE_PKG'
*********************************************************************************************************
/* Formatted on 2009/06/08 17:52 (Formatter Plus v4.8.6) */
PACKAGE xxoic_cv_bom_validate_pkg
AS
/* =====================================================================

-- All rights reserved.
-- =====================================================================
-- NAME : XXOIC_CV_BOM_VALIDATE_PKG.sql
--
-- DESIGN REFERENCE : Package for creating a Bill Of Material
--
-- PROGRAM TYPE : SQL Script
--
-- PURPOSE : This package inputs data from staging table into interface tables
-- NOTES :
--
-- REVISION HISTORY :
-- =====================================================================
-- Date Author Activity
-- =====================================================================


-- ===================================================================*/
PROCEDURE xxoic_cv_bom_int (
/* =====================================================================
-- NAME : xxoic_cv_bom_int
--
-- PROGRAM TYPE : Procedure
--
-- DESCRIPTION : This procedure fetches data from xxoic.xxoic_cv_bom_stg staging table and
-- inserts into bom_bill_of_mtls_interface,bom_inventory_comps_interface
-- interface tables.
-- INPUTS : None
-- OUTPUTS : None
-- ===================================================================*/
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2
);
END xxoic_cv_bom_validate_pkg;

PACKAGE BODY xxoic_cv_bom_validate_pkg
AS
PROCEDURE xxoic_cv_bom_int (
/* =====================================================================
-- NAME : xxoic_cv_bom_int
--
-- PROGRAM TYPE : Procedure
--
-- DESCRIPTION : This procedure fetches data from xxoic.xxoic_cv_bom_stg staging table and
-- inserts into bom_bill_of_mtls_interface,bom_inventory_comps_interface
-- interface tables.

-- INPUTS : None

-- OUTPUTS : None
-- ===================================================================*/
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2
)
AS
CURSOR c_bom_itm
IS
SELECT record_no, organization_code, item_name, alternate_bom
FROM xxoic.xxoic_cv_bom_stg
WHERE process_flag = 'R';

CURSOR c_bom_comp (cp_item VARCHAR2, cp_organization_code VARCHAR2)
IS
SELECT record_no, organization_code, item_name, alternate_bom,
operation_sequence, component_item, component_quantity,
component_yield,
DECODE (included_in_cost_rollup,
'Y', 1,
'N', 2,
NULL
) included_in_cost_rollup,
component_supply_type, supply_subinventory
FROM xxoic.xxoic_cv_bom_stg
WHERE process_flag = 'R'
AND organization_code = cp_organization_code
AND item_name = cp_item;

r_bom_record_itm c_bom_itm%ROWTYPE;
r_bom_record_comp c_bom_comp%ROWTYPE;
lv_error_msg VARCHAR2 (200 BYTE);
lv_flag VARCHAR2 (1 BYTE);
lv_comp_flag VARCHAR2 (1 BYTE);
ln_organization_id NUMBER;
lv_count NUMBER;
ln_item_id NUMBER;
ln_component_item_id NUMBER;
lv_organization_code VARCHAR2 (3 BYTE);
lv_error_code VARCHAR2 (200);
ln_bom_item_type NUMBER;
lv_item_num VARCHAR (240 BYTE);
ln_record_no NUMBER;
ln_record_no_comp NUMBER;
lv_alternate_bom VARCHAR2 (10);
lv_trans_detail1 VARCHAR2 (2000);
lv_trans_detail2 VARCHAR2 (2000);
lv_trans_detail3 VARCHAR2 (2000);
lv_trans_detail4 VARCHAR2 (2000);
ln_lookup_code NUMBER;
ln_successful_rec NUMBER := 0;
ln_failed_rec NUMBER := 0;
BEGIN
FOR r_bom_record_itm IN c_bom_itm
LOOP
lv_flag := NULL;
lv_comp_flag := NULL;
lv_error_code := NULL;
ln_record_no := r_bom_record_itm.record_no;
lv_trans_detail1 := NULL;
lv_trans_detail2 := NULL;
lv_trans_detail3 := NULL;
lv_trans_detail4 := NULL;

/*
================================================================================
--Validation for organization id
================================================================================
*/
IF r_bom_record_itm.organization_code IS NOT NULL
THEN
BEGIN
SELECT organization_id
INTO ln_organization_id
FROM org_organization_definitions
WHERE organization_code = r_bom_record_itm.organization_code;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := SQLCODE;
lv_flag := 'N';
lv_error_msg := 'Organization does not exist';
lv_trans_detail1 :=
'Unable to find organization id for this record';
lv_trans_detail2 := 'Provide with valid organization code';
xxoic_error_handling_pkg.xxoic_print_log
(ln_record_no,
lv_error_code,
lv_error_msg,
lv_trans_detail1,
lv_trans_detail2,
lv_trans_detail3,
lv_trans_detail4,
'xxoic.xxoic_cv_bom_stg',
SYSDATE
);
DBMS_OUTPUT.put_line ( r_bom_record_itm.organization_code
|| ' Organization does not exist '
|| SQLERRM
);
fnd_file.put_line (fnd_file.LOG,
r_bom_record_itm.organization_code
|| ' Organization does not exist'
);
END;
ELSE
IF r_bom_record_itm.organization_code IS NULL
THEN
lv_error_code := SQLCODE;
lv_flag := 'N';
lv_error_msg := 'Organization code can not be null';
lv_trans_detail1 := 'Organization code can not be left blank';
xxoic_error_handling_pkg.xxoic_print_log
(ln_record_no,
lv_error_code,
lv_error_msg,
lv_trans_detail1,
lv_trans_detail2,
lv_trans_detail3,
lv_trans_detail4,
'xxoic.xxoic_cv_bom_stg',
SYSDATE
);
DBMS_OUTPUT.put_line ( 'Organization code can not be null'
|| SQLERRM
);
fnd_file.put_line (fnd_file.LOG,
'Organization code can not be null'
);
END IF;
END IF;

/*

================================================================================

--Validation for inventry item name

================================================================================

*/
IF r_bom_record_itm.item_name IS NOT NULL
THEN
BEGIN
SELECT bom_item_type, segment1, inventory_item_id
INTO ln_bom_item_type, lv_item_num, ln_item_id
FROM mtl_system_items_b
WHERE segment1 = r_bom_record_itm.item_name
AND organization_id = ln_organization_id
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := SQLCODE;
lv_flag := 'N';
lv_error_msg := 'Item name does not exist';
lv_trans_detail1 := 'Item name value must be provided';
lv_trans_detail2 := 'Required for deriving item type';
xxoic_error_handling_pkg.xxoic_print_log
(ln_record_no,
lv_error_code,
lv_error_msg,
lv_trans_detail1,
lv_trans_detail2,
lv_trans_detail3,
lv_trans_detail4,
'xxoic.xxoic_cv_bom_stg',
SYSDATE
);
DBMS_OUTPUT.put_line ( r_bom_record_itm.item_name
|| 'Item name does not exist'
|| SQLERRM
);
fnd_file.put_line (fnd_file.LOG,
r_bom_record_itm.item_name
|| 'Item name does not exist'
);
END;
END IF;

/*

================================================================================

--Validation for alternate bom desig

================================================================================

*/
IF r_bom_record_itm.alternate_bom IS NOT NULL
THEN
BEGIN
SELECT alternate_designator_code
INTO lv_alternate_bom
FROM bom_alternate_designators_tl
WHERE alternate_designator_code =
r_bom_record_itm.alternate_bom
AND organization_id = ln_organization_id;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := SQLCODE;
lv_flag := 'N';
lv_error_msg :=
'The given alternate_designator does not exist';
lv_trans_detail1 :=
'The given alternate_designator does not exist';
xxoic_error_handling_pkg.xxoic_print_log
(ln_record_no,
lv_error_code,
lv_error_msg,
lv_trans_detail1,
lv_trans_detail2,
lv_trans_detail3,
lv_trans_detail4,
'xxoic.xxoic_cv_bom_stg',
SYSDATE
);
DBMS_OUTPUT.put_line ( 'The given alternate_designator '
|| r_bom_record_itm.alternate_bom
|| ' does not exist'
|| SQLERRM
);
fnd_file.put_line (fnd_file.LOG,
'The given alternate_designator '
|| r_bom_record_itm.alternate_bom
|| ' does not exist'
);
END;
END IF;

/*

================================================================================

check for already inserted data in interface table

================================================================================

*/
BEGIN
SELECT COUNT (1)
INTO lv_count
FROM bom_bill_of_mtls_interface
WHERE organization_id = ln_organization_id
AND assembly_item_id = ln_item_id;

IF lv_count > 0
THEN
lv_flag := 'N';
END IF;
END;

/*

================================================================================

--updload data into bom_bill_of_mtls_interface interface table

================================================================================

*/
IF lv_flag IS NULL
THEN
BEGIN
INSERT INTO bom_bill_of_mtls_interface
(bill_sequence_id,
alternate_bom_designator, assembly_item_id,
process_flag, item_number, last_update_login,
last_update_date, last_updated_by,
creation_date, created_by, organization_id,
transaction_type
)
VALUES (bom.bom_inventory_components_s.NEXTVAL,
lv_alternate_bom, ln_item_id,
1, lv_item_num, fnd_global.login_id,
SYSDATE, fnd_global.user_id,
SYSDATE, fnd_global.user_id, ln_organization_id,
'Create'
);

lv_comp_flag := 'Y';
EXCEPTION
WHEN OTHERS
THEN
lv_comp_flag := 'N';
lv_error_msg :=
'Unble to insert records into interface table bom_bill_of_mtls_interface';
lv_error_code := SQLCODE;
lv_trans_detail1 :=
'Unble to insert records into interface table bom_bill_of_mtls_interface';
xxoic_error_handling_pkg.xxoic_print_log
(ln_record_no,
lv_error_code,
lv_error_msg,
lv_trans_detail1,
lv_trans_detail2,
lv_trans_detail3,
lv_trans_detail4,
'xxoic.xxoic_cv_bom_stg',
SYSDATE
);
DBMS_OUTPUT.put_line
( 'Unble to insert records into interface table bom_bill_of_mtls_interface with record_no.'
|| ln_record_no
|| SQLERRM
);
fnd_file.put_line
(fnd_file.LOG,
'Unble to insert records into interface table bom_bill_of_mtls_interface'
);
END;
END IF;

/*

===============================================================================

Loop for inserting data into bom_inventory_comps_interface table

===============================================================================

*/
IF lv_comp_flag = 'Y'
THEN
BEGIN
FOR r_bom_record_comp IN
c_bom_comp (r_bom_record_itm.item_name,
r_bom_record_itm.organization_code
)
LOOP
lv_error_code := NULL;
ln_record_no_comp := r_bom_record_comp.record_no;
lv_trans_detail1 := NULL;
lv_trans_detail2 := NULL;
lv_trans_detail3 := NULL;
lv_trans_detail4 := NULL;

/*

================================================================================

--Validation for component item name/component item id

================================================================================

*/
IF r_bom_record_comp.component_item IS NOT NULL
THEN
BEGIN
SELECT inventory_item_id
INTO ln_component_item_id
FROM mtl_system_items_b
WHERE organization_id = ln_organization_id
AND segment1 = r_bom_record_comp.component_item
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := SQLCODE;
lv_flag := 'N';
lv_error_msg :=
'component item of this name does not exist';
lv_trans_detail1 :=
'Unable to find component_item_id for this component_item_name';
lv_trans_detail2 :=
'Provide with valid component item name';
xxoic_error_handling_pkg.xxoic_print_log
(ln_record_no_comp,
lv_error_code,
lv_error_msg,
lv_trans_detail1,
lv_trans_detail2,
lv_trans_detail3,
lv_trans_detail4,
'xxoic.xxoic_cv_bom_stg',
SYSDATE
);
DBMS_OUTPUT.put_line
( r_bom_record_comp.component_item
|| ' component item does not exist '
|| SQLERRM
);
fnd_file.put_line
(fnd_file.LOG,
r_bom_record_comp.component_item
|| ' component item does not exist'
);
END;
END IF;

/*

================================================================================

--Validation for component_supply_type

================================================================================

*/
IF r_bom_record_comp.component_supply_type IS NOT NULL
THEN
BEGIN
SELECT DISTINCT lookup_code
INTO ln_lookup_code
FROM mfg_lookups
WHERE lookup_type = 'WIP_SUPPLY'
AND meaning =
r_bom_record_comp.component_supply_type;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := SQLCODE;
lv_flag := 'N';
lv_error_msg :=
'The given value for component_supply_type is invalid ';
lv_trans_detail1 :=
'The given value for component_supply_type is invalid';
xxoic_error_handling_pkg.xxoic_print_log
(ln_record_no_comp,
lv_error_code,
lv_error_msg,
lv_trans_detail1,
lv_trans_detail2,
lv_trans_detail3,
lv_trans_detail4,
'xxoic.xxoic_cv_bom_stg',
SYSDATE
);
DBMS_OUTPUT.put_line
( 'The given component_supply_type '
|| r_bom_record_comp.component_supply_type
|| 'is invalid '
|| SQLERRM
);
fnd_file.put_line
(fnd_file.LOG,
'The given component_supply_type '
|| r_bom_record_comp.component_supply_type
|| 'is invalid '
);
END;
END IF;

/*

================================================================================

--updload data into bom_inventory_comps_interface interface table

================================================================================

*/
IF lv_flag IS NULL
THEN
BEGIN
INSERT INTO bom_inventory_comps_interface
(bill_sequence_id,
assembly_item_id, component_item_id,
effectivity_date,
organization_code,
organization_id, bom_item_type,
last_update_login, last_update_date,
last_updated_by, creation_date,
created_by,
alternate_bom_designator,
operation_seq_num,
component_quantity,
component_yield_factor, process_flag,
transaction_type,
include_in_cost_rollup,
wip_supply_type,
supply_subinventory
)
VALUES (bom.bom_inventory_components_s.CURRVAL,
ln_item_id, ln_component_item_id,
SYSDATE,
r_bom_record_comp.organization_code,
ln_organization_id, ln_bom_item_type,
fnd_global.login_id, SYSDATE,
fnd_global.user_id, SYSDATE,
fnd_global.user_id,
r_bom_record_comp.alternate_bom,
r_bom_record_comp.operation_sequence,
r_bom_record_comp.component_quantity,
r_bom_record_comp.component_yield, 1,
'Create',
r_bom_record_comp.included_in_cost_rollup,
ln_lookup_code,
r_bom_record_comp.supply_subinventory
);

ln_successful_rec := ln_successful_rec + 1;
/*

================================================================================

--Update xxoic.xxoic_cv_bom_stg staging table using error handling package

================================================================================

*/
xxoic_error_handling_pkg.xxoic_print_output
(ln_record_no_comp,
'Data loaded successfully',
'In bom_inventory_comps_interface table',
lv_trans_detail3,
lv_trans_detail4,
'xxoic.xxoic_cv_bom_stg',
SYSDATE
);
EXCEPTION
WHEN OTHERS
THEN
lv_flag := 'N';
lv_error_msg :=
'Unble to insert records into interface table ';
lv_error_code := SQLCODE;
lv_trans_detail1 :=
'Unble to insert records into interface table ';
-- xxoic_error_handling_pkg.xxoic_print_log

-- (ln_record_no_comp,

-- lv_error_code,

-- lv_error_msg,

-- lv_trans_detail1,

-- lv_trans_detail2,

-- lv_trans_detail3,

-- lv_trans_detail4,

-- 'xxoic.xxoic_cv_bom_stg',

-- SYSDATE

-- );
DBMS_OUTPUT.put_line
( 'Unble to insert records into interface table bom_inventory_comps_interface with record_no.'
|| ln_record_no_comp
|| SQLERRM
);
fnd_file.put_line
(fnd_file.LOG,
'Unble to insert records into interface table bom_inventory_comps_interface'
);
END;
END IF;

IF lv_flag = 'N'
THEN
ln_failed_rec := ln_failed_rec + 1;
END IF;
END LOOP;
END;
END IF;
END LOOP;

DBMS_OUTPUT.put_line ( 'Number of successful records is '
|| ln_successful_rec
|| ' and failed records is '
|| ln_failed_rec
);
fnd_file.put_line (fnd_file.LOG,
'Number of successful records is '
|| ln_successful_rec
|| ' and failed records is '
|| ln_failed_rec
);
END xxoic_cv_bom_int;
END xxoic_cv_bom_validate_pkg;
*********************************************************************************************************

/* Once run the above program..pls check the data in to the below table*/

SELECT * FROM bom_inventory_comps_interface

select * from bom_bill_of_mtls_interface

After run the above programs pls run the Standard program which is oracle provided
Program Name is : Bill and Routing Interface(Responsibility is Bills of Material Super User)

After run the above program..check the view Log..
If its any error ....

error flag is 3
Then update the Below script

update bom_inventory_comps_interface set process_flag=1 where organization_id=751
and process_flag=3

update bom_bill_of_mtls_interface set process_flag=1 where organization_id=751
and process_flag=3

Once updated above script RE-Run the Program Name is : Bill and Routing Interface(Responsibility is Bills of Material Super User)

Fa_interface

CREATE TABLE XX_ASSETMARVELL_TX_CONV
(
SNO NUMBER,
ASSET_NO VARCHAR2(2000 BYTE),
DATE_CAPITAL DATE,
ORIGINAL_COST VARCHAR2(200 BYTE),
ACC_DEP VARCHAR2(200 BYTE),
YTD_DEP VARCHAR2(200 BYTE),
NET_BOOK_VALUE VARCHAR2(200 BYTE),
SALVAGE_VALUE VARCHAR2(200 BYTE),
DEP_METHOD VARCHAR2(200 BYTE),
YEARS_OF_LIFE VARCHAR2(200 BYTE),
PRORATE_CONVENTION VARCHAR2(200 BYTE),
FA_BOOKS VARCHAR2(15 BYTE),
PROCESS_FLAG VARCHAR2(1 BYTE),
ERROR_MESSAGE VARCHAR2(4000 BYTE),
ATTRIBUTE1 VARCHAR2(200 BYTE),
ATTRIBUTE2 VARCHAR2(200 BYTE)
)
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON XX_ASSETMARVELL_TX_CONV TO APPS WITH GRANT OPTION;
/
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON XX_ASSETMARVELL_TX_CONV TO PUBLIC;
/
CREATE SYNONYM APPS.XX_ASSETMARVELL_TX_CONV FOR MVL_LNT.XX_ASSETMARVELL_TX_CONV;
/
CREATE PUBLIC SYNONYM XX_ASSETMARVELL_TX_CONV FOR MVL_LNT.XX_ASSETMARVELL_TX_CONV;
/
***************************
Step-2
------

options (skip=2)
LOAD DATA
INFILE "/bsa1/prod/prodappl/xxgt/11.5.0/bin/Asset_Marvell_TX_CA_ACE.txt"
INFILE "/bsa1/prod/prodappl/xxgt/11.5.0/bin/Asset_Marvell_TX_CA_AMT.txt"
INFILE "/bsa1/prod/prodappl/xxgt/11.5.0/bin/Asset_Marvell_TX_CA_REG.txt"
INFILE "/bsa1/prod/prodappl/xxgt/11.5.0/bin/Asset_Marvell_TX_FED_ACE.txt"
INFILE "/bsa1/prod/prodappl/xxgt/11.5.0/bin/Asset_Marvell_TX_FED_AMT.txt"
INFILE "/bsa1/prod/prodappl/xxgt/11.5.0/bin/Asset_Marvell_TX_FED_REG.txt"
INFILE "/bsa1/prod/prodappl/xxgt/11.5.0/bin/Asset_Marvell_TX_OTH_STT.txt"
APPEND
INTO TABLE MVL_LNT.XX_ASSETMARVELL_TX_CONV
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
( SNO ,
ASSET_NO ,
DATE_CAPITAL "TO_DATE(:DATE_CAPITAL)" ,
ORIGINAL_COST "TO_NUMBER(:ORIGINAL_COST,'999999999.99')" ,
ACC_DEP"TO_NUMBER(:ACC_DEP,'999999999.99')" ,
YTD_DEP ,
NET_BOOK_VALUE ,
SALVAGE_VALUE ,
DEP_METHOD ,
YEARS_OF_LIFE ,
PRORATE_CONVENTION,
FA_BOOKS ,
PROCESS_FLAG ,
ERROR_MESSAGE ,
ATTRIBUTE1 ,
ATTRIBUTE2
)
*************************************
Step-3
--------

CONN &username/&password

CREATE OR REPLACE package MVL_LNT.XX_ASSETMARVELL_TX_CONV_PKG
as
procedure ASSETMARVELL_TX_CONV ( errbuf out varchar2,
retcode out varchar2,
P_FA_BOOKS VARCHAR2);
END XX_ASSETMARVELL_TX_CONV_PKG;
/

CREATE OR REPLACE package body MVL_LNT.XX_ASSETMARVELL_TX_CONV_PKG
as
procedure ASSETMARVELL_TX_CONV (errbuf out varchar2,
retcode out varchar2,
P_FA_BOOKS VARCHAR2)
is
cursor XX_ASSETMARVELL_TX_CONV_CURSOR (P_FA_BOOKS VARCHAR2)
is
select * from XX_ASSETMARVELL_TX_CONV WHERE substr(FA_BOOKS,1,14)=P_FA_BOOKS
and asset_no is not null ;
BEGIN
----------------------------------------------
DELETE from XX_ASSETMARVELL_TX_CONV I WHERE substr(I.FA_BOOKS,1,14)=P_FA_BOOKS
and I.asset_no is not null
and rowid>(select min(rowid)from XX_ASSETMARVELL_TX_CONV A
WHERE A.ASSET_NO=I.ASSET_NO AND A.FA_BOOKS=I.FA_BOOKS);
----------------------------------------
FOR I in XX_ASSETMARVELL_TX_CONV_CURSOR(P_FA_BOOKS)
LOOP

insert into apps.fa_tax_interface(
ASSET_NUMBER
,COST
,YTD_DEPRN
,SALVAGE_VALUE
,posting_status
,LIFE_IN_MONTHS
,DEPRN_METHOD_CODE
,BOOK_TYPE_CODE
,DATE_PLACED_IN_SERVICE
,PRORATE_CONVENTION_CODE
,ADJUSTED_RATE
,AMORTIZE_NBV_FLAG
,DEPRN_RESERVE
)
VALUES( I.ASSET_NO
,I.ORIGINAL_COST
,nvl(decode(instr(I.YTD_DEP,'-'),0,to_number(I.YTD_DEP)),0)
,nvl(decode(instr(I.SALVAGE_VALUE,'-'),0,to_number(I.SALVAGE_VALUE)),0)
,'POST'
,I.YEARS_OF_LIFE*12
,SUBSTR(I.DEP_METHOD,1,12)
,SUBSTR(I.FA_BOOKS,1,14)
,I.DATE_CAPITAL
,UPPER(I.PRORATE_CONVENTION)
,I.ACC_DEP
,'Y'
,I.ACC_DEP);
-- fnd_file.put_line(FND_FILE.log,I.ASSET_NO);

begin
dbms_output.put_line ('ASSET NO' || I.ASSET_NO ||' - '|| I.FA_BOOKS);
fnd_file.put_line (FND_FILE.log,'ASSET NO' || I.ASSET_NO ||' - '|| I.FA_BOOKS);
EXCEPTION
when others then
dbms_output.put_line('Error IN TABLE' ||sqlerrm);
end;
END LOOP;
COMMIT;
End ASSETMARVELL_TX_CONV;
END XX_ASSETMARVELL_TX_CONV_PKG;
/
CREATE SYNONYM XX_ASSETMARVELL_TX_CONV_PKG FOR MVL_LNT.XX_ASSETMARVELL_TX_CONV_PKG;
GRANT SYNONYM XX_ASSETMARVELL_TX_CONV_PKG FOR MVL_LNT.XX_ASSETMARVELL_TX_CONV_PKG
*********************

Miscellaneous

set serveroutput on ;
--EXEC dbms_output.enable(1000000);
set feedback off;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
declare
m_line varchar2(150);
m_transfer_id varchar2(2):='01';
m_part_no varchar2(15);
m_plant_code varchar2(2):='LT';
m_currency varchar2(2):='YN';
m_po_price varchar2(7);
m_decimal varchar2(2);
m_previous_due_date varchar2(6):='000000';
m_shipment_date date;
m_previous_issue_date varchar2(6):='000000';
m_approved_date varchar2(6);
m_revision_level varchar2(2):='00';
m_emergency_code char(1):='M';
m_reason_code char(1):=' ';
m_cancel_code char(1):=' ';
m_previous_quantity varchar2(7):='0000000';
m_shipment_via_code char(1):=' ';
m_status_change_code char(1):=' ';
m_status_change_code2 char(11):=' ';
m_fax_ref varchar2(10);
m_model char(10):=' ';
m_description char(15);
m_creation_date varchar2(6);
cursor corder1 is
select a.po_header_id,B.po_line_id,B.item_id, rpad(A.segment1,10) po_no,
lpad(to_char(B.quantity),7,0) po_qty, A.currency_code currency,
B.unit_price,B.vendor_product_num,
trunc(A.APPROVED_DATE) approved_date, a.attribute8 emergency_Ref,
a.CREATION_DATE Creation_date ,
a.attribute7 acceptance_fax_ref, freight_terms_lookup_code
from po_headers_all a, po_lines_all b
where A.segment1 = '&1'
and a.po_header_id=b.po_header_id;

begin
for corder in corder1 loop
m_line:='01';
m_po_price:=lpad(trunc(corder.unit_price),7,0);
m_decimal:=rpad(corder.unit_price*100-trunc(corder.unit_price)*100,2,0);
select rpad(segment1,15) into m_part_no
from mtl_system_items where inventory_item_id=corder.item_id and rownum=1;
select NEED_BY_DATE INTO m_shipment_date
from po_line_locations_all where po_line_id=corder.po_line_id
AND ROWNUM=1;
if corder.emergency_ref is null then
m_emergency_code:='M';
else
m_emergency_code:=substr(corder.emergency_ref,1,1);
end if;
select rpad(nvl(description,' '),15) INTO m_description
from mtl_system_items where
inventory_item_id=corder.item_id and rownum=1;
if substr(corder.freight_terms_lookup_code,1,1)='A' then
m_shipment_via_code:='A';
else
m_shipment_via_code:=' ';
end if;
if m_emergency_code='M' then
m_approved_date:='000000';
m_fax_ref:=' ';
else
m_approved_date:=rpad(nvl(to_char(corder.approved_date,'yymmdd'),' '),6);
m_fax_ref:=rpad(nvl(corder.acceptance_fax_ref,' '),10);
end if;
m_creation_date := rpad(nvl(to_char(corder.creation_date ,'yymmdd'),' '),6);
fnd_file.put_line(FND_FILE.log,'Creation date : ' || m_creation_date);
m_line:=m_line||m_part_no||m_plant_code||corder.po_no||corder.po_qty||m_currency||
m_po_price||m_decimal||rpad(nvl(corder.vendor_product_num,' '),7)||
m_previous_due_date||rpad(nvl(to_char(m_shipment_date,'yymmdd'),' '),6)||
m_previous_issue_date||m_creation_date||
m_revision_level||m_emergency_code||
m_reason_code||m_cancel_code||m_previous_quantity||
m_shipment_via_code||m_status_change_code||
m_status_change_code2||m_fax_ref||
m_model||m_description ;
--dbms_output.put_line(to_char(length(m_line)));
--dbms_output.put_line(m_line);
fnd_file.put_line(FND_FILE.output, m_line);
end loop;
end;
/

UTL_FILE used in apps

CREATE OR REPLACE PROCEDURE XXPOFLAT (
P_ERRBUF OUT VARCHAR2
,P_RETCODE OUT NUMBER
,P_PO_HEADER_ID IN NUMBER
,P_ORGID IN NUMBER) AS
CURSOR PO_data IS
SELECT '01'"TRANSFER_ID",pha.ORG_ID,msib.ORGANIZATION_ID
,MSIB.SEGMENT1"PART_NO"
,'LT'"PLANT_CODE"
,PHA.SEGMENT1"PURCHASE_ORDER_NUMBER"
,PLA.QUANTITY"QUANTITY"
,'YN'"CURRENCY"
,ROUND(PLA.UNIT_PRICE)"UNIT_PRICE"
,POV.VENDOR_NAME
,DECODE(PLLA.CANCEL_FLAG,'N',NULL,PLLA.NEED_BY_DATE)"PREVIOUS_DUE_DATE"
,DECODE(PLLA.CANCEL_FLAG,'N',NULL,PLLA.CREATION_DATE)"PREVIOUS_ISSUE_DATE"
,TO_CHAR(PLLA.APPROVED_DATE,'DD-MON-YYYY')"APPROVED_DATE"
-- ,(CASE WHEN PHA.CANCEL_FLAG='N'THEN PHA.REVISION_NUM ELSE 01 END)"REVISION_NUM"
,DECODE(PHA.CANCEL_FLAG,'N',PHA.REVISION_NUM,'01')"REVISION_LEVEL"
,PHA.ATTRIBUTE8"CLASSIFICATION_CODE"
,' ' Reason_code
,' ' Cancel_code
,DECODE(PLLA.CANCEL_FLAG,'N',NULL,PLLA.QUANTITY_CANCELLED)"QUANTITY_CANCELLED"
,' ' Fax_Reference
,MSIB.DESCRIPTION"PART_NAME"
FROM
PO_HEADERS_ALL PHA
,PO_LINES_ALL PLA
,MTL_SYSTEM_ITEMS_B MSIB
,PO_VENDORS POV
,PO_LINE_LOCATIONS_ALL PLLA
WHERE PHA.PO_HEADER_ID = P_PO_HEADER_ID
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID = P_ORGID
AND PHA.VENDOR_ID = POV.VENDOR_ID
AND PHA.PO_HEADER_ID = PLLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PLLA.SHIP_TO_ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND PHA.APPROVED_FLAG ='Y' ;

v_file UTL_FILE.FILE_TYPE;

BEGIN
v_file := UTL_FILE.FOPEN(location => '/usr/tmp',
filename => 'PO_FLAT.txt',
open_mode => 'w',
max_linesize => 32767);
FOR cur_rec IN PO_data LOOP
UTL_FILE.PUT_LINE(v_file,
cur_rec.TRANSFER_ID || chr(9) ||
cur_rec.PART_NO || chr(9) ||
cur_rec.PLANT_CODE || chr(9) ||
cur_rec.PURCHASE_ORDER_NUMBER || chr(9) ||
cur_rec.QUANTITY || chr(9) ||
cur_rec.CURRENCY || chr(9) ||
cur_rec.UNIT_PRICE || chr(9) ||
cur_rec.VENDOR_NAME || chr(9) ||
cur_rec.PREVIOUS_DUE_DATE || chr(9) ||
cur_rec.PREVIOUS_ISSUE_DATE || chr(9) ||
cur_rec.APPROVED_DATE || chr(9) ||
cur_rec.REVISION_LEVEL || chr(9) ||
cur_rec.CLASSIFICATION_CODE || chr(9) ||
cur_rec.REASON_CODE || chr(9) ||
cur_rec.CANCEL_CODE || chr(9) ||
cur_rec.QUANTITY_CANCELLED || chr(9) ||
cur_rec.FAX_REFERENCE || chr(9) ||
cur_rec.PART_NAME);
END LOOP;
UTL_FILE.FCLOSE(v_file);

EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20000, 'File location is invalid.');

WHEN UTL_FILE.INVALID_MODE THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20001, 'The open_mode parameter in FOPEN is invalid.');

WHEN UTL_FILE.INVALID_FILEHANDLE THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20002, 'File handle is invalid.');

WHEN UTL_FILE.INVALID_OPERATION THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20003, 'File could not be opened or operated on as requested.');

WHEN UTL_FILE.READ_ERROR THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20004, 'Operating system error occurred during the read operation.');

WHEN UTL_FILE.WRITE_ERROR THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20005, 'Operating system error occurred during the write operation.');

WHEN UTL_FILE.INTERNAL_ERROR THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20006, 'Unspecified PL/SQL error.');

WHEN UTL_FILE.CHARSETMISMATCH THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20007, 'A file is opened using FOPEN_NCHAR, but later I/O ' ||
'operations use nonchar functions such as PUTF or GET_LINE.');

WHEN UTL_FILE.FILE_OPEN THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20008, 'The requested operation failed because the file is open.');

WHEN UTL_FILE.INVALID_MAXLINESIZE THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20009, 'The MAX_LINESIZE value for FOPEN() is invalid; it should ' ||
'be within the range 1 to 32767.');

WHEN UTL_FILE.INVALID_FILENAME THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20010, 'The filename parameter is invalid.');

WHEN UTL_FILE.ACCESS_DENIED THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20011, 'Permission to access to the file location is denied.');

WHEN UTL_FILE.INVALID_OFFSET THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20012, 'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; ' ||
'it should be greater than 0 and less than the total ' ||
'number of bytes in the file.');

WHEN UTL_FILE.DELETE_FAILED THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20013, 'The requested file delete operation failed.');

WHEN UTL_FILE.RENAME_FAILED THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20014, 'The requested file rename operation failed.');

WHEN OTHERS THEN
UTL_FILE.FCLOSE(v_file);
RAISE;
END;



SELECT * FROM USER_ERRORS WHERE NAME='XXPOFLAT'

DECLARE
ERR NUMBER;
ERRRETCODE VARCHAR2(1000);
BEGIN
XXPOFLAT(ERR,ERRRETCODE,6,84);
END;


SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME='PO_CSV'


declare
V_chr varchar2(3);
ctr number;
begin
for ctr in 1..255 loop
select chr(ctr) into V_chr from dual;
-- dbms_output.put_line(V_chr);
dbms_output.put_line('Chr value '||ctr||' = '||V_chr||' ');
end loop;
end;


declare
V_chr varchar2(3);
ctr number;
begin
select chr(9) into V_chr from dual;
-- dbms_output.put_line(V_chr);
dbms_output.put_line('Chr value '||ctr||' = '||V_chr||' ');
end;

FA For getting YTD Depreciation..etc...

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