Supplier Master Details in Oracle Apps R12

(
SELECT p.payment_method_code
FROM apps.iby_ext_party_pmt_mthds p
WHERE p.ext_pmt_party_id = d.ext_payee_id
AND p.primary_flag = ‘Y’
AND ROWNUM = 1
) AS “Site Payment Method”,

TO_CHAR(a.creation_date, ‘DD-MM-YYYY’) AS “Supplier Creation Date”,

NVL(
( SELECT papf.full_name
FROM fnd_user fu,
per_all_people_f papf
WHERE fu.employee_id = papf.person_id(+)
AND fu.user_id = a.created_by
AND ROWNUM = 1
),
( SELECT fu.user_name
FROM apps.fnd_user fu
WHERE fu.user_id = a.created_by
)
) AS “Supplier Created By”,

TO_CHAR(a.last_update_date, ‘DD-MON-YYYY’) AS “Supplier Last Update Date”,

NVL(
( SELECT papf.full_name
FROM fnd_user fu,
per_all_people_f papf
WHERE fu.employee_id = papf.person_id(+)
AND fu.user_id = a.last_updated_by
AND ROWNUM = 1
),
( SELECT fu.user_name
FROM apps.fnd_user fu
WHERE fu.user_id = a.last_updated_by
)
) AS “Supplier Last Updated By”,

b.vendor_site_code AS “Site Code”,
b.address_line1,
b.address_line2,
b.address_line3,
b.address_line4,
b.city,
b.state,
b.zip AS “Post Code”,

TO_CHAR(b.creation_date, ‘DD-MM-YYYY’) AS “Site Creation Date”,

NVL(
( SELECT papf.full_name
FROM fnd_user fu,
per_all_people_f papf
WHERE fu.employee_id = papf.person_id(+)
AND fu.user_id = b.created_by
AND ROWNUM = 1
),
( SELECT fu.user_name
FROM apps.fnd_user fu
WHERE fu.user_id = b.created_by
)
) AS “Site Created By”,

NVL(
( SELECT papf.full_name
FROM fnd_user fu,
per_all_people_f papf
WHERE fu.employee_id = papf.person_id(+)
AND fu.user_id = b.last_updated_by
AND ROWNUM = 1
),
( SELECT fu.user_name
FROM apps.fnd_user fu
WHERE fu.user_id = b.last_updated_by
)
) AS “Site Last Updated By”,

TO_CHAR(b.last_update_date, ‘DD-MON-YYYY’) AS “Site Last Update Date”,

b.supplier_notif_method AS “Site PO Notif Method”,
b.email_address AS “PO Email”,

a.pay_group_lookup_code AS “Pay Group”,
d.default_payment_method_code AS “Default Site Payment Method”,
c.name AS “Payment Terms”,

(
SELECT TO_CHAR(MAX(p.creation_date), ‘DD-MON-YYYY’)
FROM iby_payments_all p
WHERE p.inv_payee_supplier_id = a.vendor_id
AND p.supplier_site_id = b.vendor_site_id
) AS “Last Payment Date”,

(
SELECT party_name
FROM (
SELECT hpc.party_name
FROM apps.ap_suppliers asp,
apps.ap_supplier_contacts asco,
apps.hz_relationships hr,
apps.hz_org_contacts hoc,
apps.hz_parties hpc
WHERE asp.party_id = hr.subject_id
AND hr.relationship_code = ‘CONTACT’
AND hr.object_table_name = ‘HZ_PARTIES’
AND hr.object_id = hpc.party_id
AND asco.relationship_id = hoc.party_relationship_id
AND hoc.party_relationship_id = hr.relationship_id
AND asp.party_id = a.party_id
ORDER BY hpc.creation_date DESC
)
WHERE ROWNUM = 1
) AS “Contact Name”

FROM
apps.ap_suppliers a,
apps.ap_supplier_sites_all b,
apps.ap_terms c,
apps.iby_external_payees_all d

WHERE 1 = 1
AND a.vendor_id = b.vendor_id(+)
AND b.terms_id = c.term_id(+)
AND d.supplier_site_id(+) = b.vendor_site_id
AND d.payment_function(+) = ‘PAYABLES_DISB’
AND a.vendor_type_lookup_code NOT IN (‘EMPL_EXP’, ‘EMPLOYEE’)
AND b.inactive_date IS NULL

AND TRUNC(a.creation_date) >=
NVL(TO_DATE(”’ || TO_CHAR(p_from_creation_date,’DD-MON-YYYY’) || ”’,’DD-MON-YYYY’),
TRUNC(a.creation_date))

AND TRUNC(a.creation_date) =
NVL(TO_DATE(”’ || TO_CHAR(p_from_update_date,’DD-MON-YYYY’) || ”’,’DD-MON-YYYY’),
TRUNC(a.last_update_date))

AND TRUNC(a.last_update_date) <=
NVL(TO_DATE(''' || TO_CHAR(p_to_update_date,'DD-MON-YYYY') || ''','DD-MON-YYYY'),
TRUNC(a.last_update_date))

AND DECODE(a.end_date_active, NULL, 'ACTIVE', 'INACTIVE') =
NVL(NULLIF(''' || p_status || ''',''),
DECODE(a.end_date_active, NULL, 'ACTIVE', 'INACTIVE'))

AND UPPER(a.vendor_name) LIKE
UPPER(NVL('%' || NULLIF(''' || p_supplier_name || ''','') || '%',
a.vendor_name))

AND a.vendor_type_lookup_code =
NVL(NULLIF(''' || p_supplier_type || ''',''),
a.vendor_type_lookup_code)

ORDER BY a.vendor_name;