Oracle SQL + How to concatenate time to a date

1). select to_date ('17-Feb-2019' || ' 23:59:00', 'dd-mon-yyyy hh24:mi:ss') from dual;

2). If you're passing a DATE variable and a STRING below query should works

select to_date ( to_char(DATE_VARIABLE, 'dd-mon-yyyy') || ' 23:59:00', 'dd-mon-yyyy hh24:mi:ss') from( select to_date('17-Feb-2019', 'dd-mon-yyyy') as DATE_VARIABLE   from dual);

Thanks

Amar Alam

Oracle Cloud + Query to get AR Receipts Recommendations Details

 SELECT 

    acra.receipt_number            receipt_number

   ,rcta.trx_number                recommendation

   ,acr.match_score_value          reference_score

   ,DECODE(acr.match_reason_code ,'AR_AA_BELOW_TRX_TSLD','Below transaction threshold','AR_AM_INV_THRESHOLD','Above transaction threshold') reason

   ,apsa.amount_due_remaining      balance

   ,DECODE(tta.type, 'INV', 'Invoice', 'CM', 'Credit Memo') document_type

   ,hp.party_name                  customer

   ,acr.recommendation_status      accepted

   ,acr.amount_applied

   ,SUBSTR(apsa.trx_number,'-0',7)||' '||acr.amount_applied Reference_number

FROM

    ar_cash_receipts_all            acra

   ,ar_receipt_methods              arm

   ,ar_cash_remit_refs_all          acrfa

   ,ar_cash_recos_all               acr

   ,ar_cash_reco_lines_all          acrla

   ,ra_customer_trx_all             rcta

   ,ar_payment_schedules_all        apsa

   ,ra_cust_trx_types_all           tta

   ,hz_cust_accounts                hca

   ,hz_parties                      hp

   ,hz_party_sites                  hps

   ,hz_cust_site_uses_all           hcsu

   ,hz_cust_acct_sites_all          hcsa

WHERE 1 = 1

    AND acra.receipt_number          = '111300952062031'

AND acra.receipt_method_id       = arm.receipt_method_id   

AND acra.cash_receipt_id         = acrfa.cash_receipt_id

AND acrfa.remit_reference_id     = acr.remit_reference_id

AND acr.recommendation_id        = acrla.recommendation_id

AND acr.resolved_matching_number = rcta.trx_number

AND rcta.customer_trx_id         = apsa.customer_trx_id

AND rcta.cust_trx_type_seq_id    = tta.cust_trx_type_seq_id

AND rcta.bill_to_customer_id     = hca.cust_account_id

AND acr.customer_id              = hca.cust_account_id

AND hca.party_id                 = hp.party_id

AND hp.party_id                  = hps.party_id

AND acr.customer_site_use_id     = hcsu.site_use_id

AND hcsu.cust_acct_site_id       = hcsa.cust_acct_site_id

AND hcsa.cust_account_id         = hca.cust_account_id

AND hcsa.party_site_id           = hps.party_site_id

Thanks

Amar Alam

Oracle Cloud + Query to get The Data Access for an User

