Friday, September 26, 2025

currentAppUi built in variable in VBCS

$global.currentAppUi.id:- The id of the App UI

$global.currentAppUi.urlId:-The id of the App UI as shown in the URL

$global.currentAppUi.displayName:- The display name for the App UI

$global.currentAppUi.description:- The description of the App UI

$global.currentAppUi.defaultPage:- The default page of the App UI (if there is one)

$global.currentAppUi.defaultFlow:- The default flow of the App UI (if there is one)

$global.currentAppUi.applicationStripe:- The stripe of the custom App UI

$global.currentAppUi.pillarTheme:- The pillar theme to use for the App UI

$global.currentAppUi.pillarThemeMode:- The pillar theme mode to use for the App UI

$global.currentAppUi.icon:- The icon of the custom App UI

$global.currentAppUi.usage:- (This variable is reserved for Oracle Cloud Applications)

$global.currentAppUi.menuDisplayName:- The name of the custom App UI

$global.currentAppUi.extensible: (Boolean) If this App UI can be extended

currentFlow built in variable in VBCS

If there is a routerFlow in the page, the $page.currentFlow variable can be used to retrieve the id of this flow.


$page.currentFlow:- The id of the current flow.

currentPage Built in variable in VBCS

  • To access some of the current page's metadata, such as ID and title, there is a built-in variable named currentPage on the application object. 
  • The currentPage variable automatically updates as the current page changes during navigation. 
  • This can be used to update a navigation component with the currently selected page.

$flow.currentPage:- The id of the current page for this flow. 

$application.currentPage.id:-The path of the current page. The path describes the location of the page in the flow hierarchy.

$application.currentPage.path:-The path of the current page for the application. The path describes the location of the page in the flow hierarchy.

$application.currentPage.title:- The title of the current page. The title is formed by prepending all the titles of the shells in the flow hierarchy to the current page.

Saturday, February 1, 2025

Query to get the Concurrent Program along with Value Set details

SELECT fcpl1.user_concurrent_program_name
      ,fdfcuv.end_user_column_name
      ,ffvs.flex_value_set_name value_set_name
      ,ffvt.application_table_name
  ,ffvt.value_column_name
      ,ffvt.additional_where_clause
  FROM apps.fnd_concurrent_programs fcp1
      ,apps.fnd_descr_flex_col_usage_vl fdfcuv
      ,apps.fnd_flex_value_sets ffvs
      ,apps.fnd_flex_validation_tables ffvt
      ,apps.fnd_concurrent_programs_tl fcpl1
 WHERE 1 = 1     
   AND fcp1.concurrent_program_id IN (SELECT DISTINCT fcp.concurrent_program_id
                                        FROM apps.fnd_concurrent_programs fcp
                                            ,apps.fnd_concurrent_programs_tl fcpl
                                            ,apps.fnd_concurrent_requests fcr
                                       WHERE 1 = 1
                                         AND fcp.concurrent_program_id = fcpl.concurrent_program_id
                                         AND fcpl.language = 'US'
                                         AND fcr.concurrent_program_id = fcpl.concurrent_program_id
                                         AND trunc(fcr.request_date) BETWEEN :p_from_date AND :p_to_date
                                         AND fcp.concurrent_program_name LIKE '%XXCPNAME%'
)
   AND fdfcuv.descriptive_flexfield_name = '$SRS$.' ||fcp1.concurrent_program_name
   AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
   AND ffvt.flex_value_set_id = ffvs.flex_value_set_id
   AND fcp1.concurrent_program_id = fcpl1.concurrent_program_id
ORDER BY fcpl1.user_concurrent_program_name 

Monday, January 27, 2025

Query to get Unposted Journals in Oracle APPS R12

SELECT gjh.accrual_rev_flag accrual_rev_flag
      ,gjh.accrual_rev_period_name accrual_rev_period_name
  ,gjh.creation_date journal_creation_date
  ,gjh.currency_code currency_code
  ,gjh.doc_sequence_value doc_sequence_value
  ,fu.email_address email_address
  ,gjh.je_category je_category
  ,gjh.last_update_date last_update_date
  ,gjh.name name journal_header_name
  ,gsobv.name ledger_name
  ,gjh.period_name period_name
  ,gjh.posted_date posted_date
  ,gjh.reversed_je_header_id reversed_je_header_id
  ,gjh.running_total_accounted_dr running_total_accounted_dr
  ,gjh.status status
  ,fu.user_name user_name
  ,gjsv.user_je_source_name user_je_source_name
  ,fu1.user_name user_name
  ,gjbv.name journal_batch_name
  ,gjbv.posted_by_name posted_by_name
  ,ppx.email_address email_address
  ,period.period_set
  ,CASE WHEN ( ( MIN(fad.attached_document_id) ) IS NOT NULL OR ( MIN(fad1.attached_document_id) ) IS NOT NULL ) 
        THEN 'Y' 
ELSE 'N' 
END document_exists
  FROM applsys.fnd_user fu
      ,gl.gl_je_headers gjh
  ,apps.gl_sets_of_books_v gsobv
  ,applsys.fnd_user fu1
  ,apps.gl_je_sources_v gjsv
  ,apps.gl_je_batches_v gjbv
  ,apps.per_people_x ppx
  ,applsys.fnd_attached_documents fad
  ,applsys.fnd_attached_documents fad1
  ,(SELECT gpv.period_name AS period_name
          ,MAX((gpv.period_set_name||gpv.period_year||LPAD(gpv.period_num,2,0))) AS period_set 
  FROM apps.gl_periods_v gpv 
WHERE (gpv.period_year >= 2024) 
GROUP BY gpv.period_name
) period
 WHERE ( (gsobv.set_of_books_id = gjh.ledger_id)
   AND (fu1.employee_id = ppx.person_id(+))
   AND (gjh.last_updated_by = fu.user_id(+))
   AND (gjh.created_by = fu1.user_id(+))
   AND (gjh.je_batch_id = gjbv.je_batch_id(+))
   AND ((TO_CHAR(gjbv.je_batch_id)) = fad.pk1_value(+))
   AND ((TO_CHAR(gjh.je_header_id)) = fad1.pk2_value(+))
   AND (gjh.je_source = gjsv.je_source_name)
   AND (gjh.period_name = period.period_name(+)))
   AND (fad.entity_name(+) = 'GL_JE_BATCHES')
   AND (fad1.entity_name(+) = 'GL_JE_HEADERS')
   AND (gjh.posted_date >= :p_posted_date)
   AND (gjsv.user_je_source_name IN (:p_source))
   AND (gjh.status IN ('U'))
   AND (gjh.period_name = :p_period_name)
GROUP BY period.period_set
        ,gjh.accrual_rev_flag
