The create or replace procedure peis_sign (TASKID_IN Varchar2) in the as
N_ registration ID Number (18);
N_ personnel ID Number (18);
V_ operator name varchar2 (20);
V_ sign in batch Varchar2 (10);
V_ billing batch Varchar2 (10);
V_ send batch Varchar2 (10);
N_ task id Number (18);
N_ send Number (18);
V_ name Varchar2 (100);
Sex Varchar2 v_ (10);
V_ age Varchar2 (10);
V_ age digital Varchar2 (10);
V_ combination id Varchar2 (10);
Applicant v_ Varchar2 (100);
D_ application time date;
V_ application department Varchar2 (50);
V_ health VARCHAR2 (50);
V_ patient department VARCHAR2 (50);
V_ booking form VARCHAR2 (8);
N_ outpatient NUMBER (18);
D_ date of date of birth;
V_ ward VARCHAR2 (100);
Coding, ward V_ VARCHAR2 (10);
V_ sample barcode VARCHAR2 (18);
State n_ billing NUMBER (3);
The begin
Select name into v_ operator name from table s computer personnel, personnel table r where s. personnel id=r.i d and s. username=user;
The select nextno into v_ (160) sign in batches from dual;
Select task id registration, registration record id, a medical staff id into n_ task id, n_ registration id, n_ id from a medical group personnel r where r. physical examination number=TASKID_IN;
Zl_ medical group personnel _sign (n_ registration ID, n_ personnel ID, v_ operator name, sysdate, v_ sign in batches);
Select NextNO (' 153 ', '0', ', '1') into v_ billing batches From Dual;
Zl_ medical personnel project _Billing (n_ task id | | ', '| | n_ personnel id, v_ billing batch,' 1 ');
Zl_ medical personnel costs _Insert (v_ billing batch, '0');

For c in (task registration id, Select a. a. medical personnel id, a. medical personnel id As the patient id, a. registration project id, a. physical examination project id, a. valuation mode, the serial number, a. dependent parent, a. price father, a. overtime, a. additional marks, a. pay number, a. operator name, a. operator number,
Calculating unit, a. charge categories, a. a. fees detail id, a. charging items id, a. income project id, a. receipt fee, a. charging items, a. quotation, a. batch, a. can zero, a. dose coefficient,
Paid-in amount, a. receivable amount, a. a. standard amount, a. check-up amount, a. several times, a. ordered id, a. outpatient number, a. name, a. gender, a. age, a. fee, a. bills, a. cost object, a. charge properties, a. record nature,
A. a medical department id, a. billing department id, a. open singles, a. executive department, a. executive department id, a. executive department id As enforcement id, round (NVL (a. standard unit price, 0), (2) As the unit price, a. registration time, a. patient department id, time of occurrence, 'As accurately the person
The From (
Select RowNum As serial number, Null As subordinate father, father, Null As the price 0 As overtime, 0 As additional symbol, number 1 As pay, a. send personnel As the operator's name, 'zlhis As operator number, a. task registration id, a. registration project id, a. physical examination project id, a. charge categories, a. calculating unit,
A. details of charge items id as id, a. charging items id, a. income project id, a. receipt fee, a. charging items, a. quotation, a. batch, a. can zero,
A. a. dose coefficient, implement the department id, 0 As receivable amount, 0 As paid-in amount, a. standard amount, a. check-up amount, a. several times, a. ordered id, a. medical personnel id, a. name, a. gender, a. outpatient number,
Fee don't, a. age, a. a. bills, a. cost object, a. charge properties, a. record nature, a. medical department id, a. billing department id as billing department id, a. doctor billed as A single,
B. name As executive department, a. standard unit price, a. registration time, a. medical department id As patient department id, Sysdate As time, a. appraising
The From (Select c. task registration id, c. medical personnel id, e. registration project id, c. A medical project id, k. valuation way, d. category As charge categories, d. calculating unit, a. charging items id, d. name As charging items,
Nvl (d. whether intending, 0) As quotation, Nvl (Decode (d. category, '4', i. in batches, h. pharmacy partial), 0) As partial, h. can outpatient minute, As can the zero,
H. dose coefficient, Decode (a. execution department id, b. executive department id, c. executive department id, a. executive department id) As executive department id,
A. check-up amount,
Nvl (a. charge several times, 1) As several times,
Decode (d. whether intending, 1, a. check-up amount, z. present price) as standard unit price, z. present price * Nvl (a. charge several times, 1) as the standard amount,
Decode (e. cost object, 1, c. orders record id, c. collection orders id) As the doctor's advice id, f. name,
Gender, f. outpatient number, f. f. age, f. fee, e. bills, e. cost object, e. charge properties, e. record nature, g. a medical department id, b. billing department id, b. billing doctor, Sysdate As registration time, z. income project id, Q., receipt fee, c. send personnel to
In terms of the From A medical group A,
A medical group project B,
Medical personnel item C,
Charge D project directory,
F the patient information,
(Select Distinct x. bills, x. cost object, x. charge properties, x. record nature, y.I d project id As personnel, y. registration project id, x. project object
Project Y From a medical personnel, medical personnel cost X
Where y. charging batch=v_ batches And x. personnel project id=y.I d And x. project object=1
The Union
Select Distinct x. bills, x. cost object, x. charge properties, x. record nature, y.I d As project id, Z.I d As the registered id, x. project object
Project Y From a medical personnel, medical personnel cost X, a medical group project Z
Where y. charging batch=v_ batches And x. personnel project id=y.I d And (y. registration project id=z. correlation id Or y. registration project id=Z.i d) And y. medical personnel id=z. medical personnel id And z. whether professional=1 And x. project object=2
E,
A medical mission registration G, a medical record group K,
Drug specifications H,
Material properties,
Charge price Z,
Where income project Q c. charging batch=v_ batches AND z. income project id=Q.I D AND (Q). The time taken is Null Or Q. The time taken & gt; Sysdate)
And a. registration project id=B.I d
And B.I d=e. registration project ID
Project And C.I d=e. personnel id
And D.I D=a. charging items id
And D.I D=z. fees detail ID
And z. execution date & lt;=Sysdate And (z. termination date Is Null Or z. termination date & gt; Sysdate)
And G.I D=b. task registration id
And c. medical personnel id=F patient id
And h. drug id (+)=D.I D
And i. material id (+)=D.I D
And K.I D=b. registration record id
And a. charging object=e. cost object And z. price level is Null Order By c. task registration id, c. medical personnel id, c. registration project id, a. inspection area, a. charging items id, Nvl (z. serial number, 0)
) A,
Department of table B
Where a. executive department id=B.I D (+)
/* and (a. a. cost documents for record properties) not in (Select bills, b. b. record nature
From A medical personnel project, A physical examination personnel costs, B outpatient records C
Where a. charging batch=v_ batches And Anderson, d=b. personnel project id And b. bills=between o And b. record nature=c. properties) */
Registration id) Order by a. a. task, a. medical personnel id, a. registration project id, a. record nature, a. a. charging projects cost documents for id) loop

Zl_ outpatient billing records _Insert (c. bills, c. serial number, c. the patient id, c. outpatient number, c. name, c. gender, c. age, c. don't, ' ', ' ', c. the patient department id, c. billing department id, c. open singles, ' ', c. charging items, c. charge categories,
C. calculating unit, c. pay number, c. several times, c. additional marks, c. executive department id, c. the price of the father, c. income project id, c. receipt fee, c. monovalent, c. amount receivable, c. paid-in amount, c. occurred, c. registration time, 'and' 0 ', c. operator number, c. operator name,
'a', ' ', ' ', ' ' ' ' ' ', '4');
Select Max (m. send batches), Max sent (n.) into V_ send batch, project m n_ send number From a medical personnel, patients orders sent n Where m. orders record id=n. orders id and m. task registration id=n_ task id and m. medical personnel id=n_ personnel id;
Zl_ medical personnel project _Send (n_ registration ID, n_ personnel ID, c. registration project ID, c. operator name, sysdate, V_ send batch, n_ send);
end loop;

For z (in the doctor's advice records id, Select a. a. collection orders id, a. test specimens, b. coding As executive department coding, d. name As acquisition methods, e. name As collection department,
F. fee, the Sum (f.) receivable amount As receivable amount, Sum (Decode (f. record state, 1, f. paid-in amount, 0)) As paid-in amount
From a medical personnel project, a department table b, c, patient doctor's advice records, diagnosis and treatment project directory d department table e, outpatient records f
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull