API - Customer Additional Information (Price List , Payment Terms ,Sales Type )

CREATE OR REPLACE procedure APPS.XX_CUSTOMER_ADDITIONAL_LOAD IS p_cust_site_use_rec HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE; x_return_status VARCHAR2(2000); x_msg_count NUMBER; xio_p_object_version NUMBER; x_msg_data VARCHAR2(2000); v_site_use_id NUMBER; V_OBJECT_VERSION NUMBER; BEGIN -FND_GLOBAL.APPS_INITIALIZE(<user_id>,<resp_id>,<resp_applic arion_id>); -MO_GLOBAL.INIT('AR'); -MO_GLOBAL.SET_POLICY_CONTEXT('S', <org_id>); FND_GLOBAL.APPS_INITIALIZE(1535,50930,222); MO_GLOBAL.INIT('AR'); MO_GLOBAL.SET_POLICY_CONTEXT('S', 1210); BEGIN HCSU.SITE_USE_ID, HCSU.OBJECT_VERSION_NUMBER INTO V_SITE_USE_ID, V_OBJECT_VERSION FROM HZ_PARTIES HP, HZ_PARTY_SITES HPS, HZ_CUST_ACCT_SITES_ALL HCAS, HZ_CUST_SITE_USES_ALL HCSU WHERE HP.PARTY_ID = HPS.PARTY_ID AND HPS.PARTY_SITE_ID = HCAS.PARTY_SITE_ID AND HCAS.CUST_ACCT_SITE_ID =HCSU.CUST_ACCT_SITE_ID AND HCSU.SITE_USE_CODE = 'BILL_TO' AND ltrim(rtrim(upper(HP.PARTY_NAME)))= ltrim(rtrim(upper('SSE Test Customer'))); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR IS'|| SUBSTR(SQLERRM,1,150)); END; SELECT

p_cust_site_use_rec.site_use_id:= -- Site USe to be updated xio_p_object_version := --xio_p_object_version := 1;

V_SITE_USE_ID; V_OBJECT_VERSION;

p_cust_site_use_rec.price_list_id := 98202; -- SSE Standard p_cust_site_use_rec.payment_term_id := 1000 ; -- 90 Days p_cust_site_use_rec.order_type_id := 1193; --SSE Duabi Showroom Cash Sales hz_cust_account_site_v2pub.update_cust_site_use( 'T', p_cust_ site_use_rec, xio_p_o bject_version, x_retur n_status, x_msg_c ount, x_msg_d ata); dbms_output.put_line('***************************'); dbms_output.put_line('Output information ....'); dbms_output.put_line('x_return_status: '|| x_return_status); dbms_output.put_line('x_msg_count: '||x_msg_count); dbms_output.put_line('xio_p_object_version: '|| xio_p_object_version); dbms_output.put_line('x_msg_data: '||x_msg_data); dbms_output.put_line('***************************'); COMMIT; END; /
Posted by Sarfaraz at 12:04 PM 0 comments Email This BlogThis! Share to Twitter Share to Facebook Share to Google Buzz Labels: Receivables (AR)

Reactions:

API - User Security Attribute Creation

declare x_return_status VARCHAR2(2000); x_msg_count NUMBER; x_msg_data VARCHAR2(2000); x_vendor_site_id number; begin icx_user_sec_attr_pub.create_user_sec_attr( p_api_version_number => 1, p_return_status => x_return_status, p_msg_count => x_msg_count, p_msg_data => x_msg_data, p_web_user_id => 1394, p_attribute_code => 'ICX_SUPPLIER_ORG_ID', p_attribute_appl_id => 177, ----iSupplier Portal p_varchar2_value => '', p_date_value => '', p_number_value => 493, -- Vendor_ID for ICX_SUPPLIER_ORG_ID'and Vendor_site_id for ICX_SUPPLIER_SITE_ID' attribute code p_created_by => -1, p_creation_date => sysdate, p_last_updated_by => -1, p_last_update_date => sysdate, p_last_update_login => -1); if (x_return_status <> 'S') then dbms_output.put_line('Encountered ERROR in Attribute Creation!!!'); dbms_output.put_line('-------------------------------------'); dbms_output.put_line(x_msg_data); IF x_msg_count > 1 THEN FOR i IN 1..x_msg_count LOOP dbms_output.put_line(substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255)); END LOOP; END IF; ELSE dbms_output.put_line('Attribute has been attached !!!!!!');

end if; end;

Posted by Sarfaraz at 11:56 AM 0 comments Email This BlogThis! Share to Twitter Share to Facebook Share to Google Buzz Labels: SYS ADMIN

