Thursday, November 17, 2022

Oracle PLQSL Script to convert Binary data (BLOB) to BASE64 encoded data (UTL_ENCODE.BASE64)

 







In this post, we have given a small script to convert BINARY data to BASE64 encoded data.  


Before seeing the script, lets have a quick background,  


Background: We have some binary data that we want to send across a network. Let's assume, we send them as bits and bytes over the network in a raw format.  


Possibility 1: Few combinations of Binary data may be considered as control characters by the intermediate hardware components. (modem). This will result Unexpected behaviour.  


Possibility 2: Binary data could be corrupted because the underlying protocol might think that you've entered a special character combination (like how FTP translates line endings). 


So, to avoid all these, people encode the binary data into characters. Base64 is one of these types of encodings. 

 

Script: 

CREATE OR REPLACE FUNCTION convert_blob_to_base64(pic_blob IN BLOB)
RETURN CLOB
IS
  lc_clob CLOB;
  ln_chunk_size PLS_INTEGER := 24000;
BEGIN
  FOR i IN 0..TRUNC((DBMS_LOB.GETLENGTH(pic_blob) - 1 ) / ln_chunk_size)
  LOOP
    lc_clob := lc_clob 
              || UTL_RAW.CAST_TO_VARCHAR2
	 	       ( UTL_ENCODE.BASE64_ENCODE
			        ( DBMS_LOB.SUBSTR( pic_blob,
                                       ln_chunk_size,
                                       i * ln_chunk_size + 1
                                     )
                    )
               );
  END LOOP;
  RETURN lc_clob;
END convert_blob_to_base64; 


Thursday, November 17, 2022 by Team search · 0

Saturday, November 12, 2022

API to terminate Employee in Oracle Apps R12 (HR_EX_EMPLOYEE_API.ACTUAL_TERMINATION_EMP, HR_EX_EMPLOYEE_API.UPDATE_TERM_DETAILS_EMP, HR_EX_EMPLOYEE_API.FINAL_PROCESS_EMP )

 









In this post we have given a script to end date an employee. Below three API's are used. 

API:

HR_EX_EMPLOYEE_API.ACTUAL_TERMINATION_EMP
HR_EX_EMPLOYEE_API.UPDATE_TERM_DETAILS_EMP
HR_EX_EMPLOYEE_API.FINAL_PROCESS_EMP 

TEST Instance: R12.1.3

Script:

SET SERVEROUTPUT ON; DECLARE l_obj NUMBER; l_period_of_service_id NUMBER; l_dod_warning BOOLEAN; l_supervisor_warning BOOLEAN; l_event_warning BOOLEAN; l_interview_warning BOOLEAN; l_review_warning BOOLEAN; l_recruiter_warning BOOLEAN; l_pay_proposal_warning BOOLEAN; l_entries_changed_warning VARCHAR2(30); l_asg_future_changes_warning BOOLEAN; l_org_now_no_manager_warning BOOLEAN; l_termination_date DATE := SYSDATE-5; l_last_std_process_date DATE := SYSDATE-5; l_final_process_date DATE := SYSDATE-5; ln_person_id NUMBER DEFAULT 39010; -- To be end dated BEGIN SELECT period_of_service_id ,object_version_number INTO l_period_of_service_id ,l_obj FROM per_periods_of_service WHERE person_id = ln_person_id AND actual_termination_date IS NULL; /*   -- HR_EX_EMPLOYEE_API.ACTUAL_TERMINATION_EMP populates the Leaving_Reason,   -- Actual_Termination_Date and Last_Standard_Process_Date   */ hr_ex_employee_api.actual_termination_emp (p_validate => FALSE ,p_effective_date => SYSDATE ,p_period_of_service_id => l_period_of_service_id ,p_object_version_number => l_obj ,p_actual_termination_date => l_termination_date ,p_last_standard_process_date => l_last_std_process_date -- OUT Variables ,p_supervisor_warning => l_supervisor_warning ,p_event_warning => l_event_warning ,p_interview_warning => l_interview_warning ,p_review_warning => l_review_warning ,p_recruiter_warning => l_recruiter_warning ,p_asg_future_changes_warning => l_asg_future_changes_warning ,p_entries_changed_warning => l_entries_changed_warning ,p_pay_proposal_warning => l_pay_proposal_warning ,p_dod_warning => l_dod_warning ); /*   -- HR_EX_EMPLOYEE_API.UPDATE_TERM_DETAILS_EMP populates Accepted_Termination_Date,   -- Notified_Termination_Date and Projected_Termination_Date   */ hr_ex_employee_api.update_term_details_emp (p_validate => FALSE ,p_effective_date => SYSDATE ,p_period_of_service_id => l_period_of_service_id ,p_object_version_number => l_obj ,p_accepted_termination_date => SYSDATE-5 ,p_notified_termination_date => SYSDATE-5 ,p_projected_termination_date => SYSDATE-5 ); COMMIT; DBMS_OUTPUT.PUT_LINE('Employee is terminated…'); /*   -- Update Final Process Date for Terminated Employee --   -- To Be executed only after the employee is terminated   -- and has an become EX-EMP   */ SELECT period_of_service_id, object_version_number, last_standard_process_date INTO l_period_of_service_id, l_obj, l_final_process_date FROM per_periods_of_service ppos WHERE period_of_service_id = ( SELECT MAX(period_of_service_id) FROM per_periods_of_service WHERE person_id = ppos.person_id AND actual_termination_date IS NOT NULL) AND person_id = ln_person_id AND final_process_date IS NULL; hr_ex_employee_api.final_process_emp (p_validate => FALSE ,p_period_of_service_id => l_period_of_service_id ,p_object_version_number => l_obj ,p_final_process_date => l_final_process_date -- OUT Variables ,p_org_now_no_manager_warning => l_org_now_no_manager_warning ,p_asg_future_changes_warning => l_asg_future_changes_warning ,p_entries_changed_warning => l_entries_changed_warning ); COMMIT; DBMS_OUTPUT.PUT_LINE('Final Process Date is updated for Terminated Employee…'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error while terminating Employee : '||SQLERRM); END; /

Testing:





Saturday, November 12, 2022 by Team search · 0

Thursday, October 20, 2022

Query to get AP Payments , Bank Statements, Internal Bank account and Vendor Details in Oracle apps R12

 







In this post, we have given the query which gives you the joins between AP payment tables, Bank statement tables, Internal bank Account details and vendor details.  


Tested instance: R12.2.4 


Tables Involved: 

  1.     CE_BANK_ACCOUNTS  

  1.     CE_BANK_ACCT_USES_ALL 

  1.     CE_BANK_BRANCHES_V    

  1.     AP_SYSTEM_PARAMETERS_ALL 

  1.     CE_PAYMENT_DOCUMENTS     

  1.     AP_LOOKUP_CODES      

  1.     AP_CHECKS_ALL       

  1.     IBY_PAYMENT_METHODS_VL   

  1.     IBY_PAYMENT_PROFILES     

  1.     FND_LOOKUPS              

  1.     FND_LOOKUPS              

  1.     AP_LOOKUP_CODES          

  1.     FND_DOCUMENT_SEQUENCES   

  1.     FND_DOC_SEQUENCE_CATEGORIES 

  1.     FND_TERRITORIES_VL          

  1.     GL_DAILY_CONVERSION_TYPES   

  1.     AP_SUPPLIERS                

  1.     AP_SUPPLIER_SITES_ALL       

  1.     CE_STATEMENT_RECONCILS_ALL  

  1.     CE_STATEMENT_HEADERS        

  1.     CE_STATEMENT_LINES 

  1.     GL_DAILY_CONVERSION_TYPES                 

  1.     AP_SUPPLIERS                

  1.     AP_SUPPLIER_SITES_ALL       

  1.     HZ_PARTY_SITES              

  1.     HZ_LOCATIONS                

  1.     HZ_PARTIES                

  1.  

Query:  

SELECT 
    cba.bank_account_id ,
    ac.bank_account_name,
    ac.bank_account_num,
    ac.bank_account_type,
    ac.bank_num,
    ac.base_amount,
    ac.address_line1,
    ac.address_line2,
    ac.address_line3,
    ac.address_line4,
    nvl(ac.address_style, 'DEFAULT') address_style,
    ac.amount,
    ac.checkrun_id,
    ac.checkrun_name,
    ac.check_date,
    ac.check_id,
    ac.check_number,
    pd.payment_document_id,
    ac.check_voucher_num,
    ac.city,
    ac.cleared_amount,
    ac.cleared_base_amount,
    ac.cleared_date,
    ac.cleared_exchange_date,
    ac.cleared_exchange_rate,
    ac.cleared_exchange_rate_type,
    ac.country,
    ac.county,
    ac.currency_code,
    ac.doc_category_code,
    ac.doc_sequence_id,
    ac.doc_sequence_value,
    ac.exchange_date,
    ac.exchange_rate,
    ac.exchange_rate_type,
    ac.future_pay_due_date,
    ac.org_id,
    ac.payment_method_code,
    ac.payment_type_flag,
    ac.province,
    ac.released_date,
    ac.released_by,
    ac.state,
    ac.status_lookup_code,
    ac.stopped_date,
    ac.stopped_by,
    ac.treasury_pay_date,
    ac.treasury_pay_number,
    ac.vendor_id,
    ac.vendor_name,
    ac.vendor_site_code,
    ac.vendor_site_id,
    ac.void_date,
    ac.withholding_status_lookup_code,
    ac.zip,
    ac.address_line1
    || DECODE(ac.address_line1, NULL, '', fnd_global.local_chr(10))
    || ac.address_line2
    || DECODE(ac.address_line2, NULL, '', fnd_global.local_chr(10))
    || ac.address_line3
    || DECODE(ac.address_line3, NULL, '', fnd_global.local_chr(10))
    || ac.city
    || ', '
    || ac.state
    || ' '
    || ac.zip
    || DECODE(ac.city, 
             NULL, DECODE(ac.state, 
                          NULL, DECODE(ac.zip, 
                                       NULL, '', fnd_global.local_chr(10)
                                      ), 
                          fnd_global.local_chr(10)
                         ), 
             fnd_global.local_chr(10)
             ) 
    || ac.country address,
    cbb.bank_name,
    DECODE(paycard_reference_id, 
           NULL, cba.bank_account_name, 
           ac.bank_account_name
          ),
    cba.currency_code ,
    aspa.set_of_books_id  ,
    pd.payment_document_name,
    alc1.displayed_field,
    iby1.payment_method_name ,
    alc3.displayed_field check_status,
    stopped_date stop_date,
    fds.name doc_sequence_name,
    fdsc.name doc_category_name,
    ft.territory_short_name,
    gdct.user_conversion_type user_rate_type,
    nvl(pv.vendor_name, 
        DECODE(ac.party_id, '', '*****', hzp.party_name)
        ) current_vendor_name,
    DECODE(ac.vendor_id, 
           '', DECODE(ac.party_id, '', '****', NULL), 
           pv.segment1
           )  vendor_number,
    DECODE(ac.vendor_id, 
          '', DECODE(ac.party_id, '', '***', NULL), 
          pv.num_1099
          )  num_1099,
    DECODE(ac.vendor_site_id, '', 
            DECODE(ac.party_id, '', '****', NULL), 
            DECODE(sign(ac.vendor_site_id), - 1, NULL, pvs.vendor_site_code)
          )  current_vendor_site_code,
    DECODE(sign(ac.vendor_site_id), - 1, 
           NULL, hzl.address1
                 || ' '
                 || hzl.address2
                 || ' '
                 || hzl.city
                 || ' '
                 || hzl.state
                 || ' '
                 || hzl.postal_code
         )  trading_partner_address,
    csh.statement_number ,
    csl.line_number statement_line_number,
    ap_auto_payment_pkg.selection_criteria_exists(ac.check_id) 
    selection_criteria_flag,
    ap_invoice_payments_pkg.get_max_gl_date(ac.check_id) max_payment_gl_date,
    ac.positive_pay_status_code,
    ac.transfer_priority,
    ac.external_bank_account_id, 
    ac.stamp_duty_amt,
    ac.stamp_duty_base_amt,
    ac.maturity_exchange_date,
    ac.maturity_exchange_rate_type,
    ac.maturity_exchange_rate,
    gdct1.user_conversion_type maturity_user_rate_type,
    ac.description,
    ac.anticipated_value_date,
    ac.actual_value_date,
    ap_checks_pkg.get_posting_status(ac.check_id),
    iby3.meaning bank_charge_bearer_dsp,
    iby5.meaning settlement_priority_dsp,
    ac.bank_charge_bearer,
    ac.settlement_priority,
    ac.party_id,
    ac.party_site_id,
    iby2.payment_profile_id,
    iby2.payment_profile_name,
    iby2.processing_type,
    ac.payment_id,
    ac.legal_entity_id,
    ac.void_check_id,
    ac.void_check_number,
    ac.ce_bank_acct_use_id,
    ac.remit_to_supplier_name remit_to_supplier_name,
    ac.remit_to_supplier_id remit_to_supplier_id,
    ac.remit_to_supplier_site remit_to_supplier_site,
    ac.remit_to_supplier_site_id  remit_to_supplier_site_id,
    pv1.segment1 remit_to_supplier_number,
    DECODE(sign(ac.remit_to_supplier_site_id),
           - 1, NULL, hzl1.address1
                      || ' '
                      || hzl1.address2
                      || ' '
                      || hzl1.city
                      || ' '
                      || hzl1.state
                      || ' '
                      || hzl1.postal_code
           ) remit_to_supplier_address,
    ac.relationship_id relationship_id, 
    ac.paycard_authorization_number,
    ac.paycard_reference_id,
    DECODE(ac.remit_to_supplier_id, 
           '', DECODE(pv1.party_id, '', 'NA', NULL), 
           pv1.num_1099
          ) remit_num_1099,
    DECODE(pv1.party_id, 
           '', 'NA', 
           hzp1.party_name
           ) current_remit_to_supplier_name,
    DECODE(ac.remit_to_supplier_site_id,
           '', DECODE(pv1.party_id, '', 'NA', NULL), 
           DECODE(sign(ac.remit_to_supplier_site_id), - 1,NULL, 
                  pvs1.vendor_site_code
                 )
          ) current_remit_vendor_site_code,
    ac.acknowledged_flag  acknowledged_flag
FROM
    ce_bank_accounts            cba,
    ce_bank_acct_uses_all       cbau,
    ce_bank_branches_v          cbb,
    ap_system_parameters_all    aspa,
    ce_payment_documents        pd,
    ap_lookup_codes             alc1,
    iby_payment_methods_vl      iby1,
    iby_payment_profiles        iby2,
    fnd_lookups                 iby3,
    fnd_lookups                 iby5,
    ap_lookup_codes             alc3,
    fnd_document_sequences      fds,
    fnd_doc_sequence_categories fdsc,
    fnd_territories_vl          ft,
    gl_daily_conversion_types   gdct,
    ap_suppliers                pv,
    ap_supplier_sites_all       pvs,
    ce_statement_reconcils_all  csr,
    ce_statement_headers        csh,
    ce_statement_lines          csl,
    ap_checks_all               ac,
    gl_daily_conversion_types   gdct1,
    hz_parties                  hzp,
    hz_party_sites              hps,
    hz_locations                hzl, 
    ap_suppliers                pv1,
    ap_supplier_sites_all       pvs1,
    hz_party_sites              hps1,
    hz_locations                hzl1, 
    hz_parties                  hzp1 
WHERE ac.ce_bank_acct_use_id    = cbau.bank_acct_use_id (+)
    AND cbau.bank_account_id    = cba.bank_account_id (+)
    AND cbau.org_id             = aspa.org_id (+)
    AND ac.maturity_exchange_rate_type = gdct1.conversion_type (+)
    AND cbb.branch_party_id (+) = cba.bank_branch_id
    AND ac.payment_document_id  = pd.payment_document_id (+)
    AND alc1.lookup_type        = 'PAYMENT TYPE'
    AND alc1.lookup_code        = ac.payment_type_flag
    AND iby1.payment_method_code (+) = ac.payment_method_code
    AND iby2.payment_profile_id (+) = ac.payment_profile_id
    AND alc3.lookup_type (+)    = 'CHECK STATE'
    AND alc3.lookup_code (+)    = ac.status_lookup_code
    AND ac.bank_charge_bearer   = iby3.lookup_code (+)
    AND iby3.lookup_type (+)    = 'IBY_BANK_CHARGE_BEARER'
    AND ac.settlement_priority  = iby5.lookup_code (+)
    AND iby5.lookup_type (+)    = 'IBY_SETTLEMENT_PRIORITY'
    AND ac.doc_sequence_id      = fds.doc_sequence_id (+)
    AND fdsc.application_id (+) = 200
    AND ac.doc_category_code    = fdsc.code (+)
    AND ac.country              = ft.territory_code (+)
    AND ac.exchange_rate_type   = gdct.conversion_type (+)
    AND ac.vendor_id            = pv.vendor_id (+)
    AND ac.party_id             = hzp.party_id (+)
    AND ac.vendor_site_id       = pvs.vendor_site_id (+)
    AND ac.party_site_id        = hps.party_site_id (+)
    AND hps.location_id         = hzl.location_id (+)
    AND csr.reference_type (+)  = 'PAYMENT'
    AND csr.reference_id (+)    = ac.check_id
    AND csr.current_record_flag (+) = 'Y'
    AND csr.statement_line_id   = csl.statement_line_id (+)
    AND csl.statement_header_id = csh.statement_header_id (+)
    AND csr.status_flag (+)     = 'M' 
    AND ac.remit_to_supplier_id = pv1.vendor_id (+)
    AND ac.remit_to_supplier_site_id = pvs1.vendor_site_id (+)
    AND pvs1.party_site_id      = hps1.party_site_id (+)
    AND hps1.location_id        = hzl1.location_id (+) 
    AND pv1.party_id            = hzp1.party_id (+) 
    AND ac.creation_date > SYSDATE - 50;

Thursday, October 20, 2022 by Team search · 1

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.