Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Tuesday, March 26, 2013

Important websites

http://www.oracle-base.com/articles/11g/plsql-new-features-and-enhancements-11gr1.php

http://sbhoracle.wordpress.com/2011/11/06/plscope-and-plsql-hierarchical-profiler-in-oracle-11g/

http://technology.amis.nl/2007/11/14/oracle-11g-generating-plsql-compiler-warnings-java-style-using-plscope/


For more information see:
Explore about: 

Streams
Materialized views


New PL/SQL Features for 11g Release 1 (11.1)



http://www.exforsys.com/tutorials/oracle-11g/oracle-analytic-enhancements.html#nth_value_function

http://www.club-oracle.com/

Buy book
Oracle Advanced PL/SQL Developer Professional Guide(Link)
Packt Publishing
http://www.oracle-developer.net/11g.php

http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/OTNN41/Default.aspx

3/13/2013

http://www.gcreddy.com/2012/12/oracle-interview-questions.html#.UUEECdaG2Sp
http://www.aboutoracleapps.com/2008/07/oracle-financials-interview-question.html
http://www.shareoracleapps.com/search/label/AP
http://www.shareoracleapps.com/p/video-tutorials.html

http://www.orafaq.com/tuningguide/

http://zerotoprotraining.com

http://www.cathycakebread.com/papers/papers.html

http://imdjkoch.wordpress.com/category/oracle/oracle-technologies/oa-framework/
http://bhaskarreddyapps.blogspot.com/

http://dillipkumarjena.blogspot.com/2012/09/real-time-interview-question-with.html
http://prasanthapps.blogspot.com/2011/06/oracle-receivables-transaction-types.html
http://oracle-applications-forall.blogspot.com/p/account-receivables.html

http://oracleapps4u.com/wp/interview-qa/

http://kranthijuvva.blogspot.com/?view=classic

Purchasing SQL Scripts


Steps to customize the PO Approval Workflow.

1. Take the seeded workflow (POAPPRV)
2. Right click on the Item TYpe and create a new Item TYpe .
3. Open the Seeded Workflow(POAPPRV) in other window .
4. Copy and paste ATTRIBUTES, NOTIFICATIONs, FUNCTIONS and PROCESSES etc from the the seeded workflow to the Newly named workflow .
5. Verify the workflow
6. Customize the new workflow according to your needs.
7. Save the changes and port it to Oracle.

Login with a PO Super User responsibility, go to Setup -> Document Types, select the PO types for which you want to use the custom workflow, and change workflow fields 'Approval Workflow' and 'Workflow Startup Process' to the new ones.

Sql Script to extract Vendor, Project, Receipt information for a PO

