Oracle EBS (ERP): Script

Showing posts with label Script. Show all posts

Oracle Advanced Pricing Modifier Qualifiers Setup extract script

--OM Advanced Pricing Modifier Qualifiers Setup

SELECT qsh.name,

qsh.description,

qsh.comments,

qsh.active_flag,

qsh.automatic_flag,

qsh.start_date_active,

qsh.end_date_active,

qms.operand,

qms.arithmetic_operator,

qms.start_date_active,

qms.modifier_level,

qms.list_line_type,

qms.product_attr_value item_code

FROM qp_secu_list_headers_vl qsh,

qp_qualifiers_v qqv,

ar_customers arc,

qp_modifier_summary_v qms

WHERE qqv.list_header_id = qsh.list_header_id

AND qualifier_attr_value = TO_CHAR (arc.customer_id)

AND arc.customer_name = NVL (:p_customer, arc.customer_name)

AND qms.product_attr_value = NVL (:p_item_code, qms.product_attr_value)

AND qqv.list_line_id = qms.list_line_id

AND qqv.list_header_id = qms.list_header_id

--AND qsh.end_date_active is NULL

AND qsh.active_flag = 'Y';

Oracle R12 Supplier Site update API

---Supplier Site update API

DECLARE

p_api_version NUMBER;

p_init_msg_list VARCHAR2 (200);

p_commit VARCHAR2 (200);

p_validation_level NUMBER;

x_return_status VARCHAR2 (200);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (200);

lr_vendor_site_rec apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;

lr_existing_vendor_site_rec ap_supplier_sites_all%ROWTYPE;

p_vendor_site_id NUMBER;

p_calling_prog VARCHAR2 (200);

BEGIN

-- Initialize apps session

--user_id, resp_id, resp_app_id

fnd_global.apps_initialize (0, 7000, 200);

mo_global.init ('SQLAP');

fnd_client_info.set_org_context (261);

-- Assign Basic Values

p_api_version := 1.0;

p_init_msg_list := fnd_api.g_true;

p_commit := fnd_api.g_true;

p_validation_level := fnd_api.g_valid_level_full;

p_vendor_site_id := 121039; -- to be end dated

p_calling_prog := 'XXCUSTOM';

BEGIN

SELECT *

INTO lr_existing_vendor_site_rec

FROM ap_supplier_sites_all assa

WHERE assa.vendor_site_id = p_vendor_site_id;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (

'Unable to derive the supplier site information for site id:'

|| p_vendor_site_id);

END;

-- Assign Vendor Site Details

lr_vendor_site_rec.vendor_site_id := lr_existing_vendor_site_rec.vendor_site_id;

lr_vendor_site_rec.last_update_date := SYSDATE;

lr_vendor_site_rec.last_updated_by := 0;

lr_vendor_site_rec.vendor_id := lr_existing_vendor_site_rec.vendor_id;

lr_vendor_site_rec.org_id := lr_existing_vendor_site_rec.org_id;

lr_vendor_site_rec.vendor_site_code := 'Sec. 194(Q)';

--lr_vendor_site_rec.inactive_date := SYSDATE;

AP_VENDOR_PUB_PKG.UPDATE_VENDOR_SITE (

p_api_version => p_api_version,

p_init_msg_list => p_init_msg_list,

p_commit => p_commit,

p_validation_level => p_validation_level,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

p_vendor_site_rec => lr_vendor_site_rec,

p_vendor_site_id => p_vendor_site_id,

p_calling_prog => p_calling_prog);

DBMS_OUTPUT.put_line ('RETURN_STATUS = ' || x_return_status);

DBMS_OUTPUT.put_line ('MSG_COUNT = ' || x_msg_count);

DBMS_OUTPUT.put_line ('MSG_DATA = ' || x_msg_data);

END;

Oracle GL Account Value Set with Qualifier Details script

Key Flexfields segments value set details (Values/Effective/Hierarchy/Qualifiers)

SELECT ffvs.flex_value_set_name,

ffv.flex_value,flex_value_meaning,

ffvt.description,

ffv.start_date_active,

ffv.end_date_active,

DECODE (ffv.enabled_flag, 'N', 'No', 'Yes') enabled,

