How can i excute the package body in C#?

Apr 23 2015 10:26 AM

Dears I would appreciate if someone give me ideas about how can i execute below package body in C#, I am trying to make a program which executes different scripts this is one of the several scripts.

CREATE OR REPLACE PACKAGE BODY ah_gen_dts_file_pkg
AS
   FUNCTION ah_gen_dts_file(
      i_Dir             IN       VARCHAR2,
      i_File_Name_Init IN       VARCHAR2,
      o_error_message   OUT      VARCHAR2
   )
      RETURN BOOLEAN
   IS
       ----------------------------------------------------------- Notes
        -- here substr is used to consider MNS store name as they are not purely numeric
        --
       ----------------------------------------------------------- Local Variables
        sqlcd               NUMBER;
        ln_count            NUMBER;
        lv_file_handle      UTL_FILE.file_type;
        lv_error_file_dir   VARCHAR2 (120)     := i_Dir;
        lv_error_file       VARCHAR2 (120)
                := i_File_Name_Init || '_DTS';
        lv_ibt_file       VARCHAR2 (120)
                := i_File_Name_Init || '_IBT';
        lv_sale_file       VARCHAR2 (120)
                := i_File_Name_Init || '_SALE';

        lv_payment_file       VARCHAR2 (120)
                := i_File_Name_Init || '_PAYMENT';

--        L_SchemaName         dba_users.USERNAME%TYPE;
--        L_SCHEMA_NAME        dba_users.USERNAME%TYPE;

        L_SchemaName         varchar2 (30);
        L_SCHEMA_NAME        varchar2 (30);

        L_DC_DY_BSN          VARCHAR2 (120);
        L_ID_STR_RT          VARCHAR2 (120);
        L_TRN_TYPE           VARCHAR2 (20);
        L_QTY                NUMBER;
        L_RETAIL             NUMBER;
        StrSql               VARCHAR2(12000) := null;
        StrSql1              VARCHAR2(12000) := null;
        StrSql2              VARCHAR2(12000) := null;
        RetVal               BOOLEAN;

        -------------------- Variables for sales cursor
        S_Store VARCHAR2(300 BYTE);
        S_Reg VARCHAR2(9 BYTE);
        S_Buss_Date VARCHAR2(30 BYTE);
        S_Tick_No INTEGER;
        S_Cash VARCHAR2(30 BYTE);
        S_Strt_Time VARCHAR2(30 BYTE);
        S_End_Time VARCHAR2(30 BYTE);
        S_Sale_Dt VARCHAR2(30 BYTE);
        S_Barcode VARCHAR2(42 BYTE);
        S_Item VARCHAR2(42 BYTE);
        S_Type VARCHAR2(21 BYTE);
        S_Qty NUMBER(9,2);
        S_Flag CHAR(3 BYTE);
        S_Amt NUMBER(13,2);
        S_Nt_Amt NUMBER(13,2);
        S_Man_Disc NUMBER(14,3);
        S_Tot_Disc NUMBER;
    S_MDFR_PROMO_CODE     VARCHAR2(300 BYTE);
    S_MDFR_PROMO_COMP     VARCHAR2(300 BYTE);
    S_MDFR_DISCOUNT      NUMBER(9,2);
    S_LTM_PROMO_CODE     VARCHAR2(300 BYTE);
    S_LTM_PROMO_COMP     VARCHAR2(300 BYTE);
    S_LTM_DISCOUNT      NUMBER(9,2);
    S_MO_PRN_PRC         NUMBER(13,2);
    S_LN_ITM         NUMBER(10);
        ---------------------------------------------------------- Variable for Payment
        P_STORE          VARCHAR2(15 BYTE);
        P_REGISTER       VARCHAR2(9 BYTE);
        P_BUSINESS_DATE VARCHAR2(30 BYTE);
        P_SALE_DT        VARCHAR2(30 BYTE);
        P_TRN_NO         INTEGER;
        P_TENDER_TYPE    VARCHAR2(60 BYTE);
        P_VALUE          NUMBER;
        P_CARDNAME       VARCHAR2(60 BYTE);
        P_AUTH_CODE      VARCHAR2(120 BYTE);
        P_EXP_DATE       VARCHAR2(60 BYTE);
        ----------------------------------------------------------- FOR IBT
        I_ALOC_TYPE varchar2(12 BYTE);
        I_ALLOCATION_NUMBER varchar2(12 BYTE);
        I_FROM_STORE varchar2(12 BYTE);
        I_TO_STORE varchar2(12 BYTE);
        I_GDN_NO varchar2(12 BYTE);
        I_STARTDATE varchar2(30 BYTE);
        I_STATUS varchar2(100 BYTE);
        I_CONFIRMDATE varchar2(30 BYTE);
        I_QUANTITY varchar2(12 BYTE);
        ----------------------------------------------------------- Cursors
