DML to update Sales Order Type After Booking Sales Order in Oracle APPS R12

 /*DML to update Sales Order Type After Booking Sales Order in Oracle APPS R12*/

UPDATE oe_order_headers_all
SET    order_type_id = 9999
WHERE  order_number = <--SO_NUMBER-->;

UPDATE wsh_delivery_details wdd
SET    source_header_type_id = 9999,

       source_header_type_name = '---SO_TYPE_NAME---'
WHERE  source_header_number = '<--SO_NUMBER-->';

UPDATE mtl_sales_orders
SET    segment2 = '---SO_TYPE_NAME---'

WHERE  segment1 = '<--SO_NUMBER-->';

/*Sales Order Type Name & Id*/

SELECT transaction_type_id,
       name
FROM   wsh_transaction_types_vl wtt ;

BASE 64 in Oracle APPS R12

 In computer programming, Base64 is a group of binary-to-text encoding schemes that transforms binary data into a sequence of printable characters, limited to a set of 64 unique characters. 

CREATE TABLE xx_blob_base64
  (
     xid   NUMBER,
     xfile BLOB
  ); 

Upload a PDF file over XFILE column of table 'xx_blob_base64':

Function to convert BLOB to BASE64:

CREATE FUNCTION Xx_getbase64(p_source BLOB) RETURN CLOB IS
  v_result CLOB;
BEGIN
    dbms_lob.Createtemporary(lob_loc => v_result, CACHE => FALSE, dur => 0);

    wf_mail_util.Encodeblob (p_source, v_result);

    RETURN ( v_result );
END xx_getbase64; 

Now, get the BASE64 :

SELECT Xx_getbase64(xfile)
FROM   xxcns_blob_base64; 

Save this data into TEXT file.

Can use any decode tool (For Ex, online tool https://base64.guru/converter/decode/file) to decode this file into PDF.

API to recreate TAXEs in Entere stage Sale Order in Oracel APPS R12

 

SELECT ooh.*
FROM   oe_order_headers_all ooh
WHERE  org_id = 862
AND    flow_status_code = 'ENTERED'
AND    header_id NOT IN
       (
              SELECT trx_id
              FROM   jai_tax_lines
              WHERE  1 = 1 --trx_id = l_hdr_id
              AND    entity_code = 'OE_ORDER_HEADERS'
              AND    event_class_code = 'SALES_TRANSACTION_TAX_QUOTE'
              AND    event_type_code = 'CREATE'
              AND    application_id = 660
              AND    org_id = 862);

DECLARE
  CURSOR c1 IS
    SELECT 9391680 header_id
    FROM   dual
    UNION
    SELECT 9393542
    FROM   dual
    UNION
    SELECT 9395854
    FROM   dual
    UNION
    . . .. 

;

BEGIN
    FOR c IN c1 LOOP
        Xx_recreate_tax_factors(c.header_id);
        dbms_output.Put_line(c.header_id);
    END LOOP;
    dbms_output.Put_line('Done!');
EXCEPTION
    WHEN OTHERS THEN
      dbms_output.Put_line('Error :'||SQLERRM);
END; 

CREATE OR replace PROCEDURE xx_recreate_tax_factors(p_hdr_id NUMBER) IS

  l_return_code    VARCHAR2(100);
  l_return_message VARCHAR2(2000);
  l_event_class_rec zx_api_pub.event_class_rec_type;
  l_lines oe_order_lines_all%ROWTYPE;
BEGIN
  fnd_global.Apps_initialize(0,59480,660);
  FOR c1 IN
  (
         SELECT A.*,
                B.legal_entity,
                B.set_of_books_id
         FROM   oe_order_lines_all A,
                org_organization_definitions B
         WHERE  A.ship_from_org_id=B.organization_id
         AND    A.header_id=p_hdr_id
         AND    A.flow_status_code IN ('AWAITING_SHIPPING',
                                       'BOOKED',
                                       'AWAITING_RETURN',
                                       'ENTERED')
  )
  LOOP
    DELETE
    FROM   jai_tax_det_factors
    WHERE  trx_id=c1.header_id
    AND    trx_line_id=c1.line_id
    AND    application_id=660;
    
    DELETE
    FROM   jai_tax_lines
    WHERE  trx_id=c1.header_id
    AND    trx_line_id=c1.line_id
    AND    application_id=660;
    
    COMMIT;
    l_event_class_rec.application_id :=660;
    l_event_class_rec.event_class_code :='SALES_TRANSACTION_TAX_QUOTE';
    l_event_class_rec.event_type_code :='CREATE';
    l_event_class_rec.entity_code:='OE_ORDER_HEADERS';
    l_event_class_rec.trx_id:=c1.header_id;
    l_event_class_rec.trx_date:=SYSDATE;
    l_event_class_rec.tax_event_class_code:='SALES_TRANSACTION';
    l_event_class_rec.tax_event_type_code:='CREATE';
    l_event_class_rec.internal_organization_id :=c1.org_id;
    l_event_class_rec.legal_entity_id:=c1.legal_entity;
    l_event_class_rec.ledger_id:=c1.set_of_books_id;
    jai_om_tax_det_pkg.Determine_factors (p_action_code =>'CREATE',
                                          p_line_id =>c1.line_id,
                                          p_event_class_rec =>l_event_class_rec,
                                          x_return_code =>l_return_code,
                                          x_return_message =>l_return_message,
                                          p_call_from =>'API' ) ;
  END LOOP;
  COMMIT;
END;