Query to get the AR Invoice Amount, transaction type and location -- Oracle Apps

SELECT
RCT.CUSTOMER_TRX_ID,
RCT.TRX_NUMBER,
RCT.TRX_DATE,
RCG.GL_DATE,
RCT.PURCHASE_ORDER,
OOD.ORGANIZATION_ID,
OOD.ORGANIZATION_NAME,
HCSUA.SITE_USE_ID,
HCSUA.LOCATION,
RCL.DESCRIPTION,
HCA.ACCOUNT_NUMBER,
HP.PARTY_NAME,
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))) TOTAL_INV_AMOUNT,  
FU.USER_ID,FU.USER_NAME
FROM
RA_CUSTOMER_TRX_ALL RCT,
RA_CUSTOMER_TRX_LINES_ALL RCL,
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_ACCT_SITES_ALL HCASA,
,HZ_CUST_SITE_USES_ALL HCSUA
WHERE
RCT.CUSTOMER_TRX_ID = RCL.CUSTOMER_TRX_ID
AND RCL.CUSTOMER_TRX_LINE_ID = RCG.CUSTOMER_TRX_LINE_ID
AND RCT.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
AND HCA.ACCOUNT_NUMBER = 'XXRAM001'
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 HCASA.CUST_ACCOUNT_ID=HCA.CUST_ACCOUNT_ID
--AND HCASA.CUST_ACCT_SITE_ID=HCSUA.CUST_ACCT_SITE_ID
AND RCT.BILL_TO_SITE_USE_ID=HCSUA.SITE_USE_ID
AND RCT.TRX_DATE BETWEEN :PFROM AND :PTO
GROUP BY
RCT.CUSTOMER_TRX_ID,RCT.TRX_NUMBER,
RCT.CUST_TRX_TYPE_ID,RCT.TRX_DATE,RCG.GL_DATE,
RCT.CUSTOMER_TRX_ID,RCT.PURCHASE_ORDER,OOD.ORGANIZATION_NAME,
RCL.DESCRIPTION,
HCA.ACCOUNT_NUMBER,OOD.ORGANIZATION_ID,
HP.PARTY_NAME,
HCSUA.SITE_USE_ID,HCSUA.LOCATION,
FU.USER_ID,FU.USER_NAME,
RTT.NAME,RTT.TYPE,RCL.LINE_TYPE
ORDER BY RCT.TRX_DATE DESC

Query to get Receipt data with tax details -- Oracle Apps

