-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--   PAIDCHEQUE->TBL_PAID_CHEQUE_RETURN TBL_PAID_CHEQUE_RETURN TBL_PAID_CHEQUE_RETURN TBL_PAID_CHEQUE_RETURN  
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
------------------------------------------------------------------------------------------
            CREATE OR REPLACE FUNCTION fn_get_all_paid_cheque_return() RETURNS SETOF paidcheque.tbl_paid_cheque_return AS $$
                DECLARE

                BEGIN
                    RETURN QUERY SELECT * FROM paidcheque.tbl_paid_cheque_return WHERE int_sys_action_id != -1;  
                END;
            $$ LANGUAGE plpgsql;
        

------------------------------------------------------------------------------------------
            CREATE OR REPLACE FUNCTION fn_get_all_paid_cheque_return_with_action_user() 
                            RETURNS TABLE (pk_paid_cheque_return_id BIGINT,

								fk_documents_id BIGINT,
								fk_paid_cheque_id BIGINT,
								fk_paid_cheque_details_id BIGINT,

								fk_bank_charge_id BIGINT,
								fk_bank_cr_customer_id BIGINT,
								fk_bank_expense_id BIGINT,

								vhr_bank_currency VARCHAR,
								dbl_bank_cur_roe DOUBLE PRECISION,
								dbl_bank_cur_bank_charge DOUBLE PRECISION,

								vhr_cust_currency VARCHAR,
								dbl_cust_cur_roe DOUBLE PRECISION,
								dbl_cust_cur_cust_charge DOUBLE PRECISION,

								vhr_base_currency VARCHAR,
								dbl_base_cur_bank_charge DOUBLE PRECISION,
								dbl_base_cur_cust_charge DOUBLE PRECISION,

								fk_service_tax_id BIGINT,
								dbl_sys_perce_of_tax DOUBLE PRECISION,
								dbl_perce_of_tax DOUBLE PRECISION,
								dbl_ledg_cur_tax_base_amount DOUBLE PRECISION,
								dbl_ledg_cur_tax_amount DOUBLE PRECISION,

								dbl_base_cur_tax_base_amount DOUBLE PRECISION,
								dbl_base_cur_tax_amount DOUBLE PRECISION) AS $$
                DECLARE

                BEGIN
                    RETURN QUERY SELECT mt.pk_paid_cheque_return_id,

					mt.fk_documents_id,
					mt.fk_paid_cheque_id,
					mt.fk_paid_cheque_details_id,

					mt.fk_bank_charge_id,
					mt.fk_bank_cr_customer_id,
					mt.fk_bank_expense_id,

					mt.vhr_bank_currency,
					mt.dbl_bank_cur_roe,
					mt.dbl_bank_cur_bank_charge,

					mt.vhr_cust_currency,
					mt.dbl_cust_cur_roe,
					mt.dbl_cust_cur_cust_charge,

					mt.vhr_base_currency,
					mt.dbl_base_cur_bank_charge,
					mt.dbl_base_cur_cust_charge,

					mt.fk_service_tax_id,
					mt.dbl_sys_perce_of_tax,
					mt.dbl_perce_of_tax,
					mt.dbl_ledg_cur_tax_base_amount,
					mt.dbl_ledg_cur_tax_amount,

					mt.dbl_base_cur_tax_base_amount,
					mt.dbl_base_cur_tax_amount
                        FROM paidcheque.tbl_paid_cheque_return mt 
                            LEFT JOIN authentication.tbl_user u1
                                ON mt.fk_created_user_id =  u1.pk_user_id
                            LEFT JOIN authentication.tbl_user u2
                                ON mt.fk_last_modified_user_id =  u2.pk_user_id
                            LEFT JOIN authentication.tbl_user u3
                                ON mt.fk_deleted_user_id =  u3.pk_user_id
                        WHERE mt.int_sys_action_id != -1  
                        ORDER BY ;
                END;
            $$ LANGUAGE plpgsql;
        

