出貨不成功,出貨介面為shipped, 部分訂單為booked的處理過程.(有修改)

問題:
delivery number:2357335 ,FW2CC008CP-GTA1 /100PCS 至今還是SHIPPED狀態，銷貨不成功。

用如下sql查

[@more@]

問題:
delivery number:2357335 ,FW2CC008CP-GTA1 /100PCS 至今還是SHIPPED狀態，銷貨不成功。

步驟一.

用如下sql查

--**** CHECK status
--依delivery_id 查出貨後status是否正常,如有沒有拋ar,inv_iterfaeced等.
--

begin
dbms_application_info.set_client_info('2');
end;

select wda.delivery_id,wi.item_key,
wdd.source_header_number,
oola.line_id, oola.FLOW_STATUS_CODE status,
msib.segment1,
wdd.shipped_quantity ship_qty,
oola.shipped_quantity,
oola.actual_shipment_date,
oola.INVOICE_INTERFACE_STATUS_CODE,
oola.line_number||'.'||oola.shipment_number line_no,
wdd.organization_id,
wdd.OE_INTERFACED_FLAG,
wdd.INV_INTERFACED_FLAG,
mmt.TRANSACTION_QUANTITY,
mmt.PRIMARY_QUANTITY,
mmt.TRANSACTION_DATE
from
MTL_MATERIAL_TRANSACTIONS mmt,
wsh_delivery_details wdd,
mtl_system_items_b msib,
oe_order_lines_all oola,
WF_ITEMS WI,
wsh_delivery_assignments wda
where 1=1
and mmt.TRANSACTION_TYPE_ID(+)=33
and mmt.SOURCE_CODE(+)='ORDER ENTRY'
and mmt.SOURCE_LINE_ID(+)=oola.line_id
and mmt.INVENTORY_ITEM_ID(+)=oola.inventory_item_id
and wdd.org_id=oola.org_id
and wda.delivery_detail_id = wdd.delivery_detail_id
and msib.organization_id=oola.org_id
and oola.org_id=203
and oola.inventory_item_id=msib.inventory_item_id
and wdd.source_header_id=oola.header_id
and wdd.source_line_id=oola.line_id
and to_char(oola.line_id)=wi.item_key(+)
and wda.delivery_id IN (2392780,2392719,2406677)

--****查得 Order Line 2732483:BOOKED OE_INTERFACE_FLA='N'

如TRANSACTION_QUANTITY為null,則說明還沒有扣帳的

INV_INTERFACE_FLA='N'
(INV 未扣帳, AR 未拋)
依這個line找訂單3140010656, FW2CC008CP-GTA1 有workflow,
但line的workflow有error,且看不了流程圖.

步驟二.

處理方法1:

在訂單line裡點右鍵,progress order/ship. 提示成功
再查workflow,沒有error了.

接著
在om裡run:
1,Order Management Interface - SRS
2,Inventory Interface - SRS

都正常完成.

再查出貨介面,shipped已變成infterfaced

處理方法2:

當方法1處理不成功時,使用此方法

即找一筆類似的workflow取代

