Original query:

--Func
CREATE OR REPLACE FUNCTION public.ecs_inv_insert_update_op_package_commit_bill(_package_id bigint, _shift_id bigint, _patient_id bigint, _site_id bigint, _ordering_physician_id bigint, _billed_by bigint, _gross_total double precision, _net_total double precision, _package_total_tax double precision, _payment_details json, _actfrm_id bigint, _is_self integer, _order_date character varying, _is_expiry integer, _expiry_date character varying, _is_external integer, _is_zero_bill integer, _comments text, _discount_details json, _shift_date character varying, _commit_id bigint, _shift_check_override bit)
 RETURNS TABLE(status_value integer, status_text character varying, id bigint, billid bigint, url text, new_url text, bill_config_num character varying, receipt_config_num character varying)
 LANGUAGE plpgsql
AS $function$
/************************************************************************************************************
** Name: ecs_inv_insert_update_op_package_commit_bill
** Desc: For Committing OP Health Check Package Bill
** Owner: Priyanka S D
** DATE: 04-09-2018

**** Input Param ******
** _package_id : Health Check Package ID
** _shift_id : Logged in Shift ID
** _patient_id : Appointment Details in JSON
** _site_id : Hospital ID
** _ordering_physician_id : Package Ordering Physician Id
** _billed_by : Logged in UserId
** _gross_total : Bill amount
** _net_total : Total Payable Bill Amount
** _package_total_tax : Package Tax Amount
** _payment_details : Patient details in JSON
** _actfrm_id : Package Activation From ID
** _is_self : If no ordering physician THEN 1 ELSE 0
** _order_date : Package order DATE
** _is_expiry : If Package has expiry DATE
** _expiry_date : Package expiry DATE
** _is_external : Is external physician
** _is_zero_bill : If Zero Bill Package THEN 1 ELSE 0
** _comments : comments
** _discount_details : Discount details (IF any)
** _shift_date : Logged In Shift DATE
** _commit_id :Commit ID
** _shift_check_override : Shift Override

*************************************************************************************************************
** Example to call
*******************

SELECT * FROM ecs_inv_insert_update_op_package_commit_bill(10, 390, 10988, 1, 133, 2, 4000.00, 4000.00, 0, '[{"paymentMode":1,"amount":4000}]'::JSON, 25, 0, '09/08/2018', 0, null, 0, 0, 'test', '[]'::JSON, '2018-09-08', 4844, 0::BIT);

**************************************************************************************************************
** Change History
*****************
** SL   DATE	    	Author   		Reviewer     	Description
** --   --------   	-------   		--------    	-----------------------------
** 01   2018-10-08	Shidramshetti SP			Adding Sales Price to Bill Item Table
** 02	2018 Oct 31	Arun KS					Modifying bill realization perform
** 03	2018 Nov 06	Arun KS					Selecting receipt id from relaization function
** 04	2019 Jan 10	Priyanka S D				Corrected bill type while performing realizaion function
**********************************************************************************************************/

DECLARE
	num_rows_ INTEGER :=0;
	customerid_ BIGINT;
	phy_name_ VARCHAR;
	patientname_ VARCHAR;
	username_ VARCHAR;
	patientmpi_ VARCHAR;
	patientage_ VARCHAR;
	sexid_ INTEGER;
	billid_ BIGINT;
	billno_ BIGINT;
	receiptno_ BIGINT;
	receiptid_ BIGINT;
	recconfigno_ VARCHAR;
	pat_gender_ VARCHAR;
	package_name_ VARCHAR;
	pack_validity_ INTEGER;
	pack_total_ NUMERIC;
	def_enc_id_ BIGINT;
	pat_pack_id_ BIGINT;
	qty_credit_ INTEGER;
	dis_id_ BIGINT;
	is_variable_ BIT;
	disc_perc_ NUMERIC;
	overrun_perc_ NUMERIC;
	credit_amt_ NUMERIC;
	dis_amt_ NUMERIC;
	self_amt_ NUMERIC;
	bill_config_no_ VARCHAR;
	advance_amt_ NUMERIC;
	adj_id_ BIGINT;
	pack_item_tax_ NUMERIC;
	cash_ NUMERIC;
	card_ NUMERIC;
	cheque_ NUMERIC;
	wallet_ NUMERIC;
	bank_transfer_amt_ NUMERIC;
	vendor_name_ VARCHAR;
	reciept_total_ NUMERIC;
	dis_rsn_ VARCHAR;
	det_ JSON;
	is_auto_schedule_ BIT;
	ins_perc_ NUMERIC;
	self_perc_ NUMERIC;
	current_bal_ NUMERIC;
	online_third_party_amt_ NUMERIC;
	notify_shift_change_ BIT;
	adv_details_ JSON;
	
