最近学习了EBS 开发报表输出的两种格式：
1.文本格式
CREATE OR REPLACE PROCEDURE giti_inv_fh_rpt(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
l_date_from IN VARCHAR2,
l_date_to IN VARCHAR2 ) IS
--创建游标 mycur
--游标的开始
CURSOR mycur IS
---需求名称：各工厂库存旬报
SELECT rownum ro,
hou.NAME organization_name,
ms.segment2 order_type,
msi.segment1 item_code,
giti_item_pkg.get_item_class(msi.segment1) class1,
SUM(mmt.transaction_quantity) quantity,
mmt.subinventory_code sub_code,
to_char(mmt.transaction_date, 'YYYY-MM-DD') transaction_date
FROM inv.mtl_material_transactions mmt,
inv.mtl_sales_orders ms,
inv.mtl_system_items_b msi,
hr.hr_all_organization_units hou
WHERE mmt.transaction_source_type_id = 2
AND mmt.transaction_source_id = ms.sales_order_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.organization_id = 83
AND (msi.segment1 LIKE '1%' OR msi.segment1 LIKE 'Y1%')
AND mmt.organization_id IN ('82', '441', '442', '443', '444', '445', '635')
AND mmt.transaction_date >= fnd_date.垃圾广告ical_to_date(l_date_from)--to_date(l_date_from, 'yyyy/mm/dd ')
AND mmt.transaction_date < fnd_date.垃圾广告ical_to_date(l_date_to)+1 --to_date(l_date_to, 'yyyy/mm/dd') + 1
AND hou.organization_id = mmt.organization_id
AND mmt.transaction_quantity < 0
GROUP BY rownum,
hou.NAME,
hou.organization_id,
ms.segment2,
msi.segment1,
mmt.subinventory_code,
to_char(mmt.transaction_date, 'YYYY-MM-DD')
UNION ALL
SELECT rownum,
hou.NAME organization_name,
ms.segment2 order_type,
msi.segment1 item_code,
giti_item_pkg.get_item_class(msi.segment1) ,
SUM(mmt.transaction_quantity) quantity,
mmt.subinventory_code,
to_char(mmt.transaction_date, 'YYYY-MM-DD') transaction_date
FROM inv.mtl_material_transactions mmt,
inv.mtl_sales_orders ms,
inv.mtl_system_items_b msi,
hr.hr_all_organization_units hou
WHERE mmt.transaction_source_type_id = 12
AND mmt.transaction_source_id = ms.sales_order_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.organization_id = 83
AND (msi.segment1 LIKE '1%' OR msi.segment1 LIKE 'Y1%')
AND mmt.organization_id IN ('82', '441', '442', '443', '444', '445', '635')
AND mmt.transaction_date >= fnd_date.垃圾广告ical_to_date(l_date_from) --to_date(l_date_from, 'yyyy/mm/dd ')
AND mmt.transaction_date < fnd_date.垃圾广告ical_to_date(l_date_to)+1 --to_date(l_date_to, 'yyyy/mm/dd') + 1
AND hou.organization_id = mmt.organization_id
AND mmt.transaction_quantity < 0
GROUP BY hou.NAME,
rownum,
hou.organization_id,
ms.segment2,
msi.segment1,
mmt.subinventory_code,
to_char(mmt.transaction_date, 'YYYY-MM-DD');
--游标的结束
l_print_line VARCHAR2(4000);
--l_dem VARCHAR2(10) := ',';
l_k VARCHAR2(30) := ' ';
BEGIN
fnd_file.put_line(fnd_file.output, lpad('各工厂库存情况旬报表', 78));
l_print_line := '序号'||l_k||'组织名称' || l_k || '销售类型' || l_k || '品号' || l_k||'大类' ||l_k|| '销售数量' || l_k || '子库名称/子库' || l_k || '交易日';
fnd_file.put_line(fnd_file.output, l_print_line);
FOR rec IN mycur
LOOP
l_print_line :=rec.ro||l_k|| rec.organization_name ||' '|| rec.order_type ||' '|| rec.item_code ||' '||rec.class1 ||' ' || rec.quantity || ' '|| rec.sub_code ||' '|| rec.transaction_date;
fnd_file.put_line(fnd_file.output, l_print_line);
END LOOP;
fnd_file.put_line(fnd_file.output, lpad('***报表结束***', 80));
END giti_inv_fh_rpt;
/
2.html 格式
--------用html输出报表----------------
CREATE OR REPLACE PACKAGE giti_inv_xbb IS
-- Author : YUWEILIANG
-- Created : 2009-4-28 7:57:25
-- Purpose : 各工厂库存情况旬报表
PROCEDURE giti_inv_fh_rpt(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
l_date_from IN VARCHAR2,
l_date_to IN VARCHAR2);
PROCEDURE print_line(g_srs_flag IN VARCHAR2,
p_line_type IN VARCHAR2,
p_line_content IN VARCHAR2);
END giti_inv_xbb;
/
CREATE OR REPLACE PACKAGE BODY giti_inv_xbb IS
PROCEDURE giti_inv_fh_rpt(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
l_date_from IN VARCHAR2,
l_date_to IN VARCHAR2) IS
--创建游标 mycur
--游标的开始
--需求名称：各工厂库存旬报
CURSOR mycur IS
SELECT rownum ro,
hou.NAME organization_name,
ms.segment2 order_type,
msi.segment1 item_code,
SUM(mmt.transaction_quantity) quantity,
mmt.subinventory_code sub_code,
to_char(mmt.transaction_date, 'YYYY-MM-DD') transaction_date
FROM inv.mtl_material_transactions mmt,
inv.mtl_sales_orders ms,
inv.mtl_system_items_b msi,
hr.hr_all_organization_units hou
WHERE mmt.transaction_source_type_id = 2
AND mmt.transaction_source_id = ms.sales_order_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.organization_id = 83
AND (msi.segment1 LIKE '1%' OR msi.segment1 LIKE 'Y1%')
AND mmt.organization_id IN ('82', '441', '442', '443', '444', '445', '635')
AND mmt.transaction_date >= to_date(l_date_from, 'yyyy-mm-dd ')
AND mmt.transaction_date < to_date(l_date_to, 'yyyy-mm-dd') + 1
AND hou.organization_id = mmt.organization_id
AND mmt.transaction_quantity < 0
GROUP BY rownum,
hou.NAME,
hou.organization_id,
ms.segment2,
msi.segment1,
mmt.subinventory_code,
to_char(mmt.transaction_date, 'YYYY-MM-DD')
UNION ALL
SELECT rownum,
hou.NAME organization_name,
ms.segment2 order_type,
msi.segment1 item_code,
SUM(mmt.transaction_quantity) quantity,
mmt.subinventory_code,
to_char(mmt.transaction_date, 'YYYY-MM-DD') transaction_date
FROM inv.mtl_material_transactions mmt,
inv.mtl_sales_orders ms,
inv.mtl_system_items_b msi,
hr.hr_all_organization_units hou
WHERE mmt.transaction_source_type_id = 12
AND mmt.transaction_source_id = ms.sales_order_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.organization_id = 83
AND (msi.segment1 LIKE '1%' OR msi.segment1 LIKE 'Y1%')
AND mmt.organization_id IN ('82', '441', '442', '443', '444', '445', '635')
AND mmt.transaction_date >= to_date(l_date_from, 'yyyy-mm-dd ')
AND mmt.transaction_date < to_date(l_date_to, 'yyyy-mm-dd') + 1
AND hou.organization_id = mmt.organization_id
AND mmt.transaction_quantity < 0
GROUP BY hou.NAME,
rownum,
hou.organization_id,
ms.segment2,
msi.segment1,
mmt.subinventory_code,
to_char(mmt.transaction_date, 'YYYY-MM-DD');
--游标的结束
l_temp_string VARCHAR2(10000);
BEGIN
print_line('Y', 'OUTFILE', '');
print_line('Y', 'OUTFILE', '
| 序号 | ' ||组织名称 | ' ||销售类型 | ' ||品号 | ' ||销售数量 | ' ||子库名称/子库 | ' ||交易日 | ' |||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ' || nvl(rec.ro, 0) || ' | ' ||' || nvl(rec.organization_name, '-') || ' | ' ||' || nvl(rec.order_type, '-') || ' | ' ||' || nvl(rec.item_code, '-') || ' | ' ||' || nvl(TO_CHAR(rec.quantity), '-') || ' | ' ||' || nvl(rec.sub_code, '-') || ' | ' ||' || nvl(rec.transaction_date, NULL) || ' |
佳通轮胎有限公司客户当前手续费报表 |
| 销售中心 | 收款编号 | 客户编码 | 客户名称 | 特惠客户 | 全免客户 | 银行信息 | 收款日期 | 汇票到期日 | 释放日期 | 收款金额 | 手续费 | 总超出金额 | 汇票比例 | 总汇票比例 | 特惠比率 | 减免比例 | 基础比率 | 超额比率 | 银行/天数比率 | 金额固定 | 组合比率 | 累计增加 | 最终比率 | 银行/天数固定 | 银行/金额比率 | 银行/金额固定 | 金额比率 | 准确计息比率 | 天数比率 | 天数固定 | 传送标志 | 冻结状态 | 手续费率 |
| TEXT01 | TEXT02 | TEXT03 | TEXT04 | TEXT05 | TEXT06 | TEXT07 | TEXT08 | TEXT09 | TEXT10 | TEXT11 | TEXT12 | TEXT13 | TEXT14 | TEXT15 | TEXT16 | TEXT17 | TEXT58 | TEXT68 | TEXT19 | TEXT20 | TEXT21 | TEXT22 | TEXT23 | TEXT24 | TEXT25 | TEXT26 | TEXT27 | TEXT28 | TEXT29 | TEXT30 | TEXT31 | TEXT32 | TEXT18 |
CURSOR C_RECEIPTS(P_ORG_ID IN NUMBER) IS
/*SELECT ACRA.RECEIPT_NUMBER, --收款编号
T.REMITTED_AMOUNT, --收款金额
CUST.CUSTOMER_NUMBER, --客户编码
CUST.CUSTOMER_NAME, --客户名称
DECODE(CUST.ATTRIBUTE1, 'Y', '是', 'N', '否') SPEC_BENE_FLAG, --特惠客户
DECODE(CUST.ATTRIBUTE2, 'Y', '是', 'N', '否') ALLOW_ALL_FREE_FLAG, --全免客户
T.ISSUE_BANK_NAME BANK_NAME, --银行信息
ACRA.RECEIPT_DATE, --收款日期
T.DUE_DATE, --汇票到期日期
T.ATTRIBUTE8 RELE_DATE, --释放日期
ROUND(TO_NUMBER(T.ATTRIBUTE1), 2) CHARGE_AMT,
CBCR.BASE_RULE, --手续费率
DECODE(T.ATTRIBUTE6,
'Y',
CBCR.MAIN_ADJ_RULE,
'N',
CBCR.OTHER_ADJ_RULE) BANK_RULE, -- 银行比率
T.ATTRIBUTE4 AMT_RULE, --金额比率
T.ATTRIBUTE3 DAYS_RULE, --天数比率
CBCR.BASE_RULE * NVL(T.ATTRIBUTE4, 1) * NVL(T.ATTRIBUTE3, 1) *
NVL(DECODE(T.ATTRIBUTE6,
'Y',
CBCR.MAIN_ADJ_RULE,
'N',
CBCR.OTHER_ADJ_RULE),
1) COMB_RULE, --组合比率
T.ATTRIBUTE5 SPEC_RULE, --特惠比率
CBCR.BASE_RULE * NVL(T.ATTRIBUTE4, 1) * NVL(T.ATTRIBUTE3, 1) *
NVL(T.ATTRIBUTE5, 1) * NVL(DECODE(T.ATTRIBUTE6,
'Y',
CBCR.MAIN_ADJ_RULE,
'N',
CBCR.OTHER_ADJ_RULE),
1) F_RULE, --最终比率
T.ATTRIBUTE9 INTERFACE_ID
FROM CUX.CUX_BILL_OF_EXCHANGES_ALL T,
AR_CASH_RECEIPTS_ALL ACRA,
AR_CASH_RECEIPT_HISTORY_ALL ACRH,
CUX_BOE_CALC_RULE CBCR,
--CUX_BOE_CUSTOMER_V CUST
CUX_BOE_TM_INTERFACE CUST
WHERE T.RELATED_DOCUMENT_ID = ACRA.CASH_RECEIPT_ID
AND ACRA.CASH_RECEIPT_ID = ACRH.CASH_RECEIPT_ID
AND CBCR.CALC_RULE_ID = T.ATTRIBUTE2
AND CUST.DOCUMENT_ID = T.DOCUMENT_ID
AND CBCR.POLICY_ID = CUST.POLICY_ID
--AND ACRH.FIRST_POSTED_RECORD_FLAG = 'Y'
AND CUST.ORG_ID = FND_GLOBAL.ORG_ID
AND CBCR.POLICY_ID = P_POLICY_ID
AND ACRH.GL_DATE BETWEEN P_DATE_FROM AND P_DATE_TO
ORDER BY ACRA.RECEIPT_NUMBER;*/
SELECT CUST.NAME, --销售中心
CUST.RECEIPT_NUMBER, --收款编号
CUST.REMITTED_AMOUNT, --收款金额
CUST.CUSTOMER_NUMBER, --客户编码
CUST.CUSTOMER_NAME, --客户名称
DECODE(CUST.ATTRIBUTE1, 'Y', '是', 'N', '否') SPEC_BENE_FLAG, --特惠客户
DECODE(CUST.ATTRIBUTE2, 'Y', '是', 'N', '否') ALLOW_ALL_FREE_FLAG, --全免客户
CUST.BANK_NAME, --银行信息
CUST.RECEIPT_DATE, --收款日期
CUST.DUE_DATE, --汇票到期日期
CUST.RELEASE_DATE RELE_DATE, --释放日期
CUST.SOURCE_LINES_AMOUNT CHARGE_AMT, --手续费
get_cust_base_rule(CUST.ORG_ID,CUST.POLICY_ID,CUST.CUSTOMER_ID) NEW_BASE_RULE, --基础比率
get_cust_overtake_rule(CUST.ORG_ID,CUST.POLICY_ID,CUST.CUSTOMER_ID) NEW_OVERTAKE_RULE,--超额比率
CBCR.BASE_RULE, --手续费率
DECODE(SUBSTR(CUST.PERIOD_NAME, 4, 5),
'07',
DECODE(CUST.ATTRIBUTE2, --brain.chen on 2007.05.31
'Y',
'',
DECODE(T.ATTRIBUTE6,
'Y',
CBCR.MAIN_ADJ_RULE,
'N',
CBCR.OTHER_ADJ_RULE)),
T.ATTRIBUTE10 --08年银行比率
)BANK_RULE, -- 银行比率
T.ATTRIBUTE11 BANK_AMOUNT, --银行固定 08年政策新增
T.ATTRIBUTE4 AMT_RULE, --金额比率
T.ATTRIBUTE9 AMT_AMOUNT, --金额固定 08年政策新增
T.ATTRIBUTE5 PREC_RULE, --准确计息比率
T.ATTRIBUTE3 DAYS_RULE, --天数比率
T.ATTRIBUTE8 DAYS_AMOUNT, --天数固定 08年政策新增
DECODE(SUBSTR(CUST.PERIOD_NAME, 4, 5),
'07',
CBCR.BASE_RULE * NVL(T.ATTRIBUTE4, 1) * NVL(T.ATTRIBUTE3, 1) *
NVL(T.ATTRIBUTE5, 1) * NVL(DECODE(T.ATTRIBUTE6,
'Y',
CBCR.MAIN_ADJ_RULE,
'N',
CBCR.OTHER_ADJ_RULE), 1),
CBCR.BASE_RULE * NVL(T.ATTRIBUTE4, 1) * NVL(T.ATTRIBUTE3, 1) *
NVL(T.ATTRIBUTE5, 1)) COMB_RULE, --组合比率
NVL(T.ATTRIBUTE11, 0) + NVL(T.ATTRIBUTE9, 0) + NVL(T.ATTRIBUTE8, 0) TOTAL_AMOUNT, --累计增加 08年政策
CUST.SPEC_RULE, --特惠比率
DECODE(SUBSTR(CUST.PERIOD_NAME, 4, 5),
'07',
CBCR.BASE_RULE * NVL(T.ATTRIBUTE4, 1) * NVL(T.ATTRIBUTE3, 1) *
NVL(T.ATTRIBUTE5, 1) * NVL(CUST.SPEC_RULE, 1) *
NVL(DECODE(T.ATTRIBUTE6,
'Y',
CBCR.MAIN_ADJ_RULE,
'N',
CBCR.OTHER_ADJ_RULE),
1),
NVL(T.ATTRIBUTE15,
--CUST.SOURCE_LINES_AMOUNT / NVL(CUST.REMITTED_AMOUNT, CUST.SOURCE_LINES_AMOUNT)
DECODE(NVL(CUST.REMITTED_AMOUNT,0),
0,
DECODE(NVL(CUST.SOURCE_LINES_AMOUNT, 0),
0,
0,
1),
NVL(CUST.SOURCE_LINES_AMOUNT, 0) / CUST.REMITTED_AMOUNT)
--MODI BY LEONFENG 20080108
)
)F_RULE, --最终比率
DECODE(CUST.IMPORT_FLAG, 'Y', '是', 'N', '否') IMPORT_FLAG,
CUST.BOE_SCALE, --汇票比率
T.ATTRIBUTE1, --减免比率
CUST.TOTAL_BOE_SCALE, --总汇票比率
CUST.OVER_AMOUNT, --总超出金额
T.ATTRIBUTE16, --银行金额比例
T.ATTRIBUTE17, --银行金额固定
DECODE(CUST.FREEZE_FLAG,
'Y',
'冻结',
'释放') FREEZE_FLAG
FROM CUX.CUX_BILL_OF_EXCHANGES_ALL T,
CUX_BOE_CALC_RULE CBCR,
CUX_BOE_TM_INTERFACE_V CUST
WHERE T.DOCUMENT_ID = CUST.DOCUMENT_ID
AND CBCR.CALC_RULE_ID = T.ATTRIBUTE2
AND CUST.POLICY_ID = CBCR.POLICY_ID
AND CUST.ORG_ID =
DECODE(P_ORG_ID, 88, CUST.ORG_ID, P_ORG_ID, P_ORG_ID) --FND_GLOBAL.ORG_ID
AND CUST.POLICY_ID = P_POLICY_ID
AND CUST.PERIOD_NAME = P_PERIOD
ORDER BY CUST.RECEIPT_NUMBER;
V_START_DATE DATE;
V_END_DATE DATE;
V_ORG_ID NUMBER;
BEGIN
--100总公司显示所有OU的报表，其他分公司只显示当前OU的报表
V_ORG_ID := FND_GLOBAL.ORG_ID;
/*SELECT START_DATE, END_DATE
INTO V_START_DATE, V_END_DATE
FROM GL_PERIOD_STATUSES
WHERE CLOSING_STATUS IN ('O', 'C')
AND APPLICATION_ID = 101
AND SET_OF_BOOKS_ID = 1
AND ADJUSTMENT_PERIOD_FLAG = 'N'
--AND TO_CHAR(SYSDATE, 'YYYY') = PERIOD_YEAR
AND PERIOD_NAME = P_PERIOD;*/
CUX_CONC_UTL.OUT_MSG(LV_HEADER);
FOR REC IN C_RECEIPTS(V_ORG_ID) LOOP
LV_STRING := LV_BODY;
LV_STRING := REPLACE(LV_STRING, 'TEXT01', NVL(REC.NAME, ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT02',
NVL(TO_CHAR(REC.RECEIPT_NUMBER), ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT03',
NVL(REC.CUSTOMER_NUMBER, ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT04',
NVL(REC.CUSTOMER_NAME, ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT05',
NVL(REC.SPEC_BENE_FLAG, ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT06',
NVL(REC.ALLOW_ALL_FREE_FLAG, ''));
LV_STRING := REPLACE(LV_STRING, 'TEXT07', NVL(REC.BANK_NAME, ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT08',
NVL(TO_CHAR(REC.RECEIPT_DATE, 'YYYY-MM-DD'),
''));
LV_STRING := REPLACE(LV_STRING,
'TEXT09',
NVL(TO_CHAR(REC.DUE_DATE, 'YYYY-MM-DD'), ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT10',
NVL(TO_CHAR(REC.RELE_DATE, 'YYYY-MM-DD'), ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT11',
NVL(TO_CHAR(REC.REMITTED_AMOUNT), ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT12',
NVL(TO_CHAR(ROUND(REC.CHARGE_AMT, 4)), ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT13',
NVL(TO_CHAR(ROUND(REC.OVER_AMOUNT, 4)), ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT14',
NVL(TO_CHAR(ROUND(REC.BOE_SCALE, 4)), ''));
--银行固定
LV_STRING := REPLACE(LV_STRING,
'TEXT15',
NVL(TO_CHAR(ROUND(REC.TOTAL_BOE_SCALE, 4)), ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT16',
NVL(TO_CHAR(ROUND(REC.SPEC_RULE, 4)), ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT17',
NVL(TO_CHAR(REC.ATTRIBUTE1), ''));
--基础比率
LV_STRING := REPLACE(LV_STRING,
'TEXT58',
NVL(TO_CHAR(REC.NEW_BASE_RULE), ''));
--超额比率
LV_STRING := REPLACE(LV_STRING,
'TEXT68',
NVL(TO_CHAR(REC.NEW_OVERTAKE_RULE), ''));
--金额比率
LV_STRING := REPLACE(LV_STRING,
'TEXT18',
NVL(TO_CHAR(REC.BASE_RULE), ''));
--金额固定
LV_STRING := REPLACE(LV_STRING,
'TEXT19',
NVL(TO_CHAR(REC.BANK_RULE), ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT20',
NVL(TO_CHAR(REC.AMT_AMOUNT), ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT21',
NVL(TO_CHAR(ROUND(REC.COMB_RULE, 4)), ''));
--天数固定
LV_STRING := REPLACE(LV_STRING,
'TEXT22',
NVL(TO_CHAR(ROUND(REC.TOTAL_AMOUNT, 4)), ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT23',
NVL(TO_CHAR(ROUND(REC.F_RULE, 6)), ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT24',
NVL(TO_CHAR(REC.BANK_RULE), ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT25',
NVL(TO_CHAR(ROUND(REC.ATTRIBUTE16, 6)), ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT26',
NVL(TO_CHAR(ROUND(REC.ATTRIBUTE17, 6)), ''));
--汇票比例
LV_STRING := REPLACE(LV_STRING,
'TEXT27',
NVL(TO_CHAR(round(REC.AMT_RULE, 4)), ''));
--减免比例
LV_STRING := REPLACE(LV_STRING,
'TEXT28',
NVL(TO_CHAR(REC.PREC_RULE), ''));
--总超出金额
LV_STRING := REPLACE(LV_STRING,
'TEXT29',
NVL(TO_CHAR(ROUND(REC.DAYS_RULE, 4)), ''));
--总汇票比例
LV_STRING := REPLACE(LV_STRING,
'TEXT30',
NVL(TO_CHAR(ROUND(REC.DAYS_AMOUNT, 4)), ''));
--传送标志
LV_STRING := REPLACE(LV_STRING,
'TEXT31',
NVL(REC.IMPORT_FLAG, ''));
LV_STRING := REPLACE(LV_STRING,
'TEXT32',
NVL(REC.FREEZE_FLAG, ''));
CUX_CONC_UTL.OUT_MSG(LV_STRING);
END LOOP;
CUX_CONC_UTL.OUT_MSG(LV_TAIL);
END MAIN;
PROCEDURE CUXZGSAT(ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2,
P_ORG_ID IN NUMBER,
P_PERIOD IN VARCHAR2) IS
l_start_date DATE;
l_end_date DATE;
CURSOR C_RECEIPTS(p_start_date DATE, p_end_date DATE) IS
SELECT hou.name ORG_NAME,
hp.party_name cust_name,
flv.meaning boe_doc_type,
boe.remittor_name,
boe.issue_bank_name,
boe.date_of_issue,
boe.due_date,
boe.remitted_amount,
boe.document_number1 || boe.document_number2 || boe.document_number3 document_number,
boe.remittee_name,
boe.receive_bank_name,
boe.receive_bank_account_name,
boe.issue_bank_account_name,
boe.agreement_number,
boe.currency_code,
boe.issue_bank_number,
boe.issue_bank_address,
get_first_endor(boe.document_id)first_endor,
get_second_endor(boe.document_id)second_endor,
cr.receipt_number
FROM cux.cux_bill_of_exchanges_all boe,
ar_cash_receipts_all cr,
ar_cash_receipt_history_all crh,
ar_cash_receipt_history_all crhc,
hz_cust_accounts hca,
hz_parties hp,
fnd_lookup_values flv,
HR_ORGANIZATION_UNITS HOU
WHERE 1 = 1
AND boe.related_document_id = cr.cash_receipt_id
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.first_posted_record_flag = 'Y'
AND cr.cash_receipt_id = crhc.cash_receipt_id
AND crhc.current_record_flag = 'Y'
AND crhc.status <> 'REVERSED'
AND cr.pay_from_customer = hca.cust_account_id(+)
AND hca.party_id = hp.party_id(+)
AND flv.lookup_code(+) = boe.document_type
AND flv.LANGUAGE(+) = 'ZHS'
AND flv.lookup_type(+) = 'CUX_BOE_DOC_TYPE'
AND crh.gl_date between nvl(p_start_date, crh.gl_date) and nvl(p_end_date, crh.gl_date)
AND CR.ORG_ID = DECODE(P_ORG_ID,88,CR.ORG_ID,NVL(P_ORG_ID,CR.ORG_ID))
AND CRH.ORG_ID = DECODE(P_ORG_ID,88,CRH.ORG_ID,NVL(P_ORG_ID,CRH.ORG_ID))
AND CRHC.ORG_ID = DECODE(P_ORG_ID,88,CRHC.ORG_ID,NVL(P_ORG_ID,CRHC.ORG_ID))
AND CR.ORG_ID = HOU.organization_id
;
V_START_DATE DATE;
V_END_DATE DATE;
V_ORG_ID NUMBER;
l_temp_string VARCHAR2(4000);
l_org_name VARCHAR2(500);
BEGIN
IF P_ORG_ID IS NOT NULL THEN
SELECT ou.name
INTO l_org_name
FROM hr_operating_units ou
WHERE ou.organization_id = P_ORG_ID;
END IF;
print_line(g_srs_flag, 'OUTFILE', '');
print_line(g_srs_flag, 'OUTFILE', '
print_line(g_srs_flag, 'OUTFILE', '
print_line(g_srs_flag, 'OUTFILE', '
| 业务实体 : | ' || l_org_name || ' | ');
| 打印时间 : | ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || ' | ');
print_line(g_srs_flag, 'OUTFILE', '
| 业务实体 | ' ||客户名称 | ' ||汇票类型 | ' ||出票人全称 | ' ||付款银行名称 | ' ||出票日期 | ' ||到日期 | ' ||出票金额 | ' ||汇票编号 | ' ||收款人名称 | ' ||收款人开户银行 | ' ||收款人帐号 | ' ||出票人帐号 | ' ||承兑协议编号 | ' ||付款币种 | ' ||付款行行号 | ' ||付款行地址 | ' ||第一背书人 | ' ||第二背书人 | ' ||收款编号 | ' ||
| ' || x.ORG_NAME || ' | ' ||' || nvl(x.cust_name, '') || ' | ' ||' || x.boe_doc_type || ' | ' ||' || x.remittor_name || ' | ' ||' || x.issue_bank_name || ' | ' ||' || to_char(x.date_of_issue, 'YYYY-MM-DD') || ' | ' ||' || to_char(x.due_date, 'YYYY-MM-DD') || ' | ' ||' || to_char(round(x.remitted_amount, 2)) || ' | ' ||' || x.document_number || ' | ' ||' || x.remittee_name || ' | ' ||' || x.receive_bank_name || ' | ' ||' || x.receive_bank_account_name || ' | ' ||' || x.issue_bank_account_name || ' | ' ||' || nvl(x.agreement_number, '') || ' | ' ||' || x.currency_code || ' | ' ||' || x.issue_bank_number || ' | ' ||' || x.issue_bank_address || ' | ' ||' || nvl(x.first_endor, '') || ' | ' ||' || nvl(x.second_endor, '') || ' | ' ||' || x.receipt_number || ' | ' ||
EXCEPTION
WHEN OTHERS THEN
print_line(g_srs_flag, 'OUTFILE', '