,gjh.accrual_rev_period_name
,gjh.creation_date
,gjh.currency_code
,gjh.doc_sequence_value
,fu.email_address,gjh.je_category
,gjh.last_update_date
,gjh.name
,gsobv.name
,gjh.period_name
,gjh.posted_date
,gjh.reversed_je_header_id
,gjh.running_total_accounted_dr
,gjh.running_total_dr
,gjh.status
,fu.user_name
,gjsv.user_je_source_name
,fu1.user_name
,gjbv.name
,gjbv.posted_by_name
,ppx.email_address
ORDER BY gsobv.name ASC 
        ,period.period_set ASC 
,gjsv.user_je_source_name ASC 
,gjh.doc_sequence_value ASC 

Sunday, January 26, 2025

Query to get Employee Responsibility details in Oracle Fusion

SELECT papf.person_number
      ,ppnfv.display_name person_display_name
  ,paam.assignment_name
  ,hl.meaning AS representative_type
  ,par.asg_responsibility_id
  ,par.responsibility_name
  ,par.business_unit_id
  ,par.work_contacts_flag
  FROM per_asg_responsibilities par
      ,per_all_people_f papf
  ,per_all_assignments_m paam
  ,per_person_names_f_v ppnfv
  ,hcm_lookups hl
 WHERE par.person_id = papf.person_id
   AND par.assignment_id = paam.assignment_id
   AND par.person_id = ppnfv.person_id
   AND par.responsibility_type = hl.lookup_code
   AND hl.lookup_type = 'PER_RESPONSIBILITY_TYPES'
   AND par.status = 'Active'
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN ppnfv.effective_start_date AND ppnfv.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN par.start_date AND NVL(par.end_date,TO_DATE('31-12-4712','DD-MM-YYYY'))
ORDER BY papf.person_number

Employee assigned Responsibilities in Oracle Fusion

