18:45:29 SQL> desc csah_receipts_master ERROR: ORA-04043: object csah_receipts_master does not exist 18:45:48 SQL> desc cash_receipts_master; Name Null? Type ----------------------------------------- -------- ---------------------------- CASH_RECEIPT_ID NUMBER(22) PRINCIPLE_SUB_NO NUMBER(22) CUSTOMER_ID NUMBER(22) ENTRY_DATE DATE RECEIPT_DATE DATE CASH_RECEIPT_NUM VARCHAR2(30) CHARGE_CODE VARCHAR2(30) ADVANCE_DEPOSIT_FLAG VARCHAR2(1) TRANSACTION_TYPE VARCHAR2(2) SURCHARGE_AMOUNT_N NUMBER(22,2) ACTUAL_PAY_AMT NUMBER(22,2) PAY_MODE VARCHAR2(3) CHEQUE_NUMBER VARCHAR2(20) BANK_CODE VARCHAR2(20) BRANCH_CODE VARCHAR2(40) AREA_CODE VARCHAR2(50) CENTER_CODE VARCHAR2(50) FINACIAL_ACCOUNTING_DATE DATE CREATED_BY VARCHAR2(14) CREATED_DATE DATE MODIFIED_BY VARCHAR2(14) MODIFIED_DATE DATE 18:46:09 SQL> desc cust_service_acc_map; Name Null? Type ----------------------------------------- -------- ---------------------------- CUSTOMER_ID NUMBER(22) PRINCIPLE_SUB_NO NUMBER(22) CB_SUBSCRIBER_CODE NUMBER(10) CB_ACCOUNT_CODE NUMBER(10) CB_ACCOUNT_LINK_CODE NUMBER(10) CB_ACCOUNT_SERVICE NUMBER(10) CB_STATUS VARCHAR2(2) 18:46:37 SQL> desc invoice_acc_map; ERROR: ORA-04043: object invoice_acc_map does not exist 18:46:59 SQL> desc invoice_account_map; Name Null? Type ----------------------------------------- -------- ---------------------------- CUSTOMER_ID NUMBER(22) CB_ACCOUNT_CODE NUMBER(10) CB_SUBSCRIBER_CODE NUMBER(10) PRINCIPLE_SUB_NO NUMBER(22) CB_ACCOUNT_LINK_CODE NUMBER(10) CREATE OR REPLACE TYPE RECPT_obj AS OBJECT ( CASH_RECEIPT_ID NUMBER(22), PRINCIPLE_SUB_NO NUMBER(22), CUSTOMER_ID NUMBER(22), ENTRY_DATE DATE, RECEIPT_DATE DATE, CASH_RECEIPT_NUM VARCHAR2(30), CHARGE_CODE VARCHAR2(30), ADVANCE_DEPOSIT_FLAG VARCHAR2(1 ), TRANSACTION_TYPE VARCHAR2(2 ), SURCHARGE_AMOUNT_N NUMBER(22,2), ACTUAL_PAY_AMT NUMBER(22,2), PAY_MODE VARCHAR2(3 ), CHEQUE_NUMBER VARCHAR2(20), BANK_CODE VARCHAR2(20), BRANCH_CODE VARCHAR2(40), AREA_CODE VARCHAR2(50), CENTER_CODE VARCHAR2(50), FINACIAL_ACCOUNTING_DATE DATE, CREATED_BY VARCHAR2(14), CREATED_DATE DATE, MODIFIED_BY VARCHAR2(14), MODIFIED_DATE DATE, serv_link_code number(10), CB_ACCOUNT_CODE number(10), ACC_LINK_CODE NUMBER(10) ); declare TYPE RCPT_TAB IS TABLE OF RECPT_obj INDEX BY PLS_INTEGER; RCPT RCPT_TAB; main_acc_link_code number(10); n_bill_cycle number(10); l_trans_type_v varchar2(1); l_serial_n number(5); l_trans_num_v varchar2(20); v_error_code varchar2(15); v_error_DESC VARCHAR2(200); tran_date date; n_user_code number(4); CURSOR POP_RCPT IS SELECT a.CASH_RECEIPT_ID, a.PRINCIPLE_SUB_NO, a.CUSTOMER_ID, a.ENTRY_DATE, a.RECEIPT_DATE, a.CASH_RECEIPT_NUM, a.CHARGE_CODE, a.ADVANCE_DEPOSIT_FLAG, a.TRANSACTION_TYPE, a.SURCHARGE_AMOUNT_N, a.ACTUAL_PAY_AMT, a.PAY_MODE, a.CHEQUE_NUMBER, a.BANK_CODE, a.BRANCH_CODE, a.AREA_CODE, a.CENTER_CODE, a.FINACIAL_ACCOUNTING_DATE, a.CREATED_BY, a.CREATED_DATE, a.MODIFIED_BY, a.MODIFIED_DATE, b.CB_ACCOUNT_LINK_CODE serv_link_code , b.CB_ACCOUNT_CODE , c.CB_ACCOUNT_LINK_CODE acc_link_code FROM cash_receipts_master a, cust_service_acc_map b, invoice_account_map c WHERE a.CUSTOMER_ID=b.CUSTOMER_ID AND b.CUSTOMER_ID=c.CUSTOMER_ID AND CB_STATUS IN ('AC','SP') and SURCHARGE_AMOUNT_N is NOT NULL; BEGIN OPEN POP_RCPT; FETCH POP_RCPT BULK COLLECT INTO RCPT LIMIT 1000; FOR i IN 1 .. RCPT.COUNT loop main_acc_link_code :=RCPT(I).acc_link_code; n_bill_cycle := 101000001; -- l_trans_type_v := CASE RCPT(I)..TRANSACTION_TYPE WHEN 'RC' THEN '1' WHEN 'DN' THEN '4' end; l_trans_type_v := '1'; -- IF RCPT(I)..SURCHARGE_AMOUNT_N is null THEN -- l_trans_type_v := '4'; -- END IF; Begin Select MAX(TO_NUMBER(NVL(SUBSTR(TRANS_NUM_V,15),'0')))+1 INTO l_serial_n FROM CB_ACCOUNT_RECEIVE_PAY where TO_NUMBER(SUBSTR(TRANS_NUM_V,5,6))=RCPT(I).CASH_RECEIPT_ID AND TRANS_TYPE_V ='1' ; Exception When others then l_serial_n :=1; End; l_trans_num_v := 'RCT/'||lpad(abs(RCPT(I).CASH_RECEIPT_ID),6,'0')||'/'||to_char(RCPT(I).RECEIPT_DATE,'yy')||'/'||l_serial_n; v_error_code := 'mig_ka:06'; v_error_desc := 'into cb_account_receive_pay'||RCPT(I).cb_account_code||'main a/c ln->'||RCPT(I).acc_link_code; tran_date := RCPT(I).RECEIPT_DATE ; BEGIN v_error_desc := 'select user code from cb_users :: '||RCPT(I).CREATED_BY; SELECT USER_CODE_N INTO n_user_code FROM CB_USERS WHERE LOGIN_NAME_V = RCPT(I).CREATED_BY; EXCEPTION WHEN others THEN n_user_code := 2; END; IF RCPT(I).ACTUAL_PAY_AMT >0 THEN BEGIN v_error_desc := 'error while inserting into cb_receipts main a/c ln cd:: '||RCPT(I).acc_link_code; v_error_desc := 'error while inserting into main ARAP main a/c ln cd:: '||RCPT(I).acc_link_code; INSERT INTO CB_ACCOUNT_RECEIVE_PAY( ACCOUNT_LINK_CODE_N, TRANS_TYPE_V, TRANS_NUM_V, SERIAL_NUM_N, ADJUST_CLASS_N, TRANS_DATE_D, DB_CR_V, BILL_CLEARED_FLG_V, BILL_FULLY_CLEARED_FLG_V, DUE_DATE_D, CALC_INTRST_FLG_V, LAST_INTRST_CALC_DATE_D, PRINT_LEDG_FLG_V, PRINT_IN_INVOICE_FLG_V, INFO_OPTN_V, TRANS_AMT_N, TRANS_AMT_CLEARED_N, TEMP_CLR_AMT_N, TRANS_DESC_V, OLD_YR_ADJUST_AMT_N, MOV_DUNNING_FLG_V, SUB_DTLS_OPTN_V, USER_CODE_N, LOCATION_CODE_V, USER_MAC_ADDRESS_V, CASH_BOX_NUMBER_N, TRANSFERRED_AMT_N ) VALUES( RCPT(I).acc_link_code, --ACCOUNT_LINK_CODE_N, l_trans_type_v ,--l_trans_type_v, --TRANS_TYPE_V, l_trans_num_v ,--l_trans_num_v, --TRANS_NUM_V, 0,--n_serial_no, --SERIAL_NUM_N, NULL, --ADJUST_CLASS_N, tran_date, --TO_DATE(populate_inv.ref_date, 'dd-mon-yyyy hh24:mi:ss'), --TRANS_DATE_D, 'C',--l_db_cr, --DB_CR_V, 'N', --BILL_CLEARED_FLG_V, 'N', --BILL_FULLY_CLEARED_FLG_V, tran_date,--to_date(cast(populate_inv.POSTING_DATE as date),'dd/mm/rrrr'), --DUE_DATE_D, 'N', --CALC_INTRST_FLG_V, NULL, --LAST_INTRST_CALC_DATE_D, 'Y', --PRINT_LEDG_FLG_V, 'Y', --PRINT_IN_INVOICE_FLG_V, 'P', --l_info_optn_v, --INFO_OPTN_V, RCPT(I).ACTUAL_PAY_AMT *100 , --TRANS_AMT_N, 0, --TRANS_AMT_CLEARED_N, 0, --TEMP_CLR_AMT_N, null, --TRANS_DESC_V 0, --OLD_YR_ADJUST_AMT_N, NULL, --MOV_DUNNING_FLG_V, NULL, --SUB_DTLS_OPTN_V, n_user_code, --USER_CODE_N, 'APDST', --LOCATION_CODE_V, NULL, --USER_MAC_ADDRESS_V, NULL, --CASH_BOX_NUMBER_N NULL--TRANS_DESC_V ); EXCEPTION WHEN OTHERS THEN v_error_code := SUBSTR(v_error_code||':'||SQLCODE,1,30); v_error_desc := SUBSTR(RTRIM(v_error_desc)||'::'|| SQLERRM,1,400); INSERT INTO MIG_ARAP_ERRORS ( ERROR_CODE_V, ERROR_DESC_V, DATE_D, main_account_link_code ,TRANS_NUM ) VALUES ( v_error_code, v_error_desc, SYSDATE, main_acc_link_code ,l_trans_num_v ); commit; END; enD IF; EXIT WHEN POP_RCPT%NOTFOUND; END LOOP; cloSE POP_RCPT; end; /