SELECT
VENDOR_CODE,
VENDOR_NAME,
CATEGORY,
VENDOR_SITE_CODE,
ADDRESESS1,
ADDRESESS2,
CITY,
STATE,
COUNTRY,
CONTACT,
CONTACT_EMAIL, --CURRENCY_CODE,
SUM(NVL("BASE_VALUE",0)+NVL("SALES TAX",0)+NVL("EXCISE",0)+NVL("INSURANCE",0)+NVL("OTHERS",0)+NVL("FREIGHT",0))TOTAL
FROM
(
SELECT DISTINCT C.SEGMENT1 VENDOR_CODE,
                 C.VENDOR_NAME,
C.VENDOR_TYPE_LOOKUP_CODE Category,
E.VENDOR_SITE_CODE,
E.ADDRESS_LINE1 ADDRESESS1,
E.ADDRESS_LINE2 ADDRESESS2,
E.CITY,
                 E.STATE,
UPPER(PC.NAME) COUNTRY,
E.PHONE CONTACT,
E.ATTRIBUTE11 CONTACT_EMAIL,H.CURRENCY_CODE,A.RECEIPT_NUM,D.SHIPMENT_LINE_ID,
                NVL(DECODE (H.CURRENCY_CODE,
                            'INR',
                             P.ACT_QTY * H.PO_UNIT_PRICE,
                             P.ACT_QTY * H.PO_UNIT_PRICE * H.CURRENCY_CONVERSION_RATE
                            ),0) "BASE_VALUE",
                NVL(DECODE (H.CURRENCY_CODE,
                          'INR',
                           NULL,
                           P.ACT_QTY * H.PO_UNIT_PRICE
                           )
                     + ROUND (DECODE (H.CURRENCY_CODE,
                            'INR',
                             NULL,
                             I.TAX_AMOUNT / H.CURRENCY_CONVERSION_RATE
                                )
                        ),0) "GRN DOLLER VAL",
                ROUND ((I.TAX_AMOUNT / I.QTY_RECEIVED) * P.ACT_QTY ) "TOTAL_LINE_AMT",
                ST.TAX_TYPE,
            NVL((ST.SALESTAX * P.ACT_QTY / H.QUANTITY),0) "SALES TAX",
              ETAX.TAX_TYPE ETAX_TYPE,    
              ETAX.SALESTAX * P.ACT_QTY / H.QUANTITY "ENTRY TAX",
              NVL((ET.EXCISE * P.ACT_QTY / H.QUANTITY),0) "EXCISE",
              NVL((INS.INSURANCE * P.ACT_QTY / H.QUANTITY),0) "INSURANCE",
              NVL((Freight.Freight * P.ACT_QTY / H.QUANTITY),0) "FREIGHT",
              NVL((OTHES.OTHERS * P.ACT_QTY / H.QUANTITY),0) "OTHERS",
              DECODE (A.RECEIPT_SOURCE_CODE,
                        'VENDOR',
                         C.VENDOR_NAME,
                        'CUSTOMER',
                         NULL,
                        ORG.ORGANIZATION_NAME ) "VENDOR_NAME1"
           FROM
            PO.RCV_SHIPMENT_HEADERS A,
            PO.PO_HEADERS_ALL B,
   APPS.AP_SUPPLIERS C,
            PO.RCV_SHIPMENT_LINES D,
            APPS.AP_SUPPLIER_SITES_ALL E,  
            INV.MTL_SYSTEM_ITEMS_B F,
            PO.RCV_TRANSACTIONS H,
   APPS.JAI_RCV_LINES  I,
   APPS.JAI_RCV_LINE_TAXES RCV,
            INV.MTL_CATEGORIES_B J,
            GL.GL_CODE_COMBINATIONS K,
   PA_COUNTRY_V PC,
            APPS.JAI_CMN_VENDOR_SITES L,
            APPS.AP_TERMS M,
              (SELECT
                  TRIM (A.FLEX_VALUE) AS FLEXID,
                        B.DESCRIPTION AS PRODUCT
FROM
                   APPS.FND_FLEX_VALUES A,
                   APPS.FND_FLEX_VALUES_TL B
WHERE
                   A.FLEX_VALUE_ID = B.FLEX_VALUE_ID
                   AND A.FLEX_VALUE_SET_ID =
  (SELECT FLEX_VALUE_SET_ID
                      FROM APPS.FND_ID_FLEX_SEGMENTS
     WHERE APPLICATION_COLUMN_NAME = 'SEGMENT6'
AND ID_FLEX_CODE = 'GL#')) FLEX,
   (SELECT   SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID,
                          SUM (QUANTITY) ACT_QTY
                     FROM PO.RCV_TRANSACTIONS
                    WHERE TRANSACTION_TYPE = 'DELIVER'
                 GROUP BY SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID) P,
                (SELECT   SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID,
                          SUM (QUANTITY) RJT_QTY
                     FROM PO.RCV_TRANSACTIONS
                    WHERE UPPER(TRANSACTION_TYPE) = 'REJECT'
                 GROUP BY SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID) Q,
                (SELECT   SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID,
                          SUM (QUANTITY) RTN_QTY
                     FROM PO.RCV_TRANSACTIONS
                    WHERE UPPER(TRANSACTION_TYPE) = 'RETURN TO VENDOR'
                 GROUP BY SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID) R,
                (SELECT   SUM (TAX_AMOUNT) SALESTAX, SHIPMENT_LINE_ID,
                          SHIPMENT_HEADER_ID,TAX_TYPE
                     FROM
     APPS.JAI_RCV_LINE_TAXES
                    WHERE UPPER(TAX_TYPE) IN ('SALES TAX', 'CST', 'LST')
       AND UPPER(TAX_NAME) NOT LIKE '%ENTRY%'
                 GROUP BY SHIPMENT_LINE_ID, SHIPMENT_HEADER_ID,TAX_TYPE
                ) ST,
                (SELECT   SUM (TAX_AMOUNT) SALESTAX, SHIPMENT_LINE_ID,
                          SHIPMENT_HEADER_ID,TAX_TYPE
                     FROM
APPS.JAI_RCV_LINE_TAXES
                    WHERE UPPER(TAX_TYPE) IN ('SALES TAX', 'CST', 'LST')
AND UPPER(TAX_NAME) LIKE '%ENTRY%'
                 GROUP BY SHIPMENT_LINE_ID, SHIPMENT_HEADER_ID,TAX_TYPE
) ETAX,
                (SELECT   SUM (TAX_AMOUNT) EXCISE, SHIPMENT_LINE_ID,
                          SHIPMENT_HEADER_ID
                     FROM
APPS.JAI_RCV_LINE_TAXES
                    WHERE UPPER(TAX_TYPE) IN ('EXCISE', 'CUSTOMS', 'CVD')
                 GROUP BY SHIPMENT_LINE_ID, SHIPMENT_HEADER_ID) ET,
                (SELECT   SUM (TAX_AMOUNT) INSURANCE, SHIPMENT_LINE_ID,
                          SHIPMENT_HEADER_ID
                     FROM
APPS.JAI_RCV_LINE_TAXES
                    WHERE UPPER(TAX_TYPE) IN ('INSURANCE')
                 GROUP BY SHIPMENT_LINE_ID, SHIPMENT_HEADER_ID) INS,
                 (SELECT   SUM (TAX_AMOUNT) FREIGHT, SHIPMENT_LINE_ID,
                          SHIPMENT_HEADER_ID
                     FROM
APPS.JAI_RCV_LINE_TAXES
                    WHERE UPPER(TAX_TYPE) IN ('FREIGHT')
                 GROUP BY SHIPMENT_LINE_ID, SHIPMENT_HEADER_ID) Freight,
                (SELECT   SUM (TAX_AMOUNT) OTHERS, SHIPMENT_LINE_ID,
                          SHIPMENT_HEADER_ID
                     FROM
APPS.JAI_RCV_LINE_TAXES
                    WHERE UPPER(TAX_TYPE) NOT IN
                             ('EXCISE',
                              'CUSTOMS',
                              'CVD',
                              'SALES TAX',
                              'CST',
                              'LST',
                              'INSURANCE' ,
                              'FREIGHT'
                             )
                 GROUP BY SHIPMENT_LINE_ID, SHIPMENT_HEADER_ID) OTHES,
                APPS.ORG_ORGANIZATION_DEFINITIONS ORG
--              AP.AP_INVOICES_ALL AP,
--              (SELECT * FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL WHERE REVERSAL_FLAG IS NULL) APID
          WHERE D.PO_HEADER_ID = B.PO_HEADER_ID(+)
            AND A.SHIPMENT_HEADER_ID = D.SHIPMENT_HEADER_ID
            AND A.VENDOR_ID = C.VENDOR_ID(+)
            AND A.VENDOR_SITE_ID = E.VENDOR_SITE_ID(+)
            AND D.TO_ORGANIZATION_ID = F.ORGANIZATION_ID
            AND D.ITEM_ID = F.INVENTORY_ITEM_ID
            AND D.SHIPMENT_HEADER_ID = H.SHIPMENT_HEADER_ID
            AND D.SHIPMENT_LINE_ID = H.SHIPMENT_LINE_ID
            AND H.TRANSACTION_TYPE = 'RECEIVE'
            AND D.CATEGORY_ID = J.CATEGORY_ID
            AND F.COST_OF_SALES_ACCOUNT = K.CODE_COMBINATION_ID
            AND H.SHIPMENT_HEADER_ID = I.SHIPMENT_HEADER_ID(+)
            AND H.SHIPMENT_LINE_ID = I.SHIPMENT_LINE_ID(+)
            AND H.SHIPMENT_HEADER_ID = P.SHIPMENT_HEADER_ID(+)
            AND H.SHIPMENT_LINE_ID = P.SHIPMENT_LINE_ID(+)
            AND H.SHIPMENT_HEADER_ID = Q.SHIPMENT_HEADER_ID(+)
            AND H.SHIPMENT_LINE_ID = Q.SHIPMENT_LINE_ID(+)
            AND H.SHIPMENT_HEADER_ID = R.SHIPMENT_HEADER_ID(+)
            AND H.SHIPMENT_LINE_ID = R.SHIPMENT_LINE_ID(+)
            AND RCV.SHIPMENT_HEADER_ID = ST.SHIPMENT_HEADER_ID(+)
            AND RCV.SHIPMENT_LINE_ID = ST.SHIPMENT_LINE_ID(+)
            AND RCV.SHIPMENT_LINE_ID = ET.SHIPMENT_LINE_ID(+)
            AND RCV.SHIPMENT_HEADER_ID = ET.SHIPMENT_HEADER_ID(+)
            AND RCV.SHIPMENT_LINE_ID = INS.SHIPMENT_LINE_ID(+)
            AND RCV.SHIPMENT_HEADER_ID = INS.SHIPMENT_HEADER_ID(+)
            AND RCV.SHIPMENT_HEADER_ID = OTHES.SHIPMENT_HEADER_ID(+)
            AND RCV.SHIPMENT_LINE_ID = OTHES.SHIPMENT_LINE_ID(+)
            AND RCV.SHIPMENT_HEADER_ID(+) = H.SHIPMENT_HEADER_ID
            AND RCV.SHIPMENT_LINE_ID(+) = H.SHIPMENT_LINE_ID
            AND ORG.ORGANIZATION_ID(+) = A.ORGANIZATION_ID
--           AND APID.INVOICE_ID = AP.INVOICE_ID(+)
--           AND H.TRANSACTION_ID = APID.RCV_TRANSACTION_ID(+)
            AND C.VENDOR_ID IS NOT NULL
---         AND F.COST_OF_SALES_ACCOUNT = K.CODE_COMBINATION_ID
            AND FLEX.FLEXID = K.SEGMENT6
             AND E.VENDOR_ID = L.VENDOR_ID(+)
             AND E.VENDOR_SITE_ID  =  L.VENDOR_SITE_ID(+)
     AND B.TERMS_ID=M.TERM_ID  
AND PC.COUNTRY_CODE=E.COUNTRY
AND RCV.SHIPMENT_HEADER_ID = ETAX.SHIPMENT_HEADER_ID(+)
            AND RCV.SHIPMENT_LINE_ID = ETAX.SHIPMENT_LINE_ID(+)
AND RCV.SHIPMENT_HEADER_ID = Freight.SHIPMENT_HEADER_ID(+)
            AND RCV.SHIPMENT_LINE_ID = Freight.SHIPMENT_LINE_ID(+)
AND H.CURRENCY_CODE  IN ('INR')
AND TRUNC(H.TRANSACTION_DATE) BETWEEN :PFROM AND :PTO
)
GROUP BY
VENDOR_CODE,
VENDOR_NAME,
CATEGORY,
VENDOR_SITE_CODE,
ADDRESESS1,
ADDRESESS2,
CITY,
STATE,
COUNTRY,
CONTACT,
CONTACT_EMAIL
ORDER BY
VENDOR_CODE

