Procure to Pay Technical Cycle

P2P Technical Flow (Procure to Pay Cycle with Important Columns and Joins)

SELECT
PRHA.SEGMENT1 REQ_NUMBER,
PRHA.AUTHORIZATION_STATUS,
PRHA.TYPE_LOOKUP_CODE,
PRLA.ITEM_DESCRIPTION,
PRLA.UNIT_PRICE,
PRLA.QUANTITY,
PRLA.ITEM_ID,
PRLA.QUANTITY_DELIVERED,
PRLA.QUANTITY_CANCELLED,
PRLA.ORG_ID,
MSIB.INVENTORY_ITEM_ID,
MSIB.ORGANIZATION_ID,
MSIB.SEGMENT1 "ITEM",
PRDA.DISTRIBUTION_ID,
PDA.PO_DISTRIBUTION_ID,
PDA.REQ_DISTRIBUTION_ID,
PLA.PO_LINE_ID,
PHA.PO_HEADER_ID,
PHA.SHIP_TO_LOCATION_ID,
PHA.AUTHORIZATION_STATUS "PO STATUS",
ASS.VENDOR_NAME,
ASSA.ADDRESS_LINE1||','||ASSA.CITY||','||ASSA.STATE||','||ASSA.ZIP||','||ASSA.COUNTRY,
RT.TRANSACTION_ID,
RT.TRANSACTION_TYPE,
RT.UNIT_OF_MEASURE,
RSL.QUANTITY_SHIPPED,
RSL.QUANTITY_RECEIVED,
RSH.RECEIPT_NUM,
AILA.INVOICE_ID,
AILA.LINE_SOURCE,
AILA.LINE_TYPE_LOOKUP_CODE,
AILA.AMOUNT,
AIA.INVOICE_NUM,
AIA.INVOICE_AMOUNT,
AIA.AMOUNT_PAID,
AIA.SOURCE,
AIA.INVOICE_TYPE_LOOKUP_CODE,
ACA.BANK_ACCOUNT_NAME,
ACA.CHECK_NUMBER,
ACA.CHECK_ID,
ACA.STATUS_LOOKUP_CODE,
ACA.CLEARED_AMOUNT,
XAH.DESCRIPTION "INVOICE DESCRIPTION",
GJB.NAME "BATCH NAME",
GJB.STATUS,
GJB.DESCRIPTION "BATCH DESCRIPTION",
GJH.NAME "JOURNAL NAME",
GJH.DESCRIPTION "JOURNAL DESCRIPTION"
from
PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA,
MTL_SYSTEM_ITEMS_B MSIB,
PO_LINE_LOCATIONS_ALL PLLA,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
PO_DISTRIBUTIONS_ALL PDA,
PO_LINES_ALL PLA,
PO_HEADERS_ALL PHA,
AP_SUPPLIERS ASS,
AP_SUPPLIER_SITES_ALL ASSA,
--AP_SUPPLIER_CONTACTS ASCS
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_LINES RSL,
RCV_SHIPMENT_HEADERS RSH,
AP_INVOICE_LINES_ALL AILA,
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA,
XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA_EVENTS XE,
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
XLA_DISTRIBUTION_LINKS XDL,
GL_IMPORT_REFERENCES GIR,
GL_JE_BATCHES GJB,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL
where
PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
AND
PRLA.ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND
PLLA.SHIP_TO_ORGANIZATION_ID=MSIB.ORGANIZATION_ID
AND
PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID
AND
PRDA.DISTRIBUTION_ID=PDA.REQ_DISTRIBUTION_ID
AND
PDA.PO_LINE_ID=PLA.PO_LINE_ID
AND
PLLA.PO_LINE_ID=PLA.PO_LINE_ID
AND
PLA.PO_HEADER_ID=PHA.PO_HEADER_ID
AND
PHA.VENDOR_ID=ASS.VENDOR_ID
AND
ASS.VENDOR_ID=ASSA.VENDOR_ID
--AND
--ASSA.VENDOR_SITE_ID=ASCS.VENDOR_SITE_ID
AND
PDA.PO_DISTRIBUTION_ID=RT.PO_DISTRIBUTION_ID
AND
RT.TRANSACTION_TYPE='RECEIVE'
AND
RT.SHIPMENT_LINE_ID=RSL.SHIPMENT_LINE_ID
AND
RSL.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID
AND
PDA.PO_DISTRIBUTION_ID=AILA.PO_DISTRIBUTION_ID
AND
AILA.INVOICE_ID=AIA.INVOICE_ID
AND
AIA.INVOICE_ID=AIDA.INVOICE_iD
AND
AIDA.INVOICE_ID=AIPA.INVOICE_ID
AND
AIPA.CHECK_ID=ACA.CHECK_ID
AND
XTE.TRANSACTION_NUMBER=AIA.INVOICE_NUM
AND
XTE.ENTITY_ID=XE.ENTITY_ID
AND
XE.EVENT_ID=XAH.EVENT_ID
AND
XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
AND
XAL.AE_LINE_NUM=AIDA.INVOICE_LINE_NUMBER
AND
XE.EVENT_ID=XDL.EVENT_ID
AND
XAH.AE_HEADER_ID=XDL.AE_HEADER_ID
AND
XAL.AE_LINE_NUM=XDL.AE_LINE_NUM
AND
GIR.REFERENCE_5=XTE.ENTITY_ID
AND
GIR.REFERENCE_6=TO_CHAR(XE.EVENT_ID)
AND
GIR.REFERENCE_7=TO_CHAR(XAH.AE_HEADER_ID)
AND
GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID
AND
GIR.JE_BATCH_ID=GJB.JE_BATCH_ID
AND
GJB.JE_BATCH_ID=GJH.JE_BATCH_ID
AND
GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND
GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
AND
GJL.JE_LINE_NUM=GIR.JE_LINE_NUM
AND
PRHA.SEGMENT1=2259;