DECODE (ffv.summary_flag, 'N', 'No', 'Yes') parent,

ffhv.hierarchy_code rollup_group,

-- Qualifiers

DECODE (SUBSTR (TO_CHAR (ffv.compiled_value_attributes), 1, 1),'N', 'No','Yes') budgeting_allowed,

DECODE (SUBSTR (TO_CHAR (ffv.compiled_value_attributes), 3, 1), 'N', 'No', 'Yes') posting_allowed,

(SELECT acct_type_desc2 FROM gl_acct_typ gat WHERE gat.acct_type_code = SUBSTR (TO_CHAR (ffv.compiled_value_attributes), 5, 1))

account_type,

DECODE (SUBSTR (TO_CHAR (ffv.compiled_value_attributes), 7, 1), 'N', 'No', 'Yes') third_party_control,

DECODE (SUBSTR (TO_CHAR (ffv.compiled_value_attributes), 9, 1), 'N', 'No', 'Yes') reconcile,

value_category

FROM fnd_flex_values ffv,

fnd_flex_values_tl ffvt,

fnd_flex_value_sets ffvs,

fnd_id_flex_segments fifs,

fnd_flex_hierarchies_vl ffhv

WHERE ffv.flex_value_id = ffvt.flex_value_id

AND ffv.flex_value_set_id = ffvs.flex_value_set_id

AND fifs.flex_value_set_id = ffvs.flex_value_set_id

AND fifs.id_flex_code = 'GL#'

AND ffvs.flex_value_set_name = :P_GL_ACCOUNT

-- AND ffv.flex_value_set_id = 1016027

AND ffv.structured_hierarchy_level = ffhv.hierarchy_id(+)

ORDER BY ffv.flex_value

Oracle Inventory MTL Temporary Data Delete Script

--Oracle Inventory Material Transactions Temporary data Delete

--Check first

SELECT * FROM MTL_TRANSACTIONS_TEMP_ALL_V;

SELECT *

FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT

WHERE TRANSACTION_TEMP_ID = 6651466;

---Delete from temp

DELETE FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT

WHERE TRANSACTION_TEMP_ID = 6651466;

COMMIT;

Oracle R12 Item Update via API

Oracle R12 Inventory Item Update through API - Sample for SHIPPABLE_ITEM_FLAG update

--CREATE OR REPLACE PROCEDURE JG_ITEMS_UPDATE_API

--AS

DECLARE

x_item_tbl EGO_ITEM_PUB.ITEM_TBL_TYPE;

x_message_list Error_Handler.Error_Tbl_Type;

x_return_status VARCHAR2 (2);

x_msg_count NUMBER := 0;

l_user_id NUMBER := -1;

l_resp_id NUMBER := -1;

l_application_id NUMBER := -1;

l_rowcnt NUMBER := 1;

l_api_version NUMBER := 1.0;

l_init_msg_list VARCHAR2 (2) := FND_API.G_TRUE;

l_commit VARCHAR2 (2) := FND_API.G_FALSE;

l_item_tbl EGO_ITEM_PUB.ITEM_TBL_TYPE;

l_role_grant_tbl EGO_ITEM_PUB.ROLE_GRANT_TBL_TYPE;

l_user_name VARCHAR2 (30) := 'JGTECH';

l_resp_name VARCHAR2 (30) := 'India Local Inventory';

l_item_catalog_group_id NUMBER := 0;

CURSOR C1

SELECT 271 ORGANIZATION_ID,

13018 INVENTORY_ITEM_ID,

-- '4946992' ITEM,

'Y' SHIPPABLE_ITEM_FLAG

