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

Query to get Supplier/Vendor data along with GST Number

SELECT ASUP.SEGMENT1 SUPPLIER_CODE,
VENDOR_SITE_CODE SUPPLIER_SITE,
ASSA.ADDRESS_LINE1,
ASSA.ADDRESS_LINE2,
ASSA.ADDRESS_LINE3,
ASSA.CITY,
STATE STATE_NAME,
(SELECT 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 = ASSA.VENDOR_SITE_ID
AND JPR.PARTY_ID = ASUP.VENDOR_ID
AND NVL (JPRL.EFFECTIVE_FROM, SYSDATE) <= SYSDATE—TO TAKE ACTIVE REG NUMBERS
AND NVL (JPRL.EFFECTIVE_TO, SYSDATE) >= SYSDATE—TO TAKE ACTIVE REG NUMBERS
AND REGISTRATION_TYPE_CODE = ‘GSTIN’)
SUPPLIER_GSTIN,
DECODE (
(SELECT 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 = ASSA.VENDOR_SITE_ID
AND JPR.PARTY_ID = ASUP.VENDOR_ID
AND NVL (JPRL.EFFECTIVE_FROM, SYSDATE) <= SYSDATE
AND NVL (JPRL.EFFECTIVE_TO, SYSDATE) >= SYSDATE
AND REGISTRATION_TYPE_CODE = ‘GSTIN’),
NULL, ‘UNREGISTERED’,
‘REGISTERED’)
REGISTRATION_TYPE
FROM AP_SUPPLIERS ASUP, AP_SUPPLIER_SITES_ALL ASSA
WHERE ASUP.VENDOR_ID = ASSA.VENDOR_ID

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

Start date of Financial Year for a date

The function calculates start day of financial year when a date is given as a parameter

CREATE OR REPLACE FUNCTION FINYEAR_FROM_DATE (A_DATE IN DATE)
RETURN CHAR
IS
AA CHAR (10);
NN NUMBER (8);
BB CHAR (8);
BEGIN
BB := TO_CHAR (A_DATE, ‘yyyy’) || TO_CHAR (A_DATE, ‘yyyy’);
NN := BB;

IF TO_CHAR (A_DATE, ‘mm’) < ’04’
THEN
NN := NN – 10000;
ELSE
NN := NN + 1;
END IF;

BB := LTRIM (RTRIM (TO_CHAR (NN, ‘00000000’)));
–aa := substr(bb,1,4)||substr(bb,7,2);
AA := ’01/04/’ || SUBSTR (BB, 1, 4);
RETURN (AA);
END;

 

Suppliers in Oracle Apps

The are two major tables for suppliers

1. AP_SUPPLIERS Vendor Master
2. AP_SUPPLIER_SITES_ALL Site Master joined with Vendor_id

A simple example to demonstrate data fetch using query:

SELECT
ASUP.SEGMENT1 VENDOR_CODE,
ASUP.VENDOR_NAME,
ASSA.VENDOR_SITE_CODE –VENDOR_SITE_NAME
FROM AP_SUPPLIERS ASUP,
ASP_SUPPLIER_SITES_ALL ASSA
WHERE
ASUP.VENDOR_ID=ASSA.VENDOR_ID
AND ASSA.ORG_ID=:P_ORG_ID–SUPPLIERS FOR PARTICULAR OPERATING UNIT

Function to Check Validity of Email (Regular Expression)

The function is to check email so that it does not contain any junk character.It returns 0 for false and 1 for true.

CREATE OR REPLACE FUNCTION EMAIL_VALID (p_email IN VARCHAR2)
RETURN NUMBER
IS
cemailregexp CONSTANT VARCHAR2 (1000)
:= ‘^[a-z0-9!#$%&”*+/=?^_`{|}~-]+(\.[a-z0-9!#$%&”*+/=?^_`{|}~-]+)*@([a-z0-9]([a-z0-9-]*[a-z0-9])?\.)+([A-Z]{2}|arpa|biz|com|info|intww|name|net|org|pro|aero|asia|cat|coop|edu|gov|jobs|mil|mobi|museum|pro|tel|travel|post)$’ ;
BEGIN
IF REGEXP_LIKE (p_email, cemailregexp, ‘i’)
THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;

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
);

Function to return HSN/SAC code for an item

CREATE OR REPLACE FUNCTION ITEM_HSN_CD (
P_ITEM_ID NUMBER
)
RETURN VARCHAR2
IS
L_REP_CODE VARCHAR2(40);

BEGIN
SELECT JRA.REPORTING_CODE
INTO L_REP_CODE
FROM JAI_REPORTING_ASSOCIATIONS_V JRA
WHERE JRA.ENTITY_CODE = ‘ITEM’
AND REPORTING_TYPE_CODE IN (‘GST_HSN_CODE’ ,’GST_SAC_CODE_ITEM’)
AND ( ENTITY_ID =
(SELECT MIN(JR.TEMPLATE_HDR_ID)
FROM JAI_ITEM_TEMPL_HDR JR
WHERE JR.ENTITY_TYPE_CODE = ‘ITEM’
AND JR.ENTITY_ID = P_ITEM_ID
AND JR.ITEM_CLASSIFICATION = ‘OTIN’
)
OR ENTITY_ID IN
(SELECT JRT.TEMPLATE_HDR_ID
FROM JAI_ITEM_TEMPL_HDR JRT
WHERE JRT.ENTITY_TYPE_CODE = ‘ITEM_TEMPL’
AND JRT.INVENTORY_ITEM_ID = P_ITEM_ID
AND JRT.ITEM_CLASSIFICATION = ‘OTIN’
)
OR ENTITY_ID IN
(SELECT JT.TEMPLATE_HDR_ID
FROM JAI_ITEM_TEMPL_HDR JT
WHERE JT.ENTITY_TYPE_CODE = ‘ITEM_TEMPL_ASGN’
AND JT.ENTITY_ID = P_ITEM_ID
AND JT.INVENTORY_ITEM_ID = P_ITEM_ID
AND JT.ITEM_CLASSIFICATION = ‘OTIN’
));
RETURN L_REP_CODE;
EXCEPTION WHEN OTHERS THEN RETURN NULL;
END ;