erpSchools

Category: API

  • XLA: Delete event XLA_EVENTS

    — Creted by Prudhvi </code> DECLARE p_event_source_info xla_events_pub_pkg.t_event_source_info; BEGIN p_event_source_info.source_application_id := NULL; –101 GL — 602 XLE p_event_source_info.application_id := 222; — AR p_event_source_info.legal_entity_id := 1234; p_event_source_info.ledger_id := 1; p_event_source_info.entity_type_code := ‘TRANSACTIONS’; p_event_source_info.transaction_number := NULL; p_event_source_info.source_id_int_1 := 1234; — Customer Transaction ID; p_event_source_info.source_id_int_2 := NULL; p_event_source_info.source_id_int_3 := NULL; p_event_source_info.source_id_int_4 := NULL; p_event_source_info.source_id_char_1 := NULL; p_event_source_info.source_id_char_2 :=…

  • API: Update event status code in XLA_EVENTS

    — Created by Prudhvi DECLARE p_event_source_info xla_events_pub_pkg.t_event_source_info; BEGIN p_event_source_info.source_application_id := NULL; p_event_source_info.application_id := 222; — AR p_event_source_info.legal_entity_id := 12340; — change it p_event_source_info.ledger_id := 1; — change it p_event_source_info.entity_type_code := ‘TRANSACTIONS’; p_event_source_info.transaction_number := ‘1234’; — change it p_event_source_info.source_id_int_1 := 1234; –Transaction ID; p_event_source_info.source_id_int_2 := NULL; p_event_source_info.source_id_int_3 := NULL; p_event_source_info.source_id_int_4 := NULL; p_event_source_info.source_id_char_1 := NULL; p_event_source_info.source_id_char_2…

  • OE_INF_POPULATE_PUB.populate_interface

    declare v_return_status varchar2(10); v_order_number_from number; v_order_source_id number; begin oe_debug_pub.debug_on; oe_debug_pub.initialize; oe_debug_pub.add(‘Executing API..’,1); oe_inf_populate_pub.populate_interface ( p_api_version_number => 1.0 , x_return_status => v_return_status , p_order_number_from => v_order_number_from , p_order_source_id => v_order_source_id ); oe_debug_pub.add(‘API Executed.’,1); end;

  • OZF_OFFER_PUB.process_modifiers

    SET serveroutput on; DECLARE — v_return_status VARCHAR2(1); –v_msg_count NUMBER; — v_msg_data VARCHAR2(20000); l_user_id NUMBER; l_resp_id NUMBER; l_api_version constant number := 1.0; l_qp_list_header_id NUMBER := 0; l_validation_level NUMBER := fnd_api.g_valid_level_none; l_return_status varchar2(1); l_msg_count number; l_msg_data varchar2(2000); l_error_location NUMBER := 0; l_modifier_list_rec OZF_Offer_Pub.modifier_list_rec_type; l_modifier_line_tbl OZF_Offer_Pub.modifier_line_tbl_type; l_qualifier_tbl OZF_Offer_Pub.qualifiers_tbl_type; l_qualifiers_tbl_out qp_qualifier_rules_pub.qualifiers_tbl_type; l_budget_tbl OZF_Offer_Pub.budget_tbl_type; l_act_product_tbl OZF_Offer_Pub.act_product_tbl_type; l_discount_tbl OZF_Offer_Pub.discount_line_tbl_type; l_excl_tbl OZF_Offer_Pub.excl_rec_tbl_type;…

  • OZF_FUNDS_PUB

    SET serveroutput on; DECLARE l_user_name VARCHAR2(100); l_user_id NUMBER; l_resource_id NUMBER; l_resp_id NUMBER; l_api_version NUMBER := 1.0; l_fund_id NUMBER; l_validation_level NUMBER := fnd_api.g_valid_level_none; l_return_status VARCHAR2(1); l_msg_count NUMBER; l_msg_data VARCHAR2(2000); l_pvt_fund_rec OZF_FUNDS_PUB.fund_rec_type; l_modifier_list_rec ozf_offer_pub.modifier_list_rec_type; l_modifier_line_tbl ozf_offer_pub.modifier_line_tbl_type; l_vo_pbh_tbl ozf_offer_pub.vo_disc_tbl_type; l_vo_dis_tbl ozf_offer_pub.vo_disc_tbl_type; l_vo_prod_tbl ozf_offer_pub.vo_prod_tbl_type; l_qualifier_tbl ozf_offer_pub.qualifiers_tbl_type; l_vo_mo_tbl ozf_offer_pub.vo_mo_tbl_type; BEGIN select resource_id into l_pvt_fund_rec.owner from JTF_RS_RESOURCE_EXTNS where user_id = 1177;…

  • OZF_FUND_UTILIZED_PVT.create_utilization

        SET serveroutput on; DECLARE l_user_name VARCHAR2(100) ; l_user_id NUMBER; l_resp_id NUMBER; l_adj_rec OZF_FUND_UTILIZED_PVT.utilization_rec_type; l_return_status VARCHAR2(1); l_msg_count NUMBER; l_msg_data VARCHAR2(2000); l_utilization_id NUMBER; BEGIN BEGIN select responsibility_id into l_resp_id from fnd_responsibility_vl where responsibility_key = ‘OZF_USER’; EXCEPTION WHEN OTHERS THEN l_resp_id := NULL; END; FND_GLOBAL.apps_initialize (user_id => 1234 — change this value , resp_id => l_resp_id,…

  • DBMS_JOB API Example

    DBMS_JOB API example to kill DB session of a concurrent request: The DBMS_JOB package schedules and manages jobs in the job queue. Main Procedures that will be used: DBMS_JOB.SUBMIT submits the job to the job queue. You must issue a COMMIT statement immediately after the statement. Parameters: job: Number of the job being run. what:…

  • API: OE_OE_TOTALS_SUMMARY to get Sales Order’s Subtotal, discounts and tax details

    OE_OE_TOTALS_SUMMARY.ORDER_TOTALS can be used to derive a sales order’s subtotal, discount, tax and charges. Below is the code snippet on how to use the API DECLARE v_HEADER_ID NUMBER; v_SUBTOTAL NUMBER; v_DISCOUNT NUMBER; v_CHARGES NUMBER; v_TAX NUMBER; BEGIN v_HEADER_ID := 5908; OE_OE_TOTALS_SUMMARY.ORDER_TOTALS( P_HEADER_ID => v_HEADER_ID, P_SUBTOTAL => v_SUBTOTAL, P_DISCOUNT => v_DISCOUNT, P_CHARGES => v_CHARGES, P_TAX => V_TAX ); DBMS_OUTPUT.PUT_LINE(‘SUBTOTAL =…

  • API: QP_PRICE_LIST_PUB Example To create and update Price Lists

    QP_PRICE_LIST_PUB example To Create a New Price List: DECLARE v_return_status VARCHAR2(1) := NULL; v_msg_count NUMBER := 0; v_msg_data VARCHAR2 (2000); v_price_list_rec qp_price_list_pub.price_list_rec_type; v_price_list_val_rec qp_price_list_pub.price_list_val_rec_type; v_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type; v_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type; v_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type; v_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type; v_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type; v_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type; ppr_price_list_rec qp_price_list_pub.price_list_rec_type; ppr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type; ppr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type; ppr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type; ppr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type; ppr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type; ppr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type; ppr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type; –apps initilization variables…

  • API: DBMS_CRYPTO: encrypt and decrypt information

    To Encrypt or decrypt, we will need a key. This key will be stored in a table which can be accessed only by the authorized personnel. In the below examples, the key has been hardcoded. dbms_crypto example to encrypt information Declare l_key varchar2(2000) := ‘1234567890123456’; l_mod number := dbms_crypto.ENCRYPT_AES128 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5; l_enc raw…

  • DBMS_XMLSTORE: Insert, Update, Delete XML data

    Overview: DBMS_XMLSTORE API / Package enables DML operations to be performed on relational tables using XML data. Steps to use DBMS_XMLSTORE: 1. Create a context handle by calling function DBMS_XMLSTORE.newContext and supplying it with the table name to use for the DML operations. For case sensitivity, double-quote (“) the string that is passed to the…

  • OE_ORDER_PUB: Cancel Sales Order Line

    set serveroutput on; DECLARE v_api_version_number NUMBER := 1; v_return_status VARCHAR2 (2000); v_msg_count NUMBER; v_msg_data VARCHAR2 (2000); l_debug_level NUMBER := 1 — IN Variables — v_header_rec oe_order_pub.header_rec_type; v_line_tbl oe_order_pub.line_tbl_type; v_action_request_tbl oe_order_pub.request_tbl_type; v_line_adj_tbl oe_order_pub.line_adj_tbl_type; — OUT Variables — v_header_rec_out oe_order_pub.header_rec_type; v_header_val_rec_out oe_order_pub.header_val_rec_type; v_header_adj_tbl_out oe_order_pub.header_adj_tbl_type; v_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type; v_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type; v_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type; v_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type; v_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type; v_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type; v_line_tbl_out…

  • OE_ORDER_PUB: Book existing Sales Order

    set serveroutput on; DECLARE l_api_version_number NUMBER := 1; l_return_status VARCHAR2(2000); l_msg_count NUMBER; l_msg_data VARCHAR2(2000); l_debug_level NUMBER := 1; — OM DEBUG LEVEL (MAX 5) — IN Variables — l_header_rec oe_order_pub.header_rec_type; l_line_tbl oe_order_pub.line_tbl_type; l_action_request_tbl oe_order_pub.Request_Tbl_Type; — OUT Variables — l_header_rec_out oe_order_pub.header_rec_type; l_header_val_rec_out oe_order_pub.header_val_rec_type; l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type; l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type; l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type; l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type; l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type; l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type; l_header_scredit_val_tbl_out…

  • OE_ORDER_PUB: Add lines to existing Sales Order

    OE_ORDER_PUB: Add lines to existing Sales Order set serveroutput on; DECLARE l_api_version_number NUMBER := 1; l_return_status VARCHAR2(2000); l_msg_count NUMBER; l_msg_data VARCHAR2(2000); l_debug_level NUMBER := 1; — OM DEBUG LEVEL (MAX 5) — IN Variables — l_header_rec oe_order_pub.header_rec_type; l_line_tbl oe_order_pub.line_tbl_type; l_action_request_tbl oe_order_pub.Request_Tbl_Type; — OUT Variables — l_header_rec_out oe_order_pub.header_rec_type; l_header_val_rec_out oe_order_pub.header_val_rec_type; l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type; l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type; l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;…

  • OE_ORDER_PUB: Create and Book Sales Order

    Create and Book Sales Order set serveroutput on; DECLARE l_api_version_number NUMBER := 1; l_return_status VARCHAR2(2000); l_msg_count NUMBER; l_msg_data VARCHAR2(2000); l_debug_level NUMBER := 1; — OM DEBUG LEVEL (MAX 5) — IN Variables — l_header_rec oe_order_pub.header_rec_type; l_line_tbl oe_order_pub.line_tbl_type; l_action_request_tbl oe_order_pub.Request_Tbl_Type; — OUT Variables — l_header_rec_out oe_order_pub.header_rec_type; l_header_val_rec_out oe_order_pub.header_val_rec_type; l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type; l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type; l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type; l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;…

  • OE_ORDER_PUB: Create Sales Order in Entered Status

    set serveroutput on; DECLARE l_api_version_number NUMBER := 1; l_return_status VARCHAR2(2000); l_msg_count NUMBER; l_msg_data VARCHAR2(2000); l_debug_level NUMBER := 1; — OM DEBUG LEVEL (MAX 5) — IN Variables — l_header_rec oe_order_pub.header_rec_type; l_line_tbl oe_order_pub.line_tbl_type; l_action_request_tbl oe_order_pub.Request_Tbl_Type; — OUT Variables — l_header_rec_out oe_order_pub.header_rec_type; l_header_val_rec_out oe_order_pub.header_val_rec_type; l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type; l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type; l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type; l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type; l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type; l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type; l_header_scredit_val_tbl_out…

  • API: FND_USER_PKG: Create User,reset Password and add Responsibilities

    1) API to create FND User BEGIN fnd_user_pkg.CreateUser ( x_user_name => ‘Operations’, x_owner => NULL, x_unencrypted_password =>’welcome1′, x_start_date => TO_DATE(’01-JAN-2000′), x_end_date => NULL, x_last_logon_date => NULL, x_description => ‘Operations User’, x_password_date => NULL, x_password_accesses_left => NULL, x_employee_id => NULL, x_email_address => NULL, x_fax => NULL, x_customer_id => NULL, x_supplier_id => NULL); COMMIT; END; 2) API…

  • API: Concurrent Program and Request Group related

    1) API to create concurrent program executable BEGIN fnd_program.executable( ‘Test User Executable’ –User Executable Name ,’System Administration’ –Application Name ,’TEST_SHORT_EXEC’ –Short Name ,’Test Executable Creation’ –Description ,’PL/SQL Stored Procedure’ –Execution Method ,’test_pkg.test_proc’ ,” — subroutine_name ,” –icon_name ,’US’ –language_code ,” –execution file path ); COMMIT; END; 2) API to create or register concurrent program for…

  • API: ARP_NOTES_PKG

      API: ARP_NOTES_PKG.insert_p to create new AR Notes DECLARE v_notes_rec ar_notes%rowtype; begin –Set the org_id for apps 11i –fnd_client_info.set_org_context(‘204’); — set org_id for apps R12 mo_global.init(‘AR’); mo_global.set_policy_context(‘S’,’204′); — to initialize the variables v_notes_rec.note_type := ‘MAINTAIN’; v_notes_rec.customer_trx_id := 759812; v_notes_rec.text := ‘AR Notes API Demo form erpSchools.com’; v_notes_rec.customer_call_topic_id := NULL; v_notes_rec.call_action_id := NULL; v_notes_rec.customer_call_id := NULL;…

  • Ship Confirm through API

    Use this script to create a procedure in Database and call the procedure by passing the delivery number as a parameter to ship confirm it. You can set the options for 1. Backordering unspecified quantities 2. Closing the delivery automatically by submitting the Trip stop program after ship confirm is successfull SHIP CONFIRMATION THROUGH API…

  • Trading Community Architecture (TCA) API

    Overview: Trading Community Architecture (TCA) is an architecture concept designed to support complex trading communities. This document provides information about how to create a customer using TCA API. These APIs utilize the new TCA model, inserting directly to the HZ tables. Architecture Create Organization DECLARE p_organization_rec   hz_party_v2pub.organization_rec_type; x_return_status      VARCHAR2 (2000); x_msg_count          NUMBER; x_msg_data           VARCHAR2 (2000); x_party_id           NUMBER; x_party_number       VARCHAR2 (2000); x_profile_id         NUMBER; BEGIN p_organization_rec.organization_name := ‘erpschools’;…