--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';
