Oracle Apps 4u
  • Home
  • About Us
  • Services
    • Consulting
    • Training
    • On Job Support
    • Sample Projects
  • Courses
    • Oracle Apps Technical
    • Oracle DBA
    • Oracle Apps SCM – PO & OM
  • Downloads
  • Interview
    • Interview Questions
    • Assessment Quiz
    • Resume Services
  • Tools
  • Blog
  • Contact

Oracle Purchasing Important Queries

Posted on April 17, 2014 by admin Posted in Blog
–Purchase Order, Requisition and Receipt Details Query
SELECT prh.segment1 requisition_number,
       prl.line_num requisition_line_num,
       ph.segment1 po_num,
       pl.line_num po_line_num,
       rsh.receipt_num receipt_num,
       rsl.line_num receipt_line_num,
       pll.line_location_id,

       pll.ship_to_location_id,
       hl.description ship_to_location,
       pd.po_distribution_id,
       rsl.line_num receipt_line_num,
       rsl.quantity_shipped,
       rsl.quantity_received,
       pd.code_combination_id charge_acct_id,
       prd.distribution_id requisition_distribution_id,
       pd.req_distribution_id req_distribution_id,
       prl.requisition_line_id
  FROM rcv_shipment_headers rsh,
       rcv_shipment_lines rsl,
       po_headers ph,
       po_lines pl,
       po_line_locations pll,
       po_distributions pd,
       po_req_distributions_all prd,
       po_requisition_lines_all prl,
       po_requisition_headers prh,
       hr_locations_all hl
 WHERE     rsl.PO_HEADER_ID = 123456
       AND rsh.shipment_header_id = rsl.shipment_header_id
       AND ph.po_header_id = rsl.po_header_id
       AND ph.po_header_id = pl.po_header_id
       AND rsl.po_line_id = pl.po_line_id
       AND pl.po_line_id = pll.po_line_id
       AND rsl.po_line_location_id = pll.line_location_id
       AND pd.po_line_id = pl.po_line_id
       AND pd.po_header_id = ph.po_header_id
       AND pd.line_location_id = pll.line_location_id
       AND prd.distribution_id(+) = pd.req_distribution_id
       AND prl.requisition_line_id(+) = prd.requisition_line_id
       AND prh.requisition_header_id(+) = prl.requisition_header_id
       AND hl.location_id = pll.ship_to_location_id;
— Approval Groups Query
SELECT pcr.amount_limit
  FROM fnd_user fur,
       per_assignments_x pax,
       per_jobs pjs,
       PO_POSITION_CONTROLS_ALL ppc,
       po_control_rules pcr,
       PO_CONTROL_FUNCTIONS pcf,
       hr_operating_units hou
 WHERE     fur.user_id = fnd_global.user_id
       AND pax.person_id = fur.employee_id
       AND pax.primary_flag = ‘Y’
       AND pjs.job_id = pax.job_id
       AND ppc.job_id = pjs.job_id
       AND ppc.org_id = hou.organization_id
       AND ppc.control_function_id = pcf.control_function_id
       AND pcr.control_group_id = ppc.control_group_id
       AND UPPER (pcr.object_code) = ‘DOCUMENT_TOTAL’
       AND UPPER (pcf.control_function_name) =
              ‘APPROVE PURCHASE REQUISITIONS’;
— Purchase Order and Receipts associated with a Payables Invoice Query
SELECT aia.invoice_num Invoice_number,
       NVL (pha.segment1, ‘100’) PO_number,
       NVL (rsl.shipment_line_id, 1000) Shipment_line_id
  FROM ap_invoices_all AIA,
       ap_invoice_lines_all AILA,
       ap_invoice_distributions_all AIDA,
       po_headers_all PHA,
       po_lines_all PLA,
       po_line_locations_all PLLA,
       po_distributions_all PDA,
       rcv_shipment_lines RSL,
       rcv_transactions RT
 WHERE     AIA.invoice_id = AILA.invoice_id
       AND AIA.invoice_id = AIDA.invoice_id
       AND AIDA.line_type_lookup_code IN (‘ACCRUAL’, ‘ITEM’)
       AND AILA.line_type_lookup_code = (‘ITEM’)
       AND AILA.line_number = AIDA.invoice_line_number
       AND AILA.po_distribution_id = PDA.po_distribution_id(+)
       AND AILA.po_line_location_id = PLLA.line_location_id(+)
       AND AILA.po_line_id = PLA.po_line_id(+)
       AND AILA.po_header_id = PHA.po_header_id(+)
       AND AILA.rcv_transaction_id = RT.transaction_id(+)
       AND AILA.rcv_shipment_line_id = RSL.shipment_line_id(+)
       AND AIA.invoice_num IN (‘INV345201’, ‘TESTINV5301’);
