Important Interview Question for Sql* Loader

This is one of the interview question which I faced regarding the sql*loader concept.

If  you are loading the data into table using sql*loader, Discarded records should not cross more than 10 while loading. How?


We are having an option in the sql*loader DISCARDMAX

It holds the integer value and if you pass DISCARDMAX=10, it will stop loading the data into table if the discarded records exceeds more than 10.

Migrate Item Master with Item Category details

Item with Item Category Interface
This script will help to migrate Item Master with Item Category details
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

CREATE OR REPLACE PROCEDURE XXX_Item_Master_Org_Api AS

l_err_flag varchar2(3);
l_err_msg varchar2(2500);
l_count number (2);
l_category_set_id number (20);
l_category_id number (20);
l_org_id number (4);
l_template_name varchar2(250);
l_uom varchar2(20);
l_item_type varchar2(20);
l_organization_id number (10);

--CURSOR TO FETCH THE DATA FROM STAGING TABLE
cursor c1 is
select *
from xxx_item_master_stg
where nvl(verify_flag,'N') = 'N' ;

begin

for c_rec in c1 loop

begin
--- initialize the variables
l_err_flag :='Y';
l_err_msg := null;

---*********** MASTER ORGANIZATION VALIDATION
begin
select organization_id
into l_organization_id
from org_organization_definitions
where upper(organization_name) = upper(trim('XXX ITEM MASTER'));
exception
when others then
l_err_flag := 'Y';
l_err_msg := 'INVALID ORGANIZATION' ;
end ;

---*********** ITEM TYPE VALIDATION
begin
select lookup_code
into l_item_type
from fnd_lookup_values
where lookup_type = 'ITEM_TYPE'
and upper(meaning) = trim(upper(c_rec.item_type));
exception
when others then
l_err_flag := 'N';
l_err_msg := l_err_msg || 'INVALID ITEM TYPE' ;
end ;

---*************** ITEM CODE VALIDATION

begin
l_count := 0;
select count(*)
into l_count
from mtl_system_items_b
where upper(trim(segment1)) = upper(trim(c_rec.item_code))
and organization_id = l_organization_id;
if l_count > 0 then
l_err_flag := 'N';
l_err_msg :=l_err_msg || 'ITEM ALREADY EXISTING' ;
end if;

----*********** VALIDATE THE DESCRIPTION
if trim(c_rec.description) is null then
l_err_flag := 'N';
l_err_msg := l_err_msg || 'INVALID DESCRIPTION';
end if;
end;


--- ITEM TEMPLATE VALIDATION

begin
select template_name
into l_template_name
from mtl_item_templates
where upper(trim(template_name)) = upper(trim(c_rec.template_name));
exception
when others then
l_err_flag := 'N';
l_err_msg := l_err_msg||'INVALID TEMPLATE NAME';
end ;
end;

--- PRIMARY UOM VALIDATION
begin
select unit_of_measure
into l_uom
from mtl_units_of_measure
where upper(trim(uom_code)) = upper(trim(c_rec.uom)); ---eg litre, numbers
exception
when others then
l_err_flag := 'N';
l_err_msg := l_err_msg||'INVALID UOM';
end;

--- ITEM CATEGORY VALIDATION
begin
select category_id
into l_category_id
from mtl_categories_v
where segment1 =trim(c_rec.cat_segment1)
and segment2 = trim(c_rec.cat_segment2) ;
exception
when others then
l_err_flag := 'N';
l_err_msg :=l_err_msg||'INVALID CATEGORY';
end;

---************** ITEM CATEGORY SET VALIDATION
begin
select category_set_id
into l_category_set_id
from mtl_category_sets_tl
where category_set_name = trim(c_rec.cat_set_name);
exception
when others then
l_err_flag := 'N';
l_err_msg :=l_err_msg||'INVALID CATEGORY SET';
end;


--- ITEM CATEGORY COMBINATION VALIDATION
begin
l_count := 0;
select count(*)
into l_count
from mtl_category_set_valid_cats_v
where category_set_id = l_category_set_id
and category_id = l_category_id ;
if l_count = 0 then
l_err_flag := 'N';
l_err_msg :=l_err_msg || 'INVALID CATEGORY COMBINATION' ;
end if;
exception
when others then
l_err_flag := 'N';
l_err_msg :=l_err_msg||'INVALID CATEGORY COMBINATION';
end;



---- IF ALL VALIDATIONS ARE CORRECT, THEN INSERT DATA INTO INTERFACE TABLE
---- THEN BY USING IMPORT ITEM CONCURRENT PGM, IMPORT ITEMS FROM INTERFACE TABLE INTO BASE TABLE

if l_err_flag='Y' then
begin

