Tech Touch Online

Office Activation

2025-07-20T04:46:00.000-07:00

How to convert MP3 to Text

2025-05-30T02:48:00.000-07:00

Download

https://github.com/BtbN/FFmpeg-Builds/releases

Convert to pure MP3 first & then Split (chatgpt)

If the original file is wrapped in a different container or is partially corrupt, re-export it:

bash

কপি করুনএডিট করুন

ffmpeg -i input.mp3 -vn -ar 44100 -ac 2 -b:a 192k clean_input.mp3

Then split the cleaned file:

bash

কপি করুনএডিট করুন

ffmpeg -i clean_input.mp3 -f segment -segment_time 300 -c copy output_%03d.mp3

N.B.: 300 = 5 minutes

Convert MP3 to Text

https://turboscribe.ai/dashboard

Use the following queries to check the status of accounting periods. Be sure to substitute the relevant start and end dates as needed.

2025-05-28T02:30:00.000-07:00

SELECT FA.APPLICATION_NAME, SOB.NAME LEDGER, GPS.PERIOD_NAME,

GPS.PERIOD_NUM, GPS.START_DATE, GPS.END_DATE, GPS.CLOSING_STATUS,

DECODE(CLOSING_STATUS,'O','Open', 'C','Closed', 'F','Future', 'N','Never', CLOSING_STATUS) GL_STATUS

FROM GL_PERIOD_STATUSES GPS,

GL_SETS_OF_BOOKS SOB,

FND_APPLICATION_VL FA

WHERE 1=1

--AND FA.APPLICATION_NAME = 'General Ledger'

AND SOB.NAME LIKE 'Vision Operations (USA)%'

AND SOB.SET_OF_BOOKS_ID=GPS.SET_OF_BOOKS_ID

AND FA.APPLICATION_ID = GPS.APPLICATION_ID

--AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'

--AND GPS.CLOSING_STATUS = 'O'

AND PERIOD_NAME ='Jun-25'

How to find GL Code Combination ID and Description

2025-05-19T21:49:00.000-07:00

Find GL Code Combination ID & Description

SELECT gcc.segment1

|| '.'

|| gcc.segment2

|| '.'

|| gcc.segment3

|| '.'

|| gcc.segment4

|| '.'

|| gcc.segment5 ACCOUNT_CODE,

a1.description

|| '-'

|| a2.description

|| '-'

|| a3.description

|| '-'

|| a4.description

|| '-'

|| a5.description ACCOUNT_DECS

FROM fnd_flex_values_vl A1,

fnd_flex_values_vl A2,

fnd_flex_values_vl A3,

fnd_flex_values_vl A4,

fnd_flex_values_vl A5,

gl_code_combinations gcc

WHERE a1.flex_value = gcc.segment1

AND a1.flex_value_set_id IN

(SELECT FLEX_VALUE_SET_ID

FROM fnd_id_flex_segments

WHERE application_id = 101

AND id_flex_code = 'GL#'

AND enabled_flag = 'Y'

AND application_column_name = 'SEGMENT1')

AND a2.flex_value = gcc.segment2

AND a2.flex_value_set_id IN

(SELECT flex_value_set_id

FROM fnd_id_flex_segments

WHERE application_id = 101

AND id_flex_code = 'GL#'

AND enabled_flag = 'Y'

AND application_column_name = 'SEGMENT2')

AND a3.flex_value = gcc.segment3

AND a3.flex_value_set_id IN

(SELECT flex_value_set_id

FROM fnd_id_flex_segments

WHERE application_id = 101

AND id_flex_code = 'GL#'

AND enabled_flag = 'Y'

AND application_column_name = 'SEGMENT3')

AND a4.flex_value = gcc.segment4

AND a4.flex_value_set_id IN

(SELECT flex_value_set_id

FROM fnd_id_flex_segments

WHERE application_id = 101

AND id_flex_code = 'GL#'

AND enabled_flag = 'Y'

AND application_column_name = 'SEGMENT4')

AND a5.flex_value = gcc.segment5

AND a5.flex_value_set_id IN

(SELECT flex_value_set_id

FROM fnd_id_flex_segments

WHERE application_id = 101

AND id_flex_code = 'GL#'

AND enabled_flag = 'Y'

AND application_column_name = 'SEGMENT5')

PLSQL query to find Open/Close status of Periods - Oracle EBS R12

2025-05-05T23:16:00.000-07:00

/* Formatted on 5/6/2025 9:55:34 AM (QP5 v5.381) */

select * from GL_PERIOD_STATUSES

select * from GL_PERIOD_STATUSES_V

SELECT * FROM GL_LEDGERS where name ='Vision Operations (USA)'

SELECT NAME,

LEDGER_ID,

period_name,

APPLICATION_NAME,

status

FROM (SELECT ff.NAME NAME,

kk.LEDGER_ID,

kk.PERIOD_NAME,

jj.APPLICATION_NAME,

period_year,

start_date, end_date,

DECODE (kk.CLOSING_STATUS,

'O', 'Open',

'C', 'Closed',

'F', 'Future',

'N', 'Never') STATUS

FROM GL_PERIOD_STATUSES_V kk, fnd_application_vl jj, gl_ledgers ff

WHERE 1 = 1

AND jj.APPLICATION_ID = kk.APPLICATION_ID

AND ff.LEDGER_ID = kk.LEDGER_ID

AND kk.CLOSING_STATUS != 'N')

where name ='Vision Operations (USA)'

--and period_year = 2024

and APPLICATION_NAME='General Ledger'

UNION

(SELECT

fbc.BOOK_TYPE_CODE NAME,

fbc.set_of_books_id LEDGER_ID,

fdp.period_name,

'Assets' APPLICATION_NAME,

DECODE (fdp.period_close_date, NULL, 'Open', 'Closed') status

FROM fa_book_controls fbc, fa_deprn_periods fdp

WHERE fbc.book_type_code = fdp.book_type_code

AND fbc.DATE_INEFFECTIVE IS NULL)

how to increase session timeout in oracle apps r12

2023-10-16T00:25:00.000-07:00

how to increase session timeout in Oracle apps r12

Find Profile name ICX:Session Timeout

Default : None
Recommendation : 30 (minutes)

pick release process could not allocate of item for order as there was insufficient quantity available or inventory allocations have failed, and the line has been back ordered.

2023-10-06T02:11:00.003-07:00

Question: pick release process could not allocate of item for order as there was insufficient quantity available or inventory allocations have failed, and the line has been back ordered.
Oracle EBS Apps R12

Answer:

Please confirm whether the inventory period is currently accessible for the specified dates of the order. If it is not, kindly proceed to initiate the opening of the inventory period and conduct a thorough examination.

SELECT SEVERITY, STATUS, A.*

FROM WSH_EXCEPTIONS A

WHERE BATCH_ID = 6393351

Bitlocker Keys solution for accidental lock of drive

2023-02-09T21:27:00.007-08:00

1. login in myaccount.microsoft.com

like abc.d@standard-group.com

2. go to Devices option

3. Click the Downarrow option

4. View Bitlocker Keys option will be visible after a while

5. Click View Bitlocker Keys

6. Click Show Recovery Key

7. Copy the Recovery Key

8. Click the drive to recover & paste Recovery Key into Recovery Key option

View https://www.youtube.com/watch?v=J1UMvVuyQAU

Oracle Cloud HCM Table Structure & Others

2023-01-15T22:19:00.005-08:00

Preface

The document is created based on online references related to Oracle Cloud HCM - Human Capital Management Solution which may be changed based on version or implementation cases.

Link to view as Video

Credit

S.M. Assaduzzaman Sarker | ITS Department | STANDARD GROUP

Contents

Tables. 3

HR_ORGANIZATION_UNITS_F_TL. 3

HR_ORGANIZATION_UNITS. 3

PER_LOCATIONS. 3

HR_ALL_POSITIONS_F_TL. 4

PER_ALL_PEOPLE_F. 4

PER_PEOPLE_EXTRA_INFO_F. 4

PER_ASSIGNMENT_SUPERVISORS_F. 4

PER_GRADES_F. 5

PER_IMAGES. 5

PER_PERSONS. 5

PER_PERSON_NAMES_F. 5

PER_PEOPLE_LEGISLATIVE_F_V.. 5

PER_PHONES. 6

PER_RELIGIONS. 6

PER_ALL_PEOPLE_F_V.. 6

PER_ASSIGNMENT_SUPERVISORS_F. 6

PER_ALL_PEOPLE_F_V.. 6

PER_ADDRESSES_F. 6

PER_PAYROLL_STATUTORY_UNITS. 7

PER_PERSON_ADDR_USAGES_F. 7

HCM_EXTENDED_LOOKUP_CODES_B. 7

PER_DEPARTMENTS. 8

PER_EMAIL_ADDRESSES_V.. 8

PER_JOBS. 8

CMP_SALARY. 8

PER_NATIONAL_IDENTIFIERS. 8

PER_ASSIGNMENTS_F. 8

PER_CONTACT_RELSHIPS_F. 9

PER_CONTRACTS_F. 9

PER_LOCATION_EXTRA_INFO_F_. 9

PER_LOCATIONS_. 10

PER_LOCATION_EXTRA_INFO_F. 10

PER_LOCATION_DETAILS_F_TL. 10

PER_LOCATION_DETAILS_F. 10

PER_CONTACT_RELSHIPS_F. 11

PER_CONTRACTS_F. 11

PER_CITIZENSHIPS. 11

PER_PASSPORTS. 11

Queries. 12

Some common query. 12

Employee Profile Query: 15

Query of Degree. 18

Referance. 19

Tables

HR_ORGANIZATION_UNITS_F_TL

OR HR_ALL_ORGANIZATION_UNITS_VL

SELECT ORGANIZATION_ID, NAME FROM HR_ORGANIZATION_UNITS_F_TL

ORGANIZATION_ID	NAME
1	Standard Group Enterprise
300000001955046	Standard Group LE
300000001955046	Standard Group LE
300000001955853	Industrial Engineering
300000001955873	Engineering (Non-Professional)
300000001955878	Engineering (Professional)
300000001955883	Finance & Accounts
300000001955958	Quality
300000001955968	Sales & Marketing
300000001955978	Administration
300000001955988	Technical
300000002502003	Customer Service
300000002502008	Commercial
300000002503113	The Civil Engineers Limited (Unit-2)
300000002503149	Wear Mag Limited
300000002503203	Crazy Fashions Limited

HR_ORGANIZATION_UNITS

select NAME from HR_ORGANIZATION_UNITS WHERE ORGANIZATION_ID = 300000001955046

300000001955001 (STANDARD GROUP)

NAME

Standard Group LE

PER_LOCATIONS

select INTERNAL_LOCATION_CODE from PER_LOCATIONS WHERE LOCATION_ID = 300000008812728

INTERNAL_LOCATION_CODE

Standard Group

HR_ALL_POSITIONS_F_TL

SELECT POSITION_ID, NAME

FROM HR_ALL_POSITIONS_F_TL

