Sunday, 31 December 2017

Part-2 : Oracle XML Publisher Reports

Oracle XML Publisher Reports Part-2

 
How to Display Star dynamically based on your Data.
 

 
This example demonstrates how to set up a template that will generate a star-rating based on data from an incoming XML file. Assume the following incoming XML data:

This is the sample xml Data.


 

Notice there is a USER_RATING element for each CD.Using this data element and the shape manipulation commands,we can create visual representation of the ratings so that the reader can compare them at a glance. A template to achieve this is shown in the following figure:





This is the Below code you have to use as below.

Oracle XML Publisher Reports


Sample Output as Below:-

Oracle XML Publisher Reports
 

How to Insert Page Breaks in Report Output:-


Page Breaks

To create a page break after the occurrence of a specific element use the "split-by-page-break"alias. This will cause the report output to insert a hard page break between every instance of a specific element. To insert a page break between each occurrence of a group, insert the "split-by-page-break" form field within the group immediately before the<?end for-each?>tag that closes the group. In the Help Text of this form field enter the syntax: <?split-by-page-break:?>.

How to Insert Page Breaks in XML Report Output
 

In the template samples how in the following figure, the field called Page Break contains the split-by-page-break syntax:

 
 
Initial Page Number:-

 Some reports require that the initial page number be set at a specified number. For example, monthly reports may be required to continue numbering from month to month.

XML Publisher allows you to set the page number in the template to support this requirement.

Use the following syntax in your template to set the initial  page number:
 <?initial-page-number:pagenumber?>
where pagenumber is the XML element or parameter that holds the numeric value. Example 1 - Set page number from XML data element

If your XML data contains an element to carry the initial page number. For example:

Creating an RTF Template 2-45

<REPORT>
<PAGESTART>200<\PAGESTART>
 .... </REPORT>
Enter the following in your template:
<?initial-page-number:PAGESTART?>

Your initial page number will be the value of the PAGESTART element, which in this case is 200.

Example 2 - Set page number by passing a parameter value

If you define a parameter called PAGESTART, you can pass the initial value by calling the parameter. Enter the following in your template: <?initial-page-number:$PAGESTART?> Note: You must first declare the parameter in your template. See Defining Parameters in Your Template, page2-79.

 

Part-1 : Oracle XML Publisher Reports: Advanced XML publisher Coding in the Template.

Part-1 : Oracle XML Publisher Reports: Advanced XML publisher Coding in the Template.

 
Oracle XML Publishers Reports

XML Publisher provides lots of Flexibility in Oracle Reporting System. If you are not using xml publisher and you are doing your report development completely in Oracle RDF then you can design maximum 1 layout with 1 RDF report but if your business requirement to use different layout for different BU’s then you cannot stick to Oracle RDF layouts because you must develop different RDF for each BU’s but if you will use XML Publisher for Layout designing then you don’t need to develop different RDF for each BU’s. You can use one RDF but develop different layouts in XML publisher for each BU’s so you can think that how much it gives Flexibility.

Even XML report supports multi languages that is also an important feature of XML reports beside this we all know that XML reports are very impressive and their Layout qualities are too too good as compared to RDF paper layouts.

We all know that, how to build an basic xml reports but in this post I will share some advanced XML publisher techniques which can help you to take care some complex scenario in XML reports designing.

How to use If statement in XML reports.

Some time we have requirement in Layouts that, we want to display some xml tags or values based on some report column values or some other xml tag value. So we can use if command in xml RTF layouts to display data based on conditions like below.

<?if:XML_TAG_NAME =’COMPARISON VALUE’?>   <?end if?>

As an example, I want to his or her name based on gender value of xml tag then I will use if statement as below.

<?if:GENEDER_TAG =’MALE’?> His<?end if?> <?if:GENEDER_TAG =’FEMALE’?> Her<?end if?>

This is how we can use if statement.

How to Create Group as below

<?for-each:G_VENDOR_NAME?>  <?end for-each?>

How to Show Multiple or Complex Headers and Footers :-

If your template requires multiple headers and footers, create them by using XML Publisher tags to define the body area of your report. You may also want to use this method if your header and footer contain complex objects that you wish to place inform fields. When you define the body area, the elements occurring before the beginning of the body area will compose the header. The elements occurring after the body area will compose the footer. Use the following tags to enclose the body area of your report:

 <?start:body?>  <?end body?>

Use the tags either directly in the template, or inform fields.

Insert <?start:body?> before the Suppliers group tag: <?for-each:G_VENDOR_ NAME?>

2. Insert <?end body?> after the Suppliers group closing tag: <?end for-each?>


The following figure shows the Payables Invoice Register with the start body/end body tags inserted:





How to insert chat or create chat based on your report output in xml publisher rtf output.



