Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Wednesday, 11 May 2016

To Get Receivables Ship To Query

SELECT DISTINCT hca.account_number, hps.party_site_number, hp.party_name,
                hl1.address1 street1, hl1.address2 street2,
                hl1.address3 street3, hl1.address4 street4,
                hl1.house_number house, hl1.city, hl1.postal_code,
                DECODE (hcp.phone_line_type,
                        'GEN', hcp.phone_area_code || hcp.phone_number,
                        NULL
                       ) phone_number,
                hl1.country, hl1.state region,
                DECODE (hcp.phone_line_type,
                        'FAX', hcp.phone_area_code || hcp.phone_number,
                        NULL
                       ) fax_number,
                hp.email_address
           FROM hz_parties hp,
                hz_locations hl1,
                hz_cust_site_uses_all hcsu,
                hz_cust_acct_sites_all hcas,
                hz_party_sites hps,
                hz_cust_accounts hca,
                hz_contact_points hcp
          WHERE 1 = 1
            AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
            AND hcas.party_site_id = hps.party_site_id
            AND hps.location_id = hl1.location_id
            AND hp.party_id = hcp.owner_table_id(+)
            AND hp.party_id = hps.party_id
            AND hca.cust_account_id = hcas.cust_account_id
            AND hcsu.site_use_code = 'SHIP_TO'
--            AND HP.PARTY_NAME LIKE 'A%'

ORDER BY        hp.party_name;
Share this article :

2 comments:

  1. i Buddy,


    I genuinely do look forward for the time where you post some new write ups. Your blog make me feel so educated! Continue soaring and writing please.


    i created one alert that picks employees that have probation end date before one month and mail is sent to supervisor and it works fine but a new requirement is that when the probation initiation begins it should pick from hr_api_transactions table and stop the alert from sending afte probation confirmation please help. below is the query

    SELECT papf.employee_number,
    papf.full_name employee_name,
    pjt.NAME job,
    pp.NAME position_name,
    papf.start_date,
    paaf.date_probation_end,
    paaf.probation_period, notice_period,
    DECODE (probation_unit,
    'D', 'DAYS',
    'W', 'WEEKS',
    'H', 'HOURS',
    'M', 'MONTHS',
    'Y', 'YEARS'
    ) probation_unit,
    DECODE (notice_period_uom,
    'D', 'DAYS',
    'W', 'WEEKS',
    'H', 'HOURS',
    'M', 'MONTHS',
    'Y', 'YEARS'
    ) notice_period_uom,
    hl_freq.meaning, paaf.effective_start_date, paaf.effective_end_date,
    mg.full_name supervisor_name, mg.email_address
    supervisor_email
    FROM per_all_assignments_f paaf,
    per_jobs_tl pjt,
    per_grades_tl pgt,
    per_all_people_f papf,
    hr_lookups hl_freq,
    per_all_positions pp,
    per_all_people_f mg
    WHERE 1 = 1
    AND paaf.person_id = papf.person_id
    AND paaf.business_group_id = papf.business_group_id
    AND pgt.grade_id(+) = paaf.grade_id
    AND pjt.job_id = paaf.job_id
    AND paaf.frequency = hl_freq.lookup_code
    AND papf.business_group_id = pp.business_group_id
    AND pp.position_id = paaf.position_id
    AND paaf.person_id = papf.person_id
    AND paaf.job_id = pp.job_id
    AND paaf.supervisor_id = mg.person_id(+)
    AND TRUNC (SYSDATE) BETWEEN TRUNC (mg.effective_start_date)
    AND TRUNC (mg.effective_end_date)
    AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
    AND paaf.effective_end_date
    AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
    AND papf.effective_end_date
    AND hl_freq.lookup_type = 'FREQUENCY'
    AND paaf.probation_period IS NOT NULL
    AND TRUNC (paaf.date_probation_end) BETWEEN paaf.effective_start_date
    AND NVL (paaf.effective_end_date,
    hr_general.end_of_time
    )
    AND TRUNC (paaf.date_probation_end) BETWEEN papf.effective_start_date
    AND NVL (papf.effective_end_date,
    hr_general.end_of_time
    )
    AND TRUNC (NVL (paaf.date_probation_end, SYSDATE))
    BETWEEN papf.effective_start_date
    AND papf.effective_end_date
    AND TRUNC (NVL (paaf.date_probation_end, SYSDATE))
    BETWEEN paaf.effective_start_date
    AND paaf.effective_end_date
    AND TO_CHAR (papf.original_date_of_hire, 'yyyy') =
    TO_CHAR (SYSDATE, 'yyyy')
    AND TO_DATE (SYSDATE, 'DD-MM-RRRR') =
    TO_DATE (paaf.date_probation_end, 'DD-MM-RRRR')
    - 30





    Great effort, I wish I saw it earlier. Would have saved my day :)


    Obrigado,

    ReplyDelete
  2. .Halo,


    Zoooooooom! That’s how speedy and easy this read was! Looking forward to more of such powerful content on To Get Receivables Ship To Query

    I have data in a column
    #abcd#efghi#jlmn#opqrs

    I need to retrieve values between '#'. I need to get output as below

    var1 = abcd
    var2 = efghi
    var3 = jlmn
    var4 = opqrs

    Similary if
    column value is

    #abcd#efghi#jlmnopqrs

    var1 = abcd
    var2 = efghi
    var3 = jlmnopqrs
    var4 = null



    But nice Article Mate! Great Information! Keep up the good work!


    Thanks and Regards
    Ajeeth

    ReplyDelete