Oraask - You Will Never Get Stuck Again

Asked: February 21, 2023In: Oracle EBS Queries

Query to Get Supplier Details in Oracle APPS R12

Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant

Added an answer on February 22, 2023 at 12:03 am

This answer was edited.

Hi @Beter, Please find below a query to get the master supplier informations: SELECT HOU.NAME OU_NAME ,APPS.ORG_ID ,APS.SEGMENT1 SUPPLIER_NUMBER ,APS.VENDOR_TYPE_LOOKUP_CODE SUPPLIER_TYPE ,APS.VENDOR_NAME SUPPLIER_NAME ,APS.VENDOR_NAME_ALT SUPPLIER_NAME_ALT ,APS.NUM_1099 TAXPAYER_ID ,APS.VAT_REGISTRRead more

Hi Beter, Please find below a query to get the master supplier informations:

SELECT HOU.NAME OU_NAME
,APPS.ORG_ID
,APS.SEGMENT1 SUPPLIER_NUMBER
,APS.VENDOR_TYPE_LOOKUP_CODE SUPPLIER_TYPE
,APS.VENDOR_NAME SUPPLIER_NAME
,APS.VENDOR_NAME_ALT SUPPLIER_NAME_ALT
,APS.NUM_1099 TAXPAYER_ID
,APS.VAT_REGISTRATION_NUM TAX_REGISTRATION_NUM
,APS.ALLOW_AWT_FLAG SUPPLIER_ALLOW_WITHHOLDING_TAX
,DECODE (APS.END_DATE_ACTIVE, NULL, 'ACTIVE', 'IN ACTIVE') ACTIVE_CODE
,APPS.VENDOR_SITE_ID
,APPS.INACTIVE_DATE
,APPS.VENDOR_SITE_CODE SUPPLIER_SITE_CODE
,APPS.VENDOR_SITE_CODE_ALT SITE_CODE_ALT
,APPS.ALLOW_AWT_FLAG SITE_ALLOW_WITHHOLDING_TAX
,APPS.ADDRESS_LINE1
,APPS.ADDRESS_LINE2
,APPS.ADDRESS_LINE3
,APPS.ADDRESS_LINE4
,APPS.CITY
,APPS.STATE
,APPS.ZIP POST_CODE
,APPS.SUPPLIER_NOTIF_METHOD SITE_PO_NOTIF_METHOD
,APPS.EMAIL_ADDRESS SITE_PO_EMAIL
,APS.PAY_GROUP_LOOKUP_CODE
FROM AP_SUPPLIERS APS, AP_SUPPLIER_SITES_ALL APPS, HR_OPERATING_UNITS HOU
WHERE APS.VENDOR_ID = APPS.VENDOR_ID(+)
AND APS.END_DATE_ACTIVE IS NULL
AND APPS.INACTIVE_DATE IS NULL
AND HOU.ORGANIZATION_ID(+) = APPS.ORG_ID;

Above query will list all suppliers details: OU_NAME, ORG_ID, SUPPLIER_NUMBER, SUPPLIER_TYPE, SUPPLIER_NAME, SUPPLIER_NAME_ALT, TAXPAYER_ID, TAX_REGISTRATION_NUM, SUPPLIER_ALLOW_WITHHOLDING_TAX, ACTIVE_CODE, VENDOR_SITE_ID, INACTIVE_DATE, SUPPLIER_SITE_CODE, SITE_CODE_ALT, SITE_ALLOW_WITHHOLDING_TAX, ADDRESS_LINE1, ADDRESS_LINE2, ADDRESS_LINE3, ADDRESS_LINE4 ,CITY, STATE, POST_CODE, SITE_PO_NOTIF_METHOD, and SITE_PO_EMAIL. And the list is open to add any other column/s you prefer. Note: If you are looking also about other details for the supplier you can find them in the list below:

Supplier Bank Details Query

Hope this helps.

See less