Reactions:

API - Inventory Item Category Combination Creation

CREATE OR REPLACE PROCEDURE APPS.xx_create_new_item_category IS l_category_rec inv_item_category_pub.category_rec_type; /* Query to check results SELECT M1.SEGMENT1||'.'||M1.SEGMENT2||'.'|| M1.SEGMENT3||'.'||M1.SEGMENT4||'.'||M1.SEGMENT5||'.'|| M1.SEGMENT6 FROM MTL_CATEGORIES_B M1, MTL_CATEGORIES_TL M2 WHERE M1.CATEGORY_ID = M2.CATEGORY_ID AND M2.LANGUAGE = 'US' Table Structure CREATE TABLE XX_CREATE_NEW_CATEGORY ( STRUCTURE_ID NUMBER, SEGMENT1 VARCHAR2(2000 BYTE), SEGMENT2 VARCHAR2(2000 BYTE), SEGMENT3 VARCHAR2(2000 BYTE), SEGMENT4 VARCHAR2(2000 BYTE), SEGMENT5 VARCHAR2(2000 BYTE), SEGMENT6 VARCHAR2(2000 BYTE) ) */ CURSOR c_cat IS SELECT * FROM xx_create_new_category; l_api_version o_return_status o_msg_count o_msg_data NUMBER; VARCHAR2 (2000); NUMBER; VARCHAR2 (2000);

o_errorcode l_error_code l_error_desc l_conv_status v_category_id

VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 NUMBER;

(2000); (2000); (2000); (2000);

BEGIN fnd_global.apps_initialize (1130, 50634, 401); FOR i IN c_cat LOOP l_category_rec := NULL; l_category_rec.structure_id := i.structure_id; -inventory super user > setup > items > categories > Query The Category Set > get the Struture id from Examine -l_category_rec.structure_code := g_cat_flex_code; l_category_rec.summary_flag := 'N'; l_category_rec.enabled_flag := 'Y'; l_category_rec.segment1 := TO_CHAR (i.segment1); l_category_rec.segment2 := TO_CHAR (i.segment2); l_category_rec.segment3 := TO_CHAR (i.segment3); l_category_rec.segment4 := TO_CHAR (i.segment4); l_category_rec.segment5 := TO_CHAR (i.segment5); l_category_rec.segment6 := TO_CHAR (i.segment6); --- After the category record is loaded, then call the create_category api to -- create the new mtl_categories record. inv_item_category_pub.create_category (p_api_version => 1.0, --l_api_version, p_init_msg_list => fnd_api.g_false, p_commit => fnd_api.g_true, x_return_status => o_return_status, x_errorcode => o_errorcode, x_msg_count => o_msg_count, x_msg_data => o_msg_data,

p_category_rec => l_category_rec, x_category_id => v_category_id ); --l_category_rec.category_id); IF o_return_status = 'S' THEN DBMS_OUTPUT.put_line ('*****************'); DBMS_OUTPUT.put_line ( 'Category successfully created. New Category Id = '|| v_category_id); DBMS_OUTPUT.put_line ( 'Segment Combination = '|| i.segment1|| '.'|| i.segment2|| '.'|| i.segment3|| '.'|| i.segment4|| '.'|| i.segment5|| '.'|| i.segment6); DBMS_OUTPUT.put_line ('*****************'); ELSE l_error_code := NVL (l_error_code, '') || 'CATCONV005,'; l_error_desc := NVL (l_error_desc, '') || '#API Error while creating Category'; l_conv_status := 'ERROR'; DBMS_OUTPUT.put_line ('API STATUS : ' || o_return_status); DBMS_OUTPUT.put_line ( 'Segment Combination = '|| i.segment1|| '.'|| i.segment2|| '.'|| i.segment3|| '.'|| i.segment4|| '.'|| i.segment5 || '.'|| i.segment6|| SQLERRM); IF o_msg_count > 0 THEN DBMS_OUTPUT.put_line (o_errorcode); DBMS_OUTPUT.put_line (o_msg_data); FOR i IN 1 .. o_msg_count LOOP DBMS_OUTPUT.put_line ( i|| '.'|| SUBSTR(fnd_msg_pub.get (p_encoded => fnd_api.g_false),1,255)); END LOOP; END IF; END IF; END LOOP; END; /

begin xx_create_new_item_category; end;

Posted by Sarfaraz at 11:53 AM 0 comments Email This BlogThis! Share to Twitter Share to Facebook Share to Google Buzz Labels: API - Inventory Item Category Combination Creation, Inventory (INV)