This example will show how to insert a chart into your template to display it as a vertical bar chart as shown in the following figure:
Oracle XML Publisher Reports


 


To Display chart in your report output , you have to follow below steps.

Inserting the Dummy Image

The first step is to add a dummy image to the template in the position you want the chart to appear. The image size will define how big the chart image will be in the final document. Important: You must insert the dummy image as a "Picture" and not any other kind of object.

The following figure shows an example of a dummy image:



Right-click the image to open the Format Picture palette and select the Web tab. Use the Alternative text entry box to enter the code to define the chart characteristics and data definition for the chart.

The following graphic shows an example of the XML Publisher code in the Format Picture Alternative text box:



chart:
<Graph graphType = "BAR_VERT_CLUST">
<Title text="Company Sales 2004" visible="true" horizontalAlignme nt="CENTER"/>
<Y1Title text="Sales in Thousands" visible="true"/> <O1Title text="Division" visible="true"/>
<LocalGridData colCount="{count(//division)}" rowCount="1">
<RowLabels>
<Label>Total Sales $1000s</Label>
</RowLabels>
<ColLabels>
<xsl:for-each select="//division">
<Label>
<xsl:value-of select="name"/>
</Label>
</xsl:for-each>
</ColLabels>
<DataValues>
<RowData>
<xsl:for-each select="//division">
<Cell>
<xsl:value-of select="totalsales"/>
</Cell>
</xsl:for-each>
</RowData>
</DataValues>
</LocalGridData>
</Graph>


How to Display Pie chart in XML Publisher Reports. :-


Oracle XML Publisher Reports


Put the Below code in the Web tab of the Sample picture in Format Options.
chart:
<Graph graphType="PIE">
<Title text="Company Sales 2004" visible="true" horizontalAlignment="CENTER"/>
<LocalGridData rowCount="{count(//division)}" colCount="1">
<RowLabels>
<xsl:for-each select="//division">
<Label>
<xsl:value-of select="name"/>
</Label>
</xsl:for-each>
</RowLabels>
<DataValues>
<xsl:for-each select="//division">
<RowData>
<Cell>
<xsl:value-of select="totalsales"/>
</Cell>
</RowData>
</xsl:for-each>
</DataValues>
</LocalGridData>
</Graph>

Friday, 29 December 2017

Oracle Business Event concept and how we can create Business event and perform custom tasks through Workflow Business event

Oracle Business Event concept and how we can create Business event and perform custom tasks through Workflow Business event


In this Post, I am going to share the use of Oracle Standard Business Event functionality in Oracle Apps. This is really a very useful feature shared by oracle where you can sync some custom objects or Custom activities with the Standard functionality.

 

In this post, I am sharing an example of Business event, where I am going to create business event for one Specific concurrent program “Format Payment Instructions with Text Output”. I will give trigger to my customer package by business event when this concurrent program will complete its execution.

 

Step 1. I have enable checkbox program completed to trigger business event when this program will complete.

 

Step2:- Go to Workflow Administrator Web Applications responsibility è Business Event

Step3:- Search fnd%req%comp% as below this below business event will be come. Then we need to enable this Business event.
Step 4: -Click on Subscription button and then Create Subscription button.

 
 

Step-5

Give values as below and click next

Please note we are going to custom procedure so you need to select action type custom.

 

Step-6:- Enter the name of PLSQL procedure and give owner name and owner tag as Application name and its short code. Click apply.

 
 

Now event has been created and subscription is also created.

 

In this above procedure, I am submitting one custom concurrent request automatically when user will submit program “Format Payment Instructions with Text Output” and this program will complete its execution.

 

We can create Business events in many other scenarios like customer creation, when order will create. We can create business event and perform our task in its subscription.

 

To enable business event functionality this below profile should be yes.

'Concurrent: Business Intelligence Integration Enable' to Yes.

PLSQL script to Register Oracle Views from One Instance to Another automatically


PLSQL SCRIPT TO REGISTER ORACLE VIEWS FROM ONE INSTANCE TO ANOTHER INSTANCE AUTOMATICALLY IN ORACLE APPS





In this post, I will share you one method in which you can extract the Oracle Views registration details in few seconds using this below script.



As an example, you are working on some project in development instance and you have created around 40 views for this and now you want to register these 40 views in other instance and it’s a time-consuming activity.



By this below query, you just need to execute this below script in test instance with proper values as per your requirement and that will create one .sql file in you server will all the 40 Views registration details.



Copy this file from server and put this file in your system and then open sql plus with targeted instance where you want to register these Views and execute this file in sql plus for that instance and your Views will be register there in few seconds.





For Views



declare

a1 LONG;

b LONG;

C1 LONG;

TEST_FILE Utl_File.File_Type;