insert into mtl_system_items_interface
(
item_type
,process_flag
,segment1
,description
,primary_unit_of_measure
,set_process_id
,template_name
,organization_id
,transaction_type
)
values
(
l_item_type
,1
,c_rec.item_code
,c_rec.description
,l_uom
,3
,l_template_name
,l_organization_id
,'CREATE'
);


insert into mtl_item_categories_interface
(
item_number
,category_set_id
,category_id
,process_flag
,organization_id
,set_process_id
,transaction_type
)
values
(
c_rec.item_code
,l_category_set_id
,l_category_id
,1
,l_organization_id
,3
,'CREATE'
);

update xxx_item_master_stg
set verify_flag = 'Y',
err_msg = null
where item_code = c_rec.item_code ;
commit;
exception
when others then
l_err_msg := sqlerrm;
update xxx_item_master_stg
set verify_flag = 'N'
,err_msg = l_err_msg
where item_code = c_rec.item_code ;
end;

commit;

else

update xxx_item_master_stg
set verify_flag = 'N'
,err_msg = l_err_msg
where item_code = c_rec.item_code ;

end if;
commit;
end loop;

commit;

exception
when others then
dbms_output.put_line(sqlerrm);

end xxx_item_master_org_api;
/

Payables base tables in R12 oracle apps

Oracle Apps Payable Table Structure:

Supplier Tables
AP_SUPPLIERS This table replaces the old PO_VENDORS table.
It stores information about your supplier level attributes.
AP_SUPPLIER_SITES_ALL It stores information about your supplier site level attributes.
IBY_EXTERNAL_PAYEES_ALL stores Payee(supplier) information.
HZ_PARTIES Party data for the suppliers.
HZ_PARTY_SITES Party site data for the supplier sites.B4


Supplier Interface tables
ap_suppliers_int Supplier Interface. Execute Supplier Open Interface Import Program
ap_supplier_sites_int Supplier site interface. Execute "Supplier Sites Open Interface Import" Program


Invoice
AP_INVOICES_ALL It contains records for invoices you enter
AP_INVOICE_LINES_ALL It contains records for invoice lines entered manually, generated automatically or imported from the Open Interface.
AP_INVOICE_DISTRIBUTIONS_ALL It holds the distribution information that is manually entered or system-generated


Invoice Interface
AP_INVOICES_INTERFACE Invoice Header Information. Run Payables Open Interface Import concurrent program
AP_INVOICE_LINES_INTERFACE Invoice Lines and Distributions Information


Payments:
AP_CHECKS_ALL Check Header Information
AP_INVOICE_PAYMENTS_ALL Details of Invoice paid by a Check/Payment
AP_PAYMENT_HISTORY_ALL
AP_INV_SELECTION_CRITERIA_ALL
AP_SELECTED_INVOICES_ALL
AP_PAYMENT_DISTRIBUTIONS_ALL
CE_PAYMENT_DOCUMENTS AP_CHECK_STOCKS_ALL
IBY_PAY_SERVICE_REQUESTS   Payment Process Request information  (11i Terminology is  Payment Batch)
IBY_PAY_INSTRUCTIONS_ALL Payment Instruction information
IBY_DOC_PAYABLES_ALL Invoice information stored by IBY for generating payment
IBY_PAYMENTS_ALL Payment Information


Accounting
XLA_EVENTS replaces AP_ACOCUNTING_EVENTS_ALL
XLA_AE_HEADERS replaces AP_AE_HEADERS_ALL
XLA_AE_LINES replaces AP_AE_LINES_ALL


Bank Accounts
CE_BANK_ACCOUNTS AP_BANK_ACCOUNTS_ALL
CE_BANK_ACCT_USES_ALL   replaces AP_BANK_ACCOUNT_USES_ALL


Credit Card Transactions Table
AP_EXPENSE_FEED_LINES Use the Credit Card Transaction Interface Table to load transaction datafrom your credit card issuers into your Payables system. When younsubmit the Credit Card Transaction Validation and Exception Report, for records where the CREATE_DISTRIBUTION_FLAG is Y, Payables
uses the data in the AP_EXPENSE_FEED_LINES table to create distributions with proper accounts in the AP_EXPENSE_FEED_DISTS
table

Identify a special character in your string

How to identify special character in your string.


  SELECT *
FROM   dual
WHERE
LENGTH(REPLACE(TRANSLATE(lower('chalam'),'asdfghjklqwertyuiopzxcvbnm1234567890',''),' ',''))>0


If it display null value there is no special character in your string

If it display X value there is special character in your string

XML Bursting through RDF Report

XML BURSTING THROUGH RDF REPORT

For Bursting we need to have 

1. RDF report. In rdf report we have to write the trigger in after report trigger

Why we write the trigger in After Report Trigger?

 The output which is coming from the concurrent program request, we want to send the output to the different persons using the Bursting program.

