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;
কোন মন্তব্য নেই:
একটি মন্তব্য পোস্ট করুন