WHERE NAME = 'Senior Admin Manager'

NAME	POSITION_ID
Senior Admin Manager	300000009140171
Senior Admin Manager	300000009140151
Senior Admin Manager	300000009146552
Senior Admin Manager	300000009140181

PER_ALL_PEOPLE_F

SELECT PERSON_ID, PERSON_NUMBER FROM PER_ALL_PEOPLE_F WHERE PERSON_NUMBER ='1222513'

PERSON_ID	PERSON_NUMBER
300000007491461	1222513

PERSON_ID	PERSON_NUMBER
300000007499130	1033577(WAHID BHAI)

PER_PEOPLE_EXTRA_INFO_F

SELECT PEI_INFORMATION_CATEGORY,PEI_INFORMATION1,PEI_INFORMATION2

FROM PER_PEOPLE_EXTRA_INFO_F

WHERE PERSON_ID = 300000008793576

PEI_INFORMATION_CATEGORY	PEI_INFORMATION1	PEI_INFORMATION2
HRX_IN_MISCELLANEOUS
SG_CONTACTS	Father	dfghjkl
SG_CONTACTS	Mother	sdfghjk

PER_ASSIGNMENT_SUPERVISORS_F

SELECT PERSON_ID, MANAGER_ID FROM PER_ASSIGNMENT_SUPERVISORS_F

WHERE MANAGER_TYPE= 'LINE_MANAGER' AND PERSON_ID= 300000007499130

MANAGER_ID	PERSON_ID
300000007498154 (AZAD SIR)	300000007499130 (WAHID BHAI)

PER_GRADES_F

SELECT GRADE_CODE, GRADE_ID FROM PER_GRADES_F

GRADE_ID	GRADE_CODE
300000001955508	SMC-M3
300000001955511	S3
300000001955514	SMC-M2
300000001955517	W7
300000001955520	JMC-M9

PER_IMAGES

SELECT IMAGE_ID,IMAGE FROM PER_IMAGES WHERE PERSON_ID =300000007491461

IMAGE_ID	IMAGE
100000004602850	AFLAKJFLKAJFLKACNLKLLJLAKJFLKAJLK…..

PER_PERSONS

SELECT BLOOD_TYPE, TO_CHAR(DATE_OF_BIRTH, 'DD-MON-RR') DATE_OF_BIRTH FROM PER_PERSONS WHERE PERSON_ID =300000007491461

BLOOD_TYPE	DATE_OF_BIRTH
O+	30-12-81

PER_PERSON_NAMES_F

SELECT FULL_NAME, KNOWN_AS SHORT_NAME FROM PER_PERSON_NAMES_F

WHERE PERSON_ID =300000007491461 AND PERSON_NAME_ID = 300000007491465

FULL_NAME	SHORT_NAME
S.M. ASSADUZZAMAN SARKER	ASSADUZZAMAN

PER_PEOPLE_LEGISLATIVE_F_V

SELECT SEX,MARITAL_STATUS,HIGHEST_EDUCATION_LEVEL

FROM PER_PEOPLE_LEGISLATIVE_F_V WHERE PERSON_ID = 300000007491461

SEX	MARITAL_STATUS	HIGHEST_EDUCATION_LEVEL
M	M	M.Sc

PER_PHONES

SELECT PHONE_NUMBER , PHONE_TYPE FROM PER_PHONES WHERE PERSON_ID =300000007491461

PHONE_TYPE	PHONE_NUMBER
W1	01716591619

PER_RELIGIONS

SELECT RELIGION FROM PER_RELIGIONS WHERE PERSON_ID =300000007491461

RELIGION

MUSLIM

PER_ALL_PEOPLE_F_V

SELECT PRIMARY_EMAIL_ID,PRIMARY_PHONE_ID,MAILING_ADDRESS_ID,PRIMARY_NID_ID

FROM PER_ALL_PEOPLE_F_V

WHERE PERSON_ID =300000007491461

PRIMARY_EMAIL_ID	PRIMARY_PHONE_ID	MAILING_ADDRESS_ID	PRIMARY_NID_ID
300000007491481	300000007491482	300000007491474	300000007491484

PER_ASSIGNMENT_SUPERVISORS_F

SELECT *

FROM PER_ASSIGNMENT_SUPERVISORS_F

--WHERE ADDRESS_ID = 300000007491474

PER_ALL_PEOPLE_F_V

ADDRESS & OTHER INFO

PER_ADDRESSES_F

SELECT ADDRESS_LINE_1, ADDRESS_LINE_2,COUNTRY,POSTAL_CODE,REGION_1,REGION_2,REGION_3,TOWN_OR_CITY

FROM PER_ADDRESSES_F WHERE ADDRESS_ID = 300000007491474

ADDRESS_LINE_1	ADDRESS_LINE_2	COUNTRY	POSTAL_CODE	REGION_1	REGION_2	REGION_3	TOWN_OR_CITY
Village: Taluk khutamara	Lalmonirhat	BD	Lalmonirhat Sadar-5500	Lalmonirhat Sadar	Rangpur	Lalmonirhat	Lalmonirhat

PER_PAYROLL_STATUTORY_UNITS

select ORGANIZATION_ID, name from PER_PAYROLL_STATUTORY_UNITS

ORGANIZATION_ID	NAME
300000002817481	Test LE
300000001955001	Standard Group
300000001955001	Standard Group
300000002817481	Test LE

PER_PERSON_ADDR_USAGES_F

SELECT ADDRESS_ID,ADDRESS_TYPE

FROM PER_PERSON_ADDR_USAGES_F

WHERE PERSON_ID = 300000007491461

ADDRESS_ID	ADDRESS_TYPE
300000007491474	HOME
300000007491476	MAIL

HCM_EXTENDED_LOOKUP_CODES_B

SELECT EXTENDED_LOOKUP_CODE_ID,ENTERPRISE_ID,LEGISLATION_CODE,LOOKUP_TYPE, LOOKUP_CODE, EXTENDED_LOOKUP_CODE

FROM HCM_EXTENDED_LOOKUP_CODES_B

WHERE EXTENDED_LOOKUP_CODE_ID IN (300000000252599, 300000000252600,300000000252601, 300000000252602)

EXTENDED_LOOKUP_CODE_ID	ENTERPRISE_ID	LEGISLATION_CODE	LOOKUP_TYPE	LOOKUP_CODE	EXTENDED_LOOKUP_CODE
300000000252599	0	RU	ORA_PER_HIGHEST_EDUCATION_LEVE	40	40
300000000252600	0	RU	ORA_PER_HIGHEST_EDUCATION_LEVE	600	600
300000000252601	0	RU	ORA_PER_HIGHEST_EDUCATION_LEVE	80	80
300000000252602	0	RU	ORA_PER_HIGHEST_EDUCATION_LEVE	ORA_HRX_EDU1	ORA_HRX_EDU1

SELECT * FROM HCM_EXTENDED_LOOKUP_CODES_TL

EXTENDED_LOOKUP_CODE_ID IN (300000000252599, 300000000252600,300000000252601, 300000000252602)

PER_DEPARTMENTS

SELECT NAME, ORGANIZATION_ID FROM PER_DEPARTMENTS WHERE ORGANIZATION_ID = 300000001955938

ORGANIZATION_ID	NAME
300000001955938	Information Technology & Services

PER_EMAIL_ADDRESSES_V

SELECT EMAIL_ADDRESS FROM PER_EMAIL_ADDRESSES_V WHERE PERSON_ID =300000007491461

EMAIL_ADDRESS

assaduzzaman.s@standard-group.com

PER_JOBS

SELECT NAME,JOB_FAMILY_ID FROM PER_JOBS WHERE JOB_ID=300000008328813

NAME	JOB_FAMILY_ID
System Analyst	300000006583496

CMP_SALARY

SELECT SALARY_AMOUNT, CURRENCY_CODE, SALARY_BASIS_CODE

FROM CMP_SALARY

WHERE PERSON_ID =300000007491461

SALARY_AMOUNT	SALARY_BASIS_CODE	CURRENCY_CODE
10000	MONTHLY	BDT

PER_NATIONAL_IDENTIFIERS

SELECT NATIONAL_IDENTIFIER_NUMBER

FROM PER_NATIONAL_IDENTIFIERS

WHERE PERSON_ID =300000007491461

NATIONAL_IDENTIFIER_NUMBER

2696405625007

PER_ASSIGNMENTS_F

Employee History Table

PERSON_ID	CONTENT_TYPE_NAME	CONTENT_ITEM_NAME	SPECIFICAION	GRADE	INSTITUTE	LEVEL_	COM_YEAR
300000007495370	Degrees	M.S.S	Economics	2nd	National University	Masters	2006
300000007495370	Degrees	H.S.C	Science	2nd	Dhaka Board	Higher Secondary	2001
300000007495370	Degrees	S.S.C	Science	1st	Dhaka Board	Secondary	1998
300000007495370	Degrees	B.S.S	Economics	2nd	National University	Graduate	2005

PER_CONTACT_RELSHIPS_F

CONTACT_PERSON_ID	CONTACT_RELATIONSHIP_ID	CREATED_BY
300000008793802	300000008793806	BROTHER
300000008888357	300000008888361	S
300000009039001	300000009039005	IN_FR
300000009130558	300000009130562	IN_FR
300000008767985	300000008767989	BROTHER

PER_CONTRACTS_F

SELECT ASSIGNMENT_ID,CONTRACT_NUMBER,PERSON_ID,PERIOD_OF_SERVICE_ID FROM PER_CONTRACTS_F

PERSON_ID	CONTRACT_NUMBER	PERIOD_OF_SERVICE_ID	ASSIGNMENT_ID
300000008218399	CONT2000006	300000008305199	300000008305200
300000008319872	CONT2000007	300000008319887	300000008319888
300000008674906	CONT2000009	300000008674910	300000008674911
300000008794269	CONT2000051	300000008794273	300000008794274
300000008862119	CONT1000002	300000008862128	300000008862129

PER_LOCATION_EXTRA_INFO_F_

NO DATA

PER_LOCATIONS_

NO DATA

PER_LOCATION_EXTRA_INFO_F

May be valid data

PER_LOCATION_DETAILS_F_TL

SELECT LOCATION_DETAILS_ID, LOCATION_CODE,LOCATION_NAME DESCRIPTION FROM PER_LOCATION_DETAILS_F_TL

LOCATION_DETAILS_ID	LOCATION_CODE	DESCRIPTION
300000001955242	CEC-2	CEC-2
300000001955260	CEL-WOVEN	CEL-WOVEN
300000009277776	Test Location	Test Location
300000001955272	SFL	SFL
300000001955266	SDL	SDL

PER_LOCATION_DETAILS_F

SELECT LOCATION_DETAILS_ID, LOCATION_ID, MAIN_ADDRESS_ID

FROM PER_LOCATION_DETAILS_F

LOCATION_DETAILS_ID	LOCATION_ID	MAIN_ADDRESS_ID
300000001955242	300000001955241	300000001955244
300000001955260	300000001955259	300000001955262
300000009277776	300000009277777	300000009277779
300000001955272	300000001955271	300000001955274
300000001955266	300000001955265	300000001955268