— Invoice Accounting Data Query
SELECT AIDA.invoice_distribution_id “Invoice Distribution ID”,
       GSOB.name “Ledger”,
       AIA.gl_date “GL Date”,
       XDL.rounding_class_code “Accounting Class”,
       XDL.unrounded_entered_dr “Accounted DR”,
       XDL.unrounded_entered_cr “Accounted CR”,
          GCC.SEGMENT1
       || ‘.’
       || GCC.SEGMENT2
       || ‘.’
       || GCC.SEGMENT3
       || ‘.’
       || GCC.SEGMENT4
       || ‘.’
       || GCC.SEGMENT5
          “Account”
  FROM xla_distribution_links XDL,
       xla_ae_lines XAL,
       gl_code_combinations GCC,
       ap_invoices_all AIA,
       ap_invoice_distributions_all AIDA,
       gl_sets_of_books GSOB
 WHERE     AIA.invoice_id = AIDA.invoice_id
       AND AIDA.invoice_distribution_id = XDL.source_distribution_id_num_1
       AND XDL.ae_header_id = XAL.ae_header_id
       AND XDL.ae_line_num = XAL.ae_line_num
       AND XAL.code_combination_id = GCC.code_combination_id
       AND GSOB.set_of_books_id = AIA.set_of_books_id
       AND AIA.invoice_num = ‘INV345201’
       AND XDL.source_distribution_type = ‘AP_INV_DIST’;
— Check numbers for an Invoice Query
SELECT aia.invoice_num “Invoice_Number”,
       aca.check_number “Check_Number”
  FROM ap_invoices_all AIA,
       ap_checks_all ACA,
       ap_invoice_payments_all AIP,
       iby_payments_all IPA
 WHERE     AIA.invoice_id = AIP.invoice_id
       AND ACA.check_id = AIP.check_id
       AND ACA.payment_id = IPA.payment_id
       AND AIA.invoice_num = ‘TESTINV6234’;
— Supplier Details Query
SELECT ASP.vendor_name “Supplier Name”,
       ASSA.vendor_site_code “Supplier Site Code”,
       ASSA.address_line1 “Address Line 1”,
       ASSA.city “City”,
       ASSA.state “State”,
       ASSA.county “County”,
       ASSA.country “Country”,
       ASCA.vendor_contact_id “Vendor Contact Id”,
       HP_OBJECT.person_first_name “Contact First Name”,
       HP_OBJECT.person_middle_name “Contact Middle Name”,
       HP_OBJECT.person_last_name “Contact Last Name”,
       HP_PARTY.email_address “Contact Email Address”
  FROM ap_suppliers ASP,
       ap_supplier_sites_all ASSA,
       ap_supplier_contacts ASCA,
       hz_parties HP_OBJECT,
       hz_parties HP_PARTY,
       hz_relationships HR
 WHERE     ASP.vendor_id = ASSA.vendor_id
       AND ASP.party_id = HR.subject_id
       AND HR.relationship_id = ASCA.relationship_id
       AND HR.object_id = HP_OBJECT.party_id
       AND HR.party_id = HP_PARTY.party_id
       AND HR.relationship_code = ‘CONTACT’
       AND ASP.vendor_name = ‘Test Supplier 123’;
