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
IS
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)
IS
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;