/*PREPROCESSING_LEAD_TIME,

FULL_LEAD_TIME,

POSTPROCESSING_LEAD_TIME,

FIXED_LEAD_TIME,

VARIABLE_LEAD_TIME,

CUM_MANUFACTURING_LEAD_TIME,

CUMULATIVE_TOTAL_LEAD_TIME,

BUYER,

MRP_PLANNING_CODE,

ATO_FORECAST_CONTROL,

NVL(UPPER(END_ASSEMBLY_PEGGING_FLAG),'B')END_ASSEMBLY_PEGGING_FLAG,

DECODE(UPPER(CREATE_SUPPLY_FLAG),'YES','Y','N')CREATE_SUPPLY_FLAG,

PLANNER_CODE,

PLANNING_MAKE_BUY_CODE,

INVENTORY_PLANNING_CODE,

MIN_MINMAX_QUANTITY,

MAX_MINMAX_QUANTITY,

DECODE(UPPER(BOM_ENABLED_FLAG),'YES','Y','N')BOM_ENABLED_FLAG,

DECODE(UPPER(BUILD_IN_WIP_FLAG),'YES','Y','N')BUILD_IN_WIP_FLAG,

DECODE(UPPER(PURCHASING_ENABLED_FLAG),'YES','Y','N')PURCHASING_ENABLED_FLAG,

TRANSACTION_TYPE*/

FROM DUAL; --XX_ITEM_UPDATE_STG;

BEGIN

-- Get the user_id

SELECT user_id

INTO l_user_id

FROM fnd_user

WHERE user_name = l_user_name;

FOR i IN C1 LOOP

-- Get the application_id and responsibility_id

SELECT application_id, responsibility_id

INTO l_application_id, l_resp_id

FROM fnd_responsibility_vl

WHERE responsibility_name = l_resp_name

--FND_GLOBAL.APPS_INITIALIZE (1380, 51037, 7000);

FND_GLOBAL.APPS_INITIALIZE (l_user_id, l_resp_id, l_application_id);

DBMS_OUTPUT.put_line (

'Initialized applications context: '

|| l_user_id

|| ' '

|| l_resp_id

|| ' '

|| l_application_id);

-- Load l_item_tbl with the data

l_item_tbl (l_rowcnt).Organization_id := i.Organization_id; -- Organization Id

l_item_tbl (l_rowcnt).inventory_item_id := i.inventory_item_id;

l_item_tbl (l_rowcnt).transaction_type := 'UPDATE';

-- l_item_tbl (l_rowcnt).Segment1 := i.item; -- Item Number

l_item_tbl (l_rowcnt).SHIPPABLE_ITEM_FLAG := i.SHIPPABLE_ITEM_FLAG; -- Item Description

-- l_item_tbl(l_rowcnt).Template_Name := 'Finished Good'; -- Item template (** should be associated to ICC, Not mandatory)

-- call API to load Items

DBMS_OUTPUT.PUT_LINE ('=====================================');

DBMS_OUTPUT.PUT_LINE ('Calling EGO_ITEM_PUB.Process_Items API');

EGO_ITEM_PUB.PROCESS_ITEMS (p_api_version => l_api_version,

p_init_msg_list => l_init_msg_list,

p_commit => l_commit,

p_item_tbl => l_item_tbl,

p_role_grant_tbl => l_role_grant_tbl,

x_item_tbl => x_item_tbl,

x_return_status => x_return_status,

x_msg_count => x_msg_count);

DBMS_OUTPUT.PUT_LINE ('=====================================');

DBMS_OUTPUT.PUT_LINE ('Return Status: ' || x_return_status);

IF (x_return_status = FND_API.G_RET_STS_SUCCESS)

THEN

FOR i IN 1 .. x_item_tbl.COUNT

LOOP

DBMS_OUTPUT.PUT_LINE (

'Inventory Item Id :'

|| TO_CHAR (x_item_tbl (i).inventory_item_id));

DBMS_OUTPUT.PUT_LINE (

'Organization Id :' || TO_CHAR (x_item_tbl (i).organization_id));

END LOOP;

ELSE

DBMS_OUTPUT.PUT_LINE ('Error Messages ::');

Error_Handler.GET_MESSAGE_LIST (x_message_list => x_message_list);

FOR i IN 1 .. x_message_list.COUNT

LOOP

DBMS_OUTPUT.PUT_LINE (x_message_list (i).MESSAGE_TEXT);

END LOOP;

END IF;

END LOOP;

COMMIT;

DBMS_OUTPUT.PUT_LINE ('=====================================');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE ('Exception Occured :');