— Supplier Bank Information Query
SELECT DECODE (IEPA.supplier_site_id, NULL, ‘Supplier’, ‘Supplier Site’)
          “Level”,
       ASP.vendor_name “Vendor Name”,
       ASSA.vendor_site_code “Vendor Site Code”,
       HOP_BANK.organization_name “Bank Name”,
       HOP_BANK.bank_or_branch_number “Bank Number”,
       HOP_BRANCH.organization_name “Branch Name”,
       HOP_BRANCH.bank_or_branch_number “Branch Number”,
       IEBA.bank_account_name “Bank Account Name”,
       IEBA.bank_account_num “Bank Account Number”
  FROM ap_suppliers ASP,
       ap_supplier_sites_all ASSA,
       hz_party_sites HPS,
       hz_organization_profiles HOP_BANK,
       hz_organization_profiles HOP_BRANCH,
       iby_external_payees_all IEPA,
       iby_pmt_instr_uses_all IPIUA,
       iby_ext_bank_accounts IEBA
 WHERE     IEPA.ext_payee_id = IPIUA.ext_pmt_party_id
       AND IPIUA.instrument_id = IEBA.ext_bank_account_id
       AND IEBA.bank_id = HOP_BANK.party_id
       AND IEBA.branch_id = HOP_BRANCH.party_id
       AND ASP.party_id = IEPA.payee_party_id
       AND ASP.vendor_id = ASSA.vendor_id
       AND ASSA.vendor_site_id =
              NVL (IEPA.supplier_site_id, ASSA.vendor_site_id)
       AND ASSA.party_site_id = HPS.party_site_id
       AND ipiua.payment_flow = ‘DISBURSEMENTS’
       AND ASP.vendor_name = ‘Test Supplier 123’;
— Internal Bank Details Query
SELECT CBA.bank_account_name “Bank_Account_Name”,
       CBA.bank_account_num “Bank_Account_Number”,
       HOP_BANK.organization_name “Bank_name”,
       HOP_BANK.bank_or_branch_number “Bank_Number”,
       HOP_BRANCH.bank_or_branch_number “Branch_Number”
  FROM ce_bank_accounts CBA,
       hz_organization_profiles HOP_BANK,
       hz_organization_profiles HOP_BRANCH
 WHERE     CBA.bank_account_name = ‘BAN-1234’
       AND CBA.bank_id = HOP_BANK.PARTY_ID
       AND CBA.bank_branch_id = HOP_BRANCH.party_id;
— Payment Documents and Formats associated with an Internal Bank Account Query
SELECT CBA.bank_account_num,
       CBA.bank_account_name,
       CPD.payment_document_name,
       IFT.format_name,
       IFB.format_template_code
  FROM ce_bank_accounts CBA,
       ce_payment_documents CPD,
       iby_formats_tl IFT,
       iby_formats_b IFB
 WHERE     CBA.bank_account_id = CPD.internal_bank_account_id
       AND CPD.format_code = IFT.format_code
       AND IFT.format_code = IFB.format_code
       AND IFT.language = USERENV (‘LANG’)
       AND IFT.source_lang = USERENV (‘LANG’)
       AND CBA.bank_account_name = ‘BofA-204’;
« Oracle Purchasing AME Tables and APIs
Important tables in Oracle Receivables (TCA – Trading Community Architecture) »

Recent Posts

  • Oracle Quizz
  • How to invoke a calendar in a form
  • Oracle DBA Resume
  • JAVA Quizz
  • DBA Quizz

Categories

  • Blog
  • Quizz

Courses List

  • Oracle Apps Technical support
  • ORACLE DBA
  • Oracle SCM – PO & OM

About Us

Oracleapps4u.net is a global provider of oracle application consulting services. We are passionate about helping our clients to adapt and grow in a changing world of technology by focusing on innovation, quality, process, workforce development and re-usable solutions.

Services

Assurance Services BI & Performance Management Business Process Services Consulting Digital Enterprise Enterprise Solutions IT Infrastructure Services IT Services Platform Solutions Supply Chain Management

Events

Enterprise Security & Risk Management Enterprise Solutions iON Small and Medium Business IT Infrastructure Services IT Services Platform Solutions Supply Chain Management

Contact Us

Oracleapps4u.net
ASA Technologies,
Raheja Mindspace IT Park,
Hitech City, Hyderabad.

Phone No: +91-9885101475
Email: info@oracleapps4u.net

CyberChimps WordPress Themes

Copyright © 2015 Oracleapps4u.net