Join between PO and PA modules -- Oracle Apps

SELECT *
FROM   PA_PROJECTS_ALL PA,
  PA_TASKS PT,
  PO_DISTRIBUTIONS_ALL PDA,
  PO_LINE_LOCATIONS_ALL PLLA,
  PO_LINES_ALL PLA,
  PO_HEADERS_ALL PHA,
          PURCHASE_TAX_V IPPTV,
  PO_REQ_DISTRIBUTIONS_ALL PRDA,
  PO_REQUISITION_LINES_ALL PRLA,
  PO_REQUISITION_HEADERS_ALL PRHA,
  PER_ALL_PEOPLE_F PAPF,
  PER_ALL_PEOPLE_F PAPF1,
  AP SUPPLIDRS PV
WHERE  PA.PROJECT_ID = PT.PROJECT_ID
AND   PT.TASK_ID = PDA.TASK_ID
AND   PDA.LINE_LOCATION_ID = PLLA.LINE_LOCATION_ID
AND   PLLA.PO_LINE_ID = PLA.PO_LINE_ID
AND   PLA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND   IPPTV.LINE_LOCATION_ID(+) = PLLA.LINE_LOCATION_ID
AND   PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID(+)
AND   PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID(+)
AND   PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID(+)
AND   PAPF.PERSON_ID = PHA.AGENT_ID
AND   PAPF1.PERSON_ID(+) = PRHA.PREPARER_ID
AND   PHA.VENDOR_ID = PV.VENDOR_ID(+)
AND  (PHA.CANCEL_FLAG IS NULL OR PHA.CANCEL_FLAG = 'N')
AND  (PLA.CANCEL_FLAG IS NULL OR PLA.CANCEL_FLAG = 'N')
AND  (PLLA.CANCEL_FLAG IS NULL OR PLLA.CANCEL_FLAG = 'N')
--AND  PHA.SEGMENT1 = NVL(:PO_NUM,PHA.SEGMENT1)
--AND   PA.SEGMENT1 BETWEEN DECODE(:PA_SEGMENT1,'ALL',PA.SEGMENT1,:PA_SEGMENT1) AND DECODE(:PA_SEGMENT1_1,'ALL',PA.SEGMENT1,:PA_SEGMENT1_1)
--AND   TRUNC(PHA.CREATION_DATE) BETWEEN :FROM_PO_DATE AND :TO_PO_DATE

Join bw JAI_CMN_RG_23AC_II_TRXS and rcv_transactions

SELECT DISTINCT a.rma_reference, a.organization_id --,a.*
              FROM
              apps.JAI_CMN_RG_23AC_II_TRXS jip,
               rcv_transactions a
             WHERE jip.organization_id = a.organization_id
               --AND jip.receipt_id = a.transaction_id
               AND jip.RECEIPT_REF = a.transaction_id
  AND TRUNC(a.CREATION_DATE) <= '30-JUN-2013'
               AND a.rma_reference IS NOT NULL;

RA_ADDRESS_ALL Replacement in R12 -- Oracle Apps

SELECT DISTINCT  HCA.CUST_ACCOUNT_ID CUSTOMER_ID,
         HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
         HP.PARTY_NAME CUSTOMER_NAME,
         HCSU.CUST_ACCT_SITE_ID ADDRESS_ID,
         HL.ADDRESS1,
         HL.ADDRESS2,
         HL.ADDRESS3,
         HL.ADDRESS4,
         HL.CITY,
         HL.POSTAL_CODE,
         UPPER(HL.STATE) STATE,
         DECODE(NVL(HCSA.SHIP_TO_FLAG,'N'),'P','P','Y','Y','N') SHIP_TO_FLAG,
         DECODE(NVL(HCSA.BILL_TO_FLAG,'N'),'P','Y','Y','Y','N') BILL_TO_FLAG
