上个月报表开发总结

       最近我上个月开发的几个报表要收工，描述一下在开发过程中，遇到的主要问题：

1、前期不熟悉环境，要向老技术顾问咨询数据来源取数，我咨询的方向出了问题，他的技术不是最好的，到开发后期有很多问题做不了主，不能提供最优的建议，所以报表开发的时候最好咨询公司最厉害的技术，最有话语权的技术

2、报表选择参考模版也要选择公司最厉害的技术开发的模版，他开发的报表已经成为公司的参考模型，后期最省事，不然到后期验收的时候，很有可能会重新改版

     报表开发的技术问题

1、业务实体ou和组织ID

在FORM 里面获取业务实体OU 的org_id 方式是 fnd_profile.value(‘ORG_ID’)

获取组织ID organization_id 方式是 fnd_org.choose_org 这种方式弹出一个组织选择框，在登录form的时候，注意这里的organization_id 是库存组织ID ,在企业组织架构中是比业务实体OU 小一级的，所以可以通过一个表来转换到OU的id;实际上就是多个库存组织对应一个业务实体的关系

SELECT T.OPERATING_UNIT
      FROM ORG_ORGANIZATION_DEFINITIONS T
     WHERE T.ORGANIZATION_ID = 83;

2、用户信息的获取：FND_GLOBAL.USER_ID

3、用HTML 打印报表的时候，一定要注意表结构中的 COLSPAN,ROWSPAN;比较保险的做法是将打印好的报表复制到EXCEL 中观察其格式

4、左右连接：连接实际上就是取两个表的集合的操作，默认是内连接，即连接条件相同的记录的集合；左连接就是取内连接的结合与左边表剩余的记录(右表字段全为空)，这里有点搅，实际上可以这么看，(+)在那个表，对应表剩余记录全都要取

 

SELECT e.empno,e.ename,d.deptno,d.dname,d.loc
       FROM scott.emp e , scott.dept d
WHERE e.deptno(+)=d.deptno;

1. NO1 经销商票据报表

主要涉及的表：应收票据 AR_CASH_RECEIPTS_ALL ，应收方法 AR_RECEIPT_METHODS

AR_PAYMENT_SCHEDULES_ALL 应收事务表   AR_CASH_RECEIPT_HISTORY_ALL  应收历史记录表(记录状态)

SELECT AC.ATTRIBUTE10 CPYH, --出票银行
       AC.ATTRIBUTE13 PJLX, --票据类型
       AC.RECEIPT_NUMBER, --收款编号

       AC.ATTRIBUTE9 PH, --票号
       TO_CHAR(FND_DATE.CANONICAL_TO_DATE(AC.ATTRIBUTE11), 'YYYY-MM-DD') CP_DATE, --出票日
       TO_CHAR(APS.DUE_DATE, 'YYYY-MM-DD') DUE_DATE, --到期日
       AC.AMOUNT AMOUNT, --金额
       TO_CHAR(ACH.GL_DATE, 'YYYY-MM-DD') GL_DATE, --财务日期
       TO_CHAR(AC.RECEIPT_DATE, 'YYYY-MM-DD') CREATION_DATE --登记日期
FROM AR_CASH_RECEIPTS_ALL        AC,
       AR_RECEIPT_METHODS          ARM,
       AR_CASH_RECEIPT_HISTORY_ALL ACH,
       AR_PAYMENT_SCHEDULES_ALL    APS
WHERE AC.ATTRIBUTE13 IN ('电子承兑', '纸质承兑')
AND AC.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
AND ARM.NAME IN ('商业承兑汇票', '银行承兑汇票')
AND ACH.CASH_RECEIPT_ID(+) = AC.CASH_RECEIPT_ID
AND ACH.ORG_ID(+) = AC.ORG_ID
AND ACH.FIRST_POSTED_RECORD_FLAG(+) = 'Y'
AND APS.CASH_RECEIPT_ID(+) = AC.CASH_RECEIPT_ID
AND APS.ORG_ID(+) = AC.ORG_ID
--      AND AC.PAY_FROM_CUSTOMER = 5078
AND AC.ORG_ID = 81
--       AND AC.CUSTOMER_SITE_USE_ID = 1450

经销商账户和其公司地点目的之间的关系

SELECT ACNT.GLOBAL_ATTRIBUTE7 AREA, --大区
       DECODE(ACNT.GLOBAL_ATTRIBUTE8, '', '', ACNT.GLOBAL_ATTRIBUTE8) PROVINCE, --省份
       ACNT.ACCOUNT_NUMBER DEALER_CODE, --经销商代码
       HP.PARTY_NAME DEALER_NAME, --经销商名称
       USES.LOCATION,
       USES.SITE_USE_ID