SELECT (SELECT DISTINCT person_number
          FROM per_all_people_f per
         WHERE per.person_id = aor.person_id
       ) person_number
  ,(SELECT DISTINCT full_name
          FROM per_person_names_f per
         WHERE per.person_id = aor.person_id
   AND name_type = 'GLOBAL'
    ) person_name
  ,aor.responsibility_name
  ,TO_CHAR(aor.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') start_date
  ,TO_CHAR(aor.end_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') end_date
  ,aor.responsibility_type
  ,aor.status
  ,(SELECT hou.name
          FROM hr_all_organization_units hou
         WHERE hou.organization_id = aor.business_unit_id
       ) business_unit_name
  ,(SELECT hou.name
          FROM hr_all_organization_units hou
         WHERE hou.organization_id = aor.legal_entity_id
        ) legal_entity
  ,(SELECT hou.name
          FROM hr_all_organization_units hou
         WHERE hou.organization_id = aor.organization_id
        ) department_name
      ,(SELECT location_name
          FROM hr_locations hl
         WHERE hl.location_id = aor.location_id
        ) location_name
  ,(SELECT DISTINCT name
          FROM hr_all_positions_f_vl pp
         WHERE pp.position_id = aor.position_id
        ) position_name
  ,(SELECT DISTINCT name
          FROM per_jobs_f_tl pj
         WHERE pj.job_id = aor.job_id
        ) job_name
  ,(SELECT DISTINCT name
          FROM per_grades_f_tl pg
         WHERE pg.grade_id = aor.grade_id
        ) grade_name
  ,aor.assignment_category
  ,(SELECT pay.payroll_name
          FROM pay_all_payrolls_f pay
         WHERE pay.payroll_id = aor.payroll_id
           AND TRUNC(SYSDATE) BETWEEN pay.effective_start_date AND pay.effective_end_date
        ) payroll_name
  ,(SELECT name
          FROM per_legislative_data_groups_vl pld
         WHERE pld.legislative_data_group_id = aor.legislative_data_group_id
            ) legislative_data_group
  FROM per_asg_responsibilities aor

Thursday, January 23, 2025

Business Classification in Oracle Supplier R12 - POS_BUSINESS_CLASSIFICATIONS

Query:
SELECT * 
  FROM fnd_lookup_values_vl flv
 WHERE lookup_type = 'POS_BUSINESS_CLASSIFICATIONS'
   AND flv.enabled_flag='Y' 
   --AND lookup_code = '8010'
   AND flv.start_date_active < sysdate 
   AND (flv.end_date_active > sysdate or flv.end_date_active is null) 


Application Developer->Application->Lookups->Application Object Library 

Query Type with POS_BUSINESS_CLASSIFICATIONS

SQL Query To Get Invoice Validation And Accounting Status For The Given Check Number (Doc ID 2975480.1)

SELECT aca.check_number
      ,aia.invoice_id
  ,aia.invoice_num
  ,aia.invoice_amount
  ,distribution_line_number
  ,aida.accrual_posted_flag
  ,decode(aida.match_status_flag,
              'A', 'Validated',
              'N', 'Never Validated',
              'T', 'Needs Revalidation',
              'S', 'Stopped') "Invoice Validation Status"
  ,decode(ap_invoices_pkg.get_posting_status(aia.invoice_id),
              'S', 'Selected for Accounting',
              'P', 'Partial',
              'N', 'Unaccounted',
              'Y', 'Accounted',
              'Unaccounted') "Invoice Accounting Status",
      aida.invoice_distribution_id,
      aida.accounting_event_id,
      apps.ap_invoices_pkg.get_approval_status(aia.invoice_id,
                                               aia.invoice_amount,
                                               aia.payment_status_flag,
                                               aia.invoice_type_lookup_code)"Invoice Approval Status",
      decode(aida.posted_flag,
              'Y', 'Posted',
              'N', 'Not Posted',
              'Not Posted') "Invoice Posting Status"   
FROM ap_checks_all aca
    ,ap_invoice_payments_all aipa
,ap_invoices_all aia
,ap_suppliers asa
,ap_invoice_distributions_all aida
WHERE aca.check_id = aipa.check_id
AND aipa.invoice_id = aia.invoice_id
AND aia.vendor_id = asa.vendor_id
AND aida.invoice_id = aia.invoice_id
AND aca.check_number = <pass check number>

Friday, January 17, 2025

How To Populate the Phone Number Field in Oracle Loans? (Doc ID 2990762.1)

SELECT party.party_name Borrower_Name
      ,org.name operating_unit
  ,loan.loan_number
  ,contact_person.party_name contact_person_name
  ,con_phone.raw_phone_number contact_phone_number
  FROM hz_parties party
      ,lns_loan_headers_all loan
  ,hr_all_organization_units_tl org
  ,hz_parties contact_person
  ,hz_contact_points con_phone
 WHERE party.party_id = loan.primary_borrower_id
   AND org.organization_id = loan.org_id
   AND loan.contact_pers_party_id = contact_person.party_id(+)
   AND con_phone.owner_table_name(+) = 'HZ_PARTIES'
   AND con_phone.owner_table_id(+) = loan.contact_rel_party_id
   AND con_phone.primary_flag(+) = 'Y'
   AND con_phone.status(+) = 'A'
   AND con_phone.contact_point_type(+) = 'PHONE'
   AND loan.loan_id = :p_loan_id

Thursday, January 16, 2025

Query for Outstanding Card Transactions Report (Doc ID 2755945.1) in Fusion Expenses

SELECT TRUNC(SYSDATE - e.start_date) "No. of Days"
      ,papf.person_number employee_number
  ,hzp.person_first_name emp_first_name
  ,hzp.person_last_name emp_last_name
  ,e.assignment_id
  ,ecp.card_program_name AS card_program_name
  ,cc.card_program_id AS card_prog_id
  ,e.org_id AS org_id
  ,e.emp_default_cost_center AS cost_ctr
  ,cc.company_account_id AS company_acct_id
  ,e.merchant_name
  ,ec.card_reference_id
  ,icc.masked_cc_number credit_card_num
  ,cc.description description
  ,e.emp_default_cost_center AS cost_center
  ,eed.segment1
  ,eed.segment2
  ,eed.segment3
  ,eed.segment4
  ,eed.segment5
  ,cc.billed_date
  ,cc.billed_amount
  ,cc.posted_date
  ,cc.posted_amount
  ,cc.transaction_date
  ,cc.transaction_amount
  ,e.orig_reimbursable_amount
  ,DECODE(er.expense_status_code, NULL, 'EMPLOYEE',
       'PEND_MGR_APPROVAL', 'APPROVER',
       'SUBMITTED', 'APPROVER',
       'PEND_IND_APPROVAL', 'EMPLOYEE',
       'MGR_REJECTED', 'EMPLOYEE',
       'IND_REJECTED', 'EMPLOYEE',
       'SAVED', 'EMPLOYEE',
       'WITHDRAWN', 'EMPLOYEE',
       'REQUEST_INFO', 'EMPLOYEE',
       'EMPLOYEE') AS pending_action
  ,ca.payment_currency_code AS currency_code
  FROM fusion.exm_expenses e
      ,fusion.exm_expense_Reports er
      ,fusion.exm_credit_card_trxns cc
      ,fusion.exm_cc_company_accounts ca
      ,fusion.exm_cards ec
      ,fusion.per_all_people_f papf
      ,fusion.IBY_CREDITCARD ICC
      ,fusion.hz_parties hzp
      ,fusion.PER_USERS pu
      ,fusion.EXM_EXPENSE_DISTS eed
      ,fusion.EXM_CARD_PROGRAMS ecp
 WHERE (e.expense_source = 'CREDIT_CARD' or e.expense_source = 'BUSINESS_TRAVEL')
   AND e.expense_report_id = er.expense_report_id(+)
   AND er.expense_status_code(+) NOT IN ('PAID', 'PARTIAL_PAID', 'APPROVAL_COMPLETE', 'INVOICED', 'INVOICE_CANCELED')
   AND (e.expense_report_id IS NULL OR er.expense_report_id IS NOT NULL)
   AND (e.itemization_parent_expense_id IS NULL OR e.itemization_parent_expense_id = -1)
   AND e.credit_card_trxn_id = cc.credit_card_trxn_id
   AND cc.company_account_id = ca.cc_company_account_id(+)
   AND ec.card_id(+) = cc.card_id
   AND NVL(ec.account_type_code,'EMPLOYEE') <> 'COMPANY'
   AND papf.person_id = e.person_id
   AND pu.person_id = e.person_id
   AND ec.card_reference_id = icc.instrid
   AND pu.user_guid = hzp.user_guid(+)
   AND e.expense_id = eed.expense_id
   -- and papf.person_number in ( 1984678,1984999,1978237,1992954,1986569, 1984999 )
   AND ecp.card_program_id = cc.card_program_id
   AND expense_report_num like 'EXP%6386164%'
GROUP BY TRUNC(SYSDATE - cc.billed_date)
        ,TRUNC(SYSDATE - e.start_date)
,papf.person_number
,hzp.person_first_name 
,hzp.person_last_name 
,e.assignment_id
,ecp.card_program_name
,cc.card_program_id
,e.org_id
,e.emp_default_cost_center
,cc.company_account_id
,e.merchant_name
,ec.card_reference_id
,icc.masked_cc_number
,cc.description
,e.emp_default_cost_center
,eed.segment1
,eed.segment2
,eed.segment3
,eed.segment4
,eed.segment5
,cc.billed_date
,cc.billed_amount
,cc.posted_date
,cc.posted_amount
,cc.transaction_date
,cc.transaction_amount
,e.orig_reimbursable_amount
,DECODE(er.expense_status_code, NULL, 'EMPLOYEE',
         'PEND_MGR_APPROVAL', 'APPROVER',
         'SUBMITTED', 'APPROVER',
         'PEND_IND_APPROVAL', 'EMPLOYEE',
         'MGR_REJECTED', 'EMPLOYEE',
         'IND_REJECTED', 'EMPLOYEE',
         'SAVED', 'EMPLOYEE',
         'WITHDRAWN', 'EMPLOYEE',
         'REQUEST_INFO', 'EMPLOYEE',
         'EMPLOYEE')
,ca.payment_currency_code
ORDER BY 1 DESC

Query to get the Approver Details for Expense Report (Doc ID 2279634.1) in Oracle Fusion

SELECT er.expense_report_id
      ,er.expense_report_num
      ,er.expense_report_total
      ,er.reimbursement_currency_code
      ,er.person_id
      ,er.expense_report_date
      ,er.expense_status_code
      ,approval.event
      ,approval.event_performer_id
      ,approval.event_date
      ,approval.approval_level
      ,approval.expense_status_code approval_status
      ,approval.audit_code
      ,approval.audit_return_reason_code
      ,approval.export_reject_code
  FROM exm_expense_reports er
      ,exm_exp_rep_processing approval
 WHERE er.expense_report_id= approval.expense_report_id
   AND er.expense_report_num='EXP00000123456'
ORDER BY approval.approval_level

Wednesday, January 8, 2025

Query to get Parent and Child Accounts in Oracle APPS R12

SELECT ffv1.flex_value parent_account
      ,ffvt1.description parent_account_desc
  ,ffv2.flex_value child_account
  ,ffvt2.description child_account_desc
  FROM fnd_flex_values_tl ffvt2
      ,fnd_flex_values ffv2
      ,fnd_flex_value_hierarchies ffvh
      ,fnd_flex_values_tl ffvt1
      ,fnd_flex_values ffv1
      ,fnd_id_flex_segments fifs
 WHERE fifs.application_id = 101
   AND fifs.id_flex_code = 'GL#'
   AND fifs.id_flex_num = 101
   AND fifs.application_column_name = 'SEGMENT4'
   AND fifs.enabled_flag = 'Y'
   AND ffv1.enabled_flag = 'Y'
   AND ffv1.summary_flag = 'Y'
   AND ffv1.flex_value != 'T'
   AND ffv1.flex_value_set_id = fifs.flex_value_set_id + 0
   AND NVL (TO_CHAR (ffv1.start_date_active, 'YYYY/MM/DD'), '0000/00/00') <= TO_CHAR (SYSDATE, 'YYYY/MM/DD')
   AND NVL (TO_CHAR (ffv1.end_date_active, 'YYYY/MM/DD'), '9999/99/99') >= TO_CHAR (SYSDATE, 'YYYY/MM/DD')
   AND ffvt1.flex_value_id = ffv1.flex_value_id + 0
   AND ffvt1.language = USERENV ('LANG')
   AND ffvh.flex_value_set_id = fifs.flex_value_set_id + 0
   AND ffvh.parent_flex_value = ffv1.flex_value
   AND ffv2.summary_flag = 'N'
   AND NVL (TO_CHAR (ffv2.start_date_active, 'YYYY/MM/DD'), '0000/00/00') <= TO_CHAR (SYSDATE, 'YYYY/MM/DD')
   AND NVL (TO_CHAR (ffv2.end_date_active, 'YYYY/MM/DD'), '9999/99/99') >= TO_CHAR (SYSDATE, 'YYYY/MM/DD')
   AND ffv2.flex_value_set_id + 0 = fifs.flex_value_set_id + 0
   AND ffv2.flex_value BETWEEN ffvh.child_flex_value_low AND ffvh.child_flex_value_high
   AND ffv2.enabled_flag = 'Y'
   AND ffvt2.flex_value_id = ffv2.flex_value_id + 0
   AND ffvt2.language = USERENV ('LANG')
ORDER BY ffv1.flex_value, ffv2.flex_value

Friday, January 3, 2025

Query to get Employee Assignment Status in Oracle Fusion

SELECT papf.person_number
      ,pastt.user_status assignment_status
  FROM per_all_people_f papf
      ,per_all_assignments_m paam
  ,per_assignment_status_types past
  ,per_assignment_status_types_tl pastt
 WHERE papf.person_id = paam.person_id
   AND paam.assignment_status_type_id = past.assignment_status_type_id
   AND past.assignment_status_type_id = pastt.assignment_status_type_id
   AND pastt.source_lang = USERENV('LANG')
   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND paam.primary_assignment_flag = 'Y'
   AND paam.assignment_type = 'E'
   and paam.effective_latest_change = 'Y'
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN past.start_date AND NVL(past.end_date,SYSDATE)
   AND papf.person_number = nvl(:p_person_number,papf.person_number)
ORDER BY papf.person_number asc
        ,pastt.user_status 

Query to get Element Entry details in Oracle Fusion HCM

SELECT peef.* 
  FROM per_all_people_f papf
      ,pay_element_entries_f peef
  ,pay_element_types_f pet
 WHERE peef.element_type_id = pet.element_type_id
   AND papf.person_id = peef.person_id
   AND papf.person_number = 'E-12345'
   AND peef.creator_type = 'BEN'
   AND pet.base_element_name= 'XX_ELEMENT_NAME'  

Mandatory Input value for HCM Elements in Oracle Fusion

SELECT petf.element_type_id
      ,pettl.element_name
      ,petf.processing_type Recurring_NonRecurring
      ,petf.effective_start_date
      ,petf.effective_end_date
      ,petf.multiple_entries_allowed_flag
      ,pivf.base_name
      ,pivf.mandatory_flag
  FROM pay_element_types_f petf
      ,pay_element_types_tl pettl
      ,pay_input_values_f pivf
 WHERE petf.element_type_id = pettl.element_type_id
   AND pivf.element_type_id=petf.element_type_id
   AND pettl.element_name = 'XX_ELEMENT_NAME'
   AND pettl.language=USERENV('LANG')
   AND pivf.user_enterable_flag='Y'

Query to get Grade Rate details in Oracle ERP

SELECT prft.name "ratename"
      ,pg.name "gradename"
      ,prvf.minimum
      ,prvf.maximum
      ,prvf.mid_value
      ,prft.effective_start_date "ab"
      ,prvf.effective_start_date "cd"
      ,pg.effective_start_date "ef"
  FROM per_rates_f_tl    prft
      ,per_rate_values_f prvf
      ,per_grades        pg
 WHERE prft.rate_id        = prvf.rate_id
   AND prvf.rate_object_id = pg.grade_id
   AND prvf.rate_object_type = 'GRADE'
   AND prft.name IN ('XX Rate Name')
   AND PRFT.LANGUAGE = SYS_CONTEXT('USERENV', 'LANG')
   AND TRUNC(SYSDATE) BETWEEN prft.effective_start_date AND prft.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN prvf.effective_start_date AND prvf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN pg.effective_start_date AND pg.effective_end_date
ORDER BY prft.name
       , pg.name

Exclude Freight from Discount(Supplier) in Oracle APPS R12

SELECT aps.vendor_name
      ,aps.exclude_freight_from_discount vend_excd
  ,apss.vendor_site_code
  ,apss.exclude_freight_from_discount site_excd
  FROM ap_suppliers aps
      ,ap_supplier_sites_all apss
 WHERE aps.vendor_id = apss.vendor_id
   AND apss.exclude_freight_from_discount IS NULL
   AND aps.exclude_freight_from_discount IS NULL
   --AND aps.vendor_id NOT IN (1001)
   

SQL query to get list of Jurisdictions for which Tax Rates has been defined in Oracle APPS R12

SELECT zj.tax
      ,zj.tax_jurisdiction_code
  ,hzg.geography_element2_code state_code
  ,hzg.geography_element3_code county_code
  ,hzg.geography_element4_code city_code
  FROM zx_jurisdictions_b zj
      ,hz_geographies hzg
 WHERE zj.tax_regime_codE='XX_SALE_TAX'
   AND SYSDATE BETWEEN zj.effective_from AND NVL(zj.effective_to,'31-DEC-4999')
   AND SYSDATE BETWEEN hzg.start_date AND hzg.end_date
   AND zj.zone_geography_id=hzg.geography_id
   AND zj.tax=hzg.geography_type
   AND NOT EXISTS (SELECT 1 
                     FROM zx_rates_b zr
                    WHERE zr.tax_regime_code='XX_SALE_TAX'
                      AND zr.tax_jurisdiction_code=zj.tax_jurisdiction_code
   )
ORDER BY zj.tax
        ,zj.tax_jurisdiction_code
    ,hzg.geography_element2_code
,hzg.geography_element3_code
,hzg.geography_element4_code

Query to get Customer Address that doesn't have Geography Reference in Oracle APPS R12

SELECT hca.account_number
      ,hca.account_name
      ,hcs_ship.site_use_code
      ,hl_ship.address1
      ,hl_ship.state
      ,hl_ship.county
      ,hl_ship.city
      ,hl_ship.postal_code
  FROM hz_cust_site_uses_all hcs_ship
      ,hz_cust_acct_sites_all hca_ship
      ,hz_cust_accounts hca
      ,hz_party_sites hps_ship
      ,hz_locations hl_ship
 WHERE hca.cust_account_id=hca_ship.cust_account_id(+)
   AND hcs_ship.cust_acct_site_id(+) = hca_ship.cust_acct_site_id
   AND hca_ship.party_site_id = hps_ship.party_site_id
   AND hps_ship.location_id = hl_ship.location_id
   AND hca.status='A'
   AND hcs_ship.status='A'
   AND hca_ship.status='A'
   AND hl_ship.country='US'
   --AND hca.account_number='1234567890'
   AND NOT EXISTS (SELECT 1 
                     FROM hz_geographies hg
                    WHERE hg.geography_element2_code=hl_ship.state
                      AND UPPER(hl_ship.county)=UPPER(hg.geography_element3_code)
                      AND UPPER(hl_ship.city)=UPPER(hg.geography_element4_code)
                      AND SYSDATE BETWEEN hg.start_date AND hg.end_date
  )

Thursday, January 2, 2025

Query to get Errored or Warning Concurrent Program Request details in R12

SELECT fu.user_name
      ,fcpt.user_concurrent_program_name
  ,fcpt.description
  ,fcp.concurrent_program_name
  ,fcr.request_id
  ,((fcr.actual_completion_date
         -DECODE (TRUNC(fcr.request_date)
         ,fcr.requested_start_date
,fcr.request_date
,fcr.requested_start_date
))*60*24) wait_time_min
      ,DECODE(fcr.phase_code,'C','Completed'
                        ,'R','Running'
,fcr.phase_code
) phase_code
  ,DECODE(fcr.status_code,'C','Completed'
                         ,'R','Running'
,'W','Paused'
,'E','Error'
,'G','Warning'
,fcr.status_code
) status_code
  ,TO_CHAR(fcr.request_date,'DD/MM/YYYY HH24:MI:SS') request_date
  ,TO_CHAR(fcr.requested_start_date,'DD/MM/YYYY HH24:MI:SS') start_time
  ,TO_CHAR(fcr.actual_completion_date,'DD/MM/YYYY HH24:MI:SS') complete_time
  FROM fnd_concurrent_requests fcr
      ,fnd_concurrent_programs fcp
  ,fnd_concurrent_programs_tl fcpt
  ,fnd_user fu
 WHERE 1=1
   AND fcp.concurrent_program_id=fcr.concurrent_program_id
   AND fcpt.concurrent_program_id=fcp.concurrent_program_id
   AND fcr.requested_by = fu.user_id
   AND TRUNC(fcr.request_date) BETWEEN SYSDATE - 5 AND SYSDATE
   AND fcr.status_code IN ('E','G')
ORDER BY fcr.status_code
        ,fcp.concurrent_program_name
,fcr.request_id

fnd_concurrent_requests.status_code in Oracle APPS R12(CP_STATUS_CODE)

SELECT lookup_code
      ,meaning 
  FROM fnd_lookups 
 WHERE lookup_type = 'CP_STATUS_CODE'
ORDER BY lookup_code

SQL Query to find Request Set details in R12

SELECT frt.responsibility_name
      ,frg.request_group_name
  ,frgu.request_unit_type
  ,frgu.request_unit_id
  ,fcpt.user_request_set_name
 FROM apps.fnd_Responsibility fr
     ,apps.fnd_responsibility_tl frt
,apps.fnd_request_groups frg
,apps.fnd_request_group_units frgu
,apps.fnd_request_Sets_tl fcpt
 WHERE frt.responsibility_id = fr.responsibility_id
   AND frg.request_group_id = fr.request_group_id
   AND frgu.request_group_id = frg.request_group_id
   AND fcpt.request_set_id = frgu.request_unit_id
   AND frt.language = USERENV('LANG')
   AND fcpt.language = USERENV('LANG')
   AND fcpt.user_request_set_name = :p_request_set_name
order by frt.responsibility_name
      ,frg.request_group_name
  ,frgu.request_unit_type

SQL Query to check Concurrent Program details in R12

SELECT frt.responsibility_name
      ,frg.request_group_name
  ,frgu.request_unit_type
  ,frgu.request_unit_id
  ,fcpt.user_concurrent_program_name
  FROM fnd_Responsibility fr
      ,fnd_responsibility_tl frt
  ,fnd_request_groups frg
  ,fnd_request_group_units frgu
  ,fnd_concurrent_programs_tl fcpt
 WHERE frt.responsibility_id = fr.responsibility_id
   AND frg.request_group_id = fr.request_group_id
   AND frgu.request_group_id = frg.request_group_id
   AND fcpt.concurrent_program_id = frgu.request_unit_id
   AND frt.language = USERENV('LANG')
   AND fcpt.language = USERENV('LANG')
   AND fcpt.user_concurrent_program_name = :p_conc_program_name
ORDER BY frt.responsibility_name
      ,frg.request_group_name
  ,frgu.request_unit_type

SQL Query to check Concurrent Requests SID & OS Process Details for given Request ID in R12

SELECT fcr.request_id
      ,fcr.argument_text
      ,fcr.phase_code
  ,fcr.status_code
  ,fcr.oracle_process_id
  ,fcr.os_process_id
  ,f.user_name
  FROM fnd_concurrent_requests fcr
      ,fnd_user f 
WHERE fcr.request_id = :p_request_id
  AND fcr.requested_by=f.user_id

SQL Query to Check Currently Running Concurrent Requests in Oracle APPS R12

SELECT fcr.request_id
      ,fcr.oracle_process_id
  ,fcr.os_process_id
  ,fcp.user_concurrent_program_name
  ,fu.user_name
  ,fcr.status_code
  ,fcr.phase_code
  ,vsp.username
  ,vsp.sid
  ,vsp.serial#
  ,vsp.program
  ,vsw.event
  ,TO_CHAR(fcr.actual_start_date,'MON-DD-HH-MI-SS') actual_start_date
  ,TO_CHAR(fcr.actual_completion_date,'MON-DD-HH-MI-SS') actual_completion_date
  FROM fnd_concurrent_requests fcr
      ,(SELECT c.username
          ,c.sid
  ,c.serial#
  ,c.program
  ,d.spid 
  FROM v$session c
      ,v$process d
         WHERE c.paddr=d.addr
) vsp
  ,fnd_concurrent_programs_tl fcp
  ,fnd_user fu
  ,v$session_wait vsw
WHERE fcr.oracle_process_id=vsp.spid
  AND fcr.concurrent_program_id=fcp.concurrent_program_id
  AND fcp.language=USERENV('LANG')
  AND fcr.requested_by=fu.user_id
  AND vsp.sid=vsw.sid
  AND fcr.status_code='R'
  AND fcr.phase_code='R'

Wednesday, January 1, 2025

Query to get History of Concurrent Requests which are errored out in Oracle APPS R12

SELECT a.request_id request_id
      ,a.phase_code
  ,a.status_code
      ,actual_start_date
      ,actual_completion_date
      ,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name concurrent_program_name
  FROM fnd_Concurrent_requests a
      ,fnd_concurrent_processes b
      ,fnd_concurrent_queues q
      ,fnd_concurrent_programs c
      ,fnd_concurrent_programs_tl ctl
 WHERE a.controlling_manager = b.concurrent_process_id
   AND a.concurrent_program_id = c.concurrent_program_id
   AND a.program_application_id = c.application_id
   AND a.status_code = 'E'
   AND a.phase_code = 'C'
   AND actual_start_date > SYSDATE - 10
   AND b.queue_application_id = q.application_id
   AND b.concurrent_queue_id = q.concurrent_queue_id
   AND ctl.concurrent_program_id = c.concurrent_program_id
   AND ctl.LANGUAGE = USERENV('LANG')
ORDER BY actual_completion_date

Query to get the Running History of Concurrent Programs in Oracle APPS R12

SELECT fcr.oracle_session_id o_sid
      ,ROUND (( ( NVL (fcr.actual_completion_date, SYSDATE)- fcr.actual_start_date)* 60* 24),2) runtime_min
      ,fcr.request_id request_id
      ,fcr.requested_by requested_by
      ,fu.user_name user_name
      ,fr.responsibility_name
      ,fcr.concurrent_program_id concurrent_program_id
      ,fcp.user_concurrent_program_name concurrent_program_name
      ,TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') start_time
      ,DECODE (fcr.status_code ,'C', 'C:Completed','G', 'G:Warning','E', 'E:Error','Q','Q:Queued',fcr.status_code) status
      ,TO_CHAR (fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') end_time
      ,ROUND (( ( NVL (fcr.actual_completion_date, SYSDATE)- fcr.actual_start_date)* 60* 60* 24),2) run_time_in_sec
      ,fcr.oracle_process_id "oracle_pid/SPID"
      ,fcr.os_process_id os_pid
      ,fcr.argument_text
      ,fcr.outfile_name
      ,fcr.logfile_name
      ,fcr.enable_trace
  FROM fnd_concurrent_requests fcr
      ,fnd_user fu
      ,fnd_responsibility_tl fr
      ,fnd_concurrent_programs_tl fcp
 WHERE fcp.user_concurrent_program_name LIKE 'XX Concurrent Program Name%'
    AND fr.responsibility_id = fcr.responsibility_id
   AND fcr.concurrent_program_id = fcp.concurrent_program_id
   AND fcr.program_application_id = fcp.application_id
   AND fu.user_id = fcr.requested_by
   --AND fcr.request_id = 1234567890
ORDER BY fcr.concurrent_program_id
        ,request_id DESC

Query to get Long Running Concurrent Programs in R12

SELECT fcr.oracle_session_id
      ,fcr.request_id rqst_id
      ,fcr.requested_by rqst_by
      ,fu.user_name
      ,fr.responsibility_name
      ,fcr.concurrent_program_id cp_id
      ,fcp.user_concurrent_program_name cp_name
      ,TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') act_start_datetime
      ,DECODE (fcr.status_code, 'R', 'R:Running', fcr.status_code) status
      ,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) runtime_in_min
      ,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 60 * 24), 2)runtime_in_sec
      ,fcr.oracle_process_id "oracle_pid/SPID"
      ,fcr.os_process_id os_pid
      ,fcr.argument_text
      ,fcr.outfile_name
      ,fcr.logfile_name
      ,fcr.enable_trace
  FROM fnd_concurrent_requests fcr
      ,fnd_user fu
      ,fnd_responsibility_tl fr
      ,fnd_concurrent_programs_tl fcp
 WHERE fcr.status_code = 'R'
   AND fu.user_id = fcr.requested_by
   AND fr.responsibility_id = fcr.responsibility_id
   AND fcr.concurrent_program_id = fcp.concurrent_program_id
   AND fcr.program_application_id = fcp.application_id
   AND ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) > 60
   ORDER BY fcr.concurrent_program_id
           ,request_id DESC