Reactions:
THURSDAY, OCTOBER 21, 2010

Setting Org Context

Setting the Multi Org Context :

METHOD 1: begin MO_GLOBAL.SET_POLICY_CONTEXT(ACCESS_MODE,ORG_ID); end; Example: begin MO_GLOBAL.SET_POLICY_CONTEXT('S',101); end; S - Denotes that the current session will work for Single Org_id (101) M - Denotes that the current session will work for Multiple Org_id METHOD 2: begin mo_global.init (<APPLICATION SHORT NAME>); end; Example : begin

mo_global.init ('AR'); end; Query : select Application_short_name , application_name from fnd_application fapp, fnd_application_tl fappt where fapp.APPLICATION_ID = fappt.application_id and fappt.language = 'US' and application_name = 'General Ledger' ----------------------------------------------------------------

Setting the Application Context :

METHOD 1: begin fnd_global.apps_initialize(p_user_id, p_resp_id, p_resp_appl_id); end; Example : begin fnd_global.APPS_INITIALIZE(200131258,20004,51710); end; begin fnd_global.APPS_INITIALIZE (user_id => 200131258, resp_id => 20004, resp_appl_id => 51710 ); end; Query to find resp_is , resp_appl_id and user_id select responsibility_id ,application_id ,responsibility_name from fnd_responsibility_tl where upper(responsibility_name) IN ( upper('Receivables Manager'), upper('Application Developer' ) ) and language = 'US';

select user_id from fnd_user where upper(user_name) = 'SAIF'; SELECT fnd_profile.value (RESP_ID) FROM dual SELECT fnd_profile.value (USER_ID) FROM dual SELECT fnd_profile.value (APPLICATION_ID) FROM dual SELECT TO_NUMBER (FND_PROFILE.VALUE( LOGIN_ID )) FROM dual SELECT FND_PROFILE.VALUE(ORG_ID) FROM dual SELECT FND_PROFILE.VALUE(SO_ORGANIZATION_ID) FROM dual SELECT FND_PROFILE.VALUE(USERNAME) FROM dual SELECT FND_PROFILE.VALUE(GL_SET_OF_BKS_ID) FROM dual METHOD 2 : begin dbms_application_info.set_client_info('<org id>'); end; Example begin dbms_application_info.set_client_info('101'); end;
Posted by Sarfaraz at 12:50 AM 0 comments Email This BlogThis! Share to Twitter Share to Facebook Share to Google Buzz Labels: Setting Org Context

Reactions:
TUESDAY, OCTOBER 19, 2010

Open Interface - GL Interface

CREATE OR REPLACE PROCEDURE XX_GL_INTERFACE IS BEGIN

/* SELECT * FROM GL_INTERFACE MANDATORY FIELDS STATUS ACCOUNTING_DATE CURRENCY_CODE DATE_CREATED CREATED_BY ACTUAL_FLAG USER_JE_CATEGORY_NAME USER_JE_SOURCE_NAME */ -GL > JOURNALS > IMPORT > RUN ,