FROM APPS.HZ_PARTIES HP,
APPS.HZ_PARTY_SITES HPS,
APPS.HZ_LOCATIONS HL,
APPS.HZ_CUST_ACCOUNTS_ALL HCA,
APPS.HZ_CUST_ACCT_SITES_ALL HCSA,
APPS.HZ_CUST_SITE_USES_ALL HCSU
WHERE HP.PARTY_ID = HPS.PARTY_ID
AND HPS.LOCATION_ID = HL.LOCATION_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HCSA.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HCSU.CUST_ACCT_SITE_ID = HCSA.CUST_ACCT_SITE_ID
AND HCA.CUST_ACCOUNT_ID = HCSA.CUST_ACCOUNT_ID
--AND TO_DATE(TO_CHAR(HCSA.CREATION_DATE,'DD-MON-YYYY'),'DD-MON-YYYY') >= '28-JUN-2010'
AND (HCA.CUSTOMER_CLASS_CODE = 'WEB DISTRIBUTORS' OR HCA.SALES_CHANNEL_CODE = 'KEY WEB CUSTOMERS')
ORDER BY HL.ADDRESS1 --,HCSA.CREATION_DATE

Credit Hold Query -- Oracle Apps

SELECT
DISTINCT HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
HP.PARTY_NAME CUSTOMER_NAME,
OHA.ORDER_NUMBER,
OHA.ORDERED_DATE,
OHD.NAME HOLD_NAME,
OHD.TYPE_CODE HOLD_TYPE,
FLV.MEANING RELEASE_REASON_CODE,
FU.USER_NAME RELEASED_BY,
OHR.CREATION_DATE RELEASE_DATE
 FROM
OE_ORDER_HEADERS_ALL OHA,
ONT.OE_ORDER_HOLDS_ALL OOHA,
ONT.OE_HOLD_SOURCES_ALL OHSA,
OE_HOLD_DEFINITIONS OHD,
OE_HOLD_RELEASES OHR,
FND_USER FU,
FND_LOOKUP_VALUES FLV,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP
WHERE trunc(ORDERED_DATE) BETWEEN TO_DATE(:PFROM,'DD-MON-YYYY') AND TO_DATE(:TDATE,'DD-MON-YYYY')
AND OHA.HEADER_ID=OOHA.HEADER_ID
AND OOHA.HOLD_SOURCE_ID=OHSA.HOLD_SOURCE_ID
AND OHSA.HOLD_ID=OHD.HOLD_ID
AND OOHA.HOLD_RELEASE_ID=OHR.HOLD_RELEASE_ID
AND OHR.CREATED_BY=FU.USER_ID
AND OHR.RELEASE_REASON_CODE=FLV.LOOKUP_CODE
AND FLV.LOOKUP_TYPE='RELEASE_REASON'
and oha.SOLD_TO_ORG_ID=HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID=HP.PARTY_ID
ORDER BY OHA.ORDERED_DATE

To get the employee details by using PER_PEOPLE_F table

SELECT
PPF.PERSON_ID USERID,
PPF.EMPLOYEE_NUMBER EMPLOYEE_NUM,
PPF.LAST_NAME USER_NAME,
--rtrim(rtrim(EMAIL_ADDRESS,'signodeindia.com'),'@')ALIAS_NAME,
SUBSTR(PPF.EMAIL_ADDRESS,1,INSTR(PPF.EMAIL_ADDRESS,'@',1)-1) ALIAS_NAME,
PPF.EMAIL_ADDRESS,
PPF.LAST_NAME FULL_NAME,
--PPF.EFFECTIVE_START_DATE JOING_DATE,
TO_CHAR(PPF.DATE_OF_BIRTH,'DD-MON-YYYY')DATE_OF_BIRTH,
PAV.DEFAULT_CODE_COMB_ID,
IGCV.SEGMENT2,
IGCV.SEG2_DESC,
IGCV.SEGMENT4 COST_CODE,
IGCV.SEG4_DESC COST_CODE_DESC
,PAV.LOCATION_CODE,
PAV.ADDRESS_LINE_1 ADD1,
PAV.ADDRESS_LINE_2 ADD2,
PAV.ADDRESS_LINE_3 ADD3,
PAV.TOWN_OR_CITY,
PAV.COUNTRY,
PAV.POSTAL_CODE,
PAV.TELEPHONE_NUMBER_1,
PAV.TELEPHONE_NUMBER_2,
PPF.WORK_TELEPHONE TEL
FROM
PER_PEOPLE_F PPF,
PER_ASSIGNMENTS_V7 PAV,
GL_CODE_COMBINATIONS IGCV
WHERE
PPF.PERSON_ID=PAV.PERSON_ID
AND PPF.EMPLOYEE_NUMBER=PAV.ASSIGNMENT_NUMBER
AND PAV.DEFAULT_CODE_COMB_ID=IGCV.CODE_COMBINATION_ID
--AND SUBSTR(PPF.EMAIL_ADDRESS,INSTR(PPF.EMAIL_ADDRESS,'@',1)+1)  != 'ramesh.com'
order by PPF.EMPLOYEE_NUMBER