SELECT LOCATION_ID,LOC_ADDRESS_USAGE_ID, ADDRESS_USAGE_TYPE

FROM PER_LOC_ADDRESS_USAGES_F

LOCATION_ID	LOC_ADDRESS_USAGE_ID	ADDRESS_USAGE_TYPE
300000001955241	300000001955243	MAIN
300000001955259	300000001955261	MAIN
300000006721016	300000006721017	MAIN
300000009277777	300000009277778	MAIN
300000001955265	300000001955267	MAIN

PER_CONTACT_RELSHIPS_F

CONTACT_PERSON_ID	CONTACT_RELATIONSHIP_ID	CREATED_BY
300000008793802	300000008793806	BROTHER
300000008888357	300000008888361	S
300000009039001	300000009039005	IN_FR
300000009130558	300000009130562	IN_FR
300000008767985	300000008767989	BROTHER

PER_CONTRACTS_F

SELECT ASSIGNMENT_ID,CONTRACT_NUMBER,PERSON_ID,PERIOD_OF_SERVICE_ID FROM PER_CONTRACTS_F

PERSON_ID	CONTRACT_NUMBER	PERIOD_OF_SERVICE_ID	ASSIGNMENT_ID
300000008218399	CONT2000006	300000008305199	300000008305200
300000008319872	CONT2000007	300000008319887	300000008319888
300000008674906	CONT2000009	300000008674910	300000008674911
300000008794269	CONT2000051	300000008794273	300000008794274
300000008862119	CONT1000002	300000008862128	300000008862129

PER_CITIZENSHIPS

select CITIZENSHIP_ID, LEGISLATION_CODE from per_citizenships

where PERSON_ID = 300000007499130

CITIZENSHIP_ID	LEGISLATION_CODE
300000009236083	BD

PER_PASSPORTS

SELECT PASSPORT_NUMBER,PASSPORT_TYPE,ISSUE_DATE,EXPIRATION_DATE,ISSUING_AUTHORITY,ISSUING_COUNTRY,ISSUING_LOCATION,PROFESSION

FROM PER_PASSPORTS

WHERE PERSON_ID = 300000007499130

PASSPORT_NUMBER	PASSPORT_TYPE	ISSUE_DATE	EXPIRATION_DATE	ISSUING_AUTHORITY	ISSUING_COUNTRY	ISSUING_LOCATION	PROFESSION
34567890

Queries

Some common query

SELECT DISTINCT ENTITY_TYPE

FROM PER_ACTION_OCCURRENCES

ENTITY_TYPE

Salary

Position

Grade

Location

Organization

Job

BEN_PL_TYP_F

NO VALID DATA

PER_RATES_F

NO DATA

PAY_ENTRY_PCT_DIST

NO DATA

SELECT ORGANIZATION_ID, ESTABLISHMENT_ID,LOCATION_ID

FROM HR_ALL_ORGANIZATION_UNITS_F

ORGANIZATION_ID	ESTABLISHMENT_ID	LOCATION_ID
1
300000001955046	300000001950038
300000001955046	300000001950038
300000001955853
300000001955868

SELECT NAME, ORGANIZATION_ID

FROM HR_ORGANIZATION_UNITS_F_TL

NAME	ORGANIZATION_ID
Standard Group Enterprise	1
Standard Group LE	300000001955046
Standard Group LE	300000001955046
Industrial Engineering	300000001955853
Engineering (Non-Professional)	300000001955873

SELECT DISTINCT ORG_INFORMATION_ID, ORG_INFORMATION_CONTEXT,

ORGANIZATION_ID

FROM HR_ORGANIZATION_INFORMATION_F

ORG_INFORMATION_ID	ORG_INFORMATION_CONTEXT	ORGANIZATION_ID
300000006724780	FUN_BUSINESS_UNIT	300000006724777
300000002501983	PER_WORK_DAY_INFO	300000001955001
300000002503960	PER_ORG_MANAGER_INFO	300000002503958
300000006711474	PER_ORG_MANAGER_INFO	300000001955978
300000006724672	FUN_BUSINESS_UNIT	300000006724669

SELECT DISTINCT ORG_UNIT_CLASSIFICATION_ID,ACTION_OCCURRENCE_ID,CLASSIFICATION_CODE,

LEGISLATION_CODE,STATUS,ORGANIZATION_ID,SET_ID,CATEGORY_CODE

FROM HR_ORG_UNIT_CLASSIFICATIONS_F

ORG_UNIT_CLASSIFICATION_ID	ACTION_OCCURRENCE_ID	CLASSIFICATION_CODE	LEGISLATION_CODE	STATUS	ORGANIZATION_ID	SET_ID	CATEGORY_CODE
300000001955934	300000001955932	DEPARTMENT		A	300000001955933	300000000001412	DEPARTMENT
300000001955959	300000001955957	DEPARTMENT		A	300000001955958	300000000001412	DEPARTMENT
300000001955979	300000006711477	DEPARTMENT		A	300000001955978	300000000001412	DEPARTMENT
300000002503096	300000002503097	FUN_BUSINESS_UNIT		A	300000002503095		FUN_BUSINESS_UNIT
300000002503114	300000002503115	FUN_BUSINESS_UNIT		A	300000002503113		FUN_BUSINESS_UNIT

SELECT DISTINCT CLASSIFICATION_CODE,CATEGORY_CODE

FROM HR_ORG_UNIT_CLASSIFICATIONS_F

CLASSIFICATION_CODE	CATEGORY_CODE
HCM_TRU	HCM_TRU
HCM_LRU	HCM_LRU
HCM_REPORTING_ESTABLISHMENT	HCM_REPORTING_ESTABLISHMENT
INV	NO_EFF_CATEGORY
DEPARTMENT	DEPARTMENT
HCM_TRU	HCM_TRU_US
HCM_LEMP	HCM_LEMP_US
FUN_BUSINESS_UNIT	FUN_BUSINESS_UNIT
HCM_PSU	HCM_PSU_US
HCM_PSU	HCM_PSU
ENTERPRISE	ENTERPRISE
CONTRACTS_RESOURCE_ORGANIZATION	NO_EFF_CATEGORY
HCM_LEMP	HCM_LEMP

select LOOKUP_TYPE,LOOKUP_CODE,MEANING

FROM HR_LOOKUPS

WHERE LOOKUP_TYPE = 'CONTACT'

LOOKUP_TYPE	LOOKUP_CODE	MEANING
CONTACT	A	Adopted child
CONTACT	BROTHER	Brother
CONTACT	C	Child
CONTACT	DP	Domestic partner
CONTACT	EMRG	Emergency

SELECT CF.CATEGORY_CODE, OI.ORG_INFORMATION_ID, OI.ORG_INFORMATION_CONTEXT, O.NAME , AL.ESTABLISHMENT_ID

FROM HR_ORG_UNIT_CLASSIFICATIONS_F CF, HR_ORGANIZATION_INFORMATION_F OI, HR_ORGANIZATION_UNITS_F_TL O,

HR_ALL_ORGANIZATION_UNITS_F AL

WHERE CF.CATEGORY_CODE =' FUN_BUSINESS_UNIT '

AND CF.ORGANIZATION_ID = OI.ORGANIZATION_ID

AND CF.ORGANIZATION_ID = O.ORGANIZATION_ID

AND CF.ORGANIZATION_ID = AL.ORGANIZATION_ID

ORG_INFORMATION_ID	ORG_INFORMATION_CONTEXT	NAME	ESTABLISHMENT_ID
300000002503188	FUN_BUSINESS_UNIT	Adhunik Poshak Shilpa Limited
300000002503314	FUN_BUSINESS_UNIT	Kazipur Fashions Limited (Unit-1)
300000002503404	FUN_BUSINESS_UNIT	Standard Group Limited (Baraiderchala)
300000006724650	FUN_BUSINESS_UNIT	Standard Stitches (Gmts Unit) Limited
300000006724780	FUN_BUSINESS_UNIT	Adhunik Plastic Industries Limited

SELECT CF.CATEGORY_CODE, OI.ORG_INFORMATION_ID, OI.ORG_INFORMATION_CONTEXT, O.NAME , AL.ESTABLISHMENT_ID

FROM HR_ORG_UNIT_CLASSIFICATIONS_F CF, HR_ORGANIZATION_INFORMATION_F OI, HR_ORGANIZATION_UNITS_F_TL O,

HR_ALL_ORGANIZATION_UNITS_F AL

WHERE CF.CATEGORY_CODE = 'HCM_REPORTING_ESTABLISHMENT'

AND CF.ORGANIZATION_ID = OI.ORGANIZATION_ID

AND CF.ORGANIZATION_ID = O.ORGANIZATION_ID

AND CF.ORGANIZATION_ID = AL.ORGANIZATION_ID

ORG_INFORMATION_ID	ORG_INFORMATION_CONTEXT	NAME	CATEGORY_CODE
300000002503866	PER_ORG_MANAGER_INFO	Peak Fashions Limited	HCM_REPORTING_ESTABLISHMENT
300000002503938	PER_ORG_MANAGER_INFO	Adhunik Poshak Shilpa Limited	HCM_REPORTING_ESTABLISHMENT
300000002746079	PER_ORG_MANAGER_INFO	Standard Fashions Limited	HCM_REPORTING_ESTABLISHMENT
300000002503888	PER_ORG_MANAGER_INFO	Wear Mag Limited	HCM_REPORTING_ESTABLISHMENT
300000002503960	PER_ORG_MANAGER_INFO	Crazy Fashions Limited	HCM_REPORTING_ESTABLISHMENT

SELECT ORGANIZATION_ID , NAME FROM HR_ORGANIZATION_UNITS_F_TL

WHERE ORGANIZATION_ID IN (

300000001955938, -- ORGANIZATION_ID--

300000001955001, --LEGAL_ENTITY_ID --

300000002503131, -- BUSINESS_UNIT_ID -

300000002503842, -- ESTABLISHMENT_ID--

NULL -- SET_OF_BOOKS_ID

)

ORGANIZATION_ID	NAME
300000001955001	Standard Group
300000001955001	Standard Group
300000001955938	Information Technology & Services
300000002503131	The Civil Engineers Limited (Woven Unit)
300000002503842	The Civil Engineers Limited

Employee Profile Query:

/* Formatted on 11/19/2020 12:57:56 PM (QP5 v5.215.12089.38647) */

SELECT DISTINCT

M.PERSON_ID,

M.ASSIGNMENT_NUMBER,

N.FULL_NAME,

N.KNOWN_AS SHORT_NAME,

M.ASSIGNMENT_NAME DESIGNATION,

M.ASSIGNMENT_ID,

M.TIME_NORMAL_FINISH OUTTIME,

M.TIME_NORMAL_START INTIME,

M.ASSIGNMENT_STATUS_TYPE ACTIVE_INACTIVE,

M.ASS_ATTRIBUTE2 LOCATION,