Query to find the AP SUPPLIERS and HZ Parties -- Oracle Apps

SELECT
asp.vendor_id ,
asp.segment1 "Supplier Num" ,
asp.vendor_name "Supplier Name" ,
ass.vendor_site_code "site name" ,
hou.name "Operating Unit Name" ,
ass.address_line1 ,
ass.city ,
ass.state ,
ass.zip ,
ass.country ,
ass.phone ,
person.person_first_name ,
person.person_last_name ,
pty_rel.primary_phone_number ,
pty_rel.email_address
FROM
ap_suppliers asp ,
ap_supplier_sites_all ass ,
APPS.ap_supplier_contacts apsc ,
hz_parties person ,
hz_parties pty_rel,
hr_operating_units hou
WHERE
ass.vendor_id = asp.vendor_id
AND apsc.per_party_id = person.party_id
AND apsc.rel_party_id = pty_rel.party_id
AND ass.org_id = hou.organization_id
AND apsc.org_party_site_id = ass.party_site_id

Query to find the record count of table -- Oracle Apps

create or replace
function get_records( p_tname in varchar2 ) return number
as
    l_colval  number default NULL;
begin
    execute immediate
       'select count(*)
          from ' || p_tname INTO l_colval;

    return l_colval;
end;


Ex: 1. select
user, table_name,
       get_records(table_name) cnt
  from
user_tables
 
2. select
user, TNAME,
       get_records(TNAME) cnt
  from
R12_TABLES

Join between MTL tables -- Oracle Apps

SELECT
mta.organization_id org_id,
OOD.ORGANIZATION_NAME,
mta.inventory_item_id inv_id,
MSIB.SEGMENT1,
MSIB.DESCRIPTION,
mtt.transaction_type_name,
mta.transaction_date,
(mta.PRIMARY_QUANTITY*-1) AS PRIMQTY,
(mta.base_transaction_value*-1) AS Base_Val
FROM
APPS.mtl_parameters mtp,
APPS.mtl_transaction_accounts mta,
APPS.mtl_material_transactions  mmt,
APPS.mtl_transaction_types mtt,
ORG_ORGANIZATION_DEFINITIONS OOD,
MTL_SYSTEM_ITEMS_B MSIB
WHERE
mta.transaction_id = mmt.transaction_id
AND mmt.transaction_type_id = mtt.transaction_type_id
AND mtp.material_account = mta.reference_account
AND mtp.organization_id = mta.organization_id
AND mta.organization_id = OOD.organization_id
AND MSIB.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND MSIB.INVENTORY_ITEM_ID = MTA.INVENTORY_ITEM_ID
AND mta.primary_quantity <= 0
AND TRUNC (mta.transaction_date) BETWEEN :SDATE AND  :EDATE

Query to find the Active User list -- Oracle Apps

select
*
from
fnd_user
where
END_DATE <sysdate

Query to get the Bank details by using the AR Invoice number -- 11i Oracle Apps

SELECT  
AA.trx_number,AA.trx_date,TO_CHAR (acrh.gl_date, 'DD-MON-YYYY') "GL Date", 'R' "Type",acr.amount,
         acr.doc_sequence_value "Voucher Number",
         acr.receipt_number "Cheque Number", TO_CHAR (acr.receipt_date, 'DD-MON-YYYY')  "Cheque Date",
         rc.customer_name "Vendor Name",
         acrh.account_code_combination_id "Account Code",
         SUM (DECODE (acrh.status,
                      'REVERSED', (acrh.amount * NVL (acrh.exchange_rate, 1))
                       * -1,
                      NVL (acrh.amount, 0) * NVL (acrh.exchange_rate, 1)
                     )
             ) "Receipts",
         SUM (0) "Payments", aba.bank_account_name "Bank Name",
         aba.bank_account_num "Bank Account Number", acr.org_id "Org ID",
         arbat.NAME "Batch Number", arsour.NAME "Batch Source"
    FROM ar.ar_cash_receipt_history_all acrh,
         ar.ar_cash_receipts_all acr,
         apps.ra_customers rc,
         ap.ap_bank_accounts_all aba,
         ar.ar_batches_all arbat,
         ar.ar_batch_sources_all arsour,
(SELECT DISTINCT ac.CASH_RECEIPT_ID,ra.trx_number,ra.trx_date
  FROM ra_customer_trx_all ra ,
   ar_cash_receipts_all ac ,
   ar_receivable_applications_all aaa
   WHERE /*aaa.application_type = 'CASH'
   AND*/ aaa.cash_receipt_id = ac.cash_receipt_id
   AND aaa.applied_customer_trx_id = ra.customer_trx_id
   )AA
   WHERE acrh.cash_receipt_id = acr.cash_receipt_id
     AND acr.remittance_bank_account_id = aba.bank_account_id
     AND acr.pay_from_customer = rc.customer_id(+)
     AND arsour.batch_source_id(+) = arbat.batch_source_id
     AND acrh.batch_id = arbat.batch_id(+)
     AND acrh.status IN ('CLEARED', 'REMITTED', 'CONFIRMED', 'REVERSED')
AND acr.CASH_RECEIPT_ID =AA.CASH_RECEIPT_ID
GROUP BY AA.trx_number,AA.trx_date,acrh.gl_date,acr.amount,
         acr.doc_sequence_value,
         rc.customer_name,
         acr.receipt_number,
         acr.receipt_date,
         acrh.account_code_combination_id,
         aba.bank_account_name,
         aba.bank_account_num,
         acr.org_id,
         arbat.NAME,
         arsour.NAME

Query to find ASE or Sales Rep details -- Oracle Apps

SELECT
HCS.SITE_USE_ID LOCATION_ID,
HCS.LAST_UPDATE_DATE,
HCS.LAST_UPDATED_BY,
FU.USER_NAME,
HCS.CREATION_DATE,
JRS.SALESREP_NUMBER,
JRS.ATTRIBUTE2 EMAIL,
JRR.RESOURCE_NAME ASE_NAME
FROM
AR.HZ_CUST_SITE_USES_ALL HCS,
FND_USER FU,
JTF_RS_SALESREPS JRS,
JTF.JTF_RS_RESOURCE_EXTNS_TL JRR
WHERE
HCS.LAST_UPDATED_BY = FU.USER_ID
AND HCS.PRIMARY_SALESREP_ID = JRS.SALESREP_ID
AND JRS.RESOURCE_ID = JRR.RESOURCE_ID
AND HCS.PRIMARY_SALESREP_ID = 122333444
ORDER BY LAST_UPDATE_DATE DESC

Query to get the FLEX FIELD Data -- Oracle Apps