FROM HZ_CUST_ACCOUNTS       ACNT,
             hz_parties hp,
       HZ_CUST_ACCT_SITES_ALL SITE,
       HZ_CUST_SITE_USES_ALL  USES
WHERE ACNT.PARTY_ID = HP.PARTY_ID(+)
AND ACNT.CUST_ACCOUNT_ID = SITE.CUST_ACCOUNT_ID
AND USES.CUST_ACCT_SITE_ID = SITE.CUST_ACCT_SITE_ID
AND USES.GLOBAL_ATTRIBUTE10 BETWEEN '01' AND '09'
AND USES.SITE_USE_CODE = 'BILL_TO'

1. NO2 批发信息报表(销售)

       批发信息也就是订单信息,主要涉及到订单和应收票据关系

挑选出所有符合要求的订单行

SELECT DISTINCT RCTL.SALES_ORDER SALES_ORDER,
                ac.CUSTOMER_NUMBER,
                AC.CUSTOMER_NAME PARTY_NAME,
                TO_CHAR(RCTL.SALES_ORDER_DATE, 'yyyy-mm-dd') SALES_ORDER_DATE,
                OOH.ATTRIBUTE2 REMARK
FROM RA_CUSTOMER_TRX_ALL       RCT,
       RA_CUSTOMER_TRX_LINES_ALL RCTL,
       MTL_SYSTEM_ITEMS_B        M,
       OE_ORDER_HEADERS_ALL      OOH,
       AR_CUSTOMERS              AC
WHERE RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID
AND RCTL.INVENTORY_ITEM_ID = M.INVENTORY_ITEM_ID
AND RCT.BILL_TO_CUSTOMER_ID = AC.CUSTOMER_ID
AND RCTL.SALES_ORDER = OOH.ORDER_NUMBER
AND M.ORGANIZATION_ID = 83

主要涉及到发票头行和客户行之间的关系，然后再用相应的订单查找出相应的信息

CURSOR C_LINE(x_SALES_ORDER in varchar2) IS
SELECT RCTL.SALES_ORDER ORDER_NUMBER,
            M.SEGMENT1,
            DECODE(RCTL.DESCRIPTION, '折扣', '折扣', M.DESCRIPTION) ITEM_DESC,
            DECODE(RCTL.QUANTITY_INVOICED,
NULL,
                   RCTL.QUANTITY_CREDITED,
                   RCTL.QUANTITY_INVOICED) QUANTITY,
            RCTL.UNIT_SELLING_PRICE UNIT_SELLING_PRICE
FROM RA_CUSTOMER_TRX_LINES_ALL RCTL, MTL_SYSTEM_ITEMS_B M
WHERE RCTL.INVENTORY_ITEM_ID = M.INVENTORY_ITEM_ID
AND M.ORGANIZATION_ID = 83
AND RCTL.SALES_ORDER = X_SALES_ORDER
AND EXISTS
      (SELECT 1
FROM CUX_PF_DATA_T C
WHERE C.CUSTOMER_TRX_LINE_ID = RCTL.CUSTOMER_TRX_LINE_ID)

 

1. NO3 订单报表

查询订单弹性域(值集与值集合)

SELECT T.FLEX_VALUE, T.DESCRIPTION
FROM FND_FLEX_VALUES_VL T, FND_FLEX_VALUE_SETS FFVS
WHERE T.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
AND FFVS.FLEX_VALUE_SET_NAME LIKE 'LM_ORDER_TYPE%'
AND T.FLEX_VALUE IN ('1', '2', '3', '4', '5')

这个报表中最重要的是统计发票数，首先讲符合条件的订单统计出来(里面最重要的地方是库存物料的类别，通过类别与价目表里面的类别条件相同，最后取出价格)

SELECT DISTINCT CEO.ORDER_NUMBER ORDER_NUMBER,
                     TO_CHAR(CEO.ORDER_DATE, 'YYYY-MM-DD') ORDER_DATE,
                     FFVV.DESCRIPTION DESCRIPTION_STR,
                     HCA.ACCOUNT_NUMBER SHIP_TO_ORG_ID,
                     HP.PARTY_NAME PARTY_NAME
