--procedure
CREATE OR REPLACE PROCEDURE pd_acc_vn (p_dr_acc_no varray_varchar,
p_cr_acc_no varray_varchar,
p_amount varray_number,
p_cq_no varray_varchar,
p_cq_dt varray_date,
p_party varray_varchar,
p_coa_id VARCHAR2,
p_vr_dt DATE,
p_pv_type VARCHAR2,
p_particulars VARCHAR2,
p_category VARCHAR2,
p_doctyp VARCHAR2,
p_index NUMBER,
p_user VARCHAR2,
p_company NUMBER
)
IS
x NUMBER;
vVRNO VARCHAR2(10);
vGPVNO NUMBER;
CURSOR C_DR_ACC_HEAD (P_DR_ACC_NO VARCHAR2)
IS
SELECT COA_ID, COA_SUB_ID, PO_STATUS, LOCATION,
CTRLCODE, SUBCODE, ACCTYPE, ACCGROUP,
ACCHEAD, DR_CR_FLAG, BS_IS_CODE, CF_DR_CODE,
CF_CR_CODE, BS_IS_CODE1, CF_DR_CODE1, CF_CR_CODE1,
PARTY_CODE, RETAIN_EARNING_FLAG, SUBCODE_ORG
FROM ACC_HEAD
WHERE SUBCODE_ORG = P_DR_ACC_NO;
CURSOR C_CR_ACC_HEAD (P_CR_ACC_NO VARCHAR2)
IS
SELECT COA_ID, COA_SUB_ID, PO_STATUS, LOCATION,
CTRLCODE, SUBCODE, ACCTYPE, ACCGROUP,
ACCHEAD, DR_CR_FLAG, BS_IS_CODE, CF_DR_CODE,
CF_CR_CODE, BS_IS_CODE1, CF_DR_CODE1, CF_CR_CODE1,
PARTY_CODE, RETAIN_EARNING_FLAG, SUBCODE_ORG
FROM ACC_HEAD
WHERE SUBCODE_ORG = P_CR_ACC_NO;
R_C_ACC_HEAD_DR C_DR_ACC_HEAD%ROWTYPE;
R_C_ACC_HEAD_CR C_CR_ACC_HEAD%ROWTYPE;
v_dr_acc_no varray_varchar := varray_varchar();
v_cr_acc_no varray_varchar := varray_varchar();
v_amount varray_number := varray_number();
v_cq_no varray_varchar := varray_varchar();
v_cq_dt varray_date := varray_date();
v_party varray_varchar := varray_varchar();
BEGIN
SELECT NVL(MAX(GPVNO),0) + 1 INTO vGPVNO FROM PV;
v_dr_acc_no.extend (p_index);
v_cr_acc_no.extend (p_index);
v_amount.extend (p_index);
v_cq_no.extend (p_index);
v_cq_dt.extend (p_index);
v_party.extend (p_index);
FOR I IN 1 .. p_index
LOOP
SELECT LAST_DOC_NO+1 INTO x FROM DOC_TYPE WHERE coa_id = p_coa_id and DOC_TYPE = 'CV';
SELECT LPAD(X,6,'0') INTO vVRNO FROM DOC_TYPE WHERE COA_ID = P_COA_ID AND DOC_TYPE = 'CV';
UPDATE DOC_TYPE SET LAST_DOC_NO = x WHERE coa_id = P_COA_ID and DOC_TYPE = 'CV';
OPEN C_DR_ACC_HEAD(p_dr_acc_no(i));
FETCH C_DR_ACC_HEAD INTO R_C_ACC_HEAD_DR;
IF C_DR_ACC_HEAD%NOTFOUND THEN
CLOSE C_DR_ACC_HEAD;
ELSE
CLOSE C_DR_ACC_HEAD;
END IF;
OPEN C_CR_ACC_HEAD(p_cr_acc_no(i));
FETCH C_CR_ACC_HEAD INTO R_C_ACC_HEAD_CR;
IF C_CR_ACC_HEAD%NOTFOUND THEN
CLOSE C_CR_ACC_HEAD;
ELSE
CLOSE C_CR_ACC_HEAD;
END IF;
--insert into abc (ac)
--values (p_dr_acc_no(i));
--insert into pv (TOT_AMT)
--values (p_amount(i));
INSERT INTO PV (PVNO, PVDT, PVTYPE, COA_ID, COA_SUB_ID, PO_STATUS, LOCATION, ACC_HEAD_CTRLCODE, ACC_HEAD_SUBCODE, PARTICULARS,CQNO,CQDT
,CQCATEGORY,DOC_TYPE, PARTY_CODE, TOT_AMT, GPVNO)
--,DRAWNDT,ONACCOF,COMPANY_ID,BRANCH_ID,OFFICE_OFFICE_CODE,BANK_CODE,BRANCH_CODE,POSTING_FLAG,AUDIT_FLAG,BR_FLAG,REF_BILL_NO, REF_BILL_DT, CC_ID)
VALUES(vVRNO, NVL(p_vr_dt,SYSDATE), p_pv_type, p_coa_id, R_C_ACC_HEAD_CR.COA_SUB_ID, R_C_ACC_HEAD_CR.PO_STATUS,
R_C_ACC_HEAD_CR.LOCATION, R_C_ACC_HEAD_CR.CTRLCODE, R_C_ACC_HEAD_CR.SUBCODE, p_particulars, v_cq_no(i), sysdate,
p_category, p_doctyp, v_party(i), p_amount(i), vGPVNO);
--,I.DRAWNDT,I.ONACCOF,I.COMPANY_ID,I.BRANCH_ID,I.OFFICE_OFFICE_CODE,,I.BANK_CODE,I.BRANCH_CODE,I.POSTING_FLAG,I.AUDIT_FLAG,I.BR_FLAG,I.REF_BILL_NO,I.REF_BILL_DT,I.CC_ID);
INSERT INTO PVCHILD (PVNO, PVDT, PVTYPE, COA_ID, COA_SUB_ID, PO_STATUS, LOCATION, ACC_HEAD_CTRLCODE,
ACC_HEAD_SUBCODE, AMT, USERID, ENTRDT, POSTING_FLAG, PERIOD, YEAR, YEAR_NEXT,
DOC_TYPE, AUDIT_FLAG, BR_FLAG, CC_ID, ID, SUBCODE_ORG, SL)
VALUES (vVRNO, NVL(p_vr_dt,SYSDATE), p_pv_type, P_COA_ID, nvl(R_C_ACC_HEAD_DR.COA_SUB_ID,'01'), R_C_ACC_HEAD_DR.PO_STATUS, R_C_ACC_HEAD_DR.LOCATION,
R_C_ACC_HEAD_DR.CTRLCODE, R_C_ACC_HEAD_DR.SUBCODE, p_amount(i), p_user, SYSDATE, 0, '', '', '',--v_amount(i)
p_doctyp, '', '', '', PVCHILD_ID_SEQ.NEXTVAL, R_C_ACC_HEAD_DR.SUBCODE_ORG, '');
END LOOP;
END;
--cALLING PROCEDURE
DECLARE
v_ind NUMBER := 0;
v_dracc varray_number := varray_number ();
v_cracc varray_number := varray_number ();
v_amnt varray_number := varray_number ();
v_cq_no varray_number := varray_number ();
v_cq_dt varray_number := varray_number ();
v_party varray_number := varray_number ();
v_coa_id VARCHAR2(10);
v_vr_dt DATE;
v_pv_type VARCHAR2(10);
v_particulars VARCHAR2(200);
v_category VARCHAR2(10);
v_doctyp VARCHAR2(5);
v_index NUMBER;
v_user VARCHAR2(10);
v_company NUMBER;
BEGIN
--v_acc.EXTEND (10);
FOR i IN 1 .. 1
LOOP
v_dracc :='12080107';
v_cracc :='12080106';
v_amnt :=500;
v_cq_no :='12345';
v_cq_dt :=sysdate;
v_party :='5458';
v_coa_id :='DNA';
v_vr_dt :=SYSDATE;
v_pv_type :='Q';
v_particulars :='N/A';
v_category :='Q';
v_doctyp :='BV';
v_index :=1;
v_user :=1001;
v_company :=1;
END LOOP;
pd_acc_vn (v_dracc,
v_cracc,
v_amnt,
v_cq_no,
v_cq_dt,
v_party,
v_coa_id,
v_vr_dt,
v_pv_type,
v_particulars,
v_category,
v_doctyp,
v_index,
v_user,
v_company);
--dbms_output.put_line(v_ind);
END;
select distinct doc_type From PV;
select * From PV;
SELECT coa_sub_id, po_status, location, ctrlcode, subcode FROM ACC_HEAD_VW WHERE SUBCODE_ORG = '11010107';
SELECT COA_ID, COA_SUB_ID, PO_STATUS, LOCATION,
CTRLCODE, SUBCODE, ACCTYPE, ACCGROUP,
ACCHEAD, DR_CR_FLAG, BS_IS_CODE, CF_DR_CODE,
CF_CR_CODE, BS_IS_CODE1, CF_DR_CODE1, CF_CR_CODE1,
PARTY_CODE, RETAIN_EARNING_FLAG, SUBCODE_ORG
FROM ACC_HEAD
WHERE subcode_org = '11010107';
SELECT NVL(MAX(GPVNO),0) + 1 FROM PV;
DECLARE
v_ind NUMBER := 0;
v_dracc varray_varchar := varray_varchar ();
v_cracc varray_varchar := varray_varchar ();
v_amnt varray_number := varray_number ();
v_cq_no varray_varchar := varray_varchar ();
v_cq_dt varray_date := varray_date ();
v_party varray_varchar := varray_varchar ();
v_coa_id VARCHAR2(10);
v_vr_dt DATE;
v_pv_type VARCHAR2(10);
v_particulars VARCHAR2(200);
v_category VARCHAR2(10);
v_doctyp VARCHAR2(5);
v_index NUMBER;
v_user VARCHAR2(10);
v_company NUMBER;
BEGIN
v_dracc.EXTEND (5);
v_cracc.EXTEND (5);
v_amnt.EXTEND (5);
v_cq_no.EXTEND (5);
v_cq_dt.EXTEND (5);
v_party.EXTEND (5);
v_coa_id :='DNA';
v_vr_dt :=SYSDATE;
v_pv_type :='Q';
v_particulars :='N/A';
v_category :='Q';
v_doctyp :='BV';
v_index :=5;
v_user :=1001;
v_company :=1;
FOR i IN 1 ..5
LOOP
if i = 1 then
v_ind := v_ind + 1;
v_dracc (v_ind) :='12080107';
v_cracc (v_ind) :='12080106';
v_amnt (v_ind) :=50;
v_cq_no (v_ind) :='12345';
v_cq_dt (v_ind) :=sysdate;
v_party (v_ind) :='5458';
--end if;
elsif i = 2 then
--Second set
v_ind := v_ind + 1;
v_dracc (v_ind) :='12080107';
v_cracc (v_ind) :='12080106';
v_amnt (v_ind) :=500;
v_cq_no (v_ind) :='12345';
v_cq_dt (v_ind) :=sysdate;
v_party (v_ind) :='5458';
elsif i = 3 then
--Third set
v_ind := v_ind + 1;
v_dracc (v_ind) :='12080107';
v_cracc (v_ind) :='12080106';
v_amnt (v_ind) :=500;
v_cq_no (v_ind) :='12345';
v_cq_dt (v_ind) :=sysdate;
v_party (v_ind) :='5458';
elsif i = 4 then
--Forth set
v_ind := v_ind + 1;
v_dracc (v_ind) :='12080107';
v_cracc (v_ind) :='12080106';
v_amnt (v_ind) :=500;
v_cq_no (v_ind) :='12345';
v_cq_dt (v_ind) :=sysdate;
v_party (v_ind) :='5458';
elsif i = 5 then
--Fifth set
v_ind := v_ind + 1;
v_dracc (v_ind) :='12080107';
v_cracc (v_ind) :='12080106';
v_amnt (v_ind) :=500;
v_cq_no (v_ind) :='12345';
v_cq_dt (v_ind) :=sysdate;
v_party (v_ind) :='5458';
end if;
END LOOP;
pd_acc_vn (v_dracc,
v_cracc,
v_amnt,
v_cq_no,
v_cq_dt,
v_party,
v_coa_id,
v_vr_dt,
v_pv_type,
v_particulars,
v_category,
v_doctyp,
v_index,
v_user,
v_company);
--dbms_output.put_line(v_ind);
END;
SELECT LAST_DOC_NO + 1 INTO VX FROM DOC_TYPE
WHERE COA_ID = 'DNA' AND DOC_TYPE = 'BV'
FOR UPDATE;
vVR_NO := LPAD (VX, 6, '0');
UPDATE DOC_TYPE SET LAST_DOC_NO = VX WHERE COA_ID = 'DNA' AND DOC_TYPE = 'BV';
X := X + 1;