M.ASS_ATTRIBUTE1 FLOOR_,

M.ASS_ATTRIBUTE3 SAL_TYPE,

PP.BLOOD_TYPE,

TO_CHAR (PP.DATE_OF_BIRTH, 'DD-MON-RR') DATE_OF_BIRTH,

CASE

WHEN P.SEX = 'M' THEN 'Male'

WHEN P.SEX = 'F' THEN 'Female'

ELSE NULL

END

SEX,

CASE WHEN P.MARITAL_STATUS = 'M' THEN 'Married' ELSE 'Unmarried' END

MARITAL_STATUS,

P.HIGHEST_EDUCATION_LEVEL,

B.NAME BUSINESSUNIT,

BB.NAME SALARYUNIT,

E.EMAIL_ADDRESS,

--PH.PHONE_NUMBER ,

(SELECT COUNTRY_CODE_NUMBER || '-' || PHONE_NUMBER

FROM PER_PHONES PH

WHERE PHONE_TYPE = 'WM' AND PH.PERSON_ID = P.PERSON_ID)

OFFICE_MOBILE_PHONE,

(SELECT COUNTRY_CODE_NUMBER || '-' || PHONE_NUMBER

FROM PER_PHONES PH1

WHERE PHONE_TYPE = 'W1' AND PH1.PERSON_ID = P.PERSON_ID)

OFFICE_DESK_PHONE,

(SELECT COUNTRY_CODE_NUMBER || '-' || PHONE_NUMBER

FROM PER_PHONES PH2

WHERE PHONE_TYPE = 'H1' AND PH2.PERSON_ID = P.PERSON_ID)

HOME_PHONE,

R.RELIGION,

G.GRADE_CODE,

O.NAME ORGANIZATION_UNITS,

AD.ADDRESS_LINE_1,

AD.ADDRESS_LINE_2,

AD.COUNTRY,

AD.POSTAL_CODE,

AD.REGION_1,

AD.REGION_2,

AD.REGION_3,

AD.TOWN_OR_CITY,

MAIL.ADDRESS_LINE_1 ADDRESS_LINE_11,

MAIL.ADDRESS_LINE_2 ADDRESS_LINE_22,

MAIL.COUNTRY COUNTRY1,

MAIL.POSTAL_CODE POSTAL_CODE1,

MAIL.REGION_1 REGION_11,

MAIL.REGION_2 REGION_21,

MAIL.REGION_3 REGION_31,

MAIL.TOWN_OR_CITY TOWN_OR_CITY1,

SNM.MANAGER_NAME,

NI.NATIONAL_IDENTIFIER_NUMBER,

TO_CHAR (PSER.DATE_START, 'DD-Mon-RR') HIRE_DATE,

PASS.PASSPORT_NUMBER,

MOTHER_IN.MOTHER_INFO,

FATHER_IN.FATHER_INFO,

fn.full_name FATHER_name,

sn.full_name spouse_name

FROM PER_PERSONS PP,

PER_PERSON_NAMES_F N,

--PER_PHONES PH,

PER_RELIGIONS R,

PER_ADDRESSES_F AD,

PER_ALL_PEOPLE_F_V AL,

PER_ADDRESSES_F MAIL,

HR_ORGANIZATION_UNITS_F_TL B,

HR_ORGANIZATION_UNITS_F_TL BB,

PER_NATIONAL_IDENTIFIERS NI,

PER_PERIODS_OF_SERVICE PSER,

PER_PERSON_ADDR_USAGES_F AU,

PER_PERSON_ADDR_USAGES_F AUMAIL,

PER_ALL_ASSIGNMENTS_M M,

PER_PEOPLE_LEGISLATIVE_F_V P,

PER_EMAIL_ADDRESSES_V E,

PER_GRADES_F G,

HR_ORGANIZATION_UNITS O,

(SELECT SUP.PERSON_ID, SN.FULL_NAME MANAGER_NAME

FROM PER_ASSIGNMENT_SUPERVISORS_F SUP, PER_PERSON_NAMES_F SN

WHERE SUP.MANAGER_TYPE = 'LINE_MANAGER'

AND SUP.MANAGER_ID = SN.PERSON_ID

AND SN.NAME_TYPE = 'GLOBAL') SNM,

(SELECT FF.PERSON_ID, FF.PEI_INFORMATION2 MOTHER_INFO

FROM PER_PEOPLE_EXTRA_INFO_F FF

WHERE PEI_INFORMATION1 = 'Mother'

AND PEI_INFORMATION_CATEGORY = 'SG_CONTACTS') MOTHER_IN,

(SELECT FF.PERSON_ID, FF.PEI_INFORMATION2 FATHER_INFO

FROM PER_PEOPLE_EXTRA_INFO_F FF

WHERE PEI_INFORMATION1 = 'Mother'

AND PEI_INFORMATION_CATEGORY = 'SG_CONTACTS') FATHER_IN,

(SELECT p.FULL_NAME, r.PERSON_ID

FROM PER_ALL_PEOPLE_F f,

PER_PERSON_NAMES_F p,

PER_CONTACT_RELSHIPS_F r

WHERE p.person_id = f.person_id

AND r.CONTACT_PERSON_ID = p.person_id

AND p.NAME_TYPE = 'GLOBAL'

AND CONTACT_TYPE = 'IN_FR') fn,

(SELECT p.FULL_NAME, r.PERSON_ID

FROM PER_ALL_PEOPLE_F f,

PER_PERSON_NAMES_F p,

PER_CONTACT_RELSHIPS_F r

WHERE p.person_id = f.person_id

AND r.CONTACT_PERSON_ID = p.person_id

AND p.NAME_TYPE = 'GLOBAL'

AND CONTACT_TYPE = 'DP') sn,

PER_PASSPORTS PASS

WHERE --M.PERSON_ID= 300000007491461 --300000007491461

--N.PERSON_NAME_ID = 300000007491465

--AND AD.ADDRESS_ID = 300000007491474

N.NAME_TYPE = 'GLOBAL'

AND M.PRIMARY_FLAG = 'Y'

AND ASSIGNMENT_TYPE IN ('E', 'C')

--and ph.PHONE_TYPE ='W1'

AND SYSDATE BETWEEN M.EFFECTIVE_START_DATE AND M.EFFECTIVE_END_DATE

AND M.BUSINESS_UNIT_ID = B.ORGANIZATION_ID

AND M.ESTABLISHMENT_ID = BB.ORGANIZATION_ID

AND M.PERSON_ID = P.PERSON_ID

AND M.PERSON_ID = NI.PERSON_ID

AND M.PERSON_ID = AL.PERSON_ID

AND M.PERSON_ID = MOTHER_IN.PERSON_ID(+)

AND M.PERSON_ID = FATHER_IN.PERSON_ID(+)

AND M.PERSON_ID = fn.PERSON_ID(+)

AND M.PERSON_ID = sn.PERSON_ID(+)

AND M.PERSON_ID = PSER.PERSON_ID

AND M.PERSON_ID = SNM.PERSON_ID(+)

AND M.PERSON_ID = N.PERSON_ID

AND M.PERSON_ID = PASS.PERSON_ID(+)

AND M.PERSON_ID = R.PERSON_ID

AND PP.PERSON_ID = P.PERSON_ID

--AND PH.PERSON_ID=P.PERSON_ID

AND M.PERSON_ID = E.PERSON_ID

AND M.PERSON_ID = AU.PERSON_ID

AND M.ASS_ATTRIBUTE_NUMBER1 = :P_EMP_ID

AND M.PERSON_ID = AUMAIL.PERSON_ID

AND M.ORGANIZATION_ID = O.ORGANIZATION_ID

AND AU.ADDRESS_ID = AD.ADDRESS_ID(+)

AND AUMAIL.ADDRESS_ID = MAIL.ADDRESS_ID(+)

AND AUMAIL.ADDRESS_TYPE = 'MAIL'

AND AU.ADDRESS_TYPE = 'HOME'

AND M.GRADE_ID = G.GRADE_ID

--AND O.NAME ='Information Technology & Services'

ORDER BY M.ASSIGNMENT_NAME

Query of Degree

/* Formatted on 11/19/2020 12:58:52 PM (QP5 v5.215.12089.38647) */

SELECT CTP.CONTENT_TYPE_NAME,

CTI.NAME AS CONTENT_ITEM_NAME,

HPI.ITEM_TEXT240_1 SPECIFICAION,

HPI.ITEM_TEXT240_6 GRADE,

HPI.ITEM_TEXT2000_1 INSTITUTE,

HPI.ITEM_TEXT30_8 LEVEL_,

HPI.ITEM_NUMBER_8 COM_YEAR,

HPB.PERSON_ID

FROM HRT_PROFILES_B HPB,

HRT_PROFILE_ITEMS HPI,

HRT_CONTENT_TYPES_TL CTP,

HRT_CONTENT_ITEMS_TL CTI

WHERE HPB.PROFILE_ID = HPI.PROFILE_ID

AND HPI.CONTENT_TYPE_ID = CTP.CONTENT_TYPE_ID(+)

AND HPI.CONTENT_ITEM_ID = CTI.CONTENT_ITEM_ID(+)

--AND HPB.PERSON_ID = 300000007499130

AND CONTENT_TYPE_NAME = 'Degrees'

ORDER BY HPI.ITEM_NUMBER_8 DESC

Referance

HCM Detail documentation

https://docs.oracle.com/en/cloud/saas/human-resources/20b/faigh/person.html#FAIGH1115189

hcm table structure

https://docs.oracle.com/en/cloud/saas/human-resources/20c/oedmh/overview.html#overview

https://docs.oracle.com/en/cloud/saas/human-resources/20b/oedmh/global-human-resources.html#tables

https://docs.oracle.com/en/cloud/saas/human-resources/20c/oedmh/global-payroll.html#payallpayrollsf-22193

https://docs.oracle.com/en/cloud/saas/human-resources/20b/oedmh/global-human-resources.html#global-human-resources

SOME COMMONLY USED QUERIES IN ORACLE HCM CLOUD

https://www.apps2fusion.com/oracle-fusion-online-training/fusion-applications/fusion-payroll/2343-some-commonly-used-queries-in-oracle-hcm-cloud

Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher

https://docs.oracle.com/middleware/12211/bip/BIPDM/GUID-9A9233A9-1D48-4D50-AB00-7FDD29EB0E23.htm#BIPDM534

Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher

https://docs.oracle.com/middleware/12211/bip/BIPRD/GUID-58D7E662-146A-44AA-86F9-111E2C6DB3A5.htm#BIPRD2395

Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher

https://docs.oracle.com/middleware/12211/bip/BIPRD/GUID-58D7E662-146A-44AA-86F9-111E2C6DB3A5.htm#BIPRD2392

image

http://oraclemasterminds.blogspot.com/2016/09/advanced-rtf-template-image-chart-and.html

https://oraclecloudhcmtechfunc.blogspot.com/2019/02/how-to-get-profile-images-in-otbi-report.html

https://emqu-test.fa.ap1.oraclecloud.com/hcmUI/personImage?personId=300000007499130

