Search 800 + Posts

Dec 18, 2017

Query to extract Contingent Worker Time Sheets Not Getting Picked-up By Pay On Receipt Auto Invoice Program

SELECT PPF.FULL_NAME,
PPF.EMPLOYEE_NUMBER,
PPF.PERSON_ID,
HXCTS.RESOURCE_ID,
HXCTS.APPROVAL_STATUS "Timecard Status",
HXCTS.TRANSFERRED_TO,
HXCTS.START_TIME,
HXCTS.STOP_TIME,
PH.SEGMENT1 "Purchase Order Num",
RSH.RECEIPT_NUM "Receipt Num",
PL.LINE_NUM "PO Line Num" ,
PLL.SHIPMENT_NUM "PO Shipment Num" ,
PLL.AMOUNT,
PLL.AMOUNT_RECEIVED,
PLL.AMOUNT_BILLED,
RT.TRANSACTION_ID,
RT. TRANSACTION_TYPE ,
RT.TIMECARD_ID ,
RT.TIMECARD_OVN ,
PAP.SEGMENT1 "Project Number" ,
PAT.TASK_NUMBER "Task Number" ,
RT.PROJECT_ID ,
RT.TASK_ID ,
HXCTS.RECORDED_HOURS ,
RT.CREATION_DATE ,
RT.INVOICE_STATUS_CODE ,
RSSL.PA_ADDITION_FLAG
FROM APPS.RCV_SHIPMENT_HEADERS RSH,
APPS.RCV_SHIPMENT_LINES RSL,
APPS.RCV_TRANSACTIONS RT,
APPS.PO_HEADERS_ALL PH,
APPS.PO_LINES_ALL PL,
APPS.PO_LINE_LOCATIONS_ALL PLL,
APPS.HXC_TIMECARD_SUMMARY HXCTS,
APPS.PER_ALL_PEOPLE_F PPF ,
APPS.PA_PROJECTS_ALL PAP ,
APPS.PA_TASKS PAT,
APPS.RCV_RECEIVING_SUB_LEDGER RSSL
WHERE PH.SEGMENT1=&PO_NUMBER       << to be entered
AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PL.PO_LINE_ID = PLL.PO_LINE_ID
AND RT.PO_LINE_LOCATION_ID =PLL.LINE_LOCATION_ID
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID = RSSL.RCV_TRANSACTION_ID
AND NVL (RT.PO_RELEASE_ID,0) = 0
AND PAT.PROJECT_ID=PAP.PROJECT_ID
AND PAP.PROJECT_ID= RT.PROJECT_ID
AND PAT.TASK_ID =RT.TASK_ID
AND PPF.PERSON_ID =HXCTS.RESOURCE_ID
AND TRUNC(HXCTS.START_TIME) BETWEEN TRUNC(PPF.EFFECTIVE_START_DATE) AND TRUNC(PPF.EFFECTIVE_END_DATE)
AND RT.TIMECARD_ID=HXCTS.TIMECARD_ID
AND not exists (select 'existing invoice' from ap_invoice_distributions_all aid
where aid.rcv_transaction_id = rt.transaction_id)
ORDER BY HXCTS.START_TIME,
HXCTS.STOP_TIME,
RSH.RECEIPT_NUM,
RSL.SHIPMENT_LINE_ID,
RT.TRANSACTION_ID;

No comments:

Post a Comment