Query to get Trace file Including SID in Oracle APPS R12

SELECT 'Request id: '||request_id 
      ,'Trace id: '||oracle_Process_id
  ,'Trace Flag: '||req.enable_trace
  ,'Trace Name: '||dest.value||'/'||LOWER(dbnm.value)
                 ||'_ora_'||oracle_process_id||'.trc', 'Prog. Name: '
                     ||prog.user_concurrent_program_name
  ,'File Name: '||execname.execution_file_name|| execname.subroutine_name 
  ,'Status : '||DECODE(phase_code,'R','Running') ||'-'|| DECODE(status_code,'R','Normal')
  ,'SID Serial: '||ses.sid||','|| ses.serial#, 'Module : '||ses.module 
  FROM fnd_concurrent_requests req
      ,v$session ses
  ,v$process proc
  ,v$parameter dest
  ,v$parameter dbnm
  ,fnd_concurrent_programs_vl prog
  ,fnd_executables execname 
 WHERE req.request_id = :p_request_id
   AND req.oracle_process_id=proc.spid(+)
   AND proc.addr = ses.paddr(+) 
   AND dest.name='user_dump_dest' 
   AND dbnm.name='db_name'
   AND req.concurrent_program_id = prog.concurrent_program_id 
   AND req.program_application_id = prog.application_id
   AND prog.application_id = execname.application_id 
   AND prog.executable_id=execname.executable_id

