Query to Get Customer GST number with customer details

SELECT CUST.ACCOUNT_NUMBER CUSTOMER_NUMBER,
PARTY_SITE.PARTY_SITE_NUMBER,
PARTY.PARTY_NAME CUST_NAME,
LOC.ADDRESS1
|| ‘ ‘
|| LOC.ADDRESS2
|| ‘ ‘
|| LOC.ADDRESS3
|| ‘ ‘
|| LOC.POSTAL_CODE
|| ‘ ‘
|| LOC.CITY
|| ‘ ‘
|| NVL (LOC.STATE, LOC.PROVINCE)
ADDRESS,
(SELECT MIN (JPRL.REGISTRATION_NUMBER)
FROM JAI_PARTY_REGS JPR, JAI_PARTY_REG_LINES JPRL
WHERE JPRL.PARTY_REG_ID = JPR.PARTY_REG_ID
AND JPR.PARTY_SITE_ID(+) = ACCT.CUST_ACCT_SITE_ID
AND JPR.PARTY_ID = CUST.CUST_ACCOUNT_ID
AND NVL (JPRL.EFFECTIVE_FROM, SYSDATE) = SYSDATE
AND REGISTRATION_TYPE_CODE = ‘GSTIN’
AND JPR.PARTY_TYPE_CODE IN (‘THIRD_PARTY’, ‘THIRD_PARTY_SITE’)) CUST_GST_NO
FROM HZ_CUST_ACCOUNTS_ALL CUST,
HZ_CUST_ACCT_SITES_ALL ACCT,
HZ_CUST_SITE_USES_ALL SHIP,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOCATIONS LOC,
HZ_PARTIES PARTY
WHERE CUST.CUST_ACCOUNT_ID = ACCT.CUST_ACCOUNT_ID
AND ACCT.CUST_ACCT_SITE_ID = SHIP.CUST_ACCT_SITE_ID
AND ACCT.ORG_ID = SHIP.ORG_ID
AND SHIP.SITE_USE_CODE = ‘BILL_TO’
AND CUST.STATUS = ‘A’
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND ACCT.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND CUST.PARTY_ID = PARTY.PARTY_ID
AND SHIP.ORG_ID = :P_ORG_ID  —-enter OPERATING UNIT

FND_PROFILE VALUES and FND_GLOBAL VALUES in oracle apps

FOLLOWING ARE THE FND_PROFILE VALUES THAT CAN BE USED IN THE PL/SQL CODE:

FND_PROFILE.VALUE(‘PROFILEOPTION’);
FND_PROFILE.VALUE(‘MFG_ORGANIZATION_ID’);
FND_PROFILE.VALUE(‘ORG_ID’);
FND_PROFILE.VALUE(‘LOGIN_ID’);
FND_PROFILE.VALUE(‘USER_ID’);
FND_PROFILE.VALUE(‘USERNAME’);
FND_PROFILE.VALUE(‘CONCURRENT_REQUEST_ID’);
FND_PROFILE.VALUE(‘GL_SET_OF_BKS_ID’);
FND_PROFILE.VALUE(‘SO_ORGANIZATION_ID’);
FND_PROFILE.VALUE(‘APPL_SHRT_NAME’);
FND_PROFILE.VALUE(‘RESP_NAME’);
FND_PROFILE.VALUE(‘RESP_ID’);

FOLLOWING ARE THE FND_GLOBAL VALUES THAT CAN BE USED IN THE PL/SQL CODE:

FND_GLOBAL.USER_ID;
FND_GLOBAL.APPS_INTIALIZE;
FND_GLOBAL.LOGIN_ID;
FND_GLOBAL.CONC_LOGIN_ID;
FND_GLOBAL.PROG_APPL_ID;
FND_GLOBAL.CONC_PROGRAM_ID;
FND_GLOBAL.CONC_REQUEST_ID;

Unable to make an item ‘Inactive’ in oracle apps Item master.