Check what we have to write in the Before Report trigger and After Report trigger

Before Report Trigger:-

function BeforeReport return boolean is

begin

  SRW.USER_EXIT('FND SRWINIT');

  return (TRUE);

end;

After Report Trigger:-

FUNCTION AfterReport RETURN BOOLEAN

IS

     v_sub_req                      NUMBER;

     v_cp_description               VARCHAR2(100);

     c_cp_request_id            CONSTANT  NUMBER         := :P_CONC_REQUEST_ID; 

BEGIN

         BEGIN 

               SELECT fcp.user_concurrent_program_name

                 INTO v_cp_description

                 FROM FND_CONCURRENT_REQUESTS    fcr, 

                      FND_CONCURRENT_PROGRAMS_VL fcp

             WHERE  fcr.concurrent_program_id = fcp.concurrent_program_id

               AND fcr.request_id             = c_cp_request_id;

         EXCEPTION WHEN OTHERS THEN 

              v_cp_description := NULL;

         END;

         BEGIN

              v_sub_req :=  fnd_request.submit_request(

                                                        application => 'XDO',             -- application

                                                        program => 'XDOBURSTREP',     -- Program

                                                       description=>  v_cp_description,  -- description

                                                      argument1=>'N' , 

                                                        argument2=> c_cp_request_id,   -- argument1

                                                       argument3=> 'Yes'                -- argument2

                                                      );

              COMMIT;

              IF v_sub_req <= 0

              THEN

                   SRW.MESSAGE('206','Failed to submit Bursting XML Publisher Request for Request ID = '|| c_cp_request_id);

              ELSE

                   SRW.MESSAGE('207','Submitted Bursting XML Publisher Request Request ID = ' || v_sub_req);

              END IF;

         EXCEPTION

            WHEN OTHERS THEN

                 RAISE SRW.PROGRAM_ABORT;

         END;

  SRW.USER_EXIT('FND SRWEXIT');

  RETURN (TRUE);

EXCEPTION

      WHEN SRW.USER_EXIT_FAILURE

      THEN

         SRW.MESSAGE (1, 'Failed in AFTER REPORT TRIGGER');

         RETURN (FALSE);

      WHEN OTHERS

      THEN

         SRW.MESSAGE (1000, 'Failed in When Others' || SQLERRM);

END;

Data Model:-

select * from emp where empno =:p_empno;

User Parameters:-

P_CONC_REQUEST_ID

P_EMPNO

Save the rdf file and move the rdf file in the respective top in the server through winscp.

Concurrent Program Registration:-

Register the rdf report which already you are having using the executable name and prepare the valueset for the empno parameter, output type should be xml and attach it to the concurrent program definition.

Now attach the concurrent program to any of the Request Group and run from that responsibility,  you will be getting the xml output of the query.

Copy the xml file and open 1 word document and upload the xml file and prepare the RTF as per your design.

After completing the rtf  , you need to move the rtf to the server in the below path as 

/u01/d02/apps/apps_st/appl/xdo/12.0.0/patch/115/publisher/templates/US/templatename.rtf

Path may vary based on the AU_TOP defined in your server.

Now you are having the xml file which was generated after running the concurrent program and rtf.

Now it is the time for creating the data definition and attach the RTF file in the template.

Go to the XML Publisher Responsibility and create the data definition, after that attach the rtf file in the template.

After doing this we need to create 1 Bursting file and attach it to the Data Definition.

Bursting control file will be as shown below:

<?xml version="1.0" encoding="UTF-8" ?>

<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting">

<xapi:globalData location="stream"/>

<xapi:request select="/TEST/LIST_G_EMPNO/G_EMPNO">

<xapi:delivery>

<xapi:email id="123" server="your-server-address" port="25" from="chalam101251@gmail.com" >

<xapi:message id="123" to="chaloo.jayoo@gmail.com" attachment="true"

subject="Emp_details ${ENAME}">

--------------------------------------------------------------------------------------------------------Hi,

  Please find the attached employee details.

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material.

Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than

the intended recipient is prohibited.  If you received this in error, please contact the sender and delete the material from any computer.

Regards

HR

All Oracle Apps

</xapi:message>

</xapi:email>

</xapi:delivery>

<xapi:document output="${ENAME}.pdf" output-type="pdf" delivery="123">

<xapi:template type="rtf" location="/u01/d02/apps/apps_st/appl/xdo/12.0.0/patch/115/publisher/templates/US/TEST_PRACTISE.rtf">

      </xapi:template>

    </xapi:document>

</xapi:request>

</xapi:requestset>

After Attaching the Bursting control file and go to the particular responsibility and run the report and your output will be delivered to the person.

This is the concept of XML bursting in XML Publisher and you can do it using the PL/SQL Report and using Java Concurrent Program also.