SELECT
FDFT.TITLE, FDFCU.*,
FDF.*
FROM
APPS.FND_DESCRIPTIVE_FLEXS FDF,
APPS.FND_DESCRIPTIVE_FLEXS_TL FDFT,
 APPS.FND_DESCR_FLEX_COLUMN_USAGES FDFCU
WHERE
FDF.APPLICATION_TABLE_NAME = NVL(UPPER(:P_TABLE_NAME),FDF.APPLICATION_TABLE_NAME)
AND    FDF.DESCRIPTIVE_FLEXFIELD_NAME = FDFCU.DESCRIPTIVE_FLEXFIELD_NAME
AND   FDF.DESCRIPTIVE_FLEXFIELD_NAME = FDFT.DESCRIPTIVE_FLEXFIELD_NAME
AND   UPPER(FDFT.TITLE) = NVL(UPPER(:P_TITLE),UPPER(FDFT.TITLE))
ORDER BY 5

TDS Invoice Query -- Oracle Apps

SELECT  DISTINCT
 HRO.NAME OPERATING_UNIT
,IGCCV.SEGMENT5 GL_ACCOUNT_CODE  
,SPV.SEGMENT1 VENDOR_CODE
,SPV.VENDOR_NAME
,SPVS.VENDOR_SITE_CODE
,CONCAT('INV NO-',API.INVOICE_NUM) INVOICE_NUM
,API.INVOICE_DATE
,API.INVOICE_AMOUNT INVOICE_HEADER_AMOUNT
,JIATI.INVOICE_AMOUNT INVOICE_LINE_AMOUNT
,JIATI.TDS_INVOICE_NUM TDS_INVOICE_NUM
,APITDS.INVOICE_DATE TDS_INVOICE_DATE
,JIATI.TDS_AMOUNT TDS_INVOICE_AMOUNT
,API.GL_DATE TDS_GL_DATE
,APITDS.AMOUNT_PAID  TDS_AMOUNT_PAID
,JAP.PAN_NO
 ,ADDRESS_LINE1
 ,ADDRESS_LINE2
 ,ADDRESS_LINE3
 ,CITY
 ,STATE
 ,ZIP
 ,COUNTRY
 ,TPV.VENDOR_NAME  TDS_VENDOR
,JIATI.TDS_SECTION
,JIATI.TDS_TAX_RATE,
TRUNC(AIDA.ACCOUNTING_DATE)  ACCOUNTING_DATE,API.ORG_ID
FROM
AP_INVOICES_ALL API,
APPS.AP_INVOICES_ALL APITDS,
APPS.AP_SUPPLIERS SPV,
APPS.AP_SUPPLIERS TPV,
APPS.AP_SUPPLIER_SITES_ALL SPVS,
APPS.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
APPS.HR_OPERATING_UNITS HRO,
APPS.JAI_AP_TDS_VENDOR_HDRS JAP,
APPS.JAI_AP_TDS_INVOICES JIATI,
GL_CODE_COMBINATIONS IGCCV
WHERE
API.VENDOR_ID=SPV.VENDOR_ID
AND   SPV.VENDOR_ID=SPVS.VENDOR_ID
AND   SPVS.VENDOR_SITE_ID= API.VENDOR_SITE_ID
AND  TPV.VENDOR_ID=APITDS.VENDOR_ID
AND   APITDS.ORG_ID=HRO.ORGANIZATION_ID
AND API.INVOICE_ID = JIATI.INVOICE_ID
AND   JAP.VENDOR_ID=API.VENDOR_ID
AND   JAP.VENDOR_SITE_ID=API.VENDOR_SITE_ID
AND APITDS.INVOICE_ID = AIDA.INVOICE_ID
AND APITDS.DESCRIPTION = JIATI.TDS_INVOICE_NUM
AND IGCCV.CODE_COMBINATION_ID = APITDS.ACCTS_PAY_CODE_COMBINATION_ID
AND API.ORG_ID = '102'
AND TRUNC(AIDA.ACCOUNTING_DATE) BETWEEN :PFROM AND :PTO
ORDER BY SPV.SEGMENT1

Submit the request through back end -- Oracle Apps

BEGIN
   fnd_file.put_line (fnd_file.output,
                      ‘*** Call The XXXX Import Program  ***’);
   fnd_global.apps_initialize (
      user_id             => fnd_profile.VALUE (‘USER_ID’),
      resp_id             => fnd_profile.VALUE (‘RESP_ID’),
      resp_appl_id        => fnd_profile.VALUE (‘RESP_APPL_ID’),
      security_group_id   => 0);
   fnd_file.put_line (fnd_file.LOG, ‘Batch ID:’ || ln_group_id);
   ln_req_id :=

      fnd_request.submit_request (‘XX’,  – Application short name
                                  ‘XXXX’, — conc program short name
                                  NULL,
                                  SYSDATE,
                                  FALSE,
                                  ‘BATCH’,
                                  ln_group_id);
   COMMIT;

   IF ln_req_id = 0
   THEN
      fnd_file.put_line (
         fnd_file.LOG,
         ’Request Not Submitted due to “‘ || fnd_message.get || ‘”.’);
   ELSE
      fnd_file.put_line (
         fnd_file.LOG,
         ’The XXXX Import Program submitted – Request id :’ || ln_req_id);
   END IF;

   IF ln_req_id > 0
   THEN
      LOOP
         lv_req_return_status :=
            fnd_concurrent.wait_for_request (ln_req_id,
                                             60,
                                             0,
                                             lv_req_phase,
                                             lv_req_status,
                                             lv_req_dev_phase,
                                             lv_req_dev_status,
                                             lv_req_message);
         EXIT WHEN UPPER (lv_req_phase) = ‘COMPLETED’
                   OR UPPER (lv_req_status) IN
                         (‘CANCELLED’, ‘ERROR’, ‘TERMINATED’);
      END LOOP;

      IF UPPER (lv_req_phase) = ‘COMPLETED’
         AND UPPER (lv_req_status) = ‘ERROR’
      THEN
         fnd_file.put_line (
            fnd_file.LOG,
            ‘The XXXX Import program completed in error. Oracle request id’);
         fnd_file.put_line (fnd_file.LOG, SQLERRM);
      ELSIF UPPER (lv_req_phase) = ‘COMPLETED’
            AND UPPER (lv_req_status) = ‘NORMAL’
      THEN
         Fnd_File.PUT_LINE (
            Fnd_File.LOG,
            ‘The XXXX Import program request successful for request id: ‘
            || ln_req_id);
      ELSE
         Fnd_File.PUT_LINE (
            Fnd_File.LOG,
            ‘The XXXX Import Program request failed. Oracle request id ‘);
         Fnd_File.PUT_LINE (Fnd_File.LOG, SQLERRM);
      END IF;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      fnd_file.put_line (
         fnd_file.LOG,
         ’OTHERS exception while submitting The XXXX Import Program: ‘
         || SQLERRM);
END;

Join bw JAI_CMN_CUS_ADDRESSES and HZ_CUST_ACCOUNTS -- Oracle Apps