declare
/*
1.確認 HEADER WORKFLOW IS Close - Wait For Line
2.確認 LINE WORKFLOW IS Create Supply Order - Eligible ERROR
3.單身為picked
要替換的有 order_line_id,order_number,order_line_number
7190010283
2790529
Line 6.2
'ORG_ID',NULL,3
*/
begin
/*

delete wf_items where item_key='2790529'

delete WF_ITEM_ATTRIBUTE_VALUES where item_key='2790529'

delete WF_ITEM_ACTIVITY_STATUSES where item_key='2790529'

*/
INSERT INTO wf_items
VALUES ('OEOL','2790529','R_STANDARD_LINE',29,'','OEOH','603598',NULL,TO_date('2006/07/01','yyyy/mm/dd'),NULL,'Sales Order 7190010283 , Line 1.1..',NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','#SCHEMA','APPS',NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','.ADMIN_KEY','3007420415',NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','.MONITOR_KEY','2306600295',NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','AFAS_LINE_ID',NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','APPLICATION_ID',NULL,660,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','DELIVERY_ID',NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','GOBO_OM_EMAIL_BODY',NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','GOBO_OM_EMAIL_HEADER',NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','GOBO_OM_RECIPIENT',NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','LEAD_TIME_CATEGORY_STRUCTURE',NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','LEAD_TIME_DATE',NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','LEAD_TIME_REQUEST_ID',NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','LEAD_TIME_ROLLUP_ITEM',NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','LEAD_TIME_ROLLUP_ORG',NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','LINE_CATEGORY','ORDER',NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','LIN_SHORT_DESCRIPTOR','plsql:OE_ORDER_WF_UTIL.Set_Line_Descriptor/'||chr(38)||'#NID',NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','LT_ATTR_REQ_ID','LEAD_TIME_REQUEST_ID',NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','MESSAGE_TYPE',NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','NOTIFICATION_APPROVER','SYSADMIN',NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','NOTIFICATION_FROM_ROLE',NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','ORDER_CATEGORY',NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','ORDER_NUMBER',NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','ORG_ID',NULL,2,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','PURCHASE_ORDER',NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','RESPONSIBILITY_ID',NULL,50114,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','SITE_USE_CODE',NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','USAGE_CODE',NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','USER_ID',NULL,12926,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES
VALUES ('OEOL','2790529','WF_ADMINISTRATOR','SYSADMIN',NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ACTIVITY_STATUSES
VALUES ('OEOL','2790529',13903,'ACTIVE','#NULL',NULL,NULL,to_date('05-12-2006','dd-mm-YYYY'),NULL,9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ACTIVITY_STATUSES
VALUES ('OEOL','2790529',134746,'COMPLETE','STOCK',NULL,NULL,to_date('05-12-2006','dd-mm-YYYY'),to_date('05-12-2006','dd-mm-YYYY'),30,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ACTIVITY_STATUSES
VALUES ('OEOL','2790529',134751,'COMPLETE','#NULL',NULL,NULL,to_date('05-12-2006','dd-mm-YYYY'),to_date('05-12-2006','dd-mm-YYYY'),32,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ACTIVITY_STATUSES
VALUES ('OEOL','2790529',134826,'COMPLETE','#NULL',NULL,NULL,to_date('05-12-2006','dd-mm-YYYY'),to_date('05-12-2006','dd-mm-YYYY'),11,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ACTIVITY_STATUSES
VALUES ('OEOL','2790529',134828,'COMPLETE','#NULL',NULL,NULL,to_date('05-12-2006','dd-mm-YYYY'),to_date('05-12-2006','dd-mm-YYYY'),18,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ACTIVITY_STATUSES
VALUES ('OEOL','2790529',135186,'COMPLETE',NULL,NULL,NULL,to_date('05-12-2006','dd-mm-YYYY'),to_date('05-12-2006','dd-mm-YYYY'),29,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ACTIVITY_STATUSES
VALUES ('OEOL','2790529',135191,'COMPLETE',NULL,NULL,NULL,to_date('05-12-2006','dd-mm-YYYY'),to_date('05-12-2006','dd-mm-YYYY'),10,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ACTIVITY_STATUSES
VALUES ('OEOL','2790529',135195,'COMPLETE',NULL,NULL,NULL,to_date('05-12-2006','dd-mm-YYYY'),to_date('05-12-2006','dd-mm-YYYY'),21,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ACTIVITY_STATUSES
VALUES ('OEOL','2790529',135196,'ACTIVE',NULL,NULL,NULL,to_date('05-12-2006','dd-mm-YYYY'),NULL,35,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ACTIVITY_STATUSES
VALUES ('OEOL','2790529',135299,'COMPLETE','#NULL',NULL,NULL,to_date('05-12-2006','dd-mm-YYYY'),to_date('05-12-2006','dd-mm-YYYY'),26,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ACTIVITY_STATUSES
VALUES ('OEOL','2790529',135301,'COMPLETE','COMPLETE',NULL,NULL,to_date('05-12-2006','dd-mm-YYYY'),to_date('05-12-2006','dd-mm-YYYY'),24,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ACTIVITY_STATUSES
VALUES ('OEOL','2790529',135305,'COMPLETE','#NULL',NULL,NULL,to_date('05-12-2006','dd-mm-YYYY'),to_date('05-12-2006','dd-mm-YYYY'),22,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO WF_ITEM_ACTIVITY_STATUSES
VALUES ('OEOL','2790529',135315,'NOTIFIED',NULL,NULL,NULL,to_date('05-12-2006','dd-mm-YYYY'),NULL,36,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
commit;
end;
/
接著在om裡run:
1,Order Management Interface - SRS
2,Inventory Interface - SRS

步驟三.

但原為book的這筆line在訂單單身裡狀態為shipped,是因為沒有拋ar.

繼續處理此line的ar

/*
--查有沒有拋轉ar的記錄
SELECT invoice_interface_status_code
FROM oe_order_lines Line
WHERE Line.line_id = 2732483
AND invoice_interface_status_code = 'RFR-PENDING'

沒有記錄

update oe_order_lines Line

set invoice_interface_status_code = 'RFR-PENDING'

WHERE Line.line_id = 2732483

commit;

這樣子在下面的拋ar才會成功

*/

declare
l_result_out VARCHAR2(30);
l_return_status VARCHAR2(30);

cursor c1 is

SELECT line_id
FROM OE_ORDER_LINES
where line_id=2732483;

--line_id in (2704842,2750637);
--WHERE FLOW_STATUS_CODE = 'SHIPPED';

begin

/*
select user_id from fnd_user
where user_name='LONGSON.LONG'
*/

for ii in c1 loop

--用系統的功能,補拋ar.

Oe_Invoice_Pub.Interface_Line(ii.line_id,'OEOL',l_result_out,l_return_status);

dbms_output.put_line(to_char(ii.line_id) ||'+'||l_result_out || '+' || l_return_status);

--變更訂單line的狀態
UPDATE OE_ORDER_LINES
SET OPEN_FLAG='N',
FLOW_STATUS_CODE='CLOSED'
WHERE LINE_ID = ii.line_id;

/*
--除了gusz,gify,guhw之外要執行如下的.
UPDATE RA_INTERFACE_LINES_ALL RI
SET TRX_NUMBER =
(SELECT DISTINCT DELIVERY_ID
FROM WSH_DELIVERABLES_V WSH
WHERE WSH.SOURCE_HEADER_NUMBER = RI.SALES_ORDER
AND WSH.SOURCE_LINE_ID = RI.INTERFACE_LINE_ATTRIBUTE6
)
WHERE ORG_ID in (37,57,98,118,158,331,222)
AND TRX_NUMBER IS NULL;
*/

Update OE_ORDER_LINES ol
set ol.shipped_quantity=ol.ordered_quantity
where ol.line_id=ii.line_id
and ol.shipped_quantity is null;

commit;

end loop;

end;

--至此處理完畢.