http://www.ebiztechnics.net/2017/04/oracle-bi-publisher-reports-generating-dynamic-images.html

https://stackoverflow.com/questions/40608525/blob-image-at-report-header

Customizing OTBI Enterprise Reports and Dashboards

https://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/otbi_ent/rel10/analyses_dashboards/analyses_dashboards.html#section2

Oracle EBS Functional Financials - Accounts Receivable setups - R12

2022-08-05T01:25:00.001-07:00

1. Oracle Receivables -> Setup -> System ->System Options

a. Receivable GL related setup

b. Transaction and Customer-related setup

c. Miscellaneous

e.g., Vision Operations (USA)

2. Oracle Receivables -> Setup -> Transactions -> Transaction Types

a. Unit, Class, Transaction status, Invoice Type mapping

b. Receivable Account GL

c. Revenue Account GL

d. Tax Account GL

e. other GL setups

e.g. Auto SRV CHG Invoice , BR - Drawee Issued

3. Oracle Receivables -> Setup -> Transactions -> Sources

a. Transaction Source naming & Setup

e.g. Intercompany, Manual Order Entry

4. Oracle Receivables -> Setup -> Transactions -> Memo Lines

a. Standard Memo Lines ( Name, Type, Revenue Account definition)

5. Oracle Receivables -> Setup -> Transactions -> Accounting Rules

a. Type and Schedule

6. Oracle Receivables -> Setup -> Transactions -> Payment Terms

a. Billing Cycle, Payment schedule, Amount, Discounts

7. Oracle Receivables -> Setup -> Transactions -> AutoAccounting

a. Operating Unit, Type and Segment setup

8. Oracle Receivables -> Setup -> Receipts -> Receivable Activities

a. Operating Unit, Type, Accounting Mapping

9. Oracle Receivables -> Setup -> Receipts -> Receipt Classes

a. Collection Method, Remittance Method, Clearance Method mapping

b. Remittance Banks and GL Account Mapping

10. Oracle Receivables -> Setup -> Receipts -> Receipt Sources

a. Automatic & Manual, Batch numbering

11. GL Super User Responsibility -> Setup -> Financials -> Flexfields -> Key -> Values

a. Find Key Flexfield Segment (First Window)

Application: General Ledger

Title: GL Ledger FlexField

b. Segment values ( Second Window)

Oracle Receivables Implementation Guide

https://docs.oracle.com/cd/E18727-01/doc.121/e13510/T447343T355914.htm

https://www.oracleerpappsguide.com/2013/01/r12-receivables-setups-ar-in-oracle-apps.html

BI Publisher Advance Features

2022-05-21T23:17:00.003-07:00

TO DECLARE/SET A VARIABLE VALUE:

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

<?xdoxslt:set_variable($_XDOCTX, 'variable name', value)?>

<?xdoxslt:set_variable($_XDOCTX,’V_EMP’,0)?>

TO PERFORM CALCULATIONS.

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