SELECT HCA.ACCOUNT_NUMBER,
HP.PARTY_NAME,
--HP.PARTY_NUMBER,HP.ADDRESS1 PARTY_ADDR1,HP.ADDRESS2 PARTY_ADDR2,
----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,
HCS.SITE_USE_CODE,
HCS.SITE_USE_ID LOCATION_ID,HCS.LOCATION,
HL.ADDRESS1 SITE_ADD1,
HL.ADDRESS2 SITE_ADD2,
HL.ADDRESS3 SITE_ADD3,
HL.ADDRESS4 SITE_ADD4, HL.CITY,
HL.POSTAL_CODE ,
HL.STATE SITE_STATE,
--HPS.PARTY_SITE_ID,
HPS.PARTY_SITE_NUMBER SITE_NUMBER,
--HCAS.CUST_ACCT_SITE_ID,
--HCS.CREATION_DATE SITE_CREATION_DATE
CUS.EXCISE_DUTY_REG_NO,
CUS.EXCISE_DUTY_RANGE,
CUS.EXCISE_DUTY_DIVISION,
CUS.EXCISE_DUTY_COMM,
CUS.ST_REG_NO,
CUS.CST_REG_NO,
CUS.EC_CODE,
CUS.VAT_REG_NO
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,
JAI_CMN_CUS_ADDRESSES CUS
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 HCA.CUST_ACCOUNT_ID = CUS.CUSTOMER_ID
AND HCS.CUST_ACCT_SITE_ID = CUS.ADDRESS_ID
--AND SITE_USE_CODE = 'BILL_TO'

ACCRUAL QUERY -- Oracle APPS

CREATE OR REPLACE PROCEDURE SIG_ACCRUAL(P_ERRBUF OUT VARCHAR2,P_RETCODE OUT VARCHAR2,PSEG IN VARCHAR2,PFROM IN DATE,PTO IN DATE)
AS
----New Deveoped by Ramesh on 25Aug14
--DECLARE
CURSOR CUR1 IS
SELECT  OPERATING_UNIT,UNIT_NAME,
       ORGANIZATION_ID,ORG_CODE,ORG_NAME,
        RECEIPT_NUM,
VENDOR_CODE,
VENDOR_NAME,
        SUM(DEBIT_AMOUNT)TOTAL_DEBIT,
