Lazyman Rassel: 2022

রবিবার, ২৫ ডিসেম্বর, ২০২২

Remove sum in interactive grid

Set the following code into

Interactive grid >> Attribute >>JavaScript Initialization Code

function(config) {

config.editable.autoAddRow = true;

config.submitSelectedRows = false;

config.defaultGridViewOptions = {

skipReadonlyCells: true,

multiple: true,

selectAll: false,

footer:false,

};

return config;

}

শনিবার, ১৭ ডিসেম্বর, ২০২২

Dates between two parameter date

SELECT ( :psdate) + LEVEL - 1 dt
FROM DUAL
CONNECT BY LEVEL <= (( :pedate) - ( :psdate) + 1);

সোমবার, ১২ ডিসেম্বর, ২০২২

Edit Link icon in oracle apex

For Edit Button

For Print Button

Open in new tab

Link attribute :

target='_blank'

For Delete icon

রবিবার, ১১ ডিসেম্বর, ২০২২

Remove last character from a string in oracle sql

SELECT SUBSTR('ABCD', 1, LENGTH('ABCD') - 1) FROM DUAL;

Insert Script to Primary table/ Master table in oracle apex

--Stored procedure for generating DML Script in apex

CREATE OR REPLACE PROCEDURE GEN_APEX_DML_SCRIPT (

p_table_name IN VARCHAR2,

p_page_item_pk IN VARCHAR2 DEFAULT NULL, -- e.g. 'P17_ID'

P_PAGE IN VARCHAR2 -- e.g. 'P17_'

)

v_table_name VARCHAR2(100);

v_columns VARCHAR2(4000);

v_bind_columns VARCHAR2(4000);

v_update_columns VARCHAR2(4000);

v_pk_column VARCHAR2(100);

v_pk_page_item VARCHAR2(100);

BEGIN

v_table_name := UPPER(p_table_name);

-- Try to detect primary key automatically

BEGIN

SELECT acc.column_name

INTO v_pk_column

FROM user_constraints uc

JOIN user_cons_columns acc

ON uc.constraint_name = acc.constraint_name

WHERE uc.table_name = v_table_name

AND uc.constraint_type = 'P'

AND ROWNUM = 1;

EXCEPTION

WHEN NO_DATA_FOUND THEN

v_pk_column := NULL;

END;

-- If user passed a page item pk name, use it, else build default

IF p_page_item_pk IS NOT NULL THEN

v_pk_page_item := p_page_item_pk;

ELSE

v_pk_page_item := 'Pxx_' || v_pk_column;

END IF;

-- Build column lists

SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id),

LISTAGG(':' ||P_PAGE|| column_name, ', ') WITHIN GROUP (ORDER BY column_id),

LISTAGG(column_name || ' = :' ||P_PAGE|| column_name, ', ')

INTO v_columns, v_bind_columns, v_update_columns

FROM user_tab_cols

WHERE table_name = v_table_name

ORDER BY COLUMN_ID;

-- Output APEX DML script

DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');

DBMS_OUTPUT.PUT_LINE('-- Generated DML for table: ' || v_table_name);

DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');

DBMS_OUTPUT.PUT_LINE('BEGIN');

DBMS_OUTPUT.PUT_LINE(' IF :' || v_pk_page_item || ' IS NULL THEN');

DBMS_OUTPUT.PUT_LINE(' INSERT INTO ' || v_table_name || ' (' || v_columns || ')');

DBMS_OUTPUT.PUT_LINE(' VALUES (' || v_bind_columns || ');');

DBMS_OUTPUT.PUT_LINE('');

DBMS_OUTPUT.PUT_LINE(' ELSE');

DBMS_OUTPUT.PUT_LINE(' UPDATE ' || v_table_name);

DBMS_OUTPUT.PUT_LINE(' SET ' || v_update_columns);

IF v_pk_column IS NOT NULL THEN

DBMS_OUTPUT.PUT_LINE(' WHERE ' || v_pk_column || ' = :' || v_pk_page_item || ';');

ELSE

DBMS_OUTPUT.PUT_LINE(' -- No primary key found. Add WHERE clause manually.');

END IF;

DBMS_OUTPUT.PUT_LINE(' END IF;');

DBMS_OUTPUT.PUT_LINE('END;');

DBMS_OUTPUT.PUT_LINE('/');

DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END GEN_APEX_DML_SCRIPT;

SET SERVEROUTPUT ON;

BEGIN

GEN_APEX_DML_SCRIPT(:TABLE_NAME, :PAGE_ITEM_PK, 'P17_');

END;

DECLARE

CURSOR c1 IS SELECT :P_TABLE_NAME P_TABLE FROM DUAL;

CURSOR C2 IS

SELECT COLUMN_NAME || ',' COLUMN_NAME

FROM USER_TAB_COLS

WHERE TABLE_NAME = :P_TABLE_NAME

ORDER BY COLUMN_ID;

CURSOR c3 IS SELECT 'INSERT INTO (' || :P_TABLE_NAME P_TABLE FROM DUAL;

CURSOR C4 IS

SELECT COLUMN_NAME || ',' COLUMN_NAME

FROM USER_TAB_COLS

WHERE TABLE_NAME = :P_TABLE_NAME

ORDER BY COLUMN_ID;

CURSOR C5 IS

SELECT COLUMN_NAME

FROM USER_TAB_COLS

WHERE TABLE_NAME = :P_TABLE_NAME

ORDER BY COLUMN_ID;

BEGIN

FOR I IN C1

LOOP