SQL to find out the Raw Trace Name and Location for the Concurrent Program

SELECT req.request_id
      ,req.logfile_node_name node
      ,req.oracle_Process_id
      ,req.enable_trace
      ,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
      ,prog.user_concurrent_program_name
      ,execname.execution_file_name
      ,execname.subroutine_name
      ,phase_code
      ,status_code
      ,ses.SID
      ,ses.serial#
      ,ses.module
      ,ses.machine
  FROM fnd_concurrent_requests req
      ,v$session ses
      ,v$process proc
      ,v$parameter dest
      ,v$parameter dbnm
      ,fnd_concurrent_programs_vl prog
      ,fnd_executables execname
 WHERE 1=1
   AND req.request_id = :p_request_id
   AND req.oracle_process_id = proc.spid(+)
   AND proc.addr = ses.paddr(+)
   AND dest.name='user_dump_dest'
   AND dbnm.name='db_name'
   AND req.concurrent_program_id = prog.concurrent_program_id
   AND req.program_application_id = prog.application_id
   AND prog.application_id = execname.application_id
   AND prog.executable_id=execname.executable_id

Query to get Concurrent Manager Status in R12

SELECT q.concurrent_queue_id
      ,q.concurrent_queue_name
  ,q.user_concurrent_queue_name
  ,q.target_node
  ,q.max_processes
  ,q.running_processes
  ,running.run running
  ,pending.pend
  ,DECODE(q.control_code, 'D', 'Deactivating'
, 'E', 'Deactivated'
, 'N', 'Node unavai'
, 'A', 'Activating'
, 'X', 'Terminated'
, 'T', 'Terminating'
, 'V', 'Verifying'
, 'O', 'Suspending'
, 'P', 'Suspended'
, 'Q', 'Resuming'
, 'R', 'Restarting') status
  FROM (SELECT concurrent_queue_name
              ,COUNT(phase_code) run
          FROM fnd_concurrent_worker_requests
         WHERE phase_code = 'R'
           AND hold_flag != 'Y'
           AND requested_start_date <= SYSDATE GROUP BY concurrent_queue_name
) running
   ,(SELECT concurrent_queue_name
           ,COUNT(phase_code) pend 
   FROM fnd_concurrent_worker_requests 
  WHERE phase_code = 'P' 
    AND hold_flag != 'Y' 
AND requested_start_date <= SYSDATE GROUP BY concurrent_queue_name
    ) pending
   ,apps.fnd_concurrent_queues_vl q
 WHERE q.concurrent_queue_name = running.concurrent_queue_name(+) 
   AND q.concurrent_queue_name = pending.concurrent_queue_name(+) 
   AND q.enabled_flag = 'Y' 