SELECT pha.po_header_id, pha.segment1 po_number, pov.vendor_name,
pov.segment1 vendor_num, pla.line_num po_line_number, pla.item_id,
(SELECT MAX (segment1)
FROM mtl_system_items_b
WHERE inventory_item_id = pla.item_id) item,
DECODE (NVL (pla.item_id, 0),
0, NULL,
pla.item_description
) item_description,
DECODE (pla.cancel_flag,
'Y', 'CANCELLED',
DECODE (pha.authorization_status,
'IN PROCESS', 'PENDING APPROVAL',
NVL (pha.authorization_status, 'INCOMPLETE')
)
) authorization_status,
DECODE (pla.cancel_flag,
'Y', 'N/A',
DECODE (pha.authorization_status,
'APPROVED', DECODE (NVL (pla.closed_code, 'b'),
'CLOSED', 'CLOSED',
'FINALLY CLOSED', 'CLOSED',
'OPEN'
),
'N/A'
)
) po_status,
pha.creation_date date_issued, pla.creation_date line_creation_date,
(pda.quantity_ordered - pda.quantity_cancelled) po_quantity,
(NVL (pla.unit_price, 0) * NVL (pda.quantity_ordered, 0)
) po_line_amt,
(NVL (pla.unit_price, 0) * NVL (pda.quantity_cancelled, 0)
) po_cancelled_amount,
(NVL (pla.unit_price, 0) * NVL (pda.quantity_delivered, 0)
) po_line_received_amount,
rt.transaction_date receipt_date,
TO_CHAR (rt.transaction_date, 'MON-RR') receipt_month,
rsh.receipt_num,
(DECODE (NVL (rt.transaction_type, 'a'),
'RETURN TO VENDOR', NVL (rt.quantity * (-1), 0),
'RETURN TO RECEIVING', NVL (rt.quantity * (-1), 0),
NVL (rt.quantity, 0)
)
) receipt_quantity,
( NVL (rt.po_unit_price, 0)
* DECODE (NVL (rt.transaction_type, 'a'),
'RETURN TO VENDOR', NVL (rt.quantity * (-1), 0),
'RETURN TO RECEIVING', NVL (rt.quantity * (-1), 0),
NVL (rt.quantity, 0)
)
) receipt_amount,
ai.invoice_id, ai.invoice_num, ai.invoice_amount, ai.amount_paid,
ai.invoice_date, aid.quantity_invoiced, aid.amount inv_line_amount,
ppa.project_id, ppa.segment1 project_number, ppa.NAME project_name,
(SELECT full_name
FROM pa_project_players_v
WHERE project_id = ppa.project_id
AND UPPER (ROLE) = 'PROJECT MANAGER'
AND NVL (end_date_active, SYSDATE) IN (
SELECT MAX (NVL (end_date_active, SYSDATE))
FROM pa_project_players_v
WHERE 1 = 1
AND project_id = ppa.project_id
AND UPPER (ROLE) = 'PROJECT MANAGER')
AND ROWNUM < 2) "Project Manager",
total_billings.invoice_amount total_actual_billings
FROM po_headers_all pha,
po_lines_all pla,
po_distributions_all pda,
ap_invoices_all ai,
ap_invoice_distributions_all aid,
rcv_transactions rt,
rcv_shipment_headers rsh,
po_vendors pov,
pa_projects_all ppa,
(SELECT i.project_id, SUM (NVL (ii.inv_amount, 0)) invoice_amount
FROM pa_draft_invoices_all i, pa_draft_inv_items_bas ii
WHERE 1 = 1
AND ii.project_id(+) = i.project_id
AND ii.draft_invoice_num(+) = i.draft_invoice_num
AND i.pa_date < ((SELECT end_date
FROM pa_periods_all ppi
WHERE ppi.current_pa_period_flag = 'Y') + 1)
GROUP BY i.project_id) total_billings
WHERE pha.po_header_id = pla.po_header_id
AND pda.po_header_id = pla.po_header_id
AND pda.po_line_id = pla.po_line_id
AND pda.po_header_id = pha.po_header_id
AND pha.vendor_id = pov.vendor_id
AND pda.po_distribution_id = aid.po_distribution_id(+)
AND aid.invoice_id = ai.invoice_id(+)
AND pla.po_line_id = rt.po_line_id(+)
AND rt.shipment_header_id = rsh.shipment_header_id(+)
AND rt.destination_type_code(+) = 'RECEIVING'
AND pda.project_id = ppa.project_id(+)
AND ppa.project_id = total_billings.project_id(+)

Script to Approve PO Requisition

/* Formatted on 2010/05/15 11:23 (Formatter Plus v4.8.0) */
DECLARE
l_itemkey VARCHAR2 (200);
l_po_id NUMBER := :PO_HEADER_ID;
l_po_number VARCHAR2 (200) := :REQUISITION_NUMBER;
BEGIN
SELECT l_po_id || '-' || TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO l_itemkey
FROM DUAL;