DBMS_OUTPUT.PUT_LINE (

'BEGIN IF ' || ':' || :PAGE_NO_PK || ' IS NULL

THEN

INSERT INTO ' || I.P_TABLE || '(');

FOR X IN C2

LOOP

DBMS_OUTPUT.PUT_LINE (X.COLUMN_NAME);

END LOOP;

DBMS_OUTPUT.PUT_LINE (')');

FOR J IN C3

LOOP

DBMS_OUTPUT.PUT_LINE ('VALUES(');

FOR Y IN C4

LOOP

DBMS_OUTPUT.PUT_LINE (':' || :PAGE_NO_PK || Y.COLUMN_NAME);

END LOOP;

DBMS_OUTPUT.PUT_LINE (');');

END LOOP;

DBMS_OUTPUT.PUT_LINE (

ELSIF ' || ':' || :PAGE_NO_PK || ' IS NOT NULL

THEN

UPDATE ' || :P_TABLE_NAME || ' SET ');

FOR Z IN C5

LOOP

DBMS_OUTPUT.PUT_LINE (

Z.COLUMN_NAME

|| ' = :'

|| :PAGE_NO_PK

|| Z.COLUMN_NAME

|| ',');

END LOOP;

DBMS_OUTPUT.PUT_LINE (

'WHERE ' || :PAGE_NO_PK || ' = ' || ':' || :PAGE_NO_PK || ';');

DBMS_OUTPUT.PUT_LINE ('END IF;

END;');

END LOOP;

END;

শুক্রবার, ৯ ডিসেম্বর, ২০২২

Archive log clearing in oracle

rman target /

RMAN> delete archivelog all;

RMAN> exit;

-----------------------
> set oracled_sid=write_oracle_sid_here
> rman target sys/put_sys_password_here
> crosscheck archivelog all;
> delete noprompt expired archivelog all;
>exit;

রবিবার, ৪ ডিসেম্বর, ২০২২

Total calculation by java script in oracle apex

(function($) {
// This is the function that calculates over all the rows of the model and then
// updates something else.
// Change this to do whatever calculation is needed.
// Call this whenever the model data changes.

// Total is the column name which is to be calculated.
function update(model) {
var salKey = model.getFieldKey("TOTAL"), //TOTAL is the column name would be calculated
total = 0;
console.log(">> starting sum AMOUNT column")
model.forEach(function(record, index, id) {
var sal = parseFloat(record[salKey]), // record[salKey] should be a little faster than using model.getValue in a loop
meta = model.getRecordMetadata(id);
if (!isNaN(sal) && !meta.deleted && !meta.agg) {
total += sal;
}
});
console.log(">> setting sum AMOUNT column to " + total)
$s("P68_TOTAL_AMNT", total);
}
// P68_TOTAL_AMNT is the field name summary field.
// This is the general pattern for subscribing to model notifications
//
// need to do this here rather than in Execute when Page Loads so that the handler
// is setup BEFORE the IG is initialized otherwise miss the first model created event
$(function() {
// the model gets released and created at various times such as when the report changes
// listen for model created events so that we can subscribe to model notifications
$("#BILL").on("interactivegridviewmodelcreate", function(event, ui) {
var sid,
model = ui.model;
// note this is only done for the grid veiw. It could be done for
// other views if desired. The imporant thing to realize is that each
// view has its own model
if ( ui.viewId === "grid" ) {
sid = model.subscribe( {
onChange: function(type, change) {
console.log(">> model changed ", type, change);
if ( type === "set" ) {
// don't bother to recalculate if other columns change
if (change.field === "TOTAL" ) {
update( model );
}
} else if (type !== "move" && type !== "metaChange") {
// any other change except for move and metaChange affect the calculation
update( model );
}
},
progressView: $("#P68_TOTAL_AMNT") // this will cause a spinner on this field
} );
// if not lazy loaded there is no notification for initial data so update
update( model );
// just in case fetch all the data. Model notifications will
// cause calls to update so nothing to do in the callback function.
// can remove if data will always be less than 50 records
model.fetchAll(function() {});
}
});
});
})(apex.jQuery);

শনিবার, ৩ ডিসেম্বর, ২০২২

Execute immediate statement to create table

DECLARE
vCNT NUMBER;
BEGIN
SELECT COUNT (*)
INTO vCNT
FROM USER_TABLES
WHERE UPPER (TABLE_NAME) = UPPER ('table_01');

IF vCNT > 0

THEN

EXECUTE IMMEDIATE 'DROP table table_01 CASCADE CONSTRAINT';
EXECUTE IMMEDIATE 'create table table_01 as
SELECT *
FROM table_name
WHERE column= ' || :value;
ELSE
EXECUTE IMMEDIATE 'create table table_01 as
SELECT *
FROM table_name
WHERE column= ' || :value;
END IF;
END;

বৃহস্পতিবার, ১০ নভেম্বর, ২০২২

Checking json parameter value

CREATE TABLE TEST_CLOB

(

COL1 CLOB,

COL2 NUMBER,

TDATA VARCHAR2 (4000 BYTE)

);

CREATE OR REPLACE PROCEDURE CHECK_ANY_PARAM_VALUE (pCHECK_VALUE VARCHAR2)

AUTHID CURRENT_USER

PRAGMA AUTONOMOUS_TRANSACTION;

vCOL_NUMBER NUMBER;

BEGIN

SELECT NVL (MAX (COL2), 0)+1 INTO vCOL_NUMBER FROM TEST_CLOB;

INSERT INTO TEST_CLOB (COL2, COL1)

VALUES (vCOL_NUMBER, TO_CLOB (pCHECK_VALUE));

COMMIT;

END;

CREATE OR REPLACE PROCEDURE DPRC_INVOICE (PINVOICE_MST IN CLOB,

PINVOICE_DTL IN CLOB,

PINVOICE_ID IN NUMBER,

PCOMPANY_ID IN NUMBER,

PLOCATION_ID IN NUMBER,

PUSER_ID IN NUMBER,

PSTATUS OUT CLOB)

VINPUT_OBJ JSON_OBJECT_T := NEW JSON_OBJECT_T;

VINPUT_ARR JSON_ARRAY_T;

VOUTPUT_OBJ JSON_OBJECT_T := NEW JSON_OBJECT_T;

VMESSAGE VARCHAR2 (4000);

VINVOICE_ID NUMBER;

VINVOICE_NO VARCHAR2 (50);

VMODULE_ID NUMBER;

VREG_ID NUMBER;

VADM_ID NUMBER;

VCONS_ID NUMBER;

VREMARKS VARCHAR2 (4000);

VREF_DOC_ID NUMBER;

VDEL_DATE DATE;

VEMP_ID NUMBER;

VCC_ID NUMBER;

VRELATION_ID NUMBER;

VCC_CARD_NO VARCHAR2 (100);

VITEM_ID NUMBER;

VITEM_NAME VARCHAR2 (150);

VITEMTYPE_ID NUMBER;

VITEM_QTY NUMBER;

VITEM_RATE NUMBER;

VITEM_VAT NUMBER;

VURGENT_FEE NUMBER;

VSERVICE_CHARGE NUMBER;

VDEL_DATE_DTL VARCHAR2(50);

VDEL_STATUS_ID NUMBER;

BEGIN

CHECK_ANY_PARAM_VALUE (PINVOICE_MST);

CHECK_ANY_PARAM_VALUE (PINVOICE_DTL);

-- CHECK_ANY_PARAM_VALUE ('PINVOICE_ID:'||PINVOICE_ID||'~PCOMPANY_ID:'||PCOMPANY_ID||'~PLOCATION_ID:'||PLOCATION_ID||'~'||'PUSER_ID:'||PUSER_ID);

VINPUT_OBJ := JSON_OBJECT_T.PARSE (PINVOICE_MST);

VINPUT_ARR := VINPUT_OBJ.GET_ARRAY ('invoice_mst');

FOR I IN 0 .. VINPUT_ARR.GET_SIZE - 1

LOOP

DECLARE

PBILL_INVOICE_OBJ JSON_OBJECT_T;

BEGIN

PBILL_INVOICE_OBJ := JSON_OBJECT_T (VINPUT_ARR.GET (I));

vMODULE_ID := PBILL_INVOICE_OBJ.GET_NUMBER ('module_id');

vREG_ID := PBILL_INVOICE_OBJ.GET_NUMBER ('reg_id');

vADM_ID := PBILL_INVOICE_OBJ.GET_NUMBER ('adm_id');

vCONS_ID := PBILL_INVOICE_OBJ.GET_NUMBER ('cons_id');

vREMARKS := PBILL_INVOICE_OBJ.GET_STRING ('remarks');

vREF_DOC_ID := PBILL_INVOICE_OBJ.GET_NUMBER ('ref_doc_id');

vDEL_DATE := TO_DATE (PBILL_INVOICE_OBJ.GET_STRING ('del_date'),'mm-dd-rrrr');

vEMP_ID := PBILL_INVOICE_OBJ.GET_NUMBER ('emp_id');

vCC_ID := PBILL_INVOICE_OBJ.GET_NUMBER ('cc_id');

vRELATION_ID := PBILL_INVOICE_OBJ.GET_NUMBER ('relation_id');

vCC_CARD_NO := PBILL_INVOICE_OBJ.GET_STRING ('cc_card_no');

--CHECK_ANY_PARAM_VALUE (vMODULE_ID);

--PINVOICE_ID := S_BILL_INVOICE_MST_ID.NEXTVAL;

PRC_GLOBAL_SEQUENCE ('HIMS', 'BILL_INVOICE_MST', SYSDATE, VINVOICE_NO);

INSERT INTO BILL_INVOICE_MST (ID, INVOICE_NO, MODULE_ID, REG_ID,

ADM_ID, CONS_ID, INVOICE_DATE, REMARKS,

REF_DOC_ID, DEL_DATE, EMP_ID, CC_ID,

RELATION_ID, CC_CARD_NO, CREATED_BY, CREATED_ON,

COMPANY_ID, LOCATION_ID, STATUS)

VALUES (PINVOICE_ID, VINVOICE_NO, VMODULE_ID, VREG_ID,

VADM_ID, VCONS_ID, SYSDATE, VREMARKS,

VREF_DOC_ID, VDEL_DATE, VEMP_ID, VCC_ID,

VRELATION_ID, VCC_CARD_NO, PUSER_ID, SYSDATE,

PCOMPANY_ID, PLOCATION_ID, 1);

EXCEPTION WHEN OTHERS THEN

VOUTPUT_OBJ.PUT('response_code', 400);

VMESSAGE := 'Error Code : '|| SQLCODE || ' Error Text : ' || SQLERRM;

VOUTPUT_OBJ.PUT('message1', VMESSAGE);

PSTATUS := VOUTPUT_OBJ.TO_CLOB;

ROLLBACK;

RETURN;

END;

END LOOP;

VINPUT_OBJ := JSON_OBJECT_T.PARSE (PINVOICE_DTL);

VINPUT_ARR := VINPUT_OBJ.GET_ARRAY ('invoice_dtl');

FOR J IN 0 .. VINPUT_ARR.GET_SIZE - 1

LOOP

DECLARE

PBILL_INVOICE_DTL_OBJ JSON_OBJECT_T;

BEGIN

PBILL_INVOICE_DTL_OBJ := JSON_OBJECT_T (VINPUT_ARR.GET (J));

VITEM_ID := PBILL_INVOICE_DTL_OBJ.GET_NUMBER ('item_id');

VITEM_NAME := PBILL_INVOICE_DTL_OBJ.GET_STRING ('item_name');

VITEMTYPE_ID := PBILL_INVOICE_DTL_OBJ.GET_NUMBER ('itemtype_id');

VITEM_QTY := PBILL_INVOICE_DTL_OBJ.GET_NUMBER ('item_qty');

VITEM_RATE := PBILL_INVOICE_DTL_OBJ.GET_NUMBER ('item_rate');

VITEM_VAT := PBILL_INVOICE_DTL_OBJ.GET_NUMBER ('item_vat');

VURGENT_FEE := PBILL_INVOICE_DTL_OBJ.GET_NUMBER ('urgent_fee');

VSERVICE_CHARGE := PBILL_INVOICE_DTL_OBJ.GET_NUMBER ('service_charge');

VDEL_DATE_DTL := TO_DATE (PBILL_INVOICE_DTL_OBJ.GET_STRING ('del_date'), 'dd-mm-rrrr');

VDEL_STATUS_ID := PBILL_INVOICE_DTL_OBJ.GET_NUMBER ('del_status_id');

INSERT INTO BILL_INVOICE_DTL (INVOICE_ID, ITEM_ID, ITEM_NAME, ITEMTYPE_ID,

ITEM_QTY, ITEM_RATE, ITEM_VAT, URGENT_FEE,

SERVICE_CHARGE, DEL_DATE, DEL_STATUS_ID, CREATED_BY,

CREATED_ON, COMPANY_ID, LOCATION_ID, STATUS)

VALUES (PINVOICE_ID, VITEM_ID, VITEM_NAME, VITEMTYPE_ID,

VITEM_QTY, VITEM_RATE, VITEM_VAT, VURGENT_FEE,

VSERVICE_CHARGE, VDEL_DATE_DTL, VDEL_STATUS_ID, PUSER_ID,

SYSDATE, PCOMPANY_ID, PLOCATION_ID, 1);

EXCEPTION WHEN OTHERS THEN

VOUTPUT_OBJ.PUT('response_code', 400);

VMESSAGE := 'Error Code : '|| SQLCODE || ' Error Text : ' || SQLERRM;

VOUTPUT_OBJ.PUT('message', VMESSAGE);

PSTATUS := VOUTPUT_OBJ.TO_CLOB;

ROLLBACK;

RETURN;

END;

END LOOP;

VMESSAGE := 'Invoice '|| VINVOICE_NO || 'generated successfully.';

VOUTPUT_OBJ.PUT ('response_code', 200);

VOUTPUT_OBJ.PUT ('message', VMESSAGE);

PSTATUS := VOUTPUT_OBJ.TO_CLOB;

EXCEPTION WHEN OTHERS THEN

VOUTPUT_OBJ.PUT('response_code', 400);

VMESSAGE := 'Error Code : '|| SQLCODE || ' Error Text : ' || SQLERRM;

VOUTPUT_OBJ.PUT('message2', VMESSAGE);

PSTATUS := VOUTPUT_OBJ.TO_CLOB;

ROLLBACK;

RETURN;

END DPRC_INVOICE;

বুধবার, ৯ নভেম্বর, ২০২২

Fade away message in oracle apex

Create a dynamic action on page load execute javascript & set this code

setTimeout(function() {

$('#APEX_SUCCESS_MESSAGE').fadeOut('fast');

}, 5000);

শুক্রবার, ২৮ অক্টোবর, ২০২২

Number/ Numeric allowed only in oracle apex item/ Column

-- paste this code in execute when page load option of the page & change the item name as required.

$("#P37_COLLECTION").keypress(function(event){

event = (event) ? event : window.event;

if ( event.which != 0 && (event.which < 48 || event.which > 57) && (event.which != 46 || $(this).val().indexOf('.') != -1)){

return false;

}

return true;

});

-- For grid column

$(".only-numeric").bind("keypress", function (e) {

    var keyCode = e.which ? e.which : e.keyCode               

    if (!(keyCode >= 48 && keyCode <= 57)) {

      return false;

    }else{

      return true;

-- set class to the expected item

in css only-numeric

}

});

বৃহস্পতিবার, ২৭ অক্টোবর, ২০২২

Scroll bar in apex interactive report

Put this code in html header of region for vertical scrollbar

for both horizontal and vertical scrollbar

বুধবার, ১৯ অক্টোবর, ২০২২

Script Creator script specially for json procedure

select FNC_SEQ_SCRIPT(:TABLE_NAME) FROM DUAL;

-- TO SELECT COLUMN NAME

SELECT column_name||','

FROM user_tab_cols

WHERE table_name = :TABLE_NAME

order by column_id;

--INSERT SATEMENT

SELECT 'v'||column_name||','

FROM user_tab_cols

WHERE table_name = :TABLE_NAME

order by column_id;

-- UPDATING COLUMN NAME AND vCOLUMN NAME

SELECT column_name||'=v' ||column_name||','

FROM user_tab_cols

WHERE table_name = :TABLE_NAME

order by column_id;

SELECT column_name||','

FROM user_tab_cols

WHERE table_name = :TABLE_NAME

order by column_id;

--calling procedure variable value ASSIGN json

SELECT '"' || LOWER(COLUMN_NAME) || '":' || 'null,'

FROM user_tab_cols

WHERE table_name = :TABLE_NAME

ORDER BY column_id;

SELECT COLUMN_NAME || ':' || column_name || ','

FROM user_tab_cols

WHERE table_name = :TABLE_NAME

ORDER BY column_id;

SELECT COLUMN_NAME || '=' || 'v' || column_name || ','

FROM user_tab_cols

WHERE table_name = :TABLE_NAME

ORDER BY column_id;

-- ASSINGING VARIABLE VALUE

SELECT 'v' || column_name || ','

FROM user_tab_cols

WHERE table_name = :TABLE_NAME

ORDER BY column_id;

-- PROCEDURE CALLING SCRIPT

SELECT '"' || COLUMN_NAME || '":' || '"NULL",'

FROM user_tab_cols

WHERE table_name = :TABLE_NAME

ORDER BY column_id;

SELECT 'v' || column_name || ':= vMANUFACTURER_OBJ.GET_NUMBER (' || ','

FROM user_tab_cols

WHERE table_name = :TABLE_NAME

ORDER BY column_id;

SELECT CASE WHEN :data_type = 'DATE' THEN '' END FROM DUAL;

SELECT 'P_'

|| column_name

|| ' '

|| data_type

|| CASE

WHEN DATA_TYPE NOT IN ('DATE', 'NUMBER')

THEN

'(' || data_length || ');'

ELSE

';'

END

FROM user_tab_cols

WHERE table_name = :TABLE_NAME

ORDER BY column_id;

SELECT 'v'

|| column_name

|| ' '

|| data_type

|| CASE

WHEN DATA_TYPE NOT IN ('DATE', 'NUMBER')

THEN

'(' || data_length || ');'

ELSE

';'

END

FROM user_tab_cols

WHERE table_name = :TABLE_NAME

ORDER BY column_id;

-- to assign value in json declaration

SELECT 'v'

|| column_name

|| ':= '

|| CASE

WHEN DATA_TYPE = 'DATE' THEN 'TO_DATE(' || :PARAMETER_OBJ

ELSE :PARAMETER_OBJ

END

|| '.'

|| CASE

WHEN DATA_TYPE = 'NUMBER'

THEN

'GET_NUMBER (''' || LOWER (column_name) || ''');'

WHEN DATA_TYPE = 'VARCHAR2'

THEN

'GET_STRING (''' || LOWER (column_name) || ''');'

WHEN DATA_TYPE = 'DATE'

THEN

'GET_STRING ('''

|| LOWER (column_name)

|| ''')'

|| ','

|| '''dd-mm-rrrr'''

|| ');'

END

FROM user_tab_cols

WHERE table_name = :TABLE_NAME

ORDER BY column_id;

--- CALLING A JSON PROCEDURE

declare

vstatus clob;

preg_id varchar2(100);

phims_registration clob := '{ "HIMS_REGISTRATION":[

{

"reg_date":"16-OCT-2022",

"pat_type_no":101,

"salutation":null,

"fname":"Kafil",

"mname":"Uddin",

"lname":null,

"pname":null,

"gender":"M",

"mstatus":null,

"bloodgroup":null,

"religion":null,

"father_name":null,

"mother_name":null,

"spouse_name":null,

"dob":"10-OCT-1988",

"phone_tnt":null,

"phone_mobile":null,

"email":null,

"address":null,

"passport_no":null,

"nationality":null,

"nid":null,

"emp_id":null,

"cc_id":null,

"relation_id":null,

"cc_card_no":null,

"height_cm":null,

"weight_kg":null,

"contact_person_name":null,

"contact_relation":null,

"contact_phone":null,

"contact_mobile":null,

"contact_email":null,

"image_file_name":null,

"johndoe_flag":null,

"vip_flag":null,

"conceal_flag":null,

"company_id":1,

"location_id":1

}

]

}';

begin

dprc_registration (phims_registration, preg_id, 1, vstatus);

dbms_output.put_line ('vSTATUS > ' || vstatus);

exception

when others

then

dbms_output.put_line (sqlerrm);

end;

-- Calling procedure for admission

DECLARE

vstatus CLOB;

vADM_NO VARCHAR2 (50);

preg_no VARCHAR2 (100) := 'DNAR000009';

pHIMS_IPD_ADM CLOB := '{ "HIMS_IPD_ADM":[

{

"adm_date":"17-oct-22",

"ref_doctor_id":2,

"admitted_doctor_id":2,

"duty_doctor_id":2,

"bed_id":5,

"pat_type_no":null,

"dept_id":null,

"remarks":null,

"dis_req_flag":null,

"dis_req_by":null,

"dis_req_time":null,

"clearence_flag":null,

"clearence_date":null,

"fin_clear_flag":null,

"fin_clear_by":null,

"fin_clear_time":null,

"dis_flag":null,

"dis_by":null,

"dis_date":null,

"due_dis_flag":null,

"ref_adm_module_id":null,

"ref_adm_id":null,

"mother_adm_id":null,

"emp_id":null,

"cc_id":null,

"relation_id":null,

"cc_card_no":null,

"mkt_person_id":null,

"company_id":1,

"location_id":1

}

]

}';

BEGIN

dprc_IPD_ADM (pHIMS_IPD_ADM, vADM_NO, preg_no, 1, vstatus);

DBMS_OUTPUT.put_line ('vSTATUS > ' || vstatus);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

END;

DECLARE

v_seq_no VARCHAR2 (50);

BEGIN

PRC_GLOBAL_SEQUENCE ( 'HIMS',

'HIMS_REGISTRATION',

SYSDATE,

v_seq_no);

DBMS_OUTPUT.put_line ('Sequence No' || v_seq_no);

END;

SET SERVEROUTPUT ON

DECLARE

vstatus CLOB;

vINV_NO VARCHAR2 (100);

vBILL_INVOICE_MST CLOB := '{ "bill_invoice_mst":[

{

"module_id":1,

"reg_id":13,

"adm_id":8,

"cons_id":2,

"invoice_date":"18-oct-22",

"remarks":"Checking bill for detail.",

"cancel_flag":0,

"cancel_remarks":null,

"ref_doc_id":2,

"del_date":null,

"emp_id":null,

"cc_id":null,

"relation_id":null,

"cc_card_no":null,

"company_id":1,

"location_id":1

}

]

}';

vBILL_INVOICE_DTL CLOB := '{ "BILL_INVOICE_DTL":[

{

"item_id":1,

"item_name":"REGISTRATION FEE",

"itemtype_id":1,

"item_qty":1,

"item_rate":100,

"item_vat":null,

"urgent_fee":null,

"service_charge":null,

"cancel_flag":null,

"cancel_qty":null,

"reentry_flag":null,

"del_date":null,

"del_status_id":null,

"company_id":1,

"location_id":1

}

]

}';

BEGIN

DPRC_BILL_INVOICE (vBILL_INVOICE_MST,vBILL_INVOICE_DTL, vINV_NO, 1, vstatus);

DBMS_OUTPUT.put_line ('vSTATUS > ' || vstatus);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

END;

SET SERVEROUTPUT ON;

DECLARE

vstatus CLOB;

vINV_NO NUMBER;

vBILL_INVOICE_MST CLOB := '{ "bill_pay_mst":[

{

"id":null,

"reg_id":13,

"adm_id":null,

"invoice_id":null,

"module_id":1,

"pay_date":"19-oct-22",

"pay_type_id":8352,

"pay_mode_id":null,

"coll_mode_id":null,

"pay_amt":550,

"pay_remarks":null,

"given_amt":null,

"disc_type_id":null,

"disc_auth_by":null,

"disc_remarks":null,

"status":null,

"created_by":null,

"created_on":null,

"updated_by":null,

"updated_on":null,

"company_id":1,

"location_id":1

}

]

}';

vBILL_INVOICE_DTL CLOB := '{ "BILL_PAY_DTL":[

{

"id":null,

"pay_id":null,

"item_id":1,

"his_amt":null,

"his_ref":null,

"ref_amt":null,

"ref_ref":null,

"disc_type_id":null,

"status":null,

"created_by":null,

"created_on":null,

"updated_by":null,

"updated_on":null,

"company_id":1,

"location_id":1

}

]

}';

BEGIN

DPRC_BILL_COLLECTION (vBILL_INVOICE_MST,vBILL_INVOICE_DTL, vINV_NO, 1, vstatus);

DBMS_OUTPUT.put_line ('vSTATUS > ' || vstatus);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

END;

Function to create table script & necessary primary info with table name parameter

CREATE OR REPLACE FUNCTION fnc_table_script (p_table_name VARCHAR2)
RETURN VARCHAR2
IS
V_SCRIPT VARCHAR2 (4000);
BEGIN
SELECT 'CREATE TABLE '
|| P_TABLE_NAME
|| '(
ID NUMBER(10),
UDID VARCHAR2(30),
STATUS NUMBER(1),
CREATED_BY NUMBER(10),
CREATED_ON DATE,
UPDATED_BY NUMBER(10),
UPDATED_ON DATE,
COMPANY_NO NUMBER(10),
BRANCH_NO NUMBER(10)
);'
|| CHR (10)
|| '
CREATE SEQUENCE '
|| P_TABLE_NAME
|| '_ID_SEQ'
|| '
START WITH 1
INCREMENT BY 1
MINVALUE 0
NOCACHE
NOCYCLE
NOORDER;'
|| CHR (10)
|| '
CREATE OR REPLACE TRIGGER '
|| P_TABLE_NAME
|| '_TRG
BEFORE INSERT OR UPDATE
ON '
|| P_TABLE_NAME
|| '
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
BEGIN

IF INSERTING THEN
IF :NEW.ID IS NULL THEN
:NEW.ID := '
|| P_TABLE_NAME
|| '_ID_SEQ.NEXTVAL;
END IF;
:NEW.CREATED_ON := SYSDATE;
:NEW.CREATED_BY := FNC_USER_ID(''APP_USER'');

ELSIF UPDATING THEN

:NEW.UPDATED_ON := SYSDATE;
:NEW.UPDATED_BY := FNC_USER_ID(''APP_USER'');

END IF;

EXCEPTION
WHEN OTHERS THEN
RAISE;
END '
|| P_TABLE_NAME
|| '_TRG;
/'
|| '
CREATE UNIQUE INDEX '
|| P_TABLE_NAME
|| '_PK ON '
|| P_TABLE_NAME
|| '
(ID);
ALTER TABLE '
|| P_TABLE_NAME
|| ' '
|| ' ADD (
CONSTRAINT '
|| P_TABLE_NAME
|| '_PK'
|| '
PRIMARY KEY
(ID)
USING INDEX '
|| P_TABLE_NAME
|| '_PK
ENABLE VALIDATE);

CREATE OR REPLACE PUBLIC SYNONYM '
|| P_TABLE_NAME
|| ' FOR '
|| P_TABLE_NAME
|| ';
'
INTO V_SCRIPT
FROM DUAL;
RETURN V_SCRIPT;
END;
/

Function to create sequence script associate with table name

CREATE OR REPLACE FUNCTION fnc_seq_script (p_table_name VARCHAR2)

RETURN VARCHAR2

V_SCRIPT VARCHAR2 (4000);

BEGIN

SELECT 'CREATE SEQUENCE '

|| 'S_'

|| P_TABLE_NAME

|| '_ID'

|| '

START WITH 1

INCREMENT BY 1

MINVALUE 0

NOCACHE

NOCYCLE

NOORDER;'

|| CHR (10)

|| '

CREATE OR REPLACE TRIGGER '

|| P_TABLE_NAME

|| '_TRG BEFORE INSERT OR UPDATE

ON '

|| P_TABLE_NAME

|| '

REFERENCING NEW AS New OLD AS Old

FOR EACH ROW

DECLARE

V_SEQ_NO NUMBER;

BEGIN

IF INSERTING THEN

IF :NEW.ID IS NULL THEN

:NEW.ID :='

|| 'S_'

|| P_TABLE_NAME

|| '_ID'

|| '.NEXTVAL;

END IF;

IF :NEW.COMPANY_ID IS NULL THEN

:NEW.COMPANY_ID := FNC_GET_COMPANY;

END IF;

IF :NEW.LOCATION_ID IS NULL THEN

:NEW.LOCATION_ID := FNC_GET_LOCATION;

END IF;

IF :NEW.STATUS IS NULL THEN

:NEW.STATUS := 1;

END IF;

:NEW.CREATED_BY := FNC_USER_ID(NVL(V(''APP_USER''),USER));

:NEW.CREATED_ON := SYSDATE;

ELSIF UPDATING THEN

:NEW.UPDATED_BY := FNC_USER_ID(NVL(V(''APP_USER''),USER));

:NEW.UPDATED_ON := SYSDATE;

END IF;

EXCEPTION

WHEN OTHERS THEN

RAISE;

END '

|| P_TABLE_NAME

|| '_TRG;

SHOW ERRORS;

CREATE OR REPLACE PUBLIC SYNONYM '

|| P_TABLE_NAME

|| ' FOR '

|| P_TABLE_NAME

|| ';

INTO V_SCRIPT

FROM DUAL;

RETURN V_SCRIPT;

END;

Insert script in apex detail data block

--Storeed Procedure for generating APEX_GRID_DML

CREATE OR REPLACE PROCEDURE GEN_APEX_GRID_DML (p_table_name IN VARCHAR2)

v_table_name VARCHAR2(100);

v_columns VARCHAR2(4000);

v_bind_columns VARCHAR2(4000);

v_update_cols VARCHAR2(4000);

v_pk_column VARCHAR2(100);

BEGIN

v_table_name := UPPER(p_table_name);

-- Detect Primary Key Column

BEGIN

SELECT acc.column_name

INTO v_pk_column

FROM user_constraints uc

JOIN user_cons_columns acc

ON uc.constraint_name = acc.constraint_name

WHERE uc.table_name = v_table_name

AND uc.constraint_type = 'P'

AND ROWNUM = 1;

EXCEPTION

WHEN NO_DATA_FOUND THEN

v_pk_column := NULL;

END;

-- Build column lists

SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id),

LISTAGG(':' || column_name, ', ') WITHIN GROUP (ORDER BY column_id),

LISTAGG(column_name || ' = :' || column_name, ', ')

INTO v_columns, v_bind_columns, v_update_cols

FROM user_tab_cols

WHERE table_name = v_table_name;

-- Print generated block

DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');

DBMS_OUTPUT.PUT_LINE('-- APEX GRID DML BLOCK FOR TABLE: ' || v_table_name);

DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');

DBMS_OUTPUT.PUT_LINE('BEGIN');

DBMS_OUTPUT.PUT_LINE(' IF :APEX$ROW_STATUS = ''C'' THEN');

DBMS_OUTPUT.PUT_LINE(' INSERT INTO ' || v_table_name || ' (' || v_columns || ')');

DBMS_OUTPUT.PUT_LINE(' VALUES (' || v_bind_columns || ');');

DBMS_OUTPUT.PUT_LINE('');

DBMS_OUTPUT.PUT_LINE(' ELSIF :APEX$ROW_STATUS = ''U'' THEN');

DBMS_OUTPUT.PUT_LINE(' UPDATE ' || v_table_name);

DBMS_OUTPUT.PUT_LINE(' SET ' || v_update_cols);

IF v_pk_column IS NOT NULL THEN

DBMS_OUTPUT.PUT_LINE(' WHERE ' || v_pk_column || ' = :' || v_pk_column || ';');

ELSE

DBMS_OUTPUT.PUT_LINE(' -- No primary key found. Add WHERE clause manually.');

END IF;

DBMS_OUTPUT.PUT_LINE('');

DBMS_OUTPUT.PUT_LINE(' ELSIF :APEX$ROW_STATUS = ''D'' THEN');

DBMS_OUTPUT.PUT_LINE(' DELETE FROM ' || v_table_name);

IF v_pk_column IS NOT NULL THEN

DBMS_OUTPUT.PUT_LINE(' WHERE ' || v_pk_column || ' = :' || v_pk_column || ';');

ELSE

DBMS_OUTPUT.PUT_LINE(' -- No primary key found. Add WHERE clause manually.');

END IF;

DBMS_OUTPUT.PUT_LINE(' END IF;');

DBMS_OUTPUT.PUT_LINE('END;');

DBMS_OUTPUT.PUT_LINE('/');

DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END GEN_APEX_GRID_DML;

SET SERVEROUTPUT ON;

BEGIN

GEN_APEX_GRID_DML(:TABLE_NAME);

END;

--Latest version

DECLARE

v_table_name VARCHAR2(100) := UPPER(:P_TABLE_NAME);

v_columns VARCHAR2(4000);

v_bind_columns VARCHAR2(4000);

v_update_cols VARCHAR2(4000);

v_pk_column VARCHAR2(100);

BEGIN

-- Detect Primary Key Column

BEGIN

SELECT acc.column_name

INTO v_pk_column

FROM user_constraints uc

JOIN user_cons_columns acc

ON uc.constraint_name = acc.constraint_name

WHERE uc.table_name = v_table_name

AND uc.constraint_type = 'P'

AND ROWNUM = 1;

EXCEPTION

WHEN NO_DATA_FOUND THEN

v_pk_column := NULL;

END;

-- Build column lists for insert/update

SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id),

LISTAGG(':' || column_name, ', ') WITHIN GROUP (ORDER BY column_id),

LISTAGG(column_name || ' = :' || column_name, ', ')

INTO v_columns, v_bind_columns, v_update_cols

FROM user_tab_cols

WHERE table_name = v_table_name;

-- Print output block

DBMS_OUTPUT.PUT_LINE('BEGIN');

DBMS_OUTPUT.PUT_LINE(' IF :APEX$ROW_STATUS = ''C'' THEN');

DBMS_OUTPUT.PUT_LINE(' INSERT INTO ' || v_table_name || ' (' || v_columns || ')');

DBMS_OUTPUT.PUT_LINE(' VALUES (' || v_bind_columns || ');');

DBMS_OUTPUT.PUT_LINE('');

DBMS_OUTPUT.PUT_LINE(' ELSIF :APEX$ROW_STATUS = ''U'' THEN');

DBMS_OUTPUT.PUT_LINE(' UPDATE ' || v_table_name);

DBMS_OUTPUT.PUT_LINE(' SET ' || v_update_cols);

IF v_pk_column IS NOT NULL THEN

DBMS_OUTPUT.PUT_LINE(' WHERE ' || v_pk_column || ' = :' || v_pk_column || ';');

ELSE

DBMS_OUTPUT.PUT_LINE(' -- ⚠️ No primary key found. Please add WHERE clause manually.');

END IF;

DBMS_OUTPUT.PUT_LINE('');

DBMS_OUTPUT.PUT_LINE(' ELSIF :APEX$ROW_STATUS = ''D'' THEN');

DBMS_OUTPUT.PUT_LINE(' DELETE FROM ' || v_table_name);

IF v_pk_column IS NOT NULL THEN

DBMS_OUTPUT.PUT_LINE(' WHERE ' || v_pk_column || ' = :' || v_pk_column || ';');

ELSE

DBMS_OUTPUT.PUT_LINE(' -- ⚠️ No primary key found. Please add WHERE clause manually.');

END IF;

DBMS_OUTPUT.PUT_LINE(' END IF;');

DBMS_OUTPUT.PUT_LINE('END;');

END;

-- Old Code

DECLARE
CURSOR c1
IS
SELECT :P_TABLE_NAME P_TABLE FROM DUAL;
CURSOR C2
IS
SELECT COLUMN_NAME || ',' COLUMN_NAME
FROM USER_TAB_COLS
WHERE TABLE_NAME = :P_TABLE_NAME

ORDER BY COLUMN_ID;
CURSOR c3
IS
SELECT 'INSERT INTO (' || :P_TABLE_NAME P_TABLE FROM DUAL;
CURSOR C4
IS
SELECT COLUMN_NAME || ',' COLUMN_NAME
FROM USER_TAB_COLS
WHERE TABLE_NAME = :P_TABLE_NAME

ORDER BY COLUMN_ID;
CURSOR C5
IS
SELECT COLUMN_NAME
FROM USER_TAB_COLS
WHERE TABLE_NAME = :P_TABLE_NAME

ORDER BY COLUMN_ID;
BEGIN
FOR I IN C1
LOOP
DBMS_OUTPUT.PUT_LINE (
'BEGIN
IF :APEX$ROW_STATUS = ''C''
THEN
INSERT INTO ' || I.P_TABLE || '(');
FOR X IN C2
LOOP
DBMS_OUTPUT.PUT_LINE (X.COLUMN_NAME);
END LOOP;
DBMS_OUTPUT.PUT_LINE (')');
FOR J IN C3
LOOP
DBMS_OUTPUT.PUT_LINE ('VALUES(');
FOR Y IN C4
LOOP
DBMS_OUTPUT.PUT_LINE (':' || Y.COLUMN_NAME);
END LOOP;
DBMS_OUTPUT.PUT_LINE (');');
END LOOP;
DBMS_OUTPUT.PUT_LINE (
'
ELSIF :APEX$ROW_STATUS = ''U''
THEN
UPDATE ' || :P_TABLE_NAME || ' SET ');
FOR Z IN C5
LOOP
DBMS_OUTPUT.PUT_LINE (
Z.COLUMN_NAME || ' = :' || Z.COLUMN_NAME || ',');
END LOOP;
DBMS_OUTPUT.PUT_LINE ('WHERE COLUMN_NAME = :COLUMN_NAME;');
DBMS_OUTPUT.PUT_LINE (
'ELSIF :APEX$ROW_STATUS = ''D''
THEN
DELETE FROM '
|| :P_TABLE_NAME
|| '
WHERE COLUMN_NAME = :COLUMN_NAME;
END IF;
END;');
END LOOP;
END;

Lazyman Rassel

রবিবার, ২৫ ডিসেম্বর, ২০২২

Remove sum in interactive grid

শনিবার, ১৭ ডিসেম্বর, ২০২২

Dates between two parameter date

সোমবার, ১২ ডিসেম্বর, ২০২২

Edit Link icon in oracle apex

রবিবার, ১১ ডিসেম্বর, ২০২২

Remove last character from a string in oracle sql

Insert Script to Primary table/ Master table in oracle apex

শুক্রবার, ৯ ডিসেম্বর, ২০২২

Archive log clearing in oracle

রবিবার, ৪ ডিসেম্বর, ২০২২

Total calculation by java script in oracle apex

শনিবার, ৩ ডিসেম্বর, ২০২২

Execute immediate statement to create table

বৃহস্পতিবার, ১০ নভেম্বর, ২০২২

Checking json parameter value

বুধবার, ৯ নভেম্বর, ২০২২

Fade away message in oracle apex

শুক্রবার, ২৮ অক্টোবর, ২০২২

Number/ Numeric allowed only in oracle apex item/ Column

বৃহস্পতিবার, ২৭ অক্টোবর, ২০২২

Scroll bar in apex interactive report

বুধবার, ১৯ অক্টোবর, ২০২২

Script Creator script specially for json procedure

Function to create table script & necessary primary info with table name parameter

Function to create sequence script associate with table name

Insert script in apex detail data block

Row-level “Add” icon using a virtual column (APEX-safe)

আপত্তিজনক অভিযোগ করুন

লেবেল