Home » Applications » Oracle Fusion Apps & E-Business Suite » Order Line update with API (OE_ORDER_PUB) (ebs r12)
Order Line update with API (OE_ORDER_PUB) [message #657501] Thu, 10 November 2016 06:28
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

hello experts

I have a situation , I need to update Oe_order_lines_all from standard API. First of all i am confuse between
OE_ORDER_PUB.PROCESS_ORDER and OE_ORDER_PUB.UPDATE_LINE
first of all which one API I use to update a single line of specific order. Secondly i want to update LINE_TYPE_ID on such conditions
for example
i have some transaction types defined specific with OU and org_id

1649	1572	BCL Nestle Soc Pwd RTN Line100
1649	1573	BCL Nestle Soc Pwd RTN Line 50
1650	1576	BCL Nestle Soc Lqd RTN Line100
1650	1577	BCL Nestle Soc Lqd RTN Line 50

1572-1577 are line_type_ids

now see my code
{code}
set Serveroutput On
DECLARE
L_USER_ID NUMBER;
L_RESP_ID NUMBER;
L_APPL_ID NUMBER;
L_HEADER_REC_IN OE_ORDER_PUB.HEADER_REC_TYPE;
L_LINE_TBL_IN OE_ORDER_PUB.LINE_TBL_TYPE;
L_ACTION_REQUEST_TBL_IN OE_ORDER_PUB.REQUEST_TBL_TYPE;
L_HEADER_REC_OUT OE_ORDER_PUB.HEADER_REC_TYPE;
L_LINE_TBL_OUT OE_ORDER_PUB.LINE_TBL_TYPE;
L_HEADER_VAL_REC_OUT OE_ORDER_PUB.HEADER_VAL_REC_TYPE;
L_HEADER_ADJ_TBL_OUT OE_ORDER_PUB.HEADER_ADJ_TBL_TYPE;
L_HEADER_ADJ_VAL_TBL_OUT OE_ORDER_PUB.HEADER_ADJ_VAL_TBL_TYPE;
L_HEADER_PRICE_ATT_TBL_OUT OE_ORDER_PUB.HEADER_PRICE_ATT_TBL_TYPE;
L_HEADER_ADJ_ATT_TBL_OUT OE_ORDER_PUB.HEADER_ADJ_ATT_TBL_TYPE;
L_HEADER_ADJ_ASSOC_TBL_OUT OE_ORDER_PUB.HEADER_ADJ_ASSOC_TBL_TYPE;
L_HEADER_SCREDIT_TBL_OUT OE_ORDER_PUB.HEADER_SCREDIT_TBL_TYPE;
L_HEADER_SCREDIT_VAL_TBL_OUT OE_ORDER_PUB.HEADER_SCREDIT_VAL_TBL_TYPE;
L_LINE_VAL_TBL_OUT OE_ORDER_PUB.LINE_VAL_TBL_TYPE;
L_LINE_ADJ_TBL_OUT OE_ORDER_PUB.LINE_ADJ_TBL_TYPE;
L_LINE_ADJ_VAL_TBL_OUT OE_ORDER_PUB.LINE_ADJ_VAL_TBL_TYPE;
L_LINE_PRICE_ATT_TBL_OUT OE_ORDER_PUB.LINE_PRICE_ATT_TBL_TYPE;
L_LINE_ADJ_ATT_TBL_OUT OE_ORDER_PUB.LINE_ADJ_ATT_TBL_TYPE;
L_LINE_ADJ_ASSOC_TBL_OUT OE_ORDER_PUB.LINE_ADJ_ASSOC_TBL_TYPE;
L_LINE_SCREDIT_TBL_OUT OE_ORDER_PUB.LINE_SCREDIT_TBL_TYPE;
L_LINE_SCREDIT_VAL_TBL_OUT OE_ORDER_PUB.LINE_SCREDIT_VAL_TBL_TYPE;
L_LOT_SERIAL_TBL_OUT OE_ORDER_PUB.LOT_SERIAL_TBL_TYPE;
L_LOT_SERIAL_VAL_TBL_OUT OE_ORDER_PUB.LOT_SERIAL_VAL_TBL_TYPE;
L_ACTION_REQUEST_TBL_OUT OE_ORDER_PUB.REQUEST_TBL_TYPE;
L_CHR_PROGRAM_UNIT_NAME VARCHAR2 (100);
L_CHR_RET_STATUS VARCHAR2 (1000) := NULL;
L_MSG_COUNT NUMBER := 0;
L_MSG_DATA VARCHAR2 (2000);
L_NUM_API_VERSION NUMBER := 1.0;
CURSOR C_SO_DETAILS
IS
SELECT OH.HEADER_ID ,
OL.LINE_ID ,
OH.ORDER_NUMBER AS SO_NUMBER ,
MSI.SEGMENT1 ,
OL.LINE_NUMBER
|| '.'
|| OL.SHIPMENT_NUMBER AS LINE ,
OL.INVENTORY_ITEM_ID
FROM OE_ORDER_HEADERS_ALL OH ,
OE_ORDER_LINES_ALL OL ,
MTL_SYSTEM_ITEMS MSI
WHERE 0 = 0
AND OH.HEADER_ID = OL.HEADER_ID
AND OL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = OL.SHIP_FROM_ORG_ID
AND OL.LINE_ID = 28368271 ;
BEGIN
SELECT USER_ID INTO L_USER_ID FROM FND_USER WHERE USER_NAME = 'ANWAR.ALI';
SELECT RESPONSIBILITY_ID ,
APPLICATION_ID
INTO L_RESP_ID ,
L_APPL_ID
FROM FND_RESPONSIBILITY_VL
WHERE RESPONSIBILITY_NAME LIKE 'Order Management Super User' ;
FND_GLOBAL.APPS_INITIALIZE (L_USER_ID, L_RESP_ID, L_APPL_ID);
FOR ISO_REC IN C_SO_DETAILS
LOOP
L_LINE_TBL_IN (1) := OE_ORDER_PUB.G_MISS_LINE_REC;
L_LINE_TBL_IN (1).LINE_ID := ISO_REC.LINE_ID;
---- L_LINE_TBL_IN (1).SCHEDULE_SHIP_DATE := SYSDATE; --TO_DATE(ISO_REC.UPDATE_SSD,'DD-MON-RRRR HH24:MI:SS');
L_LINE_TBL_IN (1).LAST_UPDATE_DATE := SYSDATE; --TO_DATE(ISO_REC.UPDATE_SSD,'DD-MON-RRRR HH24:MI:SS');
L_LINE_TBL_IN (1).OPERATION := OE_GLOBALS.G_OPR_UPDATE;
L_LINE_TBL_IN (1).line_type_id := 1023;
--- L_LINE_TBL_IN (1).return_reason_code := 'Test';
L_LINE_TBL_IN (1).change_reason := 'Not provided';

OE_MSG_PUB.DELETE_MSG;
OE_ORDER_PUB.PROCESS_ORDER ( P_API_VERSION_NUMBER => L_NUM_API_VERSION , P_INIT_MSG_LIST => FND_API.G_FALSE , P_RETURN_VALUES => FND_API.G_FALSE , P_ACTION_COMMIT => FND_API.G_FALSE , P_LINE_TBL => L_LINE_TBL_IN , X_HEADER_REC => L_HEADER_REC_OUT , X_HEADER_VAL_REC => L_HEADER_VAL_REC_OUT , X_HEADER_ADJ_TBL => L_HEADER_ADJ_TBL_OUT , X_HEADER_ADJ_VAL_TBL => L_HEADER_ADJ_VAL_TBL_OUT , X_HEADER_PRICE_ATT_TBL => L_HEADER_PRICE_ATT_TBL_OUT , X_HEADER_ADJ_ATT_TBL => L_HEADER_ADJ_ATT_TBL_OUT , X_HEADER_ADJ_ASSOC_TBL => L_HEADER_ADJ_ASSOC_TBL_OUT , X_HEADER_SCREDIT_TBL => L_HEADER_SCREDIT_TBL_OUT , X_HEADER_SCREDIT_VAL_TBL => L_HEADER_SCREDIT_VAL_TBL_OUT , X_LINE_TBL => L_LINE_TBL_OUT , X_LINE_VAL_TBL => L_LINE_VAL_TBL_OUT , X_LINE_ADJ_TBL => L_LINE_ADJ_TBL_OUT , X_LINE_ADJ_VAL_TBL => L_LINE_ADJ_VAL_TBL_OUT , X_LINE_PRICE_ATT_TBL => L_LINE_PRICE_ATT_TBL_OUT , X_LINE_ADJ_ATT_TBL => L_LINE_ADJ_ATT_TBL_OUT , X_LINE_ADJ_ASSOC_TBL => L_LINE_ADJ_ASSOC_TBL_OUT , X_LINE_SCREDIT_TBL =>
L_LINE_SCREDIT_TBL_OUT , X_LINE_SCREDIT_VAL_TBL => L_LINE_SCREDIT_VAL_TBL_OUT , X_LOT_SERIAL_TBL => L_LOT_SERIAL_TBL_OUT , X_LOT_SERIAL_VAL_TBL => L_LOT_SERIAL_VAL_TBL_OUT , X_ACTION_REQUEST_TBL => L_ACTION_REQUEST_TBL_OUT , X_RETURN_STATUS => L_CHR_RET_STATUS , X_MSG_COUNT => L_MSG_COUNT , X_MSG_DATA => L_MSG_DATA );
L_MSG_DATA := NULL;
IF L_CHR_RET_STATUS <> 'S' THEN
FOR IINDX IN 1 .. L_MSG_COUNT
LOOP
L_MSG_DATA := L_MSG_DATA || ' ' || OE_MSG_PUB.GET (IINDX);
END LOOP;
END IF;
DBMS_OUTPUT.ENABLE (10000);
DBMS_OUTPUT.PUT_LINE ('Sales Order => ' || ISO_REC.SO_NUMBER || ' - Line Number => ' || ISO_REC.LINE);
DBMS_OUTPUT.PUT_LINE ('Return Status: ' || L_CHR_RET_STATUS);
DBMS_OUTPUT.PUT_LINE ('Error Message: ' || L_MSG_DATA);
END LOOP;
COMMIT;
END;

[/code]

this code runs fine and completed and giving me customized error

PL/SQL procedure successfully completed.

Sales Order => 3056926 - Line Number => 2.1
Return Status: E
Error Message:   ONT  OE_PC_UPDATE_FIELD_VIOLATION N ATTRIBUTE Line Type N REASON Line is Booked 


It updates L_LINE_TBL_IN (1).LAST_UPDATE_DATE := SYSDATE;
but not L_LINE_TBL_IN (1).line_type_id := 1023;

question is do we update line_type_id by API on such change in return_reason_code ???
if so , what i am doing mistake. Please see and suggest the best.

your help really appreciable.


regards

Anwer Ali
Previous Topic: internal concurrent manager has terminated with status 1 - giving up.
Next Topic: REVISION NUMBER NOT GETTING GENERATED
Goto Forum:
  


Current Time: Mon Dec 23 01:50:01 CST 2024