INSERT INTO GL_INTERFACE ( LEDGER_ID STATUS , SET_OF_BOOKS_ID , USER_JE_SOURCE_NAME , USER_JE_CATEGORY_NAME , ACCOUNTING_DATE , CURRENCY_CODE , DATE_CREATED , CREATED_BY , ACTUAL_FLAG , -, -, USER_CURRENCY_CONVERSION_TYPE , CURRENCY_CONVERSION_DATE , CURRENCY_CONVERSION_RATE , BUDGET_VERSION_ID ENCUMBRANCE_TYPE_ID

SEGMENT1 , SEGMENT2 , SEGMENT3 , SEGMENT4 , SEGMENT5 , SEGMENT6 SEGMENT7 SEGMENT8 SEGMENT9 , ENTERED_DR , ENTERED_CR , ACCOUNTED_DR , ACCOUNTED_CR , PERIOD_NAME , REFERENCE1 , REFERENCE2 , REFERENCE4 REFERENCE5 ) values ( 2021 SELECT * FROM GL_SETS_OF_BOOKS 'Y' i.STATUS , -, -, , , ,

2021 , -SELECT * FROM GL_SETS_OF_BOOKS (Trading Companies SOB) 'Manual' , -SELECT * FROM GL_JE_SOURCES WHERE JE_SOURCE_NAME LIKE 'Manual' 'SSE Manual' , -SELECT USER_JE_CATEGORY_NAME FROM GL_JE_CATEGORIES WHERE

USER_JE_CATEGORY_NAME LIKE 'SSE%' SYSDATE , -i.ACCOUNTING_DATE 'AED' , -i.CURRENCY_CODE sysdate , -DATE_CREATED 1090 , -fnd_global.user_id 'A' , -i.ACTUAL_FLAG -- A Actual , B - Budget E - Encumbrance -- i.ENCUMBRANCE_TYPE_ID , -, '' i.USER_CURRENCY_CONVERSION_TYPE '' i.CURRENCY_CONVERSION_DATE '' i.CURRENCY_CONVERSION_RATE '02' SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT1 '01' SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT2 '01' SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT3 , -, -, -, -= 02 , = , = -01 -01 i.BUDGET_VERSION_ID

'05' , -SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT4 = 05 '00' , -SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT5 = 00 '00' , -SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT6 = 00 '01' , -SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT7 = 01 '981100' , -SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT8 = 981100 '00' , -SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT9 = 00

2300 i.ENTERED_DR 2300 i.ENTERED_CR 2300 i.ACCOUNTED_DR 2300 i.ACCOUNTED_CR i.PERIOD_NAME OPEN ) i.REFERENCE1 'JUN-10' (PERIOD SHOULD BE SYSDATE i.REFERENCE2 i.REFERENCE4 i.REFERENCE5 ); END; begin XX_GL_INTERFACE; end;
Posted by Sarfaraz at 9:39 AM 0 comments Email This BlogThis! Share to Twitter Share to Facebook Share to Google Buzz Labels: General Ledger (GL)

, -, -, -, -, -, --

'INSERTED BY CUSTOM GL INT' , -'INSERT' ( REFERENCE4 it takes in JE NAME 'SSE' , -) --

Reactions:

API - Allocate and Transact Orders (Transact Move Orders)

create or replace procedure XX_ALOCATE_TRANSACT_MOVE_ORDER (P_REQUEST_NUMBER VARCHAR2 ,P_ORGANIZATION_ID NUMBER,P_TRANSACTION_DATE varchar2) as ----------------- ALLOCATE MOVE ORDER API REQUIREMENTS ------------------------l_api_version NUMBER := 1.0; l_init_msg_list VARCHAR2 (2) := fnd_api.g_true; l_return_values VARCHAR2 (2) := fnd_api.g_false; l_commit VARCHAR2 (2) := fnd_api.g_false;

x_return_status VARCHAR2 (2); x_msg_count NUMBER := 0; x_msg_data VARCHAR2 (255); l_user_id NUMBER ; l_resp_id NUMBER ; l_appl_id NUMBER ; l_row_cnt NUMBER := 1; l_trohdr_rec inv_move_order_pub.trohdr_rec_type; l_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type; x_trohdr_rec inv_move_order_pub.trohdr_rec_type; x_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type; l_validation_flag VARCHAR2 (2) := inv_move_order_pub.g_validation_yes; l_trolin_tbl inv_move_order_pub.trolin_tbl_type; l_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type; x_trolin_tbl inv_move_order_pub.trolin_tbl_type; x_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type; x_number_of_rows NUMBER ; x_transfer_to_location NUMBER ; x_expiration_date DATE; x_transaction_temp_id NUMBER ;

----------------- TRANSACT MOVE ORDER API REQUIREMENTS ------------------------a_l_api_version NUMBER := 1.0; a_l_init_msg_list VARCHAR2 (2) := fnd_api.g_true; a_l_commit VARCHAR2 (2) := fnd_api.g_false; a_x_return_status VARCHAR2 (2); a_x_msg_count NUMBER := 0; a_x_msg_data VARCHAR2 (255); a_l_move_order_type NUMBER := 3; a_l_transaction_mode NUMBER := 1; a_l_trolin_tbl inv_move_order_pub.trolin_tbl_type; a_l_mold_tbl inv_mo_line_detail_util.g_mmtt_tbl_type; a_x_mmtt_tbl inv_mo_line_detail_util.g_mmtt_tbl_type; a_x_trolin_tbl inv_move_order_pub.trolin_tbl_type; a_l_transaction_date DATE := SYSDATE; a_l_user_id NUMBER; a_l_resp_id NUMBER; a_l_appl_id NUMBER;

