Dead Reservations

SELECT L.LINE_ID,
( SELECT ORDER_NUMBER FROM OE_ORDER_HEADERS_ALL WE WHERE WE.HEADER_ID = L.HEADER_ID) ORDER_NUMBER
, ORDERED_ITEM
, RESERVATION_QUANTITY
FROM OE_ORDER_LINES_ALL L, MTL_RESERVATIONS M
WHERE M.PRIMARY_RESERVATION_QUANTITY>0
AND nvl(L.OPEN_FLAG,'Y')='N'
AND L.LINE_ID = M.DEMAND_SOURCE_LINE_ID
AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI
WHERE MTI.TRX_SOURCE_LINE_ID = L.LINE_ID
AND MTI.SOURCE_HEADER_ID = L.HEADER_ID
AND MTI.SOURCE_CODE = nvl('&OE_SOURCE_CODE',MTI.SOURCE_CODE))
AND NOT EXISTS (SELECT 1 FROM WSH_DELIVERY_DETAILS WDD
WHERE WDD.SOURCE_LINE_ID=L.LINE_ID
AND WDD.SOURCE_CODE ='OE'
AND WDD.INV_INTERFACED_FLAG IN ('N','P')
AND WDD.RELEASED_STATUS <> 'D');

How to Find out where Diag file is created

SELECT value
FROM v$parameter
WHERE name = 'utl_file_dir';

Purchase Order Update

Purchase Order Getting stuck with “in process” status

Scenario:
Opened approved purchase order in edit mode, made couple of changes at PO header/Line level and submitted for approval but PO got stuck with “in process” status.
NO Pending workflow notification.
Solution:

Step1: Query purchase order data with correct po number.
SELECT hr.name,
poh.segment1,
poh.REVISION_NUM,
poh.wf_item_type,
poh.wf_item_key,
authorization_status,
poh.po_header_id
FROM po_headers_all poh, hr_all_organization_units hr
WHERE poh.org_id = hr.organization_id
AND poh.segment1 =

Step2: Update purchase order authorizing status to “REQUIRES REAPPROVAL”.
update po_headers_all
set authorization_status='REQUIRES REAPPROVAL'
where authorization_status ='IN PROCESS'
and poh.segment1 = ;

From front end Open PO , Unreserve Funds , Cancel PO , if Required .

UPDATE PO_HEADERS_ALL
SET AUTHORIZATION_STATUS = 'REJECTED'
WHERE AUTHORIZATION_STATUS = 'REQUIRES REAPPROVAL'
AND SEGMENT1 LIKE

commit;

Payroll : Employee Contract Information

SELECT
--PAPF.PERSON_ID PERSON_ID,
(SELECT NAME
FROM HR_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.PER_ALL_ASSIGNMENTS_F PAA
WHERE (NVL (SYSDATE, SYSDATE) BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE)
AND PERSON_ID = PAPF.PERSON_ID)) DEPARTMENT,
PAPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER,
PAPF.FULL_NAME EMPLOYEE_NAME,
trim(PAC.SEGMENT10) TELEPHONE_NUMBER
--to_char(PAPF.EFFECTIVE_START_DATE,'DD-MON-YY') JOINING_DATE
FROM
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_ANALYSES PPA,
PER_ANALYSIS_CRITERIA PAC,
FND_ID_FLEX_STRUCTURES FIFL,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_JOBS PJ
WHERE
PAPF.PERSON_ID = PPA.PERSON_ID
AND PAC.ANALYSIS_CRITERIA_ID = PPA.ANALYSIS_CRITERIA_ID
AND PAC.ID_FLEX_NUM = PPA.ID_FLEX_NUM
AND PAC.ID_FLEX_NUM = FIFL.ID_FLEX_NUM
AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND FIFL.ID_FLEX_STRUCTURE_CODE = 'RAR_SPECIAL_INFORMATION'
AND PAPF.PERSON_ID=PAAF.PERSON_ID
AND PAAF.PRIMARY_FLAG='Y'
AND TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PJ.JOB_ID(+)=PAAF.JOB_ID
--GROUP by NAME , PAPF.PERSON_ID , PAPF.EMPLOYEE_NUMBER , PAPF.FULL_NAME , PAC.SEGMENT10 , PAPF.EFFECTIVE_START_DATE
order by 1