SELECT USERNAME,PERSON_NUMBER,person_name,ROLE_NAME,ROLE_CODE,SECURITY_CONTEXT ,SECURITY_CONTEXT_VALUE
FROM(
SELECT
 PU.USERNAME ,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 ROLE.ROLE_NAME ROLE_CODE,
 'DATA ACCESS SET' security_context,
 GL.NAME security_context_value
 FROM
 fusion.fun_user_role_data_asgnmnts role,
 fusion.gl_access_sets gl,
 fusion.per_users pu,
 per_all_people_f papf,
 per_person_names_f_v ppnf,
 ase_role_b r,
 ase_role_tl rtl
WHERE
 GL.ACCESS_SET_ID = ROLE.ACCESS_SET_ID
 AND PU.USER_GUID = ROLE.USER_GUID
 AND ROLE.ROLE_NAME=R.CODE
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
 AND papf.person_id=ppnf.person_id
 AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate
 AND nvl(r.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
 PU.USERNAME,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 ROLE.ROLE_NAME ROLE_CODE,
 'BUSINESS UNIT' security_context,
 BU.BU_NAME security_context_value
FROM
 fusion.fun_all_business_units_v bu,
 fusion.fun_user_role_data_asgnmnts role,
 fusion.per_users pu,
 per_all_people_f papf,
 per_person_names_f_v ppnf,
 ase_role_b r,
 ase_role_tl rtl
WHERE
 ROLE.ORG_ID = BU.BU_ID
 AND PU.USER_GUID = ROLE.USER_GUID
 AND ROLE.ROLE_NAME=R.CODE
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
 pu.username,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 role.role_name role_code,
 'LEDGERS' security_context,
 led.name security_context_value
FROM
 fusion.gl_ledgers led,
 fusion.fun_user_role_data_asgnmnts role,
 fusion.per_users pu,
 per_all_people_f papf,
 per_person_names_f_v ppnf,
 ase_role_b r,
 ase_role_tl rtl
WHERE
 role.ledger_id = led.ledger_id
 and pu.user_guid = role.user_guid
 and role.role_name=r.code
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
 pu.username,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 role.role_name role_code,
 'ASSET BOOK' security_context,
 book.book_type_name security_context_value
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.FA_BOOK_CONTROLS BOOK,
 FUSION.PER_USERS PU,
 per_all_people_f papf,
 per_person_names_f_v ppnf,
 ase_role_b r,
 ase_role_tl rtl
WHERE
 BOOK.BOOK_CONTROL_ID = ROLE.BOOK_ID
 AND PU.USER_GUID = ROLE.USER_GUID
 AND ROLE.ROLE_NAME=R.CODE
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
pu.username,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 role.role_name role_code,
 'INTERCOMPANY ORGANIZATION' security_context,
 INTERCO.INTERCO_ORG_NAME security_context_value
FROM
 fusion.fun_user_role_data_asgnmnts role,
 fusion.fun_interco_organizations interco,
 fusion.per_users pu,
 per_all_people_f papf,
 per_person_names_f_v ppnf,
 ase_role_b r,
 ase_role_tl rtl
WHERE
 INTERCO.INTERCO_ORG_ID = ROLE.INTERCO_ORG_ID
 AND PU.USER_GUID = ROLE.USER_GUID
  AND ROLE.ROLE_NAME=R.CODE
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
pu.username,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 role.role_name role_code,
 'COST ORGANIZATION' security_context,
 COST.COST_ORG_NAME security_context_value
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.CST_COST_ORGS_V COST,
 FUSION.PER_USERS PU,
  per_all_people_f papf,
  per_person_names_f_v ppnf,
  ase_role_b r,
 ase_role_tl rtl
WHERE
 COST.COST_ORG_ID = ROLE.CST_ORGANIZATION_ID
 AND PU.USER_GUID = ROLE.USER_GUID
  AND ROLE.ROLE_NAME=R.CODE
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
pu.username,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 role.role_name role_code,
 'MANUFACTURING PLANT' security_context,
 MFG.DEF_SUPPLY_SUBINV security_context_value
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.RCS_MFG_PARAMETERS MFG,
 FUSION.PER_USERS PU,
 per_all_people_f papf,
 per_person_names_f_v ppnf,
  ase_role_b r,
 ase_role_tl rtl
WHERE
 MFG.ORGANIZATION_ID = ROLE.MFG_ORGANIZATION_ID
 AND PU.USER_GUID = ROLE.USER_GUID
  AND ROLE.ROLE_NAME=R.CODE
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
pu.username,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 role.role_name role_code,
 'CONTROL BUDGET' security_context,
 BUDGET.NAME security_context_value
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.XCC_CONTROL_BUDGETS BUDGET,
 FUSION.PER_USERS PU,
  per_all_people_f papf,
  per_person_names_f_v ppnf,
  ase_role_b r,
 ase_role_tl rtl
WHERE
 BUDGET.CONTROL_BUDGET_ID = ROLE.CONTROL_BUDGET_ID
 AND PU.USER_GUID = ROLE.USER_GUID
  AND ROLE.ROLE_NAME=R.CODE
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
pu.username,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 role.role_name role_code,
 'REFERENCE DATA SET' security_context,
 ST.SET_NAME security_context_value
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.FND_SETID_SETS_VL ST,
 FUSION.PER_USERS PU,
 per_all_people_f papf,
 per_person_names_f_v ppnf,
  ase_role_b r,
 ase_role_tl rtl
WHERE
 ST.SET_ID = ROLE.SET_ID
 AND PU.USER_GUID = ROLE.USER_GUID
  AND ROLE.ROLE_NAME=R.CODE
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
pu.username,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 role.role_name role_code,
 'INVENTORY ORGANIZATION' security_context,
 INV.ORGANIZATION_CODE security_context_value
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.INV_ORG_PARAMETERS INV,
 FUSION.PER_USERS PU,
 per_all_people_f papf,
 per_person_names_f_v ppnf,
  ase_role_b r,
 ase_role_tl rtl
WHERE
 INV.ORGANIZATION_ID = ROLE.INV_ORGANIZATION_ID
 AND PU.USER_GUID = ROLE.USER_GUID
  AND ROLE.ROLE_NAME=R.CODE
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
pu.username,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 role.role_name role_code,
 'PROJECT ORGANIZATION CLASSIFICATION' security_context,
 HR.CLASSIFICATION_CODE security_context_value
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.HR_ORG_UNIT_CLASSIFICATIONS_F HR,
 FUSION.PER_USERS PU,
  per_all_people_f papf,
  per_person_names_f_v ppnf,
  ase_role_b r,
 ase_role_tl rtl
WHERE
 HR.ORG_UNIT_CLASSIFICATION_ID = ROLE.ORG_ID
 AND PU.USER_GUID = ROLE.USER_GUID
  AND ROLE.ROLE_NAME=R.CODE
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y' 
 )
 WHERE username='aalam'
 ORDER BY USERNAME,ROLE_NAME,SECURITY_CONTEXT

Thanks
Amar Alam