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;
Loading
Guest UserPosted Apr 24, 2015, 3:08 AM
Feroz KhanPosted Apr 23, 2015, 1:23 PM
Guest UserPosted Apr 23, 2015, 10:46 AM