/* Formatted on 2014-9-25 17:51:37 (QP5 v5.115.810.9015) */
DECLARE
V_LINES_REC RA_INTERFACE_LINES_ALL%ROWTYPE;
V_SALESCREDITS_REC RA_INTERFACE_SALESCREDITS_ALL%ROWTYPE;
V_INTERFACE_LINE_ID NUMBER;
V_PRIMARY_SALESREP_ID NUMBER;
V_SET_OF_BOOKS_ID NUMBER;
V_CURRENCY_CODE VARCHAR2(30);
V_SOB_CURRENCY_CODE VARCHAR2(30);
V_PAYMENT_TERM_ID NUMBER := NULL;
V_CUST_TRX_TYPE_ID NUMBER;
V_INVOICE_TO_ORG_ID NUMBER;
V_SHIP_TO_ORG_ID NUMBER;
-----------------------------
V_BILL_CUSTOMER_ID NUMBER;
V_BILL_ADDRESS_ID NUMBER;
V_SHIP_CUSTOMER_ID NUMBER;
V_SHIP_ADDRESS_ID NUMBER;
V_TRANSACTION_TYPE_ID NUMBER; --订单类型
V_DELIVERY_NAME VARCHAR2(100) := '644241';
P_ORG_ID NUMBER :=188 ;
P_CUSTOMER_ID NUMBER := 1618336;
P_SHIP_HEADER_ID NUMBER :=41220 ;
P_SEND_NUM VARCHAR2(200):='FSQ11110010' ; --客制化的发货单
P_ORDER_HEADER_ID NUMBER:= 615534;
P_ORDER_HEADER_NUMBER VARCHAR2(240):='931211110003' ;
P_DELIVERY_ID NUMBER := 644241 ;
P_ACTUAL_DEPARTURE_DATE DATE := SYSDATE ;
P_AMOUNT NUMBER := 2000;
P_TYPE NUMBER :=1 ;--发票类型 1 代表运保费 ,2 代表 其它应收 --交货
L_PROCESS_PHASE NUMBER ;
---全局参数
V_USER_ID NUMBER := FND_GLOBAL.USER_ID;
--User ID, Sysadmin here
V_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
x_retcode NUMBER ;
x_errbuf VARCHAR2(2000);
BEGIN
mo_global.set_policy_context('S',P_ORG_ID);
V_primary_salesrep_id:=-3;
--查看交货名称
BEGIN
SELECT NAME
INTO V_DELIVERY_NAME
FROM WSH_NEW_DELIVERIES
WHERE DELIVERY_ID=P_DELIVERY_ID;
EXCEPTION
WHEN OTHERS THEN
x_retcode:=2;
x_errbuf:='查找交货名称失败';
DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
RETURN;
END;
--查找帐本ID
BEGIN
SELECT SET_OF_BOOKS_ID
INTO V_SET_OF_BOOKS_ID
FROM hr_operating_units HOU
WHERE HOU.ORGANIZATION_ID=P_ORG_ID;
EXCEPTION
WHEN OTHERS THEN
x_retcode:=2;
x_errbuf:='查找帐本ID时失败';
DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
RETURN;
END;
L_PROCESS_PHASE:=1;
--查找本位币
BEGIN
SELECT CURRENCY_CODE
INTO V_SOB_CURRENCY_CODE
FROM GL_LEDGERS
WHERE LEDGER_ID=V_SET_OF_BOOKS_ID;
EXCEPTION
WHEN OTHERS THEN
x_retcode:=2;
x_errbuf:='查找本位币时失败';
DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
RETURN;
END;
L_PROCESS_PHASE:=2;
--查找订单币种
BEGIN
SELECT TRANSACTIONAL_CURR_CODE,PAYMENT_TERM_ID,INVOICE_TO_ORG_ID,SHIP_TO_ORG_ID,ORDER_TYPE_ID
INTO V_CURRENCY_CODE,V_PAYMENT_TERM_ID,V_INVOICE_TO_ORG_ID,V_SHIP_TO_ORG_ID,V_TRANSACTION_TYPE_ID
FROM OE_ORDER_HEADERS_ALL
WHERE HEADER_ID=P_ORDER_HEADER_ID;
EXCEPTION
WHEN OTHERS THEN
x_retcode:=2;
x_errbuf:='查找订单时失败';
DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
RETURN;
END;
L_PROCESS_PHASE:=3;
IF V_INVOICE_TO_ORG_ID IS NULL THEN
x_retcode:=2;
x_errbuf:='查找收单地点为空';
DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
RETURN;
END IF;
IF V_SHIP_TO_ORG_ID IS NULL THEN
x_retcode:=2;
x_errbuf:='查找收货地点为空';
DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
RETURN;
END IF;
L_PROCESS_PHASE:=4;
--查找交易类型
IF P_TYPE=1 THEN
--运保费的,事务处理类型见配置
L_PROCESS_PHASE:=4.1;
BEGIN
SELECT CUST_TRX_TYPE_ID
INTO V_CUST_TRX_TYPE_ID
FROM RA_CUST_TRX_TYPES
WHERE NAME= ----更改快速编码,运费发票的事务处理类型的抓取逻辑:OU_ID&交易类型 by sam.t 2012.03.31
(SELECT XYG_PUB_COMMON_PKG.GET_CHAR_POSITION_CONTENT(MEANING,'&',2)--MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE='XYG_FREIGHT_TRANSACTION_TYPE'
AND LOOKUP_CODE=P_ORG_ID
AND ENABLED_FLAG='Y'
AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE+0.99999,SYSDATE+1)
AND ROWNUM=1)
AND ORG_ID=P_ORG_ID;
EXCEPTION
WHEN OTHERS THEN
x_retcode:=2;
x_errbuf:='查找交易类型时失败';
DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
RETURN;
END;
ELSIF P_TYPE=2 THEN
--其它应收的,事务处理类型同 原票的事务类型
L_PROCESS_PHASE:=4.2;
BEGIN
SELECT CUST_TRX_TYPE_ID
INTO V_CUST_TRX_TYPE_ID
FROM OE_TRANSACTION_TYPES_ALL
WHERE TRANSACTION_TYPE_ID=V_TRANSACTION_TYPE_ID;
EXCEPTION
WHEN OTHERS THEN
x_retcode:=2;
x_errbuf:='查找交易类型时失败';
DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
RETURN;
END;
END IF;
L_PROCESS_PHASE:=4.3;
IF V_CUST_TRX_TYPE_ID IS NULL THEN
x_retcode:=2;
x_errbuf:='查找交易类型为空';
DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
RETURN;
END IF;
L_PROCESS_PHASE:=5;
--查找BILL_ADDRESS_ID
BEGIN
SELECT SITE.CUST_ACCOUNT_ID
,SITE.CUST_ACCT_SITE_ID
INTO V_BILL_CUSTOMER_ID
,V_BILL_ADDRESS_ID
FROM HZ_CUST_SITE_USES_ALL USES
,HZ_CUST_ACCT_SITES_ALL SITE
WHERE USES.SITE_USE_ID=V_INVOICE_TO_ORG_ID
AND SITE.CUST_ACCT_SITE_ID=USES.CUST_ACCT_SITE_ID;
EXCEPTION
WHEN OTHERS THEN
x_retcode:=2;
x_errbuf:='查找收单地点失败';
DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
RETURN;
END;
L_PROCESS_PHASE:=6;
IF V_BILL_CUSTOMER_ID IS NULL OR V_BILL_ADDRESS_ID IS NULL THEN
x_retcode:=2;
x_errbuf:='查找收单方失败';
DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
RETURN;
END IF;
L_PROCESS_PHASE:=7;
--查找SHIP_TO_ORG_ID
BEGIN
SELECT SITE.CUST_ACCOUNT_ID
,SITE.CUST_ACCT_SITE_ID
INTO V_SHIP_CUSTOMER_ID
,V_SHIP_ADDRESS_ID
FROM HZ_CUST_SITE_USES_ALL USES
,HZ_CUST_ACCT_SITES_ALL SITE
WHERE USES.SITE_USE_ID=V_SHIP_TO_ORG_ID
AND SITE.CUST_ACCT_SITE_ID=USES.CUST_ACCT_SITE_ID;
EXCEPTION
WHEN OTHERS THEN
x_retcode:=2;
x_errbuf:='查找收货地点失败';
DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
RETURN;
END;
IF V_SHIP_CUSTOMER_ID IS NULL OR V_SHIP_ADDRESS_ID IS NULL THEN
x_retcode:=2;
x_errbuf:='查找收货方失败';
DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
RETURN;
END IF;
L_PROCESS_PHASE:=8;
SELECT RA_CUSTOMER_TRX_LINES_S.NEXTVAL INTO V_INTERFACE_LINE_ID FROM DUAL;
V_LINES_REC.INTERFACE_LINE_ID := V_INTERFACE_LINE_ID;
V_LINES_REC.ORG_ID := P_ORG_ID;
V_LINES_REC.INTERFACE_LINE_CONTEXT := 'SPLIT_FREIGHT';
V_LINES_REC.INTERFACE_LINE_ATTRIBUTE1 := P_ORDER_HEADER_NUMBER;
V_LINES_REC.INTERFACE_LINE_ATTRIBUTE2 := P_SHIP_HEADER_ID;
V_LINES_REC.INTERFACE_LINE_ATTRIBUTE3 := V_DELIVERY_NAME;
V_LINES_REC.INTERFACE_LINE_ATTRIBUTE4 := P_SEND_NUM;
V_LINES_REC.INTERFACE_LINE_ATTRIBUTE5 := P_TYPE;
V_LINES_REC.BATCH_SOURCE_NAME := 'OM导入发票1';
V_LINES_REC.SET_OF_BOOKS_ID := V_SET_OF_BOOKS_ID;
V_LINES_REC.LINE_TYPE := 'LINE';
SELECT DECODE(P_TYPE, 1, '运保费', '其它应收')
INTO V_LINES_REC.DESCRIPTION
FROM DUAL;
V_LINES_REC.CURRENCY_CODE := V_CURRENCY_CODE;
V_LINES_REC.AMOUNT := P_AMOUNT;
V_LINES_REC.CUST_TRX_TYPE_ID := V_CUST_TRX_TYPE_ID;
V_LINES_REC.TERM_ID := V_PAYMENT_TERM_ID;
V_LINES_REC.ORIG_SYSTEM_BILL_CUSTOMER_ID := V_BILL_CUSTOMER_ID;
V_LINES_REC.ORIG_SYSTEM_BILL_ADDRESS_ID := V_BILL_ADDRESS_ID;
V_LINES_REC.ORIG_SYSTEM_SHIP_CUSTOMER_ID := V_SHIP_CUSTOMER_ID;
V_LINES_REC.ORIG_SYSTEM_SHIP_ADDRESS_ID := V_SHIP_ADDRESS_ID;
V_LINES_REC.ORIG_SYSTEM_SOLD_CUSTOMER_ID := P_CUSTOMER_ID;
IF V_CURRENCY_CODE = V_SOB_CURRENCY_CODE THEN
V_LINES_REC.CONVERSION_TYPE := 'User';
V_LINES_REC.CONVERSION_DATE := NULL;
V_LINES_REC.CONVERSION_RATE := 1;
ELSE
V_LINES_REC.CONVERSION_TYPE := 'Corporate';
V_LINES_REC.CONVERSION_DATE := NULL;
V_LINES_REC.CONVERSION_RATE := NULL;
END IF;
--5个 who
V_LINES_REC.CREATED_BY := V_USER_ID;
V_LINES_REC.CREATION_DATE :=SYSDATE ;
V_LINES_REC.LAST_UPDATED_BY:= V_USER_ID ;
V_LINES_REC.LAST_UPDATE_DATE:= SYSDATE;
V_LINES_REC.LAST_UPDATE_LOGIN := V_LOGIN_ID ;
V_LINES_REC.PRIMARY_SALESREP_ID := V_PRIMARY_SALESREP_ID;
--V_LINES_REC.TAX_CODE := TRIM(P_VAT_CODE);
V_LINES_REC.TRX_DATE := P_ACTUAL_DEPARTURE_DATE;
V_LINES_REC.GL_DATE := P_ACTUAL_DEPARTURE_DATE;
V_LINES_REC.SALES_ORDER := P_ORDER_HEADER_NUMBER || '/' || P_SEND_NUM;
V_LINES_REC.TRANSLATED_DESCRIPTION := P_SEND_NUM;
V_SALESCREDITS_REC.INTERFACE_LINE_ID := V_INTERFACE_LINE_ID;
V_SALESCREDITS_REC.ORG_ID := P_ORG_ID;
V_SALESCREDITS_REC.INTERFACE_LINE_CONTEXT := 'SPLIT_FREIGHT';
V_SALESCREDITS_REC.INTERFACE_LINE_ATTRIBUTE1 := P_ORDER_HEADER_NUMBER;
V_SALESCREDITS_REC.INTERFACE_LINE_ATTRIBUTE2 := P_SHIP_HEADER_ID;
V_SALESCREDITS_REC.INTERFACE_LINE_ATTRIBUTE3 := V_DELIVERY_NAME;
V_SALESCREDITS_REC.INTERFACE_LINE_ATTRIBUTE4 := P_SEND_NUM;
V_SALESCREDITS_REC.INTERFACE_LINE_ATTRIBUTE5 := P_TYPE;
V_SALESCREDITS_REC.SALES_CREDIT_TYPE_ID := 1;
V_SALESCREDITS_REC.SALESREP_ID := V_PRIMARY_SALESREP_ID;
V_SALESCREDITS_REC.SALES_CREDIT_PERCENT_SPLIT := 100;
--5个 who
V_SALESCREDITS_REC.CREATED_BY := V_USER_ID;
V_SALESCREDITS_REC.CREATION_DATE :=SYSDATE ;
V_SALESCREDITS_REC.LAST_UPDATED_BY:= V_USER_ID ;
V_SALESCREDITS_REC.LAST_UPDATE_DATE:= SYSDATE;
V_SALESCREDITS_REC.LAST_UPDATE_LOGIN := V_LOGIN_ID ;
L_PROCESS_PHASE:=9;
INSERT INTO RA_INTERFACE_LINES_ALL
VALUES V_LINES_REC;
INSERT INTO RA_INTERFACE_SALESCREDITS_ALL
VALUES V_SALESCREDITS_REC;
L_PROCESS_PHASE:=10;
DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
COMMIT;
END;
/*
在查找类型的时候,有定义了一个快速编码 XYG_FREIGHT_TRANSACTION_TYPE
里面用OU_ID&交易类型 进行维护数据
*/