Query to get the list of contexts for each formula type in Oracle Fusion HCM

select t.base_formula_type_name

,      ttl.formula_type_name

,      ttl.description

,      c.base_context_name

from   ff_formula_types_b t

,      ff_formula_types_tl ttl

,      ff_ftype_context_usages u

,      ff_contexts_b c

where  t.formula_type_id = u.formula_type_id

and    ttl.formula_type_id = t.formula_type_id

and    ttl.language = userenv('LANG')

and    ttl.formula_type_name like 'Performance Rating Calculation'--'Oracle%Payroll%'

and    c.context_id = u.context_id

Worker Self Evaluation - Calculated Rating Issue - Invalid Rating Level in Oracle Fusion HCM Performance Rating Calculation

One of the possible reason to fail is if the Length of the Goal Name is more than 255 Characters. Try to  reduce the Goal Name length to below 250 characters and use description for detailed information about the Goal.

Note: Please check with Oracle before you do any changes.

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.

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 

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 

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

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>

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

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

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

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

  )

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'

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

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'