SUM(CREDIT_AMOUNT)TOTAL_CREDIT,
(SUM(DEBIT_AMOUNT) - SUM(CREDIT_AMOUNT)) NET
FROM
(
------Receipts For Cost Managements
SELECT  HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
        OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
        RSH.RECEIPT_NUM RECEIPT_NUM,RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
ASP.SEGMENT1 VENDOR_CODE,
ASP.VENDOR_NAME VENDOR_NAME,
        NVL(XAL.ACCOUNTED_DR,0) DEBIT_AMOUNT,
NVL(XAL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_DISTRIBUTION_LINKS XDL,
RCV_RECEIVING_SUB_LEDGER RRSL,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
AP_SUPPLIERS ASP,
ORG_ORGANIZATION_DEFINITIONS OOD,
HR_OPERATING_UNITS HOU
WHERE
GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_TABLE = GIR.GL_SL_LINK_TABLE
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAH.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = RRSL.RCV_SUB_LEDGER_ID
AND RRSL.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.VENDOR_ID = ASP.VENDOR_ID
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND RT.TRANSACTION_TYPE = 'RECEIVE'
AND OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJL.STATUS = 'P'
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJH.JE_CATEGORY = 'Receiving'
AND GJH.JE_SOURCE = 'Cost Management'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
---For Sales return Cost Management
SELECT DISTINCT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
RSH.RECEIPT_NUM RECEIPT_NUM,RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XAL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(XAL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_DISTRIBUTION_LINKS XDL,
RCV_RECEIVING_SUB_LEDGER RRSL,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
HR_OPERATING_UNITS HOU,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_SUPPLIERS APS
WHERE
GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAL.APPLICATION_ID = XAH.APPLICATION_ID
AND XAH.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND xah.event_id = xdl.event_id
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = RRSL.RCV_SUB_LEDGER_ID
AND RRSL.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND RT.TRANSACTION_TYPE = 'RETURN TO VENDOR'
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.VENDOR_ID = APS.VENDOR_ID
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJH.JE_CATEGORY = 'Receiving'
AND GJH.JE_SOURCE = 'Cost Management'
AND GJL.STATUS = 'P'
--AND GJL.GL_SL_LINK_TABLE IN ('XLAJEL','RSL')
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
---------Correction transactions for Cost Management
SELECT  HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
        OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
        RSH.RECEIPT_NUM RECEIPT_NUM,RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
ASP.SEGMENT1 VENDOR_CODE,
ASP.VENDOR_NAME VENDOR_NAME,
        NVL(XAL.ACCOUNTED_DR,0) DEBIT_AMOUNT,
NVL(XAL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_DISTRIBUTION_LINKS XDL,
RCV_RECEIVING_SUB_LEDGER RRSL,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
AP_SUPPLIERS ASP,
ORG_ORGANIZATION_DEFINITIONS OOD,
HR_OPERATING_UNITS HOU
WHERE
GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_TABLE = GIR.GL_SL_LINK_TABLE
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAH.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = RRSL.RCV_SUB_LEDGER_ID
AND RRSL.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.VENDOR_ID = ASP.VENDOR_ID
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND RT.TRANSACTION_TYPE = 'CORRECT'
AND OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJL.STATUS = 'P'
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJH.JE_CATEGORY = 'Receiving'
AND GJH.JE_SOURCE = 'Cost Management'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
---- Receipts for Purchase India
SELECT  HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
        OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
        RSH.RECEIPT_NUM RECEIPT_NUM,RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
PV.SEGMENT1 VENDOR_CODE,
PV.VENDOR_NAME VENDOR_NAME,
        --NVL(GJL.ACCOUNTED_DR,0) DEBIT_AMOUNT,
NVL(DECODE(GJL.ACCOUNTED_DR,NULL,0,DECODE(JRT.CURRENCY,'INR',JRT.TAX_AMOUNT,(JRT.TAX_AMOUNT*RT.CURRENCY_CONVERSION_RATE))),0) DEBIT_AMOUNT,
--NVL(GJL.ACCOUNTED_CR,0) CREDIT_AMOUNT
--NVL(DECODE(JRT.CURRENCY,'INR',JRT.TAX_AMOUNT,(JRT.TAX_AMOUNT*RT.CURRENCY_CONVERSION_RATE)),0) CREDIT_AMOUNT
NVL(DECODE(GJL.ACCOUNTED_CR,NULL,0,DECODE(JRT.CURRENCY,'INR',JRT.TAX_AMOUNT,(JRT.TAX_AMOUNT*RT.CURRENCY_CONVERSION_RATE))),0) CREDIT_AMOUNT
FROM
RCV_TRANSACTIONS RT, ---A
GL_JE_LINES GJL, ---- B
GL_CODE_COMBINATIONS IGCCV, -----C
GL_JE_HEADERS GJH, --- D
RCV_SHIPMENT_HEADERS RSH,  --- CC
APPS.JAI_RCV_LINE_TAXES JRT,
ORG_ORGANIZATION_DEFINITIONS OOD, --- DD
AP_SUPPLIERS PV, ---- EE
HR_OPERATING_UNITS HOU
WHERE   RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND     RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND            RT.TRANSACTION_ID = JRT.TRANSACTION_ID
AND            RSH.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND            JRT.VENDOR_SITE_ID IS NULL
AND            (JRT.THIRD_PARTY_FLAG = 'N' OR JRT.THIRD_PARTY_FLAG IS NULL)
AND            JRT.TAX_AMOUNT != 0
AND     JRT.VENDOR_ID = PV.VENDOR_ID
AND     OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID
AND   RT.TRANSACTION_ID = TO_NUMBER(GJL.REFERENCE_5)
AND     GJL.STATUS = 'P'
AND     GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND   GJH.JE_CATEGORY = 'Receiving India'
AND   GJH.JE_SOURCE = 'Purchasing India'
AND   GJL.REFERENCE_4 = 'rcv_transactions'
AND     RT.TRANSACTION_TYPE = 'RECEIVE'
AND     GJL.CODE_COMBINATION_ID =  IGCCV.CODE_COMBINATION_ID
AND     IGCCV.SEGMENT5 = '1297'
AND            IGCCV.SEGMENT2 = PSEG --= '11'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
---- Receipts for Third party (Purchase India)
SELECT  DISTINCT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
        OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
        RSH.RECEIPT_NUM RECEIPT_NUM,
--JRI.INVOICE_NUM RECEIPT_NUM,
RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
PV.SEGMENT1 VENDOR_CODE,
PV.VENDOR_NAME VENDOR_NAME,
        NVL(DECODE(GJL.ACCOUNTED_DR,NULL,0,DECODE(JRT.CURRENCY,'INR',JRT.TAX_AMOUNT,(JRT.TAX_AMOUNT*RT.CURRENCY_CONVERSION_RATE))),0) DEBIT_AMOUNT,
NVL(DECODE(GJL.ACCOUNTED_CR,NULL,0,DECODE(JRT.CURRENCY,'INR',JRT.TAX_AMOUNT,(JRT.TAX_AMOUNT*RT.CURRENCY_CONVERSION_RATE))),0) CREDIT_AMOUNT
FROM
RCV_TRANSACTIONS RT, ---A
GL_JE_LINES GJL, ---- B
GL_CODE_COMBINATIONS IGCCV, -----C
GL_JE_HEADERS GJH, --- D
RCV_SHIPMENT_HEADERS RSH,  --- CC
APPS.JAI_RCV_LINE_TAXES JRT,
JAI_RCV_TP_INVOICES JRI,
ORG_ORGANIZATION_DEFINITIONS OOD, --- DD
AP_SUPPLIERS PV, ---- EE
HR_OPERATING_UNITS HOU
WHERE   RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND     RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND            RT.TRANSACTION_ID = JRT.TRANSACTION_ID
AND            RSH.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND            JRT.SHIPMENT_HEADER_ID = JRI.SHIPMENT_HEADER_ID(+)
AND            JRT.VENDOR_ID = JRI.VENDOR_ID(+)
AND            JRT.THIRD_PARTY_FLAG = 'Y'
AND     JRT.VENDOR_ID = PV.VENDOR_ID
AND     OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID
AND   RT.TRANSACTION_ID = TO_NUMBER(GJL.REFERENCE_5)
AND     GJL.STATUS = 'P'
AND     GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND   GJH.JE_CATEGORY = 'Receiving India'
AND   GJH.JE_SOURCE = 'Purchasing India'
AND   GJL.REFERENCE_4 = 'rcv_transactions'
AND     RT.TRANSACTION_TYPE = 'RECEIVE'
AND     GJL.CODE_COMBINATION_ID =  IGCCV.CODE_COMBINATION_ID
AND     IGCCV.SEGMENT5 = '1297'
AND            IGCCV.SEGMENT2 = PSEG --= '11'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
---For Sales return Purchase India
SELECT  HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
        OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
        RSH.RECEIPT_NUM RECEIPT_NUM,RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
PV.SEGMENT1 VENDOR_CODE,
PV.VENDOR_NAME VENDOR_NAME,
        NVL(GJL.ACCOUNTED_DR,0) DEBIT_AMOUNT,
NVL(GJL.ACCOUNTED_CR,0) CREDIT_AMOUNT
-- 0 GL_BAL,
-- NULL FLAG
FROM
RCV_TRANSACTIONS RT, ---A
GL_JE_LINES GJL, ---- B
GL_CODE_COMBINATIONS IGCCV, -----C
GL_JE_HEADERS GJH, --- D
RCV_SHIPMENT_HEADERS RSH,  --- CC
ORG_ORGANIZATION_DEFINITIONS OOD, --- DD
AP_SUPPLIERS PV, ---- EE
HR_OPERATING_UNITS HOU
WHERE   RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND     RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND     RSH.VENDOR_ID = PV.VENDOR_ID
AND     OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID
AND   RT.TRANSACTION_ID = TO_NUMBER(GJL.REFERENCE_5)
AND     GJL.STATUS = 'P'
AND     GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND   GJH.JE_CATEGORY = 'Receiving India'
AND   GJH.JE_SOURCE = 'Purchasing India'
AND   GJL.REFERENCE_4 = 'rcv_transactions'
AND     RT.TRANSACTION_TYPE = 'RETURN TO VENDOR'
AND     GJL.CODE_COMBINATION_ID =  IGCCV.CODE_COMBINATION_ID
AND     IGCCV.SEGMENT5 = '1297'
AND            IGCCV.SEGMENT2 = PSEG --= '11'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
-----AP SOURCE
----- GL_SL_LINK_TABLE = 'APECL' REFERENCE_10 <> 'CHARGE'
--Working fine for all but we can't get receipt num
/*SELECT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,NULL ORGANIZATION_ID,
NULL ORG_CODE,NULL ORG_NAME,
AIA.INVOICE_NUM RECEIPT_NUM,NULL TRANSACTION_ID,GJH.JE_HEADER_ID,
ASP.SEGMENT1 VENDOR_CODE,ASP.VENDOR_NAME VENDOR_NAME,
NVL(GJL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(GJL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
---AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_INVOICES_ALL AIA,
AP_AE_LINES_ALL AAL,
---RCV_TRANSACTIONS RT,
---RCV_SHIPMENT_HEADERS RSH,
HR_OPERATING_UNITS HOU,
---ORG_ORGANIZATION_DEFINITIONS OOD,
AP_SUPPLIERS ASP
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.GL_SL_LINK_ID = AAL.GL_SL_LINK_ID
---AND AIDA.INVOICE_ID = AIA.INVOICE_ID
---AND AAL.SOURCE_ID = AIDA.INVOICE_DISTRIBUTION_ID(+)
AND AAL.REFERENCE2 = TO_CHAR(AIA.INVOICE_ID)
---AND AAL.SUBLEDGER_DOC_SEQUENCE_VALUE = AIA.DOC_SEQUENCE_VALUE
AND AAL.ORG_ID = AIA.ORG_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND AIA.VENDOR_ID = ASP.VENDOR_ID
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GJL.REFERENCE_10 NOT IN ('CHARGE')
AND GJL.GL_SL_LINK_TABLE = 'APECL'
AND GJL.STATUS = 'P'
AND TRUNC(EFFECTIVE_DATE) <= :LDATE */
----- GL_SL_LINK_TABLE = 'APECL' REFERENCE_10 <> 'CHARGE'
--Working fine for some orgs but we can get receipt num , issue with 03
SELECT AIDA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
ASP.SEGMENT1 VENDOR_CODE,ASP.VENDOR_NAME VENDOR_NAME,
NVL(GJL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(GJL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_INVOICES_ALL AIA,
AP_AE_LINES_ALL AAL,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
HR_OPERATING_UNITS HOU,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_SUPPLIERS ASP
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.GL_SL_LINK_ID = AAL.GL_SL_LINK_ID
AND AAL.SOURCE_ID = AIDA.INVOICE_DISTRIBUTION_ID(+)
AND AIDA.RCV_TRANSACTION_ID = RT.TRANSACTION_ID(+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID(+)
AND AIDA.ORG_ID = HOU.ORGANIZATION_ID
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID(+)
AND AIA.VENDOR_ID = ASP.VENDOR_ID
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GJL.REFERENCE_10 NOT IN ('CHARGE')
AND GJL.GL_SL_LINK_TABLE = 'APECL'
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE ---test
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
----- GL_SL_LINK_TABLE = 'APECL' REFERENCE_10 ='CHARGE'
---charges entries are available until 30DEC2013 even in R12 also
SELECT
AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,
NULL TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(GJL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(GJL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
---XLA_AE_LINES XAL,
AP_INVOICES_ALL AIA,
AP_SUPPLIERS APS,
HR_OPERATING_UNITS HOU,
JAI_RCV_TP_INVOICES JRT,
RCV_SHIPMENT_HEADERS RSH,
ORG_ORGANIZATION_DEFINITIONS OOD
--AP_INVOICE_DISTRIBU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND TO_NUMBER(GJL.REFERENCE_2) = AIA.INVOICE_ID
AND AIA.INVOICE_ID = JRT.INVOICE_ID(+)
AND JRT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID(+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID (+)
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GJL.GL_SL_LINK_TABLE = 'APECL'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJL.REFERENCE_10 = 'CHARGE'
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
-----GL_SL_LINK_TABLE = 'XLAJEL'
SELECT DISTINCT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,--AIA.INVOICE_ID,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,XDL.SOURCE_DISTRIBUTION_ID_NUM_1 TRANSACTION_ID,GJL.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XDL.UNROUNDED_ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(XDL.UNROUNDED_ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
RCV_TRANSACTIONS RT,
AP_INVOICES_ALL AIA,
RCV_SHIPMENT_HEADERS RSH,
HR_OPERATING_UNITS HOU,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_SUPPLIERS APS
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAH.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AIDA.INVOICE_DISTRIBUTION_ID
AND AIDA.RCV_TRANSACTION_ID = RT.TRANSACTION_ID (+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID (+)
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND AIDA.ORG_ID = HOU.ORGANIZATION_ID
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND GCC.SEGMENT5 = '1297'
AND AIDA.INVOICE_ID NOT IN (SELECT NVL(INVOICE_ID,0) FROM APPS.JAI_RCV_TP_INVOICES )
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GJL.GL_SL_LINK_TABLE = 'XLAJEL'
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
-----GL_SL_LINK_TABLE = 'XLAJEL' for third party
SELECT DISTINCT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,XDL.SOURCE_DISTRIBUTION_ID_NUM_1 TRANSACTION_ID,GJL.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XDL.UNROUNDED_ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(XDL.UNROUNDED_ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
JAI_RCV_TP_INVOICES JRT,
---RCV_TRANSACTIONS RT,
AP_INVOICES_ALL AIA,
RCV_SHIPMENT_HEADERS RSH,
HR_OPERATING_UNITS HOU,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_SUPPLIERS APS
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAH.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AIDA.INVOICE_DISTRIBUTION_ID
AND AIDA.INVOICE_ID = JRT.INVOICE_ID (+)
AND JRT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID (+)
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND AIDA.ORG_ID = HOU.ORGANIZATION_ID
AND JRT.VENDOR_ID = APS.VENDOR_ID
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GJL.GL_SL_LINK_TABLE = 'XLAJEL'
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
----------GL_SL_LINK_TABLE IS NULL AND GJL.REFERENCE_2 IS  NULL
SELECT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,GJL.JE_LINE_NUM TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XAL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(XAL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
---XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALl AIDA,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
AP_SUPPLIERS APS,
ORG_ORGANIZATION_DEFINITIONS OOD,
HR_OPERATING_UNITS HOU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.REFERENCE_7 = XAL.AE_HEADER_ID
AND GIR.REFERENCE_8 = XAL.AE_LINE_NUM
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND GIR.REFERENCE_5 = XAH.ENTITY_ID
AND GIR.REFERENCE_6 = XAH.EVENT_ID
---AND XAL.UPG_TAX_REFERENCE_ID1 = AIA.INVOICE_ID
AND XAl.SOURCE_ID = AIDA.INVOICE_DISTRIBUTION_ID
AND AIDA.INVOICE_ID NOT IN (SELECT NVL(INVOICE_ID,0) FROM APPS.JAI_RCV_TP_INVOICES )
AND AIDA.RCV_TRANSACTION_ID = RT.TRANSACTION_ID (+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID (+)
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG -- '11'
AND GCC.SEGMENT2 NOT IN ('02','03','04','05','06','08')
AND GJL.REFERENCE_2 IS NULL
AND GJL.GL_SL_LINK_TABLE IS NULL
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= '30-NOV-2013'  ---:LDATE
--AND TRUNC(GJL.EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
----------GL_SL_LINK_TABLE IS NULL AND GJL.REFERENCE_2 IS  NULL
SELECT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,GJL.JE_LINE_NUM TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XAL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(XAL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
---XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICES_ALL AIA,
RCV_SHIPMENT_HEADERS RSH,
JAI_RCV_TP_INVOICES JRT,
AP_SUPPLIERS APS,
ORG_ORGANIZATION_DEFINITIONS OOD,
HR_OPERATING_UNITS HOU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.REFERENCE_7 = XAL.AE_HEADER_ID
AND GIR.REFERENCE_8 = XAL.AE_LINE_NUM
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND GIR.REFERENCE_5 = XAH.ENTITY_ID
AND GIR.REFERENCE_6 = XAH.EVENT_ID
AND XAL.UPG_TAX_REFERENCE_ID1 = AIA.INVOICE_ID
AND AIA.INVOICE_ID = JRT.INVOICE_ID
AND JRT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID (+)
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
---AND AIA.INVOICE_ID = AIDA.INVOICE_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG -- '11'
AND GCC.SEGMENT2 NOT IN ('02','03','04','05','06','08')
AND GJL.REFERENCE_2 IS NULL
AND GJL.GL_SL_LINK_TABLE IS NULL
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= '30-NOV-2013'  ---:LDATE
--AND TRUNC(GJL.EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
SELECT DISTINCT  AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,TO_NUMBER(SOURCE_DISTRIBUTION_ID_NUM_1) TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(TO_NUMBER(XDL.UNROUNDED_ACCOUNTED_DR),0) DEBIT_AMOUNT,NVL(TO_NUMBER(XDL.UNROUNDED_ACCOUNTED_CR),0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_INVOICES_ALL AIA,
AP_SUPPLIERS APS,
HR_OPERATING_UNITS HOU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.REFERENCE_7 = XAL.AE_HEADER_ID
AND GIR.REFERENCE_8 = XAL.AE_LINE_NUM
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND GIR.REFERENCE_5 = XAH.ENTITY_ID
AND GIR.REFERENCE_6 = XAH.EVENT_ID
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AIDA.INVOICE_DISTRIBUTION_ID(+)
AND AIDA.RCV_TRANSACTION_ID = RT.TRANSACTION_ID(+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID (+)
AND XDL.ALLOC_TO_SOURCE_ID_NUM_1 = AIA.INVOICE_ID
AND XDL.ALLOC_TO_ENTITY_CODE = 'AP_INVOICES'
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GCC.SEGMENT2 NOT IN ('11')
AND GJL.REFERENCE_2 IS  NULL
AND GJL.GL_SL_LINK_TABLE IS NULL
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= '30-NOV-2013'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
----GL_SL_LINK_TABLE IS NULL AND GJL.REFERENCE_2 IS NOT NULL
SELECT DISTINCT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,RSH.SHIPMENT_HEADER_ID TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(GJL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(GJL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_SUPPLIERS APS,
HR_OPERATING_UNITS HOU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.REFERENCE_2 = TO_CHAR(AIA.INVOICE_ID)
AND AIA.INVOICE_ID = AIDA.INVOICE_ID(+)
AND AIDA.RCV_TRANSACTION_ID = RT.TRANSACTION_ID (+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID (+)
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJL.REFERENCE_2 IS NOT NULL
AND GJL.GL_SL_LINK_TABLE IS NULL
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= '30-NOV-2013' -- :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
SELECT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,TO_NUMBER(GIR.REFERENCE_8) TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XDL.UNROUNDED_ACCOUNTED_DR,0)  DEBIT_AMOUNT,NVL(XDL.UNROUNDED_ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_INVOICES_ALL AIA,
AP_SUPPLIERS APS,
HR_OPERATING_UNITS HOU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.REFERENCE_7 = XAL.AE_HEADER_ID
AND GIR.REFERENCE_8 = XAL.AE_LINE_NUM
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AIDA.INVOICE_DISTRIBUTION_ID
AND AIDA.RCV_TRANSACTION_ID = RT.TRANSACTION_ID (+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID (+)
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND AIA.INVOICE_ID NOT IN (SELECT NVL(INVOICE_ID,0) FROM JAI_RCV_TP_INVOICES)
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG
AND GCC.SEGMENT2 NOT IN ('02','03','04','05','06','08')
--some discounts are there in XDL table, due to that debits minus credits then we will get correct amt
AND GJL.GL_SL_LINK_TABLE IS NULL
AND GJL.STATUS = 'P'
AND TRUNC(EFFECTIVE_DATE) >= '01-DEC-2013' --:LDATE
--AND TRUNC(EFFECTIVE_DATE) <=  :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
SELECT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,TO_NUMBER(GIR.REFERENCE_8) TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XDL.UNROUNDED_ACCOUNTED_DR,0)  DEBIT_AMOUNT,NVL(XDL.UNROUNDED_ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
JAI_RCV_TP_INVOICES JRT,
RCV_SHIPMENT_HEADERS RSH,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_INVOICES_ALL AIA,
AP_SUPPLIERS APS,
HR_OPERATING_UNITS HOU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.REFERENCE_7 = XAL.AE_HEADER_ID
AND GIR.REFERENCE_8 = XAL.AE_LINE_NUM
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AIDA.INVOICE_DISTRIBUTION_ID
AND AIA.INVOICE_ID = JRT.INVOICE_ID
AND JRT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND JRT.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG
AND GCC.SEGMENT2 NOT IN ('02','03','04','05','06','08')
--some discounts are there in XDL table, due to that debits minus credits then we will get correct amt
AND GJL.GL_SL_LINK_TABLE IS NULL
AND GJL.STATUS = 'P'
AND TRUNC(EFFECTIVE_DATE) >= '01-DEC-2013' --:LDATE
--AND TRUNC(EFFECTIVE_DATE) <=  :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
)
GROUP BY
OPERATING_UNIT,UNIT_NAME,
        ORGANIZATION_ID,ORG_CODE,ORG_NAME,
        RECEIPT_NUM,
VENDOR_CODE,
VENDOR_NAME ;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Operating Unit'||CHR(9)||'Unit Name'||CHR(9)||'Organization Id'||CHR(9)||
                     'Org Code'||CHR(9)||'Org Name'||CHR(9)||'Receipt Num'||CHR(9)||'Vendor Code'||CHR(9)||'Vendor Name'||
CHR(9)||'Total Dr'||CHR(9)||'Total Cr'||CHR(9)||'Net');
FOR I IN CUR1 LOOP
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,I.OPERATING_UNIT||CHR(9)||I.UNIT_NAME||CHR(9)||I.ORGANIZATION_ID||CHR(9)||
                     I.ORG_CODE||CHR(9)||I.ORG_NAME||CHR(9)||I.RECEIPT_NUM||CHR(9)||I.VENDOR_CODE||CHR(9)||I.VENDOR_NAME||CHR(9)||
I.TOTAL_DEBIT||CHR(9)||I.TOTAL_CREDIT||CHR(9)||I.NET);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'No Data For This Organization');
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLCODE||','||SQLERRM);
END;

Join between PA_EXPEND_ITEMS_ADJUST2_V , PA_COST_DIST_LINES_V, PA_PROJECTS_ALL

SELECT * FROM
FROM  PA_EXPEND_ITEMS_ADJUST2_V PEI,
 PA_COST_DIST_LINES_V PCD,
 GL_CODE_COMBINATIONS GCC,
 PA_PROJECTS_ALL PPA
WHERE PEI.EXPENDITURE_ITEM_ID=PCD.EXPENDITURE_ITEM_ID
AND   PCD.DR_CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND   (PEI.TRANSACTION_SOURCE IS NULL OR PEI.TRANSACTION_SOURCE='AP INVOICE')
AND   PEI.PROJECT_ID=PPA.PROJECT_ID
--AND   PEI.PROJECT_NUMBER BETWEEN DECODE(:PA_SEGMENT1,'ALL',PEI.PROJECT_NUMBER,:PA_SEGMENT1) AND DECODE(:PA_SEGMENT1_1,'ALL',PEI.PROJECT_NUMBER,:PA_SEGMENT1_1)
--AND   TRUNC(PEI.CREATION_DATE) BETWEEN NVL(:P_FROM_DATE,TRUNC(PEI.CREATION_DATE)) AND NVL(:P_TO_DATE,TRUNC(PEI.CREATION_DATE))

Join between MTL_MATERIAL_TRANSACTIONS and PA_PROJECTS_ALL

FROM   MTL_MATERIAL_TRANSACTIONS  MMT
       ,PA_PROJECTS_ALL PPA
       ,PA_TASKS PT
       ,MTL_SYSTEM_ITEMS_B MSI
WHERE  MMT.SOURCE_PROJECT_ID=PPA.PROJECT_ID
AND    MMT.SOURCE_TASK_ID=PT.TASK_ID
AND    PPA.PROJECT_ID=PT.PROJECT_ID
AND    MSI.INVENTORY_ITEM_ID=MMT.INVENTORY_ITEM_ID
AND    MSI.ORGANIZATION_ID=MMT.ORGANIZATION_ID
--AND MMT.PM_COST_COLLECTED IS NULL
--AND    PPA.SEGMENT1 BETWEEN DECODE(:PA_SEGMENT1,'ALL',PPA.SEGMENT1,:PA_SEGMENT1) AND DECODE(:PA_SEGMENT1_1,'ALL',PPA.SEGMENT1,:PA_SEGMENT1_1)
--AND    TRUNC(MMT.TRANSACTION_DATE) BETWEEN NVL(:P_FROM_DATE,TRUNC(MMT.TRANSACTION_DATE)) AND NVL(:P_TO_DATE,TRUNC(MMT.TRANSACTION_DATE))

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
;