wf_engine.createprocess ('REQAPPRV',
l_itemkey,
'MAIN_REQAPPRV_PROCESS',
NULL,
:USER_NAME
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'RESPONSIBILITY_ID',
avalue => :RESPONSIBILITY_ID
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'APPLICATION_ID',
avalue => :APPLICATION_ID
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'USER_ID',
avalue => :USER_ID
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'SUBMITTER_ID',
avalue => :BUYER_ID
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_ID',
avalue => l_po_id
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_NUMBER',
avalue => l_po_number
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'ORG_ID',
avalue => :ORG_ID
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_SUBTYPE',
avalue => 'PURCHASE'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_TYPE',
avalue => 'REQUISITION'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_TYPE_DISP',
avalue => 'Purchase Requisition'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'AUTHORIZATION_STATUS',
avalue => 'APPROVED'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'AUTHORIZATION_STATUS_DISP',
avalue => 'Approved'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'SEND_CREATEPO_TO_BACKGROUND',
avalue => 'Y'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'INTERFACE_SOURCE_CODE',
avalue => 'PO_FORM'
);

DBMS_OUTPUT.put_line (l_itemkey);
wf_engine.startprocess ('REQAPPRV', l_itemkey);
COMMIT;
END;
/

Script to cancel PO Requisition