DBMS_OUTPUT.PUT_LINE (SQLERRM || ':' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

DBMS_OUTPUT.PUT_LINE ('=====================================');

RAISE;

END JG_ITEMS_UPDATE_API;

PLSQL Script to Submit Request Set from backend

----PLSQL Script to submit request set from backend in oracle apps

DECLARE

V_REQUEST_SET_EXIST BOOLEAN := FALSE;

req_id INTEGER := 0;

l_CONC_PROG_SUBMIT BOOLEAN := FALSE;

srs_failed EXCEPTION;

submitprog_failed EXCEPTION;

submitset_failed EXCEPTION;

l_start_date VARCHAR2(250);

BEGIN

V_REQUEST_SET_EXIST :=

FND_SUBMIT.set_request_set (application => 'XXJG',

request_set => 'XXTEST');

IF (NOT V_REQUEST_SET_EXIST)

THEN

RAISE srs_failed;

END IF;

fnd_file.put_line (fnd_file.LOG, 'Calling REQUEST SET first stage');

l_CONC_PROG_SUBMIT :=

fnd_submit.submit_program ('XXJG',

'FIRST_PROGRAM',

'STAGE10',

'ARGUMENT1');

IF (NOT l_CONC_PROG_SUBMIT)

THEN

RAISE submitprog_failed;

END IF;

l_CONC_PROG_SUBMIT :=

fnd_submit.submit_program ('XXJG',

'SECOND_PROGRAM',

'STAGE20');

IF (NOT l_CONC_PROG_SUBMIT)

THEN

RAISE submitprog_failed;

END IF;

l_CONC_PROG_SUBMIT :=

fnd_submit.submit_program ('XXJG',

'THIRD_PROGRAM',

'STAGE30');

IF (NOT l_CONC_PROG_SUBMIT)

THEN

RAISE submitprog_failed;

END IF;

l_CONC_PROG_SUBMIT :=

fnd_submit.submit_program ('XXJG',

'FOURTH_PROGRAM',

'STAGE40');

IF (NOT l_CONC_PROG_SUBMIT)

THEN

RAISE submitprog_failed;

END IF;

fnd_file.put_line (fnd_file.LOG, 'Calling submit_set');

select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')

into l_start_date

from dual;

req_id :=

FND_SUBMIT.submit_set (start_time => l_start_date,

sub_request => FALSE);

IF (req_id = 0)

THEN

RAISE submitset_failed;

END IF;

EXCEPTION

WHEN srs_failed

THEN

p_errbuf := 'Call to set_request_set failed: ' || fnd_message.get;

p_retcode := 2;

fnd_file.put_line (fnd_file.LOG, p_errbuf);

WHEN submitprog_failed

THEN

p_errbuf := 'Call to submit_program failed: ' || fnd_message.get;

p_retcode := 2;

fnd_file.put_line (fnd_file.LOG, p_errbuf);

WHEN submitset_failed

THEN

p_errbuf := 'Call to submit_set failed: ' || fnd_message.get;

p_retcode := 2;

fnd_file.put_line (fnd_file.LOG, p_errbuf);

WHEN OTHERS

THEN

p_errbuf := 'Request set submission failed – unknown error: ' || SQLERRM;

p_retcode := 2;

fnd_file.put_line (fnd_file.LOG, p_errbuf);

END;

How to convert numbers to words in oracle apps currency code wise

----------------------Package Specifican----------------------------------

CREATE OR REPLACE PACKAGE APPS.NUM_TO_WORD

PROCEDURE CONA_MAIN_DUP(

ipNumber IN NUMBER,

ipcurr Varchar2,

opText OUT VARCHAR2);

PROCEDURE CONA_INT_NUM_DUP(

ipNumber IN NUMBER,

ipcurr varchar2,

opText OUT VARCHAR2);

PROCEDURE CONA_NUM_20_99_DUP(

pNumber IN NUMBER,

ipcurr varchar2,

pText OUT VARCHAR2);

PROCEDURE CONA_NUM_0_19_DUP(

pNumber IN NUMBER,

pText IN OUT VARCHAR2);

END NUM_TO_WORD;

----------------------Package Body----------------------------------

CREATE OR REPLACE PACKAGE BODY APPS.NUM_TO_WORD AS

cPackageName varchar2(100):='CONA_KCT_RS_TEXT_DUP';

-- Jayanta --- CURRENCY Convert in Word ------

PROCEDURE CONA_MAIN_DUP(

ipNumber IN NUMBER,

ipcurr varchar2,

opText OUT VARCHAR2)

cProcName constant varchar2(100):='cPackageName'||'SN_P_MAIN_DUP';

vStatement varchar2(100):= ' '; -- SQL statement

vParamString varchar2(100):= ' '; -- Passed Parameter

vText VARCHAR2(200);

vLen NUMBER;

vLenDec NUMBER;

vValInt NUMBER;

vValDec NUMBER;

vTempText VARCHAR2(200);

vmaincurr varchar2(5) := null;

BEGIN

vTempText := NULL;

-- vText := 'Rs ';

If ipcurr = 'INR' then

Vtext := 'Rupees ';

elsif

ipcurr = 'USD' then

Vtext := 'US$ ';

elsif

ipcurr = 'GBP' then

Vtext := 'Pound ';

elsif

ipcurr = 'EUR' then

Vtext := 'Euro ';

elsif

ipcurr = 'BTN' then

Vtext := 'Nu ';

end if;

vValInt := TRUNC(ipNumber); -- Extract the integral part

vValDec := (ipNumber - vValInt)*100; -- Extract the decimal part

-- Call Procedure SN_P_CONV_INT_NUM_DUP for Integral Part

vmaincurr := ipcurr;

CONA_INT_NUM_DUP(vValInt,vmaincurr,vTempText);

vText := vText || vTempText;

-- Call Procedure SN_P_CONV_INT_NUM_DUP for Decimal Part

vTempText := NULL;

CONA_INT_NUM_DUP(vValDec,vmaincurr,vTempText);

-- vText := vText ||' and Paise '|| vTempText ||' only';

If ipcurr = 'INR' then

vText := vText ||' and Paise '|| vTempText ||' only';

elsif

ipcurr = 'USD' then

vText := vText ||' and Cent '|| vTempText ||' only';

elsif

ipcurr = 'GBP' then

vText := vText ||' and Penny '|| vTempText ||' only';

elsif

ipcurr = 'EUR' then

vText := vText ||' and Pence '|| vTempText ||' only';

elsif

ipcurr = 'BTN' then

vText := vText ||' and Ch '|| vTempText ||' only';

end if;

opText := vText ;

END CONA_MAIN_DUP;

PROCEDURE CONA_INT_NUM_DUP(

ipNumber IN NUMBER,

ipcurr in varchar2,

opText OUT VARCHAR2)

cProcName constant varchar2(100):='cPackageName'||'SN_P_CONV_INT_NUM_DUP';

vStatement varchar2(100):= ' '; -- SQL statement

vParamString varchar2(100):= ' '; -- Passed Parameter

vText VARCHAR2(100);

pText VARCHAR2(100);

vLenInt NUMBER;

vLenDec NUMBER;

vValInt NUMBER;

vValDec NUMBER;

vTempVal NUMBER;

vTempText VARCHAR2(100);

vCroreLen NUMBER;

vintnumcurr varchar2(5) := null;

BEGIN

vTempText := NULL;

vText := NULL;

vValInt := ipNumber;

pText := NULL;

vLenInt := LENGTH(TO_CHAR(vValInt));

vLenDec := LENGTH(TO_CHAR(vValDec));

vTempVal :=0;

vCroreLen :=0;

vintnumcurr := ipcurr;

-- If range more than or equal to one crore

IF(vValInt >= 10000000) THEN

-- Crore left side

vCroreLen := LENGTH(SUBSTR(TO_CHAR(vValInt),1,(vLenInt - 7)));

CONA_INT_NUM_DUP(TO_NUMBER(SUBSTR(TO_CHAR(vValInt),1,(vLenInt - 7))),vintnumcurr,vText);

IF (vText = 'one') THEN

pText := vText || ' Crore ';

ELSE

pText := vText || ' Crores ';

END IF;

vText := NULL;

-- Crore right side

vValInt := TO_NUMBER(SUBSTR(TO_CHAR(vValInt),(vCroreLen + 1),7));

IF(vValInt > 0) THEN

CONA_INT_NUM_DUP(vValInt,vintnumcurr,vText);

END IF;

ELSE

WHILE (vLenInt >= 1) LOOP

vTempText := NULL;

/* If range between 0 to 19 */

IF (vValInt >= 0 AND vValInt <= 19) THEN

CONA_NUM_0_19_DUP(vValInt,vTempText);

vText := vText || vTempText;

EXIT;

/* If range between 20 to 99 */

ELSIF (vValInt >= 20 AND vValInt <= 99) THEN

vTempText:= NULL;

CONA_NUM_20_99_DUP(SUBSTR(TO_CHAR(vValInt),1,1),vintnumcurr,vTempText);

vText := vText || vTempText;

IF (SUBSTR(TO_CHAR(vValInt),2,1)) <> 0 THEN

vText := vText ||'-';

vTempText:= NULL;

CONA_NUM_0_19_DUP(SUBSTR(TO_CHAR(vValInt),2,1),vTempText);

vText := vText || vTempText;

END IF;

EXIT;

/* If range between 100 to 999 */

ELSIF (vValInt >= 100 AND vValInt <= 999) THEN

CONA_NUM_0_19_DUP(SUBSTR(vValInt,1,1),vTempText);

vText := vText ||vTempText||' Hundred';

vValint := TO_NUMBER(SUBSTR(TO_CHAR(vValint),2,(vLenInt-1)));

/* If range between 1000 to 9999 */

ELSIF (vValInt >= 1000 AND vValInt <= 9999) THEN

CONA_NUM_0_19_DUP(SUBSTR(TO_CHAR(vValInt),1,1),vTempText);

vText := vText||vTempText||' Thousand';

vValint := TO_NUMBER(SUBSTR(TO_CHAR(vValint),2,(vLenInt-1)));

/* If range between 10,000 to 99,999 */

ELSIF (vValInt >= 10000 AND vValInt <= 99999) THEN

/* If range between 10,000 to 19,000 */

vTempVal := TO_NUMBER(SUBSTR(TO_CHAR(vValInt),1,2));

IF (vTempVal >= 0 AND vTempVal <= 19) THEN

vTempText:= NULL;

CONA_NUM_0_19_DUP(vTempVal,vTempText);

vText := vText || vTempText||' Thousand';

vValint := TO_NUMBER(SUBSTR(TO_CHAR(vValint),3,(vLenInt-1)));

/* If range between 20,000 to 99,999 */

ELSIF (vTempVal >= 20 AND vTempVal <= 99) THEN

vTempText:= NULL;

CONA_NUM_20_99_DUP(SUBSTR(TO_CHAR(vTempVal),1,1),vintnumcurr,vTempText);

vText := vText||vTempText;

IF (SUBSTR(TO_CHAR(vTempVal),2,1)) <> 0 THEN

vText := vText ||'-';

vTempText:= NULL;

CONA_NUM_0_19_DUP(SUBSTR(TO_CHAR(vValInt),2,1),vTempText);

vText := vText || vTempText ;

END IF;

vText := vText || ' Thousand';

vValint := TO_NUMBER(SUBSTR(TO_CHAR(vValint),3,(vLenInt-1)));

END IF;

/* If range between 1,00,000 to 99,99,999 */

ELSIF (vValInt >= 100000 AND vValInt <= 999999) THEN

CONA_NUM_0_19_DUP(SUBSTR(TO_CHAR(vValInt),1,1),vTempText);

IF (vTempText = 'one') THEN

vText := vText||vTempText||' Lakh';

ELSE

vText := vText||vTempText||' Lakhs';

END IF;

vValint := TO_NUMBER(SUBSTR(TO_CHAR(vValint),2,(vLenInt-1)));

/* If range between 10,00,000 to 99,99,999 */

ELSIF (vValInt >= 1000000 AND vValInt <= 9999999) THEN

/* If range between 10,00,000 to 19,99,999 */

vTempVal := TO_NUMBER(SUBSTR(TO_CHAR(vValInt),1,2));

IF (vTempVal >= 0 AND vTempVal <= 19) THEN

vTempText:= NULL;

CONA_NUM_0_19_DUP(vTempVal,vTempText);

vText := vText || vTempText||' Lakhs';

vValint := TO_NUMBER(SUBSTR(TO_CHAR(vValint),3,(vLenInt-1)));

/* If range between 20,00,000 to 99,99,999 */

ELSIF (vTempVal >= 20 AND vTempVal <= 99) THEN

vTempText:= NULL;

CONA_NUM_20_99_DUP(SUBSTR(TO_CHAR(vTempVal),1,1),vintnumcurr,vTempText);

vText := vText||vTempText;

IF (SUBSTR(TO_CHAR(vTempVal),2,1)) <> 0 THEN

vText := vText ||'-';

vTempText:= NULL;

CONA_NUM_0_19_DUP(SUBSTR(TO_CHAR(vValInt),2,1),vTempText);

vText := vText || vTempText ;

END IF;

vText := vText || ' Lakhs';

vValint := TO_NUMBER(SUBSTR(TO_CHAR(vValint),3,(vLenInt-1)));

END IF;

vLenInt := vLenInt -1 ;

IF ( vValInt = 0 ) THEN

EXIT;

ELSE

vText := vText || ' ';

END IF;

END LOOP;

END IF;

opText :=pText|| vText ;

END CONA_INT_NUM_DUP;

PROCEDURE CONA_NUM_20_99_DUP

(pNumber IN NUMBER,

ipcurr in varchar2,

pText OUT VARCHAR2)

cProcName constant varchar2(100):='cPackageName'||'CONA_NUM_20_99_DUP';

vStatement varchar2(100):= ' '; /* SQL statement */

vParamString varchar2(100):= ' '; /* Passed Parameter */

vText VARCHAR2(100);

v2099curr varchar2(5) := null;

BEGIN

-- vText := 'Rupees Zero and Paise ';

If ipcurr = 'INR' then

vText := 'Rupees Zero and Paise ';

elsif

ipcurr = 'USD' then

vText := 'US$ Zero and Pence ';

elsif

ipcurr = 'GBP' then

vText := 'Pound Zero and Pence ';

elsif

ipcurr = 'EUR' then

vText := 'Euro Zero and Pence ';

elsif

ipcurr = 'BTN' then

vText := 'Nu Zero and Ch ';

end if;

IF (pNumber = 2) THEN

pText := 'Twenty';

ELSIF (pNumber = 3) THEN

pText := 'Thirty';

ELSIF (pNumber = 4) THEN

pText := 'Forty';

ELSIF (pNumber = 5) THEN

pText := 'Fifty';

ELSIF (pNumber = 6) THEN

pText := 'Sixty';

ELSIF (pNumber = 7) THEN

pText := 'Seventy';

ELSIF (pNumber = 8) THEN

pText := 'Eighty';

ELSIF (pNumber = 9) THEN

pText := 'Ninety';

END IF;

END CONA_NUM_20_99_DUP;

PROCEDURE CONA_NUM_0_19_DUP

(pNumber IN NUMBER,

pText IN OUT VARCHAR2)

cProcName constant varchar2(100):='cPackageName'||'CONA_NUM_0_19_DUP';

vStatement varchar2(100):= ' '; /* SQL statement */

vParamString varchar2(100):= ' '; /* Passed Parameter */

vText VARCHAR2(100);

BEGIN

IF (pNumber = 0) THEN

pText := pText || ' Zero';

ELSIF (pNumber = 1) THEN

pText := pText || 'One';

ELSIF (pNumber = 2) THEN

pText := pText ||'Two';

ELSIF (pNumber = 3) THEN

pText := pText ||'Three';

ELSIF (pNumber = 4) THEN

pText := pText ||'Four';

ELSIF (pNumber = 5) THEN

pText := pText ||'Five';

ELSIF (pNumber = 6) THEN

pText :=pText || 'Six';

ELSIF (pNumber = 7) THEN

pText :=pText || 'Seven';

ELSIF (pNumber = 8) THEN

pText :=pText || 'Eight';

ELSIF (pNumber = 9) THEN

pText := pText ||'Nine';

ELSIF (pNumber = 10) THEN

pText := pText ||'Ten';

ELSIF (pNumber = 11) THEN

pText := pText ||'Eleven';

ELSIF (pNumber = 12) THEN

pText := pText ||'Twelve';

ELSIF (pNumber = 13) THEN

pText := pText ||'Thirteen';

ELSIF (pNumber = 14) THEN

pText := pText ||'Foeteen';

ELSIF (pNumber = 15) THEN

pText := pText ||'Fifteen';

ELSIF (pNumber = 16) THEN

pText := pText ||'Sixteen';

ELSIF (pNumber = 17) THEN

pText := pText ||'Seventeen';

ELSIF (pNumber = 18) THEN

pText := pText ||'Eighteen';

ELSIF (pNumber = 19) THEN

pText := pText ||'Nineteen';

END IF;

END CONA_NUM_0_19_DUP;

END NUM_TO_WORD;

----------------------Package Body End--------------------------------

Oracle EBS Purchase Order Cancel API

--------Approved Purchase Order Cancel via API

DECLARE

l_return_status VARCHAR2 (50);

CURSOR C_PO_CANCEL

SELECT pha.po_header_id,

pha.org_id,

pha.segment1 po_number,

pha.type_lookup_code,

pha.cancel_flag,

pha.closed_code

FROM po_headers_all pha

WHERE 1=1

AND pha.segment1 = '2100001262' -- Enter PO Number

AND nvl(pha.closed_code,'OPEN') = 'OPEN'

AND nvl(pha.cancel_flag, 'N') = 'N'

AND approved_flag = 'Y';

BEGIN

fnd_global.apps_initialize (user_id => 1278,

resp_id => 50817,

resp_appl_id => 7000);

FOR i IN c_po_cancel

LOOP

mo_global.init ('PO');

mo_global.set_policy_context ('S',i.org_id );

DBMS_OUTPUT.PUT_LINE ('Calling API PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT For Cancelling Documents');

po_document_control_pub.control_document

(p_api_version => 1.0, -- p_api_version

p_init_msg_list => fnd_api.g_true, -- p_init_msg_list

p_commit => fnd_api.g_true, -- p_commit

x_return_status => l_return_status, -- x_return_status

p_doc_type => 'PO', -- p_doc_type

p_doc_subtype => 'STANDARD', -- p_doc_subtype

p_doc_id => i.po_header_id, -- p_doc_id

p_doc_num => NULL, -- p_doc_num

p_release_id => NULL, -- p_release_id

p_release_num => NULL, -- p_release_num

p_doc_line_id => NULL, -- p_doc_line_id

p_doc_line_num => NULL, -- p_doc_line_num

p_doc_line_loc_id => NULL, -- p_doc_line_loc_id

p_doc_shipment_num => NULL, -- p_doc_shipment_num

p_action => 'CANCEL', -- p_action

p_action_date => SYSDATE, -- p_action_date

p_cancel_reason => NULL, -- p_cancel_reason

p_cancel_reqs_flag => 'N', -- p_cancel_reqs_flag

p_print_flag => NULL, -- p_print_flag

p_note_to_vendor => NULL, -- p_note_to_vendor

p_use_gldate =>NULL ,

p_org_id => i.org_id

);

COMMIT;

DBMS_OUTPUT.PUT_LINE('The Return Status of the API is => ' ||l_return_status);

If l_return_status = 'S' Then

DBMS_OUTPUT.PUT_LINE('The Purchase Order Which is Cancelled Now => ' ||i.po_number);

Else

DBMS_OUTPUT.PUT_LINE('The Purchase Order =>' ||i.po_number|| 'Failed for Cancel Due To Following Reason:');

-- Error messages returned by the Cancel API

FOR j IN 1 .. fnd_msg_pub.count_msg

LOOP

DBMS_OUTPUT.put_line (fnd_msg_pub.get (p_msg_index => j,

p_encoded => 'F'));

END LOOP;

END IF;

END LOOP;

END;

Output:

Calling API PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT For Cancelling Documents
The Return Status of the API is => S
The Purchase Order Which is Cancelled Now => 2100001262