------------------------------------------------------------------------------------------

            CREATE OR REPLACE PROCEDURE sp_insert_or_update_paid_cheque_return(jsnPaidChequeReturn JSONB, jsnSqlRes OUT JSON) AS $$
                DECLARE
                    objSpOutRes TYP_SP_OUT_RES;
                    objFnCheckRes TYP_FN_CHECK_RES;
                    dtmCurrent TIMESTAMP WITH TIME ZONE;
                BEGIN
                    SELECT NOW() INTO dtmCurrent;

                    -- // Duplication Checking in DB FUCTION(Insert and Update)
                    SELECT * FROM  fn_check_data_uniqueness('paidcheque.tbl_paid_cheque_return'::VARCHAR, ''::VARCHAR, ::VARCHAR, 'pk_paid_cheque_return_id'::VARCHAR, CAST(jsnPaidChequeReturn->>'binPaidChequeReturnId' AS BIGINT)::BIGINT) INTO objFnCheckRes;
                    IF objFnCheckRes.sin_sql_check_status = -1::SMALLINT THEN
                        objSpOutRes.sinSqlCustomStatus := -1::SMALLINT;
                        objSpOutRes.strSqlCustomStatusCode := 'ERROR';
                        objSpOutRes.strSqlCustomMessage := objFnCheckRes.vhr_sql_check_message;

                        objSpOutRes.strSqlCustomErrorType := 'DATABASE_VALIDATION_ERROR';

                        SELECT * FROM  fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
                        RAISE NOTICE 'jsnSqlRes: %', jsnSqlRes;
                        RETURN;
                    END IF;

                    IF CAST(jsnPaidChequeReturn->>'binPaidChequeReturnId' AS BIGINT)::BIGINT = 0::BIGINT THEN
                        -- // Count Exced or other checking License related in DB FUCTION(Insert)
                        

                        --jsnPaidChequeReturn.intSysActionId = 0;
                        --jsnPaidChequeReturn.binCreatedUserId = jsnPaidChequeReturn.linLoginUserId;
                        --jsnPaidChequeReturn.dtmCreated = jsnPaidChequeReturn.dtmCurrentDateTime; --SELECT NOW();

                        INSERT INTO paidcheque.tbl_paid_cheque_return (fk_documents_id,
						fk_paid_cheque_id,
						fk_paid_cheque_details_id,

						fk_bank_charge_id,
						fk_bank_cr_customer_id,
						fk_bank_expense_id,

						vhr_bank_currency,
						dbl_bank_cur_roe,
						dbl_bank_cur_bank_charge,

						vhr_cust_currency,
						dbl_cust_cur_roe,
						dbl_cust_cur_cust_charge,

						vhr_base_currency,
						dbl_base_cur_bank_charge,
						dbl_base_cur_cust_charge,

						fk_service_tax_id,
						dbl_sys_perce_of_tax,
						dbl_perce_of_tax,
						dbl_ledg_cur_tax_base_amount,
						dbl_ledg_cur_tax_amount,

						dbl_base_cur_tax_base_amount,
						dbl_base_cur_tax_amount)
                            VALUES(CAST(jsnPaidChequeReturn->>'binDocumentsId' AS BIGINT),
						CAST(jsnPaidChequeReturn->>'binPaidChequeId' AS BIGINT),
						CAST(jsnPaidChequeReturn->>'binPaidChequeDetailsId' AS BIGINT),

						CAST(jsnPaidChequeReturn->>'binBankChargeId' AS BIGINT),
						CAST(jsnPaidChequeReturn->>'binBankCrCustomerId' AS BIGINT),
						CAST(jsnPaidChequeReturn->>'binBankExpenseId' AS BIGINT),

						jsnPaidChequeReturn->>'strBankCurrency',
						CAST(jsnPaidChequeReturn->>'dblBankCurRoe' AS DOUBLE PRECISION),
						CAST(jsnPaidChequeReturn->>'dblBankCurBankCharge' AS DOUBLE PRECISION),

						jsnPaidChequeReturn->>'strCustCurrency',
						CAST(jsnPaidChequeReturn->>'dblCustCurRoe' AS DOUBLE PRECISION),
						CAST(jsnPaidChequeReturn->>'dblCustCurCustCharge' AS DOUBLE PRECISION),

						jsnPaidChequeReturn->>'strBaseCurrency',
						CAST(jsnPaidChequeReturn->>'dblBaseCurBankCharge' AS DOUBLE PRECISION),
						CAST(jsnPaidChequeReturn->>'dblBaseCurCustCharge' AS DOUBLE PRECISION),

						CAST(jsnPaidChequeReturn->>'binServiceTaxId' AS BIGINT),
						CAST(jsnPaidChequeReturn->>'dblSysPerceOfTax' AS DOUBLE PRECISION),
						CAST(jsnPaidChequeReturn->>'dblPerceOfTax' AS DOUBLE PRECISION),
						CAST(jsnPaidChequeReturn->>'dblLedgCurTaxBaseAmount' AS DOUBLE PRECISION),
						CAST(jsnPaidChequeReturn->>'dblLedgCurTaxAmount' AS DOUBLE PRECISION),

						CAST(jsnPaidChequeReturn->>'dblBaseCurTaxBaseAmount' AS DOUBLE PRECISION),
						CAST(jsnPaidChequeReturn->>'dblBaseCurTaxAmount' AS DOUBLE PRECISION)) RETURNING pk_paid_cheque_return_id INTO objSpOutRes.binSqlPk;
                    ELSE
                        -- // UPDATE Check already update another usersin DB FUCTION(Update and Delete)
                        SELECT * FROM  fn_check_already_update_or_delete_another_user('paidcheque.tbl_paid_cheque_return'::VARCHAR, 'pk_paid_cheque_return_id'::VARCHAR, CAST(jsnPaidChequeReturn->>'binPaidChequeReturnId' AS BIGINT)::BIGINT, CAST(jsnPaidChequeReturn->>'intSysActionId' AS INT)::INT) INTO objFnCheckRes;
                        IF objFnCheckRes.sin_sql_check_status = -1::SMALLINT THEN
                            objSpOutRes.sinSqlCustomStatus := -1::SMALLINT;
                            objSpOutRes.strSqlCustomStatusCode := 'ERROR';
                            objSpOutRes.strSqlCustomMessage := objFnCheckRes.vhr_sql_check_message;

                            objSpOutRes.strSqlCustomErrorType := 'DATABASE_VALIDATION_ERROR';

                            SELECT * FROM  fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
                            RAISE NOTICE 'jsnSqlRes: %', jsnSqlRes;
                            RETURN;
                        END IF;

                        --jsnPaidChequeReturn.intSysActionId = jsnPaidChequeReturn.intSysActionId + 1;
                        --jsnPaidChequeReturn.binLastModifiedUserId = jsnPaidChequeReturn.linLoginUserId;
                        --jsnPaidChequeReturn.dtmLastModified = jsnPaidChequeReturn.dtmCurrentDateTime; --SELECT NOW();

                        UPDATE paidcheque.tbl_paid_cheque_return SET fk_documents_id = CAST(jsnPaidChequeReturn->>'binDocumentsId' AS BIGINT),
						fk_paid_cheque_id = CAST(jsnPaidChequeReturn->>'binPaidChequeId' AS BIGINT),
						fk_paid_cheque_details_id = CAST(jsnPaidChequeReturn->>'binPaidChequeDetailsId' AS BIGINT),

						fk_bank_charge_id = CAST(jsnPaidChequeReturn->>'binBankChargeId' AS BIGINT),
						fk_bank_cr_customer_id = CAST(jsnPaidChequeReturn->>'binBankCrCustomerId' AS BIGINT),
						fk_bank_expense_id = CAST(jsnPaidChequeReturn->>'binBankExpenseId' AS BIGINT),

						vhr_bank_currency = jsnPaidChequeReturn->>'strBankCurrency',
						dbl_bank_cur_roe = CAST(jsnPaidChequeReturn->>'dblBankCurRoe' AS DOUBLE PRECISION),
						dbl_bank_cur_bank_charge = CAST(jsnPaidChequeReturn->>'dblBankCurBankCharge' AS DOUBLE PRECISION),

						vhr_cust_currency = jsnPaidChequeReturn->>'strCustCurrency',
						dbl_cust_cur_roe = CAST(jsnPaidChequeReturn->>'dblCustCurRoe' AS DOUBLE PRECISION),
						dbl_cust_cur_cust_charge = CAST(jsnPaidChequeReturn->>'dblCustCurCustCharge' AS DOUBLE PRECISION),

						vhr_base_currency = jsnPaidChequeReturn->>'strBaseCurrency',
						dbl_base_cur_bank_charge = CAST(jsnPaidChequeReturn->>'dblBaseCurBankCharge' AS DOUBLE PRECISION),
						dbl_base_cur_cust_charge = CAST(jsnPaidChequeReturn->>'dblBaseCurCustCharge' AS DOUBLE PRECISION),

						fk_service_tax_id = CAST(jsnPaidChequeReturn->>'binServiceTaxId' AS BIGINT),
						dbl_sys_perce_of_tax = CAST(jsnPaidChequeReturn->>'dblSysPerceOfTax' AS DOUBLE PRECISION),
						dbl_perce_of_tax = CAST(jsnPaidChequeReturn->>'dblPerceOfTax' AS DOUBLE PRECISION),
						dbl_ledg_cur_tax_base_amount = CAST(jsnPaidChequeReturn->>'dblLedgCurTaxBaseAmount' AS DOUBLE PRECISION),
						dbl_ledg_cur_tax_amount = CAST(jsnPaidChequeReturn->>'dblLedgCurTaxAmount' AS DOUBLE PRECISION),

						dbl_base_cur_tax_base_amount = CAST(jsnPaidChequeReturn->>'dblBaseCurTaxBaseAmount' AS DOUBLE PRECISION),
						dbl_base_cur_tax_amount = CAST(jsnPaidChequeReturn->>'dblBaseCurTaxAmount' AS DOUBLE PRECISION) 
                        WHERE pk_paid_cheque_return_id = CAST(jsnPaidChequeReturn->>'binPaidChequeReturnId' AS BIGINT);
                    END IF;

                    --COMMIT;
                    objSpOutRes.sinSqlCustomStatus := 1::SMALLINT;
                    objSpOutRes.strSqlCustomStatusCode := 'SUCCESS';
                    objSpOutRes.strSqlCustomMessage := 'PaidChequeReturn Saved';
                    objSpOutRes.dtmCurrent = dtmCurrent;
                    
                    SELECT * FROM  fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
                    RAISE NOTICE 'jsnSqlRes: %', jsnSqlRes;
                    RETURN;
                EXCEPTION WHEN OTHERS THEN
                    ROLLBACK;
                    objSpOutRes.sinSqlCustomStatus := -1::SMALLINT;
                    objSpOutRes.strSqlCustomStatusCode := 'ERROR';
                    objSpOutRes.strSqlCustomMessage := 'PaidChequeReturn Save Failed';

                    objSpOutRes.strSqlCustomErrorType := 'DATABASE_SYSTEM_ERROR';

                    GET STACKED DIAGNOSTICS 
				        objSpOutRes.strSqlSysMessage := MESSAGE_TEXT,
                    	objSpOutRes.strSqlSysExceptionDetail := PG_EXCEPTION_DETAIL,
                    	objSpOutRes.strSqlSysExceptionHint := PG_EXCEPTION_CONTEXT;
                
                    SELECT * FROM  fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
                    RAISE EXCEPTION 'jsnSqlRes: %', jsnSqlRes;
                    RETURN;
                END;
            $$ LANGUAGE plpgsql;
        