FILE_NAME varchar2(400):='VIEW_NAME _REGISTRATION_FILE';

BEGIN

TEST_FILE := Utl_File.FOPEN('ODPDIR',FILE_NAME||'.txt','W',32767); -- ODPDIR is the Directory

FOR j in ( select OBJECT_NAME from dba_objects

 where object_type='VIEW'

 AND OBJECT_NAME like 'XX_%AP%')

loop

for i IN (SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS

WHERE TABLE_NAME=j.object_name

ORDER BY COLUMN_ID)

loop

a1:=a1||i.COLUMN_NAME||',';

end loop;

C1:=NULL;

SELECT TEXT INTO C1 FROM DBA_VIEWS

WHERE VIEW_NAME=j.object_name;

b:='create or replace view '||j.object_name||'R'||'('||rtrim(a1,',')||') AS '||C1||';'||chr(10);



a1:=null;

Utl_File.FOPEN('ECX_UTL_XSLT_DIR_OBJ',FILE_NAME||'.txt','W',32767);

--Utl_File.PUT_LINE(TEST_FILE ,b);

Utl_File.PUT_LINE(FILE     => TEST_FILE,

    buffer   => b,

     autoflush => TRUE);

--UTL_FILE.NEW_LINE(TEST_FILE ,1);

B:=NULL;



--dbms_output.put_line('1');



end loop;

if Utl_File.is_open(TEST_FILE) then

Utl_File.FCLOSE(TEST_FILE);

END IF;

EXCEPTION WHEN NO_DATA_FOUND THEN

NULL;

dbms_output.put_line(sqlerrm);

WHEN OTHERS THEN

dbms_output.put_line(sqlerrm);



end;






Part -2 Oracle Fusion : What is FBDI and Oracle cloud FBDI templates. Step by Step to Load Suppliers Through FBDI.

Part -2 Oracle Fusion : What is FBDI and Oracle cloud FBDI templates. Step by Step to Load Suppliers Through FBDI.


In this post we will try to upload Suppliers through FBDI.I will show you step by step of this supplier upload process in Oracle Fusion/Cloud. Before start please go through my first post of FBDI where we have discussed in detail about FBDI and How its work in Oracle Fusion.

url:-


Oracle cloud FBDI templates.Oracle FBDI Templates Process Detail Steps


Step1- First we need to download Supplier Data Template from Oracle Repository.

Go to this path for Oracle Repository.
 

FBDI Template oracle

 
Step-2:- Go to Procurement Tab as below and Click on Release 11 under File-Based Data Import.
 





Step-3:- Then this below screen page will open.


Part -2 Oracle Fusion : What is FBDI and How its work in Oracle Fusion. Step by Step to Load Suppliers Through FBDI.

Step 4:- Under File-Based Data Imports click on Import Supplier Data Template.


Step-5:- Click on XLSM template and save the data Template in our Desktop.

Part -2 Oracle Fusion : What is FBDI and How its work in Oracle Fusion. Step by Step to Load Suppliers Through FBDI.


Step-6:- This is the Below Data Template for Import Supplier.

First Tab is the Instruction tab and Second Tab is the Data Template in which we will put data for suppliers.

Part -2 Oracle Fusion : What is FBDI and How its work in Oracle Fusion. Step by Step to Load Suppliers Through FBDI.

Oracle FBDI r13

Step-7

I am going to create TEST SUPPLIER through FBDI.

Part -2 Oracle Fusion : What is FBDI and How its work in Oracle Fusion. Step by Step to Load Suppliers Through FBDI.


Step-8:- After Putting data in Data Template and then go back to first tab and Click on Generate CSV File button as below.


This will create PozSuppliersInt.zip file in your local system.


Part -2 Oracle Fusion : What is FBDI and How its work in Oracle Fusion. Step by Step to Load Suppliers Through FBDI.

Step-9:- Login to Oracle Fusion and go to 'File Import and Export ' option as below.



Step-10:- Click on Create Upload
 
 Browse local drive to select PozSuppliersInt.zip  file

 Select Account – prc/supplier/import.
Click Save and Continue.




Step-11 :- Once the File will be Imported.

Click Navigator -> Tools -> Scheduled Process


Step-12:- Select ESS Job ‘Load Interface File for Import’
 




Step13:- Go to the Procurement Module in Fusion and Select supplier.



Step:-14 : Under Supplier ==> click on Import Supplier





Step 16:-Select the Import Process ‘Import Supplier’ and your zip file uploaded in the previous step.



Step17:- Import Suppliers Parameters



Step18:- 
Supplier Has been created as Below

 
Please share your comments and feedback about this Post.



If you want to see Other OAF Related Posts , Please visit Below URL.
https://rpforacle.blogspot.in/2013/03/oaf_10.html