--        CURSOR get_schema_name_cur IS
--            select '"'||USERNAME||'".' USERNAME, '"'||USERNAME||'"' SCHEMA_NAME from dba_users
--                where DEFAULT_TABLESPACE = 'USERS' and account_status = 'OPEN' AND REGEXP_LIKE (substr(USERNAME,3,length(username)), '[0-9]','i')
--                    Order by USER_ID;

        CURSOR get_dts_cur IS
            SELECT TRN_TYPE, DC_DY_BSN, ID_STR_RT, QTY, RETAIL FROM INH_DTS_DATA
                ORDER BY ID_STR_RT, TRN_TYPE, DC_DY_BSN;

        CURSOR get_sales_det_cur IS
            SELECT STORE, REGISTER, BUSINESS_DATE, TICKET_NO, CASHIER, to_char(START_TIME,'DD-MON-YYYY HH24:MI:SS') START_TIME,
                   to_char(END_TIME,'DD-MON-YYYY HH24:MI:SS') END_TIME, SALE_DT, BARCODE, ITEM, SALE_TYPE, QTY,
                   RETN_FLAG, AMOUNT, NET_AMOUNT, MANUAL_DISCOUNT, TOTAL_DISCOUNT,
           MDFR_PROMO_CODE, MDFR_PROMO_COMP, MDFR_DISCOUNT, LTM_PROMO_CODE, LTM_PROMO_COMP, LTM_DISCOUNT, MO_PRN_PRC,LN_ITM
            FROM INH_RTLOG_SALES ORDER BY STORE, BUSINESS_DATE;

        CURSOR get_payment_det_cur IS
            SELECT STORE, REGISTER, BUSINESS_DATE, SALE_DT, TRN_NO, TENDER_TYPE, VALUE, CARDNAME, CC_AUTH_CODE, CC_EXP_DATE
                FROM INH_RTLOG_PAYMENT ORDER BY STORE, BUSINESS_DATE;

        CURSOR get_frn_gdn_cur is
            Select a.FROM_STORE STORE,'GDN' ALOC_TYPE,a.ALLOCATION_NUMBER,a.GDN_NO,a.FROM_STORE,a.TO_STORE,TO_CHAR(a.STARTDATE,'DD-MON-YYYY'),a.STATUS,TO_CHAR(a.CONFIRMDATE,'DD-MON-YYYY'),sum(b.QUANTITY)QUANTITY
                from INH_SIM.INH_GDN_MASTER a inner join INH_SIM.INH_GDN_DETAIL b on a.ALLOCATION_NUMBER=b.ALLOCATION_NUMBER
                group by a.ALLOCATION_NUMBER,a.FROM_STORE,a.TO_STORE,a.GDN_NO,a.STARTDATE,a.STATUS,a.CONFIRMDATE
                    UNION ALL
            Select a.TO_STORE STORE,'GRN' ALOC_TYPE,a.ALLOCATION_NUMBER,a.GRN_NO,a.FROM_STORE,a.TO_STORE,TO_CHAR(a.STARTDATE,'DD-MON-YYYY'),a.STATUS,TO_CHAR(a.CONFIRMDATE,'DD-MON-YYYY'),sum(b.QUANTITY)QUANTITY
                from INH_SIM.INH_GRN_MASTER a inner join INH_SIM.INH_GRN_DETAIL b on a.ALLOCATION_NUMBER=b.ALLOCATION_NUMBER
                group by a.ALLOCATION_NUMBER,a.FROM_STORE,a.TO_STORE,a.GRN_NO,a.STARTDATE,a.STATUS,a.CONFIRMDATE
                order by 2;

       ----------------------------------------------------------- SUMMARY - DTS
       BEGIN

            DELETE FROM INH_DTS_DATA;

            DELETE FROM INH_RTLOG_SALES;

            DELETE FROM INH_RTLOG_PAYMENT;

            COMMIT;

            ------------------------------------------------------ cursor to insert data from all schema