<?xdoxslt:set_variable($_XDOCTX, 'x', xdoxslt:get_variable($_XDOCTX, 'x' + 1)?>

This sets the value of variable 'x' to its original value plus 1,

much like using "x = x + 1".

The $_XDOCTX specifies the global document context for the variables.

In a multi-threaded environment there may be many transformations occurring

at the same time, therefore the variable must be assigned to a single

transformation.

<?xdoxslt:set_variable($_XDOCTX,’V_EMP’,xdoxslt:get_variable($_XDOCTX,’V_EMP’) + EMP)?>

TO RETRIEVE A VARIABLE VALUE:

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

<?xdoxslt:get_variable($_XDOCTX, 'variable name')?>

<?xdoxslt:get_variable($_XDOCTX,’V_EMP’)?>

IF THEN ELSE

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

<?xdofx:if EMP > 2 then ’Higher’

else

if EMP <2 then ’Lower’

else

’Equal’

end if?>

ROW / COLUMN HIGHLIGHTING

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

<?if@row:position() mod 2=0?>

<xsl:attribute name="background-color" xdofo:ctx="incontext">green</xsl:attribute><?end if?>

EXTENDED SQL AND XSL FUNCTIONS

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

<?xdofx:rpad(concat(FIRST_NAME,LAST_NAME),30, 'x')?>

Ref: Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher

https://docs.oracle.com/middleware/12212/bip/BIPRD/GUID-B39D756A-C54D-4C64-A822-DD2CFFC3A54E.htm#BIPRD3160

CREATION OF PAGE TOTAL ELEMENT

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

<?add-page-total:TotalFieldName;'element'?>

Example:

<?add-page-total:totalemp;'EMP'?>

DISPLAYING PAGE TOTAL

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

<?show-page-total:TotalFieldName;'number-format'?>

Example:

<?show-page-total:totalemp;$#,##0.00;($#,##0.00)?>

CREATING MULTIPLE OR COMPLEX HEADERS AND FOOTERS

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

If the template requires multiple headers and footers, then create them by using BI Publisher tags to define the body area of the report. You may also want to use this method if the header and footer contain complex objects that you want to place in form fields. When you define the body area, the elements occurring before the beginning of the body area compose the header. The elements occurring after the body area compose the footer.

Use the following tags to enclose the body area of the report:

<?start:body?>

<?end body?>

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

The Payables Invoice Register contains a simple header and footer and therefore does not require the start body/end body tags. However, if you wanted to add another header to the template, define the body area.

To define the body area:

Insert <?start:body?> before the Suppliers group tag:

<?for-each:G_VENDOR_NAME?>

Insert <?end body?> after the Suppliers group closing tag:

<?end for-each?>

https://docs.oracle.com/cd/E29505_01/bi.1111/e22254/create_rtf_tmpl.htm#do_handf

https://www.youtube.com/watch?v=hzZoVsWTF8Q

Serial, Sorting, Horizontal total, vertical total and Grand total in Oracle BI Publisher

2022-03-21T03:27:00.010-07:00

ORACLE BI PUBLISHER

A. Please learn the steps of creating Matrix report from previous video

B. Now we will discuss about Position, Sorting,

1. Position:

<?position()?>

2. Sorting:

<?sort:DNAME;'ascending'?><?DNAME?>

<?sort:JOB;'ascending';data-type='text'?><?JOB?>

3. Horizotal group Total

<?sum(current-group()/EMP)?>

4. Vertical group Total

<?for-each-group@cell://G_DNAME;./JOB?>

<?sum(current-group()/EMP)?>

5. Grant Total

<?sum(EMP)?>

How to create Matrix report / Pivot Report using Oracle BI Publisher

2021-11-12T21:18:00.000-08:00

How to create Matrix report / Pivot Report using Oracle BI Publisher

Step by step instruction:

1. Make data model using Oracle Reports Developer

2. Generate a XML output

3. Create a blank document in MSWord

4. Load the XML file from BI Publisher tab

5. Create a simple report using the Table Wizard

6. Change the Properties of the column as per instruction

<?for-each-group:G_DNAME;./DNAME?><?variable@incontext:DNAME_;DNAME?>

<?for-each-group@column:G_DNAME;./JOB?>

<?for-each-group@cell://G_DNAME;./JOB?><?if:count(current-group()[DNAME=$DNAME_])?><?end if?>

<?current-group()[DNAME=$DNAME_]/EMP?>

How to uninstall / completely remove Oracle 11g

2021-04-18T19:31:00.004-07:00

Uninstall all Oracle components using the Oracle Universal Installer (OUI).

Run regedit.exe and delete the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE key. This contains registry entires for all Oracle products.

Delete any references to Oracle services left behind in the following part of the registry: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Ora* It should be pretty obvious which ones relate to Oracle.

Reboot your machine.

Delete the "C:\Oracle" directory, or whatever directory is your ORACLE_BASE.

Delete the "C:\Program Files\Oracle" directory.

Empty the contents of your "C:\temp" directory.

Empty your recycle bin.

Calling additional attention to some great comments that were left here:

Be careful when following anything listed here (above or below), as doing so may remove or damage any other Oracle-installed products.

For 64-bit Windows (x64), you need also to delete the HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE key from the registry.

Clean-up by removing any related shortcuts that were installed to the Start Menu.

Clean-up environment variables:

Consider removing %ORACLE_HOME%.

Remove any paths no longer needed from %PATH%.

This set of instructions happens to match an almost identical process that I had reverse-engineered myself over the years after a few messed-up Oracle installs, and has almost always met the need.

Note that even if the OUI is no longer available or doesn't work, simply following the remaining steps should still be sufficient.

https://stackoverflow.com/questions/8450726/how-to-uninstall-completely-remove-oracle-11g-client

How to exclude tables during import using impdp

2021-04-18T19:27:00.007-07:00

How to exclude tables during import using impdp

grant dba to labserp identified by labserp;

CREATE TABLESPACE LABSERP_TS
DATAFILE 'LABSERP_TS.dbf'
SIZE 100M
REUSE
AUTOEXTEND ON NEXT 10M ;

CREATE OR REPLACE DIRECTORY test_dir1 AS 'E:\personal\';

GRANT READ, WRITE ON DIRECTORY test_dir1 TO labserp;

impdp labserp/labserp exclude=TABLE:\"IN (\'DO_AUDIT_DETAIL\', \'PR_DAY_WISE_ATTENDENCE_040720\', \'PR_DAY_WISE_ATTENDENCE_070720\', \'PR_DAY_WISE_ATTENDENCE_090720\', \'PR_DAY_WISE_ATTENDENCE_07073PM\', \'AC_DATA_LOG_BK\')\" directory=TEST_DIR1 dumpfile=expdp_28-07-Tue_23.00_PM.dmp logfile=impdpEMP_DEPT.log

Oracle EBS Vision Instance Installation on VirtualBox

2020-12-13T01:43:00.003-08:00

Download and install VirtualBox There are possibilities of different versions based on your Operating System. Here we worked with VirtualBox-6.1.14-140239-Win

B. Download EBS Software

On the Oracle Software Delivery Cloud page: https://edelivery.oracle.com/osdc/faces/SoftwareDelivery

1. Login with your credentials

2. Search by Oracle VM Virtual Appliance for Oracle E-Business Suite

3. Select Oracle VM Virtual Appliance for Oracle E-Business Suite 12.2.6

4. Continue in the Download Queue

5. Accept Licence & Download

6. A software for download will be downloaded name eg.Oracle_SSN_DLM_12130645.exe

7. Unzip All the files by any utility like winzip or 7zip etc.

8. Merge the unzip files with command prompt in the following way

type Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova.00 Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova.01 Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova.02 Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova.03 Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova.04 Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova.05 Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova.06 Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova.07 Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova.08 Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova.09 Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova.10 Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova.11 Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova.12 Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova.13 Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova.14 Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova.15 Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova.16 Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova.17 Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova.18 > Oracle-E-Business-Suite-12.2.6_VISION_INSTALL.ova

It takes at least 1 to 2 hours or even more based on your Processor/Memory to get the merged file.

9. Import Appliance

At this stage, you should have at least 400 gigabytes or the process aborts. You may need to delete the zip files to make room for the import.

10.

Adjust RAM memory keep 30% for Main CPU. Also adjust Graphics controller to VMSVGA

For Networking, you can attach Adapter as:

Bridged Adapter (the default setting)

NAT or Host-Only Adapter.

11. Start the VM

Set the IP when it is prompted

12. Login & Password reset

13. EBS demo Application install

At a certain point during the post-install process you will be prompted to:

select whether to add start or stop of the database and application tier processes to the OS boot script.

Enter VISION to configure the Vision Demo instance.

14. Step – 5 Access to E-Business suite VISION.

To access the EBS Application from your website you will have to make minor changes in the

Windows host file by:

Getting the IP address of the VM using ifconfig -a on the terminal.

Edit the file on your Windows, add a line in c:\windows\system32\drivers\etc\hosts

(You may have to temporarily change permissions if you do have Administrator privileges)

<IP address> apps.example.com apps

Use an SSH client, like Putty to ssh to the file system and explore the operating system installation.

To Start the application

/u01/install/APPS/fs1/inst/apps/EBSDB_apps/admin/scripts

./adstrtal.sh apps/apps

Use http://apps.example.com:8000 to access EBS Application

The default passwords are:

SYSADMIN/sysadmin

demo/demo

apps/apps

sys/manager

system/manager

WebLogic/welcome1

All these passwords could be changed but you can keep them as they are until you get used to the Apps.

But if you feel the need to change, connect to the VM through SSH as oracle:

Change directory to: /u01/install/APPS/scripts

You will find a script to change the password for Sysadmin and for the Demo users.

[oracle@apps scripts]$ ./enableSYSADMIN.sh

[oracle@apps scripts]$ ./enableDEMOusers.sh

If you want to access the db

export ORACLE_BASE=/u01/install/APPS

export ORACLE_HOME=/u01/install/APPS/12.1.0

export PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_SID=EBSDB

use sys as sysdba and enter the EBSDB schema.

Change page content based on changing select list

2018-11-11T21:06:00.000-08:00

https://stackoverflow.com/questions/32708157/oracle-apex-change-page-content-based-on-changing-select-list

Oracle Application Server on Windows

2015-08-27T02:40:00.000-07:00

Installation Steps:

1. First create a folder named app_server in any directory
2. go to the software to install and double click install.exe
3. give proper path at the fields
app_server
g:\app_server (Path)
4. follow the step by step wizard

administrator username : ias_admin
instance name : ias_admin
password assad123

5. You have to 'Retry' at any stage like OC4J instance configuration assistant
6.installation complete

Configuration

1.C:\app_server\forms\java\oracle\forms\registry\Registry.dat
app.ui.lovButtons=true
app.ui.requiredFieldVA=true

2. you can copy required files from
https://drive.google.com/folderview?id=0B9MhlLcy4ecVTkJCbWxlcmtoQkE&usp=sharing
C:\app_server\forms
C:\app_server\forms\server
C:\app_server\forms\java

3. C:\app_server\forms\server\formsweb.cfg

[HRD]
form=D:\HRD\FORMS\hrm_project.fmx
userId=hrd/hrd@orcl
workingDirectory=D:\HRD
pageTitle=Lumineux ERP.
baseHTMLjinitiator=webutiljpi.htm
imageBase=Codebase
archive=frmall.jar,fjutilities.jar,rolloverbutton.jar,images.jar,getclientinfo.jar,frm

webutil.jar,jacob.jar,banner.jar,DualPDFPrint.jar
#archive_jini=frmall_jinit.jar,fjutilities.jar,rolloverbutton.jar,images.jar,getclient

info.jar,frmwebutil.jar,jacob.jar
colorScheme=blue
otherparams=useSDI=yes
separateFrame=true
logo=none
background=none
splashScreen=none
#width="style="width: expression(document.body.clientWidth/2+'px');""
#height="style="height: expression(document.body.clientHeight+'px');""
# Parameters related to webutil
WebUtilArchive=frmwebutil.jar,jacob.jar,banner.jar
WebUtilLogging=off
WebUtilLoggingDetail=normal
WebUtilErrorMode=Alert
WebUtilDispatchMonitorInterval=5
WebUtilTrustInternal=true
WebUtilMaxTransferSize=16384
# Parameters related to show progress of jar files loader
java_showprogress=true
pageTitle=Lumineux ERP.
java_preloadjars=frmall_jinit.jar,frmwebutil.jar,jacob.jar,fjutilities.jar,rolloverbut

ton.jar,images.jar,getclientinfo.jar
em_mode=1

4. C:\app_server\reports\conf\rwservlet.properties
copy SERVER=rep_Programmer_app_server

5. C:\app_server\reports\conf\cgicmd.dat

paste it at the end of server ...
TEST: userid=hr/hr@erp SERVER=rep_Programmer_app_server%*

Oracle Application Express (APEX) 5.0 Installation

2015-08-26T22:58:00.000-07:00

SETUP:

Create a new tablespace to act as the default tablespace for APEX.

CREATE TABLESPACE apex DATAFILE '/u01/app/oracle/oradata/ldb/apex02.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M;

Installation

Change directory to the directory holding the unzipped APEX software.

$ cd /u01/app/oracle/product/11.2.0.3/dbhome_1/apex
Connect to SQL*Plus as the SYS user and run the "apexins.sql" script, specifying the relevant tablespace names and image URL.

SQL> CONN sys@pdb1 AS SYSDBA
SQL> -- @apexins.sql tablespace_apex tablespace_files tablespace_temp images
SQL>
SQL> @apexins.sql APEX APEX TEMP /i/

Once complete, change the admin password by running the "apxchpwd.sql" scripts as the SYS user.

SQL> CONN sys@pdb1 AS SYSDBA
SQL> @apxchpwd.sql
Create the APEX_LISTENER and APEX_REST_PUBLIC_USER users by running the "apex_rest_config.sql" script.

SQL> CONN sys@pdb1 AS SYSDBA
SQL> @apex_rest_config.sql

OHS Configuration

Change the password and unlock the APEX_PUBLIC_USER account. This will be used for any Database Access

Descriptors (DADs).

SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY 123 ACCOUNT UNLOCK;

Embedded PL/SQL Gateway (EPG) Configuration

Run the "apex_epg_config.sql" script, passing in the base directory of the installation software as a

parameter.

SQL> CONN sys@pdb1 AS SYSDBA
SQL> @apex_epg_config.sql /u01/app/oracle/product/11.2.0.3/dbhome_1
Unlock the ANONYMOUS account.

SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;
If this is an upgrade to an existing APEX installation, you will also have to run the following script, to

update the images.

SQL> @apxldimg.sql /u01/app/oracle/product/11.2.0.3/dbhome_1
Check the port setting for XML DB Protocol Server.

SQL> SELECT DBMS_XDB.gethttpport FROM DUAL;

GETHTTPPORT
-----------
0

1 row selected.

SQL>
If it is set to "0", you will need to set it to a non-zero value to enable it.

SQL> EXEC DBMS_XDB.sethttpport(8080);

PL/SQL procedure successfully completed.

SQL>

For future reference, to get to the Apex Administration Services:

http://127.0.0.1:8080/apex/apex_admin

n.b.:https://oracle-base.com/articles/misc/oracle-application-express-apex-5-0-installation

Collections in Oracle PL/SQL

2015-08-18T01:46:00.000-07:00

n.b.:https://oracle-base.com/articles/8i/collections-8i#index-by-tables

Oracle uses collections in PL/SQL the same way others languages use arrays. Oracle provices three basic collections,
each with an assortment of methods,

1.Index-by Tables
2.Nested Table
3.Varry

1.Index-by Tables:
i. No upper bounds
ii. the collection is indexed using BINARY_INTEGER

SET SERVEROUTPUT ON ;
Declare
Type arr_number is table of number index by BINARY_INTEGER;
v_empno arr_number;
v_index number;
v_count number:=0;
begin
for i in 1..5 loop
v_empno(i):= i;
v_count := i;
end loop;
for j in 1..v_count loop
dbms_output.put_line (v_empno(j));
end loop;
end;

2.Nested Table
...

3.Varry
...

programming interview questions

2014-10-17T20:43:00.004-07:00

http://placement.freshersworld.com/power-preparation/technical-interview-questions/C-programming-answers-21419

The 7 Layers of the OSI Model

OSI Model
	Data unit	Layer	Function
Host layers	Data	7. Application	Network process to application
		6. Presentation	Data representation, encryption and decryption, convert machine dependent data to machine independent data
		5. Session	Interhost communication, managing sessions between applications
	Segments	4. Transport	Reliable delivery of segments between points on a network.
Media layers	Packet/Datagram	3. Network	Addressing, routing and (not necessarily reliable) delivery of datagrams between points on a network.
	Bit/Frame	2. Data link	A reliable direct point-to-point data connection.
	Bit	1. Physical	A (not necessarily reliable) direct point-to-point data connection.

Commonly Programmer Interview Questions

2014-10-17T20:42:00.002-07:00

What is the difference between declaration and definition of a variable/function
Ans: Declaration of a variable/function simply declares that the variable/function exists somewhere in the program but the memory is not allocated for them. . Coming to the definition, when we define a variable/function, apart from the role of declaration, it also allocates memory for that variable/function. Therefore, we can think of definition as a super set of declaration. (or declaration as a subset of definition). From this explanation, it should be obvious that a variable/function can be declared any number of times but it can be defined only once. (Remember the basic principle that you can’t have two locations of the same variable/function).

   // This is only declaration. y is not allocated memory by this statement 
  extern int y; 

  // This is both declaration and definition, memory to x is allocated by this statement.
  int x;

What are different storage class specifiers in C?
Ans: auto, register, static, extern
What is scope of a variable? How are variables scoped in C?
Ans: Scope of a variable is the part of the program where the variable may directly be accessible. In C, all identifiers are lexically (or statically) scoped. See this for more details.
How will you print “Hello World” without semicolon?
Ans:
int main(void)
{
if (printf(“Hello World”)) ;
}
When should we use pointers in a C program?
1. To get address of a variable
2. For achieving pass by reference in C: Pointers allow different functions to share and modify their local variables.
3. To pass large structures so that complete copy of the structure can be avoided.
C
4. To implement “linked” data structures like linked lists and binary trees.
What is NULL pointer?
Ans: NULL is used to indicate that the pointer doesn’t point to a valid location. Ideally, we should initialize pointers as NULL if we don’t know their value at the time of declaration. Also, we should make a pointer NULL when memory pointed by it is deallocated in the middle of a program.

What is Dangling pointer?
Ans: Dangling Pointer is a pointer that doesn’t point to a valid memory location. Dangling pointers arise when an object is deleted or deallocated, without modifying the value of the pointer, so that the pointer still points to the memory location of the deallocated memory. Following are examples.

// EXAMPLE 1

int *ptr = (int *)malloc(sizeof(int));

.............

.............

free(ptr); 

// ptr is a dangling pointer now and operations like following are invalid

*ptr = 10;  // or printf("%d", *ptr);

// EXAMPLE 2

int *ptr = NULL

{

   int x  = 10;

   ptr = &x;

}

// x goes out of scope and memory allocated to x is free now.

// So ptr is a dangling pointer now.

What is memory leak? Why it should be avoided
Ans: Memory leak occurs when programmers create a memory in heap and forget to delete it. Memory leaks are particularly serious issues for programs like daemons and servers which by definition never terminate.

/* Function with memory leak */

#include 

void f()

{

   int *ptr = (int *) malloc(sizeof(int));

   /* Do some work */

   return; /* Return without freeing ptr*/

}

What are local static variables? What is their use?
Ans:A local static variable is a variable whose lifetime doesn’t end with a function call where it is declared. It extends for the lifetime of complete program. All calls to the function share the same copy of local static variables. Static variables can be used to count the number of times a function is called. Also, static variables get the default value as 0. For example, the following program prints “0 1″

#include 

void fun()

{

    // static variables get the default value as 0.

    static int x;

    printf("%d ", x);

    x = x + 1;

}

int main()

{

    fun();

    fun();

    return 0;

}

// Output: 0 1

What are static functions? What is their use?
Ans:In C, functions are global by default. The “static” keyword before a function name makes it static. Unlike global functions in C, access to static functions is restricted to the file where they are declared. Therefore, when we want to restrict access to functions, we make them static. Another reason for making functions static can be reuse of the same function name in other files. See this for examples and more details.

What are main characteristics of C language?
C is a procedural language. The main features of C language include low-level access to memory, simple set of keywords, and clean style. These features make it suitable for system programming like operating system or compiler development.
What is difference between i++ and ++i?
1) The expression ‘i++’ returns the old value and then increments i. The expression ++i increments the value and returns new value.
2) Precedence of postfix ++ is higher than that of prefix ++.
3) Associativity of postfix ++ is left to right and associativity of prefix ++ is right to left.
4) In C++, ++i can be used as l-value, but i++ cannot be. In C, they both cannot be used as l-value.
See Difference between ++*p, *p++ and *++p for more details.
What is l-value?
l-value or location value refers to an expression that can be used on left side of assignment operator. For example in expression “a = 3″, a is l-value and 3 is r-value.
l-values are of two types:
“nonmodifiable l-value” represent a l-value that can not be modified. const variables are “nonmodifiable l-value”.
“modifiable l-value” represent a l-value that can be modified.
What is the difference between array and pointer?
See Array vs Pointer
How to write your own sizeof operator?

