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; 

Tuesday, 23 July 2019

Query To Find Concurrent Program OPP Service Log File Location On Server In Oracle APPS R12


If concurrent program ends in warning due to any OPP service failure, run below sql by passing request id :

SELECT fcpp.concurrent_request_id req_id, 
       fcp.node_name, 
       fcp.logfile_name 
FROM   fnd_conc_pp_actions fcpp, 
       fnd_concurrent_processes fcp 
WHERE  fcpp.processor_id = fcp.concurrent_process_id 
AND    fcpp.action_type = 6 
AND    fcpp.concurrent_request_id = <request_id>;

Friday, 12 July 2019

How to seach Oracle Objects for Any Specific string included in its definition

Suppose we need to find the Objects having tables with suffix _TL or Table 'FND_FLEX_VALUES' :

Search in Oracle Objects Definition:-

SELECT DISTINCT owner, 
                name, 
                TYPE 
FROM   all_source 
WHERE  name LIKE 'XX%' 
       --AND OBJECT_TYPE IN ('PACKAGE','PACKAGE BODY','TRIGGER','PROCEDURE','FUNCTION') 
       AND ( Upper(text) LIKE '%\_TL %' ESCAPE '\'  
              OR Upper(text) LIKE '%FND_FLEX_VALUES%' );


Search in Form Personalization Definition:-

SELECT (SELECT ffv.user_form_name 
        FROM   fnd_form_vl ffv 
        WHERE  ffv.form_name = ffC.form_name) Form_Name, 
       FFC.SEQUENCE, 
       FFC.function_name, 
       FFC.description, 
       FFC.trigger_event, 
       FFC.trigger_object, 
       FFC.rule_type, 
       FFC.enabled, 
       FFC.condition 
FROM   fnd_form_custom_rules FFC 
WHERE  ( Upper(condition) LIKE '%\_TL %' ESCAPE '\' 
          OR Upper(condition) LIKE '%FND_FLEX_VALUES%' );  


Search in Value Sets Definition:-

SELECT ffvs.flex_value_set_id, 
       ffvs.flex_value_set_name, 
       ffvs.description                   set_description, 
       ffvs.validation_type, 
       ffvt.value_column_name, 
       ffvt.meaning_column_name, 
       ffvt.id_column_name, 
       ffvt.application_table_name, 
       ffvt.additional_where_clause, 
       (SELECT user_name 
        FROM   fnd_user 
        WHERE  user_id = ffvs.created_by) USER_NAME 
FROM   fnd_flex_value_sets ffvs, 
       fnd_flex_validation_tables ffvt 
WHERE  ffvs.flex_value_set_id = ffvt.flex_value_set_id 
       AND ( ( Upper(ffvt.application_table_name) LIKE '%\_TL %' ESCAPE '\' 
                OR Upper(ffvt.application_table_name) LIKE '%FND_FLEX_VALUES%' ) 
              OR ( Upper(ffvt.additional_where_clause) LIKE '%\_TL %' ESCAPE '\' 
                 ) ) 
       AND ffvs.created_by NOT IN ( -1, --ANONYMOUS  
                                    0, --  SYSADMIN  
                                    122, --  ORACLE12.2.0 
                                    2, --  INITIAL SETUP 
                                    1, --  AUTOINSTALL 
                                    121, --  ORACLE12.1.0 
                                    120--  ORACLE12.0.0 
                                   ); 


Search in View or Trigger Definition:-

STEP1: 

CREATE TABLE xxcns_clob_view 
  ( 
     view_name VARCHAR2(100), 
     text      CLOB, 
     id        NUMBER 
  );

STEP2: 

INSERT INTO xxcns_clob_view 
SELECT trigger_name, 
       To_lob(trigger_body), 
       rownum 
FROM   all_triggers 
WHERE  trigger_name LIKE 'XX%'; 


INSERT INTO xxcns_clob_view 
SELECT view_name, 
       To_lob(text), 
       rownum 
FROM   all_views 
WHERE  view_name LIKE 'XX%'; 

STEP3: 

SELECT view_name trigger_name 
FROM   xxcns_clob_view aa 
WHERE  text LIKE '%\_TL%' ESCAPE '\' 
        OR text LIKE '%\_tl%' ESCAPE '\' 
        OR text LIKE '%FND_LOOKUP_VALUES%'
;