ORDER BY DECODE(q.application_id, 0, DECODE(q.concurrent_queue_id, 1, 1,4, 2))
     ,SIGN(q.max_processes) DESC
,q.concurrent_queue_name
,q.application_id

SQL Query to get the Responsibility/Request Group attached for a given Concurrent Program

SELECT DISTINCT frt.responsibility_name ,frg.request_group_name ,frgu.request_unit_type ,frgu.request_unit_id ,fcpt.user_concurrent_program_name FROM fnd_responsibility fr ,fnd_responsibility_tl frt ,fnd_request_groups frg ,fnd_request_group_units frgu ,fnd_concurrent_programs_tl fcpt WHERE frt.responsibility_id = fr.responsibility_id AND frg.request_group_id = fr.request_group_id AND frgu.request_group_id = frg.request_group_id AND fcpt.concurrent_program_id = frgu.request_unit_id AND UPPER(fcpt.user_concurrent_program_name) = UPPER(:p_concurrent_program) ORDER BY frt.responsibility_name

Monday, December 30, 2024

Query to get failed records in Invoices Interface in Oracle APPS R12

SELECT aif.invoice_id
              ,ailf.invoice_line_id
  ,aif.invoice_num
  ,air.reject_lookup_code
  ,aif.invoice_amount
  ,ailf.amount line_amount
  ,ailf.line_number
  ,aif.source
  ,ailf.accounting_date
  ,aif.gl_date
  ,aif.invoice_date
  ,aif.creation_date
  ,(SELECT vendor_name
                  FROM ap_suppliers
                 WHERE vendor_id = aif.vendor_id) vendor_name
  ,(SELECT end_date_active
                  FROM ap_suppliers
                 WHERE vendor_id = aif.vendor_id) vendor_end_date_active
  ,(SELECT last_update_date
                  FROM ap_suppliers
                 WHERE vendor_id = aif.vendor_id) vendor_last_update_date
  ,(SELECT segment1
                  FROM po_headers_all
                 WHERE po_header_id = ailf.po_header_id) po_number
  ,(SELECT name
                  FROM hr_operating_units
                 WHERE organization_id = aif.org_id) operating_unit
          FROM ap_invoices_interface aif
      ,ap_interface_rejections air
  ,ap_invoice_lines_interface ailf
         WHERE aif.invoice_id = ailf.invoice_id
           AND ailf.invoice_line_id = air.parent_id
           -- AND    aif.group_id        =v_group_id
           AND air.parent_table = 'AP_INVOICE_LINES_INTERFACE'
   --AND aif.source = 'ERS'
           AND aif.status = 'REJECTED'
        UNION
        SELECT aif.invoice_id
      ,ailf.invoice_line_id
  ,aif.invoice_num
  ,air.reject_lookup_code
  ,aif.invoice_amount
  ,ailf.amount line_amount
  ,ailf.line_number
  ,aif.source
  ,ailf.accounting_date
  ,aif.gl_date
  ,aif.invoice_date
  ,aif.creation_date
  ,(SELECT vendor_name
                  FROM ap_suppliers
                 WHERE vendor_id = aif.vendor_id) vendor_name
  ,(SELECT end_date_active
                  FROM ap_suppliers
                 WHERE vendor_id = aif.vendor_id) vendor_end_date_active
  ,(SELECT last_update_date
                  FROM ap_suppliers
                 WHERE vendor_id = aif.vendor_id) vendor_last_update_date
  ,(SELECT segment1
                  FROM po_headers_all
                 WHERE po_header_id = ailf.po_header_id) po_number
  ,(SELECT name
                  FROM hr_operating_units
                 WHERE organization_id = aif.org_id) operating_unit
          FROM ap_invoices_interface aif
      ,ap_interface_rejections air
  ,ap_invoice_lines_interface ailf
         WHERE aif.invoice_id = ailf.invoice_id
           AND aif.invoice_id = air.parent_id
           -- AND    aif.group_id        =v_group_id
           AND air.parent_table = 'AP_INVOICES_INTERFACE'
           --AND aif.source = 'ERS'
   AND aif.status = 'REJECTED'

