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' 

Query to get Sales Invoice(Receivables) Details in R12

SELECT RA_CUST_TRX_TYPES_ALL.TYPE,

       RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID,

       RA_CUSTOMER_TRX_LINES_ALL.LAST_UPDATE_DATE,

       RA_CUSTOMER_TRX_LINES_ALL.LAST_UPDATED_BY,

       RA_CUSTOMER_TRX_LINES_ALL.CREATION_DATE,

       RA_CUSTOMER_TRX_LINES_ALL.CREATED_BY,

       RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID,

       RA_CUSTOMER_TRX_LINES_ALL.LINE_NUMBER,

       RA_CUSTOMER_TRX_LINES_ALL.SET_OF_BOOKS_ID,

       RA_CUSTOMER_TRX_LINES_ALL.INVENTORY_ITEM_ID,

       RA_CUSTOMER_TRX_LINES_ALL.QUANTITY_INVOICED,

       RA_CUSTOMER_TRX_LINES_ALL.QUANTITY_CREDITED,

       RA_CUSTOMER_TRX_LINES_ALL.UNIT_STANDARD_PRICE,

       RA_CUSTOMER_TRX_LINES_ALL.UNIT_SELLING_PRICE,

       RA_CUSTOMER_TRX_LINES_ALL.SALES_ORDER,

       RA_CUSTOMER_TRX_LINES_ALL.SALES_ORDER_LINE,

       RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE,

       RA_CUSTOMER_TRX_LINES_ALL.EXTENDED_AMOUNT,

       RA_CUSTOMER_TRX_LINES_ALL.TAX_RATE,

       RA_CUSTOMER_TRX_LINES_ALL.UOM_CODE,

       RA_CUSTOMER_TRX_LINES_ALL.ORG_ID,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_CONTEXT,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE7,

       RA_CUSTOMER_TRX_LINES_ALL.LINK_TO_CUST_TRX_LINE_ID,

       RA_CUSTOMER_TRX_LINES_ALL.WAREHOUSE_ID,

       RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID,

       RA_CUSTOMER_TRX_ALL.TRX_NUMBER,

       RA_CUSTOMER_TRX_ALL.CUST_TRX_TYPE_ID,

       RA_CUSTOMER_TRX_ALL.TRX_DATE,

       RA_CUSTOMER_TRX_ALL.SOLD_TO_CUSTOMER_ID,

       OE_ORDER_HEADERS_ALL.SOLD_TO_CONTACT_ID,

       RA_CUSTOMER_TRX_ALL.SOLD_TO_SITE_USE_ID,

       RA_CUSTOMER_TRX_ALL.BILL_TO_CUSTOMER_ID,

       RA_CUSTOMER_TRX_ALL.BILL_TO_SITE_USE_ID,

       RA_CUSTOMER_TRX_ALL.SHIP_TO_CUSTOMER_ID,

       RA_CUSTOMER_TRX_ALL.SHIP_TO_SITE_USE_ID,

       RA_CUSTOMER_TRX_ALL.TERM_ID,

       RA_CUSTOMER_TRX_ALL.TERM_DUE_DATE,

       RA_CUSTOMER_TRX_ALL.PRIMARY_SALESREP_ID,

       RA_CUSTOMER_TRX_ALL.PRINTING_ORIGINAL_DATE,

       RA_CUSTOMER_TRX_ALL.PURCHASE_ORDER,

       RA_CUSTOMER_TRX_ALL.EXCHANGE_DATE,

       RA_CUSTOMER_TRX_ALL.EXCHANGE_RATE,

       RA_CUSTOMER_TRX_ALL.TERRITORY_ID,

       RA_CUSTOMER_TRX_ALL.INVOICE_CURRENCY_CODE,

       RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG,

       RA_CUSTOMER_TRX_ALL.PAYING_SITE_USE_ID,

       RA_CUSTOMER_TRX_LINES_ALL1.CUSTOMER_TRX_LINE_ID,

       RA_CUSTOMER_TRX_LINES_ALL1.INVENTORY_ITEM_ID,

       RA_CUSTOMER_TRX_LINES_ALL1.SALES_ORDER,

       RA_CUSTOMER_TRX_LINES_ALL1.SALES_ORDER_LINE,

       RA_CUSTOMER_TRX_LINES_ALL1.INTERFACE_LINE_CONTEXT,

       RA_CUSTOMER_TRX_LINES_ALL1.INTERFACE_LINE_ATTRIBUTE6,

       RA_CUSTOMER_TRX_LINES_ALL1.INTERFACE_LINE_ATTRIBUTE7,

       RA_CUSTOMER_TRX_LINES_ALL1.WAREHOUSE_ID,

       RA_CUSTOMER_TRX_ALL.STATUS_TRX,

       RA_CUSTOMER_TRX_ALL.EXCHANGE_RATE_TYPE,

       RA_CUSTOMER_TRX_ALL.LAST_UPDATE_DATE,

       RA_CUST_TRX_TYPES_ALL.LAST_UPDATE_DATE,

       OE_ORDER_HEADERS_ALL.LAST_UPDATE_DATE,

       OE_ORDER_HEADERS_ALL.SALES_CHANNEL_CODE,

       OE_ORDER_HEADERS_ALL.ORDER_TYPE_ID,

       OE_ORDER_HEADERS_ALL.PAYMENT_TYPE_CODE,

       OE_ORDER_HEADERS_ALL.FREIGHT_TERMS_CODE,

       OE_ORDER_LINES_ALL.LINE_CATEGORY_CODE,

       OE_ORDER_LINES_ALL.ITEM_TYPE_CODE,

       OE_ORDER_LINES_ALL.SHIPMENT_NUMBER,

       OE_ORDER_LINES_ALL.SHIP_FROM_ORG_ID,

       OE_ORDER_LINES_ALL.PROJECT_ID,

       OE_ORDER_LINES_ALL.TASK_ID,

       OE_ORDER_LINES_ALL.OPTION_NUMBER,

       OE_ORDER_LINES_ALL.COMPONENT_NUMBER,

       OE_ORDER_LINES_ALL.SOURCE_TYPE_CODE,

       OE_ORDER_HEADERS_ALL.ORDER_SOURCE_ID,

       RA_CUSTOMER_TRX_ALL.LEGAL_ENTITY_ID,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE11

  FROM RA_CUSTOMER_TRX_LINES_ALL  RA_CUSTOMER_TRX_LINES_ALL1,

       RA_CUSTOMER_TRX_LINES_ALL,

       RA_CUSTOMER_TRX_ALL,

       RA_CUST_TRX_TYPES_ALL,

       OE_ORDER_HEADERS_ALL,

       OE_ORDER_LINES_ALL

 WHERE     RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID =

           RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID

       AND RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG = 'Y'

       AND RA_CUSTOMER_TRX_ALL.CUST_TRX_TYPE_ID =

           RA_CUST_TRX_TYPES_ALL.CUST_TRX_TYPE_ID

       AND RA_CUSTOMER_TRX_ALL.ORG_ID = RA_CUST_TRX_TYPES_ALL.ORG_ID

       AND RA_CUSTOMER_TRX_LINES_ALL.LINK_TO_CUST_TRX_LINE_ID =

           RA_CUSTOMER_TRX_LINES_ALL1.CUSTOMER_TRX_LINE_ID(+)

       AND RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_CONTEXT IN

               ('ORDER ENTRY', 'INTERCOMPANY')

       AND TO_NUMBER (RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6) =

           OE_ORDER_LINES_ALL.LINE_ID(+)

       AND OE_ORDER_LINES_ALL.HEADER_ID = OE_ORDER_HEADERS_ALL.HEADER_ID(+)

       AND RA_CUSTOMER_TRX_ALL.LAST_UPDATE_DATE >=

           TO_DATE (:P_LAST_EXTRACT_DATE, 'MM/D D/YYYY HH24:MI:SS')