上个月报表开发总结
最近我上个月开发的几个报表要收工，描述一下在开发过程中，遇到的主要问题：
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;
- 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'
- 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)
- 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;
- 建立临时表
当我们使用的表的数据太复杂或者处理量太大，我们就建立一个临时表来处理数据
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);其中分为两段，第一段为系统自动开票，第二段为手动开票
