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;