with t1 as (select c1.project_id,b1.item_id,to_char(c1.creation_date,'yyyymmdd') rec_dt,

    sum(quantity_delivered) recqty,sum(quantity_ordered - quantity_delivered) qty 

    from po_headers_all a1 left join po_lines_all b1 on a1.po_header_id=b1.po_header_id and a1.org_id=106

    left join po_distributions_all c1 on c1.po_line_id=b1.po_line_id

    left join pjm_seiban_numbers prj on prj.project_id=c1.project_id

    where a1.org_id=106 and b1.item_id between 318750 and 318753 and nvl(a1.cancel_flag,'N')='N' 

    and project_number in (select smschm04 from smschm where smschm01 = '2024' and smschm02 ='1' AND ( smschm03 is null or smschm03 in ('T2','T2A','T2B','T2S','TC1','TC2')) )

     group by c1.project_id,b1.item_id,to_char(c1.creation_date,'yyyymmdd')),

t2 as (select project_id,item_id,max(grandqty) grandqty from (select project_id,item_id,rec_dt,recqty,sum(recqty) over (PARTITION BY project_id,item_id order BY rec_dt) grandqty from t1) group by project_id,item_id)

SELECT smschm01, 

         (select to_char(max(smchgm08),'yyyy/mm/dd') from smchgm where smchgm04=1 and smchgm03=smschm04) chg01,

         smschm02, 

         (select to_char(max(smchgm08),'yyyy/mm/dd') from smchgm where smchgm04=2 and smchgm03=smschm04) chg02,

         smschm03, 

         (select to_char(max(smchgm08),'yyyy/mm/dd') from smchgm where smchgm04=3 and smchgm03=smschm04) chg03,

         smschm04, 

         (select to_char(max(smchgm08),'yyyy/mm/dd') from smchgm where smchgm03=smschm04) chg04,

         smschm05, 

         (select to_char(max(smchgm08),'yyyy/mm/dd') from smchgm where smchgm04=5 and smchgm03=smschm04) chg05,

         smschm06, 

         smschm07, 

         (select to_char(max(smchgm08),'yyyy/mm/dd') from smchgm where smchgm04=7 and smchgm03=smschm04) chg07,

         smschm08, 

         (select to_char(max(smchgm08),'yyyy/mm/dd') from smchgm where smchgm04=8 and smchgm03=smschm04) chg08,

         smschm09, 

         (select to_char(max(smchgm08),'yyyy/mm/dd') from smchgm where smchgm04=9 and smchgm03=smschm04) chg09,

         smschm10, 

         (select to_char(max(smchgm08),'yyyy/mm/dd') from smchgm where smchgm04=10 and smchgm03=smschm04) chg10,

         smschm11, 

         (select to_char(max(smchgm08),'yyyy/mm/dd') from smchgm where smchgm04=11 and smchgm03=smschm04) chg11,

         smschm12, 

         (select to_char(max(smchgm08),'yyyy/mm/dd') from smchgm where smchgm04=12 and smchgm03=smschm04) chg12,

         smschm13, 

         (select to_char(max(smchgm08),'yyyy/mm/dd') from smchgm where smchgm04=13 and smchgm03=smschm04) chg13,

         smschm14, 

         (select to_char(max(smchgm08),'yyyy/mm/dd') from smchgm where smchgm04=14 and smchgm03=smschm04) chg14,

         smschm15,   

          smschm16, 

         smschm17,

         (smschm07-smschm16) 未完量, 

         (select to_char(max(smchgm08),'yyyy/mm/dd') from smchgm where smchgm04=17 and smchgm03=smschm04) chg17,

         smschm18, 

         smschm19, 

         (select to_char(max(smchgm08),'yyyy/mm/dd') from smchgm where smchgm04=19 and smchgm03=smschm04) chg19,

         smschm20, 

         smschm21, 

         smschm22, 

         smschm23, 

         smschm24, 

         smschm25, 

         smschm26, 

         smschm27, 

         smschm28, 

         smschm29, 

         smschm30,   

         smschm31, 

         smschm32, 

         to_char((select YC_FND_PUB.END_DATE_OF_WEEKLY(TO_CHAR(SYSDATE,'YYYY'), YC_FND_PUB.week_num_of_year(sysdate) - 1) -1  FROM DUAL),'yyyy/mm/dd') last5,

         (select to_char(max(smchgm08),'yyyy/mm/dd') from smchgm where smchgm04=32 and smchgm03=smschm04) chg32,

         smschm33, 

         (select to_char(max(smchgm08),'yyyy/mm/dd') from smchgm where smchgm04=33 and smchgm03=smschm04) chg33,

         smschm34, 

         (select to_char(max(smchgm08),'yyyy/mm/dd') from smchgm where smchgm04=34 and smchgm03=smschm04) chg34,

         smschm35,

