[Home] [Help]
123: -----------------------------------------------------------------------
124: --Start of Comments
125: --Name: seed_po
126: --Pre-reqs:
127: --Modifies: po_purge_po_list, po_purge_req_list
128: --Locks:
129: -- None
130: --Function: Construct po purge list for eligible pos that have not been
131: -- updated since last_activity_date. This is a version from
363: -- SQL Why: This is the initial list of reqs to be purged. Later on the
364: -- records in this list will be removed if the records are no
365: -- longer eligible for purging after additional rules are applied
366:
367: INSERT INTO po_purge_req_list
368: ( requisition_header_id,
369: purge_name,
370: double_check_flag
371: )
430: -----------------------------------------------------------------------
431: --Start of Comments
432: --Name: filter_more_referenced_req
433: --Pre-reqs:
434: --Modifies: po_purge_req_list
435: --Locks:
436: -- None
437: --Function: Exclude REQs that are referenced by records that will not get
438: -- purged.
1001: l_msg_data VARCHAR2(2000);
1002:
1003: CURSOR c_po_list IS
1004: SELECT requisition_header_id
1005: FROM po_purge_req_list
1006: WHERE double_check_flag = 'Y';
1007:
1008: BEGIN
1009:
1064:
1065: l_progress := '030';
1066:
1067: FORALL i IN 1..l_out_rec.purge_allowed.COUNT
1068: UPDATE po_purge_req_list PPRL
1069: SET PPRL.double_check_flag = 'N'
1070: WHERE PPRL.requisition_header_id = l_in_rec.entity_ids(i)
1071: AND l_out_rec.purge_allowed(i) <> 'Y';
1072:
2386:
2387: SELECT PPRL.requisition_header_id
2388: BULK COLLECT
2389: INTO l_in_rec.entity_ids
2390: FROM po_purge_req_list PPRL
2391: WHERE PPRL.double_check_flag = 'Y'
2392: AND PPRL.requisition_header_id BETWEEN p_range_low
2393: AND p_range_high;
2394:
2662: FROM po_price_differentials PPD
2663: WHERE PPD.entity_type = 'REQ LINE'
2664: AND EXISTS (
2665: SELECT NULL
2666: FROM po_purge_req_list PPRL,
2667: po_requisition_lines RL
2668: WHERE PPRL.requisition_header_id =
2669: RL.requisition_header_id
2670: AND RL.requisition_line_id = PPD.entity_id
2990: G_MODULE_PREFIX || l_api_name || '.';
2991:
2992: CURSOR c_req_header IS
2993: SELECT PPRL.requisition_header_id
2994: FROM po_purge_req_list PPRL
2995: WHERE PPRL.requisition_header_id BETWEEN p_range_low AND p_range_high
2996: AND PPRL.double_check_flag = 'Y';
2997:
2998: CURSOR c_req_line IS
2996: AND PPRL.double_check_flag = 'Y';
2997:
2998: CURSOR c_req_line IS
2999: SELECT PRL.requisition_line_id
3000: FROM po_purge_req_list PPRL,
3001: po_requisition_lines PRL
3002: WHERE PPRL.requisition_header_id BETWEEN p_range_low AND p_range_high
3003: AND PPRL.double_check_flag = 'Y'
3004: AND PPRL.requisition_header_id = PRL.requisition_header_id;
3447: FROM po_approval_list_lines PALL
3448: WHERE PALL.approval_list_header_id
3449: IN ( SELECT PALH.approval_list_header_id
3450: FROM po_approval_list_headers PALH,
3451: po_purge_req_list PPRL
3452: WHERE PPRL.requisition_header_id =
3453: PALH.document_id
3454: AND PPRL.double_check_flag = 'Y'
3455: AND PPRL.requisition_header_id BETWEEN p_range_low
3476: FROM po_approval_list_headers PALH
3477: WHERE PALH.document_type = 'REQUISITION'
3478: AND PALH.document_id
3479: IN ( SELECT PPRL.requisition_header_id
3480: FROM po_purge_req_list PPRL
3481: WHERE PPRL.double_check_flag = 'Y'
3482: AND PPRL.requisition_header_Id BETWEEN p_range_low
3483: AND p_range_high
3484: );
3529: --IN:
3530: --p_module
3531: -- the location where this is called from.
3532: --p_entity
3533: -- the table to report. 'REQ' for PO_PURGE_REQ_LIST
3534: -- 'PO' for PO_PURGE_PO_LIST
3535: --IN OUT:
3536: --OUT:
3537: --Returns:
3554: IF (p_entity = 'REQ') THEN
3555:
3556: SELECT COUNT(*)
3557: INTO l_count
3558: FROM po_purge_req_list
3559: WHERE double_check_flag = 'Y';
3560:
3561: ELSIF (p_entity = 'PO') THEN
3562: