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 aWHERE 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预览都可以跑出数据
