[Home] [Help]
581: AND NVL(vendor_start_date_active, SYSDATE - 1) <= SYSDATE
582: AND NVL(vendor_end_date_active, SYSDATE + 1) > SYSDATE;
583: CURSOR get_vendor_cert IS
584: SELECT 'X'
585: FROM ahl_vendor_certifications_v
586: WHERE vendor_id = p_vendor_id
587: AND TRUNC(active_start_date) <= TRUNC(SYSDATE)
588: AND TRUNC(nvl(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
589: l_exist VARCHAR2(1);
625: AND purchasing_site_flag = G_YES_FLAG
626: AND NVL(RFQ_ONLY_SITE_FLAG, G_NO_FLAG) = G_NO_FLAG;
627: CURSOR get_vendor_cert IS
628: SELECT 'X'
629: FROM ahl_vendor_certifications_v
630: WHERE vendor_id = p_vendor_id
631: AND vendor_site_id = p_vendor_site_id
632: AND TRUNC(active_start_date) <= TRUNC(SYSDATE)
633: AND TRUNC(nvl(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
4591: IF (l_vendor_id IS NOT NULL AND l_vendor_site_id IS NOT NULL) THEN
4592: SELECT IV.service_duration INTO l_service_duration
4593: FROM ahl_inv_service_item_rels SI,
4594: ahl_item_vendor_rels IV,
4595: ahl_vendor_certifications_v VC
4596: WHERE SI.inv_item_id = p_x_osp_order_line_rec.inventory_item_id
4597: AND SI.inv_org_id = p_x_osp_order_line_rec.inventory_org_id
4598: AND SI.service_item_id = p_x_osp_order_line_rec.service_item_id
4599: AND VC.vendor_id = l_vendor_id
6024: CURSOR get_vendor_cert(c_inv_item_id NUMBER,
6025: c_inv_org_id NUMBER,
6026: c_service_item_id NUMBER) IS
6027:
6028: /* mpothuku modified ahl_vendor_certifications to _v to fix the OU related Bug 5350882 on 21-Jul-06
6029: Also added the inv_org_id related OU filter */
6030:
6031: SELECT IV.vendor_certification_id,
6032: IV.rank
6031: SELECT IV.vendor_certification_id,
6032: IV.rank
6033: FROM ahl_inv_service_item_rels SI,
6034: ahl_item_vendor_rels IV,
6035: ahl_vendor_certifications_v VC
6036: WHERE SI.inv_service_item_rel_id = IV.inv_service_item_rel_id
6037: AND IV.vendor_certification_id = VC.vendor_certification_id
6038: AND SI.inv_item_id = c_inv_item_id
6039: AND SI.inv_org_id = c_inv_org_id
6048: CURSOR get_vendor_ids(c_vendor_cert_id NUMBER) IS
6049: SELECT vendor_id,
6050: vendor_site_id,
6051: vendor_contact_id
6052: FROM ahl_vendor_certifications_v
6053: WHERE vendor_certification_id = c_vendor_cert_id
6054: AND trunc(active_start_date) <= trunc(SYSDATE)
6055: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
6056: TYPE vendor_rank_count_rec_type IS RECORD (
6274: AND TRUNC(NVL(vendor_start_date_active, SYSDATE - 1)) <= TRUNC(SYSDATE)
6275: AND TRUNC(NVL(vendor_end_date_active, SYSDATE + 1)) > TRUNC(SYSDATE);
6276: CURSOR get_vendor_cert IS
6277: SELECT 'X'
6278: FROM ahl_vendor_certifications_v
6279: WHERE vendor_id = p_vendor_id
6280: AND TRUNC(active_start_date) <= TRUNC(SYSDATE)
6281: AND TRUNC(nvl(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
6282: CURSOR val_vendor_site_id_csr IS
6288: AND purchasing_site_flag = G_YES_FLAG
6289: AND NVL(RFQ_ONLY_SITE_FLAG, G_NO_FLAG) =G_NO_FLAG ;
6290: CURSOR get_site_cert IS
6291: SELECT 'X'
6292: FROM ahl_vendor_certifications_v
6293: WHERE vendor_id = p_vendor_id
6294: AND vendor_site_id = p_vendor_site_id
6295: AND TRUNC(active_start_date) <= TRUNC(SYSDATE)
6296: AND TRUNC(nvl(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
6301: AND vendor_contact_id = p_vendor_contact_id
6302: AND TRUNC(NVL(inactive_date, SYSDATE+1)) > TRUNC(SYSDATE);
6303: CURSOR get_contact_cert IS
6304: SELECT 'X'
6305: FROM ahl_vendor_certifications
6306: WHERE vendor_contact_id = p_vendor_contact_id
6307: AND TRUNC(active_start_date) <= TRUNC(SYSDATE)
6308: AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
6309: l_exist VARCHAR2(1);
7218: --validate it against OSP header vendor attributes
7219: --Check the vendor itself is valid
7220: BEGIN
7221: SELECT 'X' INTO g_dummy_char
7222: FROM ahl_vendor_certifications_v
7223: WHERE vendor_id = l_vendor_id
7224: AND vendor_site_id = l_vendor_site_id
7225: AND trunc(active_start_date) <= trunc(SYSDATE)
7226: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);