CREATE OR REPLACE FUNCTION SF_ZY_NNDJPJ_JSONSTR ( PCJLYBM varchar2 default 'NN', --厂家来源编码 NN PJKCJBM varchar2 default '', --接口场景编码 PZYID00 number, --住院ID=ZY_BRXXB0.ZYID00 PJZDH00 number, --结账单号=ZY_JZB000.JZDH00 PCZY000 number default 0, --操作员=BM_YGBM00.YGBH00 PCZYKS0 number default 0, --操作员科室=BM_YGBM00.BMBH00 PFHLXBH number, --返回类型编码 0:完整请求报文 PINTSTR varchar2 default '' --备用参数 xml编码格式传入 ) -- modification history -- Person Date Comments -- chenHeyi 2022.09.07 create ZYSF9-20220906-002 -- chenHeyi 2022.09.09 调整冲红报文 ZYSF9-20220908-001 -- chenHeyi 2023.02.03 增加Order.extensionNumber,terminalNumber(分机号，终端号节点),报文增加发票备注remark，明细项目按发票项目汇总发送 ZYSF9-20230203-001 ---------------------------------------------------------------- return clob is LS_FHXX00 clob; -- LS_invoiceDetail clob; -- LS_JSQDMX clob; LS_Order clob; in_PJH000 ZY_JZB000.PJH000%type; LS_PJH000 ZY_JZB000.PJH000%type; LS_CZYXM0 BM_YGBM00.ZWXM00%type; LS_number_std_amt varchar2(100); LS_DJ_format varchar2(20); --单价格式化 LS_SL_format varchar2(20); --数量格式化 LS_JE_format varchar2(20); --金额格式化 LS_salerTaxNum varchar2(20); --销方税号 LS_salerTel varchar2(20); --销方电话 LS_salerAddress varchar2(80); --销方地址 LS_invoiceCode ZY_PJSYQK.WBPJDM%type; LS_invoiceNum ZY_PJSYQK.WBPJXH%type; LS_FWLSH0 ZY_WBFPXX.FWLSH0%type; LS_WBID00 ZY_WBFPXX.WBID00%type; LS_orderNo varchar2(20); LS_FPUSER BM_YGBM00.FPUSER%type; cursor CUR_invoiceDetail is select --a.XMBH00 as goodsCode, --商品编码 '3070202000000000000' as goodsCode, --商品编码 substrb(a.XMMC00,1,90) as goodsName, --商品名称 '0' as withTaxFlag, --单价含税标志：0:不含税,1:含税 a.XMDJ00 as price, --单价 sum(a.XMSL00) as num, --数量 substrb(max(a.XMDW00),1,20) as unit, --单位 substrb(max(a.XMGG00),1,40) as specType, --规格型号 0 as tax, --税额 0 as taxRate, --税率 sum(a.HJJE00) as taxExcludedAmount, --不含税金额 sum(a.HJJE00) as taxIncludedAmount, --含税金额 '0' as invoiceLineProperty, --发票行性质：0,正常行;1,折扣行;2,被折扣行 '1' as favouredPolicyFlag, --优惠政策标识：0,不使用;1,使用 '免税' as favouredPolicyName, --增值税特殊管理 '0' as deduction, --扣除额 '1' as zeroRateFlag --零税率标识 from VW_ZY_RQDHZ0_YBXM a where 1=1 and a.ZYID00=PZYID00 and a.JZDH00=PJZDH00 group by a.XMBH00,substrb(a.XMMC00,1,90),a.XMDJ00; cursor CUR_FPXMinvoiceDetail is select * from ( select d.ZYFPBM as goodsCode, --商品编码 d.ZYFPMC as goodsName, --商品名称 '0' as withTaxFlag, --单价含税标志：0:不含税,1:含税 '' as price, --单价 '' as num, --数量 '' as unit, --单位 '' as specType, --规格型号 0 as tax, --税额 0 as taxRate, --税率 sum((decode(b.JMBZ00,'4',b.HJJE00+nvl(b.JMJE00,0),b.HJJE00))) as taxExcludedAmount, --不含税金额 sum((decode(b.JMBZ00,'4',b.HJJE00+nvl(b.JMJE00,0),b.HJJE00))) as taxIncludedAmount, --含税金额 '0' as invoiceLineProperty, --发票行性质：0,正常行;1,折扣行;2,被折扣行 '1' as favouredPolicyFlag, --优惠政策标识：0,不使用;1,使用 '免税' as favouredPolicyName, --增值税特殊管理 '0' as deduction, --扣除额 '1' as zeroRateFlag --零税率标识 from ZY_JZB000 a ,ZY_FYMX00 b, ZY_JZMXXM c,BM_FPXM00 d where a.JZDH00=c.JZDH00 and c.Mxid00=b.Mxid00 and b.ZYFPID = d.FPXMID and a.ZYID00=PZYID00 and a.JZDH00=PJZDH00 group by d.ZYFPBM,d.ZYFPMC) where taxExcludedAmount>0 ; cursor CUR_Order is select a.* from VW_ZY_NNDZFP_Order a where 1=1 and a.ZYID00=PZYID00 and a.JZDH00=PJZDH00; begin LS_FHXX00:=''; LS_invoiceDetail:=''; LS_Order:=''; in_PJH000:=0; LS_salerTaxNum:=substrb(trim(SF_SF_TYZD00('第三方电子发票平台接口','NNDZFP_salerTaxNum')),1,20); LS_salerTel:=substrb(trim(SF_SF_TYZD00('第三方电子发票平台接口','NNDZFP_salerTel')),1,20); LS_salerAddress:=substrb(trim(SF_SF_TYZD00('第三方电子发票平台接口','NNDZFP_salerAddress')),1,80); LS_number_std_amt:=substrb(trim(SF_SF_TYZD00('第三方电子发票平台接口','number_std_amt')),1,100); if LS_number_std_amt is not null then LS_SL_format:=substrb(SF_SF_RETURNINDEXEH(trim(LS_number_std_amt),'|', 1),1,20); --数量格式化(保留4位) LS_DJ_format:=substrb(SF_SF_RETURNINDEXEH(trim(LS_number_std_amt),'|', 2),1,20); --单价格式化(保留4位) LS_JE_format:=substrb(SF_SF_RETURNINDEXEH(trim(LS_number_std_amt),'|', 3),1,20); --金额格式化(保留2位) else LS_SL_format:='999999990.9999'; --数量格式化(保留4位) LS_DJ_format:='999999990.9999'; --单价格式化(保留4位) LS_JE_format:='999999990.99'; --金额格式化(保留2位) end if; begin select substrb(ZWXM00,1,20),FPUSER into LS_CZYXM0,LS_FPUSER from BM_YGBM00 where YGBH00=PCZY000; exception when others then LS_CZYXM0:=''; end; --select max(nvl(YWLSH0,'')) into LS_orderNo from SF_WBFPXX where JZDH00=PJZDH00; if PJKCJBM in ('MZSF_DZFP_0006','ZYSF_DZFP_0006','MZSST_DZFP_0006','ZYSST_DZFP_0006','MZDD_DZFP_0006','ZYDD_DZFP_0006','MZYD_DZFP_0006','ZYYD_DZFP_0006','MZWLZS_DZFP_0006','ZYWLZS_DZFP_0006') then --电子票据冲红接口 select PJH000 into LS_PJH000 from ZY_JZB000 where JZDH00=PJZDH00; LS_PJH000:=SF_SF_GETYXPJH(LS_PJH000); select WBPJDM, WBPJXH into LS_invoiceCode,LS_invoiceNum from ZY_PJSYQK where PJH000=LS_PJH000; for REC in CUR_FPXMinvoiceDetail loop LS_JSQDMX:=''; LS_JSQDMX:='{' ||'"goodsName":'||'"'||REC.goodsName||'",' --药品名称 ||'"goodsCode":'||'"'||REC.goodsCode||'",' --商品编码 ||'"withTaxFlag":'||'"'||REC.withTaxFlag||'",' --单价含税标志：0:不含税,1:含税 ||'"price":'||'"'||trim(to_char(REC.price,LS_DJ_format))||'",' --单价 ||'"num":'||'"'||trim(to_char(REC.num*-1,LS_SL_format))||'",' --数量 ||'"unit":'||'"'||REC.unit||'",' --单位 ||'"specType":'||'"'||REC.specType||'",' --规格型号 ||'"tax":'||'"'||trim(to_char(REC.tax*-1,LS_JE_format))||'",' --税额 ||'"taxRate":'||'"'||trim(to_char(REC.taxRate,LS_JE_format))||'",' --税率 ||'"taxExcludedAmount":'||'"'||trim(to_char(REC.taxExcludedAmount*-1,LS_JE_format))||'",' --不含税金额 ||'"taxIncludedAmount":'||'"'||trim(to_char(REC.taxIncludedAmount*-1,LS_JE_format))||'",' --含税金额 ||'"invoiceLineProperty":'||'"'||REC.invoiceLineProperty||'",' --发票行性质 ||'"favouredPolicyFlag":'||'"'||REC.favouredPolicyFlag||'",' --优惠政策标识 ||'"favouredPolicyName":'||'"'||REC.favouredPolicyName||'",' --增值税特殊管理 ||'"deduction":'||'"'||REC.deduction||'",' --扣除额 ||'"zeroRateFlag":'||'"'||REC.zeroRateFlag||'"' --零税率标识 ||'}'; if LS_invoiceDetail is not null then LS_invoiceDetail:=LS_invoiceDetail||','||LS_JSQDMX; else LS_invoiceDetail:=LS_JSQDMX; end if; end loop; LS_invoiceDetail:='['||LS_invoiceDetail||']'; for REC in CUR_Order loop LS_Order:='"buyerName":'||'"'||REC.buyerName||'",' --购方名称 ||'"buyerTaxNum":'||'"'||REC.buyerTaxNum||'",' --购方税号 ||'"buyerTel":'||'"'||REC.buyerTel||'",' --购方电话 ||'"buyerAddress":'||'"'||REC.buyerAddress||'",' --购方地址 ||'"buyerAccount":'||'"'||REC.buyerAccount||'",' --购方银行账号及开户行地址 ||'"salerTaxNum":'||'"'||LS_salerTaxNum||'",' --销方税号 ||'"salerTel":'||'"'||LS_salerTel||'",' --销方电话 ||'"salerAddress":'||'"'||LS_salerAddress||'",' --销方地址 ||'"orderNo":'||'"'||REC.orderNo||'",' --订单号 ||'"invoiceDate":'||'"'||REC.invoiceDate||'",' --订单时间 ||'"invoiceCode":'||'"'||LS_invoiceCode||'",' --冲红时填写的对应蓝票发票代码（红票必填，不满12位请左补0） ||'"invoiceNum":'||'"'||LS_invoiceNum||'",' --冲红时填写的对应蓝票发票号码（红票必填，不满8位请左补0） ||'"clerk":'||'"'||LS_CZYXM0||'",' --开票员 ||'"extensionNumber":"0",' --分机号,都是0 ||'"terminalNumber":'||'"'||LS_FPUSER||'",' --终端号,取BM_YGBM00.FPUSER ||'"remark":'||'"'||'对应正数发票代码:'||LS_invoiceCode||'号码:'||LS_invoiceNum||'",' --发票备注remark ||'"listFlag":'||'"0",' --清单标志：非清单:0；清单:1，默认:0 ||'"pushMode":'||'"-1",' --推送方式：-1,不推送;0,邮箱;1,手机（默认）;2,邮箱、手机 ||'"invoiceType":'||'"2",' --开票类型：1:蓝票;2:红票 ||'"invoiceLine":'||'"p"'; --发票种类：p,普通发票(电票)(默认);c,普通发票(纸票);s,专用发票;e,收购发票(电票);f,收购发票(纸质);r,普通发票(卷式);b,增值税电子专用发票 end loop; LS_Order:='{'||LS_Order||',"invoiceDetail":'||LS_invoiceDetail||'}'; LS_FHXX00:='{"order":'||LS_Order||'}'; elsif PJKCJBM in ('MZSF_DZFP_0009','ZYSF_DZFP_0009') then begin select to_number(SF_SF_GETXMLNOTE('wbid00',trim(PINTSTR))) into LS_WBID00 from dual; exception when others then LS_WBID00:=0; end; select a.FWLSH0 into LS_FWLSH0 from ZY_WBFPXX a where a.ZYID00=PZYID00 and WBID00=LS_WBID00; LS_JSQDMX:='"serialNos":["'||LS_FWLSH0||'"]'; LS_FHXX00:='{"isOfferInvoiceDetail":"0",'||LS_JSQDMX||'}'; else for REC in CUR_FPXMinvoiceDetail loop LS_JSQDMX:=''; LS_JSQDMX:='{' ||'"goodsName":'||'"'||REC.goodsName||'",' --药品名称 ||'"goodsCode":'||'"'||REC.goodsCode||'",' --商品编码 ||'"withTaxFlag":'||'"'||REC.withTaxFlag||'",' --单价含税标志：0:不含税,1:含税 ||'"price":'||'"'||trim(to_char(REC.price,LS_DJ_format))||'",' --单价 ||'"num":'||'"'||trim(to_char(REC.num,LS_SL_format))||'",' --数量 ||'"unit":'||'"'||REC.unit||'",' --单位 ||'"specType":'||'"'||REC.specType||'",' --规格型号 ||'"tax":'||'"'||trim(to_char(REC.tax,LS_JE_format))||'",' --税额 ||'"taxRate":'||'"'||trim(to_char(REC.taxRate,LS_JE_format))||'",' --税率 ||'"taxExcludedAmount":'||'"'||trim(to_char(REC.taxExcludedAmount,LS_JE_format))||'",' --不含税金额 ||'"taxIncludedAmount":'||'"'||trim(to_char(REC.taxIncludedAmount,LS_JE_format))||'",' --含税金额 ||'"invoiceLineProperty":'||'"'||REC.invoiceLineProperty||'",' --发票行性质 ||'"favouredPolicyFlag":'||'"'||REC.favouredPolicyFlag||'",' --优惠政策标识 ||'"favouredPolicyName":'||'"'||REC.favouredPolicyName||'",' --增值税特殊管理 ||'"deduction":'||'"'||REC.deduction||'",' --扣除额 ||'"zeroRateFlag":'||'"'||REC.zeroRateFlag||'"' --零税率标识 ||'}'; if LS_invoiceDetail is not null then LS_invoiceDetail:=LS_invoiceDetail||','||LS_JSQDMX; else LS_invoiceDetail:=LS_JSQDMX; end if; end loop; LS_invoiceDetail:='['||LS_invoiceDetail||']'; for REC in CUR_Order loop LS_Order:='"buyerName":'||'"'||REC.buyerName||'",' --购方名称 ||'"buyerTaxNum":'||'"'||REC.buyerTaxNum||'",' --购方税号 ||'"buyerTel":'||'"'||REC.buyerTel||'",' --购方电话 ||'"buyerAddress":'||'"'||REC.buyerAddress||'",' --购方地址 ||'"buyerAccount":'||'"'||REC.buyerAccount||'",' --购方银行账号及开户行地址 ||'"salerTaxNum":'||'"'||LS_salerTaxNum||'",' --销方税号 ||'"salerTel":'||'"'||LS_salerTel||'",' --销方电话 ||'"salerAddress":'||'"'||LS_salerAddress||'",' --销方地址 ||'"orderNo":'||'"'||REC.orderNo||'",' --订单号 ||'"invoiceDate":'||'"'||REC.invoiceDate||'",' --订单时间 ||'"invoiceCode":'||'"",' --冲红时填写的对应蓝票发票代码（红票必填，不满12位请左补0） ||'"invoiceNum":'||'"",' --冲红时填写的对应蓝票发票号码（红票必填，不满8位请左补0） ||'"clerk":'||'"'||LS_CZYXM0||'",' --开票员 ||'"extensionNumber":"0",' --分机号,都是0 ||'"terminalNumber":'||'"'||LS_FPUSER||'",' --终端号,取BM_YGBM00.FPUSER ||'"remark":'||'"'||REC.remark||'",' --发票备注remark ||'"listFlag":'||'"0",' --清单标志：非清单:0；清单:1，默认:0 ||'"pushMode":'||'"-1",' --推送方式：-1,不推送;0,邮箱;1,手机（默认）;2,邮箱、手机 ||'"invoiceType":'||'"1",' --开票类型：1:蓝票;2:红票 ||'"invoiceLine":'||'"p"'; --发票种类：p,普通发票(电票)(默认);c,普通发票(纸票);s,专用发票;e,收购发票(电票);f,收购发票(纸质);r,普通发票(卷式);b,增值税电子专用发票 end loop; LS_Order:='{'||LS_Order||',"invoiceDetail":'||LS_invoiceDetail||'}'; LS_FHXX00:='{"order":'||LS_Order||'}'; end if; return LS_FHXX00; end;