FROM CUX_ERP_ORDERS_DELIVER CEO,
            HZ_CUST_ACCOUNTS HCA,
            HZ_PARTIES HP,
            QP_LIST_LINES_V KK,
            CUX_DMS_INVOICE_DETAIL CDID,
            (SELECT T.FLEX_VALUE, T.DESCRIPTION
FROM FND_FLEX_VALUES_VL T, FND_FLEX_VALUE_SETS FFVS
WHERE T.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
AND FFVS.FLEX_VALUE_SET_NAME LIKE 'LM_ORDER_TYPE%'
AND T.FLEX_VALUE IN ('1', '2', '3', '4', '5')) FFVV,
            MTL_SYSTEM_ITEMS_B MSIB,
            MTL_ITEM_CATEGORIES_V MIC
WHERE CEO.ORDER_TYPE = FFVV.FLEX_VALUE
AND (CEO.ATTRIBUTE7 IS NULL OR CEO.ATTRIBUTE7 = 'ORDER')
AND HCA.ACCOUNT_NUMBER = CEO.CUSTOMER_NUMBER
AND HP.PARTY_ID(+) = HCA.PARTY_ID
AND CEO.PRICE_LIST_ID <> 0
AND CEO.PRICE_LIST_ID = KK.LIST_HEADER_ID
/*
           AND CEO.ITEM_CODE = KK.PRODUCT_ATTR_VAL_DISP
           AND CEO.ORDER_NUMBER = CDID.DELIVERY_CODE
           */
AND CEO.ORDER_NUMBER = CDID.DELIVERY_CODE(+)
AND CEO.ITEM_CODE = MSIB.SEGMENT1
AND MSIB.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND MIC.CATEGORY_SET_NAME = '市场营销管理系统'
AND MSIB.ORGANIZATION_ID = P_ORG_ID
AND KK.PRODUCT_ATTR_VAL_DISP = MIC.CATEGORY_CONCAT_SEGS
ORDER BY CEO.ORDER_NUMBER;

1. 建立临时表

当我们使用的表的数据太复杂或者处理量太大，我们就建立一个临时表来处理数据

INSERT INTO CUX_PF_DATA_T
  (CUSTOMER_TRX_LINE_ID,
   AREA,
   CUSTOMER_NUM,
   PROVINCE,
   CAR_MODULE,
   QUANTITY,
   GL_DATE,
   ORDER_TYPE)
SELECT RCTL.CUSTOMER_TRX_LINE_ID,
         HCA.GLOBAL_ATTRIBUTE7 AREA,
         HCA.ACCOUNT_NUMBER CUSTOMER_CODE,
         HCA.GLOBAL_ATTRIBUTE8 PROVINCE,
         FFVV.FLEX_VALUE MODULE_CODE,
         DECODE(RCTL.QUANTITY_INVOICED,
NULL,
                RCTL.QUANTITY_CREDITED,
                RCTL.QUANTITY_INVOICED) QUANTITY,
         TO_CHAR(NVL(RCT.TRX_DATE, GD.GL_DATE), 'yyyy-mm-dd') GL_DATE,
         OTT.ATTRIBUTE1
FROM RA_CUSTOMER_TRX_ALL          RCT,
         RA_CUSTOMER_TRX_LINES_ALL    RCTL,
         RA_CUST_TRX_LINE_GL_DIST_ALL GD,
         OE_ORDER_HEADERS_ALL         OOH,
         OE_TRANSACTION_TYPES_ALL     OTT,
         HZ_CUST_ACCOUNTS             HCA,
         MTL_ITEM_CATEGORIES_V        MICV,
         FND_FLEX_VALUES_VL           FFVV,
         FND_FLEX_VALUE_SETS          FFVS