Query to get GRN Details in Oracle APPS R12

SELECT    
       DISTINCT rsl.to_subinventory
           ,rts.invoice_status_code
   ,phs.org_id
   ,phs.segment1 po_number
   ,rts.transaction_date
           FROM po_vendor_sites_all pvss
               ,po_vendor_sites_all pvss2
               ,po_vendors pvds
               ,po_headers_all phs
               ,po_lines_all pls
               ,po_line_locations_all plls
               ,rcv_shipment_headers rsh
               ,rcv_shipment_lines rsl
               ,inl_ship_lines_all isl
               ,rcv_transactions rts
               ,mtl_system_items_b msi
          WHERE rts.shipment_header_id = rsh.shipment_header_id
            AND rts.po_header_id = phs.po_header_id
            AND rts.po_line_location_id = plls.line_location_id
            AND rts.po_line_id = pls.po_line_id
            AND rts.shipment_header_id = rsl.shipment_header_id
            AND rts.shipment_line_id = rsl.shipment_line_id
            AND rts.lcm_shipment_line_id = isl.ship_line_id(+)
            AND rts.lcm_adjustment_num = isl.adjustment_num(+)
            AND phs.vendor_id = pvds.vendor_id
            AND phs.vendor_site_id = pvss.vendor_site_id
            AND rsh.receipt_source_code = 'VENDOR'
            AND rts.source_document_code = 'PO' 
            AND phs.segment1 = '1234567890'           --- Purchase Order Number/LPO Number
            --AND NVL (rts.invoice_status_code, 'NA') IN ('PENDING', 'REJECTED')
            AND (   (rts.transaction_type = 'RECEIVE')
                 OR (    'N' = 'Y'
                     AND rts.transaction_type = 'CORRECT'
                     AND rts.timecard_id IS NOT NULL
                     AND EXISTS (
                            SELECT 1
                              FROM apps.rcv_transactions prt
                             WHERE prt.transaction_type = 'RECEIVE'
                               AND prt.source_document_code = 'PO'
                               AND prt.transaction_id =
                                                     rts.parent_transaction_id
                               AND prt.invoice_status_code = 'INVOICED'
                               AND prt.last_update_date <= rts.creation_date)
                    )
                )
            AND pvss.pay_on_code IN ('RECEIPT', 'RECEIPT_AND_USE')
            AND phs.pay_on_code IN ('RECEIPT', 'RECEIPT_AND_USE')
            AND NVL (plls.consigned_flag, 'N') <> 'Y'
            AND pvss2.vendor_site_id = NVL (pvss.default_pay_site_id, pvss.vendor_site_id)
            AND NVL (rsh.asn_type, ' ') <> 'ASBN'
            AND rts.po_release_id IS NULL
            AND rsl.item_id = msi.inventory_item_id
            AND rts.organization_id = msi.organization_id
            AND rts.transaction_date >= '01-JAN-2024'