#define my_sizeof(type) (char *)(&type+1)-(char*)(&type)

See Implement your own sizeof for more details.
How will you print numbers from 1 to 100 without using loop?
We can use recursion for this purpose.

/* Prints numbers from 1 to n */

void printNos(unsigned int n)

{

  if(n > 0)

  {

    printNos(n-1);

    printf("%d ",  n);

  } 

}

What is volatile keyword?
The volatile keyword is intended to prevent the compiler from applying any optimizations on objects that can change in ways that cannot be determined by the compiler.
Objects declared as volatile are omitted from optimization because their values can be changed by code outside the scope of current code at any time. See Understanding “volatile” qualifier in C for more details.
Can a variable be both const and volatile?
yes, the const means that the variable cannot be assigned a new value. The value can be changed by other code or pointer. For example the following program works fine.

int main(void)

{

const volatile int local = 10;

int *ptr = (int*) &local;

printf("Initial value of local : %d \n", local);

*ptr = 100;

printf("Modified value of local: %d \n", local);

return 0;

}

What is Object Oriented Programming?
Object Oriented Programming (OOP) is a programming paradigm where the complete software operates as a bunch of objects talking to each other. An object is a collection of data and methods that operate on its data.
Why OOP?
The main advantage of OOP is better manageable code that covers following.
1) The overall understanding of the software is increased as the distance between the language spoken by developers and that spoken by users.
2) Object orientation eases maintenance by the use of encapsulation. One can easily change the underlying representation by keeping the methods same.
OOP paradigm is mainly useful for relatively big software. See this for a complete example that shows advantages of OOP over procedural programing.
What are main features of OOP?
Encapsulation
Polymorphism
Inheritance
What is encapsulation?
Encapsulation is referred to one of the following two notions.
1) Data hiding: A language feature to restrict access to members of an object. For example, private and protected members in C++.
2) Bundling of data and methods together: Data and methods that operate on that data are bundled together.
What is Polymorphism? How is it supported by C++?
Polymorphism means that some code or operations or objects behave differently in different contexts. In C++, following features support polymorphism.
Compile Time Polymorphism: Compile time polymorphism means compiler knows which function should be called when a polymorphic call is made. C++ supports compiler time polymorphism by supporting features like templates, function overloading and default arguments.
Run Time Polymorphism: Run time polymorphism is supported by virtual functions. The idea is, virtual functions are called according to the type of object pointed or referred, not according to the type of pointer or reference. In other words, virtual functions are resolved late, at runtime.

What is Inheritance? What is the purpose?
The idea of inheritance is simple, a class is based on another class and uses data and implementation of the other class.
The purpose of inheritance is Code Reuse.
What is Abstraction?
The first thing with which one is confronted when writing programs is the problem. Typically we are confronted with “real-life” problems and we want to make life easier by providing a program for the problem. However, real-life problems are nebulous and the first thing we have to do is to try to understand the problem to separate necessary from unnecessary details: We try to obtain our own abstract view, or model, of the problem. This process of modeling is called abstraction (Source http://gd.tuwien.ac.at/languages/c/c++oop-pmueller/node4.html#SECTION00410000000000000000)

See the source for a complete example and more details of abstraction.

What are advantages of DBMS over traditional file based systems?
Ans: Database management systems were developed to handle the following difﬁculties of typical ﬁle-processing systems supported by conventional operating systems.
1. Data redundancy and inconsistency
2. Difﬁculty in accessing data
3. Data isolation – multiple ﬁles and formats
4. Integrity problems
5. Atomicity of updates
6. Concurrent access by multiple users
7. Security problems
Source: http://cs.nyu.edu/courses/spring01/G22.2433-001/mod1.2.pdf
What are super, primary, candidate and foreign keys?
Ans: A superkey is a set of attributes of a relation schema upon which all attributes of the schema are functionally dependent. No two rows can have the same value of super key attributes.
A Candidate key is minimal superkey, i.e., no proper subset of Candidate key attributes can be a superkey.
A Primary Key is one of the candidate keys. One of the candidate keys is selected as most important and becomes the primary key. There cannot be more that one primary keys in a table.
Foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. See this for an example.
What is the difference between primary key and unique constraints?
Ans: Primary key cannot have NULL value, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple unique constrains.
What is database normalization?
Ans: It is a process of analyzing the given relation schemas based on their functional dependencies and primary keys to achieve the following desirable properties:
1) Minimizing Redundancy
2) Minimizing the Insertion, Deletion, And Update Anomalies
Relation schemas that do not meet the properties are decomposed into smaller relation schemas that could meet desirable properties.
Source: http://cs.tsu.edu/ghemri/CS346/ClassNotes/Normalization.pdf
What is SQL?
SQL is Structured Query Language designed for inserting and modifying in a relational database system.
What are the differences between DDL, DML and DCL in SQL?
Ans: Following are some details of three.
DDL stands for Data Definition Language. SQL queries like CREATE, ALTER, DROP and RENAME come under this.
DML stands for Data Manipulation Language. SQL queries like SELECT, INSERT and UPDATE come under this.
DCL stands for Data Control Language. SQL queries like GRANT and REVOKE come under this.
What is the difference between having and where clause?
Ans: HAVING is used to specify a condition for a group or an aggregate function used in select statement. The WHERE clause selects before grouping. The HAVING clause selects rows after grouping. Unlike HAVING clause, the WHERE clause cannot contain aggregate functions. (See this for examples)
What is Join?
Ans: An SQL Join is used to combine data from two or more tables, based on a common field between them. For example, consider the following two tables.
Student Table

EnrollNo	StudentName	Address
1000	geek1	geeksquiz1
1001	geek2	geeksquiz2
1002	geek3	geeksquiz3

StudentCourse Table

CourseID	EnrollNo
1	1000
2	1000
3	1000
1	1002
2	1003

Following is join query that shows names of students enrolled in different courseIDs.

SELECT StudentCourse.CourseID, Student.StudentName
       FROM StudentCourse
       INNER JOIN Customers 
       ON StudentCourse.EnrollNo = Student.EnrollNo
       ORDER BY StudentCourse.CourseID;

The above query would produce following result.

CourseID	StudentName
1	geek1
1	geek2
2	geek1
2	geek3
3	geek1

What is Identity?
Ans: Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.
What is a view in SQL? How to create one
Ans: A view is a virtual table based on the result-set of an SQL statement. We can create using create view syntax.

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

What are the uses of view?
1. Views can represent a subset of the data contained in a table; consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.
2. Views can join and simplify multiple tables into a single virtual table
3. Views can act as aggregated tables, where the database engine aggregates data (sum, average etc.) and presents the calculated results as part of the data
4. Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table
5. Views take very little space to store; the database contains only the definition of a view, not a copy of all the data which it presentsv.
6. Depending on the SQL engine used, views can provide extra security
Source: Wiki Page
What is a Trigger?
Ans: A Trigger is a code that associated with insert, update or delete operations. The code is executed automatically whenever the associated query is executed on a table. Triggers can be useful to maintain integrity in database.
What is a stored procedure?
Ans: A stored procedure is like a function that contains a set of operations compiled together. It contains a set of operations that are commonly used in an application to do some common database tasks.
What is the difference between Trigger and Stored Procedure?
Ans: Unlike Stored Procedures, Triggers cannot be called directly. They can only be associated with queries.
What is a transaction? What are ACID properties?
Ans: A Database Transaction is a set of database operations that must be treated as whole, means either all operations are executed or none of them.
An example can be bank transaction from one account to another account. Either both debit and credit operations must be executed or none of them.
ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably.
What are indexes?
Ans: A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data.
Data can be stored only in one order on disk. To support faster access according to different values, faster search like binary search for different values is desired, For this purpose, indexes are created on tables. These indexes need extra space on disk, but they allow faster search according to different frequently searched values.
What are clustered and non-clustered Indexes?
Ans: Clustered indexes is the index according to which data is physically stored on disk. Therefore, only one clustered index can be created on a given database table.
Non-clustered indexes don’t define physical ordering of data, but logical ordering. Typically, a tree is created whose leaf point to disk records. B-Tree or B+ tree are used for this purpose.
We will soon be covering more DBMS questions. Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

