How to find Contacts for a customer
SELECT COUNT(*)
FROM HZ_RELATIONSHIPS, HZ_CUST_ACCOUNTS
HZ_RELATIONSHIPS.OBJECT_ID = HZ_cUST_ACCOUNTS.PARTY_ID
AND HZ_RELATIONSHIPS.OBJECT_TYPE = 'ORGANIZATION'
AND HZ_RELATIONSHIPS.RELATIONSHIP_CODE = 'CONTACT_OF'
Get EFF details at the Order (header level in this query, but can be fetched from lines EFF with slight modifications), with links to AR Invoices (Headers and Lines tables), Project Billing Events Table, and Contracts tables.
SELECT count(*), d.customer_trx_id,
h.attribute_char1, h.attribute_char2, h.attribute_char3, h.attribute_char4, h.attribute_char5
FROM PJB_INV_LINE_DISTS a,
--pjb_invoice_lines a,
pjb_invoice_headers b,
ra_customer_Trx_lines_all c,
ra_customer_trx_all d,
PJB_BILLING_EVENTS E,
DOO_FULFILL_LINEs_ALL F,
doo_headers_all g,
doo_headers_eff_b h
--
where a.invoice_id = b.invoice_id
and a.invoice_line_id = c.interface_line_attribute5
and a.contract_id = c.interface_line_attribute2
and c.customer_Trx_id = d.customer_trx_id
and d.trx_number = :p_trx_number
--
AND E.EVENT_ID = A.TRANSACTION_ID
AND e.sourceref = f.fulfill_line_id
and f.header_id = g.header_id
--
and h.header_id = g.header_id
group by h.attribute_char1, h.attribute_char2, h.attribute_char3, h.attribute_char4, h.attribute_char5, d.customer_trx_id
Employee Data (Person#, original_hire_date, term_date, and rehire_date)
with
rehire as (
select to_Char(date_start, 'MM/DD/YYYY') rehire_date,
a.person_id
from per_all_assignments_m a, per_periods_of_service b
where 1=1
and a.person_id = b.person_id
and action_code ='REHIRE'
and assignment_type ='E'
and sysdate between a.effective_start_date and a.effective_end_date
and actual_termination_date is null),
term as (
select to_char(max(actual_termination_date), 'MM/DD/YYYY') term_date,
a.person_id
from per_all_assignments_m a, per_periods_of_service b
where 1=1
and a.person_id = b.person_id
and assignment_type ='E'
group by a.person_id),
hire as (
select to_char(min(date_start), 'MM/DD/YYYY') hire_date,
person_id
from per_periods_of_service
where 1=1
group by person_id)
select person_number,
rehire_date,
term_date,
hire.hire_date original_hire_date,
user_status assignment_status
from per_all_people_f a,
rehire,
term,
hire,
per_all_assignments_m b,
per_assignment_status_types_tl c
where rehire.person_id (+) = a.person_id
and term.person_id (+) = a.person_id
and hire.person_id = a.person_id
and a.person_id = b.person_id
and b.assignment_status_type_id = c.assignment_status_type_id
and sysdate between b.effective_start_date and b.effective_end_date
and b.assignment_type = 'E'
SELECT
a.pick_slip_number ,
b.source_header_number transfer_order_number,
c.supply_order_number ,
c.supply_order_reference_number
FROM
INV_PICK_SLIP_NUMBERS a ,
WSH_DELIVERY_DETAILS b ,
DOS_SUPPLY_HEADERS c ,
INV_TRANSFER_ORDER_HEADERS d,
INV_TRANSFER_ORDER_LINES e
WHERE 1 =1
AND a.PICK_SLIP_NUMBER = 3001
AND a.pick_slip_batch_id = b.batch_id
AND b.source_header_number = d.header_number
AND d.header_id = e.header_id
AND e.source_header_id = c.header_id
------------
select wdd.batch_id, hdr.header_id hdr_header_id, lines.*
from inv_transfer_order_headers hdr,
inv_Transfer_order_lines lines,
dos_supply_headers sup_hdr,
wsh_Delivery_details wdd
-- ,inv_pick_slip_numbers pickslips
where 1=1
and hdr.header_number= '778106'
and lines.header_id = hdr.header_id
and sup_hdr.header_id (+) = hdr.header_id
and wdd.source_header_number = hdr.header_number
and wdd.source_line_number = lines.line_number
-- and nvl(pickslips.pick_slip_batch_id, 'x') = nvl(wdd.batch_id, 'x')
order by hdr.creation_Date desc;
AR Invoices with Outstanding balance.
select rctt.name Invoice_type,
gcc.concatenated_segments accounting_string,
rcgl.gl_date,
aps.amount_due_remaining,
rct.trx_number,
gcc.segment1 bu_company,
NULL description,
rct.trx_date,
aps.due_date,
rct.bill_to_customer_id,
hca.account_number,
hcsu.location site_location
--hcsu.site_use_id
--hcsu.orig_system_reference
FROM RA_CUSTOMER_TRX_ALL rct,
ar_receivable_applications_all ara,
ar_cash_receipts_all acr,
ar_payment_schedules_All aps,
ra_customer_trx_lines_all rctl,
RA_CUST_TRX_LINE_GL_DIST_ALL rcgl,
ra_Cust_trx_types_All rctt,
gl_code_combinations_kfv gcc,
hz_cust_accounts hca,
hz_cust_site_uses_all hcsu
where ara.applied_customer_trx_id = rct.customer_trx_id
and ara.cash_receipt_id = acr.cash_receipt_id
and aps.customer_trx_id = rct.customer_Trx_id
and rctl.customer_Trx_id = rct.customer_trx_id
and rcgl.customer_trx_line_id = rctl.customer_Trx_line_id
and rct.cust_trx_type_id = rctt.cust_Trx_type_id
and rcgl.code_combination_id = gcc.code_combination_id
and rct.bill_to_customer_id = hca.cust_account_id
and rct.bill_to_site_use_id = hcsu.site_use_id
and rcgl.account_class = 'REV'
-- and display = 'Y'
--and rct.trx_number ='NTP00602283'
and aps.amount_due_remaining > 0;
How to find the default payment method from Supplier Master
Accrual Query (EBS) - May work in Cloud too!
SELECT (SELECT name
FROM hr_all_organization_units
WHERE organization_id = k.expenditure_organization_id)
exp_org,
l.concatenated_segments
po_dist_charge_account,
(SELECT segment1
FROM pa_projects_All
WHERE project_id = k.project_id)
project_num,
(SELECT name
FROM pa_projects_All
WHERE project_id = k.project_id)
project_name,
(SELECT task_number
FROM pa_tasks
WHERE task_id = k.task_id)
task_num,
(SELECT task_name
FROM pa_tasks
WHERE task_id = k.task_id)
task_name,
k.expenditure_type,
e.accounted_cr
project_raw_cost,
'83'
operating_unit,
'US Ledger'
Ledger_name,
b.period_name,
c.je_line_num
je_line_num,
i.segment1
po_number,
i.creation_date
Po_creation_date,
m.last_update_date
Po_shipment_last_update_date,
a.name
je_batch_name,
accounting_class_code
transaction_type,
NULL
release_num,
j.line_num
po_line_num,
(SELECT full_name
FROM per_all_people_f
WHERE person_id = k.deliver_to_person_id
AND SYSDATE BETWEEN effective_start_date
AND effective_end_date)
requester_name,
(SELECT agent_name
FROM po_agents_v
WHERE agent_id = i.agent_id)
buyer_name
FROM gl_je_batches a,
gl_je_headers b,
gl_je_lines c,
gl_import_references d,
xla_ae_lines e,
xla_ae_headers f,
xla.xla_transaction_entities g,
rcv_accounting_events h,
po_headers_all i,
po_lines_all j,
po_distributions_all k,
gl_code_combinations_kfv l,
po_line_locations_all m
WHERE b.je_batch_id = a.je_batch_id
AND c.je_header_id = b.je_header_id
AND d.je_header_id = b.je_header_id
AND d.je_batch_id = a.je_Batch_id
AND c.je_line_num = d.je_line_num
AND e.gl_sl_link_id = d.gl_sl_link_id
AND e.ae_header_id = f.ae_header_id
AND g.entity_id = f.entity_id
--
AND h.accounting_event_id = g.source_id_int_2
AND h.po_line_id = j.po_line_id
AND h.po_distribution_id = k.po_distribution_id
--
AND i.po_header_id = h.po_header_id
AND j.po_header_id = i.po_header_id
AND k.po_header_id = i.po_header_id
AND k.po_line_id = j.po_line_id
AND k.code_combination_id = l.code_combination_id
AND m.po_header_id = i.po_header_id
AND m.po_line_id = j.po_line_id
AND b.period_name = 'NOV-2022'
AND d.gl_sl_link_table = 'XLAJEL'
AND accounting_class_code = 'ACCRUAL'
AND a.name = :batch_name --'Cost Management A 113083 29632480'
--and expenditure_organization_id is null
order by exp_org, po_number, line_num