--TC0   

         case when (select sum(a1.smschm07) from smschm a1 where a1.smschm04=a.project_number and a1.smschm08<=a0.smschm08 and smschm31 is null) <= (select sum(START_QUANTITY) from WIP_DISCRETE_JOBS where project_id=a.project_id and class_code like '%組車%' group by project_id)

            then nvl((select yc_wip_pub.wipreq_shortage_check(107,null,nvl(a.project_id,(select project_id from WIP_DISCRETE_JOBS_V where organization_id=107 and wip_entity_name=smschm04)),null) from dual),'Y')

            else ' ' end short,

       case when (select sum(a1.smschm07) from smschm a1 where a1.smschm04=a.project_number and a1.smschm08<=a0.smschm08 and smschm31 is null) <= (select sum(START_QUANTITY) from WIP_DISCRETE_JOBS where project_id=a.project_id and class_code like '%組車%' group by project_id) then 99 else 0 end workno,

         nvl((select miv.segment1 from oe_order_lines_all ool, mtl_item_categories_v miv where ool.inventory_item_id=MIV.INVENTORY_ITEM_ID and miv.organization_id=107 and miv.category_set_id=1100000103 and ool.ordered_quantity>0 and ool.project_id=a.project_id and rownum=1),' ') model_name,

         (select d1.vendor_name_alt

                from po_headers_all a1,po_lines_all b1,Po_Distributions_All c1,po_vendors d1,mtl_system_items_b e1

                where a1.vendor_id=d1.vendor_id  

                and substr(e1.segment1,1,3)='032' and b1.item_id=e1.inventory_item_id and e1.organization_id=107

                and nvl(a1.cancel_flag,'N')='N' and a1.po_header_id=c1.po_header_id

                and nvl(b1.cancel_flag,'N')='N' and b1.po_line_id=c1.po_line_id

                and c1.org_id=106 and c1.project_id=a.project_id and rownum=1) boxmaker,

        decode(yc_fnd_pub.is_numberc(smschm35),'N',' ',(select b1.name from oe_order_headers_all a1,JTF_RS_SALESREPS b1 where a1.salesrep_id=b1.salesrep_id and a1.org_id=106 and a1.order_number=smschm35)) sales,

      case when nvl((select 'Y' from sm4sbm where sm4sbm02='1' and sm4sbm09=smschm08 and sm4sbm01=smschm04),' ') = 'Y' then 'Y'

         when (select grandqty from t2 where project_id=a.project_id and item_id=318750 ) >= (select sum(aa.smschm07) from smschm aa where aa.smschm04= a0.smschm04 and aa.smschm08 <= a0.smschm08) then 'Y' 

         else case when nvl((select grandqty from t2 where project_id=a.project_id and item_id=318750 ),-1) = -1 then 'X'  else '' end end p_500101,

      case when nvl((select 'Y' from sm4sbm where sm4sbm02='2' and sm4sbm09=smschm08 and sm4sbm01=smschm04),' ') = 'Y' then 'Y'

         when (select grandqty from t2 where project_id=a.project_id and item_id=318751 ) >= (select sum(aa.smschm07) from smschm aa where aa.smschm04= a0.smschm04 and aa.smschm08 <= a0.smschm08) then 'Y' 

         else case when nvl((select grandqty from t2 where project_id=a.project_id and item_id=318751 ),-1) = -1 then 'X'  else '' end end p_500102,

      case when nvl((select 'Y' from sm4sbm where sm4sbm02='3' and sm4sbm09=smschm08 and sm4sbm01=smschm04),' ') = 'Y' then 'Y'

         when (select grandqty from t2 where project_id=a.project_id and item_id=318752 ) >= (select sum(aa.smschm07) from smschm aa where aa.smschm04= a0.smschm04 and aa.smschm08 <= a0.smschm08) then 'Y' 

         else case when nvl((select grandqty from t2 where project_id=a.project_id and item_id=318752 ),-1) = -1 then 'X'  else '' end end p_500103,

      case when nvl((select 'Y' from sm4sbm where sm4sbm02='4' and sm4sbm09=smschm08 and sm4sbm01=smschm04),' ') = 'Y' then 'Y'

         when (select grandqty from t2 where project_id=a.project_id and item_id=318753 ) >= (select sum(aa.smschm07) from smschm aa where aa.smschm04= a0.smschm04 and aa.smschm08 <= a0.smschm08) then 'Y' 

         else case when nvl((select grandqty from t2 where project_id=a.project_id and item_id=318753 ),-1) = -1 then 'X'  else '' end end p_500104,

        nvl((select 'Y' from sqabqm where sqabqm16 is null and sqabqm05=smschm04 and rownum=1),'N') qcbad,

        nvl((select 'Y' from sspe1m where sspe1m11=smschm04 and rownum=1),'N') spec1,

        nvl((select 'Y' from sspe1m where sspe1m10=nvl((select segment1 from mtl_item_categories_v where category_Set_id=1100000103 and organization_id=107 and inventory_item_id=yc_get_inventory_item_id(smschm04,107)),' ') 

           and ((((instr(smschm30,'-') > 0 and instr(sspe1m08,'-') > 0) or (instr(smschm30,'-') = 0 and instr(sspe1m08,'-') = 0))  and smschm30 = sspe1m08)

           or ((instr(smschm30,'-') = 0 and instr(sspe1m08,'-') > 0) and substr(sspe1m08,1,instr(sspe1m08,'-') - 1) = smschm30)

           or ((instr(smschm30,'-') > 0 and instr(sspe1m08,'-') = 0) and substr(smschm30,1,instr(smschm30,'-') - 1) = sspe1m08)) and rownum=1),'N') spec2,

        tc_chkshort(smschm04) shortyn,

        nvl((select max(smshtm10) from smshtm where smshtm08 is null and smshtm01=smschm04),'20190901') last_indate,

        smschm42,smschm39,

         nvl((select attribute1 from mtl_categories_b where structure_id=50430 and segment1=nvl((select segment1 from mtl_item_categories_v where category_Set_id=1100000103 and organization_id=107 and inventory_item_id=yc_get_inventory_item_id(smschm04,107)),' ')),'0') stdsec,