--            OPEN get_schema_name_cur;
--            LOOP
--                FETCH get_schema_name_cur into L_SchemaName, L_SCHEMA_NAME;
--
--                EXIT WHEN get_schema_name_cur%NOTFOUND;

                StrSql := null;
                StrSql := ' INSERT INTO INH_DTS_DATA (TRN_TYPE, DC_DY_BSN,ID_STR_RT,QTY,RETAIL) ' || CHR(13) ;
                StrSql := StrSql || ' SELECT '||''''||'BSN'||''''||', H1.DC_DY_BSN,H1.id_str_rt, SUM(IT.Qu_Itm_Lm_Rtn_Sls) QTY, SUM(MO_EXTN_DSC_LN_ITM) Retail ' || CHR(13) ;
                StrSql := StrSql || ' FROM TR_TRN H1, TR_RTL H2, TR_LTM_SLS_RTN IT, TR_LTM_RTL_TRN IT1 ' || CHR(13) ;
                StrSql := StrSql || ' WHERE ' || CHR(13) ;
                StrSql := StrSql || '     H1.ID_STR_RT=H2.ID_STR_RT ' || CHR(13) ;
                StrSql := StrSql || '     AND H1.DC_DY_BSN=H2.DC_DY_BSN ' || CHR(13) ;
                StrSql := StrSql || '     AND H1.ID_WS=H2.ID_WS AND H1.AI_TRN=H2.AI_TRN ' || CHR(13) ;
                StrSql := StrSql || '     AND H1.ID_STR_RT=IT.ID_STR_RT AND H1.DC_DY_BSN=IT.DC_DY_BSN ' || CHR(13) ;
                StrSql := StrSql || '     AND H1.ID_WS=IT.ID_WS AND H1.AI_TRN=IT.AI_TRN ' || CHR(13) ;
                StrSql := StrSql || '     AND IT.ID_STR_RT=IT1.ID_STR_RT ' || CHR(13) ;
                StrSql := StrSql || '     AND IT.DC_DY_BSN=IT1.DC_DY_BSN ' || CHR(13) ;
                StrSql := StrSql || '     AND IT.ID_WS=IT1.ID_WS ' || CHR(13) ;
                StrSql := StrSql || '     AND IT.AI_TRN=IT1.AI_TRN ' || CHR(13) ;
                StrSql := StrSql || '     AND IT.AI_LN_ITM=IT1.AI_LN_ITM ' || CHR(13) ;
                StrSql := StrSql || '     AND H1.SC_TRN=2 ' || CHR(13) ;
                StrSql := StrSql || '     AND IT1.FL_VD_LN_ITM=0 ' || CHR(13) ;
                StrSql := StrSql || '     AND H1.TY_TRN IN (1,2,5) ' || CHR(13) ;
                StrSql := StrSql || '     AND to_date(H1.DC_DY_BSN, '||''''||'YYYY-MM-DD'||''''||') >= trunc(sysdate-60)' || CHR(13) ;
                StrSql := StrSql || '     GROUP BY H1.DC_DY_BSN,H1.id_str_rt';

                --dbms_output.put_line(StrSql);
                execute immediate StrSql;

                StrSql := null;
                StrSql := ' INSERT INTO INH_DTS_DATA (TRN_TYPE, DC_DY_BSN,ID_STR_RT,QTY,RETAIL) ' || CHR(13) ;
                StrSql := StrSql || ' SELECT '||''''||'TRN'||''''||', to_char(trunc(H1.TS_CRT_RCRD - 6/24),'||''''||'RRRR-MM-DD'||''''||') DC_DY_BSN, ' || CHR(13) ;
                StrSql := StrSql || ' H1.id_str_rt, SUM(IT.Qu_Itm_Lm_Rtn_Sls) QTY, SUM(MO_EXTN_DSC_LN_ITM) Retail ' || CHR(13) ;
                StrSql := StrSql || ' FROM TR_TRN H1, TR_RTL H2, TR_LTM_SLS_RTN IT, TR_LTM_RTL_TRN IT1 ' || CHR(13) ;
                StrSql := StrSql || ' WHERE ' || CHR(13) ;
                StrSql := StrSql || '     H1.ID_STR_RT=H2.ID_STR_RT ' || CHR(13) ;
                StrSql := StrSql || '     AND H1.DC_DY_BSN=H2.DC_DY_BSN ' || CHR(13) ;
                StrSql := StrSql || '     AND H1.ID_WS=H2.ID_WS AND H1.AI_TRN=H2.AI_TRN ' || CHR(13) ;
                StrSql := StrSql || '     AND H1.ID_STR_RT=IT.ID_STR_RT AND H1.DC_DY_BSN=IT.DC_DY_BSN ' || CHR(13) ;
                StrSql := StrSql || '     AND H1.ID_WS=IT.ID_WS AND H1.AI_TRN=IT.AI_TRN ' || CHR(13) ;
                StrSql := StrSql || '     AND IT.ID_STR_RT=IT1.ID_STR_RT ' || CHR(13) ;
                StrSql := StrSql || '     AND IT.DC_DY_BSN=IT1.DC_DY_BSN ' || CHR(13) ;
                StrSql := StrSql || '     AND IT.ID_WS=IT1.ID_WS ' || CHR(13) ;
                StrSql := StrSql || '     AND IT.AI_TRN=IT1.AI_TRN ' || CHR(13) ;
                StrSql := StrSql || '     AND IT.AI_LN_ITM=IT1.AI_LN_ITM ' || CHR(13) ;
                StrSql := StrSql || '     AND H1.SC_TRN=2 ' || CHR(13) ;
                StrSql := StrSql || '     AND IT1.FL_VD_LN_ITM=0 ' || CHR(13) ;
                StrSql := StrSql || '     AND H1.TY_TRN IN (1,2,5) ' || CHR(13) ;
                StrSql := StrSql || '     AND trunc(H1.TS_CRT_RCRD - 6/24) >= trunc(sysdate-60) ' || CHR(13) ;
                StrSql := StrSql || '     GROUP BY to_char(trunc(H1.TS_CRT_RCRD - 6/24),'||''''||'RRRR-MM-DD'||''''||'), H1.id_str_rt';

                --dbms_output.put_line(StrSql);
                execute immediate StrSql;

                StrSql := null;
                StrSql := ' INSERT INTO INH_RTLOG_SALES' || CHR(13) ;
                StrSql := StrSql || ' SELECT   H1.ID_STR_RT   store ' || CHR(13) ;
                StrSql := StrSql || ' ,H1.ID_WS       Register ' || CHR(13) ;
                StrSql := StrSql || ' ,H1.DC_DY_BSN   business_date           ' || CHR(13) ;
                StrSql := StrSql || ' ,H1.AI_TRN      ticket_no ' || CHR(13) ;
                StrSql := StrSql || ' ,H1.ID_OPR      Cashier ' || CHR(13) ;
                StrSql := StrSql || ' ,H1.TS_TRN_BGN start_time ' || CHR(13) ;
                StrSql := StrSql || ' ,H1.TS_TRN_END end_time ' || CHR(13) ;
                StrSql := StrSql || ' ,to_char(trunc(H1.ts_crt_rcrd - 6/24),'||''''||'DD-MON-YYYY'||''''||') sale_dt ' || CHR(13) ;
                StrSql := StrSql || ' ,H2.ID_ITM_POS Barcode' || CHR(13) ;
                StrSql := StrSql || ' ,H2.ID_ITM      item ' || CHR(13) ;
                StrSql := StrSql || ' ,decode(h1.ty_trn,1,'||''''||'SALE'||''''||',2,'||''''||'RETURN'||''''||',5,'||''''||'EEXCH'||''''||','||''''||'UNKNOWN'||''''||') sale_type ' || CHR(13) ;
                StrSql := StrSql || ' ,H2.QU_ITM_LM_RTN_SLS qty ' || CHR(13) ;
                StrSql := StrSql || ' ,(CASE WHEN H2.qu_itm_lm_rtn_sls < 0 THEN '||''''||'Y'||''''||' ELSE '||''''||'N'||''''||' END ) Retn_Flag ' || CHR(13) ;
                StrSql := StrSql || ' ,H2.mo_extn_ln_itm_rtn amount     ' || CHR(13) ;
                StrSql := StrSql || ' ,H2.mo_extn_dsc_ln_itm net_amount ' || CHR(13) ;
                StrSql := StrSql || ' ,tld.mo_dsc manual_discount ' || CHR(13) ;
                StrSql := StrSql || ' ,(H2.mo_extn_ln_itm_rtn - H2.mo_extn_dsc_ln_itm) Total_discount ' || CHR(13) ;
                StrSql := StrSql || ' ,cmr.ID_PRM MDFR_promo_code ' || CHR(13) ;
                StrSql := StrSql || ' ,cmr.ID_PRM_CMP MDFR_comp_code ' || CHR(13) ;
                StrSql := StrSql || ' ,nvl(cmr.MO_MDFR_RT_PRC,0) MDFR_Discount ' || CHR(13) ;
                StrSql := StrSql || ' ,Promo.ID_PRM LTM_Promo_code ' || CHR(13) ;
                StrSql := StrSql || ' ,Promo.ID_PRM_CMP LTM_Promo_Comp ' || CHR(13) ;
                StrSql := StrSql || ' ,nvl(Promo.MO_MDFR_RT_PRC, 0) LTM_Discount ' || CHR(13) ;
                StrSql := StrSql || ' ,H2.MO_PRN_PRC ' || CHR(13) ;
                StrSql := StrSql || ' ,H2.AI_LN_ITM ' || CHR(13) ;
                StrSql := StrSql || ' from tr_trn H1   ' || CHR(13) ;
                StrSql := StrSql || ' inner join ' || CHR(13) ;
                StrSql := StrSql || ' (    ' || CHR(13) ;
                StrSql := StrSql || '    Select a.*     ' || CHR(13) ;
                StrSql := StrSql || '    from tr_ltm_sls_rtn a    ' || CHR(13) ;
                StrSql := StrSql || '    inner join TR_LTM_RTL_TRN b    ' || CHR(13) ;
                StrSql := StrSql || '    on a.ID_STR_RT = b.ID_STR_RT    ' || CHR(13) ;
                StrSql := StrSql || '    and a.id_ws = b.id_ws     ' || CHR(13) ;
                StrSql := StrSql || '    and a.DC_DY_BSN = b.DC_DY_BSN    ' || CHR(13) ;
                StrSql := StrSql || '    and a.ai_trn = b.ai_trn    ' || CHR(13) ;
                StrSql := StrSql || '    and a.ai_ln_itm = b.ai_ln_itm    ' || CHR(13) ;
                StrSql := StrSql || '    and a.FL_VD_LN_ITM = b.FL_VD_LN_ITM    ' || CHR(13) ;
                StrSql := StrSql || '    and a.FL_VD_LN_ITM = 0        ' || CHR(13) ;
                StrSql := StrSql || ' ) H2    ' || CHR(13) ;
                StrSql := StrSql || ' on H1.dc_dy_bsn = H2.dc_dy_bsn   ' || CHR(13) ;
                StrSql := StrSql || ' and   H1.id_Str_rt= H2.id_str_rt    ' || CHR(13) ;
                StrSql := StrSql || ' and   H1.id_ws = H2.id_ws   ' || CHR(13) ;
                StrSql := StrSql || ' and   H1.ai_trn = H2.ai_trn   ' || CHR(13) ;
                StrSql := StrSql || ' left outer join tr_ltm_dsc tld on   ' || CHR(13) ;
                StrSql := StrSql || ' H2.dc_dy_bsn = tld.dc_dy_bsn ' || CHR(13) ;
                StrSql := StrSql || ' and   H2.id_Str_rt = tld.id_str_rt    ' || CHR(13) ;
                StrSql := StrSql || ' and   H2.id_ws = tld.id_ws   ' || CHR(13) ;
                StrSql := StrSql || ' and   H2.ai_trn = tld.ai_trn   ' || CHR(13) ;
                StrSql := StrSql || ' and   H2.ai_ln_itm = tld.ai_ln_itm   ' || CHR(13) ;
                StrSql := StrSql || ' left outer join CO_MDFR_RTL_PRC cmr on   ' || CHR(13) ;
                StrSql := StrSql || ' H2.dc_dy_bsn = cmr.dc_dy_bsn' || CHR(13) ;
                StrSql := StrSql || ' and   H2.id_Str_rt = cmr.id_str_rt    ' || CHR(13) ;
                StrSql := StrSql || ' and   H2.id_ws = cmr.id_ws   ' || CHR(13) ;
                StrSql := StrSql || ' and   H2.ai_trn = cmr.ai_trn   ' || CHR(13) ;
                StrSql := StrSql || ' and   H2.ai_ln_itm = cmr.ai_ln_itm ' || CHR(13) ;
                StrSql := StrSql || ' left outer join TR_LTM_PRM Promo on ' || CHR(13) ;
                StrSql := StrSql || ' H2.dc_dy_bsn = Promo.dc_dy_bsn ' || CHR(13) ;
                StrSql := StrSql || ' and   H2.id_Str_rt = Promo.id_str_rt     ' || CHR(13) ;
                StrSql := StrSql || ' and   H2.id_ws = Promo.id_ws    ' || CHR(13) ;
                StrSql := StrSql || ' and   H2.ai_trn = Promo.ai_trn    ' || CHR(13) ;
                StrSql := StrSql || ' and   H2.ai_ln_itm = Promo.ai_ln_itm ' || CHR(13) ;
                StrSql := StrSql || ' where H2.fl_vd_ln_itm = 0' || CHR(13) ;
                StrSql := StrSql || ' AND   H1.FL_TRG_TRN = 0     ' || CHR(13) ;
                StrSql := StrSql || ' AND   H1.SC_TRN = 2     ' || CHR(13) ;
                StrSql := StrSql || ' and   Trunc(H1.ts_crt_rcrd - 6/24) >= trunc(sysdate-15) ' || CHR(13) ;
                StrSql := StrSql || ' Order by H1.ID_STR_RT, H1.ID_WS, h1.dc_dy_bsn, h1.AI_TRN ' || CHR(13) ;

                --dbms_output.put_line(StrSql);
                execute immediate StrSql;

                StrSql := null;
                StrSql := ' INSERT INTO INH_RTLOG_PAYMENT' || CHR(13) ;
                StrSql := StrSql || ' SELECT P.ID_STR_RT STORE, P.ID_WS REGISTER, ' || CHR(13) ;
                StrSql := StrSql || ' P.DC_DY_BSN BUSINESS_DATE, to_char(trunc(P.TS_CRT_RCRD - 6/24),'||''''||'DD-MON-YYYY'||''''||') SALE_DT, P.AI_TRN TRN_NO, P.TY_TND TENDER_TYPE, SUM(P.MO_ITM_LN_TND) VALUE, ' || CHR(13) ;
                StrSql := StrSql || ' C.ID_ISSR_TND_MD CARDNAME, C.LU_AJD_CR_DB CC_AUTH_CODE, C.DC_EP_DB_CR_CRD CC_EXP_DATE ' || CHR(13) ;
                StrSql := StrSql || ' FROM TR_LTM_TND P ' || CHR(13) ;
                StrSql := StrSql || ' LEFT OUTER JOIN TR_LTM_CRDB_CRD_TN C ' || CHR(13) ;
                StrSql := StrSql || ' ON P.ID_STR_RT = C.ID_STR_RT ' || CHR(13) ;
                StrSql := StrSql || '    AND P.ID_WS = C.ID_WS ' || CHR(13) ;
                StrSql := StrSql || '    AND P.DC_DY_BSN = C.DC_DY_BSN ' || CHR(13) ;
                StrSql := StrSql || '    AND P.AI_TRN = C.AI_TRN ' || CHR(13) ;
                StrSql := StrSql || '    AND P.AI_LN_ITM = C.AI_LN_ITM ' || CHR(13) ;
                StrSql := StrSql || ' Where   P.TS_CRT_RCRD >= trunc(sysdate-15) ' || CHR(13) ;
                StrSql := StrSql || ' GROUP BY P.ID_STR_RT, P.ID_WS, P.DC_DY_BSN, P.AI_TRN, P.TY_TND, ' || CHR(13) ;
                StrSql := StrSql || '    C.ID_STR_RT, C.ID_WS, C.DC_DY_BSN, to_char(trunc(P.TS_CRT_RCRD - 6/24),'||''''||'DD-MON-YYYY'||''''||'), C.AI_TRN, C.TY_TND, ' || CHR(13) ;
                StrSql := StrSql || '    C.ID_ISSR_TND_MD, C.LU_AJD_CR_DB, C.DC_EP_DB_CR_CRD ' || CHR(13) ;
                StrSql := StrSql || ' Order by P.ID_STR_RT, P.ID_WS, P.DC_DY_BSN, P.AI_TRN ' || CHR(13) ;

                --dbms_output.put_line(StrSql);
                execute immediate StrSql;

