SQL语句 - 跟踪PO对应的PR、Receipt、invoice信息

SQL语句 - 跟踪PO对应的PR、Receipt、invoice信息

SELECT DISTINCT hou.name 业务实体,
                poh.segment1 采购订单号,
                pol.line_num 采购订单行号,
                pol.unit_price 采购单价,
                mcb.segment1 || '.' || mcb.segment2 || '.' || mcb.segment3 采购类别,
                msiv.segment1 物料编码,
                pol.item_description 物料描述,
                pol.quantity 采购行数量,
                cux_public_utl.get_rec_line_combi(pol.po_line_id) 接收信息,
                cux_public_utl.get_po_line_rec(pol.po_line_id) 接收行数量,
                pol.quantity -
                cux_public_utl.get_po_line_rec(pol.po_line_id) 行总未接收数量,
                cux_public_utl.get_pr_line_combi(pol.po_line_id) 采购申请, 
                                                                 /*汇总 采购申请号-行 */
                cux_public_utl.get_pr_line_qty(pol.po_line_id) 采购申请行数量, 
                                                               /*汇总*/
                pol.quantity -
                cux_public_utl.get_pr_line_qty(pol.po_line_id) pr 行未下单数量, 
                                                               /*行申请数量汇总-行对应的采购订单行数量*/
                cux_public_utl.get_pr_budget_number(pol.po_line_id) 预算号, 
                                                                    /*汇总*/
                cux_public_utl.get_ap_line_combi(pol.po_line_id) 发票信息,
                cux_public_utl.get_ap_line_qty(pol.po_line_id) 匹配数量,
                pol.quantity -
                cux_public_utl.get_ap_line_qty(pol.po_line_id) 未匹配数量
  FROM po_headers_all               poh,
       po_lines_all                 pol,
       po_distributions_all         pod,
       hr_operating_units           hou,
       mtl_categories_b             mcb,
       financials_system_params_all fsp,
       mtl_system_items_vl          msiv
 WHERE 1 = 1
   AND pod.po_header_id = poh.po_header_id
   AND pod.po_line_id = pol.po_line_id
   AND pod.org_id = hou.organization_id
   AND mcb.category_id = pol.category_id
   AND hou.organization_id = fsp.org_id
   AND (msiv.organization_id = fsp.inventory_organization_id OR
       msiv.organization_id IS NULL)
   AND msiv.inventory_item_id(+) = pol.item_id
   AND pol.line_type_id = 1000 /*费用*/
   AND ascii(mcb.segment1) > 64
   AND hou.name = 'OU' /*条件1：选择目标OU。*/
   AND to_char(poh.creation_date, 'yyyy-mm-dd') >= '2016-01-01' 
       /*条件2：PO创建对应的起始日期，选择创建日期范围，如不需要限制创建日期，请注释该条件*/
   AND to_char(poh.creation_date, 'yyyy-mm-dd') <= '2016-01-31' 
       /*条件2：PO创建对应的截止日期，选择创建日期范围，如不需要限制创建日期，请注释该条件*/
 ORDER BY poh.segment1, pol.line_num;

 

SQL语句 - 查询科目的期间发生额、期初、期末余额

