create or replace package PKG_FIN_RPT_CASH_RECEIPTS is
procedure p_mma_rpt_fin_103_cash(i_report_id in number);
procedure p_rpt_fin_bounced_cheque(i_report_id in number);
procedure p_rpt_fin_cheque_refund1(i_report_id in number,
i_date_from in varchar2,
i_date_to in varchar2,
i_user_id in number,
i_report_Type in varchar2);
procedure p_rpt_fin_cheque_refund2(i_report_id in number,
i_date_from in varchar2,
i_date_to in varchar2,
i_report_Type in varchar2);
function f_get_contract_no_by_fee_id(i_fee_id in number,
i_CURRENT_DATE in DATE)
return varchar2;
function f_get_client_name_by_fee_id(i_fee_id in number,
i_CURRENT_DATE in DATE)
return varchar2;
end PKG_FIN_RPT_CASH_RECEIPTS;
/
create or replace package body PKG_FIN_RPT_CASH_RECEIPTS is
-- Finance Report for MMA
-- author : mlw
-- date : 2019/3/15 10:00
-- PKG_FIN_RPT_CASH_RECEIPTS
procedure p_mma_rpt_fin_103_cash(i_report_id in number) is
v_countnewold number;
-- v_count number := 0;
V_CURRENT_DATE DATE := PKG_LS_PUB_UTIL.F_GET_PROCESS_DATE;
v_reporId RPT_CL010_COMMON.C_SYSRPT_REPORT_ID%type := 'A0026'; --C_SYSRPT_REPORT_ID VARCHAR2(5 CHAR),
v_rptSeqNo RPT_CL010_COMMON.C_SYSRPT_SEQ_NO%type := 'G'; --C_SYSRPT_SEQ_NO VARCHAR2(1 CHAR),
-- v_sysrpt_dept varchar2(10); --C_SYSRPT_DEPT VARCHAR2(10 CHAR)
--v_COMPANY_NAME VARCHAR2(40 CHAR) := 'MassMutual Asia Ltd.'; --C_WK_COMPANY_NAME_LG_C
v_sysrpt_desc varchar2(40) := 'Cash Receipts Post Print Control Rpt(MC)'; --C_SYSRPT_DESC
v_USERID varchar2(3) := 'OPR'; --C_USER_ID
v_channel_desc varchar2(80); --C_WK_SYS_RPT_NOTE
v_orgin number := pkg_pub_app_context.F_GET_ORGAN_ID;
v_fee_id t_cash.fee_id%type;
-- v_check_amendment t_cash.AMENDMENT_RECEIPT_INDI%type;
v_fin_rpt_A0026G_RECORD T_RPT_A0026G_RECORD%ROWTYPE;
cursor c_fin_rpt_A0026G_RECORD IS
select a.fee_id
from t_cash a, t_cash_batch b, t_contract_master c, T_RECEIPT d
where a.batch_id = b.batch_id
and a.RECEIPT_ID = d.RECEIPT_ID
and c.policy_id = a.policy_id
and b.status = 3
and c.organ_id = 102
and a.pay_amount > 0
and a.receipt_id IS NOT NULL
and trunc(d.print_time) = V_CURRENT_DATE
and a.OFFICIAL_RECEIPT_INDI = 'Y'
and (a.is_splited <> 'Y' or a.is_splited is null)
UNION
select a.fee_id
from t_cash a, t_cash_batch b, T_RECEIPT d
where a.batch_id = b.batch_id
and a.RECEIPT_ID = d.RECEIPT_ID
and b.status = 3
and ((b.organ_id = 102 and b.other_location = 'N') or
(b.organ_id <> 102 and b.other_location = 'Y'))
and a.pay_amount > 0
and a.receipt_id IS NOT NULL
and trunc(d.print_time) = V_CURRENT_DATE
and a.OFFICIAL_RECEIPT_INDI = 'Y'
and (a.is_splited <> 'Y' or a.is_splited is null);
begin
PKG_PUB_SCD_CI.P_LOG_INFO('p_mma_rpt_fin_103_cash begin');
for temp_table01 in (select a.sysch_desc
from T_SYSCH a
where a.sysch_channel_id in
(select t.sysrptch_channel_id
from T_SYSRPTCH t
where t.sysrptch_report_id = v_reporId
and t.sysrptch_seq_no = v_rptSeqNo)) loop
begin
if v_channel_desc is null then
v_channel_desc := v_channel_desc || temp_table01.sysch_desc;
else
v_channel_desc := v_channel_desc || ',' ||
temp_table01.sysch_desc;
end if;
end;
end loop;
pkg_pub_scd_ci.p_log_info('PKG_FIN_RPT_CASH_RECEIPTS ,V_CURRENT_DATE=' ||
V_CURRENT_DATE);
PKG_PUB_SCD_CI.P_LOG_INFO('p_mma_rpt_fin_103_cash insert common begin...');
insert into T_RPT_A0026G_COMMON
values
(i_report_id, --C_SPOOL_ID
v_reporId, --C_SYSRPT_REPORT_ID
v_rptSeqNo, --C_SYSRPT_SEQ_NO
(select t.sysrpt_dept_id
from t_sysrpt t
where t.sysrpt_report_id = v_reporId
and t.sysrpt_seq_no = v_rptSeqNo), --C_SYSRPT_DEPT
(select t.COMPANY_NAME from t_org t where t.ORGAN_ID = v_orgin), --C_WK_COMPANY_NAME_LG_C
PKG_LS_PUB_UTILS.F_GET_PROCESS_DATE, --C_WORK_DATE_TIME
v_sysrpt_desc, --C_SYSRPT_DESC
v_userId, --C_USER_ID
v_channel_desc --C_WK_SYS_RPT_NOTE
);
PKG_PUB_SCD_CI.P_LOG_INFO('p_mma_rpt_fin_103_cash insert record begin...');
OPEN c_fin_rpt_A0026G_RECORD;
LOOP
FETCH c_fin_rpt_A0026G_RECORD
into v_fee_id;
EXIT WHEN c_fin_rpt_A0026G_RECORD%NOTFOUND;
v_fin_rpt_A0026G_RECORD := null;
v_fin_rpt_A0026G_RECORD.SPOOL_ID := i_report_id;
v_fin_rpt_A0026G_RECORD.LINE_NO := S_RPT_A0026G_RECORD__LINE_NO.Nextval;
BEGIN
select Receipt_id,
send_code,
(select money_code
from t_money, t_cash
where t_cash.pay_money_id = t_money.money_id
and fee_id = v_fee_id),
pay_amount,
(select money_code
from t_money, t_cash
where t_cash.money_id = t_money.money_id
and fee_id = v_fee_id),
cheque_code_date,
cheque_tt_card_no
into v_fin_rpt_A0026G_RECORD.WORK_ALPHA_22_CHAR_1,
v_fin_rpt_A0026G_RECORD.CASHRECH_CONTRACT_NO,
v_fin_rpt_A0026G_RECORD.CASHRECH_IN_CURR_CODE,
v_fin_rpt_A0026G_RECORD.CASHRECH_INPUT_AMT,
v_fin_rpt_A0026G_RECORD.CASHRECH_TRX_CURR_CODE,
v_fin_rpt_A0026G_RECORD.CASHRECH_CHECK_DATE,
v_fin_rpt_A0026G_RECORD.CASHRECH_CHECK_NO
from t_cash
where fee_id = v_fee_id;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
select b.type_name
into v_fin_rpt_A0026G_RECORD.CASHRECH_PAYMENT_TYPE
from t_paymode_type b, t_cash a
where a.pay_mode = b.paymode_type
and a.fee_id = v_fee_id;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
SELECT count(*)
INTO v_countnewold
FROM (select related_id from t_cash WHERE related_id is not null)
WHERE related_id = v_fee_id;
if v_countnewold > 0 THEN
v_fin_rpt_A0026G_RECORD.WORK_NUMBER_6V2_1 := 0;
select FEE_AMOUNT
into v_fin_rpt_A0026G_RECORD.WORK_NUMBER_6V2_2
from t_cash
where fee_id = v_fee_id;
else
v_fin_rpt_A0026G_RECORD.WORK_NUMBER_6V2_2 := 0;
select FEE_AMOUNT
into v_fin_rpt_A0026G_RECORD.WORK_NUMBER_6V2_1
from t_cash
where fee_id = v_fee_id;
end if;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
select distinct (CC.COMPANY_NAME || CC.COMPANY_NAME_CN ||
NVL(C.LAST_NAME, '') || ' ' ||
NVL(C.FIRST_NAME, '') || ' ' ||
NVL(C.ENGLISH_NAME, ''))
into v_fin_rpt_A0026G_RECORD.WORK_CLIENT_NAME_1
from t_cash tc, t_contract_master cm, t_insured_list til
LEFT JOIN t_customer c
ON til.party_id = c.customer_id
left join t_company_customer cc
on til.party_id = cc.company_id
where tc.policy_id = cm.policy_id
and cm.policy_id = til.policy_id
and tc.fee_id = v_fee_id;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
SELECT COALESCE(a.address_1, a.address_8)
into v_fin_rpt_A0026G_RECORD.WORK_ADDRESS_1_1
FROM T_cash b, t_address a
where b.payee_address_id = a.address_id
and b.fee_id = v_fee_id;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
select a.receipt_id
into v_fin_rpt_A0026G_RECORD.WORK_ALPHA_20_CHAR_1
from t_cash a
where a.fee_id =
(select t.related_id from t_cash t where t.fee_id = v_fee_id);
EXCEPTION
WHEN OTHERS THEN
null;
END;
INSERT INTO T_RPT_A0026G_RECORD VALUES v_fin_rpt_A0026G_RECORD;
END LOOP;
CLOSE c_fin_rpt_A0026G_RECORD;
EXCEPTION
WHEN OTHERS THEN
PKG_PUB_SCD_CI.P_LOG_ERROR(SQLERRM ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
end p_mma_rpt_fin_103_cash;
procedure p_rpt_fin_bounced_cheque(i_report_id in number) is
V_CURRENT_DATE DATE := PKG_LS_PUB_UTIL.F_GET_PROCESS_DATE;
-- V_CURRENT_DATE DATE := PKG_LS_PUB_UTIL.F_GET_PROCESS_DATE;
V_COM pkg_mma_rpt_fin.TYPE_COM;
V_RPT_P0125A_RECORD T_RPT_P0125A_RECORD%ROWTYPE;
V_RPT_P0125A_COMMON T_RPT_P0125A_COMMON%ROWTYPE;
v_fee_id t_cash.fee_id%type;
num_record NUMBER;
-- v_fee_amount NUMBER;
v_layer_count NUMBER;
v_um_count NUMBER;
v_WIT_count NUMBER;
v_pos_count NUMBER;
v_organ_id t_cash.organ_id%type;
v_top_organ_id t_cash.organ_id%type;
cursor c_RPT_P0125A_RECORD IS
select a.fee_id
from t_cash a, t_contract_product b, t_product_life c, t_reversal e
where a.policy_id = b.policy_id
and b.product_id = c.product_id
and a.reversal_id = e.reversal_id
and b.charge_period = 1
and c.ulseries_indi = 12
and e.reason_id in (443,
444,
446,
447,
448,
449,
450,
451,
452,
453,
454,
455,
456,
457,
458,
459,
460)
and c.dump_withdraw_indi = 'Y'
and trunc(e.insert_time) = V_CURRENT_DATE
and a.pay_mode in (2, 65, 66, 73, 47, 95)
and a.fee_status in (2, 11)
and a.reversal_id is not null;
begin
V_RPT_P0125A_COMMON.C_SPOOL_ID := i_report_id;
V_RPT_P0125A_COMMON.C_WK_SYS_RPT_ID := 'P0125';
V_RPT_P0125A_COMMON.C_WK_SYS_RPT_SEQ := 'A';
V_COM := pkg_mma_rpt_fin.F_GET_COMMON('P0125',
'A');
V_RPT_P0125A_COMMON.C_WK_SYS_RPT_DEPT := V_COM.C_SYSRPT_DEPT;
V_RPT_P0125A_COMMON.C_WK_COMPANY_NAME_LG_C := V_COM.C_WK_COMPANY_NAME_LG_C;
V_RPT_P0125A_COMMON.C_WORK_DATE_TIME := V_COM.C_WORK_DATE_TIME;
V_RPT_P0125A_COMMON.C_SYSRPT_DESC := V_COM.C_SYSRPT_DESC;
V_RPT_P0125A_COMMON.C_USER_ID := V_COM.C_USER_ID;
--V_RPT_P0125A_COMMON.C_WK_VALUATION_DATE := V_COM.C_WK_VALUATION_DATE;
V_RPT_P0125A_COMMON.C_WK_SYS_RPT_NOTE := V_COM.C_WK_SYS_RPT_NOTE;
V_RPT_P0125A_COMMON.C_SYSRPT_SPOOL_PREFIX := V_COM.C_SYSRPT_SPOOL_PREFIX;
V_RPT_P0125A_COMMON.C_SYSRPT_REPORT_ID := 'P0125';
V_RPT_P0125A_COMMON.C_SYSRPT_SEQ_NO := 'A';
-- V_RPT_P0125A_COMMON.C_WK_RPT_END_COUNT := 0;
INSERT INTO T_RPT_P0125A_COMMON VALUES V_RPT_P0125A_COMMON;
OPEN c_RPT_P0125A_RECORD;
LOOP
FETCH c_RPT_P0125A_RECORD
into v_fee_id;
EXIT WHEN c_RPT_P0125A_RECORD%NOTFOUND;
V_RPT_P0125A_RECORD := null;
V_RPT_P0125A_RECORD.SPOOL_ID := i_report_id;
BEGIN
select a.send_code,
a.prem_purpose,
c.sis_code,
c.product_name || '(' || c.sis_code || ')',
-- a.fee_amount,
d.money_code,
a.check_enter_time,
e.cheque_no,
c.sis_code,
(select e.money_code
from t_money e, t_contract_master b, t_cash t
where e.money_id = b.money_id
and t.send_code = b.policy_code
and t.fee_id = v_fee_id),
a.pay_balance,
a.pay_amount,
a.fee_amount,
a.organ_id
into V_RPT_P0125A_RECORD.PRCTRLWK_CONTRACT_NO,
V_RPT_P0125A_RECORD.PRCTRLWK_PREMIUM_CODE,
V_RPT_P0125A_RECORD.PLANSPEC_PLAN_ID,
V_RPT_P0125A_RECORD.L2_WORK_ALPHA_38_CHAR,
-- v_fee_amount,
V_RPT_P0125A_RECORD.PRCTRLWK_CHECK_INP_CUR,
V_RPT_P0125A_RECORD.PRCTRLWK_INT_CTRL_DATE,
V_RPT_P0125A_RECORD.PRCTRLWK_CHECK_NO,
V_RPT_P0125A_RECORD.L2_PLANSPEC_PLAN_ID,
V_RPT_P0125A_RECORD.PRCTRLWK_POLICY_CURR,
V_RPT_P0125A_RECORD.WK_SUSPENSE_TOTAL,
V_RPT_P0125A_RECORD.PRCTRLWK_CHECK_INP_AMT,
V_RPT_P0125A_RECORD.PRCTRLWK_CHECK_TRX_AMT,
v_organ_id
from t_cash a,
t_contract_master f,
t_contract_product b,
t_product_life c,
t_money d,
t_cheque e
where a.policy_id = f.policy_id
and b.policy_id = f.policy_id
and b.product_id = c.product_id
and a.pay_money_id = d.money_id
and a.cheque_id = e.cheque_id
and b.master_id is null
and a.fee_id = v_fee_id;
EXCEPTION
WHEN OTHERS THEN
null;
END;
V_RPT_P0125A_RECORD.SPOOL_ID := i_report_id;
V_RPT_P0125A_RECORD.LINE_NO := S_T_P0125A_RECORD__LINE_NO.NEXTVAL;
V_RPT_P0125A_RECORD.CONMSTR_CURR_CODE := V_RPT_P0125A_RECORD.PRCTRLWK_POLICY_CURR;
BEGIN
v_top_organ_id := PKG_LS_GL_COMMON.F_GET_COMPANY_ID(v_organ_id);
if v_top_organ_id = 101 THEN
V_RPT_P0125A_RECORD.CONMSTR_ISSUE_STATE := 'HK';
V_RPT_P0125A_RECORD.L1_WORK_ALPHA_10_CHAR_1 := 'Hong Kong';
elsif v_top_organ_id = 102 THEN
V_RPT_P0125A_RECORD.CONMSTR_ISSUE_STATE := 'MC';
V_RPT_P0125A_RECORD.L1_WORK_ALPHA_10_CHAR_1 := 'Macau';
else
V_RPT_P0125A_RECORD.CONMSTR_ISSUE_STATE := '';
V_RPT_P0125A_RECORD.L1_WORK_ALPHA_10_CHAR_1 := '';
end if;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
select count(1)
into v_um_count
from t_um_detail_fee t
where t.table_type = 2
and t.fee_id = v_fee_id;
if v_um_count > 0 THEN
V_RPT_P0125A_RECORD.WORK_ALPHA_1_CHAR_4 := '#';
else
V_RPT_P0125A_RECORD.WORK_ALPHA_1_CHAR_4 := '';
end if;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
select count(1)
into v_layer_count
from t_cash a, t_contract_segment b
where a.policy_id = b.policy_id
and b.segment_type not in (1, 2)
and a.fee_id = v_fee_id;
if v_layer_count > 0 THEN
V_RPT_P0125A_RECORD.WORK_ALPHA_1_CHAR_3 := '*';
else
V_RPT_P0125A_RECORD.WORK_ALPHA_1_CHAR_3 := '';
end if;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
select max(b.finish_time)
into V_RPT_P0125A_RECORD.WK_PREMIUM_APPLY_DATE
from t_cash a, t_prem_arap b
where a.cheque_id = b.cheque_id
and a.fee_id = v_fee_id;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
select b.dispatch_date
into V_RPT_P0125A_RECORD.WORK_DELIVERY_DATE
from t_cash a, t_policy_acknowledgement b
where a.policy_id = b.policy_id
and a.fee_id = v_fee_id;
EXCEPTION
WHEN OTHERS THEN
null;
END;
V_RPT_P0125A_RECORD.WORK_CONTRACT_NO_1 := f_get_contract_no_by_fee_id(v_fee_id,
V_CURRENT_DATE);
V_RPT_P0125A_RECORD.WORK_CLIENT_NAME_1 := f_get_client_name_by_fee_id(v_fee_id,
V_CURRENT_DATE);
BEGIN
select count(1)
into v_WIT_count
from t_cash a,
t_contract_product b,
t_product_life c,
--t_prem_arap d ,
t_policy_change e,
t_service f,
t_fund_trans_apply g
where a.policy_id = b.policy_id
and b.product_id = c.product_id
--and d.policy_id = c.product_id
and b.policy_id = e.policy_id
and e.policy_id = g.policy_id
and e.service_id = f.service_id
and f.service_code = 'WIT'
and g.backdating_calc_gl_indi = 'Y'
and c.special_plaan = 1
and a.fee_id = v_fee_id;
if v_WIT_count > 0 THEN
V_RPT_P0125A_RECORD.WORK_ALPHA_1_CHAR_1 := 'Y';
else
V_RPT_P0125A_RECORD.WORK_ALPHA_1_CHAR_1 := '';
end if;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
select count(1)
into v_pos_count
from t_sa_change a,
t_policy_change b,
t_cash c,
t_contract_product d,
t_product_life e
where a.policy_id = b.policy_id
and b.policy_id = c.policy_id
and c.policy_id = d.policy_id
and d.product_id = e.product_id
and b.policy_chg_status = 2
and e.special_plaan = 1
and c.fee_id = v_fee_id;
if v_pos_count > 0 THEN
V_RPT_P0125A_RECORD.WORK_ALPHA_1_CHAR_2 := 'Y';
else
V_RPT_P0125A_RECORD.WORK_ALPHA_1_CHAR_2 := '';
end if;
EXCEPTION
WHEN OTHERS THEN
null;
END;
INSERT INTO T_RPT_P0125A_RECORD VALUES V_RPT_P0125A_RECORD;
END LOOP;
CLOSE c_RPT_P0125A_RECORD;
select count(1)
into num_record
from T_RPT_P0125A_RECORD t
where t.spool_id = i_report_id;
update T_RPT_P0125A_COMMON t
set t.C_WK_RPT_END_COUNT = num_record
where t.c_spool_id = i_report_id;
end;
function f_get_contract_no_by_fee_id(i_fee_id in number,
i_CURRENT_DATE in DATE)
return varchar2 as
v_CONTRACT_NO varchar2(8);
sendCode varchar2(8);
count1 number;
begin
select t.send_code
into sendCode
from t_cash t
where t.prem_purpose <> '01'
and ((t.money_id = 3 and t.pay_amount > 80) or
(t.money_id = 25 and t.pay_amount > 80) or
(t.money_id = 4 and t.pay_amount > 10) or
(t.money_id = 1 and t.pay_amount > 80))
and t.fee_id = i_fee_id;
if sendCode is not null THEN
select count(1)
into count1
from t_cash t, t_reversal e
where t.reversal_id = e.reversal_id
and e.reason_id in (443,
444,
446,
447,
448,
449,
450,
451,
452,
453,
454,
455,
456,
457,
458,
459,
460)
and t.prem_purpose = '01'
and trunc(e.insert_time) < i_CURRENT_DATE
and t.fee_id <> i_fee_id
and t.send_code = sendCode;
if count1 > 0 then
null;
else
v_CONTRACT_NO := sendCode;
end if;
else
select t.send_code
into sendCode
from t_cash t
where t.fee_id = i_fee_id;
v_CONTRACT_NO := sendCode;
end if;
return v_CONTRACT_NO;
exception
WHEN OTHERS THEN
select t.send_code
into sendCode
from t_cash t
where t.fee_id = i_fee_id;
v_CONTRACT_NO := sendCode;
return v_CONTRACT_NO;
end;
function f_get_client_name_by_fee_id(i_fee_id in number,
i_CURRENT_DATE in DATE)
return varchar2 as
v_CLIENT_NAME varchar2(42);
v_CLIENT_NAME_TEMP varchar2(42);
sendCode varchar2(8);
count1 number;
begin
select distinct (CC.COMPANY_NAME || CC.COMPANY_NAME_CN ||
NVL(C.LAST_NAME, '') || ' ' || NVL(C.FIRST_NAME, '') || ' ' ||
NVL(C.ENGLISH_NAME, ''))
into v_CLIENT_NAME_TEMP
from t_cash tc, t_contract_master cm, t_insured_list til
LEFT JOIN t_customer c
ON til.party_id = c.customer_id
left join t_company_customer cc
on til.party_id = cc.company_id
where tc.policy_id = cm.policy_id
and cm.policy_id = til.policy_id
and tc.fee_id = i_fee_id;
select t.send_code
into sendCode
from t_cash t
where t.prem_purpose <> '01'
and ((t.money_id = 3 and t.pay_amount > 80) or
(t.money_id = 25 and t.pay_amount > 80) or
(t.money_id = 4 and t.pay_amount > 10) or
(t.money_id = 1 and t.pay_amount > 80))
and t.fee_id = i_fee_id;
if sendCode is not null THEN
select count(1)
into count1
from t_cash t, t_reversal e
where t.reversal_id = e.reversal_id
and e.reason_id in (443,
444,
446,
447,
448,
449,
450,
451,
452,
453,
454,
455,
456,
457,
458,
459,
460)
and t.prem_purpose = '01'
and trunc(e.insert_time) < i_CURRENT_DATE
and t.fee_id <> i_fee_id
and t.send_code = sendCode;
if count1 > 0 then
null;
else
v_CLIENT_NAME := v_CLIENT_NAME_TEMP;
end if;
else
v_CLIENT_NAME := v_CLIENT_NAME_TEMP;
end if;
return v_CLIENT_NAME;
exception
WHEN OTHERS THEN
v_CLIENT_NAME := v_CLIENT_NAME_TEMP;
return v_CLIENT_NAME;
end;
procedure p_rpt_fin_cheque_refund1(i_report_id in number,
i_date_from in varchar2,
i_date_to in varchar2,
i_user_id in number,
i_report_Type in varchar2) is
V_COM pkg_mma_rpt_fin.TYPE_COM;
V_RPT_PM005_RECORD T_RPT_PM005_RECORD%ROWTYPE;
V_RPT_PM005_COMMON T_RPT_PM005_COMMON%ROWTYPE;
v_organ_id t_cash.organ_id%type;
v_top_organ_id t_cash.organ_id%type;
v_cheque_type varchar2(2);
v_fee_id t_cash.fee_id%type;
cursor c_RPT_PM005_RECORD IS
select a.fee_id
from t_cash a, t_cheque b, t_reason_code c
where a.cheque_id = b.cheque_id
and a.reason_code_id = c.list_id
and c.module = 1
and b.print_by = i_user_id
and a.reason_code_id is not null
and trunc(a.insert_time) >= to_date(i_date_from, 'mm/dd/yyyy')
and trunc(a.insert_time) <= to_date(i_date_to, 'mm/dd/yyyy')
and a.pay_mode in (2, 65, 66, 73, 47, 95)
and a.fee_type = 32
and a.fee_status not in (-1, 0);
begin
V_RPT_PM005_COMMON.C_SPOOL_ID := i_report_id;
V_RPT_PM005_COMMON.C_SYSRPT_REPORT_ID := 'PM005';
V_RPT_PM005_COMMON.C_SYSRPT_SEQ_NO := 'A';
V_COM := pkg_mma_rpt_fin.F_GET_COMMON('PM005',
'A');
V_RPT_PM005_COMMON.C_WK_COMPANY_NAME_LG_C := V_COM.C_WK_COMPANY_NAME_LG_C;
V_RPT_PM005_COMMON.C_SYSRPT_DESC := V_COM.C_SYSRPT_DESC;
V_RPT_PM005_COMMON.C_USER_ID := V_COM.C_USER_ID;
V_RPT_PM005_COMMON.C_WK_SYS_RPT_NOTE := V_COM.C_WK_SYS_RPT_NOTE;
V_RPT_PM005_COMMON.C_DATEFROM := to_date(i_date_from,
'mm/dd/yyyy');
V_RPT_PM005_COMMON.C_DATETO := to_date(i_date_to,
'mm/dd/yyyy');
INSERT INTO T_RPT_PM005_COMMON VALUES V_RPT_PM005_COMMON;
OPEN c_RPT_PM005_RECORD;
LOOP
FETCH c_RPT_PM005_RECORD
into v_fee_id;
EXIT WHEN c_RPT_PM005_RECORD%NOTFOUND;
BEGIN
V_RPT_PM005_RECORD := null;
V_RPT_PM005_RECORD.SPOOL_ID := i_report_id;
V_RPT_PM005_RECORD.LINE_NO := S_T_PM005_RECORD__LINE_NO.NEXTVAL;
select d.cheque_no,
a.payee_name,
a.pay_amount,
a.cheque_type,
trunc(a.insert_time),
a.send_code,
b.reason,
c.dept_name,
e.money_code,
'#' || a.send_code || ' ' || b.reason,
a.organ_id
into V_RPT_PM005_RECORD.CHQNO,
V_RPT_PM005_RECORD.PAYEE,
V_RPT_PM005_RECORD.AMOUNT,
v_cheque_type,
V_RPT_PM005_RECORD.CHQDT,
V_RPT_PM005_RECORD.POLICYNO,
V_RPT_PM005_RECORD.REASON,
V_RPT_PM005_RECORD.DEPT,
V_RPT_PM005_RECORD.CURR,
V_RPT_PM005_RECORD.PAR,
v_organ_id
from t_cash a, t_reason_code b, t_dept c, t_cheque d, t_money e
where a.reason_code_id = b.list_id
and a.cashier_dept_id = c.dept_id
and a.pay_money_id = e.money_id
and a.cheque_id = d.cheque_id
and a.fee_id = v_fee_id;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
if v_cheque_type = 7 THEN
V_RPT_PM005_RECORD.CHQTYPE := 'O';
else
V_RPT_PM005_RECORD.CHQTYPE := 'C';
end if;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
v_top_organ_id := PKG_LS_GL_COMMON.F_GET_COMPANY_ID(v_organ_id);
if v_top_organ_id = 101 THEN
V_RPT_PM005_RECORD.LOC := 'HK';
elsif v_top_organ_id = 102 THEN
V_RPT_PM005_RECORD.LOC := 'MC';
else
V_RPT_PM005_RECORD.LOC := '';
end if;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
select c.status_desc
into V_RPT_PM005_RECORD.CHQSTATUS
from t_cash a, t_cheque b, t_cheque_status c
where a.cheque_id = b.cheque_id
and b.cheque_status = c.cheque_status
and a.fee_id = v_fee_id;
EXCEPTION
WHEN OTHERS THEN
null;
END;
INSERT INTO T_RPT_PM005_RECORD VALUES V_RPT_PM005_RECORD;
END LOOP;
CLOSE c_RPT_PM005_RECORD;
end;
procedure p_rpt_fin_cheque_refund2(i_report_id in number,
i_date_from in varchar2,
i_date_to in varchar2,
i_report_Type in varchar2) is
V_COM pkg_mma_rpt_fin.TYPE_COM;
V_RPT_PM005_RECORD T_RPT_PM005_RECORD%ROWTYPE;
V_RPT_PM005_COMMON T_RPT_PM005_COMMON%ROWTYPE;
v_organ_id t_cash.organ_id%type;
v_top_organ_id t_cash.organ_id%type;
v_cheque_type varchar2(2);
v_fee_id t_cash.fee_id%type;
cursor c_RPT_PM005_RECORD IS
select a.fee_id
from t_cash a, t_cheque b, t_reason_code c
where a.cheque_id = b.cheque_id
and a.reason_code_id = c.list_id
and c.module = 1
and a.reason_code_id is not null
and trunc(a.insert_time) >= to_date(i_date_from, 'mm/dd/yyyy')
and trunc(a.insert_time) <= to_date(i_date_to, 'mm/dd/yyyy')
and a.pay_mode in (2, 65, 66, 73, 47, 95)
and a.fee_type = 32
and a.fee_status not in (-1, 0);
begin
V_RPT_PM005_COMMON.C_SPOOL_ID := i_report_id;
V_RPT_PM005_COMMON.C_SYSRPT_REPORT_ID := 'PM005';
V_RPT_PM005_COMMON.C_SYSRPT_SEQ_NO := 'A';
V_COM := pkg_mma_rpt_fin.F_GET_COMMON('PM005',
'A');
V_RPT_PM005_COMMON.C_WK_COMPANY_NAME_LG_C := V_COM.C_WK_COMPANY_NAME_LG_C;
V_RPT_PM005_COMMON.C_SYSRPT_DESC := V_COM.C_SYSRPT_DESC;
V_RPT_PM005_COMMON.C_USER_ID := V_COM.C_USER_ID;
V_RPT_PM005_COMMON.C_WK_SYS_RPT_NOTE := V_COM.C_WK_SYS_RPT_NOTE;
V_RPT_PM005_COMMON.C_DATEFROM := to_date(i_date_from,
'mm/dd/yyyy');
V_RPT_PM005_COMMON.C_DATETO := to_date(i_date_to,
'mm/dd/yyyy');
INSERT INTO T_RPT_PM005_COMMON VALUES V_RPT_PM005_COMMON;
OPEN c_RPT_PM005_RECORD;
LOOP
FETCH c_RPT_PM005_RECORD
into v_fee_id;
EXIT WHEN c_RPT_PM005_RECORD%NOTFOUND;
BEGIN
V_RPT_PM005_RECORD := null;
V_RPT_PM005_RECORD.SPOOL_ID := i_report_id;
V_RPT_PM005_RECORD.LINE_NO := S_T_PM005_RECORD__LINE_NO.NEXTVAL;
select d.cheque_no,
a.payee_name,
a.pay_amount,
a.cheque_type,
trunc(a.insert_time),
a.send_code,
b.reason,
c.dept_name,
e.money_code,
'#' || a.send_code || ' ' || b.reason,
a.organ_id
into V_RPT_PM005_RECORD.CHQNO,
V_RPT_PM005_RECORD.PAYEE,
V_RPT_PM005_RECORD.AMOUNT,
v_cheque_type,
V_RPT_PM005_RECORD.CHQDT,
V_RPT_PM005_RECORD.POLICYNO,
V_RPT_PM005_RECORD.REASON,
V_RPT_PM005_RECORD.DEPT,
V_RPT_PM005_RECORD.CURR,
V_RPT_PM005_RECORD.PAR,
v_organ_id
from t_cash a, t_reason_code b, t_dept c, t_cheque d, t_money e
where a.reason_code_id = b.list_id
and a.cashier_dept_id = c.dept_id
and a.pay_money_id = e.money_id
and a.cheque_id = d.cheque_id
and a.fee_id = v_fee_id;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
if v_cheque_type = 7 THEN
V_RPT_PM005_RECORD.CHQTYPE := 'O';
else
V_RPT_PM005_RECORD.CHQTYPE := 'C';
end if;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
v_top_organ_id := PKG_LS_GL_COMMON.F_GET_COMPANY_ID(v_organ_id);
if v_top_organ_id = 101 THEN
V_RPT_PM005_RECORD.LOC := 'HK';
elsif v_top_organ_id = 102 THEN
V_RPT_PM005_RECORD.LOC := 'MC';
else
V_RPT_PM005_RECORD.LOC := '';
end if;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
select c.status_desc
into V_RPT_PM005_RECORD.CHQSTATUS
from t_cash a, t_cheque b, t_cheque_status c
where a.cheque_id = b.cheque_id
and b.cheque_status = c.cheque_status
and a.fee_id = v_fee_id;
EXCEPTION
WHEN OTHERS THEN
null;
END;
INSERT INTO T_RPT_PM005_RECORD VALUES V_RPT_PM005_RECORD;
END LOOP;
CLOSE c_RPT_PM005_RECORD;
end;
end PKG_FIN_RPT_CASH_RECEIPTS;
/
344
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