If you want to see  Oracle Fusion Related Posts , Please visit Below URL.
https://rpforacle.blogspot.in/2018/01/oracle-fusion-learning-tutorial.html
If you want to Learn Oracle Workflow Builder, Please visit Below URL.
https://rpforacle.blogspot.in/2018/01/oracle-workflow-learning-tutorial-1.html
 

Part -1 Oracle Fusion : What is FBDI.FBDI process in oracle fusion.Step by Step to Load Suppliers Through FBDI.

What is FBDI and FBDI Process in Oracle fusion .Step by Step to Load Suppliers Through FBDI.


Hi Friends, In coming post I am going to share you all about Oracle Fusion or Oracle Cloud. In oracle fusion , oracle has given FBDI to import data from other Application or integrate oracle Application with other external applications. FBDI process in Oracle Fusion is used for Loading mass of Data. Here I will describe what is FBDI and How its work in Oracle Fusion. I will also share Step by Step to Load Suppliers Through FBDI.

Yes , In Fusion our way of working will be change. We will use new tools , new technologies(SOA,ADF,BIP,FBDI,OTBI). We use oracle cloud fbdi templates to load data in Cloud. We put data in these FBDI Templates and then upload these templates in Oracle Fusion.
 



FBDI means File Based Data Import

In this post I am going to Discuss about FBDI. FBDI means File Based Data Import.

In Oracle Apps we uses Interface tables and Oracle API to insert data in Oracle Base Tables or To make integration of Oracle EBS with other software we took help from Interfaces and API.


Oracle FBDI Templates. Oracle cloud erp FBDI process detail description in the Second Part of this Post.


Really in Oracle apps , Data Loading was a complex job.

Oracle Apps Data Loading Steps.

1.Prepare data Templates and get data in these templates from business for Suppliers , customer and Items.
2. Then create SQL loader programs to Load data from These Data Templates.
3.Run Custom Import programs to insert data from staging table to Interface tables.
4.Finally run import program to load data in Oracle Base tables.

But my friends in Fusion this thing will gone be easy we don't need to take lot of pain during data load. Oracle has already provided standard data templates for each module and each interface.we just need to download these data templates and put data in these data templates and upload this file to cloud and run scheduled processes to move the data in interface tables.

FBDI Template oracle


FBDI:-
  • Recommended for high volume data import
  • Loads data from flat files that are placed on a secure FTP server into interface tables
  •  Enterprise Scheduler Service (ESS) Jobs move data to Fusion Applications base tables
  • Interface tables and the processing job descriptions can be found in Oracle Enterprise Repository (OER)

FBDI Data Import Steps.

Step-1:- Download template from Oracle Enterprise Repository (OER) 
Step-2:-Prepare Data conversion file & generate .csv & .zip files 
Step-3:-Upload the zip file to Oracle Cloud 
Step-4:-Login to Fusion Applications 
Step-5:-Submit the Load Interface File for Import process 
Step-6:-Submit the product specific import job 
Step-6:-Review output file for any errors.

In my Next post FBDI Part-2 we will try to upload suppliers through FBDI in Oracle Cloud.
Thanks.



Part -1 Oracle Fusion : What is FBDI and How its work in Oracle Fusion. Step by Step to Load Suppliers Through FBDI.





Wednesday, 27 December 2017

PLSQL SCRIPT TO REGISTER PACKAGES FROM ONE INSTANCE TO ANOTHER INSTANCE AUTOMATICALLY IN ORACLE APPS

In this post , I will share you one method in which you can extract the PLSQL package body and definition of hundreds of packages in few seconds using this below script.

As an example , you are working on some project in development instance and you have created around 40 packages for this and now you want to register this 40 packages in other instance and its a time consuming activity.

By this below query , you just need to execute this below script in test instance with proper values as per your requirement and that will create one .sql file in you server will all the 40 packages registration details.

Copy this file from server and put this file in your system and then open sql plus with targeted instance where you want to register these packages and execute this file in sql plus for that instance and your packages will be register there in few seconds.

For Package Definition
declare
a1 LONG;
b LONG;
C1 LONG;
TEST_FILE Utl_File.File_Type;
FILE_NAME varchar2(400):='PACKAGE_NAME_PKG_DEF';
BEGIN
TEST_FILE := Utl_File.FOPEN('ODPDIR',FILE_NAME||'.txt','W',32767); -----ODPDIR  is the directory name in the server where this file will create
FOR j in ( select OBJECT_NAME from dba_objects
 where object_type='PACKAGE'
 AND OBJECT_NAME LIKE 'XXAP_%')
loop
for i IN (SELECT CASE WHEN LINE=1 THEN CHR(10)||'CREATE OR REPLACE '||REPLACE(TEXT,CHR(10)) ELSE REPLACE(TEXT,CHR(10)) END TEXT FROM DBA_SOURCE
WHERE TYPE='PACKAGE'
AND NAME=j.object_name
ORDER BY LINE)
loop
B:=NULL;
B:=I.TEXT;
Utl_File.PUT_LINE(FILE     => TEST_FILE,
     buffer   => b,
     autoflush => TRUE);
--dbms_output.put_line(rtrim(b));
end loop;
C1:=NULL;

a1:=null;

end loop;
if Utl_File.is_open(TEST_FILE) then
Utl_File.FCLOSE(TEST_FILE);
END IF;
EXCEPTION WHEN NO_DATA_FOUND THEN
NULL;
dbms_output.put_line(sqlerrm);
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
end;



For Package Body

declare
a1 LONG;
b LONG;
C1 LONG;
TEST_FILE Utl_File.File_Type;
FILE_NAME varchar2(400):='PACKAGE_NAME_PKG_BODY';
BEGIN
TEST_FILE := Utl_File.FOPEN('ODPDIR',FILE_NAME||'.sql','W',32767); -----ODPDIR  is the directory name in the server where this file will create
FOR j in ( select OBJECT_NAME from dba_objects
 where object_type='PACKAGE'
 AND OBJECT_NAME LIKE 'XXAP_%')
loop
for i IN (SELECT CASE WHEN LINE=1 THEN CHR(10)||'CREATE OR REPLACE '||REPLACE(TEXT,CHR(10)) ELSE REPLACE(TEXT,CHR(10)) END TEXT FROM DBA_SOURCE
WHERE TYPE='PACKAGE BODY'
AND NAME=j.object_name
ORDER BY LINE)
loop
B:=NULL;
B:=I.TEXT;
Utl_File.PUT_LINE(FILE     => TEST_FILE,
     buffer   => b,
     autoflush => TRUE);
--dbms_output.put_line(rtrim(b));
end loop;
C1:=NULL;
--b:='create or replace view '||j.object_name||'R'||'('||rtrim(a1,',')||') AS '||C1;
a1:=null;

--TEST_FILE := Utl_File.FOPEN('ECX_UTL_XSLT_DIR_OBJ',FILE_NAME||'.txt','W',32767);
--Utl_File.PUT_LINE(TEST_FILE ,b);
--Utl_File.PUT_LINE(FILE     => TEST_FILE,
   --  buffer   => b,
   --  autoflush => TRUE);
--UTL_FILE.NEW_LINE(TEST_FILE ,1);

--dbms_output.put_line('1');
end loop;
if Utl_File.is_open(TEST_FILE) then
Utl_File.FCLOSE(TEST_FILE);
END IF;
EXCEPTION WHEN NO_DATA_FOUND THEN
NULL;
dbms_output.put_line(sqlerrm);
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
end;






PLSQL QUERY TO VIEW ALL INVENTORY ITEMS FOR BOM IN ORACLE APPS

This below view will give you all the child items using in oracle bill of material (BOM) again finished good and sub-assembly.


CREATE OR REPLACE FORCE VIEW apps.xx_bom_tree (lev,
                                                     component_quantity,
                                                     inventory_item_id,
                                                     use_item,
                                                     primary_uom_code,
                                                     comp_item
                                                    )
AS
   SELECT DISTINCT LEVEL "LEV", component_quantity, msib.inventory_item_id,
                   msib2.inventory_item_id use_item, msib2.primary_uom_code,
                      msib2.segment1
                   || '.'
                   || msib2.segment2
                   || '.'
                   || msib2.segment3
                   || '.'
                   || msib2.segment4
                   || '.'
                   || msib2.segment5 AS "COMP_ITEM"
              /*bic.component_item_id,*/
              /*msib.inventory_item_id,*/
              /*msib2.inventory_item_id*/
   FROM            bom.bom_components_b bic,
                   bom.bom_structures_b bom,
                   inv.mtl_system_items_b msib,
                   inv.mtl_system_items_b msib2
             WHERE 1 = 1
               AND bic.bill_sequence_id = bom.bill_sequence_id
               AND bic.disable_date IS NULL
               AND bom.assembly_item_id = msib.inventory_item_id
               AND bom.organization_id = msib.organization_id
               AND bic.component_item_id = msib2.inventory_item_id
               AND bom.organization_id = msib2.organization_id
               AND bom.organization_id = 85         /* organization id here */
             --  AND msib2.segment5 = 'YUU
               AND bic.effectivity_date < SYSDATE
               AND bom.alternate_bom_designator IS NULL
        START WITH    msib.segment1
                   || msib.segment2
                   || msib.segment3
                   || msib.segment4
                   || msib.segment5 = 'ERRRRRR'
        /* top parent item here */
   CONNECT BY NOCYCLE PRIOR bic.component_item_id = msib.inventory_item_id
          ORDER BY LEVEL;