WHERE RCTL.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID
AND RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID
AND RCT.BATCH_SOURCE_ID = 1002 --销售订单导入
AND GD.ACCOUNT_CLASS = 'REC'
AND GD.LATEST_REC_FLAG = 'Y'
AND RCTL.SALES_ORDER IS NOT NULL
AND RCTL.DESCRIPTION <> '折扣'
AND RCTL.SALES_ORDER = OOH.ORDER_NUMBER
AND OOH.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID
AND HCA.CUST_ACCOUNT_ID = RCT.BILL_TO_CUSTOMER_ID
AND RCTL.INVENTORY_ITEM_ID = MICV.INVENTORY_ITEM_ID
AND MICV.ORGANIZATION_ID = G_ORGANIZATION_ID
AND MICV.CATEGORY_SET_NAME = '市场营销管理系统'
AND MICV.SEGMENT1 = FFVV.FLEX_VALUE
AND FFVV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
AND FFVS.FLEX_VALUE_SET_NAME = 'LM_OM_CATEGORY_1'
AND TRUNC(NVL(FFVV.START_DATE_ACTIVE, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(FFVV.END_DATE_ACTIVE, SYSDATE)) >= TRUNC(SYSDATE)
AND RCT.ATTRIBUTE11 IS NULL
AND EXISTS (SELECT 1
FROM MTL_SYSTEM_ITEMS_B M
WHERE M.ORGANIZATION_ID = G_ORGANIZATION_ID
AND M.INVENTORY_ITEM_ID = RCTL.INVENTORY_ITEM_ID
AND M.ITEM_TYPE = 'FG')
AND EXISTS (SELECT 1
FROM AR_GTA_GT_HEADERS_V AGH
WHERE AGH.RA_TRX_ID = RCTL.CUSTOMER_TRX_ID)
AND NOT EXISTS
   (SELECT 1
FROM CUX_PF_DATA_T R
WHERE RCTL.CUSTOMER_TRX_LINE_ID = R.CUSTOMER_TRX_LINE_ID)
UNION ALL
  ---手工
SELECT RCTL.CUSTOMER_TRX_LINE_ID,
         HCA.GLOBAL_ATTRIBUTE7 AREA,
         HCA.ACCOUNT_NUMBER CUSTOMER_CODE,
         HCA.GLOBAL_ATTRIBUTE8 PROVINCE,
         FFVV.FLEX_VALUE MODULE_CODE,
         DECODE(RCTL.QUANTITY_INVOICED,
NULL,
                RCTL.QUANTITY_CREDITED,
                RCTL.QUANTITY_INVOICED) QUANTITY,
         TO_CHAR(NVL(RCT.TRX_DATE, GD.GL_DATE), 'yyyy-mm-dd') GL_DATE,
         OTT.ATTRIBUTE1
FROM RA_CUSTOMER_TRX_ALL          RCT,
         RA_CUSTOMER_TRX_LINES_ALL    RCTL,
         RA_CUST_TRX_LINE_GL_DIST_ALL GD,
         OE_ORDER_HEADERS_ALL         OOH,
         OE_TRANSACTION_TYPES_ALL     OTT,
         HZ_CUST_ACCOUNTS             HCA,
         MTL_ITEM_CATEGORIES_V        MICV,
         FND_FLEX_VALUES_VL           FFVV,
         FND_FLEX_VALUE_SETS          FFVS
WHERE RCTL.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID
AND RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID
AND RCT.BATCH_SOURCE_ID = 1002 --销售订单导入
AND GD.ACCOUNT_CLASS = 'REC'
AND GD.LATEST_REC_FLAG = 'Y'
AND RCTL.SALES_ORDER IS NOT NULL
AND RCTL.DESCRIPTION <> '折扣'
AND RCTL.SALES_ORDER = OOH.ORDER_NUMBER
AND OOH.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID
AND HCA.CUST_ACCOUNT_ID = RCT.BILL_TO_CUSTOMER_ID
AND RCTL.INVENTORY_ITEM_ID = MICV.INVENTORY_ITEM_ID
AND MICV.ORGANIZATION_ID = G_ORGANIZATION_ID
AND MICV.CATEGORY_SET_NAME = '市场营销管理系统'
AND MICV.SEGMENT1 = FFVV.FLEX_VALUE
AND FFVV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
AND FFVS.FLEX_VALUE_SET_NAME = 'LM_OM_CATEGORY_1'
AND TRUNC(NVL(FFVV.START_DATE_ACTIVE, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(FFVV.END_DATE_ACTIVE, SYSDATE)) >= TRUNC(SYSDATE)
AND RCT.ATTRIBUTE11 IS NOT NULL
AND EXISTS (SELECT 1
FROM MTL_SYSTEM_ITEMS_B M
WHERE M.ORGANIZATION_ID = G_ORGANIZATION_ID
AND M.INVENTORY_ITEM_ID = RCTL.INVENTORY_ITEM_ID
AND M.ITEM_TYPE = 'FG')
AND NOT EXISTS
   (SELECT 1
FROM CUX_PF_DATA_T R
WHERE RCTL.CUSTOMER_TRX_LINE_ID = R.CUSTOMER_TRX_LINE_ID);

其中分为两段，第一段为系统自动开票，第二段为手动开票

转载于:https://www.cnblogs.com/SanFrans/p/3261738.html