/*Define package Specification*/
CREATE OR REPLACE Package print_xml
As
Procedure Openxmlfile(P_Dir_Name Varchar2,
--P_Transaction_Id Mtl_Material_Transactions.Transaction_Id%Type,
P_Transaction_Date1 Date,
P_Transaction_Date2 Date,
P_Transaction_Source_Type_Name Mtl_Txn_Source_Types.Transaction_Source_Type_Name%Type);
end print_xml;
/*Define package body*/
CREATE OR REPLACE Package Body print_xml
as
v_FILENAME varchar2(30);
f_XML_FILE UTL_FILE.file_type;
/* you can generate the XML either using Transaction_Id or Transaction Source I am sing source */
Procedure Openxmlfile(P_Dir_Name Varchar2,
--P_Transaction_Id Mtl_Material_Transactions.Transaction_Id%Type,
P_Transaction_Date1 Date,
P_Transaction_Date2 Date,
P_Transaction_Source_Type_Name Mtl_Txn_Source_Types.Transaction_Source_Type_Name%Type)
is
V_Record_Data Varchar2(4000) := Null;
v_TRANSACTION_ID MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID%TYPE:=NULL;
V_Transaction_Source_Type_Name Mtl_Txn_Source_Types.Transaction_Source_Type_Name%Type:=Null;
V_Transaction_Date Date:=Null;
V_Transaction_Name Varchar2(1000):=Null;
v_ATTRIBUTE1 varchar2(1000):= null;
v_INVENTORY_ITEM_ID mtl_system_items_b.INVENTORY_ITEM_ID%type:=NULL;
V_Attribute3 Mtl_System_Items_B.Description%Type:= Null;
V_SUBINVENTORY_CODE MTL_MATERIAL_TRANSACTIONS.SUBINVENTORY_CODE%TYPE:=NULL;
V_TRANSACTION_UOM MTL_MATERIAL_TRANSACTIONS.TRANSACTION_UOM%TYPE:=NULL;
V_REVISION MTL_MATERIAL_TRANSACTIONS.REVISION%TYPE:=NULL;
V_Transaction_Quantity Mtl_Material_Transactions.Transaction_Quantity%Type:= Null;
V_Currency_Code Mtl_Material_Transactions.Currency_Code%Type:=Null;
V_SEGMENT1 varchar2(1000):=NULL;
/*define the cursor to select the colmns you want to see in xml format only*/
cursor xml_cursor
is
SELECT
Mt.Transaction_Id
,Mts.Transaction_Source_Type_Name
,mt.Transaction_Date
,Gcc.Segment1||'.' ||Gcc.Segment2||'.'||Gcc.Segment3||'.'||Gcc.Segment4||'.'||Gcc.Segment5||'.'||Gcc.Segment6||'.'||Gcc.Segment7 Transaction_Name
,Msib.Segment1||'-'||Msib.Segment2||'-'||Msib.Segment3 Item
,MSIB.INVENTORY_ITEM_ID
,Msib.Description Description
,MT.SUBINVENTORY_CODE
,MT.TRANSACTION_UOM
,MT.REVISION
,MT.TRANSACTION_QUANTITY
,Mt.Currency_Code
,ML.SEGMENT1||'.'||PPMV.PROJECT_NAME||'.'||PTMV.TASK_NUMBER Locator
FROM
MTL_MATERIAL_TRANSACTIONS MT,
MTL_SYSTEM_ITEMS_B MSIB,
MTL_TRANSACTION_TYPES MTT,
MTL_TXN_SOURCE_TYPES MTS ,
MTL_ITEM_LOCATIONS ML,
PJM_PROJECTS_MTLL_V PPMV,
PJM_TASKS_MTLL_V PTMV
,gl_code_combinations gcc
WHERE MT.TRANSACTION_TYPE_ID=MTT.TRANSACTION_TYPE_ID
AND MT.INVENTORY_ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND MT.ORGANIZATION_ID=MSIB.ORGANIZATION_ID
AND MT.TRANSACTION_SOURCE_TYPE_ID=MTS.TRANSACTION_SOURCE_TYPE_ID
AND MT.LOCATOR_ID=ML.INVENTORY_LOCATION_ID
AND ML.segment19=PPMV.PROJECT_ID
AND ML.segment20=PTMV.TASK_ID
And Mt.Distribution_Account_Id=Gcc.Code_Combination_Id
AND MTS.TRANSACTION_SOURCE_TYPE_NAME=P_Transaction_Source_Type_Name
And To_Char(Mt.Transaction_Date,'DD-MON-YYYY') >=P_Transaction_Date1
And To_Char(Mt.Transaction_Date,'DD-MON-YYYY') <=P_Transaction_Date2 ;
--And Mt.Transaction_Id=P_Transaction_Id;
BEGIN
v_FILENAME := 'PRINT_XML'||TO_CHAR(SYSDATE, 'DDMMYYYYHH24MI') || '.xml'; /*Give the XML file name */
f_XML_FILE := UTL_FILE.fopen(p_dir_name, v_FILENAME, 'W');
V_Record_Data := '<?xml version="1.0" encoding="iso-8859-1"?>';
UTL_FILE.put_line(f_XML_FILE, v_RECORD_DATA);
UTL_FILE.put_line(f_XML_FILE, '<ITEM_DATA>');
open XML_cursor;
loop
fetch XML_cursor
into
V_Transaction_Id
,V_Transaction_Source_Type_Name
,V_TRANSACTION_DATE
,V_Transaction_Name
, v_ATTRIBUTE1
, V_Inventory_Item_Id
,V_Attribute3
,V_SUBINVENTORY_CODE
,V_TRANSACTION_UOM
,V_REVISION
,V_Transaction_Quantity
,V_Currency_Code
,V_SEGMENT1 ;
EXIT WHEN XML_cursor%NOTFOUND;
UTL_FILE.put_line(f_XML_FILE, ' <ITEM_DETAILS>');
UTL_FILE.put_line(f_XML_FILE,
'<TRANSACTION_ID>'||V_Transaction_Id||'</TRANSACTION_ID>');
UTL_FILE.put_line(f_XML_FILE,
'<SOURCE>'||V_Transaction_Source_Type_Name||'</SOURCE>');
UTL_FILE.put_line(f_XML_FILE,
'<TRANSACTION_DATE>'||V_Transaction_Date||'</TRANSACTION_DATE>');
UTL_FILE.put_line(f_XML_FILE,
'<TRANSACTION_NAME>'||V_Transaction_Name||'</TRANSACTION_NAME>');
Utl_File.Put_Line(F_Xml_File,
'<ITEM>'||V_Attribute1 || '</ITEM>');
Utl_File.Put_Line(F_Xml_File,
'<ITEM_ID>'||v_INVENTORY_ITEM_ID|| '</ITEM_ID>');
UTL_FILE.put_line(f_XML_FILE,
'<DESCRIPTION>'||V_Attribute3||'</DESCRIPTION>');
UTL_FILE.put_line(f_XML_FILE,
'<SUBINVENTORY_CODE>'||V_SUBINVENTORY_CODE||'</SUBINVENTORY_CODE>');
UTL_FILE.put_line(f_XML_FILE,
'<UOM>'||V_TRANSACTION_UOM||'</UOM>');
UTL_FILE.put_line(f_XML_FILE,
'<REVISION>'||V_REVISION|| '</REVISION>');
UTL_FILE.put_line(f_XML_FILE,
'<QUANTITY>'||V_TRANSACTION_QUANTITY||'</QUANTITY>');
UTL_FILE.put_line(f_XML_FILE,
'<CURRENCY_CODE>'||V_Currency_Code||'</CURRENCY_CODE>');
UTL_FILE.put_line(f_XML_FILE,
'<LOCATOR>'||V_SEGMENT1||'</LOCATOR>');
UTL_FILE.put_line(f_XML_FILE, ' </ITEM_DETAILS>');
end loop;
UTL_FILE.put_line(f_XML_FILE, '</ITEM_DATA>');
Utl_File.Fclose(f_XML_FILE);
close XML_cursor;
EXCEPTION
WHEN UTL_FILE.INTERNAL_ERROR THEN
raise_application_error(-20500,
'Cannot open file :' || v_FILENAME ||
', internal error; code:' || sqlcode ||
',message:' || sqlerrm);
WHEN UTL_FILE.INVALID_OPERATION THEN
raise_application_error(-20501,
'Cannot open file :' || v_FILENAME ||
', invalid operation; code:' || sqlcode ||
',message:' || sqlerrm);
WHEN UTL_FILE.INVALID_PATH THEN
raise_application_error(-20502,
'Cannot open file :' || v_FILENAME ||
', invalid path; code:' || sqlcode ||
',message:' || sqlerrm);
WHEN UTL_FILE.WRITE_ERROR THEN
raise_application_error(-20503,
'Cannot write to file :' || v_FILENAME ||
', write error; code:' || sqlcode ||
',message:' || sqlerrm);
end;
end print_xml;
/*Calling Method*/
Begin
PRINT_XML.OpenXmlFile('path','date1(from_date)','date2(to_date)','Source');
End;
No comments:
Post a Comment