Query to find Responsibility for the Concurrent Program

 

/*Query to find the Concurrent Program attached to Responsibility*/

SELECT frt.responsibility_name,
       frg.request_group_name,
       frg.description
FROM   fnd_request_groups frg,
       fnd_request_group_units frgu,
       fnd_concurrent_programs fcp,
       fnd_concurrent_programs_tl fcpt,
       fnd_responsibility_tl frt,
       fnd_responsibility frs
WHERE  frgu.unit_application_id = fcp.application_id
       AND frgu.request_unit_id = fcp.concurrent_program_id
       AND frg.request_group_id = frgu.request_group_id
       AND frg.application_id = frgu.application_id
       AND fcpt.source_lang = Userenv('LANG')
       AND fcp.application_id = fcpt.application_id
       AND fcp.concurrent_program_id = fcpt.concurrent_program_id
       AND frs.application_id = frt.application_id
       AND frs.responsibility_id = frt.responsibility_id
       AND frt.source_lang = Userenv('LANG')
       AND frs.request_group_id = frg.request_group_id
       AND frs.application_id = frg.application_id
       --AND   fcp.concurrent_program_name = 
       AND fcpt.user_concurrent_program_name LIKE '--------------------'; 

API FOR TRANSFER TO MANUFACTURING IN ORACLE APPS R12

 /*API FOR TRANSFER TO MANUFACTURING IN ORACLE APPS R12*/

DECLARE
    l_inventory_item_id NUMBER := 11081350;
    l_org_id            NUMBER := 319;
    l_resp_id           NUMBER;
    l_appl_id           NUMBER;
BEGIN
    SELECT responsibility_id,
           application_id
    INTO   l_resp_id, l_appl_id
    FROM   fnd_responsibility
    WHERE  responsibility_key = '-------'; --'Transfer to Manufacturing';

    --initialize user session
    fnd_global.Apps_initialize(user_id=>fnd_global.user_id,resp_id=>l_resp_id,resp_appl_id=>l_appl_id);

    eng_bom_rtg_transfer_pkg.Eng_bom_rtg_transfer 

    (x_org_id => l_org_id,

    x_eng_item_id => l_inventory_item_id, 

    x_mfg_item_id => l_inventory_item_id,
    x_transfer_option => 2, 

    x_designator_option => 1,
    x_alt_bom_designator => NULL,
    x_alt_rtg_designator => NULL, 

    x_effectivity_date => SYSDATE,
    x_last_login_id => -1, 

    x_bom_rev_starting => NULL,
    x_rtg_rev_starting => NULL,
    x_ecn_name => NULL, --'ABC.ABC_11377_121',
    x_item_code => 1, 

    x_bom_code => 1, 

    x_rtg_code => 1,
    x_mfg_description => NULL,
    x_segment1 => NULL, 

    x_segment2 => NULL, 

    x_segment3 => NULL,
    x_segment4 => NULL,
    x_segment5 => NULL, 

    x_segment6 => NULL, 

    x_segment7 => NULL,
    x_segment8 => NULL,
    x_segment9 => NULL, 

    x_segment10 => NULL, 

    x_segment11 => NULL,
    x_segment12 => NULL, 

    x_segment13 => NULL, 

    x_segment14 => NULL,
    x_segment15 => NULL, 

    x_segment16 => NULL, 

    x_segment17 => NULL,
    x_segment18 => NULL, 

    x_segment19 => NULL, 

    x_segment20 => NULL,
    x_implemented_only => 1, 

    x_unit_number => NULL, 

    x_commit => TRUE);
END; 

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;