BEGIN
	-- Site settings for Shift Change AND consult type
	SELECT	notify_shift_change
	FROM 	ecs_customer_site_settings
	WHERE 	customer_site_id=_site_id
		AND is_active=1::BIT
	INTO 	notify_shift_change_;

	-- Check Shift Change
	IF EXISTS (SELECT 1 FROM ecs_inp_shifts
		WHERE 	shift_id=_shift_id
			AND notify_shift_change_=1::BIT
			AND _shift_check_override=0::BIT
			AND CASE
				WHEN next_day=0::BIT
					THEN NOW()::TIMESTAMP WITHOUT TIME ZONE NOT BETWEEN (_shift_date::DATE + shift_from::TIME)
							AND (_shift_date::DATE + shift_till::TIME)
				WHEN next_day=1::BIT
					THEN NOW()::TIMESTAMP WITHOUT TIME ZONE NOT BETWEEN (_shift_date::DATE + shift_from::TIME)
						AND (_shift_date::DATE + 1 + shift_till::TIME)
			END
			AND site_id=_site_id
			AND is_active=1::BIT)
	THEN
		RETURN	QUERY
		SELECT 	0::INTEGER AS status_value,
			'Logged in Shift expired, Please Log in again to continue.'::VARCHAR AS status_text,
			NULL::BIGINT,
			NULL::BIGINT,
			NULL::TEXT AS url,
			NULL::TEXT AS new_url,
			NULL::VARCHAR AS bill_config_num,
			NULL::VARCHAR AS receipt_config_num;

	-- Check Patient Details
	ELSIF(_patient_id is NULL)
	THEN
		RETURN	QUERY
		SELECT 	0::INTEGER AS status_value,
			'Patient details NOT found! Reselect the patient AND try again.'::VARCHAR AS status_text,
			NULL::BIGINT,
			NULL::BIGINT,
			NULL::TEXT AS url,
			NULL::TEXT AS new_url,
			NULL::VARCHAR AS bill_config_num,
			NULL::VARCHAR AS receipt_config_num;

	-- Check Patient Alive
	ELSIF EXISTS(SELECT 1 FROM ecs_patients pa
		WHERE 	pa.patient_id=_patient_id
			AND pa.is_deceased is TRUE
			AND pa.is_active=1::BIT)
	THEN
		RETURN 	QUERY
		SELECT 	0::INTEGER AS status_value,
			'The Patient Is Not Alive, Package Cannot Be Booked'::VARCHAR AS status_text,
			NULL::BIGINT,
			NULL::BIGINT,
			NULL::TEXT AS url,
			NULL::TEXT AS new_url,
			NULL::VARCHAR AS bill_config_num,
			NULL::VARCHAR AS receipt_config_num;

	ELSE
		-- Payment Details From Json
		SELECT	SUM(CASE WHEN b.a->>'paymentMode'=1::TEXT THEN (b.a->>'amount')::NUMERIC ELSE 0::NUMERIC END),
			SUM(CASE WHEN b.a->>'paymentMode'=2::TEXT THEN (b.a->>'amount')::NUMERIC ELSE 0::NUMERIC END),
			SUM(CASE WHEN b.a->>'paymentMode'=3::TEXT THEN (b.a->>'amount')::NUMERIC ELSE 0::NUMERIC END),
			SUM(CASE WHEN b.a->>'paymentMode'=4::TEXT THEN (b.a->>'amount')::NUMERIC ELSE 0::NUMERIC END),
			SUM(CASE WHEN b.a->>'paymentMode'=5::TEXT THEN (b.a->>'amount')::NUMERIC ELSE 0::NUMERIC END),
			SUM(CASE WHEN b.a->>'paymentMode'=6::TEXT THEN (b.a->>'amount')::NUMERIC ELSE 0::NUMERIC END),
			SUM(CASE WHEN b.a->>'paymentMode'=7::TEXT THEN (b.a->>'amount')::NUMERIC ELSE 0::NUMERIC END),
			SUM(CASE WHEN b.a->>'paymentMode'=9::TEXT THEN (b.a->>'amount')::NUMERIC ELSE 0::NUMERIC END),
			SUM(CASE WHEN b.a->>'paymentMode'=10::TEXT THEN (b.a->>'amount')::NUMERIC ELSE 0::NUMERIC END),
			SUM(CASE WHEN (b.a->>'paymentMode')::INTEGER in (1,2,3,4,7,9,10) THEN (b.a->>'amount')::NUMERIC ELSE 0::NUMERIC END)
		FROM	(SELECT JSON_ARRAY_ELEMENTS(_payment_details::JSON)AS a)b
		INTO 	cash_,card_,cheque_,advance_amt_,self_amt_,credit_amt_,wallet_,online_third_party_amt_,bank_transfer_amt_,reciept_total_;

		-- Fetch Patient Current Ledger Balance
		SELECT 	COALESCE(p.current_bal::NUMERIC,0)
		FROM 	ecs_ac_patient_ledger p
		WHERE	p.patient_id=_patient_id
			AND p.site_id=_site_id
			AND p.account_type=2
			AND p.is_active=1::BIT
		INTO	current_bal_;

		-- Advance Amount Validation
		-- Advance Adjusting Amount <= Ledger Current Balance
		IF (CASE WHEN COALESCE(advance_amt_,0)=0 THEN TRUE
			 WHEN (COALESCE(advance_amt_,0)>0 AND COALESCE(advance_amt_,0) <= current_bal_) THEN TRUE
			 ELSE FALSE END)
		THEN
			-- Fetching Advance Details
			SELECT 	b.a->>'advDetails' AS adv
			FROM	(SELECT JSON_ARRAY_ELEMENTS(_payment_details::JSON) AS a)b
			WHERE 	b.a->>'advDetails' is NOT NULL
			INTO	adv_details_;

			-- Fetching Discount Details
			IF (_discount_details is NOT NULL)
			THEN
				SELECT 	SUM((b.a->>'mangTotalAmount')::NUMERIC)
				FROM	(SELECT JSON_ARRAY_ELEMENTS(_discount_details::JSON)AS a)b
				WHERE  	b.a->>'parentDiscId'=1::TEXT
				INTO 	dis_amt_;

				SELECT 	(b.a->>'discRsn')::VARCHAR
				FROM	(SELECT JSON_ARRAY_ELEMENTS(_discount_details::JSON)AS a)b
				INTO 	dis_rsn_;
			END IF;

			-- Amount Validation
			-- Gross + Tax = Discount + Payment Total
			IF((CASE WHEN _is_zero_bill=1 THEN 0 ELSE _gross_total::NUMERIC END + COALESCE(_package_total_tax::NUMERIC,0))
				= (COALESCE(dis_amt_,0) + COALESCE(credit_amt_,0) + COALESCE(self_amt_,0) + COALESCE(reciept_total_,0)))
			THEN
				-- Create Encounter If Not Exists For Same Day Else Fetch
				IF NOT EXISTS(SELECT 1 FROM ecs_patient_encounter
					WHERE	patient_id=_patient_id
						AND level_of_care=2
						AND site_id=_site_id
						AND created_on::DATE=NOW()::DATE
						AND is_active=1::BIT)
				THEN
					-- Creating Patient Encounter
					INSERT 	INTO ecs_patient_encounter
					(
						encounter_date,patient_id,physician_id,created_by,created_on,is_active,
						site_id,level_of_care,is_hospital_patient,encounter_type_id,is_ext_phy
					)
					VALUES
					(
						NOW(),_patient_id,_ordering_physician_id,_billed_by,NOW(),1::BIT,
						_site_id,2,0::SMALLINT,9,_is_external::BIT
					)
					RETURNING encounter_id INTO def_enc_id_;

					-- Generating Encounter No
					PERFORM ecs_ambulatory_get_display_encounter(def_enc_id_,_site_id);

				ELSE
					SELECT	encounter_id
					FROM	ecs_patient_encounter
					WHERE	patient_id=_patient_id
						AND level_of_care=2
						AND site_id=_site_id
						AND created_on::DATE=NOW()::DATE
						AND is_active=1::BIT
					INTO	def_enc_id_;
				END IF;

				-------------------------- Patient Details --------------------------
				-- Patient Name, MPI
				SELECT 	(COALESCE(first_name,'')||COALESCE(' '||last_name,''))::CHARACTER VARYING,mpi1
				FROM 	ecs_patients
				WHERE 	patient_id=_patient_id
					AND is_active=1::BIT
				INTO 	patientname_,patientmpi_;

				-- Patient Age and Gender
				SELECT 	age,sex_id
				FROM 	ecs_patient_demograhics
				WHERE 	patient_id=_patient_id
					AND is_active=1::BIT
				INTO 	patientage_,sexid_;

				SELECT 	description
				FROM 	ecs_master_sex
				WHERE 	sex_id=sexid_
				INTO 	pat_gender_;

				-- Physician Name
				IF (_is_external=0)
				THEN
					SELECT 	COALESCE(user_first_name,'')||COALESCE(' '||user_last_name,'')
					FROM 	ecs_users
					WHERE 	user_id=_ordering_physician_id
					INTO 	phy_name_;
				ELSE
					SELECT 	COALESCE(first_name,'')||COALESCE(last_name,'')
					FROM 	ecs_eprescribing_refferal_physicians
					WHERE 	eprescribing_refferal_physician_id=_ordering_physician_id
					INTO   	phy_name_;
				END IF;

				-- Customer ID
				SELECT 	customer_id
				FROM 	ecs_customer_site
				WHERE 	customer_site_id=_site_id AND is_active=1::BIT
				INTO 	customerid_;

				-- Logged IN User Name
				SELECT 	user_first_name||COALESCE(' '||user_last_name,'')
				FROM 	ecs_users
				WHERE 	user_id=_billed_by
				INTO 	username_;
				-------------------------- Patient Details --------------------------

				-------------------------- Package Details --------------------------
				SELECT 	COALESCE(is_variable,0::BIT),
					is_auto_schedule,
					package_name,
					COALESCE(contra_dis_id,0)
				FROM  	ecs_inv_health_check_package
				WHERE 	health_pack_id=_package_id
					AND site_id=_site_id
					AND is_active=1::BIT
				INTO 	is_variable_,is_auto_schedule_, package_name_, dis_id_;

				SELECT 	validity_days
				FROM 	ecs_inv_health_check_package_activation_from
				WHERE 	site_id=_site_id
					AND package_id=_package_id
					AND is_activated=1::BIT
					AND activation_from_id=_actfrm_id
				INTO 	pack_validity_;

				SELECT	SUM((COALESCE(gross_amount,0)
						+ CASE 	WHEN is_inclusive_of_tax=1::BIT
								THEN COALESCE(tax_amount,0)
							ELSE 0
						  END) * (COALESCE(qty,1)))::NUMERIC,
					SUM(COALESCE(tax_amount,0) * COALESCE(qty,0))::NUMERIC
				FROM	ecs_inv_health_check_package_item
				WHERE	health_pack_id=_package_id
					AND activation_from_id=_actfrm_id
					AND is_active=1::BIT
					AND site_id=_site_id
				INTO 	pack_total_, pack_item_tax_;

				-- Package Overrun and Package Discount Percentage
				IF (COALESCE(pack_total_,0)> COALESCE(_gross_total,0))
				THEN
					disc_perc_ = (COALESCE(pack_total_,0)-COALESCE(_gross_total,0))::NUMERIC
							/ COALESCE(pack_total_,0)::NUMERIC;

				ELSIF (COALESCE(pack_total_,0)< COALESCE(_gross_total,0))
				THEN
					overrun_perc_ = (COALESCE(_gross_total,0)-COALESCE(pack_total_,0))::NUMERIC
								/ COALESCE(pack_total_,0)::NUMERIC;
				END IF;

				-- Credit/Self Amount Percentage
				IF (COALESCE(credit_amt_,0)>0)
				THEN
					ins_perc_=(COALESCE(credit_amt_,0))/(COALESCE(_net_total,0)
							+ COALESCE (_package_total_tax,0)-COALESCE (dis_amt_,0) );
				END IF;

				IF (COALESCE(self_amt_,0)>0)
				THEN
					self_perc_=(COALESCE(self_amt_,0))/(COALESCE(_net_total,0)
							+ COALESCE (_package_total_tax,0)-COALESCE (dis_amt_,0) );
				END IF;
				-------------------------- Package Details --------------------------

				UPDATE 	ecs_inv_health_check_package
				SET 	is_used=1::BIT
				WHERE 	health_pack_id=_package_id
					AND site_id=_site_id
					AND is_used=0::BIT
					AND is_active=1::BIT;

				-- Patient Package Mapping
				INSERT 	INTO ecs_inv_patient_op_package_mapping
				(
					patient_id,patient_name,age,gender,mpino,dob,site_id,default_encounter_id,package_id,
					package_name,pack_booking_date,pack_valid_up_to,pack_net_amount,shift_id,lab_order_status,rad_order_status,
					lab_release_status,rad_release_status,created_by,created_on,is_cancelled,is_active,sex_id,pack_type,
					order_date,ordering_phy_id,contra_dis_id,is_expiry,expiry_date,act_frm_id,is_variable,
					variable_charge,package_price,total_pack_item_tax_amount,pack_gross_amount,shift_date
				)
				VALUES
				(
					_patient_id,patientname_,patientage_,pat_gender_,patientmpi_,NULL,_site_id,def_enc_id_,_package_id,
					package_name_,NOW(),pack_validity_,pack_total_,_shift_id,0,0,
					0,0,_billed_by,NOW(),0::BIT,1::BIT,sexid_,2,
					_order_date::DATE,_ordering_physician_id,dis_id_,_is_expiry::BIT,_expiry_date::DATE,_actfrm_id,is_variable_,
					_gross_total,_gross_total,pack_item_tax_,_gross_total,_shift_date::DATE
				)
				RETURNING patient_package_id INTO pat_pack_id_;

				-- Package Tax Seggregation Entry
				PERFORM ecs_inp_insert_update_op_package_item_tax
					(
						def_enc_id_::BIGINT,_site_id::BIGINT,23::BIGINT,
						_package_id::BIGINT,pat_pack_id_::BIGINT
					);

				-- Patient Package Ledger (Package Item Level Entry For Consumption Status Update)
				INSERT 	INTO ecs_inv_patient_op_package_ledger
				(
					site_id,
					patient_id,patient_package_id,package_item_id,ledger_type,credit,debit,balance,
					created_by,created_on,is_active,package_item_type,encounter_id,
					is_auto_sch,
					act_frm_id
				)
				SELECT 	DISTINCT ON (pi.health_pack_item_id) _site_id,
					_patient_id,pat_pack_id_,pi.health_pack_item_id,1,pi.qty,0,pi.qty,
					_billed_by,NOW(),1::BIT,pi.charge_type_id,def_enc_id_,
					CASE
						WHEN p.is_auto_schedule=1::BIT
							THEN 1::BIT
						ELSE 0::BIT
					END,
					_actfrm_id
				FROM 	ecs_inv_health_check_package p
					LEFT JOIN ecs_inv_health_check_package_item pi
						ON pi.health_pack_id=p.health_pack_id
						AND pi.is_active=1::BIT
					LEFT JOIN ecs_inv_health_check_package_activation_from a
						ON a.package_id=p.health_pack_id
						AND a.activation_from_id=pi.activation_from_id
						AND a.is_active=1::BIT
				WHERE 	p.health_pack_id=_package_id
					AND p.site_id=_site_id
					AND a.is_activated=1::BIT
					AND a.activation_from_id=_actfrm_id
					AND pi.is_unlimited=0::BIT
				GROUP 	BY pi.health_pack_item_id,p.health_pack_id;

				-- Package Items Quantity Updation For Patient Package Mapping
				SELECT 	SUM(COALESCE(l.balance,0))::NUMERIC
				FROM 	ecs_inv_patient_op_package_ledger l
				WHERE  	l.encounter_id=def_enc_id_
					AND l.patient_package_id=pat_pack_id_
					AND l.patient_id=_patient_id
					AND l.site_id=_site_id
					AND l.is_active=1::BIT
				INTO 	qty_credit_;

				-- Patient Package Ledger Item Collection (Package Item Level Entry For Amount Update)
				INSERT 	INTO ecs_inv_op_package_patient_item_collection
				(
					pat_pack_ledger_id,patient_package_id,site_id,package_item_id,package_item_name,
					charge_master_item,charge_master_type,qty,
					gross_amount,
					tax_amount,
					net_amount,
					package_item_status,
					activation_from_id,is_unlimited,is_inclusive_tax,is_group_level,
					procedure_id,created_by,created_on,is_active,encounter_id,app_phy_id,specialisation_id,
					scheduled_on,
					is_phy_spec_grp_level
				)
				SELECT 	a.pat_pack_ledger_id,a.pat_pack_id_,a.site_id,a.health_pack_item_id,a.package_item_name,
					a.charge_master_id,a.charge_type_id,1,
					a.gross_amount/(COALESCE(a.qty,1)),
					/**Item net amount=(item_gross_amount + item_net_amount) **/
					/**Tax= Item net amount *(Package_tax/Gross_total)**/
					CASE 	WHEN _is_zero_bill=1
							THEN 0::NUMERIC
						ELSE
							(
								(CASE 	WHEN COALESCE(disc_perc_,0)>0
										THEN (COALESCE(a.item_net,0)::NUMERIC
											- (COALESCE(a.item_net,0)::NUMERIC
											* COALESCE(disc_perc_,0)))::NUMERIC
									WHEN COALESCE(overrun_perc_,0)>0
										THEN (COALESCE(a.item_net,0)::NUMERIC
											+ (COALESCE(a.item_net,0)::NUMERIC
											* COALESCE(overrun_perc_,0)))::NUMERIC
									ELSE
										COALESCE(a.item_net,0)::NUMERIC
								END)
								/(COALESCE(a.qty,1))
							)
							* (COALESCE(_package_total_tax::NUMERIC,0)
								/ COALESCE(_gross_total::NUMERIC,0))::NUMERIC
					END,
					/** Net amount = Item net amount+ (Item net amount *(Package_tax/Gross_total)) **/
					CASE 	WHEN _is_zero_bill=1
							THEN 0::NUMERIC
						ELSE
							(
								(CASE 	WHEN COALESCE(disc_perc_,0)>0
										THEN (COALESCE(a.item_net,0)::NUMERIC
											- (COALESCE(a.item_net,0)::NUMERIC
											* COALESCE(disc_perc_,0)))::NUMERIC
									WHEN COALESCE(overrun_perc_,0)>0
										THEN (COALESCE(a.item_net,0)::NUMERIC
											+ (COALESCE(a.item_net,0)::NUMERIC
											* COALESCE(overrun_perc_,0)))::NUMERIC
									ELSE COALESCE(a.item_net,0)::NUMERIC
								END)
								/ (COALESCE(a.qty,1))
							)
							+
							(
								(CASE 	WHEN COALESCE(disc_perc_,0)>0
										THEN (COALESCE(a.item_net,0)::NUMERIC
											- (COALESCE(a.item_net,0)::NUMERIC
											* COALESCE(disc_perc_,0)))::NUMERIC
									WHEN COALESCE(overrun_perc_,0)>0
										THEN (COALESCE(a.item_net,0)::NUMERIC
											+ (COALESCE(a.item_net,0)::NUMERIC
											* COALESCE(overrun_perc_,0)))::NUMERIC
									ELSE COALESCE(a.item_net,0)::NUMERIC
								END)
								/ (COALESCE(a.qty,1))
							)
							* (COALESCE(_package_total_tax::NUMERIC,0)
								/ COALESCE(_gross_total::NUMERIC,0))::NUMERIC
					END,
					CASE
						WHEN is_auto_schedule_=1::BIT
							THEN 2
						ELSE 1
					END,
					a._actfrm_id,a.is_unlimited,a.is_inclusive_of_tax,a.is_group_level,
					a.procedure_id,a._billed_by,NOW(),1::BIT,def_enc_id_,a.physician_id,a.specialisation_id,
					CASE
						WHEN is_auto_schedule_=1::BIT
							THEN NOW()
						ELSE NULL
					END,
					a.sts
				FROM
				(	SELECT	generate_series(1,qty::INTEGER),l.pat_pack_ledger_id,pat_pack_id_,_site_id,
						p.health_pack_item_id,p.package_item_name,
						p.charge_master_id,p.charge_type_id,1,
						(COALESCE(p.gross_amount,0))*(COALESCE(p.qty,1))::NUMERIC AS gross_amount,
						(
							(CASE	WHEN p.is_inclusive_of_tax=1::BIT
									THEN COALESCE(p.tax_amount,0)
								ELSE 0
							END)
							* COALESCE(p.qty,1)
						)::NUMERIC AS tax_amount,
						(
							(COALESCE(p.gross_amount,0)::NUMERIC
								+ CASE 	WHEN p.is_inclusive_of_tax=1::BIT
										THEN  COALESCE(p.tax_amount,0)::NUMERIC
									ELSE 0
								END
							)
							* COALESCE(p.qty,1)
						)::NUMERIC AS item_net,
						p.net_amount AS net_amount,
						1,_actfrm_id,p.is_unlimited,p.is_inclusive_of_tax,p.is_group_level,
						p.procedure_id,_billed_by,NOW(),1::BIT,o.physician_id,p.specialisation_id,p.qty,
						CASE 	WHEN (p.is_specialisation=0::BIT AND p.is_group_level=0::BIT)
								THEN 1
							WHEN p.is_specialisation=1::BIT
								THEN 2
							WHEN p.is_group_level=1::BIT
								THEN 3
						END  AS sts,
						p.site_id
					FROM 	ecs_inv_health_check_package_item p
						LEFT JOIN ecs_inv_patient_op_package_ledger l
							ON l.package_item_id=p.health_pack_item_id
						LEFT JOIN ecs_inp_op_physician_consultant_charge_master o
							ON o.op_consultant_id=p.charge_master_id
							AND p.charge_type_id=1
							AND p.charge_master_id!=0
							AND o.site_id=_site_id
					WHERE 	p.health_pack_id=_package_id
						AND p.activation_from_id=_actfrm_id
						AND p.site_id=_site_id
						AND p.is_active=1::BIT
						AND l.encounter_id=def_enc_id_
				)a;

				-- Insert Into Bill Table
				INSERT 	INTO ecs_inv_bill
				(
					site_id,level_of_care,bill_type,patient_id,encounter_id,ordering_phy_id,ordering_phy,
					net_total,
					total_tax,
					gross_total,
					payment_status_id,created_by,created_on,patient_name,age,sex_id,shift_id,
					bill_transaction_type,is_active,billed_from,package_type,
					cant_find,
					is_zero_bill,reason,amount_paid, total_contra_amt, advance_amount,
					balance,shift_date,commit_trans_id
				)
				VALUES
				(
					_site_id,2,2,_patient_id,def_enc_id_,_ordering_physician_id,phy_name_,
					CASE 	WHEN _is_zero_bill=1
							THEN '0'
						ELSE (_net_total + COALESCE (_package_total_tax,0)-COALESCE (dis_amt_,0))
					END,
					CASE 	WHEN _is_zero_bill=1
							THEN '0'
						ELSE _package_total_tax
					END,
					CASE 	WHEN COALESCE (_is_zero_bill,0)=1 THEN '0'
						ELSE _gross_total
					END,
					1,_billed_by,NOW(),patientname_,patientage_,sexid_,_shift_id,
					1,1::BIT,3,1,
					CASE 	WHEN _is_external=1
							THEN 1::BIT
						ELSE 0::BIT
					END,
					_is_zero_bill::BIT,_comments::TEXT, reciept_total_, dis_amt_, advance_amt_,
					(COALESCE(self_amt_,0)+COALESCE(credit_amt_,0)), _shift_date::DATE,_commit_id
				)
				RETURNING bill_id INTO billid_;

				-- Generating Bill Config Number
				SELECT	b.bill_num,b.bill_config_num
				FROM	ecs_inv_get_bill_no
					(
						billid_,_site_id,
						CASE 	WHEN ((COALESCE(self_amt_,0) + COALESCE(credit_amt_,0))>0)
								THEN 34
							ELSE 22
						END
					) b
				INTO	billno_,bill_config_no_;

				-- Invoice Ledger Entry
				PERFORM ecs_bill_insert_update_patient_ledger
					(
						_patient_id, 1,_site_id,
						(COALESCE(_net_total,0) + COALESCE (_package_total_tax,0)
							- COALESCE (dis_amt_,0))::DOUBLE PRECISION,
						billid_, receiptid_, _billed_by, _shift_id, 6,
						'OP Package Invoice'::VARCHAR, NULL::BIGINT, _commit_id,billid_,2,NULL::BIGINT,NULL::BIGINT
					);

				-- Insert Into Bill Item Table
				INSERT 	INTO ecs_inv_bill_item
				(
					bill_id,site_id,level_of_care,bill_type,bill_no,item_id,item_name,qty,
					unit_price,
					tax_Amount,
					created_by,created_on,is_active,charge_master_type,payment_status,billed_date,
					net_value,
					mpi,
					bill_config_no,patient_name,ordering_physician,user_name,
					bill_trans_type,is_package_item,pack_item_coll_id,
					net_amount,
					item_contra_amt,shift_date,sales_price
				)
				SELECT 	billid_,_site_id,2,2,billno_,charge_master_item,ic.package_item_name,ic.qty,
					CASE
						WHEN _is_zero_bill=1
							THEN '0'
						ELSE  ic.gross_amount
					END,
					CASE
						WHEN _is_zero_bill=1
							THEN '0'
						ELSE ic.tax_amount
					END,
					_billed_by,NOW(),1::BIT,charge_master_type,1,NOW(),
					CASE
						WHEN _is_zero_bill=1
							THEN '0'
						ELSE ic.gross_amount
					END,
					patientmpi_,bill_config_no_,patientname_,phy_name_,username_,
					1,1::BIT,pat_pack_item_col_id,
					CASE
						WHEN _is_zero_bill=1
							THEN '0'
						ELSE ic.net_amount
					END,
					0,_shift_date::DATE,
					CASE
						WHEN _is_zero_bill=1
							THEN '0'
						ELSE ic.gross_amount
					END
				FROM 	ecs_inv_op_package_patient_item_collection ic
					LEFT JOIN ecs_inv_health_check_package_item pi
						ON ic.package_item_id=pi.health_pack_item_id
						AND pi.is_active=1::BIT
				WHERE 	patient_package_id=pat_pack_id_
					AND ic.encounter_id=def_enc_id_
					AND ic.site_id=_site_id
					AND ic.is_active=1::BIT;

				-- Performing Realization Function
				SELECT 	receiptid
				FROM	ecs_fin_insert_update_bill_realization
				(
					billid_, _site_id, _shift_id, _patient_id, 
					NULL::BIGINT, _ordering_physician_id,
					_gross_total::NUMERIC,_package_total_tax::NUMERIC,
					(_net_total + COALESCE (_package_total_tax,0))::NUMERIC,
					reciept_total_::NUMERIC,_billed_by, 1, 
					CASE 	
						WHEN ((self_amt_ <> 0) or (credit_amt_ <> 0)) 
							THEN 5 -- OP Credit Bill
						ELSE 2 -- OP Cash Bill
					END, 
					1, advance_amt_::NUMERIC, 3, 7,
					CASE WHEN COALESCE(advance_amt_,0)=0 THEN 0 ELSE 1 END,
					_shift_date::DATE, _commit_id, 16, _payment_details::JSON, customerid_
				)
				INTO	receiptid_;

				-- Discount Entry
				IF (COALESCE(dis_amt_,0)<>0)
				THEN
					-- Enounter Discount Item Entry
					PERFORM ecs_inv_insert_update_op_order_discount
						(billid_::BIGINT, _discount_details::JSON);

					-- Discount Amount Division To Item Level
					PERFORM ecs_inv_update_op_bill_discount
					(
						billid_,5,_site_id,0::DOUBLE PRECISION,dis_amt_::DOUBLE PRECISION,_billed_by,
						_shift_id,_ordering_physician_id,_patient_id,def_enc_id_,0::BIGINT,1,
						dis_rsn_::VARCHAR,1
					);
				END IF;

				-- Amount Updation For Bill Items
				UPDATE	ecs_inv_bill_item
				SET	package_discount=CASE
								WHEN (COALESCE(pack_total_,0) > COALESCE(_gross_total,0))
									THEN (COALESCE(unit_price::NUMERIC,0)+COALESCE(tax_amount::NUMERIC,0)
										- COALESCE(net_amount::NUMERIC,0))::NUMERIC
							END,
					package_markup=	CASE
								WHEN (COALESCE(pack_total_,0) < COALESCE(_gross_total,0))
									THEN (COALESCE(net_amount::NUMERIC,0)
										- COALESCE(unit_price::NUMERIC,0))::NUMERIC
							END,
					patient_payable=(COALESCE(net_amount::NUMERIC,0) * (COALESCE(self_perc_,0)))::NUMERIC,
					payer_payable=(COALESCE(net_amount::NUMERIC,0) * (COALESCE(ins_perc_,0)))::NUMERIC
				WHERE	bill_id=billid_
					AND is_active=1::BIT;

				-- Bill No/Receipt No Updation For Package Mapping
				UPDATE 	ecs_inv_patient_op_package_mapping op
				SET 	trans_no=b.bill_no,
					trans_config_no=b.bill_config_no,
					fin_period_id=b.fin_period_id,
					serial_order_id=b.serial_order_id,
					bill_id=billid_,
					receipt_id=receiptid_,
					total_pack_items_qty=qty_credit_
				FROM	ecs_inv_bill b
				WHERE 	patient_package_id=pat_pack_id_
					AND b.bill_id=billid_
					AND b.site_id=op.site_id
					and op.is_active=1::BIT;

				-- Updating Bill ID In Collection Table
				UPDATE 	ecs_inv_op_package_patient_item_collection
				SET 	bill_id=billid_
				WHERE 	patient_package_id=pat_pack_id_
					AND encounter_id=def_enc_id_
					AND site_id=_site_id
					and is_active=1::BIT;

				-- Updating Bill Entry
				UPDATE 	ecs_inv_bill
				SET 	receipt_config_no=recconfigno_,
					receipt_no=receiptno_,
					receipt_id=receiptid_,
					adjustment_id=adj_id_,
					advance_amount=advance_amt_::VARCHAR,
					bill_type= CASE WHEN (self_amt_ <> 0) THEN 5
							WHEN (credit_amt_ <> 0) THEN 5
						   ELSE bill_type
						END,
					total_patient_payable=self_amt_,
					total_payer_payable=credit_amt_
				WHERE 	bill_id=billid_
					AND is_active=1::BIT;

				GET DIAGNOSTICS num_rows_ = ROW_COUNT;

				-- Event Store
				-- ERP Integration Added By Jeswin ON 2016-11-03 **/
				IF EXISTS (SELECT 1 FROM ecs_customer_site
					WHERE 	is_erp_enable = 1::BIT
						AND is_active = 1::BIT
						AND customer_site_id  = _site_id)
				THEN
					/** Sales Invoice Trigger **/
					SELECT '{"billId":'||billid_||',"billType":'||2||',"customerId":'||
						customerid_||',"siteId":'||_site_id||',"createdBy":'||
						_billed_by||',"integrationType":'||50||'}'
					INTO 	det_;

					PERFORM ecs_insert_update_integration_events_data(det_);
				END IF;
				-- End Added By Jeswin ON 2016-11-03 ERP Integration **/

				IF num_rows_>0
				THEN
					RETURN 	QUERY
					SELECT 	1 AS status_value,
						'Payment Done Successfully'::VARCHAR AS status_text,
						pat_pack_id_,
						billid_,
						'pdf/billid/'||billid_||'/printid/31/siteid/'||_site_id||'/customerid/'||customerid_||'/receiptid/'
							||receiptid_||'/isreprint/0/op/package/bill'::TEXT AS url,
						'pdf/billid/'||billid_||'/printid/31/siteid/'||_site_id||'/customerid/'||customerid_||'/receiptid/'
							||receiptid_||'/isreprint/0/op/package/bill'::TEXT AS new_url,
						bill_config_no_::VARCHAR AS bill_config_num,
						recconfigno_::VARCHAR AS receipt_config_num;

				ELSE
					RETURN 	QUERY
					SELECT 	0 AS status_value,
						'Payment Cant be Done..!'::VARCHAR AS status_text,
						NULL::BIGINT,
						NULL::BIGINT,
						NULL::TEXT AS url,
						NULL::TEXT AS new_url,
						NULL::VARCHAR AS bill_config_num,
						NULL::VARCHAR AS receipt_config_num;
				END IF;
			ELSE
				RETURN 	QUERY
				SELECT 	0 AS status_value,
					'Amount Mismatch'::VARCHAR AS status_text,
					NULL::BIGINT,
					NULL::BIGINT,
					NULL::TEXT AS url,
					NULL::TEXT AS new_url,
					NULL::VARCHAR AS bill_config_num,
					NULL::VARCHAR AS receipt_config_num;
			END IF;

		ELSE
			RETURN 	QUERY
			SELECT 	0 AS status_value,
				'Advance Amount Mismatch'::VARCHAR AS status_text,
				NULL::BIGINT,
				NULL::BIGINT,
				NULL::TEXT AS url,
				NULL::TEXT AS new_url,
				NULL::VARCHAR AS bill_config_num,
				NULL::VARCHAR AS receipt_config_num;
		END IF;
	END IF;

	-- Catch Block For Exception Handling (Form Ledger Function)
	EXCEPTION
		WHEN SQLSTATE 'E0001' THEN
			RETURN 	QUERY
			SELECT	0 AS status_value,
				(SQLERRM::VARCHAR[])[1] as status_text,
				NULL::BIGINT,
				NULL::BIGINT,
				NULL::TEXT AS url,
				NULL::TEXT AS new_url,
				NULL::VARCHAR AS bill_config_num,
				NULL::VARCHAR AS receipt_config_num;

		WHEN others THEN
			RETURN 	QUERY
			SELECT	0 AS status_value,
				SQLERRM::VARCHAR AS status_text,
				NULL::BIGINT,
				NULL::BIGINT,
				NULL::TEXT AS url,
				NULL::TEXT AS new_url,
				NULL::VARCHAR AS bill_config_num,
				NULL::VARCHAR AS receipt_config_num;
END;
$function$
;