v_transaction_date date; CURSOR c_mo_details IS SELECT mtrh.header_id, mtrh.request_number, mtrh.move_order_type, mtrh.organization_id, mtrl.line_id, mtrl.line_number, mtrl.inventory_item_id, mtrl.lot_number, mtrl.quantity, revision,mtrl.from_locator_id, (select distinct operating_unit from org_organization_definitions where organization_id = mtrh.organization_id) org_id FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl WHERE mtrh.header_id = mtrl.header_id AND (mtrh.request_number = P_REQUEST_NUMBER OR P_REQUEST_NUMBER IS NULL )--'8007' AND (mtrh.organization_id = P_ORGANIZATION_ID OR P_ORGANIZATION_ID IS NULL ); -- 755; BEGIN FND_GLOBAL.APPS_INITIALIZE(1130, 50632, 401); FOR i IN c_mo_details LOOP mo_global.set_policy_context ('S', i.org_id); inv_globals.set_org_id (NVL(i.organization_id,P_ORGANIZATION_ID)); -- mo_global.init ('INV'); SELECT INTO FROM WHERE COUNT (*) x_number_of_rows mtl_txn_request_lines header_id = i.header_id;

DBMS_OUTPUT.put_line ('Calling INV_REPLENISH_DETAIL_PUB to Allocate MO'); -- Allocate each line of the Move Order

inv_replenish_detail_pub.line_details_pub( p_line_ id => i.line_id, x_numbe r_of_rows => x_number_of_rows, x_detai led_qty => i.quantity, x_retur n_status => x_return_status, x_msg_c ount => x_msg_count, x_msg_d ata => x_msg_data, x_revis ion => i.revision, x_locat or_id => i.from_locator_id, x_trans fer_to_location => x_transfer_to_location, x_lot_n umber => i.lot_number, x_expir ation_date => x_expiration_date, x_trans action_temp_id => x_transaction_temp_id, p_trans action_header_id => NULL, p_trans action_mode => NULL, p_move_ order_type => i.move_order_type, p_seria l_flag => fnd_api.g_false, p_plan_ tasks => FALSE, p_auto_ pick_confirm => FALSE, p_commi t => FALSE ); DBMS_OUTPUT.put_line('================= ========================================='); DBMS_OUTPUT.put_line (x_return_status); DBMS_OUTPUT.put_line (x_msg_data); DBMS_OUTPUT.put_line (x_msg_count);