What is a process and process table? What are different states of process
A process is an instance of program in execution. For example a Web Browser is a process, a shell (or command prompt) is a process.
The operating system is responsible for managing all the processes that are running on a computer and allocated each process a certain amount of time to use the processor. In addition, the operating system also allocates various other resources that processes will need such as computer memory or disks. To keep track of the state of all the processes, the operating system maintains a table known as the process table. Inside this table, every process is listed along with the resources the processes is using and the current state of the process.
Processes can be in one of three states: running, ready, or waiting. The running state means that the process has all the resources it need for execution and it has been given permission by the operating system to use the processor. Only one process can be in the running state at any given time. The remaining processes are either in a waiting state (i.e., waiting for some external event to occur such as user input or a disk access) or a ready state (i.e., waiting for permission to use the processor). In a real operating system, the waiting and ready states are implemented as queues which hold the processes in these states. The animation below shows a simple representation of the life cycle of a process (Source: http://courses.cs.vt.edu/csonline/OS/Lessons/Processes/index.html)
What is a Thread? What are the differences between process and thread?
A thread is a single sequence stream within in a process. Because threads have some of the properties of processes, they are sometimes called lightweight processes. Threads are popular way to improve application through parallelism. For example, in a browser, multiple tabs can be different threads. MS word uses multiple threads, one thread to format the text, other thread to process inputs, etc.
A thread has its own program counter (PC), a register set, and a stack space. Threads are not independent of one other like processes as a result threads shares with other threads their code section, data section and OS resources like open files and signals. See http://www.personal.kent.edu/~rmuhamma/OpSystems/Myos/threads.htm for more details.
What is deadlock?
Deadlock is a situation when two or more processes wait for each other to finish and none of them ever finish. Consider an example when two trains are coming toward each other on same track and there is only one track, none of the trains can move once they are in front of each other. Similar situation occurs in operating systems when there are two or more processes hold some resources and wait for resources held by other(s).
What are the necessary conditions for deadlock?
Mutual Exclusion: There is s resource that cannot be shared.
Hold and Wait: A process is holding at least one resource and waiting for another resource which is with some other process.
No Preemption: The operating system is not allowed to take a resource back from a process until process gives it back.
Circular Wait: A set of processes are waiting for each other in circular form.
What is Virtual Memory? How is it implemented?
Virtual memory creates an illusion that each user has one or more contiguous address spaces, each beginning at address zero. The sizes of such virtual address spaces is generally very high.
The idea of virtual memory is to use disk space to extend the RAM. Running processes don’t need to care whether the memory is from RAM or disk. The illusion of such a large amount of memory is created by subdividing the virtual memory into smaller pieces, which can be loaded into physical memory whenever they are needed by a process.
What is Thrashing?
Thrashing is a situation when the performance of a computer degrades or collapses. Thrashing occurs when a system spends more time processing page faults than executing transactions. While processing page faults is necessary to in order to appreciate the benefits of virtual memory, thrashing has a negative affect on the system. As the page fault rate increases, more transactions need processing from the paging device. The queue at the paging device increases, resulting in increased service time for a page fault (Source: http://cs.gmu.edu/cne/modules/vm/blue/thrash.html)
What is Belady’s Anomaly?
Bélády’s anomaly is an anomaly with some page replacement policies where increasing the number of page frames results in an increase in the number of page faults. It occurs with First in First Out page replacement is used. See the wiki page for an example and more details.
Differences between mutex and semphore?
See http://www.geeksforgeeks.org/mutex-vs-semaphore/
We will soon be covering more Operating System questions. Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

What is a Data Structure?
A data structure is a way of organizing the data so that the data can be used efficiently. Different kinds of data structures are suited to different kinds of applications, and some are highly specialized to specific tasks. For example, B-trees are particularly well-suited for implementation of databases, while compiler implementations usually use hash tables to look up identifiers. (Source: Wiki Page)
Which data structures are used for BFS and DFS of a graph?
Queue is used for BFS and Stack is used for DFS. DFS can also be implemented using recursion (Note that recursion also uses function call stack).

What is an algorithm?
Informally, an algorithm is any well-defined computational procedure that takes some value, or set of values, as input and produces some value, or set of values, as output. An algorithm is thus a sequence of computational steps that transform the input into the output. (Source: Introduction to Algorithms 3rd Edition by CLRS)
What is time complexity of Binary Search?
Time complexity of binary search is O(Logn). See Binary Search for more details.
Can Binary Search be used for linked lists?
Since random access is not allowed in linked list, we cannot reach the middle element in O(1) time. Therefore Binary Search is not possible for linked lists. There are other ways though, refer Skip List for example.
How to find if two given rectangles overlap?
Two rectangles do not overlap if one of the following conditions is true.
1) One rectangle is above top edge of other rectangle.
2) One rectangle is on left side of left edge of other rectangle.
See Find if two rectangles overlap for more details.
How to find angle between hour and minute hands at a given time?
The idea is to take a reference point as 12. Find the angle moved by hour and minute hands, subtract the two angles to find the angle between them. See angle between hour hand and minute hand for more details
When does the worst case of QuickSort occur?
In quickSort, we select a pivot element, then partition the given array around the pivot element by placing pivot element at its correct position in sorted array.
The worst case of quickSort occurs when one part after partition contains all elements and other part is empty. For example, if the input array is sorted and if last or first element is chosen as a pivot, then the worst occurs. See http://geeksquiz.com/quick-sort/ for more details.
A sorted array is rotated at some unknown point, how to efficiently search an element in it.
A simple approach is linear search, but we can search in O(Logn) time using Binary Search. See Search an element in a sorted and pivoted array for more details.
Other variations of this problem like find the minimum element or maximum element in a sorted and rotated array.
Given a big string of characters, how to efficiently find the first unique character in it?
The efficient solution is to use character as an index in a count array. Traverse the given string and store index of first occurrence of every character, also store count of occurrences. Then traverse the count array and find the smallest index with count as 1. See find the first unique character for more details.
How to count inversions in a sorted array?
Two elements arr[i] and arr[j] in an array arr[] form an inversion if a[i] > a[j] and i < j. How to count all inversions in an unsorted array. See Count Inversions in an array for all approaches.
Given a big array, how to efficiently find k’th largest element in it?
There can be many solutions for this. The best solution is to use min heap. We Build a Min Heap MH of the first k elements. For each element, after the kth element (arr[k] to arr[n-1]), compare it with root of MH, if the element is greater than the root then make it root and call heapify for MH, Else ignore it. Finally, MH has k largest elements and root of the MH is the kth largest element. See k largest(or smallest) elements for more details.
Given an array of size n with range of numbers from 1 to n+1. The array doesn’t contain any duplicate, one number is missing, find the missing number.
There can be many ways to solve it. The best among is to use XOR. See Find the missing number for details. There are many variations of this problem like find the two repeating numbers, find a missing and a repeating number, etc.
How to write an efficient method to calculate x raise to the power n?
The idea is to use divide an conquer here to do it in O(Logn) time. See Write a C program to calculate pow(x,n) for more details.
Given an input string and a dictionary of words, find out if the input string can be segmented into a space-separated sequence of dictionary words.
The idea is to use Dynamic Programming. See Word Break Problem for more details.
Given a row of n coins of values v1 . . . vn, where n is even. We play a game against an opponent by alternating turns. In each turn, a player selects either the first or last coin from the row, removes it from the row permanently, and receives the value of the coin. Determine the maximum possible amount of money we can definitely win if we move first.
This is also a Dynamic Programming Question. See Optimal Strategy for a Game for more details.
You are given an array of sorted words in an arbitrary language, you need to find order (or precedence of characters) in the language. For example if the given arrays is {“baa”, “abcd”, “abca”, “cab”, “cad”}, then order of characters is ‘b’, ‘d’, ‘a’, ‘c’. Note that words are sorted and in the given language “baa” comes before “abcd”, therefore ‘b’ is before ‘a’ in output. Similarly we can find other orders.
This can be solved using two steps: First create a graph by processing given set of words, then do topological sorting of the created graph, See this for more details.

PL/SQL Exam or Interview Question with answer

2014-06-17T05:39:00.002-07:00

PL/SQL Exam or Interview Question with answer

http://sql-plsql.blogspot.com/2007/04/sql-plsql-interview-questions.html
http://iamasgor.blogspot.com/2014/04/online-exam-oracle-plsql-1z0-147-dumps.html
http://careerride.com/test.aspx?type=Oracle-PLSQL
http://oraclequizquestions.blogspot.com/
http://oraclequizquestions.blogspot.com/2013/02/plsql-semester-2-final-exam.html
http://oraclequizquestions.blogspot.com/2013/02/plsql-semester-2-mid-term-exam_12.html
http://oraclequizquestions.blogspot.com/2013/02/plsql-semester-1-final-exam.html

http://dev.fyicenter.com/Interview-Questions/SQL/
http://www.globalguideline.com/interview_questions/Questions.php?sc=Oracle_PL-SQL_Interview_Questions_and_Answers_
http://www.geekinterview.com/Interview-Questions/Oracle/PL-SQL
http://sqlandplsql.com/2012/05/30/oracle-interview-questions-and-answers/
http://www.programmer2programmer.net/tips/interview_question/oracle/oracle_basiscs_of_pl_sql_interview_questions_answers.aspx
https://sites.google.com/site/allinterviewquestions/Home/oracle-sql-plsql-interview-questions
http://www.careerride.com/Oracle-Interview-Questions.aspx

PHP with Oracle 11g on Windows 8

2013-04-17T22:39:00.000-07:00

I work with the link below for my window 8 laptop. I worked fine.

Step-by-Step

STEP 1. Install WAMP server and Oracle 11g at first.
STEP 2. For Windows 7 (64-bit), extract instantclient-basic-windows.x64-11.2.0.3.0.zip, downloaded from http://www.oracle.com/technetwork/topics/winx64soft-089540.html to C:\Windows\SysWOW64\instantclient_11_2 and set it on PATH variable for Environment variable.

STEP 3. Reboot the system now.
STEP 4. Allow PHP extension from php.ini as
extension=php_oci8_11g.dll (just remove the ";" from the start of the line)
STEP 5. Restart WAMP server.
STEP 6. Open the file to see the database name as D:\app\Farhan\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

 LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Here, the service name is found as "orcl" .
STEP 7. Make the connection in PHP script as oci_connect(your_username, your_password, "localhost/orcl.168.1.2");

Conclusion

Now try it on your environment, wish you will be succeeded easily to connect with Oracle database from PHP applications on the platform of Windows 8.

Source: http://www.codeproject.com/Tips/523381/PHP-with-Oracle-11g-on-Windows-7-64-bit

common create table command in different database

2012-12-09T01:52:00.002-08:00

Lets see how a customer table is created in different database

In Oracle:

         create table customer
         (
         cust_id number,
         cust_name varchar2(200),
         cust_gender char(1),
         registration_date date,
         );

In MySQL:
         create table customer
         (
         cust_id int,
         cust_name varchar(200),
         cust_gender char(1),
         registration_date datetime,
         );

In MSSQL:

         create table customer
         (
         cust_id int,
         cust_name varchar(200),
         cust_gender char(1),
         registration_date datetime,
         );

In PostgreSQL:

         create table customer
         (
         cust_id int8, [or cust_id integer]
         cust_name varchar(200),
         cust_gender char(1),
         registration_date date,
         );