If while declaring an item inactive it is showing that item has some pending transactions.

Please run all the queries one by one and clear all pending transactions.

1) Unprocessed shipping transactions
SELECT *
FROM WSH_DELIVERY_DETAILS
WHERE PICKABLE_FLAG = ‘Y’
AND INV_INTERFACED_FLAG IN (‘N’,’P’)
AND RELEASED_STATUS <> ‘D’
AND INVENTORY_ITEM_ID = &X_ITEM_ID
AND ORGANIZATION_ID IN (
SELECT MP1.ORGANIZATION_ID
FROM
MTL_PARAMETERS MP1 — this org
,MTL_ITEM_ATTRIBUTES MIA
WHERE MIA.ATTRIBUTE_NAME = ‘MTL_TRANSACTIONS_ENABLED_FLAG’
AND MIA.CONTROL_LEVEL = 2 — Org level
AND MP1.ORGANIZATION_ID = &X_ORG_ID
UNION
SELECT MP2.ORGANIZATION_ID
FROM
MTL_PARAMETERS MP2
,MTL_PARAMETERS MP1 — this org
,MTL_ITEM_ATTRIBUTES MIA
WHERE MIA.ATTRIBUTE_NAME = ‘MTL_TRANSACTIONS_ENABLED_FLAG’
AND MIA.CONTROL_LEVEL = 1 — Master level
AND MP1.MASTER_ORGANIZATION_ID = MP2.MASTER_ORGANIZATION_ID
AND MP1.ORGANIZATION_ID = &X_ORG_ID
)
ORDER BY
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,SOURCE_LINE_ID
,DELIVERY_DETAIL_ID

Oracle SQL query to print all the dates between two dates

The query will print all the dates between two given dates.There may be many ways to achieve this. A simple example is given below.

SELECT TO_DATE (’01/01/2016′, ‘DD/MM/YYYY’) – 1 + ROWNUM AS DT
FROM ALL_OBJECTS
WHERE TO_DATE (’01/01/2016′, ‘DD/MM/YYYY’) – 1 + ROWNUM <=TO_DATE (’31/01/2016′, ‘DD/MM/YYYY’)

Parameterized Cursor in PL/SQL

Parameterized cursors are cursors that can accept  parameter values while opening the cursor.Here is an example of paramerized cursor in PL/SQL.

DECLARE
CURSOR C_ITEMS(P_MIN_ID NUMBER,P_MAX_ID NUMBER)
IS SELECT SEGMENT1,DESCRIPTION FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID BETWEEN P_MIN_ID AND P_MIN_ID;
V_ITEM_CODE VARCHAR2(100);
V_ITEM_DESC VARCHAR2(240);
BEGIN
OPEN C_ITEMS(&P_MIN_ID, &P_MIN_ID);
LOOP
FETCH C_ITEMS INTO V_ITEM_CODE,V_ITEM_DESC;
EXIT WHEN C_ITEMS%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_ITEM_CODE||’ ‘||V_ITEM_DESC);
END LOOP;
CLOSE C_ITEMS;
END;

Delete Duplicate Records from Table in Oracle PL/SQL

 

 

PLEASE FIND 5 DIFFERENT WAYS TO DELETE DUPLICATE RECORDS IN PL/SQL

CREATE A – EMP TABLE

CREATE TABLE EMP(
EMPNNO INTEGER,
EMPNAME VARCHAR2(20),
SALARY NUMBER);

10 RAJ 150000
11 VEERU 200000
12 RAJ 150000
12 WILLY 300000
12 MANU 400000
13 TARAN 400000
14 MANU 400000
15 TARAN 400000

1. USING ROWID

SQL > DELETE FROM EMP
WHERE ROWID NOT IN
(SELECT MAX(ROWID) FROM EMP GROUP BY EMPNO);

 

2. USING SELF-JOIN

SQL > DELETE FROM EMP E1
WHERE ROWID NOT IN
(SELECT MAX(ROWID) FROM EMP E2
WHERE E1.EMPNO = E2.EMPNO );