SQL Queries for Interview

Sql Queries

SELECT * FROM SCOTT.EMP WHERE TO_CHAR(HIREDATE,'yyyy')<1982;
SELECT * FROM SCOTT.EMP WHERE COMM>SAL;
SELECT ENAME,MONTHS_BETWEEN(SYSDATE,HIREDATE) EXP,SAL/30 SAL FROM SCOTT.EMP ;--where sal/30>200;
SELECT ENAME,EMPNO,SAL,TO_CHAR(HIREDATE,'day') FROM SCOTT.EMP WHERE TO_CHAR(HIREDATE,'day')IN( 'monday','saturday');
SELECT * FROM SCOTT.EMP WHERE SAL<=1000;
SELECT ENAME,MONTHS_BETWEEN(SYSDATE,HIREDATE) EXP FROM  SCOTT.EMP;
SELECT DISTINCT * FROM SCOTT.EMP;
SELECT DISTINCT(DEPTNO) FROM SCOTT.EMP;
SELECT ENAME FROM SCOTT.EMP WHERE EMPNO IN(SELECT MGR FROM SCOTT.EMP WHERE SAL>2000);
SELECT * FROM SCOTT.EMP WHERE MGR IS NULL;
SELECT (SAL*12),EMPNO FROM SCOTT.EMP;
SELECT JOB,DEPTNO,ENAME FROM SCOTT.EMP;
SELECT ENAME,SAL,JOB FROM SCOTT.EMP WHERE JOB IN(SELECT JOB FROM SCOTT.EMP WHERE ENAME='FORD');
SELECT * FROM SCOTT.EMP WHERE HIREDATE>(SELECT HIREDATE FROM SCOTT.EMP WHERE ENAME='KING');
SELECT ENAME,SAL FROM SCOTT.EMP WHERE SAL IN(SELECT MAX(SAL) FROM SCOTT.EMP);
SELECT EMPNO,ENAME,SAL FROM SCOTT.EMP WHERE JOB='CLERK' AND SAL=(SELECT MAX(SAL) FROM SCOTT.EMP WHERE JOB='CLERK');
SELECT AVG(SAL),MIN(SAL),MAX(SAL),ENAME,JOB FROM SCOTT.EMP WHERE JOB='CLERK' OR JOB='MANAGER' GROUP BY ENAME,JOB;
SELECT DEPTNO FROM SCOTT.EMP WHERE JOB='CLERK' GROUP BY DEPTNO HAVING COUNT(*)>=2;
SELECT SUM(NVL(COMM,100)+SAL),DEPTNO FROM SCOTT.EMP GROUP BY DEPTNO;
SELECT ENAME FROM SCOTT.EMP WHERE SAL >(SELECT SAL FROM SCOTT.EMP WHERE ENAME='JONES') AND SAL> (SELECT SAL FROM SCOTT.EMP WHERE ENAME='SCOTT');
SELECT * FROM SCOTT.EMP E WHERE SAL=(SELECT MAX(SAL) FROM SCOTT.EMP  WHERE DEPTNO=E.DEPTNO);
SELECT ENAME,SAL FROM SCOTT.EMP WHERE SAL<(SELECT SAL FROM SCOTT.EMP WHERE ENAME='PRESIDENT');
SELECT ENAME,LOC FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO AND LOC='CHICAGO';
CREATE VIEW TEMP_VIEW AS SELECT  ENAME,EMPNO,JOB,SAL,MGR,HIREDATE,E.DEPTNO,LOC,DNAME FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO;
SELECT * FROM TEMP_VIEW;
SELECT COUNT(EMPNO) FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO AND LOC='NEW YORK';
SELECT  DNAME,LOC,COUNT(EMPNO),AVG(SAL) FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO AND E.DEPTNO=&DEPTNO GROUP BY DNAME, LOC;
SELECT E.ENAME MGR,F.ENAME  FROM SCOTT.EMP E,SCOTT.EMP F WHERE E.EMPNO=F.MGR;
SELECT JOB FROM SCOTT.EMP  HAVING SUM(SAL) >(SELECT MAX(SAL) FROM SCOTT.EMP WHERE JOB='MGR');
SELECT SAL,(CASE WHEN SAL<=1500 THEN 'Low'
WHEN SAL>2000 THEN 'High'
ELSE 'Med'
END ) RANGE,
DECODE(SAL,5000,'King','ASDF') ASD
FROM
SCOTT.EMP;
SELECT ENAME FROM SCOTT.EMP WHERE JOB='CLERK' OR JOB='SALESMAN'
OR JOB='ANALYST' AND SAL>3000;
SELECT ENAME FROM SCOTT.EMP WHERE TO_CHAR(SYSDATE,'yyyy')-TO_CHAR(HIREDATE,'yyyy')>=27;
SELECT ENAME FROM SCOTT.EMP WHERE TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(HIREDATE,'YYYY')>=30;
SELECT USERNAME FROM ALL_USERS;
SELECT TNAME FROM TAB;
SHOW USER;
SELECT ENAME FROM SCOTT.EMP WHERE DEPTNO IN(10,20,40) OR JOB IN('CLERKS','SALESMAN','ANALYST');
SELECT ENAME,SAL*12 FROM SCOTT.EMP ORDER BY SAL DESC;
SELECT DEPTNO,COUNT(EMPNO)FROM SCOTT.EMP GROUP BY DEPTNO;
SELECT JOB,COUNT(EMPNO)FROM SCOTT.EMP GROUP BY JOB;
SELECT ENAME,SAL FROM SCOTT.EMP WHERE SAL =(SELECT MAX(SAL) FROM SCOTT.EMP);
DISPLAY THE NAMES OF CLERKS WHO EARN A SALARY MORE THAN THE LOWEST SALARY OF ANY SALESMAN.
SELECT ENAME FROM SCOTT.EMP WHERE JOB='CLERK' AND SAL>(SELECT MIN(SAL) FROM SCOTT.EMP WHERE JOB='SALESMAN');
DISPLAY THE NAMES OF THE EMPLOYEES WHO EARN HIGHEST SALARY IN THEIR RESPECTIVE DEPARTMENTS
SELECT ENAME,DEPTNO,SAL FROM SCOTT.EMP WHERE SAL IN(SELECT MAX(SAL) FROM SCOTT.EMP GROUP BY DEPTNO);
SELECT ENAME FROM SCOTT.EMP WHERE DEPTNO=(SELECT DEPTNO FROM SCOTT.DEPT WHERE DNAME='ACCOUNTING');
OR
SELECT ENAME FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO AND DNAME='ACCOUNTING';
DISPLAY THE JOB GROUPS HAVING TOTAL SALARY GREATER THAN THE MAXIMUM SALARY FOR MANAGERS.
SELECT JOB,SUM(SAL) TOTAL_SAL FROM SCOTT.EMP GROUP BY JOB HAVING SUM(SAL)>(SELECT MAX(SAL) FROM SCOTT.EMP WHERE JOB='MANAGER');
SELECT ENAME,LENGTH(ENAME) FROM SCOTT.EMP;
SELECT EMPNO,ENAME,DECODE(DEPTNO,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPRATIONS') FROM SCOTT.EMP;
SELECT TO_DATE(SYSDATE)-TO_DATE('05-may-90')FROM DUAL;
SELECT TO_DATE(SYSDATE)-TO_DATE('17-dec-88')FROM DUAL;
SELECT TO_CHAR(SYSDATE,'ddth Month day year') FROM DUAL;
SELECT JOB FROM SCOTT.EMP WHERE DEPTNO=10 AND JOB IN(SELECT JOB FROM SCOTT.EMP WHERE DEPTNO=20);
DISPLAY THE DETAILS OF THOSE WHO DO NOT HAVE ANY PERSON WORKING UNDER THEM.
SELECT E.ENAME FROM SCOTT.EMP,SCOTT.EMP E WHERE EMP.MGR=E.EMPNO GROUP BY E.ENAME HAVING COUNT(*)=1;
SELECT DISTINCT(M.ENAME) FROM SCOTT.EMP E,SCOTT.EMP M WHERE M.EMPNO=E.MGR;
SELECT ENAME FROM SCOTT.EMP WHERE ENAME NOT IN(SELECT DISTINCT(M.ENAME) FROM SCOTT.EMP E,SCOTT.EMP M WHERE M.EMPNO=E.MGR);
SELECT M.ENAME FROM SCOTT.EMP E,SCOTT.EMP M WHERE E.EMPNO=M.MGR AND E.ENAME='JONES';
DISPLAY THOSE EMPLOYEES WHOSE SALARY GRETER THAN HIS MANAGER SALARY.
SELECT P.ENAME FROM SCOTT.EMP E,SCOTT.EMP P WHERE E.EMPNO=P.MGR AND P.SAL>E.SAL;
DISPLAY THOSE EMPLOYEE WHOSE SALARY IS EQUAL TO AVERAGE OF MAXIMUM AND MINIMUM?
SELECT ENAME FROM SCOTT.EMP WHERE SAL=(SELECT MAX(SAL)+MIN(SAL)/2 FROM SCOTT.EMP);
SELECT COUNT(*) FROM SCOTT.EMP GROUP BY DEPTNO HAVING COUNT(DEPTNO)>3
FIND OUT LAST 5(LEAST)EARNERS OF THE COMPANY.?;
SELECT DISTINCT SAL FROM SCOTT.EMP E WHERE 5>=(SELECT COUNT(DISTINCT SAL) FROM SCOTT.EMP A WHERE A.SAL<=E.SAL) ORDER BY SAL DESC;
SELECT C.ENAME FROM SCOTT.EMP C, SCOTT.EMP E WHERE C.SAL=E.DEPTNO;

order to cash cycle

Oracle-Order to Cash Cycle with Important Columns and Joins

SELECT DISTINCT
OOHA.ORDER_NUMBER,
OOLA.ORDERED_ITEM,
OOLA.ORDERED_QUANTITY,
OOLA.CUST_PO_NUMBER,
OOLA.UNIT_SELLING_PRICE,
HCA.ACCOUNT_NAME CUSTOMER_NAME,
OTTT.NAME ORDER_NAME,
OTTT.DESCRIPTION ORDER_DESC,
QLH.NAME PRICELIST_NAME,
(SELECT LAST_NAME||','||FIRST_NAME FROM PER_ALL_PEOPLE_F WHERE PERSON_ID=RSA.PERSON_ID) SALESREP,HCSUA.LOCATION,
HPS.PARTY_SITE_NAME,
HL.ADDRESS1||','||HL.CITY||','||HL.POSTAL_CODE||','||HL.STATE||','||HL.COUNTRY||','||HL.ADDRESS_KEY TOTAL_ADDRESS,
MSIB.SEGMENT1 ITEM,
MSIB.DESCRIPTION,
MSIB.INVENTORY_ITEM_STATUS_CODE,
MSIB.ITEM_TYPE,
WDD.REQUESTED_QUANTITY,
WDD.SHIPPED_QUANTITY,
WDD.DELIVERED_QUANTITY,
OOHA.FLOW_STATUS_CODE HEADERS_CODE,
OOLA.FLOW_STATUS_CODE LINES_CODE,
WDD.RELEASED_STATUS,
WND.DELIVERY_ID,
WND.NAME DELIVERY_NUMBER,
RCTA.TRX_NUMBER,
ACRA.AMOUNT,
ACRA.RECEIPT_NUMBER,
GJB.NAME BACTH_NAME,
GJB.DESCRIPTION  BATCH_DESCRIPTION,
GJH.NAME JOURNAL_NAME,
GJH.STATUS JOURNAL_STATUS,
GJH.DESCRIPTION JE_DESCRIPTION
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
HZ_CUST_ACCOUNTS HCA,
OE_TRANSACTION_TYPES_TL OTTT,
QP_LIST_HEADERS QLH,
RA_SALESREPS_ALL RSA,
HZ_CUST_SITE_USES_ALL HCSUA,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_PARTY_SITES  HPS,
HZ_LOCATIONS HL,
MTL_SYSTEM_ITEMS_B MSIB,
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_NEW_DELIVERIES WND,
RA_CUSTOMER_TRX_LINES_ALL RCTLA,
RA_CUSTOMER_TRX_ALL RCTA,
AR_PAYMENT_SCHEDULES_ALL APSA,
AR_RECEIVABLE_APPLICATIONS_ALL ARAA,
AR_CASH_RECEIPTS_ALL ACRA,
XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA_EVENTS XE,
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
GL_IMPORT_REFERENCES GIR,
GL_JE_BATCHES GJB,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL
WHERE
OOHA.HEADER_ID=OOLA.HEADER_ID
AND
OOHA.SOLD_TO_ORG_ID=HCA.CUST_ACCOUNT_ID
AND
OOHA.ORDER_TYPE_ID=OTTT.TRANSACTION_TYPE_ID
AND
OOHA.PRICE_LIST_ID=QLH.LIST_HEADER_ID
AND
OOHA.SALESREP_ID=RSA.SALESREP_ID
AND
OOHA.SHIP_TO_ORG_ID=HCSUA.SITE_USE_ID
AND
HCSUA.CUST_ACCT_SITE_ID=HCASA.CUST_ACCT_SITE_ID
AND
HCASA.PARTY_SITE_ID=HPS.PARTY_SITE_ID
AND
HPS.LOCATION_ID=HL.LOCATION_ID
AND
OOLA.INVENTORY_ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND
OOLA.SHIP_FROM_ORG_ID=MSIB.ORGANIZATION_ID
AND
OOLA.HEADER_ID=WDD.SOURCE_HEADER_ID
AND
WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND
WDA.DELIVERY_ID=WND.DELIVERY_ID
AND
RCTLA.INTERFACE_LINE_ATTRIBUTE1=TO_CHAR(OOHA.ORDER_NUMBER)
AND
RCTLA.CUSTOMER_TRX_ID=RCTA.CUSTOMER_TRX_ID
AND
RCTA.CUSTOMER_TRX_ID=APSA.CUSTOMER_TRX_ID
AND
APSA.CUSTOMER_TRX_ID=ARAA.APPLIED_CUSTOMER_TRX_ID
AND
ARAA.CASH_RECEIPT_ID=ACRA.CASH_RECEIPT_ID
AND
XTE.TRANSACTION_NUMBER=RCTA.TRX_NUMBER
AND
XTE.ENTITY_ID=XE.ENTITY_ID
AND
XE.EVENT_ID=XAH.EVENT_ID
AND
XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
AND
GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID
AND
GIR.GL_SL_LINK_TABLE='XLAJEL'
AND
GIR.JE_BATCH_ID=GJB.JE_BATCH_ID
AND
GJB.JE_BATCH_ID=GJH.JE_BATCH_ID
AND
GJH.JE_HEADER_ID=GJH.JE_HEADER_ID
AND
OOHA.ORDER_NUMBER=5854;