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'

SQL Query To Link Transfer Order, Pick Slip Number, and Supply Orchestration Number (Metalink Doc ID 2309017.1)

 

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





Select "Payment Details" on the right