IF (x_return_status <> fnd_api.g_ret_sts_success) THEN DBMS_OUTPUT.put_line (x_msg_data); END IF; IF (x_return_status = fnd_api.g_ret_sts_success) THEN DBMS_OUTPUT.put_line ('ALLOCATE API SUCCESSFUL WITH - Trx temp ID: '); DBMS_OUTPUT.put_line (x_transaction_temp_id); END IF; DBMS_OUTPUT.put_line('========================= ================================='); IF x_transaction_temp_id IS NOT NULL OR x_transaction_temp_id <> 0 THEN mo_global.set_policy_context ('S', i.org_id); inv_globals.set_org_id (i.organization_id); mo_global.init ('INV'); a_l_trolin_tbl (1).line_id := i.line_id; select to_date (P_TRANSACTION_DATE) ('31-AUG-2010') into from 401); inv_pick_wave_pick_confirm_pub.pick_confirm (p_api_vers ion_number => a_l_api_version, p_init_msg_ list => a_l_init_msg_list, v_transaction_date dual; --

FND_GLOBAL.APPS_INITIALIZE(1130, 50632,

p_commit => a_l_commit, x_return_st atus => a_x_return_status, x_msg_count => a_x_msg_count, x_msg_data => a_x_msg_data, p_move_orde r_type => i.move_order_type, p_transacti on_mode => a_l_transaction_mode, p_trolin_tb l => a_l_trolin_tbl, p_mold_tbl => a_l_mold_tbl, x_mmtt_tbl => a_x_mmtt_tbl, x_trolin_tb l => a_x_trolin_tbl, p_transacti on_date => v_transaction_date -- l_transaction_date ); DBMS_OUTPUT.put_line('===================== =================================='); DBMS_OUTPUT.put_line ('Return Status - '|| a_x_return_status||' '||sqlerrm); DBMS_OUTPUT.put_line ('Return Message - '|| a_x_msg_data); DBMS_OUTPUT.put_line (a_x_msg_count); IF (a_x_return_status <> fnd_api.g_ret_sts_success) THEN DBMS_OUTPUT.put_line (a_x_msg_data); END IF; IF (a_x_return_status = fnd_api.g_ret_sts_success) THEN DBMS_OUTPUT.put_line ('TRANSACT API SUCCESSFULLY PROCESSED'); END IF;

DBMS_OUTPUT.put_line('===================== =================================='); END IF; END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('OUTER Exception Occured :'); DBMS_OUTPUT.put_line (SQLCODE||':'||SQLERRM); DBMS_OUTPUT.put_line ('======================================================='); END; END;

begin XX_ALOCATE_TRANSACT_MOVE_ORDER('',763,'31-AUG-2010'); end;

Posted by Sarfaraz at 9:34 AM 0 comments Email This BlogThis! Share to Twitter Share to Facebook Share to Google Buzz Labels: Order Management (ONT)

Reactions:

API - Ship Confirm of the Deliveries

declare p_api_version_number NUMBER; p_init_msg_list VARCHAR2(2000); V_return_status VARCHAR2(2000); V_msg_data VARCHAR2(2000); p_action_code VARCHAR2(2000); p_delivery_id NUMBER ; V_trip_id VARCHAR2(30); V_trip_name VARCHAR2(30); V_msg_details VARCHAR2(3000); V_msg_summary VARCHAR2(3000); V_msg_count NUMBER; V_init_msg_list VARCHAR(2000); begin

p_delivery_id := 7102; TRANSACTION SCREEN p_action_code :='CONFIRM';

-- dELIVERY ID FROM SALES

WSH_DELIVERIES_PUB.Delivery_Action( p_api_version_number => 1.0, p_init_msg_list => V_init_msg_list, x_return_status => V_return_status, x_msg_count => V_msg_count, x_msg_data => V_msg_data, p_action_code => p_action_code, p_delivery_id => p_delivery_id, x_trip_id => V_trip_id, x_trip_name => V_trip_name); IF (V_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN WSH_UTIL_CORE.get_messages('Y', V_msg_summary, V_msg_details, V_msg_count); dbms_output.put_line(V_msg_details); END IF; end;
Posted by Sarfaraz at 9:07 AM 0 comments Email This BlogThis! Share to Twitter Share to Facebook Share to Google Buzz Labels: Order Management (ONT)

Reactions:

API - Inventory Item Creation

DECLARE X_INVENTORY_ITEM_ID X_ORGANIZATION_ID X_RETURN_STATUS X_MSG_DATA X_MSG_COUNT LX_MSG_INDEX_OUT BEGIN NUMBER; NUMBER; VARCHAR2(4000); VARCHAR2(4000); NUMBER; NUMBER;

FND_GLOBAL.APPS_INITIALIZE(USER_ID=>1832,RESP_ID=>NULL,RESP _APPL_ID=>NULL); ego_item_pub.process_item ( => 1.0 => 'T' , p_commit => 'T' => 'CREATE' => 'XYZABC' => 'CREATED BY API' => 'CREATED FOR LONG' => 755 => 2 , p_Inventory_Item_Status_Code => 'Active' , p_approval_status => 'A' , x_inventory_item_id => x_inventory_item_id , x_organization_id => x_organization_id , x_return_status => x_return_status , x_msg_count => x_msg_count , x_msg_data => x_msg_data ); dbms_output.put_line(x_msg_data); FOR j in 1 .. x_msg_count LOOP FND_MSG_PUB.Get(p_msg_index => j, p_encoded => 'F', p_data => x_msg_data, p_msg_index_out => lx_msg_index_out); dbms_output.put_line(x_msg_data); end loop; , p_Transaction_Type -- UPDATE FOR Updating item , p_segment1 -- ITEM CODE , p_description -- ITEM DESCRIPTION , p_long_description -- ITEM LONG DESCRIPTION , p_organization_id -- WAREHOUSE ORGANIZATION ID --, P_TEMPLATE_ID p_api_version , p_init_msg_list

if x_return_status = 'S' dbms_output.put_line('ITEM CREATION SUCCESSFUL'); end if; end;

Posted by Sarfaraz at 9:00 AM 0 comments Email This BlogThis! Share to Twitter Share to Facebook Share to Google Buzz Labels: Inventory (INV)

Reactions:

API - Assign Categries to Inventory Items

DECLARE X_RETURN_STATUS VARCHAR2(1000); X_ERRORCODE NUMBER; X_MSG_COUNT NUMBER; X_MSG_DATA VARCHAR2(1000); X_MSG_INDEX_OUT VARCHAR2(2000); L_ERROR_MESSAGE VARCHAR2(2000); BEGIN --Apps Initialization is available in another section. Use the below link to know in detail -Apps_Initialize; INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY_ASSIGNMENT ( P_API_VERSION P_INIT_MSG_LIST P_COMMIT X_RETURN_STATUS X_ERRORCODE X_MSG_COUNT X_MSG_DATA P_CATEGORY_ID 1355, id to be assigned => 1.0, => FND_API.G_FALSE, => FND_API.G_FALSE, => X_RETURN_STATUS, => X_ERRORCODE, => X_MSG_COUNT, => X_MSG_DATA, => -- Category

P_CATEGORY_SET_ID 1100000043, P_INVENTORY_ITEM_ID 25803, Id P_ORGANIZATION_ID 755);

=> -- Category Set id => -- Inventory Item => -- Warehouse

IF x_return_status = fnd_api.g_ret_sts_success THEN COMMIT; DBMS_OUTPUT.put_line ('Item Category Assignment using API is Successful'); ELSE BEGIN IF (fnd_msg_pub.count_msg > 1) THEN FOR k IN 1 .. fnd_msg_pub.count_msg LOOP fnd_msg_pub.get (p_msg_index p_encoded p_data p_msg_index_out => 'F', => x_msg_data, => x_msg_index_out ); DBMS_OUTPUT.PUT_LINE('x_msg_data:= ' || x_msg_data); IF x_msg_data IS NOT NULL THEN l_error_message := l_error_message || '-' || x_msg_data; END IF; END LOOP; ELSE --Only one error fnd_msg_pub.get (p_msg_index p_encoded p_data => 'F', => x_msg_data,

=> k,

=> 1,

p_msg_index_out => x_msg_index_out ); l_error_message := x_msg_data; END IF; DBMS_OUTPUT.put_line ( API is ' || l_error_message ); ROLLBACK; EXCEPTION WHEN OTHERS THEN l_error_message := SQLERRM; DBMS_OUTPUT.put_line ( 'Error encountered by the API is ' || l_error_message ); END; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('Error in Assigning Category to an Item and error is '||SUBSTR(SQLERRM,1,200)); END;
Posted by Sarfaraz at 8:56 AM 0 comments Email This BlogThis! Share to Twitter Share to Facebook Share to Google Buzz Labels: Inventory (INV)

'Error encountered by the

Reactions:

API - Update Cost and Life (in months) of an ASSET

declare -- alter trigger JAI_FA_MA_ARIUD_T1 enable; -- alter trigger JAI_FA_MA_BRIUD_T1 enable; l_trans_rec FA_API_TYPES.trans_rec_type; l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type; l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type; l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type; l_asset_fin_mrc_tbl_new FA_API_TYPES.asset_fin_tbl_type; l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type; l_inv_tbl FA_API_TYPES.inv_tbl_type; l_inv_rate_tbl FA_API_TYPES.inv_rate_tbl_type;

l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type; l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type; l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type; l_inv_rec FA_API_TYPES.inv_rec_type; l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type; l_return_status VARCHAR2(1); l_mesg_count number := 0; l_mesg_len number; l_mesg varchar2(4000); --l_new_life FA_API_TYPES.asset_hr_attr_rec_type; begin fnd_profile.put('PRINT_DEBUG', 'Y'); dbms_output.enable(1000000); FA_SRVR_MSG.Init_Server_Message; FA_DEBUG_PKG.Initialize; --asset header info l_asset_hdr_rec.asset_id := 1326; -Asset Id from fa_additions l_asset_hdr_rec.book_type_code := 'SSE FA REGISTER'; -Book Name from FA_BOOKS -- fin info --l_asset_fin_rec_adj.cost := 500000; -- IF YOU WANT TO UPDATE COST l_asset_fin_rec_adj.life_in_months := 35; ---should be a value : life_in_months from : select METHOD_CODE , LIFE_IN_MONTHS from fa_methods where method_code like 'STL' ---should be in the denomination of years if oracle standard used like 1 year = 12 , 2 year = 24 , 3 year = 36 , 4 year = 48, 5 year 60 l_asset_fin_rec_adj.deprn_method_code:= 'STL'; from FA_METHODS FA_ADJUSTMENT_PUB.do_adjustment (p_api_version => 1.0, p_init_msg_list => FND_API.G_FALSE, --

p_commit => FND_API.G_FALSE, p_validation_level =>FND_API.G_VALID_LEVEL_FULL, x_return_status => l_return_status, x_msg_count => l_mesg_count, x_msg_data => l_mesg, p_calling_fn => 'ADJ_TEST_SCRIPT', px_trans_rec => l_trans_rec, px_asset_hdr_rec => l_asset_hdr_rec, p_asset_fin_rec_adj => l_asset_fin_rec_adj, x_asset_fin_rec_new => l_asset_fin_rec_new, x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new, px_inv_trans_rec => l_inv_trans_rec, px_inv_tbl => l_inv_tbl, -=> l_inv_rate_tbl, p_asset_deprn_rec_adj => l_asset_deprn_rec_adj, x_asset_deprn_rec_new => l_asset_deprn_rec_new, x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new, p_group_reclass_options_rec=> l_group_reclass_options_rec ); dbms_output.put_line(sqlerrm||' STATUS : '|| l_return_status); if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then fa_debug_pkg.dump_debug_messages(max_mesgs=>0); l_mesg_count := fnd_msg_pub.count_msg; if l_mesg_count > 0 then px_inv_rate_tbl

l_mesg := substr(fnd_msg_pub.get(fnd_msg_pub.G_FIRST,fnd_api.G_FALSE) , 1, 512); dbms_output.put_line(substr(l_mesg,1, 255)); for i in 1..l_mesg_count - 1 loop l_mesg := substr(fnd_msg_pub.get(fnd_msg_pub.G_NEXT,fnd_api.G_FALSE), 1, 512); dbms_output.put_line(substr(l_mesg, 1, 255)); end loop; fnd_msg_pub.delete_msg(); end if; else dbms_output.put_line('SUCCESS'); commit; dbms_output.put_line('THID' || to_char(l_trans_rec.transaction_header_id)); end if; end;
Posted by Sarfaraz at 7:03 AM 0 comments Email This BlogThis! Share to Twitter Share to Facebook Share to Google Buzz Labels: API - Update Cost and Life of Asset, Fixed Assets (FA)

Reactions:

API -Update Fixed Asset Cost

declare l_trans_rec FA_API_TYPES.trans_rec_type; l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type; l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type; l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type; l_asset_fin_mrc_tbl_new FA_API_TYPES.asset_fin_tbl_type; l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type; l_inv_tbl FA_API_TYPES.inv_tbl_type; l_inv_rate_tbl FA_API_TYPES.inv_rate_tbl_type; l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type; l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type; l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;

l_inv_rec FA_API_TYPES.inv_rec_type; l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type; l_return_status VARCHAR2(1); l_mesg_count number := 0; l_mesg_len number; l_mesg varchar2(4000); --l_new_life FA_API_TYPES.asset_hr_attr_rec_type; begin fnd_profile.put('PRINT_DEBUG', 'Y'); dbms_output.enable(1000000); FA_SRVR_MSG.Init_Server_Message; FA_DEBUG_PKG.Initialize; --asset header info l_asset_hdr_rec.asset_id := 317; l_asset_hdr_rec.book_type_code := 'SSE FA REGISTER'; -- fin info l_asset_fin_rec_adj.cost := 500000; --l_new_life.life_in_months:=100; FA_ADJUSTMENT_PUB.do_adjustment (p_api_version => 1.0, p_init_msg_list => FND_API.G_FALSE, p_commit => FND_API.G_FALSE, p_validation_level =>FND_API.G_VALID_LEVEL_FULL, x_return_status => l_return_status, x_msg_count => l_mesg_count, x_msg_data => l_mesg, p_calling_fn => 'ADJ_TEST_SCRIPT', px_trans_rec => l_trans_rec, px_asset_hdr_rec => l_asset_hdr_rec, p_asset_fin_rec_adj => l_asset_fin_rec_adj, x_asset_fin_rec_new => l_asset_fin_rec_new, x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,

px_inv_trans_rec => l_inv_trans_rec, -l_inv_rate_tbl, p_asset_deprn_rec_adj => l_asset_deprn_rec_adj, x_asset_deprn_rec_new => l_asset_deprn_rec_new, x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new, p_group_reclass_options_rec => l_group_reclass_options_rec ); dbms_output.put_line(l_return_status); if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then fa_debug_pkg.dump_debug_messages(max_mesgs=>0); l_mesg_count := fnd_msg_pub.count_msg; if l_mesg_count > 0 then l_mesg := substr(fnd_msg_pub.get (fnd_msg_pub.G_FIRST,fnd_api.G_FALSE), 1, 512); dbms_output.put_line(substr(l_mesg,1, 255)); for i in 1..l_mesg_count - 1 loop l_mesg := substr(fnd_msg_pub.get (fnd_msg_pub.G_NEXT, fnd_api.G_FALSE), 1, 512); dbms_output.put_line(substr(l_mesg, 1, 255)); end loop; fnd_msg_pub.delete_msg(); end if; else dbms_output.put_line('SUCCESS'); commit; dbms_output.put_line('THID' || to_char(l_trans_rec.transaction_header_id)); end if; end; px_inv_tbl => l_inv_tbl, px_inv_rate_tbl =>