3. USING ROW_NUMBER()

SQL > DELETE FROM EMP WHERE ROWID IN
(
SELECT RID FROM
(
SELECT ROWID RID,
ROW_NUMBER() OVER(PARTITION BY EMPNO ORDER BY EMPNO) RNK
FROM EMP
)
WHERE RNK > 1
);

 

4. USING DENSE_RANK()

SQL > DELETE FROM EMP WHERE ROWID IN
(
SELECT RID FROM
(
SELECT ROWID RID,
DENSE_RANK() OVER(PARTITION BY EMPNO ORDER BY ROWID) RNK
FROM EMP
)
WHERE RNK > 1
);

HERE YOU CAN USE BOTH RANK() AND DENS_RANK() SINCE BOTH WILL GIVE UNIQUE RECORDS WHEN ORDER BY ROWID.

5. USING GROUP BY

CONSIDER THE EMP TABLE WITH BELOW ROWS

12 MANU 400000
13 TARAN 500000
14 MANU 400000
15 TARAN 500000

SQL > DELETE FROM EMP WHERE
(EMPNO,EMPNAME,SALARY) IN
(
SELECT MAX(EMPNO),EMPNAME,SALARY FROM EMP
GROUP BY EMPNAME,SALARY
);

SQL query for INV_IOI_MASTER_CHILD_1G Errors in Item Import in oracle apps

SELECT M.ORGANIZATION_ID MASTERORG,
C.ORGANIZATION_ID CHILDORG,
M.INVENTORY_ITEM_ID ITEM_ID,
M.SEGMENT1 ITEMNAME,
M.RECEIPT_DAYS_EXCEPTION_CODE MST_RECEIPT_DAYS_EXCEPT_CODE,
C.RECEIPT_DAYS_EXCEPTION_CODE CH_RECEIPT_DAYS_EXCEPT_CODE,
M.RECEIVING_ROUTING_ID MST_RECEIVING_ROUTING_ID,
C.RECEIVING_ROUTING_ID CH_RECEIVING_ROUTING_ID,
M.INVOICE_CLOSE_TOLERANCE MST_INVOICE_CLOSE_TOLERANCE,
C.INVOICE_CLOSE_TOLERANCE CH_INVOICE_CLOSE_TOLERANCE,
M.RECEIVE_CLOSE_TOLERANCE MST_RECEIVE_CLOSE_TOLERANCE,
C.RECEIVE_CLOSE_TOLERANCE CH_RECEIVE_CLOSE_TOLERANCE,
M.AUTO_LOT_ALPHA_PREFIX MST_AUTO_LOT_ALPHA_PREFIX,
C.AUTO_LOT_ALPHA_PREFIX CH_AUTO_LOT_ALPHA_PREFIX,
M.DESCRIPTION MST_DESCRIPTION,
C.DESCRIPTION CH_DESCRIPTION,
T.LONG_DESCRIPTION MST_LONG_DESCRIPTION,
C.LONG_DESCRIPTION CH_LONG_DESCRIPTION
FROM MTL_SYSTEM_ITEMS_INTERFACE C,
MTL_SYSTEM_ITEMS_B M,
MTL_SYSTEM_ITEMS_TL T
WHERE C.TRANSACTION_ID IN
(SELECT TRANSACTION_ID
FROM MTL_INTERFACE_ERRORS
WHERE MESSAGE_NAME = ‘INV_IOI_MASTER_CHILD_1G’)
AND C.REQUEST_ID = 42238949 —-change your request id
AND C.INVENTORY_ITEM_ID = M.INVENTORY_ITEM_ID
AND C.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
AND T.ORGANIZATION_ID = M.ORGANIZATION_ID
AND M.ORGANIZATION_ID = (SELECT MASTER_ORGANIZATION_ID
FROM MTL_PARAMETERS P
WHERE C.ORGANIZATION_ID = P.ORGANIZATION_ID)