------------------------------------------------------------------------------------------

            CREATE OR REPLACE PROCEDURE sp_delete_paid_cheque_return(binPaidChequeReturnId BIGINT, intSysActionId INT, binDeletedUserId BIGINT, jsnSqlRes OUT JSON) AS $$
                DECLARE
                    objSpOutRes TYP_SP_OUT_RES;
                    objFnCheckRes TYP_FN_CHECK_RES;
                    dtmCurrent TIMESTAMP WITH TIME ZONE;
                BEGIN
                    SELECT NOW() INTO dtmCurrent;

                    -- // FOREIGN KEY REFERENCES Checking in DB FUCTION(Delete)
                    SELECT * FROM  fn_check_foreignkey_referance('tbl_paid_cheque_return'::VARCHAR, 'pk_paid_cheque_return_id'::VARCHAR, binPaidChequeReturnId::BIGINT, '{}'::VARCHAR ARRAY) INTO objFnCheckRes;
                    IF objFnCheckRes.sin_sql_check_status = -1::SMALLINT THEN
                        objSpOutRes.sinSqlCustomStatus := -1::SMALLINT;
                        objSpOutRes.strSqlCustomStatusCode := 'ERROR';
                        objSpOutRes.strSqlCustomMessage := objFnCheckRes.vhr_sql_check_message;

                        objSpOutRes.strSqlCustomErrorType := 'DATABASE_VALIDATION_ERROR';

                        SELECT * FROM  fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
                        RAISE NOTICE 'jsnSqlRes: %', jsnSqlRes;
                        RETURN;
                    END IF;

                    -- // UPDATE Check already update another users in DB FUCTION(Update and Delete)
                    SELECT * FROM  fn_check_already_update_or_delete_another_user('paidcheque.tbl_paid_cheque_return'::VARCHAR, 'pk_paid_cheque_return_id'::VARCHAR, CAST(binPaidChequeReturnId AS BIGINT)::BIGINT, intSysActionId::INT) INTO objFnCheckRes;
                    IF objFnCheckRes.sin_sql_check_status = -1::SMALLINT THEN
                        objSpOutRes.sinSqlCustomStatus := -1::SMALLINT;
                        objSpOutRes.strSqlCustomStatusCode := 'ERROR';
                        objSpOutRes.strSqlCustomMessage := objFnCheckRes.vhr_sql_check_message;

                        objSpOutRes.strSqlCustomErrorType := 'DATABASE_VALIDATION_ERROR';

                        SELECT * FROM  fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
                        RAISE NOTICE 'jsnSqlRes: %', jsnSqlRes;
                        RETURN;
                    END IF;

                    --dtmDeleted = SELECT NOW();
                    UPDATE paidcheque.tbl_paid_cheque_return SET int_sys_action_id = -1::INT,
                                fk_deleted_user_id = binDeletedUserId,
                                dtm_deleted = dtmCurrent
                        WHERE pk_paid_cheque_return_id = binPaidChequeReturnId;

                    --COMMIT;
                    objSpOutRes.sinSqlCustomStatus := 1::SMALLINT;
                    objSpOutRes.strSqlCustomStatusCode := 'SUCCESS';
                    objSpOutRes.strSqlCustomMessage := 'PaidChequeReturn Deleted';
                    objSpOutRes.dtmCurrent = dtmCurrent;

                    SELECT * FROM fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
                    RAISE NOTICE 'jsnSqlRes: %', jsnSqlRes;
                    RETURN;
                EXCEPTION WHEN OTHERS THEN
                    ROLLBACK;
                    objSpOutRes.sinSqlCustomStatus := -1::SMALLINT;
                    objSpOutRes.strSqlCustomStatusCode := 'ERROR';
                    objSpOutRes.strSqlCustomMessage := 'PaidChequeReturn Delete Failed';

                    objSpOutRes.strSqlCustomErrorType := 'DATABASE_SYSTEM_ERROR';

                    GET STACKED DIAGNOSTICS 
				        objSpOutRes.strSqlSysMessage := MESSAGE_TEXT,
                    	objSpOutRes.strSqlSysExceptionDetail := PG_EXCEPTION_DETAIL,
                    	objSpOutRes.strSqlSysExceptionHint := PG_EXCEPTION_CONTEXT;

                    SELECT * FROM fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
                    RAISE EXCEPTION 'jsnSqlRes: %', jsnSqlRes;
                    RETURN;
                END;
            $$ LANGUAGE plpgsql;