APPS.JA_CN_CR_EXPORT_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 70

SELECT DISTINCT Customer_Number,Customer_Name,Customer_Short_Name
FROM
(
  --from AR Transction
  SELECT
  hp.PARTY_NUMBER Customer_Number,
  hp.PARTY_NAME Customer_Name,
  hp.PARTY_NAME Customer_Short_Name
  FROM HZ_PARTIES hp ,HZ_CUST_ACCOUNTS hca
  ,RA_CUSTOMER_TRX rcta ,RA_CUST_TRX_LINE_GL_DIST_ALL gd
  WHERE hp.PARTY_ID = hca.PARTY_ID
  AND hca.CUST_ACCOUNT_ID = rcta.BILL_TO_CUSTOMER_ID
  AND rcta.CUSTOMER_TRX_ID = gd.CUSTOMER_TRX_ID
  AND rcta.legal_entity_id = pn_legal_entity_id
  AND rcta.COMPLETE_FLAG = 'Y'
  AND to_char(gd.GL_DATE,'YYYY') = pv_accounting_year
  AND gd.ACCOUNT_CLASS = 'REC'
  AND gd.LATEST_REC_FLAG = 'Y'
  UNION
  -- from AR Receipts
  SELECT
  PARTY.PARTY_NUMBER Customer_Number,
  PARTY.PARTY_NAME Customer_Name,
  PARTY.PARTY_NAME Customer_Short_Name
  FROM
  HZ_PARTIES PARTY,
  HZ_CUST_ACCOUNTS CUST,
  AR_CASH_RECEIPTS_ALL CR,
  AR_CASH_RECEIPT_HISTORY_ALL CRH_REM,
  AR_BATCHES_ALL REM_BAT,
  AR_BATCHES_ALL BAT,
  AR_BATCHES_ALL BAT_BR,
  AR_CASH_RECEIPT_HISTORY_ALL CRH_FIRST_POSTED
  WHERE
  CR.PAY_FROM_CUSTOMER = CUST.CUST_ACCOUNT_ID (+)
  AND CUST.PARTY_ID = PARTY.PARTY_ID(+)
  AND CRH_REM.CASH_RECEIPT_ID (+) = CR.CASH_RECEIPT_ID
  AND CRH_REM.ORG_ID (+) = CR.ORG_ID
  AND NOT EXISTS
  ( SELECT CASH_RECEIPT_HISTORY_ID
     FROM AR_CASH_RECEIPT_HISTORY_ALL CRH3
     WHERE CRH3.STATUS = 'REMITTED'
     AND CRH3.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
     AND CRH3.CASH_RECEIPT_HISTORY_ID < CRH_REM.CASH_RECEIPT_HISTORY_ID)
  AND CRH_REM.STATUS(+) = 'REMITTED'
  AND CRH_REM.BATCH_ID = REM_BAT.BATCH_ID (+)
  AND CRH_REM.ORG_ID = REM_BAT.ORG_ID (+)
  AND REM_BAT.TYPE (+) = 'REMITTANCE'
  AND CRH_FIRST_POSTED.BATCH_ID = BAT.BATCH_ID (+)
  AND CRH_FIRST_POSTED.ORG_ID = BAT.ORG_ID (+)
  AND BAT.TYPE (+) = 'MANUAL'
  AND CRH_FIRST_POSTED.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID
  AND CRH_FIRST_POSTED.ORG_ID(+) = CR.ORG_ID
  AND CRH_FIRST_POSTED.FIRST_POSTED_RECORD_FLAG(+) = 'Y'
  AND CRH_FIRST_POSTED.BATCH_ID = BAT_BR.BATCH_ID (+)
  AND CRH_FIRST_POSTED.ORG_ID = BAT_BR.ORG_ID (+)
  AND BAT_BR.TYPE (+) = 'BR'
  AND PARTY.PARTY_NUMBER IS NOT NULL
  AND CR.Legal_Entity_Id = pn_legal_entity_id
  AND CR.Status IN ('APP','UNAPP','UNID') --Applied, Unapplied, Unidentified
  AND to_char(CRH_FIRST_POSTED.gl_date,'YYYY') = pv_accounting_year
)
ORDER BY Customer_Number;