PL SQL Script to Read data from Flat file in Oracle Apps

PL SQL Script to Read data from Flat file in Oracle Apps


In this post I will share you the code to read data from flat files which is available in the server.

CREATE OR REPLACE PROCEDURE XXREAD_data(errbuff varchar2,errcode number)

AS

v_line VARCHAR2(2000); -- Data line read from input file

v_file UTL_FILE.FILE_TYPE; -- Data file handle

v_dir VARCHAR2(250); -- Directory containing the data file

v_filename VARCHAR2(50); -- filename

v_1st_Comma number;

v_2nd_Comma number;

v_3rd_Comma number;

v_4th_Comma number;

v_5th_Comma number;


v_suppno VARHAR2(4000);

v_suppname  VARHAR2(4000);

v_phone_no  VARHAR2(4000);

v_address VARHAR2(4000);

v_country VARHAR2(4000);


BEGIN

v_dir := '/usr/tmp01';

v_filename := 'xxtest.dat';

v_file := UTL_FILE.FOPEN(v_dir, v_filename, 'r');



LOOP

BEGIN

UTL_FILE.GET_LINE(v_file, v_line);

EXCEPTION

WHEN no_data_found THEN

exit;

END;


-- ----------------------------------------------------------

v_1st_Comma := INSTR(v_line, ',' ,1 , 1);

v_2nd_Comma := INSTR(v_line, ',' ,1 , 2);

v_3rd_Comma := INSTR(v_line, ',' ,1 , 3);

v_4th_Comma := INSTR(v_line, ',' ,1 , 4);

v_5th_Comma := INSTR(v_line, ',' ,1 , 5);

v_suppno := to_number(SUBSTR(v_line, 1, v_1st_Comma-1));

v_suppname := SUBSTR(v_line, v_1st_Comma+1, v_2nd_Comma-v_1st_Comma-1);

v_phone_no := SUBSTR(v_line, v_2nd_comma+1, v_3rd_Comma-v_2nd_Comma-1);

v_address := to_number(SUBSTR(v_line, v_3rd_comma+1, v_4th_Comma-v_3rd_Comma-1));

v_country := to_date(SUBSTR(v_line, v_4th_comma+1, v_5th_Comma-v_4th_Comma-1),'DD-MON-YYYY');



DBMS_OUTPUT.PUT_LINE(v_suppno ||' '|| v_suppname || ' ' || v_phone_no || ' ' || v_address ||' ' || v_country);

-- ------------------------------------------

-- Insert the new record into the DEPT table.

-- ------------------------------------------

INSERT INTO XX_SUPPLIER

VALUES (v_suppno ,v_suppname ,v_phone_no,v_mgr,v_address);

END LOOP;

UTL_FILE.FCLOSE(v_file);

COMMIT;

END;


PL SQL Script to Read data from Flat file in Oracle Apps

API TO CREATE AND UPDATE SALE ORDER PRICE LIST IN ORACLE APPS

API TO CREATE AND UPDATE SALE ORDER PRICE LIST IN ORACLE APPS


In this post , I will share you the PlSql code and oracle API to Create and update sale order price list in oracle apps. Using this Below script you are Able to Create and Update Sale Orders Prices List in Oracle Apps.



First you need to register PL sql stored procedure.

API TO CREATE AND UPDATE SALE ORDER PRICE LIST IN ORACLE APPS


CREATE OR REPLACE PROCEDURE APPS.xx_pricelist (
   p_list_header_id       IN   NUMBER,
   p_operand              IN   NUMBER,
   p_product_attr_value   IN   VARCHAR2,
   p_uom                  IN   VARCHAR2
)
IS
   v_return_status               VARCHAR2 (1)                         := NULL;
   v_msg_count                   NUMBER                                  := 0;
   v_msg_data                    VARCHAR2 (2000);
   v_price_list_rec              qp_price_list_pub.price_list_rec_type;
   v_price_list_val_rec          qp_price_list_pub.price_list_val_rec_type;
   v_price_list_line_tbl         qp_price_list_pub.price_list_line_tbl_type;
   v_price_list_line_val_tbl     qp_price_list_pub.price_list_line_val_tbl_type;
   v_qualifiers_tbl              qp_qualifier_rules_pub.qualifiers_tbl_type;
   v_qualifiers_val_tbl          qp_qualifier_rules_pub.qualifiers_val_tbl_type;
   v_pricing_attr_tbl            qp_price_list_pub.pricing_attr_tbl_type;
   v_pricing_attr_val_tbl        qp_price_list_pub.pricing_attr_val_tbl_type;
   ppr_price_list_rec            qp_price_list_pub.price_list_rec_type;
   ppr_price_list_val_rec        qp_price_list_pub.price_list_val_rec_type;
   ppr_price_list_line_tbl       qp_price_list_pub.price_list_line_tbl_type;
   ppr_price_list_line_val_tbl   qp_price_list_pub.price_list_line_val_tbl_type;
   ppr_qualifiers_tbl            qp_qualifier_rules_pub.qualifiers_tbl_type;
   ppr_qualifiers_val_tbl        qp_qualifier_rules_pub.qualifiers_val_tbl_type;
   ppr_pricing_attr_tbl          qp_price_list_pub.pricing_attr_tbl_type;
   ppr_pricing_attr_val_tbl      qp_price_list_pub.pricing_attr_val_tbl_type;
