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;
i Buddy,
ReplyDeleteI 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,
.Halo,
ReplyDeleteZoooooooom! 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
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