XMLSEQUENCE SQL Function

XML SEQUENCE SQL Function

How to use and what is XMLSEQUENCE SQL Function?
SQL function XMLSequence returns an XMLSequenceType value (a varray of XMLType instances). Because it returns a collection, this function can be used in the FROM clause of SQL queries.

Export and Import Data from XML Schema Database

Export and Import Data from XML Schema Database

Username:  sys as dba   

Password:   oracle

SQL> show parameter db_name;
It will shows database name

SQL Loader with XML DATA

SQL Loader with XML DATA

1. Conn hr/hr
2. Create table load_test of xmltype;
3. Exit fom user
4. Create a control file test.ctl

Queries Related to Concurrent Requests in 11i Applications

As part of day to day work, we need to use lot of queries to check the information about concurrent requests. Here are few queries which can be frequently used for day to day works and troubleshooting concurrent request / manager issues.

Note: These queries  needs to be run from APPS schema.

Query For find Last Query executed on the form

List the invoices for Trading Partner 'CDS, Inc' from the application.

Payables Manager > Invoices > Inquiry > Invoices > give "CDS, Inc" in the Trading Partner Name field > Find

Now we want to find the database query executed in the backend to show this data for you. Then goto

Important Oracle Apps OM Back to Back orders queries

Important Oracle Apps OM Back to Back orders queries

Back to Back order Requisition:

When the order line status moves to PO-ReqRequested (flow_status_code PO_REQ_REQUESTED). OM will insert a record in the PO requisitions interface table.

Purchase Order Detail Query

Purchase Order Detail Query :

 h.segment1 "PO NUM",

h.authorization_status "STATUS",
l.line_num "SEQ NUM",
ll.line_location_id,

Script for to Find Oracle APIs

Following script and get all the packages related to API in Oracle applications, from which you can select APIs that pertain to AP. You can change the name like to PA or AR and can check for different modules

Oracle EBS R12 Purchasing, Inventory, Order Management Queries

To Find Duplicate Item Category Code

SELECT category_set_name, category_concat_segments, COUNT (*)
FROM mtl_category_set_valid_cats_v
WHERE (category_set_id = 1)
GROUP BY category_set_name, category_concat_segments
HAVING COUNT (*) > 1
ORDER BY category_concat_segments

Convert BLOB to CLOB

This Function is used to convert BLOB files (Images) to CLOB.

This can be used to display Images in the XML Publisher Reports as you cannot directly display the images stored in BLOB format in XML Publisher.

/* ---------------------------------

    TO GET IMAGES USING FUNCTION:

----------------------------------- */

   FUNCTION getbase64 (p_source BLOB)

      RETURN CLOB

   IS

Function to convert Number to Arabic Number

FUNCTION convert_to_arabic (p_input IN VARCHAR2, p_msg OUT VARCHAR2)

    RETURN VARCHAR2

    IS

    --

    l_output VARCHAR2(20) DEFAULT NULL;

Functions for Gregorian to Hijrah and Hijrah to Gregorian

HIJRAH TO GREGORIAN
--------------------------------------
Syntax: 
to_date(hr_sa_hijrah_functions.hijrah_to_gregorian(<VALUE>), 'YYYY/MM/DD')

eg:
SELECT to_date(hr_sa_hijrah_functions.hijrah_to_gregorian('1433/08/30'), 'YYYY/MM/DD')Greg_date FROM DUAL;

Function for converting Number to Words

Syntax:
--------- 
TO_CHAR (TO_DATE (<VALUE>, 'j'), 'jsp')

eg: SELECT (TO_CHAR(TO_DATE (4566,'j'),'jsp')) num_to_words FROM DUAL;