BEGIN
   v_price_list_rec.list_header_id := p_list_header_id;
   v_price_list_rec.list_type_code := 'PRL';
   v_price_list_rec.operation := qp_globals.g_opr_update;
   v_price_list_line_tbl (1).list_header_id := p_list_header_id;
   v_price_list_line_tbl (1).list_line_id := fnd_api.g_miss_num;
   v_price_list_line_tbl (1).list_line_type_code := 'PLL';
   v_price_list_line_tbl (1).operation := qp_globals.g_opr_create;
   v_price_list_line_tbl (1).operand := p_operand;
   v_price_list_line_tbl (1).arithmetic_operator :=  'UNIT_PRICE';
 --  v_price_list_line_tbl (1).start_date_active := '05-OCT-2011';
 --  v_price_list_line_tbl (1).start_date_active := '05-OCT-2011';
  -- v_price_list_line_tbl (1).organization_id := NULL;
   v_pricing_attr_tbl (1).pricing_attribute_id := fnd_api.g_miss_num;
   v_pricing_attr_tbl (1).list_line_id := fnd_api.g_miss_num;
  ---- v_pricing_attr_tbl (1).product_attribute_context := 'Item';
  -- v_pricing_attr_tbl (1).product_attribute := 'Item Number';
  v_pricing_attr_tbl (1).product_attribute_context := 'ITEM';
v_pricing_attr_tbl (1).product_attribute := 'PRICING_ATTRIBUTE1';
   v_pricing_attr_tbl (1).product_attr_value := p_product_attr_value;
   v_pricing_attr_tbl (1).product_uom_code := p_uom;
   v_pricing_attr_tbl (1).excluder_flag := 'N';
   v_pricing_attr_tbl (1).attribute_grouping_no := 1;
   v_pricing_attr_tbl (1).price_list_line_index := 1;
   v_pricing_attr_tbl (1).operation := qp_globals.g_opr_create;
   DBMS_OUTPUT.put_line ('Calling API to insert Price List');
   qp_price_list_pub.process_price_list
                    (p_api_version_number           => 1,
                     p_init_msg_list                => fnd_api.g_true,
                     p_return_values                => fnd_api.g_false,
                     p_commit                       => fnd_api.g_false,
                     x_return_status                => v_return_status,
                     x_msg_count                    => v_msg_count,
                     x_msg_data                     => v_msg_data,
                     p_price_list_rec               => v_price_list_rec,
                     p_price_list_line_tbl          => v_price_list_line_tbl,
                     p_pricing_attr_tbl             => v_pricing_attr_tbl,
                     x_price_list_rec               => ppr_price_list_rec,
                     x_price_list_val_rec           => ppr_price_list_val_rec,
                     x_price_list_line_tbl          => ppr_price_list_line_tbl,
                     x_qualifiers_tbl               => ppr_qualifiers_tbl,
                     x_qualifiers_val_tbl           => ppr_qualifiers_val_tbl,
                     x_pricing_attr_tbl             => ppr_pricing_attr_tbl,
                     x_pricing_attr_val_tbl         => ppr_pricing_attr_val_tbl,
                     x_price_list_line_val_tbl      => ppr_price_list_line_val_tbl
                    );
   IF v_return_status = fnd_api.g_ret_sts_success
   THEN
      COMMIT;
      DBMS_OUTPUT.put_line
                        ('The Item loading into the price list is Sucessfull');
   ELSE
      DBMS_OUTPUT.put_line ('The Item loading into the price list Failed');
      ROLLBACK;
      FOR i IN 1 .. v_msg_count
      LOOP
         v_msg_data := oe_msg_pub.get (p_msg_index => i, p_encoded => 'F');
         DBMS_OUTPUT.put_line (i || ') ' || v_msg_data);
      END LOOP;
   END IF;
END;
/


After this you need to create one custom table