--            END LOOP;

--            CLOSE get_schema_name_cur;

            ------------------------------------------------------ file cursor to write dts file
             BEGIN
                lv_file_handle :=
                           UTL_FILE.fopen (lv_error_file_dir, lv_error_file, 'W');

                OPEN get_dts_cur;
                LOOP

                   FETCH get_dts_cur into L_TRN_TYPE, L_DC_DY_BSN, L_ID_STR_RT, L_QTY, L_RETAIL;

                   EXIT WHEN get_dts_cur%NOTFOUND;

                   IF L_DC_DY_BSN IS NOT NULL
                   THEN

                      UTL_FILE.put_line (lv_file_handle, L_TRN_TYPE || '|' || L_DC_DY_BSN || '|' || L_ID_STR_RT || '|' || L_QTY || '|' || L_RETAIL );

                   END IF;

                   --dbms_output.put_line('get_sales_info_cur');

                END LOOP;

                CLOSE get_dts_cur;

                UTL_FILE.fclose (lv_file_handle);

             END;

            ------------------------------------------------------ file cursor to write SALES file
             BEGIN
                lv_file_handle :=
                           UTL_FILE.fopen (lv_error_file_dir, lv_sale_file, 'W');

                OPEN get_sales_det_cur;
                LOOP

                   FETCH get_sales_det_cur into S_Store,S_Reg,S_Buss_Date,S_Tick_No,S_Cash,S_Strt_Time,S_End_Time,S_Sale_Dt, S_Barcode, S_Item,S_Type,S_Qty,S_Flag,S_Amt,S_Nt_Amt,S_Man_Disc,S_Tot_Disc, S_MDFR_PROMO_CODE, S_MDFR_PROMO_COMP, S_MDFR_DISCOUNT, S_LTM_PROMO_CODE, S_LTM_PROMO_COMP, S_LTM_DISCOUNT, S_MO_PRN_PRC,S_LN_ITM;

                   EXIT WHEN get_sales_det_cur%NOTFOUND;

                   IF L_DC_DY_BSN IS NOT NULL
                   THEN

                      UTL_FILE.put_line (lv_file_handle, S_Store|| '|' ||S_Reg|| '|' ||S_Buss_Date|| '|' ||S_Tick_No|| '|' ||S_Cash|| '|' ||S_Strt_Time|| '|' ||S_End_Time|| '|' ||S_Sale_Dt|| '|' ||S_Barcode|| '|' ||S_Item|| '|' ||S_Type|| '|' ||S_Qty|| '|' ||S_Flag|| '|' ||S_Amt|| '|' ||S_Nt_Amt|| '|' ||S_Man_Disc|| '|' ||S_Tot_Disc|| '|' ||S_MDFR_PROMO_CODE|| '|' ||S_MDFR_PROMO_COMP|| '|' ||S_MDFR_DISCOUNT|| '|' ||S_LTM_PROMO_CODE|| '|' ||S_LTM_PROMO_COMP|| '|' ||S_LTM_DISCOUNT|| '|' ||S_MO_PRN_PRC|| '|' || S_LN_ITM);


                   END IF;
                   --dbms_output.put_line('get_sales_det_cur');
                END LOOP;

                CLOSE get_sales_det_cur;

                UTL_FILE.fclose (lv_file_handle);

             END;

            ------------------------------------------------------ file cursor to write PAYMENT file
             BEGIN
                lv_file_handle :=
                           UTL_FILE.fopen (lv_error_file_dir, lv_payment_file, 'W');

                OPEN get_payment_det_cur;
                LOOP

                   FETCH get_payment_det_cur into P_STORE,P_REGISTER,P_BUSINESS_DATE, P_SALE_DT, P_TRN_NO,P_TENDER_TYPE,P_VALUE,P_CARDNAME ,P_AUTH_CODE,P_EXP_DATE;

                   EXIT WHEN get_payment_det_cur%NOTFOUND;

                   IF P_STORE IS NOT NULL
                   THEN

                      UTL_FILE.put_line (lv_file_handle, P_STORE|| '|' ||P_REGISTER|| '|' ||P_BUSINESS_DATE|| '|' || P_SALE_DT || '|' ||P_TRN_NO|| '|' ||P_TENDER_TYPE|| '|' ||P_VALUE|| '|' ||P_CARDNAME || '|' ||P_AUTH_CODE|| '|' ||P_EXP_DATE);

                   END IF;
                   --dbms_output.put_line('get_payment_det_cur');
                END LOOP;

                CLOSE get_payment_det_cur;

                UTL_FILE.fclose (lv_file_handle);

             END;

             BEGIN
                lv_file_handle :=
                           UTL_FILE.fopen (lv_error_file_dir, lv_ibt_file, 'W');

                OPEN get_frn_gdn_cur;
                LOOP

                   FETCH get_frn_gdn_cur into S_Store,I_ALOC_TYPE,I_ALLOCATION_NUMBER, I_FROM_STORE,I_TO_STORE,I_GDN_NO,I_STARTDATE,I_STATUS,I_CONFIRMDATE,I_QUANTITY;

                   EXIT WHEN get_frn_gdn_cur%NOTFOUND;

                   IF I_ALLOCATION_NUMBER IS NOT NULL
                   THEN

                    UTL_FILE.put_line (lv_file_handle,S_Store|| '|' || I_ALOC_TYPE|| '|' ||I_ALLOCATION_NUMBER|| '|' || I_FROM_STORE|| '|' ||I_TO_STORE|| '|' ||I_GDN_NO|| '|' ||I_STARTDATE|| '|' ||I_STATUS|| '|' ||I_CONFIRMDATE|| '|' ||I_QUANTITY );
                    --dbms_output.put_line (I_ALLOCATION_NUMBER|| '|' || I_FROM_STORE|| '|' ||I_TO_STORE|| '|' ||I_GDN_NO|| '|' ||I_STARTDATE|| '|' ||I_STATUS|| '|' ||I_CONFIRMDATE|| '|' ||I_QUANTITY );
                   END IF;

                   dbms_output.put_line('get_sales_info_cur');

                END LOOP;

                CLOSE get_frn_gdn_cur;

                UTL_FILE.fclose (lv_file_handle);

             END;
            -------------------------------------------------------------------

          RETURN TRUE;
       EXCEPTION
          WHEN OTHERS
          THEN
                dbms_output.put_line(to_char(sqlerrm));

             RETURN FALSE;
       END ah_gen_dts_file;
END ah_gen_dts_file_pkg;

Answers (3)

inserire il dato nel database access (senza generare nuovo)

Print All the items in a windows form in C#