DECLARE
l_return_status VARCHAR2 (1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
lv_header_id po_tbl_number;
lv_line_id po_tbl_number;
m NUMBER := NULL;
l_msg_dummy VARCHAR2 (2000);
l_output VARCHAR2 (2000);
BEGIN
m := 1;
lv_header_id := po_tbl_number (:REQ_HEADER_ID);
lv_line_id := po_tbl_number (:REQ_LINE_ID);
po_req_document_cancel_grp.cancel_requisition
(p_api_version => 1.0,
p_req_header_id => lv_header_id,
p_req_line_id => lv_line_id,
p_cancel_date => SYSDATE,
p_cancel_reason => 'Cancelled Requisition',
p_source => 'REQUISITION',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
COMMIT;
DBMS_OUTPUT.put_line (l_return_status);

IF l_return_status <> 'S'
THEN
fnd_msg_pub.get (m, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (m) || ': ' || l_msg_data);
DBMS_OUTPUT.put_line (l_output);
END IF;
END;

Credit goes to the below website(s) :

http://arunrathod.blogspot.com/

p_datetrack_update_mode, p_datetrack_delete_mode in HRMS API


Understanding the p_datetrack_update_mode Control Parameter in HRMS API

The p_datetract_update_mode control parameter enables you to define the type of DateTrack change to be made. This mandatory parameter must be set to one of the values as mentioned below:

UPDATE - Keep history of existing information
CORRECTION - Correct existing information
UPDATE_OVERRIDE - Replace all scheduled changes
UPDATE_CHANGE_INSERT - Insert this change before next scheduled change

Understanding the p_datetrack_delete_mode Control Parameter

The p_datetract_update_mode control parameter enables you to define the type of DateTrack deletion to be made. This mandatory parameter must be set to one of the following values:

ZAP - Completely remove from the database
DELETE - Set end date to effective date
FUTURE_CHANGE - Remove all scheduled changes
DELETE_NEXT_CHANGE - Remove next change

Script to create a FND user using PL/SQL

DECLARE
l_responsibility_id NUMBER;
l_application_id NUMBER;
l_user_id NUMBER := fnd_global.user_id;
x_user_id NUMBER;
l_password VARCHAR2 (2000) := 'welcome1';
BEGIN
apps.hr_user_acct_internal.create_fnd_user (p_user_name => 'arathod',
p_password => l_password,
p_employee_id => NULL,
p_user_id => x_user_id,
p_user_start_date => SYSDATE,
p_email_address => NULL,
p_description => NULL,
p_password_date => NULL
);
DBMS_OUTPUT.put_line (x_user_id);

IF x_user_id IS NOT NULL
THEN
UPDATE fnd_user
SET password_lifespan_days = 90
WHERE user_id = x_user_id;

COMMIT;
END IF;
END;

PL/Sql script to add System Administrator responsibility


BEGIN
fnd_user_pkg.addresp
(username => UPPER
('vinod.khanna'),
resp_app => 'SYSADMIN',
resp_key => 'SYSTEM_ADMINISTRATOR',
security_group => 'STANDARD',
description => 'DESCRIPTION',
start_date => SYSDATE,
end_date => NULL
);
COMMIT;
DBMS_OUTPUT.put_line ('Responsibility Added Successfully');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Responsibility is not added due to'|| SQLCODE|| SUBSTR (SQLERRM, 1, 100));
ROLLBACK;
END;
/

How To Process 1099s in Oracle Payables



1099
Each January the subject of 1099 processing shoots to the top of the priority list for many AP Managers. Remembering exactly what was done the previous year typically is the first step. Your IT Department may have already downloaded the latest annual 1099 patches from Oracle for 1099 processing. To help pick up where you left off last year I have summarized the setup and processing steps below for ease of reference.
For a deeper dive Oracle provides plenty of pertinent information and documentation for the 1099 process. The best place to visit is the Information Center here on the Oracle My Support site (support.oracle.com) for what is new or for frequently asked questions.
The 1099 preparation steps are as follows:
1.     Set up the Query Driver for either INV or PAY (Setup > Tax > Reporting Entities)
    • Name: Name of your 1099 Tax Reporting Entity
    • Location: From a LOV
    • Tax ID: Number Tax ID Number
    • Balancing Segments: The balancing segment of the accounting flexfield that is 1099 reportable
 2.     Set up Suppliers to be reportable (Supplier > Entry, Tax Reporting tab) 
    • Reportable, Federal: Must be checked
    • Income Tax Type: This MISC code will default into the invoice distributions
    • Reportable, State: This controls if State Code on the Site is required and if there is an LOV for the State Code
    • Reporting Site: * This will be filled in for you from the SITE
    • Reporting Name: If you want the Suppliers Name to be different on the 1099s
    • Verification Date: Used for the Supplier Tax Verification Letter
    • Organization Type: Used for the 1099 Electronic Media, and must be one of the eight options listed (You cannot make up your own, because the IRS will not accept them)
 3.     Setup one Supplier site as the 1099 reporting site (Supplier > Entry, Site button, Tax Reporting tab)
    • Tax Registration Number: This has nothing to do with 1099s
    • Income Tax Reporting Site: Only one site per supplier can have this field checked
    • The Reporting Site * on the Supplier record gets filled in with this Sites name
    • This Sites address is used for mailing the Suppliers copy of the 1099 form to the Supplier
 NOTE: All sites are reportable. This site is just the address to use on the 1099 form.
  4.     Ensure that the appropriate AP invoice lines are flagged appropriately (Invoices > Entry > Invoices)
    • Enter a regular invoice
    • Enter Distributions
      • Income Tax Type The MISC code defaults from Supplier record
      • Income Tax Region The State code (like FL for Florida) defaults from the Site’s address
 5.     Understand what will be considered 1099 reportable
    •  The total for all 1099 reportable invoice distributions is $600 or higher (See http://www.irs.gov for more information on which MISC codes have reportable amounts different from the normal $600 limit)
    • There is a MISC code on the invoice distribution in the Income Tax Type field
    • The balancing segment of the account number is the same as the balancing segment of the Tax Reporting Entity
    • The Invoice is paid within the date range specified (the year usually)
    • The Query Driver (INV or PAY) determines which account number is checked
      • INV checks the balancing segment from the charge account on the invoice distribution
      • PAY checks the balancing segment on the banks cash account used for the payment
 6.     Identify and resolve 1099 exceptions: 
          • suppliers with null or invalid state abbreviations
          • suppliers that will be flagged as foreign in the 1099 Tape
          • suppliers with missing or non-standard Tax Identification Numbers
          • Resolve any exceptions in the Suppliers window
  7.     (Optional) Print a Tax Information Verification Letter for each supplier who has not yet  furnished or confirmed its           tax identification number or tax reporting region (See: Tax Information Verification Letter)
  8.     Update 1099 Payment Information
  9.     Generate 1099 Reports 
    • 1099 Forms - Submit to the Internal Revenue Service for each 1099 supplier, and send a copy to the supplier (See:1099 Forms)
    • 1096 Form - Submit to the Internal Revenue Service for each 1099 MISC type paid during the calendar year (See:1096 Form)
    • 1099 Tape - If you submit 250 or more 1099 forms, the Internal Revenue Service requires you to file your 1099 payment information on magnetic media (See: 1099 Tape)
    • Form 941 - Send this form to the Internal Revenue Service to report total supplier withholding for a quarter. Your Accounts Payable department should create a manual payment for the total amount withheld, and you should remit this amount and the 1096 Form to the Internal Revenue Service. In a future version of Payables, you can generate the 941 Form as a standard report.
    • 1099 Payments Report - Run this report for your reference to review 1099 payments by supplier or MISC type (See:1099 Payments Report)

      Credit goes to the below website(s) :

      http://info.tricoresolutions.com/blog/bid/261468/How-To-Process-1099s-in-Oracle-Payables

Monday, March 25, 2013

Oracle Payables – Interview FAQ – Invoice Payment


What is the difference between the Electronic and Wire payment methods?
In short, the Electronic method enforces the existence of a supplier bank
account and the Wire method does not.  Therefore, use the Electronic payment
method to generate instructions to your bank to make payment to a supplier bank account.  Use the Wire payment method to record payment when you have used a process outside of your Oracle Payables system to instruct your bank to pay a supplier.
Electronic Payment
Use the Electronic payment method when Payables will create instructions for your bank to make payment to a supplier bank account. Typically, this communication is an electronic file that instructs your disbursement bank to pay your suppliers, and is in the specific format that your bank requires.  However, you should use the Electronic payment method whenever you need to generate a document that requires a supplier bank account.  For example, use it if your Payables system is set up to print letters that you send to your bank to request that the bank make an electronic funds transfer directly into the supplier’s bank account.
Payables ensure that you have recorded supplier bank account information when you use the Electronic payment method. Typically, to pay invoices with the Electronic payment method, users use a payment document with a Computer Generated disbursement type and use a payment batch or Quick payment to create a payment instruction file.  The payment instruction file is saved in the ap.out directory for delivery to the bank, unless one of the following features is used to automatically transmit the instruction file to the bank: e-Commerce Gateway (for EDI payments), Automatic Bank Transmission, XML Payment Processing.
However, you can use localizations, or custom payment methods and payment formats to create any type of communication with your bank when you use the Electronic payment method.
Wire Payment
Use the Wire payment method to manually record payment when you have used a process outside of your Oracle Payables system to instruct your disbursement bank to pay a supplier. Oracle Payables does not require supplier bank account information when you use the Wire payment method. When you define payment documents for these payments, we recommend you use the Recorded disbursement type because you are simply recording a payment made outside of the system.  Further Oracle recommends that you record the transaction with a manual payment.
(However, the system will allow you to use any disbursement type.  For example, some users who regularly record Wire payments for multiple suppliers use payment documents with the Computer Generated disbursement type, create an electronic payment batch, and then delete the resulting electronic file.)

What is the Payment Batch processing flow?
AutoSelect - First you initiate the Payment Batch by entering criteria for invoices
Build – The system then builds payments based on the selection criteria.
Modify – You can optionally modify the payment batch.
Format – Format payments to have Payables produce an output file.
Print – Print checks from the output file or deliver the output file to your bank.
Confirm  - Confirm the payment batch.
I selected a Payment Document for my Bank Account that says it is in use.
When you selected the Payment Document for the Bank Account, the Document Names window displays a column “In Use By”.  This is the Payment Batch name that is using the Payment Document.  You must complete the Payment Batch using that document before you can use it.  You will only get the list of payment documents if there is more than one payment document associated with that bank account.  If there is only one payment document for that bank account, you will simply get the message that the payment document is in use.  If you still do not see a Payment Batch, then most likely a QuickCheck did not complete successfully.
How do I add an invoice to the Payment Batch?
1. From the Actions Window, unselect Format and select Modify Payment Batch.
2. Enter the Supplier Name and Site.
3. Select Yes for Pay Supplier.
4. Select the invoice you wish to add to this Payment Batch.
5. Select Done.
The system will automatically submit the Build Payments program to rebuild the payments.
When can I modify a Payment Batch?
After the Payment Batch has been Built and before it has been Formatted.  Once the payment batch has been Formatted, Modify is no longer an option.
Can I add an invoice that has been excluded because the payment batch exceeds the maximum outlay.
Yes.  Select the Supplier and Site and choose ‘Force’ as the Pay Option in the Modify Payments Window.

How do I  reprint checks after payment batch has been formatted.
1. Navigate to the concurrent request summary form (Other -> Concurrent)
2. Either query the format payment concurrent request, or query all and manually search for the format payments request.
3. Select Special…Reprint from the toolbar.
4. This opens the reprint dialog box.
5. Select the number of copies, the printer, and the print style
6. Press OK
Can I cancel a Payment Batch that has been confirmed.
No.  You must void each payment created by the Payment Batch to accomplish this
How do I determine the status of the Payment Batch?
Query up the Payment Batch in the Payment Batches Summary window to view the status.  If you can not get into the application, you can select the status from SQL*Plus:
    SELECT status
    FROM ap_inv_selection_criteria_all
    WHERE checkrun_name = ‘<payment batch name>’
    Credit goes to the below website(s) :
    http://knoworacle.wordpress.com/2010/05/24/oracle-payables-interview-faq-invoice-payment/

Query to fetch Responsiblities attached to a Concurrent Program


Select distinct fr.responsibility_name,
                fcp.concurrent_program_name,
                frg.request_group_name
  from fnd_concurrent_programs fcp,
       fnd_request_group_units frgu,
       fnd_request_groups      frg,
       fnd_responsibility_vl   fr
 Where fcp.concurrent_program_id = frgu.request_unit_id
   AND frg.request_group_id = fr.request_group_id
   AND frg.application_id = fr.group_application_id
   AND frgu.application_id = frg.application_id
   And frgu.request_group_id = fr.request_group_id
   AND frgu.unit_application_id = fcp.application_id
      --AND rgu.request_unit_name = fcp.concurrent_program_name
      --And fcp.concurrent_program_name LIKE 'RSPERSTF1%'
   And fcp.concurrent_program_name = 'RHRREGPERS' -- put short name here

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

How many times have you been told by Oracle Support that you should run a concurrent request...only to realize that you have no idea which responsibility to choose to run the request? Or you want to instruct a user to run a request, and you want to be sure they have access? The query below will help you. It will provide for you the responsibility name and the request group name which can run a concurrent program. You can search by the user_concurrent_program_name or the "short_name".


SELECT FRT.RESPONSIBILITY_NAME, FRG.REQUEST_GROUP_NAME, FRG.DESCRIPTION
  FROM FND_REQUEST_GROUPS FRG
      ,FND_REQUEST_GROUP_UNITS FRGU
      ,FND_CONCURRENT_PROGRAMS FCP
      ,FND_CONCURRENT_PROGRAMS_TL FCPT
      ,FND_RESPONSIBILITY_TL FRT
      ,FND_RESPONSIBILITY FR
 WHERE     FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
       AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
       AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
       AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
       AND FCPT.SOURCE_LANG = USERENV('LANG')
       AND FCP.APPLICATION_ID = FCPT.APPLICATION_ID
       AND FCP.CONCURRENT_PROGRAM_ID = FCPT.CONCURRENT_PROGRAM_ID
       AND FR.APPLICATION_ID = FRT.APPLICATION_ID
       AND FR.RESPONSIBILITY_ID = FRT.RESPONSIBILITY_ID
       AND FRT.SOURCE_LANG = USERENV('LANG')
       AND FR.REQUEST_GROUP_ID = FRG.REQUEST_GROUP_ID
       AND FR.APPLICATION_ID = FRG.APPLICATION_ID
       --   AND FCP.CONCURRENT_PROGRAM_NAME = 'OPMTMOPG'  --  YOU CAN PUT THE SHORTNAME HERE
       AND FCPT.USER_CONCURRENT_PROGRAM_NAME LIKE 'Purge OPM Txns and Move Order Lines' --OR THE USER CONC PROGRAM NAME HERE
;

Wednesday, March 20, 2013

ETL Concepts and Business Logic


ETL Concept:
It is a process of extracting the data and transforming into required business format via loading into DWH.
ETL is often a complex combination of process and technology that consumes a significant portion of the data warehouse development efforts and requires the skills of business analysts, database designers, and application developers. It is not a one time event as new data is added to the Data Warehouse periodically – monthly, daily, hourly. Because ETL is an integral, on-going, and recurring part of a data warehouse
o    Automated
o    Well documented
o    Easily changeable
There are 2 types of ETL’s used in implementing data acquisition.
Code based ETL:
An ETL application can be developed using some programming languages such as SQL PL/SQL.
Example:
o    Oracle SQL Loader
o    SAS Based
o    SAS Access
o    Tera data Load
GUI based ETL :
An ETL application can be designed using simple graphically user interface point and click techniques.
o    Informatica
o    Data stage
o    AB Initio
o    Data Manager
o    Data services
o    SSIS
Data Extraction:
It is a process of reading the data from multiple operational source system.
The following are the types of operation source systems.
o    Oracle
o    Sql server
o    DB-2
o    Sybase
o    Informix
ERP Sources:
o    SAP
o    People soft
o    C-Bell
Legacy sources:
o    Mainframe
o    Cobol files
File sources:
o    Flat Files
o    XML files
Other sources:
o    Excel sheets
o    MS-Access
Data Transformation:
It is a process of converting the data and clinzing the data in the staging area. Staging area is a temporary memory or buffer where the data transformation activities take place. The following one the various types of data transformation activities take place
o    Data Merging
o    Data clinzing
o    Data scrubbing
o    Data aggregation
Data Merging:
It is process of integrating the data from multiple operational source system.
There are 2 types of data merge operations.
o    Horizontal Merging(Join)
o    Vertical Merging(Union)
Data Clinzing:
It’s a process of changing inconsistencies and inaccuracies; or a process of removing unwanted data (filtering)
Data Scrubbing:
It is a process of deriving new data definitions.
Data Aggregation: 
It’s a process of calculating the summaries using an aggregate function called Sum ()
Data Loading:
It is a process of inserting data into a target system. there is 2 types of data loads.
Initial Load or full Load:
It’s a process of inserting the data into an empty the data into an empty target tables very first time. All the required data loads into empty target tables.
Incremental data load or delta load:
It’s a process of loading only new record or any changes records which takes place after initial load
An ETL Use-Case
In order to demonstrate this, we’ll use a use-case that is similar in concept. In our ETL development example, the human resources department uses a recruiting tool written using a dBase- or FoxPro-like data structure. But the employee data is stored in Microsoft SQL Server.
For reference, here is the new_empl.dbf and using an XSLT transform that reads a table and displays it as HTML.
Our steps will be then, to Extract, Transform, and Load
•    The extract step will take the data from the dBASE III file and convert it into a more usable format – XML
•    The transform step will change the date format into standard ISO dates, split the name into first and last names, and assign the appropriate manager based on whether the employee is being assigned to inside sales or the external sales force.
•    The load step will take the resulting file and send it to SQL Server.
•    As a side-effect, it would be nice to get a report of data loaded.
Credit goes to the below website(s) :