--TC1

 smschm38,

         smschm39,

         smschm40,

         smschm45,

          case when (select sum(a1.smschm07) from smschm a1 where a1.smschm04=a.project_number and a1.smschm08<=a0.smschm08 and smschm31 is null) <= (select sum(START_QUANTITY) from WIP_DISCRETE_JOBS where project_id=a.project_id and class_code like '%組車%' group by project_id)

            then nvl((select yc_wip_pub.wipreq_shortage_check(107,null,nvl(a.project_id,(select project_id from WIP_DISCRETE_JOBS_V where organization_id=107 and wip_entity_name=smschm04)),null) from dual),'Y')

            else ' ' end short,

         case when (select sum(a1.smschm07) from smschm a1 where a1.smschm04=a.project_number and a1.smschm08<=a0.smschm08 and smschm31 is null) <= (select sum(START_QUANTITY) from WIP_DISCRETE_JOBS where project_id=a.project_id and class_code like '%組車%' group by project_id) then 99 else 0 end workno,

         --nvl((select segment1 from mtl_item_categories_v where category_Set_id=1100000103 and organization_id=107 and inventory_item_id=yc_get_inventory_item_id(smschm04,107)),' ') model_name,

         nvl((select miv.segment1 from oe_order_lines_all ool, mtl_item_categories_v miv where ool.inventory_item_id=MIV.INVENTORY_ITEM_ID and miv.organization_id=107 and miv.category_set_id=1100000103 and ool.ordered_quantity>0 and ool.project_id=a.project_id and rownum=1),' ') model_name,

         nvl((select replace(segment2,'"','') from mtl_item_categories_v where category_Set_id=1100000062 and organization_id=107 and inventory_item_id=yc_get_inventory_item_id(smschm04,107)),' ') csize,

         nvl((select segment1 from mtl_item_categories_v where category_Set_id=1100000102 and organization_id=107 and inventory_item_id=yc_get_inventory_item_id(smschm04,107)),' ') pinnm,

         (select d1.vendor_name_alt

                from po_headers_all a1,po_lines_all b1,Po_Distributions_All c1,po_vendors d1,mtl_system_items_b e1

                where a1.vendor_id=d1.vendor_id  

                and substr(e1.segment1,1,3)='032' and b1.item_id=e1.inventory_item_id and e1.organization_id=107

                and nvl(a1.cancel_flag,'N')='N' and a1.po_header_id=c1.po_header_id

                and nvl(b1.cancel_flag,'N')='N' and b1.po_line_id=c1.po_line_id

                and c1.org_id=106 and c1.project_id=a.project_id and rownum=1) boxmaker,

        decode(yc_fnd_pub.is_numberc(smschm35),'N',' ',(select b1.name from oe_order_headers_all a1,JTF_RS_SALESREPS b1 where a1.salesrep_id=b1.salesrep_id and a1.org_id=106 and a1.order_number=smschm35)) sales,

      case when nvl((select 'Y' from sm4sbm where sm4sbm02='1' and sm4sbm09=smschm08 and sm4sbm01=smschm04),' ') = 'Y' then 'Y'

         when (select grandqty from t2 where project_id=a.project_id and item_id=318750 ) >= (select sum(aa.smschm07) from smschm aa where aa.smschm04= a0.smschm04 and aa.smschm08 <= a0.smschm08) then 'Y' 

         else case when nvl((select grandqty from t2 where project_id=a.project_id and item_id=318750 ),-1) = -1 then 'X'  else '' end end p_500101,

      case when nvl((select 'Y' from sm4sbm where sm4sbm02='2' and sm4sbm09=smschm08 and sm4sbm01=smschm04),' ') = 'Y' then 'Y'

         when (select grandqty from t2 where project_id=a.project_id and item_id=318751 ) >= (select sum(aa.smschm07) from smschm aa where aa.smschm04= a0.smschm04 and aa.smschm08 <= a0.smschm08) then 'Y' 

         else case when nvl((select grandqty from t2 where project_id=a.project_id and item_id=318751 ),-1) = -1 then 'X'  else '' end end p_500102,

      case when nvl((select 'Y' from sm4sbm where sm4sbm02='3' and sm4sbm09=smschm08 and sm4sbm01=smschm04),' ') = 'Y' then 'Y'

         when (select grandqty from t2 where project_id=a.project_id and item_id=318752 ) >= (select sum(aa.smschm07) from smschm aa where aa.smschm04= a0.smschm04 and aa.smschm08 <= a0.smschm08) then 'Y' 

         else case when nvl((select grandqty from t2 where project_id=a.project_id and item_id=318752 ),-1) = -1 then 'X'  else '' end end p_500103,

      case when nvl((select 'Y' from sm4sbm where sm4sbm02='4' and sm4sbm09=smschm08 and sm4sbm01=smschm04),' ') = 'Y' then 'Y'

         when (select grandqty from t2 where project_id=a.project_id and item_id=318753 ) >= (select sum(aa.smschm07) from smschm aa where aa.smschm04= a0.smschm04 and aa.smschm08 <= a0.smschm08) then 'Y' 

         else case when nvl((select grandqty from t2 where project_id=a.project_id and item_id=318753 ),-1) = -1 then 'X'  else '' end end p_500104,

        nvl((select case when sqabqm16 is null then 'X' else 'Y' end from sqabqm where sqabqm05=smschm04 and rownum=1),'N') qcbad,

        nvl((select 'Y' from sspe1m where sspe1m11=(case when smschm04='W2203027' and (substr(smschm30,7,2)='GB' or substr(smschm30,7,2)='AU') then smschm04||substr(smschm30,7,2) else smschm04 end) and rownum=1),'N') spec1,

        nvl((select 'Y' from sspe1m where sspe1m10=nvl((select segment1 from mtl_item_categories_v where category_Set_id=1100000103 and organization_id=107 and inventory_item_id=yc_get_inventory_item_id(smschm04,107)),' ') 

           and ((((instr(smschm30,'-') > 0 and instr(sspe1m08,'-') > 0) or (instr(smschm30,'-') = 0 and instr(sspe1m08,'-') = 0))  and smschm30 = sspe1m08)

           or ((instr(smschm30,'-') = 0 and instr(sspe1m08,'-') > 0) and substr(sspe1m08,1,instr(sspe1m08,'-') - 1) = smschm30)

           or ((instr(smschm30,'-') > 0 and instr(sspe1m08,'-') = 0) and substr(smschm30,1,instr(smschm30,'-') - 1) = sspe1m08)

              or (1 = case when (instr(smschm30,'A005')>0 and instr(sspe1m08,'A658')>0) or (instr(sspe1m08,'A005')>0 and instr(smschm30,'A658')>0)

                                  or (instr(smschm30,'A528')>0 and instr(sspe1m08,'A659')>0) or (instr(sspe1m08,'A528')>0 and instr(smschm30,'A659')>0)

                         then 1 else 0 end)) and 

            sspe1m05=nvl((select '20'||substr(attribute3,3,2) from oe_order_headers_all where order_number=smschm35 and ORG_ID=106),'9999') and rownum=1),'N') spec2,

        tc_chkshort(smschm04) shortyn,

        nvl((select max(smshtm10) from smshtm where smshtm08 is null and smshtm01=smschm04),'20190901') last_indate,

        smschm42,smschm46,smschm43,smschm44,

        decode(substr(smschm03,1,2)||substr(smschm30,1,instr(smschm30,'-') - 1),'T5A542',(case when substr(smschm08,1,6)='202101' then 150 when substr(smschm08,1,6)='202102' then 120 else nvl((select to_number(nvl(attribute1,'0')) from mtl_categories_b where structure_id=50430 and segment1=nvl((select segment1 from mtl_item_categories_v where category_Set_id=1100000103 and organization_id=107 and inventory_item_id=yc_get_inventory_item_id(smschm04,107)),' ')),0) end), 

            nvl((select to_number(nvl(attribute1,'0')) from mtl_categories_b where structure_id=50430 and segment1=nvl((select miv.segment1 from oe_order_lines_all ool, mtl_item_categories_v miv where ool.inventory_item_id=MIV.INVENTORY_ITEM_ID and miv.organization_id=107 and miv.category_set_id=1100000103 and ool.ordered_quantity>0 and ool.project_id=a.project_id and rownum=1),' ')),0)*(case when substr(smschm03,1,2)='T5' then 1 when smschm03='T2' and smschm06='2201T' then 0.5 when smschm03='T2' and smschm06='2101T' then 3 else 1 end)) stdsec,

            (select sm4sbm05 from sm4sbm where sm4sbm02='1' and nvl(sm4sbm09,case when (select count(*) from sm4sbm a1 where a1.sm4sbm02='1' and a1.sm4sbm01=smschm04 and nvl(a1.sm4sbm09,smschm08)=smschm08) = 1 then smschm08 else ' ' end)=smschm08 and sm4sbm01=smschm04) bo1,

         (select sm4sbm05 from sm4sbm where sm4sbm02='2' and nvl(sm4sbm09,case when (select count(*) from sm4sbm a1 where a1.sm4sbm02='2' and a1.sm4sbm01=smschm04 and nvl(a1.sm4sbm09,smschm08)=smschm08) = 1 then smschm08 else ' ' end)=smschm08 and sm4sbm01=smschm04) bo2,

         (select sm4sbm05 from sm4sbm where sm4sbm02='3' and nvl(sm4sbm09,case when (select count(*) from sm4sbm a1 where a1.sm4sbm02='3' and a1.sm4sbm01=smschm04 and nvl(a1.sm4sbm09,smschm08)=smschm08) = 1 then smschm08 else ' ' end )=smschm08 and sm4sbm01=smschm04) bo3,

         (select sm4sbm05 from sm4sbm where sm4sbm02='4' and nvl(sm4sbm09,case when (select count(*) from sm4sbm a1 where a1.sm4sbm02='4' and a1.sm4sbm01=smschm04 and nvl(a1.sm4sbm09,smschm08)=smschm08) = 1 then smschm08 else ' ' end)=smschm08 and sm4sbm01=smschm04) bo4,

         nvl((select attribute4 from mtl_categories_b where structure_id=50430 and segment1=nvl((select segment1 from mtl_item_categories_v where category_Set_id=1100000103 and organization_id=107 and inventory_item_id=yc_get_inventory_item_id(smschm04,107)),' ')),'0') stdbo1,

         nvl((select attribute5 from mtl_categories_b where structure_id=50430 and segment1=nvl((select segment1 from mtl_item_categories_v where category_Set_id=1100000103 and organization_id=107 and inventory_item_id=yc_get_inventory_item_id(smschm04,107)),' ')),'0') stdbo2,

         nvl((select attribute6 from mtl_categories_b where structure_id=50430 and segment1=nvl((select segment1 from mtl_item_categories_v where category_Set_id=1100000103 and organization_id=107 and inventory_item_id=yc_get_inventory_item_id(smschm04,107)),' ')),'0') stdbo3,

         nvl((select attribute7 from mtl_categories_b where structure_id=50430 and segment1=nvl((select segment1 from mtl_item_categories_v where category_Set_id=1100000103 and organization_id=107 and inventory_item_id=yc_get_inventory_item_id(smschm04,107)),' ')),'0') stdbo4,

       nvl((select case when nvl(b.attribute12,' ') <= ' ' then a.attribute3 else b.attribute12 end from oe_order_headers_all a,oe_order_lines_all b ,pjm_seiban_numbers c where a.header_id=b.header_id and b.project_id=c.project_id and c.project_number=smschm04 and b.CANCELLED_FLAG='N' and rownum=1),smschm01) erpyy, ' ' nownow,

         nvl((select 'Y' from spblum where spblum01=(select segment1 from mtl_item_categories_v where category_Set_id=1100000103 and organization_id=107 and inventory_item_id=yc_get_inventory_item_id(smschm04,107)) and rownum=1),'N') SOP,

         nvl((select 'Y' from WIP_REQUIREMENT_OPERATIONS a,WIP_DISCRETE_JOBS b,pjm_seiban_numbers c where a.wip_entity_id=b.wip_entity_id and  b.project_id=c.project_id and a.WIP_SUPPLY_TYPE <> 6 and a.ORGANIZATION_ID=107 and c.project_number=smschm04 and a.comments is not null and rownum=1),'N') wipnote,

        nvl((select count(*) from po_lines_all aa,po_distributions_all bb where aa.po_line_id=bb.po_line_id and bb.org_id=106 and bb.quantity_ordered - bb.quantity_cancelled>0 and bb.project_id=a.project_id),0) cnt1,

        nvl((select sum(case when bb.quantity_ordered>bb.quantity_delivered then 0 else 1 end) from po_lines_all aa,po_distributions_all bb where aa.po_line_id=bb.po_line_id and bb.org_id=106 and bb.quantity_ordered - bb.quantity_cancelled>0 and bb.project_id=a.project_id),0) cnt2,

        nvl((select to_number(nvl(replace(attribute12,',',''),'0'))+to_number(nvl(replace(attribute13,',',''),'0')) from pjm_project_parameters_v where organization_id=107 and project_number=smschm04),0) price,

        nvl((select saparm43 from saparm where saparm01=substr(smschm03,1,2)),0) stdamt,

        nvl((select count(*) from (select distinct aa.smschm08 from smschm aa where  aa.smschm01 = '2024' and aa.smschm02 ='1' AND ( aa.smschm03 is null or aa.smschm03 in( 'T2','T2A','T2B','T2S','TC1','TC2'))) abc),0) days,

        nvl((select 'Y' from mtl_material_transactions aa,pjm_seiban_numbers bb where aa.organization_id=107 and nvl(bb.attribute1,'N')='N' and aa.project_id=bb.project_id and nvl(aa.project_id,-1)<>nvl(aa.to_project_id,-1) and aa.primary_quantity<0 and aa.transaction_type_id=67 and aa.to_project_id=a.project_id and rownum=1),'N') move

FROM smschm a0,pjm_seiban_numbers a

WHERE smschm04 = a.project_number(+) and

      smschm31 is null and 

--      substr(smschm03,1,2)='TC' and  

      smschm01 = '2024'  and smschm02 ='1' AND smschm03 in ('T2','T2A','T2B','T2S','TC1','TC2','sp') 

ORDER BY smschm08 ASC  

WEB预览时会报错,无效数字,但将参数带入贴到SQL Develope可以跑,甚至是在这个数据集的SQL预览都可以跑出数据