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