Item Details in between that transaction dates

SELECT  --OOD.ORGANIZATION_NAME,
MSIB.SEGMENT1 ITEM_CODE,SUBSTR(MSIB.DESCRIPTION,1,INSTR(MSIB.DESCRIPTION,' ',1,1)) PART_NO,
MSIB.DESCRIPTION ITEM_DESC,MSIB.ATTRIBUTE6 MODEL_NO,MSIB.ATTRIBUTE7 ITEM_CLASS,OOD.ORGANIZATION_NAME
FROM MTL_SYSTEM_ITEMS_B MSIB,
mtl_material_transactions MT,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE
MSIB.INVENTORY_ITEM_ID=MT.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID=MT.ORGANIZATION_ID
AND MSIB.ORGANIZATION_ID=MT.ORGANIZATION_ID
AND MSIB.ORGANIZATION_ID=OOD.ORGANIZATION_ID
AND MT.ORGANIZATION_ID=OOD.ORGANIZATION_ID
AND OOD.ORGANIZATION_NAME LIKE '%XXX%'
AND MT.CREATION_DATE between TO_DATE(:PFROM) AND TO_DATE(:PTO)

AR Invoice details with Item and Tax -- Oracle Apps

SELECT
HCA.ACCOUNT_NUMBER CUSTOMER_CODE,
HP.PARTY_NAME CUSTOMER_NAME,
HCSUA.LOCATION,
--RCT.CUSTOMER_TRX_ID,
RCT.TRX_NUMBER INV_NO,RCT.TRX_DATE INV_DATE,
--RCG.GL_DATE,RCT.PURCHASE_ORDER,OOD.ORGANIZATION_ID,OOD.ORGANIZATION_NAME,
--HCSUA.SITE_USE_ID,
IGCC.SEG6_DESC PRODUCT,
MSIB.SEGMENT1 ITEM_CODE,RCL.DESCRIPTION,MSIB.PRIMARY_UOM_CODE UOM,
RCL.QUANTITY_ORDERED QUANTITY,RCL.UNIT_SELLING_PRICE RATE,
RTT.NAME TRANSACTION_NAME,DECODE(RTT.TYPE,'INV','INVOICE','CM','Credit Memo','DM','Debit Memo') TRANSACTION_TYPE,
--DECODE(RCL.LINE_TYPE,'LINE','LINE_TOTAL','TAX','TOTAL_TAX') LINE_TYPE,
sum((DECODE(RCT.INVOICE_CURRENCY_CODE,'INR',RCG.AMOUNT*1,RCG.AMOUNT*RCT.EXCHANGE_RATE))) INV_AMOUNT
,JCT.TAX_NAME
--,JAT.TAX_ID
,SUM(JAT.TAX_AMOUNT)TAX_AMOUNT,JAT.TAX_RATE  
--,FU.USER_ID,FU.USER_NAME
FROM
RA_CUSTOMER_TRX_ALL RCT
,RA_CUSTOMER_TRX_LINES_ALL RCL
,MTL_SYSTEM_ITEMS_B MSIB
,GL_CODE_COMBINATIONS IGCC
,APPS.JAI_AR_TRX_LINES JAL
,APPS.JAI_AR_TRX_TAX_LINES JAT
,APPS.JAI_CMN_TAXES_ALL JCT
,RA_CUST_TRX_LINE_GL_DIST_ALL RCG
,ORG_ORGANIZATION_DEFINITIONS OOD
,HZ_CUST_ACCOUNTS HCA
,HZ_PARTIES HP
,RA_CUST_TRX_TYPES_ALL RTT
,FND_USER FU
,HZ_CUST_SITE_USES_ALL HCSUA
WHERE
RCT.TRX_DATE BETWEEN :PFROM AND :PTO
AND RCT.CUSTOMER_TRX_ID = RCL.CUSTOMER_TRX_ID
AND RCL.CUSTOMER_TRX_LINE_ID = RCG.CUSTOMER_TRX_LINE_ID
AND RCL.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND RCL.INTERFACE_LINE_ATTRIBUTE10 = MSIB.ORGANIZATION_ID
AND MSIB.SALES_ACCOUNT = IGCC.CODE_COMBINATION_ID
---AND RCL.CUSTOMER_TRX_ID = JAL.CUSTOMER_TRX_ID (+)
AND RCL.CUSTOMER_TRX_ID = JAL.CUSTOMER_TRX_ID(+)
AND JAL.CUSTOMER_TRX_LINE_ID = JAT.LINK_TO_CUST_TRX_LINE_ID (+)
AND JAT.TAX_ID = JCT.TAX_ID (+)
AND RCT.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
AND HCA.ACCOUNT_NUMBER = 'TEST123'
AND HCA.PARTY_ID = HP.PARTY_ID
AND RCT.LAST_UPDATED_BY = FU.USER_ID
AND RCT.CUST_TRX_TYPE_ID = RTT.CUST_TRX_TYPE_ID
AND TO_NUMBER(RCT.INTERFACE_HEADER_ATTRIBUTE10) = OOD.ORGANIZATION_ID (+)
AND RCT.TRX_NUMBER = 'Test02884'
--AND FU.USER_NAME IN ('TEST')
--AND HCASA.CUST_ACCOUNT_ID=HCA.CUST_ACCOUNT_ID
--AND HCASA.CUST_ACCT_SITE_ID=HCSUA.CUST_ACCT_SITE_ID
AND IGCC.SEG6_DESC = 'XXXX'
AND RCT.BILL_TO_SITE_USE_ID=HCSUA.SITE_USE_ID
GROUP BY
HCA.ACCOUNT_NUMBER,
HP.PARTY_NAME, HCSUA.LOCATION,
--RCT.CUSTOMER_TRX_ID,
RCT.TRX_NUMBER,RCT.TRX_DATE,
--RCT.CUST_TRX_TYPE_ID,RCG.GL_DATE,
IGCC.SEG6_DESC,MSIB.SEGMENT1,RCL.DESCRIPTION,MSIB.PRIMARY_UOM_CODE,
RCL.QUANTITY_ORDERED ,RCL.UNIT_SELLING_PRICE ,
JCT.TAX_NAME,--JAT.TAX_AMOUNT,
JAT.TAX_RATE,
--RCT.CUSTOMER_TRX_ID,RCT.PURCHASE_ORDER,OOD.ORGANIZATION_NAME,OOD.ORGANIZATION_ID,HCSUA.SITE_USE_ID,FU.USER_ID,FU.USER_NAME,
RTT.NAME,RTT.TYPE
--RCL.LINE_TYPE
ORDER BY RCT.TRX_DATE DESC