select gltb.period_name,
       glccid.segment1,
       glccid.segment2,
       glccid.segment3,
       glccid.segment4,
       glccid.segment5,
       gltb.currency_code,
       glccid.concatenated_segments,
       glle.ledger_id,
       glle.name Ledger_Name,
       nvl(gltb.begin_balance_dr, 0) - nvl(gltb.begin_balance_cr, 0) YTD_Period_Start,
       nvl(gltb.period_net_dr, 0) - nvl(gltb.period_net_cr, 0) PTD,
       nvl(gltb.period_net_dr, 0) + nvl(gltb.begin_balance_dr, 0) -
       nvl(gltb.period_net_cr, 0) - nvl(gltb.begin_balance_cr, 0) YTD_Period_End,
       glccid.enabled_flag,
       glccid.end_date_active,
       gltb.last_update_date ---- GL Trial Balance Detail
  from gl.gl_balances                gltb,
       apps.gl_code_combinations_kfv glccid,
       gl.gl_ledgers                 glle
 where gltb.code_combination_id = glccid.code_combination_id
   and gltb.ledger_id = glle.ledger_id
   and gltb.template_id is null
   and glccid.summary_flag = 'N'
   and gltb.actual_flag = 'A'
   and gltb.currency_code <> 'STAT'
   and not exists
 (select 1
          from rg.rg_report_axis_contents rptl,
               rg.rg_report_axis_sets     rptrh,
               rg.rg_report_axes          rptrhi
         where rptrh.axis_set_id = rptl.axis_set_id
           and rptrh.axis_set_id = rptrhi.axis_set_id
           and rptl.axis_seq = rptrhi.axis_seq
           and rptrh.axis_set_type = 'R'
           and glccid.segment1 between
               decode(rptl.segment1_low,
                      '',
                      '0',
                      'T',
                      '0',
                      rptl.segment1_low) and
               decode(rptl.segment1_high, '', 'T', rptl.segment1_high)
           and glccid.segment2 between
               decode(rptl.segment2_low,
                      '',
                      '0',
                      'T',
                      '0',
                      rptl.segment2_low) and
               decode(rptl.segment2_high, '', 'T', rptl.segment2_high)
           and glccid.segment3 between
               decode(rptl.segment3_low,
                      '',
                      '0',
                      'T',
                      '0',
                      rptl.segment3_low) and
               decode(rptl.segment3_high, '', 'T', rptl.segment3_high)
           and glccid.segment4 between
               decode(rptl.segment4_low,
                      '',
                      '0',
                      'T',
                      '0',
                      rptl.segment4_low) and
               decode(rptl.segment4_high, '', 'T', rptl.segment4_high)
           and glccid.segment5 between
               decode(rptl.segment5_low,
                      '',
                      '0',
                      'T',
                      '0',
                      rptl.segment5_low) and
               decode(rptl.segment5_high, '', 'T', rptl.segment5_high)
           and glccid.segment6 between
               decode(rptl.segment6_low,
                      '',
                      '0',
                      'T',
                      '0',
                      rptl.segment6_low) and
               decode(rptl.segment6_high, '', 'T', rptl.segment6_high)
           and glccid.segment7 between
               decode(rptl.segment7_low,
                      '',
                      '0',
                      'T',
                      '0',
                      rptl.segment7_low) and
               decode(rptl.segment7_high, '', 'T', rptl.segment7_high)
           and glccid.segment8 between
               decode(rptl.segment8_low,
                      '',
                      '0',
                      'T',
                      '0',
                      rptl.segment8_low) and
               decode(rptl.segment8_high, '', 'T', rptl.segment8_high)
           and glccid.segment9 between
               decode(rptl.segment9_low,
                      '',
                      '0',
                      'T',
                      '0',
                      rptl.segment9_low) and
               decode(rptl.segment9_high, '', 'T', rptl.segment9_high)
           and rptrh.name like 'Balance Sheet_US')
   and nvl(gltb.period_net_dr, 0) + nvl(gltb.begin_balance_dr, 0) -
       nvl(gltb.period_net_cr, 0) - nvl(gltb.begin_balance_cr, 0) <> 0
--   and gltb.period_name = '2015-12'
--期间名字
--   and glle.name like'%TS%-PRC'
--账套名字

SQL语句 - 比对销售订单SO上的信息 头行收单收货方