Item with categories query -- Oracle Apps

SELECT DISTINCT MSIB.ORGANIZATION_ID,OOD.ORGANIZATION_NAME,IGC.SEGMENT6 PRODUCT_CODE,IGC.SEG6_DESC PRODUCT ,MSIB.SEGMENT1 ITEM_CODE,MSIB.DESCRIPTION,MC.SEGMENT1 MAJOR_CATEGORY,MC.SEGMENT2 MINOR_CATEGORY
FROM
GL_CODE_COMBINATIONS IGC,
MTL_SYSTEM_ITEMS_B MSIB,
MTL_ITEM_CATEGORIES MIC,
MTL_CATEGORIES MC,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE
IGC.CODE_COMBINATION_ID = MSIB.SALES_ACCOUNT
AND MSIB.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND MIC.CATEGORY_ID = MC.CATEGORY_ID
AND MSIB.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND MSIB.ORGANIZATION_ID = 10

TO find the excisable,modavate flag items -- Oracle Apps R12

  SELECT ORGANIZATION_ID, MTL.SEGMENT1 ITEM_CODE,DESCRIPTION ITEM_DESCRIPTION,MTL.PRIMARY_UOM_CODE UOM,MTL.INVENTORY_ITEM_STATUS_CODE ITEM_STATUS,IGCC.SEGMENT6 PRODUCT_CODE,IGCC.SEG6_DESC PRODUCT,
                MTL.ATTRIBUTE6 MODEL,MTL.ATTRIBUTE7 CLASSIFICATION,
                SUBSTR(MTL.DESCRIPTION,1,INSTR(MTL.DESCRIPTION,' ',1,1)) PART_NO,
                                                                                  (SELECT F1.ATTRIBUTE_VALUE
                                                                FROM
                                                                JAI_RGM_ITEM_ATTRIB_V F1
                                                                WHERE F1.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
                                                                AND F1.ORGANIZATION_ID = MTL.ORGANIZATION_ID
                                                                AND F1.ATTRIBUTE_CODE = 'EXCISABLE') EXC_NEX_FLG,
                                                                (SELECT F1.ATTRIBUTE_VALUE
                                                                FROM
                                                                JAI_RGM_ITEM_ATTRIB_V F1
                                                                WHERE F1.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
                                                                AND F1.ORGANIZATION_ID = MTL.ORGANIZATION_ID
                                                                AND F1.ATTRIBUTE_CODE = 'MODVATABLE') MODVAT_FLAG,
                                                                (SELECT F1.ATTRIBUTE_VALUE
                                                                FROM JAI_RGM_ITEM_ATTRIB_V F1
                                                                WHERE F1.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
                                                                AND F1.ORGANIZATION_ID = MTL.ORGANIZATION_ID
                                                                AND F1.ATTRIBUTE_CODE = 'TRADABLE') TRADING_FLAG
FROM MTL_SYSTEM_ITEMS_B MTL,
GL_CODE_COMBINATIONS IGCC
WHERE
MTL.SALES_ACCOUNT = IGCC.CODE_COMBINATION_ID
AND MTL.ORGANIZATION_ID = 1000
;