Customer Details with site numbers and location -- Oracle Apps

SELECT HCA.ACCOUNT_NUMBER,
HP.PARTY_NAME,HP.PARTY_NUMBER,
HP.ADDRESS1 PARTY_ADD1,HP.ADDRESS2 PARTY_ADD1,HP.CITY PARTY_CITY,HP.STATE PARTY_STATE,HP.POSTAL_CODE PARTY_POSTAL,HP.PRIMARY_PHONE_AREA_CODE STD_CODE,HP.PRIMARY_PHONE_NUMBER PARTY_PHONE,
HL.ADDRESS1 SITE_ADD1, HL.ADDRESS2 SITE_ADD2, HL.ADDRESS3 SITE_ADD1, HL.ADDRESS4 SITE_ADD4, HL.CITY, HL.POSTAL_CODE SITE_CODE, HL.STATE SITE_STATE,
HPS.PARTY_SITE_ID,HPS.PARTY_SITE_NUMBER SITE_NUMBER,
HCAS.CUST_ACCT_SITE_ID,
HCS.SITE_USE_ID LOCATION_ID,HCS.SITE_USE_CODE,HCS.LOCATION,HCS.CREATION_DATE SITE_CREATION_DATE
FROM
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
HZ_PARTY_SITES HPS,
HZ_CUST_ACCT_SITES_ALL HCAS,
HZ_CUST_SITE_USES_ALL HCS,
HZ_LOCATIONS HL
WHERE
HCA.PARTY_ID = HP.PARTY_ID
AND HP.PARTY_ID = HPS.PARTY_ID
AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HCAS.CUST_ACCT_SITE_ID = HCS.CUST_ACCT_SITE_ID
AND HPS.LOCATION_ID = HL.LOCATION_ID
--AND SITE_USE_CODE = 'BILL_TO'
AND HCA.ACCOUNT_NUMBER = 'AA01132'

Adjustments query in receiving transactions -- oracle apps