Query to get POR is Enabled at Supplier Site and PO Level in Oracle APPS R12

SELECT DISTINCT apss.pay_on_code por_supp_site
               ,pha.pay_on_code por_po
           FROM ap_suppliers aps
       ,ap_supplier_sites_all apss
   ,po_headers_all pha
          WHERE aps.vendor_id = apss.vendor_id
            AND pha.vendor_id = apss.vendor_id
            AND pha.vendor_site_id = apss.vendor_site_id
            AND pha.segment1 = '1234567890'  --- Purchase Order Number/LPO Number

Query to get Inventory Item details in R12

SELECT MTL_SYSTEM_ITEMS_B.LAST_UPDATE_DATE
      ,MTL_SYSTEM_ITEMS_B.LAST_UPDATED_BY
  ,MTL_SYSTEM_ITEMS_B.CREATION_DATE
  ,MTL_SYSTEM_ITEMS_B.CREATED_BY
  ,MTL_SYSTEM_ITEMS_B.DESCRIPTION
  ,MTL_SYSTEM_ITEMS_B.SEGMENT1
  ,MTL_SYSTEM_ITEMS_B.SHELF_LIFE_CODE
  ,MTL_SYSTEM_ITEMS_B.UNIT_WEIGHT
  ,MTL_SYSTEM_ITEMS_B.WEIGHT_UOM_CODE
  ,MTL_SYSTEM_ITEMS_B.VOLUME_UOM_CODE
  ,MTL_SYSTEM_ITEMS_B.UNIT_VOLUME
  ,MTL_SYSTEM_ITEMS_B.PRIMARY_UOM_CODE
  ,MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID
  ,MTL_SYSTEM_ITEMS_B.ITEM_TYPE
  ,MTL_PARAMETERS.MASTER_ORGANIZATION_ID
  ,MTL_SYSTEM_ITEMS_B.HAZARD_CLASS_ID
  ,MTL_SYSTEM_ITEMS_B.SERIAL_NUMBER_CONTROL_CODE
  ,MTL_SYSTEM_ITEMS_B.PLANNING_MAKE_BUY_CODE
  ,MTL_SYSTEM_ITEMS_B.BASE_ITEM_ID
  ,MTL_SYSTEM_ITEMS_B.START_DATE_ACTIVE
  ,MTL_SYSTEM_ITEMS_B.RETURNABLE_FLAG
  ,MTL_SYSTEM_ITEMS_B.PURCHASING_ENABLED_FLAG
  ,MTL_SYSTEM_ITEMS_B.SERVICEABLE_PRODUCT_FLAG
  ,MTL_SYSTEM_ITEMS_B.END_DATE_ACTIVE
  ,MTL_SYSTEM_ITEMS_B.FIXED_ORDER_QUANTITY
  ,MTL_SYSTEM_ITEMS_B.ORDER_COST
  ,MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_FLAG
  ,MTL_SYSTEM_ITEMS_B.CUSTOMER_ORDER_ENABLED_FLAG 
  FROM MTL_SYSTEM_ITEMS_B
      ,MTL_PARAMETERS 
 WHERE MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID = MTL_PARAMETERS.ORGANIZATION_ID 
   AND MTL_PARAMETERS.MASTER_ORGANIZATION_ID = MTL_PARAMETERS.ORGANIZATION_ID                             

Query to get GL Journal Details in R12

SELECT JEL.JE_HEADER_ID
      ,JEL.JE_LINE_NUM
  ,JEL.LAST_UPDATE_DATE
      ,JEL.LAST_UPDATED_BY
  ,JEL.LEDGER_ID
  ,JEL.CODE_COMBINATION_ID
      ,JEL.PERIOD_NAME
  ,JEL.STATUS
  ,JEL.CREATION_DATE
  ,JEL.CREATED_BY
  ,JEL.ENTERED_DR
  ,JEL.ENTERED_CR
  ,JEL.ACCOUNTED_DR
  ,JEL.ACCOUNTED_CR
  ,JEL.REFERENCE_1
  ,JEL.REFERENCE_2
  ,JEL.REFERENCE_3
  ,JEL.REFERENCE_4
  ,JEL.REFERENCE_5
  ,JEL.REFERENCE_6
  ,JEL.REFERENCE_7
  ,JEL.REFERENCE_8
  ,JEL.REFERENCE_9
  ,JEL.REFERENCE_10
  ,JEL.GL_SL_LINK_ID
  ,JEH.JE_CATEGORY
  ,JEH.JE_SOURCE
  ,JEH.NAME
  ,JEH.CURRENCY_CODE
  ,JEH.POSTED_DATE
  ,JEB.NAME
  ,PRDS.START_DATE
  ,PRDS.END_DATE
  ,GL.LEDGER_CATEGORY_CODE
  ,PRDS.ADJUSTMENT_PERIOD_FLAG 
  FROM GL_JE_LINES JEL
      ,GL_JE_HEADERS JEH
      ,GL_JE_BATCHES JEB
      ,GL_PERIOD_STATUSES PRDS
  ,GL_LEDGERS GL 
 WHERE JEL.JE_HEADER_ID = JEH.JE_HEADER_ID 
   AND JEH.ACTUAL_FLAG = 'A' 
   AND JEH.JE_BATCH_ID = JEB.JE_BATCH_ID (+) 
   AND JEL.PERIOD_NAME = PRDS.PERIOD_NAME 
   AND JEL.LEDGER_ID = PRDS.SET_OF_BOOKS_ID 
   AND JEL.LEDGER_ID = GL.LEDGER_ID 
   AND PRDS.APPLICATION_ID = 101 
   AND JEH.CURRENCY_CODE <> 'STAT' 
   AND JEB.STATUS = 'P' 

currentAppUi built in variable in VBCS

$global.currentAppUi.id :- The id of the App UI $global.currentAppUi.urlId :-The id of the App UI as shown in the URL $global.currentAppUi.d...