CREATE TABLE APPS.XXPRICE_ROHIT
(
  ORG_ID      NUMBER,
  PRICE_LIST  VARCHAR2(400 BYTE),
  ITEM_CODE   VARCHAR2(400 BYTE),
  ATTR_VALUE  NUMBER,
  STATUS      VARCHAR2(40 BYTE),
  ERR_MSG     VARCHAR2(400 BYTE),
  ENTRY_ID    NUMBER
)

This table will store all information of Sale order item and which price list will be assigned and what rate will be provided to this item. So upload all the price list data in this staging table.

Then you need to create this final procedure to load the price list data from staging table to oracle standard price list base tables.

CREATE OR REPLACE PROCEDURE APPS.xx_rohit_plist
IS
   CURSOR plist_info_stg
   IS
      SELECT *
        FROM xxprice_rohit
       WHERE NVL (status, 'N') <> 'P';
   v_status             VARCHAR2 (1);
   v_err_msg            VARCHAR2 (100);
   v_item_id            NUMBER;
   v_primary_uom_code   VARCHAR2 (3);
   v_list_header_id     NUMBER;
BEGIN
   FOR z1 IN plist_info_stg
   LOOP
   v_status:= 'P';
   v_err_msg := null;
      BEGIN
         SELECT inventory_item_id, primary_uom_code
           INTO v_item_id, v_primary_uom_code
           FROM mtl_system_items_b msi
          WHERE segment1||'.'||segment2||'.'||segment3||'.'||segment4||'.'||segment5 = z1.item_code
            AND msi.organization_id = z1.org_id;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_status := 'E';
            v_err_msg := 'Inventory Item ID not found';
      END;
      BEGIN
         SELECT list_header_id
           INTO v_list_header_id
           FROM qp_list_headers_tl
          WHERE NAME = z1.price_list;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_status := 'E';
            v_err_msg := 'Price list ID not found';
      END;
      IF     v_item_id IS NOT NULL
         AND v_primary_uom_code IS NOT NULL
         AND v_list_header_id IS NOT NULL
      THEN
         xx_pricelist (v_list_header_id,
                             to_number(z1.attr_value),
                             v_item_id,
                             v_primary_uom_code
                            );
      END IF;
      UPDATE xxprice_rohit
         SET status= v_status,
             err_msg = v_err_msg
       WHERE entry_id = z1.entry_id;
      COMMIT;
   END LOOP;
END;
/




After this just execute this above procedure and all the data will be uploaded in Oracle.



API TO CREATE AND UPDATE SALE ORDER PRICE LIST IN ORACLE APPS


API to Update Customer Sites and assign Tax code in Oracle apps


API to Update Customer Sites and assign Tax code in Oracle apps

 

This below script helps to update Customer Sites and Assign Vat Tax code/Tax Code and Vat Registration No/ Tax Registration No in Customer Master in Oracle.
This is the complete API to Update Customer Sites Information in Oracle Apps.


We are using Standard Oracle API to Achieve this Below requirement.


API to Update Customer Sites and assign Tax code in Oracle apps


DECLARE

x_return_status varchar2(10);

l_init_msg_list VARCHAR2 (1000) := FND_API.G_TRUE;

x_msg_count number(10);

x_msg_data varchar2(1200);

p_object_version_number number(10):=18;



l_cust_site_use_rec APPS.hz_cust_account_site_v2pub.cust_site_use_rec_type;



begin

fnd_global.apps_initialize(61477,61595,222);

dbms_output.put_line('API STARTED' );



 mo_global.set_policy_context('S',5674);



fnd_profile.initialize(61470,61515,222);



l_cust_site_use_rec.SITE_USE_ID:=264031;



l_cust_site_use_rec.cust_acct_site_id:=225246;



l_cust_site_use_rec.TAX_CODE := ‘XX_VAT_5';

--l_cust_site_use_rec.status := 'I';

dbms_output.put_line('API STARTED EXECUTION' );


hz_cust_account_site_v2pub.update_cust_site_use(fnd_api.g_true,

l_cust_site_use_rec,

p_object_version_number,

x_return_status,

x_msg_count,

x_msg_data);IF x_return_status = 'S' THEN

dbms_output.put_line(' Now site use is active' );

ELSE

IF NVL (x_msg_count, 0) > 1 THEN

 FOR i IN 1 .. x_msg_count LOOP

 dbms_output.put_line(' Error Status ' ||x_return_status);

 dbms_output.put_line(' Error message ' ||x_msg_data);

 END LOOP;

 ELSE

 dbms_output.put_line(' Error message ' ||x_msg_data);

 END IF;

 END IF;

 commit;

 exception when others then

 dbms_output.put_line(' Error Here'||sqlcode||sqlerrm);

 end;

Please share your comments if you like this post and pls email me if you are getting any issue in that.

 
API to Update Customer Sites and assign Tax code in Oracle apps