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 :

3 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
  3. Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.

    ReplyDelete