SELECT
I.NAME ORG_NAME,
A.ORG_ID,
      H.GL_MONTH PERIOD_MONTH,
      H.GL_YEAR PERIOD_YEAR,
      H.GL_QUARTER PERIOD_QTR,
      'ADJUSTMENT' REVENUE_GROUP,
      E.SEGMENT5 ACCT_CODE,
      REPLACE(E.SEG5_DESC,'''','') ACCT_NAME,
      REPLACE(BG.SEG4_DESC,'''','') GRUP,
      REPLACE(E.SEG6_DESC,'''','') PRODUCT,
D.CUSTOMER_NUMBER CUSTOMER_CODE,
      REPLACE(D.CUSTOMER_NAME,'''','') CUSTOMER_NAME,
      SR.NAME ASE_NAME,
      SR.ATTRIBUTE2 ASE_EMAIL_ID,
      REPLACE(E.SEG3_DESC,'''','') INVOICE_LOCATION,
      J.SITE_USE_ID LOCATION_ID,
-- J.LOCATION LOCATION_NAME,
B.TRX_NUMBER INV_NO,
      B.TRX_DATE INV_DATE,
      A.GL_DATE,
      TRUNC(B.LAST_UPDATE_DATE) LUPDT_DATE,
      TRUNC(B.CREATION_DATE) CREATION_DATE,
      F.TYPE INV_TYPE,
SUM(A.ACCTD_AMOUNT) ACTAMT,
TRUNC(SYSDATE) PROC_EXEC_DATE
FROM
APPS.RA_HCUSTOMERS D,
AR.RA_CUSTOMER_TRX_ALL B,
AR.RA_CUSTOMER_TRX_LINES_ALL EX,
AR.RA_CUST_TRX_LINE_GL_DIST_ALL A,
GL_CODE_COMBINATIONS E,
AR.RA_CUST_TRX_TYPES_ALL F,
GL_PERIOD H,
--HR.HR_ALL_ORGANIZATION_UNITS I
HR_OPERATING_UNITS I,
AR.HZ_CUST_SITE_USES_ALL J,
JTF.JTF_RS_SALESREPS SR,
GL_CODE_COMBINATIONS BG
WHERE
D.CUSTOMER_ID = B.BILL_TO_CUSTOMER_ID
AND A.CUSTOMER_TRX_ID = B.CUSTOMER_TRX_ID
AND  I.ORGANIZATION_ID = A.ORG_ID
AND A.CUSTOMER_TRX_ID = EX.CUSTOMER_TRX_ID
AND EX.CUSTOMER_TRX_LINE_ID = A.CUSTOMER_TRX_LINE_ID
AND A.CODE_COMBINATION_ID = E.CODE_COMBINATION_ID
AND     B.CUST_TRX_TYPE_ID = F.CUST_TRX_TYPE_ID
AND   F.TYPE IN ('DM','CM')
AND   J.SITE_USE_ID = B.BILL_TO_SITE_USE_ID
AND   J.SITE_USE_CODE = 'BILL_TO'
AND         SR.ORG_ID = A.ORG_ID
AND         SR.SALESREP_ID = J.PRIMARY_SALESREP_ID
AND J.GL_ID_REV = BG.CODE_COMBINATION_ID
AND   A.GL_DATE BETWEEN H.GL_START_DATE AND H.GL_END_DATE
AND A.ACCOUNT_CLASS = 'REV'
AND B.COMPLETE_FLAG='Y'
AND E.SEGMENT5 IN ( '1234')
AND TRUNC(A.GL_DATE) BETWEEN :SDATE AND :EDATE
GROUP BY
I.NAME ,
A.ORG_ID,
      H.GL_MONTH ,
      H.GL_YEAR ,
      H.GL_QUARTER ,
      'ADJUSTMENT' ,
      E.SEGMENT5 ,
      E.SEG5_DESC,
      BG.SEG4_DESC,
      E.SEG6_DESC,
D.CUSTOMER_NUMBER ,
      D.CUSTOMER_NAME,
      SR.NAME ,
      SR.ATTRIBUTE2 ,
      E.SEG3_DESC,
      J.SITE_USE_ID ,
-- J.LOCATION LOCATION_NAME,
B.TRX_NUMBER ,
      B.TRX_DATE ,
      A.GL_DATE,
      B.LAST_UPDATE_DATE,
       B.CREATION_DATE,
      F.TYPE,
TRUNC(SYSDATE)

To know the responsibilities f particular user

SELECT FR.RESPONSIBILITY_NAME,
 FU.USER_NAME,
 FUR.START_DATE,
 FUR.END_DATE
 FROM
 FND_USER_RESP_GROUPS_DIRECT FUR,
 APPS.FND_RESPONSIBILITY_TL FR
 ,FND_USER FU
 WHERE FUR.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
 AND FR.RESPONSIBILITY_NAME LIKE '%AP%'
 AND FUR.USER_ID = FU.USER_ID
 AND FUR.END_DATE IS NULL

To find the active reports in oracle apps

SELECT DISTINCT
 FCPT.USER_CONCURRENT_PROGRAM_NAME
,FCPT.CONCURRENT_PROGRAM_NAME CP_SHORT_NAME
,FCPT.DESCRIPTION CP_DESC
,FCPT.APPLICATION_ID
--,FCP.CONCURRENT_PROGRAM_NAME SHORT_NAME
,FCP.OUTPUT_FILE_TYPE
,DECODE(FCP.ENABLED_FLAG,'Y','ACTIVE','N','INACTIVE')STATUS
,FEX.EXECUTABLE_NAME
,FEX.APPLICATION_NAME
,FEX.DESCRIPTION EX_DESC
,DECODE(FEX.EXECUTION_METHOD_CODE,'I','PL/SQL Stored Procedure','H','Host','P','Oracle Reports',
                                 'K','Java Concurrernt Program','Q','SQL*Plus','M','Multi Lanuage Function',
'A','Spawned','J','Java Stored Procedure','B','Request Set Stage Function',
'S','Immediate','L','SQL*Loader','P','Perl Concurrent Program')EXECUTION_METHOD
,FEX.EXECUTION_FILE_NAME
,FDP.END_USER_COLUMN_NAME PARAMETERS
,FDP.SRW_PARAM TOKEN
,FORM_LEFT_PROMPT PROMPT
,FFVS.FLEX_VALUE_SET_NAME
--,FRU.REQUEST_GROUP_ID
--,FRU.REQUEST_GROUP_NAME
,FAT.APPLICATION_NAME Module_name
,FAT.APPLICATION_ID
FROM
--FND_CONCURRENT_PROGRAMS_TL FCPT,
APPS.FND_CONCURRENT_PROGRAMS_VL FCPT
,APPS.FND_CONCURRENT_PROGRAMS FCP
,APPS.FND_EXECUTABLES_FORM_V FEX
,APPS.FND_DESCR_FLEX_COL_USAGE_VL FDP
,APPS.FND_FLEX_VALUE_SETS FFVS
--,FND_CONCURRENT_REQUESTS FCR
--,FND_RESPONSIBILITY_VL FRT
,APPS.FND_REQUEST_GROUP_UNITS FRU
,APPS.FND_APPLICATION_TL FAT
WHERE
FCPT.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND FCP.ENABLED_FLAG='Y'
AND FCPT.EXECUTABLE_ID = FEX.EXECUTABLE_ID
AND FCPT.EXECUTABLE_APPLICATION_ID=FEX.APPLICATION_ID
AND FCPT.APPLICATION_ID = FDP.APPLICATION_ID
AND FCPT.CONCURRENT_PROGRAM_NAME = SUBSTR(FDP.DESCRIPTIVE_FLEXFIELD_NAME,7)  
AND FDP.FLEX_VALUE_SET_ID=FFVS.FLEX_VALUE_SET_ID
AND FCPT.CONCURRENT_PROGRAM_ID = FRU.REQUEST_UNIT_ID(+)
--AND FRU.UNIT_APPLICATION_ID = FAT.APPLICATION_ID
AND FRU.APPLICATION_ID = FAT.APPLICATION_ID(+)
--AND FEX.APPLICATION_NAME like '%Busi%'
AND FCPT.USER_CONCURRENT_PROGRAM_NAME like ' Expense Analysis Report'
ORDER BY FCPT.USER_CONCURRENT_PROGRAM_NAME

To find the scheduled reports in oracle apps

SELECT fl.meaning
     , fu.user_name
     , fu.description requestor
     , fu.end_date
     , NVL(fu.email_address, 'n/a') email_address
     , fcr.request_id
     , fcr.number_of_copies
     , fcr.printer
     , fcr.request_date
     , fcr.requested_start_date
     , fcp.description,fcp.user_concurrent_program_name
     , fcr.argument_text
     , frt.responsibility_name
  FROM apps.fnd_concurrent_requests fcr
     , apps.fnd_user fu
     , apps.fnd_lookups fl
     , apps.fnd_concurrent_programs_vl fcp
     , apps.fnd_responsibility_tl frt
 WHERE fcr.requested_by = fu.user_id
   AND fl.lookup_type = 'CP_STATUS_CODE'
   AND fcr.status_code = fl.lookup_code
   AND fcr.program_application_id = fcp.application_id
   AND fcr.concurrent_program_id = fcp.concurrent_program_id
   AND fcr.responsibility_id = frt.responsibility_id
   AND fcr.phase_code = 'P'
   ORDER BY fu.user_name