SELECT hou.name              业务实体,
       ood.organization_code 仓库,
       ooh.order_number      订单编号,
       otl.name              订单类型,
       --公用信息       
       bill_to.party_name     头客户,
       bill_to.account_number 头客户编号,
       ship_to.location       头收货方,
       ship_to.address1       头收货地址,
       bill_to.location       头收单方,
       bill_to.address1       头收单方地址,
       --以上为头信息
       bill_to2.party_name     行客户,
       bill_to2.account_number 行客户编号,
       ship_to2.location       行收货方,
       ship_to2.address1       行收货地址,
       bill_to2.location       行收单方,
       bill_to2.address1       行收单方地址,
       --以上为行信息       
       ooh.cust_po_number        头客户po,
       ool.cust_po_number        行客户po,
       otll.name                 行类型,
       psn.project_number        项目编号,
       ool.shipping_instructions 行包装说明
  FROM oe_order_headers_all ooh, --1452
       hr_operating_units hou,
       oe_transaction_types_tl otl,
       hz_cust_accounts hca,
       hz_parties hp,
       oe_order_lines_all ool,
       oe_transaction_types_tl otll,
       pjm_seiban_numbers psn,
       mtl_system_items_vl msi,
       org_organization_definitions ood,
       (SELECT csu.location, loc.address1, csu.site_use_id, hp.party_name
          FROM hz_cust_site_uses_all  csu,
               hz_cust_acct_sites_all cas,
               hz_party_sites         hps,
               hz_locations           loc,
               hz_cust_accounts       hca,
               hz_parties             hp
         WHERE cas.cust_acct_site_id = csu.cust_acct_site_id
           AND hps.party_site_id = cas.party_site_id
           AND loc.location_id = hps.location_id
           AND hca.cust_account_id = cas.cust_account_id
           AND hp.party_id = hca.party_id) ship_to,
       (SELECT csu.location,
               loc.address1,
               csu.site_use_id,
               hp.party_name,
               hca.account_number
          FROM hz_cust_site_uses_all  csu,
               hz_cust_acct_sites_all cas,
               hz_party_sites         hps,
               hz_locations           loc,
               hz_cust_accounts       hca,
               hz_parties             hp
         WHERE cas.cust_acct_site_id = csu.cust_acct_site_id
           AND hps.party_site_id = cas.party_site_id
           AND loc.location_id = hps.location_id
           AND hca.cust_account_id = cas.cust_account_id
           AND hp.party_id = hca.party_id) bill_to,
       (SELECT csu.location, loc.address1, csu.site_use_id, hp.party_name
          FROM hz_cust_site_uses_all  csu,
               hz_cust_acct_sites_all cas,
               hz_party_sites         hps,
               hz_locations           loc,
               hz_cust_accounts       hca,
               hz_parties             hp
         WHERE cas.cust_acct_site_id = csu.cust_acct_site_id
           AND hps.party_site_id = cas.party_site_id
           AND loc.location_id = hps.location_id
           AND hca.cust_account_id = cas.cust_account_id
           AND hp.party_id = hca.party_id) ship_to2,
       (SELECT csu.location,
               loc.address1,
               csu.site_use_id,
               hp.party_name,
               hca.account_number
          FROM hz_cust_site_uses_all  csu,
               hz_cust_acct_sites_all cas,
               hz_party_sites         hps,
               hz_locations           loc,
               hz_cust_accounts       hca,
               hz_parties             hp
         WHERE cas.cust_acct_site_id = csu.cust_acct_site_id
           AND hps.party_site_id = cas.party_site_id
           AND loc.location_id = hps.location_id
           AND hca.cust_account_id = cas.cust_account_id
           AND hp.party_id = hca.party_id) bill_to2
 WHERE hou.organization_id = ooh.org_id
   AND otl.transaction_type_id = ooh.order_type_id
   AND otl.language = 'US'
   AND hca.cust_account_id(+) = ooh.sold_to_org_id
   AND hp.party_id(+) = hca.party_id
   AND ool.header_id(+) = ooh.header_id
   AND otll.transaction_type_id(+) = ool.line_type_id
   AND otll.language(+) = 'US'
   AND psn.project_id(+) = ool.project_id
   AND ship_to.site_use_id(+) = ooh.ship_to_org_id
   AND bill_to2.site_use_id(+) = ool.invoice_to_org_id
   AND ship_to2.site_use_id(+) = ool.ship_to_org_id
   AND bill_to.site_use_id(+) = ooh.invoice_to_org_id
   AND msi.inventory_item_id(+) = ool.inventory_item_id
   AND msi.organization_id(+) = ool.ship_from_org_id
   AND ood.organization_id(+) = ool.ship_from_org_id
   and (bill_to.location != Bill_to2.Location --头行收单方不一致的
       or ship_to.location != ship_to2.location) --头行收货方不一样的
--  AND OOH.ORDER_NUMBER in （'201152778'）
--如果看具体SO范围就填写
 ORDER BY hou.name, ooh.order_number, ool.schedule_ship_date