- 0
Share
Share

Asked: September 14, 2021In: Oracle EBS Queries

Query To Find Operating Unit And Corresponding Inventory Organizations Details in Oracle Apps R12

andy.haack Junior

Added an answer on February 18, 2023 at 3:25 pm

This answer was edited.

You can use the following SQL from the GL Ledgers and Organizations using blitz report SELECT HAOUV.NAME BUSINESS_GROUP , (SELECT DISTINCT LISTAGG (GL0.NAME, ', ') WITHIN GROUP (ORDER BY GL0.NAME) OVER (PARTITION BY GLSNAV.LEDGER_ID) LEDGER_SET FROM GL_LEDGER_SET_NORM_ASSIGN_V GLSNAV, GL_LEDGERS GL0Read more

You can use the following SQL from the GL Ledgers and Organizations using blitz report

SELECT HAOUV.NAME BUSINESS_GROUP
, (SELECT DISTINCT LISTAGG (GL0.NAME, ', ') WITHIN GROUP (ORDER BY GL0.NAME) OVER (PARTITION BY GLSNAV.LEDGER_ID) LEDGER_SET
FROM GL_LEDGER_SET_NORM_ASSIGN_V GLSNAV, GL_LEDGERS GL0
WHERE GL.LEDGER_ID = GLSNAV.LEDGER_ID
AND GLSNAV.LEDGER_SET_ID = GL0.LEDGER_ID)
LEDGER_SET
,GL.NAME LEDGER
,XXEN_UTIL.MEANING (GL.LEDGER_CATEGORY_CODE, 'GL_ASF_LEDGER_CATEGORY', 101) LEDGER_CATEGORY
,GL.CURRENCY_CODE CURRENCY
,FIFSV.ID_FLEX_STRUCTURE_NAME CHART_OF_ACCOUNTS_NAME
,HOU.NAME OPERATING_UNIT
,OOD.ORGANIZATION_CODE
,OOD.ORGANIZATION_NAME ORGANIZATION
,FTV.TERRITORY_SHORT_NAME COUNTRY
,XEP.NAME LEGAL_ENTITY
,GL.LEDGER_ID
,FIFSV.ID_FLEX_STRUCTURE_CODE CHART_OF_ACCOUNTS_CODE
,GL.CHART_OF_ACCOUNTS_ID
,OOD.OPERATING_UNIT OPERATING_UNIT_ID
,OOD.ORGANIZATION_ID
FROM GL_LEDGERS GL
,FND_ID_FLEX_STRUCTURES_VL FIFSV
,HR_OPERATING_UNITS HOU
,HR_ALL_ORGANIZATION_UNITS_VL HAOUV
,ORG_ORGANIZATION_DEFINITIONS OOD
,HR_ALL_ORGANIZATION_UNITS HAOU
,HR_LOCATIONS_ALL HLA
,FND_TERRITORIES_VL FTV
,XLE_ENTITY_PROFILES XEP
WHERE NVL (OOD.DISABLE_DATE, SYSDATE) >= SYSDATE
AND NVL (HOU.DATE_TO, SYSDATE) >= SYSDATE
AND NVL (OOD.DISABLE_DATE, SYSDATE) >= SYSDATE
AND NVL (HOU.DATE_TO, SYSDATE) >= SYSDATE
AND GL.OBJECT_TYPE_CODE = 'L'
AND GL.CHART_OF_ACCOUNTS_ID = FIFSV.ID_FLEX_NUM
AND FIFSV.APPLICATION_ID = 101
AND FIFSV.ID_FLEX_CODE = 'GL#'
AND GL.LEDGER_ID = HOU.SET_OF_BOOKS_ID(+)
AND HOU.BUSINESS_GROUP_ID = HAOUV.ORGANIZATION_ID(+)
AND HOU.ORGANIZATION_ID = OOD.OPERATING_UNIT(+)
AND OOD.ORGANIZATION_ID = HAOU.ORGANIZATION_ID(+)
AND HAOU.LOCATION_ID = HLA.LOCATION_ID(+)
AND HLA.COUNTRY = FTV.TERRITORY_CODE(+)
AND OOD.LEGAL_ENTITY = XEP.LEGAL_ENTITY_ID(+)
ORDER BY CHART_OF_ACCOUNTS_NAME
,LEDGER_SET
,BUSINESS_GROUP
,LEDGER
,CURRENCY
,OPERATING_UNIT
,COUNTRY
,ORGANIZATION_CODE

See less

- 0
Share
Share

Asked: December 10, 2022In: Oracle EBS Queries

Query to Get Chart of Account Description in Oracle APPS R12

andy.haack Junior

Added an answer on February 18, 2023 at 3:24 pm

This answer was edited.

You can use the following query from blitz report FND Key Flexfields: SELECT FAV.APPLICATION_NAME,FIF.ID_FLEX_NAME TITLE ,FIF.DESCRIPTION ,FIF.ID_FLEX_CODE ,FIF.APPLICATION_TABLE_NAME ,FIF.UNIQUE_ID_COLUMN_NAME ,FIF.SET_DEFINING_COLUMN_NAME ,DECODE (FIFSV.ENABLED_FLAG, 'Y', 'Y') STRUCTURE_ENABLED ,FRead more

You can use the following query from blitz report FND Key Flexfields:

SELECT FAV.APPLICATION_NAME
,FIF.ID_FLEX_NAME TITLE
,FIF.DESCRIPTION
,FIF.ID_FLEX_CODE
,FIF.APPLICATION_TABLE_NAME
,FIF.UNIQUE_ID_COLUMN_NAME
,FIF.SET_DEFINING_COLUMN_NAME
,DECODE (FIFSV.ENABLED_FLAG, 'Y', 'Y') STRUCTURE_ENABLED
,FIFSV.ID_FLEX_STRUCTURE_CODESTRUCTURE_CODE
,FIFSV.ID_FLEX_STRUCTURE_NAMESTRUCTURE_NAME
,FIFSV.ID_FLEX_NUM
,FIFSV.CONCATENATED_SEGMENT_DELIMITER DELIMITER
,FIFSV.SHORTHAND_PROMPT
,FIFSGV.SEGMENT_NUMSEGMENT_NUMBER
,FIFSGV.SEGMENT_NAME
,FIFSGV.FORM_LEFT_PROMPT WINDOW_PROMPT
,FIFSGV.APPLICATION_COLUMN_NAMECOLUMN_NAME
, (SELECT DISTINCT LISTAGG (XXEN_UTIL.MEANING (FSAV.SEGMENT_ATTRIBUTE_TYPE, 'XLA_FLEXFIELD_SEGMENTS_QUAL', 602), ', ')
WITHIN GROUP (ORDER BY XXEN_UTIL.MEANING (FSAV.SEGMENT_ATTRIBUTE_TYPE, 'XLA_FLEXFIELD_SEGMENTS_QUAL', 602))
OVER (PARTITION BY FSAV.APPLICATION_ID
,FSAV.ID_FLEX_CODE
,FSAV.ID_FLEX_NUM
,FSAV.APPLICATION_COLUMN_NAME)
SEGMENT_ATTRIBUTES
FROM FND_SEGMENT_ATTRIBUTE_VALUES FSAV
WHERE FIFSGV.APPLICATION_ID = FSAV.APPLICATION_ID
AND FIFSGV.ID_FLEX_CODE = FSAV.ID_FLEX_CODE
AND FIFSGV.ID_FLEX_NUM = FSAV.ID_FLEX_NUM
AND FIFSGV.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
AND FSAV.ATTRIBUTE_VALUE = 'Y'
AND FSAV.SEGMENT_ATTRIBUTE_TYPE <> 'GL_GLOBAL')
FLEXFIELD_QUALIFIER
, (SELECT DISTINCT LISTAGG (FSAV.SEGMENT_ATTRIBUTE_TYPE, ', ')
WITHIN GROUP (ORDER BY FSAV.SEGMENT_ATTRIBUTE_TYPE)
OVER (PARTITION BY FSAV.APPLICATION_ID
,FSAV.ID_FLEX_CODE
,FSAV.ID_FLEX_NUM
,FSAV.APPLICATION_COLUMN_NAME)
SEGMENT_ATTRIBUTES
FROM FND_SEGMENT_ATTRIBUTE_VALUES FSAV
WHERE FIFSGV.APPLICATION_ID = FSAV.APPLICATION_ID
AND FIFSGV.ID_FLEX_CODE = FSAV.ID_FLEX_CODE
AND FIFSGV.ID_FLEX_NUM = FSAV.ID_FLEX_NUM
AND FIFSGV.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
AND FSAV.ATTRIBUTE_VALUE = 'Y'
AND FSAV.SEGMENT_ATTRIBUTE_TYPE <> 'GL_GLOBAL')
FLEXFIELD_QUALIFIER_CODE
,FFVS.FLEX_VALUE_SET_NAME VALUE_SET_NAME
,DECODE (FIFSGV.ENABLED_FLAG, 'Y', 'Y') ENABLED
,DECODE (FIFSGV.DISPLAY_FLAG, 'Y', 'Y') DISPLAYED
,DECODE (FIFSGV.REQUIRED_FLAG, 'Y', 'Y') REQUIRED
,DECODE (FIFSGV.SECURITY_ENABLED_FLAG, 'Y', 'Y') SECURITY_ENABLED
,XXEN_UTIL.MEANING (FFVS.VALIDATION_TYPE, 'SEG_VAL_TYPES', 0) VALIDATION_TYPE
,FFVT.APPLICATION_TABLE_NAME VALIDATION_TABLE
,FFVT.ADDITIONAL_WHERE_CLAUSE WHERE_CLAUSE
,FIFSGV.FLEX_VALUE_SET_ID
FROM FND_APPLICATION_VL FAV
,FND_ID_FLEXS FIF
,FND_ID_FLEX_STRUCTURES_VL FIFSV
,FND_ID_FLEX_SEGMENTS_VL FIFSGV
,FND_FLEX_VALUE_SETS FFVS
,FND_FLEX_VALIDATION_TABLES FFVT
WHERE FIF.ID_FLEX_CODE = 'GL#'
AND 1 = 1
AND FAV.APPLICATION_ID = FIF.APPLICATION_ID
AND FIF.APPLICATION_ID = FIFSV.APPLICATION_ID(+)
AND FIF.ID_FLEX_CODE = FIFSV.ID_FLEX_CODE(+)
AND FIFSV.APPLICATION_ID = FIFSGV.APPLICATION_ID(+)
AND FIFSV.ID_FLEX_CODE = FIFSGV.ID_FLEX_CODE(+)
AND FIFSV.ID_FLEX_NUM = FIFSGV.ID_FLEX_NUM(+)
AND FIFSGV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID(+)
AND DECODE (FFVS.VALIDATION_TYPE, 'F', FFVS.FLEX_VALUE_SET_ID) = FFVT.FLEX_VALUE_SET_ID(+)
ORDER BY FAV.APPLICATION_NAME
,FIF.ID_FLEX_NAME
,FIFSV.ID_FLEX_STRUCTURE_NAME
,FIFSGV.SEGMENT_NUM

See less

- 0
Share
Share

Asked: January 23, 2023In: Oracle Database

How to Find Queries Running For More than 5 Minutes

Best Answer

Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant

Added an answer on January 28, 2023 at 3:09 am

To find the queries running for more than 5 or x of minutes you can try the below query and change the seconds parameter as per your need: Query: SELECT S.USERNAME ,Q.SQL_ID ,ROUND (S.LAST_CALL_ET, 2) TIME_IN_SECONDS ,ROUND (S.LAST_CALL_ET / 60, 2) TIME_IN_MINS ,S.SID ,Q.SQL_TEXT FROM GV$SESSION S,Read more

To find the queries running for more than 5 or x of minutes you can try the below query and change the seconds parameter as per your need:

Query:

SELECT S.USERNAME
,Q.SQL_ID
,ROUND (S.LAST_CALL_ET, 2) TIME_IN_SECONDS
,ROUND (S.LAST_CALL_ET / 60, 2) TIME_IN_MINS
,S.SID
,Q.SQL_TEXT
FROM GV$SESSION S, V$SQL Q
WHERE S.SQL_ID = Q.SQL_ID
AND STATUS = 'ACTIVE'
AND USERNAME LIKE '%APPS%'
AND S.LAST_CALL_ET > 300;

Output:

USERNAME SQL_ID TIME_IN_SECONDS TIME_IN_MINS
—————————— ————- ————— ————
SID
———-
SQL_TEXT
——————————————————————————–
APPS 497wh6n7hu14f 10562436 176040.6
331
BEGIN FND_CP_GSM_IPC.Get_Message(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10); END;

1 row selected.

Note: 300 is (5*60). change it as you want to.

See less

- 1
Share
Share

Asked: January 25, 2023In: Oracle EBS Queries

Query to get posted AP invoice in Oracle APPS R12

Best Answer

Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant

Added an answer on January 27, 2023 at 9:53 pm

There is a seeded standard function provided from oracle to identify whether the invoice is posted or not. Which is (AP_INVOICES_PKG.GET_POSTING_STATUS) . This function take the invoice_id parameter and return one of four values: 'Y' - Posted 'N' - Unposted 'S' - Selected 'P' - Partially Posted ThesRead more

There is a seeded standard function provided from oracle to identify whether the invoice is posted or not. Which is (AP_INVOICES_PKG.GET_POSTING_STATUS) .

This function take the invoice_id parameter and return one of four values:

‘Y’ – Posted
‘N’ – Unposted
‘S’ – Selected
‘P’ – Partially Posted

These values is the invoice posting status flag.

Example:

SELECT AIA.INVOICE_NUM, AP_INVOICES_PKG.GET_POSTING_STATUS (AIA.INVOICE_ID) INVOICE_POSTING_FLAG
FROM AP_INVOICES_ALL AIA;

Output:

INVOICE_NUM
————————————————–
INVOICE_POSTING_FLAG
——————————————————————————–
ERS-9163-109073
Y

19879-781
N

ERS-4400-109091
Y

ERS-9164-109093
P

ERS-6970-109094
Y

ERS-6971-109095
Y

See less

- 1
Share
Share

Asked: November 24, 2022In: Oracle Database

Directory Object vs BLOB

Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant

Added an answer on January 23, 2023 at 12:16 am

It depends on the number and size of files you want to store. If you have a small amount of files you can store them into the database however, the database size will get bigger by time. On the other side the directory you can assign a specific space you need based on your size of the files and youRead more

It depends on the number and size of files you want to store. If you have a small amount of files you can store them into the database however, the database size will get bigger by time. On the other side the directory you can assign a specific space you need based on your size of the files and you can leverage other advantages like to open the file write into it and so on.

This a brief and for sure there are other benefits for each option but hopefully my answer give you a little insight.

See less

- 0
Share
Share

Asked: September 15, 2022In: Oracle Reports

Font in RDF

Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant

Added an answer on September 18, 2022 at 12:37 am

This answer was edited.

I am wondering why to use a report builder template to create a PDF report layout while you have the XML publisher with all its facilities and the ease to design any template using MSWord. But generally, the update of the character set shouldn't have any impact on the report layout or output. Also,Read more

I am wondering why to use a report builder template to create a PDF report layout while you have the XML publisher with all its facilities and the ease to design any template using MSWord.

But generally, the update of the character set shouldn’t have any impact on the report layout or output. Also, is this issue exists in all reports or only in this report? try to delete this repeating frame or create a new one, the same as the one that has the issue, and observe the new one.

See less

- 0
Share
Share

Asked: August 17, 2022In: Oracle SQL

How to get Column Names from a Table in Oracle

Best Answer

Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant

Added an answer on August 17, 2022 at 1:35 am

This answer was edited.

To get the list of columns of a table in Oracle, we use to query the view "USER_TAB_COLUMNS" to get the columns of the tables or views owned by the current user. Or use the other view "USER_TAB_COLS". SELECT table_name ,column_name ,data_type ,data_length ,nullable ,data_default FROM user_tab_columnRead more

To get the list of columns of a table in Oracle, we use to query the view “USER_TAB_COLUMNS” to get the columns of the tables or views owned by the current user. Or use the other view “USER_TAB_COLS“.

SELECT table_name
,column_name
,data_type
,data_length
,nullable
,data_default
FROM  user_tab_columns
WHERE table_name = '&YOUTABLENAME'
ORDER BY column_id;

in the above query, we selected 7 columns to retrieve the most important information that you might be looking for in table columns which are

table names: the table name.
column name: column name.
data type: column data type like varchar2, number, etc.
data length: the value length.
nullable: whether the table accepts the null value or not.
data_default: whether the column has a default value or not.

Note: you have to specify the table name parameter in uppercase.

Note 2: If you use the above view, you will get all unhidden columns, but if you query the view “USER_TAB_COLS” you will get all columns, including hidden columns.

Another view is “ALL_TAB_COLUMNS” this one is the same as “USER_TAB_COLUMNS” except that the “ALL_TAB_COLUMNS” view has a column called “OWNER” in which you can specify the owner of the table that you want to get its columns list.

See less

- 0
Share
Share

Asked: August 10, 2022In: Oracle E-Business Suite

Read Only Status Monitor function

Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant

Added an answer on August 13, 2022 at 5:17 pm

Yes, It's possible to achieve this by following these steps: 1- create a menu that has status monitor function and two other sub-menu, "Workflow Configuration tab" and "workflow administrator notification tab". 2- assign this menu to your responsibility. 3- create a grant through a functional adminiRead more

Yes, It’s possible to achieve this by following these steps:

1- create a menu that has status monitor function and two other sub-menu, “Workflow Configuration tab” and “workflow administrator notification tab”.

2- assign this menu to your responsibility.

3- create a grant through a functional administrator for your responsibility to all or specific users

4- exclude the above two menus from the responsibility.

That’s it.

And for the detailed steps with screenshots, I will write an article to showcase this process step by step and will update this answer later on by leaving the article link.

stay tuned 🙂

See less

- 1
Share
Share

Asked: August 1, 2022In: Oracle Ebs Api’s

INV_ITEM_CATEGORY_PUB.update_category_assignment API returned status ‘E’ but without error message

Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant

Added an answer on August 2, 2022 at 12:07 pm

Sometimes this due to Item Categories Key flexfields needs to be recompiled. So, follow this steps to recompile Item Categories KFF: Go to System Administrator > Application > Flexfield > Key > Segments Query for: Flexfield - Item Categories Query or Select your KFF Structure Uncheck theRead more

Sometimes this due to Item Categories Key flexfields needs to be recompiled.

So, follow this steps to recompile Item Categories KFF:

Go to System Administrator > Application > Flexfield > Key > Segments
Query for:
Flexfield – Item Categories
Query or Select your KFF Structure
Uncheck the option “Freeze Flexfield Definition” if it’s checked and then save (ctrl+s)
Recheck the option “Freeze Flexfield Definition” and then save (ctrl+s)
System throws a message that the flex-field is being recompiled.

Now retest the API again.

See less