Thursday, 25 July 2019

Update PO Match Approval Level Through BackEnd in Oracle Apps R12


In the PO_LINE_LOCATIONS_ALL table, there exists two columns name INSPECTION_REQUIRED_FLAG and RECEIPT_REQUIRED_FLAG. These two column values decide what is the match approval.

INSPECTION_REQUIRED_FLAGRECEIPT_REQUIRED_FLAGMatch Approval
NN2-Way
NY3-Way
YY4-Way


DECLARE 
    num NUMBER; 
    CURSOR c1 IS 
      SELECT h.org_id, 
             l.line_num                         "SEQ NUM", 
             h.segment1                         "PO NUM", 
             h.authorization_status             "STATUS", 
             (SELECT segment1 
              FROM   mtl_system_items_b 
              WHERE  inventory_item_id = l.item_id 
                     AND organization_id = 112) ITEM, 
             h.type_lookup_code                 "TYPE", 
             ll.match_option, 
             CASE 
               WHEN ll.inspection_required_flag = 'N' 
                    AND receipt_required_flag = 'N' THEN '2-Way' 
               WHEN ll.inspection_required_flag = 'N' 
                    AND receipt_required_flag = 'Y' THEN '3-Way' 
               WHEN ll.inspection_required_flag = 'Y' 
                    AND receipt_required_flag = 'Y' THEN '4-Way' 
             END                                "Match Approval", 
             receipt_required_flag, 
             inspection_required_flag, 
             ll.po_line_id, 
             ll.line_location_id 
      FROM   po.po_headers_all h, 
             po.po_lines_all l, 
             po.po_line_locations_all ll 
      WHERE  h.po_header_id = l.po_header_id 
             AND ll.po_line_id = l.po_line_id 
             --AND ll.line_location_id = d.line_location_id   
             AND h.closed_date IS NULL 
             AND h.type_lookup_code NOT IN ( 'QUOTATION' ) 
             --AND receipt_required_flag ||'-'||inspection_required_flag <> 'Y-N'  
             AND h.segment1 = '<PO_NUMBER>'; 
BEGIN 
    num := 0; 

    FOR c_rec IN c1 LOOP 
        num := num + 1; 

        UPDATE po_line_locations_all  --Suppose we need to change the matching level to 3-way from 4 or 2 -way 
        SET    receipt_required_flag = 'Y', 
               inspection_required_flag = 'N' 
        WHERE  po_line_id = c_rec.po_line_id 
               AND line_location_id = c_rec.line_location_id; 
    END LOOP; 

    dbms_output.Put_line('Total records updated : ' 
                         ||num); 
EXCEPTION 
    WHEN OTHERS THEN 
      dbms_output.Put_line('Error ' 
                           ||SQLERRM); 
END; 

No comments:

Post a Comment