DECLARE
CURSOR CUR_ALL_TRX IS
SELECT ROWID,
NULL PARTY_NUMBER,
RCT.ORG_ID,
RCT.CUSTOMER_TRX_ID,
RCT.TRX_NUMBER
FROM XX_DELETE_TRANSACTION RCT
WHERE NVL(V_RET_STATUS, 0) <> 'S';
XV_MSG_DATA VARCHAR2(4000) := NULL;
XV_MSG_COUNT NUMBER := 0;
V_MSG_INDEX NUMBER := 0;
XV_RET_STATUS VARCHAR2(1) := NULL;
V_MESSAGE_TBL ARP_TRX_VALIDATE.MESSAGE_TBL_TYPE;
V_RES VARCHAR2(4000) := NULL;
V_RES_NAME VARCHAR2(4000) := NULL;
V_APP VARCHAR2(4000) := NULL;
V_USER NUMBER := 1250;
BEGIN
DBMS_OUTPUT.PUT_LINE('Detele Transaction...');
FOR C_REC IN CUR_ALL_TRX LOOP
DBMS_OUTPUT.PUT_LINE(' Transaction No.: ' || C_REC.TRX_NUMBER);
DBMS_OUTPUT.PUT_LINE(' Transaction ID : ' || C_REC.CUSTOMER_TRX_ID);
DBMS_OUTPUT.PUT_LINE(' Org ID : ' || C_REC.ORG_ID);
----------------------------------------------------------------------------
---- Setting the org context for the particular session
APPS.MO_GLOBAL.SET_POLICY_CONTEXT('S', C_REC.ORG_ID);
-- apps.mo_global.init('AR');
SELECT APPLICATION_ID, RESPONSIBILITY_ID
INTO V_APP, V_RES
FROM FND_RESPONSIBILITY_TL
WHERE RESPONSIBILITY_ID = 20678;
---- Setting the oracle applications context for the particular session
FND_GLOBAL.APPS_INITIALIZE (1113,20678,222,0);
----------------------------------------------------------------------------
XV_RET_STATUS := NULL;
XV_MSG_COUNT := NULL;
XV_MSG_DATA := NULL;
AR_INVOICE_API_PUB.DELETE_TRANSACTION(P_API_NAME => 'Delete_Transaction',
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_TRUE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
P_CUSTOMER_TRX_ID => C_REC.CUSTOMER_TRX_ID,
P_RETURN_STATUS => XV_RET_STATUS,
P_MSG_COUNT => XV_MSG_COUNT,
P_MSG_DATA => XV_MSG_DATA,
P_ERRORS => V_MESSAGE_TBL);
UPDATE XX_DELETE_TRANSACTION
SET V_RET_STATUS = XV_RET_STATUS
WHERE ROWID = C_REC.ROWID;
UPDATE XX_DELETE_TRANSACTION
SET V_MSG_COUNT = XV_MSG_COUNT
WHERE ROWID = C_REC.ROWID;
IF XV_RET_STATUS <> 'S' THEN
DBMS_OUTPUT.PUT_LINE(' Status: ' || XV_RET_STATUS);
UPDATE XX_DELETE_TRANSACTION
SET V_MSG_DATA = V_RET_STATUS
WHERE ROWID = C_REC.ROWID;
FOR I IN 1 .. XV_MSG_COUNT LOOP
APPS.FND_MSG_PUB.GET(I,
APPS.FND_API.G_FALSE,
XV_MSG_DATA,
V_MSG_INDEX);
DBMS_OUTPUT.PUT_LINE(' Error : ' || XV_MSG_DATA);
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ' || XV_MSG_DATA);
ELSE
DBMS_OUTPUT.PUT_LINE(' Deleted.');
END IF;
DBMS_OUTPUT.PUT_LINE('--------------------');
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error : ' || SQLERRM);
END;
-------------------------
/*delete ra_customer_trx_lines_all a where a.customer_trx_id= 16005;
delete ra_customer_trx_all a where a.customer_trx_id= 16005;
delete RA_CUST_TRX_LINE_GL_DIST_ALL a where a.customer_trx_id= 16005;
delete ar_payment_schedules_all a where a.customer_trx_id= 16005;
select * from ra_cust_trx_line_salesreps_all
where customer_trx_id = 16005 for update;
select * from ar_credit_memo_amounts
where customer_trx_id = 16005;
select * from ar_receivable_applications_all
where customer_trx_id = 16005;*/
/*create table XX_DELETE_TRANSACTION
(
org_id NUMBER(15),
customer_trx_id NUMBER(15) not null,
trx_number VARCHAR2(20) not null,
v_ret_status VARCHAR2(20),
v_msg_count NUMBER,
v_msg_data VARCHAR2(4000)
)
tablespace APPS_TS_TX_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);*/
Comments
Post a Comment