PAC工单科目金额明细

SELECT we.wip_entity_name,
        1 transaction_type_id,
        WT.TRANSACTION_QUANTITY,
         wt.transaction_date,
         fnd_flex_ext.get_segs('SQLGL',
                               'GL#',
                               cgacd.chart_of_accounts_id,
                               cal.code_combination_id) cc_code,
                               
         xla_oa_functions_pkg.get_ccid_description(cgacd.chart_of_accounts_id,
                                                   cal.code_combination_id) cc_desc,
         cal.accounted_dr,
         cal.accounted_cr
    FROM cst_ae_headers            cah,
         cst_ae_lines              cal,
         cux_gl_account_code_desc  cgacd,
         HR_ALL_ORGANIZATION_UNITS HAOU,
         wip_transactions          wt,
         WIP_ENTITIES              WE
   WHERE 1 = 1
     and cah.organization_id = HAOU.Organization_Id
   --  and cah.gl_transfer_flag = 'Y'
     AND cah.ae_header_id = cal.ae_header_id
     AND cal.code_combination_id = cgacd.code_combination_id
     and cah.ae_category = 'WIP' -- <> 'Accrual' ---Accrual在GL中将被冲销/* MTL,      WIP,      Receiving*/            
     and cah.organization_id = wt.organization_id
     and cal.source_id = wt.transaction_id
     and we.wip_entity_name = 'WI-2310003'
     and cah.set_of_books_id = 2023
     AND WT.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
     AND WT.ORGANIZATION_ID = WE.ORGANIZATION_ID
    -- AND cgacd.segment3 = '5002'
    -- AND cgacd.segment4= '1001'
   --  and cah.period_name <= '2022-01'
  union all
  SELECT we.wip_entity_name,
  mmt.transaction_type_id,
  MMT.TRANSACTION_QUANTITY,
         mmt.transaction_date,
         fnd_flex_ext.get_segs('SQLGL',
                               'GL#',
                               cgacd.chart_of_accounts_id,
                               cal.code_combination_id) cc_code,
                               
         xla_oa_functions_pkg.get_ccid_description(cgacd.chart_of_accounts_id,
                                                   cal.code_combination_id) cc_desc,
         cal.accounted_dr,
         cal.accounted_cr
  
    FROM cst_ae_headers            cah,
         cst_ae_lines              cal,
         cux_gl_account_code_desc  cgacd,
         HR_ALL_ORGANIZATION_UNITS HAOU,
         mtl_material_transactions mmt,
         WIP_ENTITIES              WE
   WHERE 1 = 1
     and cah.organization_id = HAOU.Organization_Id
    -- and cah.gl_transfer_flag = 'Y'
     AND cah.ae_header_id = cal.ae_header_id
     AND cal.code_combination_id = cgacd.code_combination_id
     and cah.ae_category = 'MTL' -- <> 'Accrual' ---Accrual在GL中将被冲销/* MTL,      WIP,      Receiving*/            
     and cah.organization_id = mmt.organization_id
     AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
     AND MMT.TRANSACTION_SOURCE_ID = WE.WIP_ENTITY_ID
     AND MMT.ORGANIZATION_ID = WE.ORGANIZATION_ID
     and cal.source_id = mmt.transaction_id
     --   and cgacd.segment3 = '5002'
     and cah.set_of_books_id = 2023
    --  AND cgacd.segment4= '1001'
     -- and cah.period_name <= '2022-01'
     and we.wip_entity_name = 'WI-2310003'