Overview: This SQL query is basically use for fetching voucher number based on PO, Receipt, Work confirmation in Oracle R12 Appliation.
SELECT SHIPMENT_HEADER_ID,
po_number,
CREATION_DATE,
EXPECTED_RECEIPT_DATE,
WC_NUM,
receipt_num,
VOUCHER_NUM,
invoice_num,
APPROVAL_STATUS,
APPROVED_DATE,
PENDING_WITH,
STATUS_CODE,
amount_received,
Rec_line_desc,
Vendor_Name,
PERIOD_FROM,
PERIOD_TO,
Comments_header
,PERFORMANCE_PERIOD_FROM
,PERFORMANCE_PERIOD_TO
FROM (
select DISTINCT
rsh.SHIPMENT_HEADER_ID,
poh.segment1 po_number,
rsh.CREATION_DATE,
rsh.EXPECTED_RECEIPT_DATE,
rsh.SHIPMENT_NUM WC_NUM,
rsl.SHIPMENT_LINE_ID
,(select DOC_SEQUENCE_VALUE from ap_invoices_all where invoice_id = ail.invoice_id)VOUCHER_NUM --------added
,(select invoice_NUM from ap_invoices_all where invoice_id = ail.invoice_id)invoice_NUM --------added
,(select action_date from po_action_history where sequence_num = (SELECT MAX(sequence_num) FROM po_action_history WHERE OBJECT_ID=RSH.SHIPMENT_HEADER_ID)AND object_id=RSH.SHIPMENT_HEADER_ID)APPROVED_DATE
,rsh.receipt_num
,nvl(rsh.APPROVAL_STATUS,'PENDING')APPROVAL_STATUS,
(SELECT FULL_NAME
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = (SELECT EMPLOYEE_ID
FROM PO_ACTION_HISTORY
WHERE OBJECT_ID = RSH.SHIPMENT_HEADER_ID
AND ACTION_CODE IS NULL
AND ACTION_CODE IS NULL)
AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE) PENDING_WITH
, SHIPMENT_LINE_STATUS_CODE STATUS_CODE,
rsl.amount_received,
rsl.item_description Rec_line_desc,
(select vendor_name from po_vendors where vendor_id = rsh.vendor_id) Vendor_Name,
rsh.performance_period_from PERIOD_FROM,
rsh.performance_period_to PERIOD_TO,
rsh.COMMENTS Comments_header
,rsh.PERFORMANCE_PERIOD_FROM
,rsh.PERFORMANCE_PERIOD_TO
from rcv_shipment_headers rsh
,rcv_shipment_lines rsl
, po_headers_all poh
,ap_invoice_lines_all ail -----------added
where rsh.SHIPMENT_HEADER_ID=rsl.SHIPMENT_HEADER_ID
and rsl.po_header_id=poh.po_header_id
and ail.RCV_TRANSACTION_ID = rsh.SHIPMENT_HEADER_ID ----added
and NVL(rsl.SHIPMENT_LINE_STATUS_CODE,'A')<>'CANCELLED'
and ASN_TYPE = 'WC'
and ail.LINE_TYPE_LOOKUP_CODE = 'ITEM'
and ail.LINE_SOURCE = 'HEADER MATCH'
--AND ail.invoice_id=26485
order by po_number)
WHERE APPROVAL_STATUS = DECODE(:P_STATUS,'Pending','PENDING','Approved','APPROVED','Rejected','REJECTED','All',APPROVAL_STATUS)
AND CREATION_DATE BETWEEN NVL(:P_FROM_DATE,CREATION_DATE) AND NVL(:P_TO_DATE,CREATION_DATE)
AND VENDOR_NAME = nvl(:P_VENDOR_NAME,VENDOR_NAME)
and PERFORMANCE_PERIOD_TO = nvl(:P_PER_PERIOD_TO,PERFORMANCE_PERIOD_TO)
and PERFORMANCE_PERIOD_FROM = nvl(:P_PER_PERIOD_FROM,PERFORMANCE_PERIOD_FROM)
UNION
SELECT SHIPMENT_HEADER_ID,
po_number,
CREATION_DATE,
EXPECTED_RECEIPT_DATE,
WC_NUM,
receipt_num,
VOUCHER_NUM, -------------added
invoice_NUM,
APPROVAL_STATUS,
APPROVED_DATE,
PENDING_WITH,
STATUS_CODE,
amount_received,
Rec_line_desc,
Vendor_Name,
PERIOD_FROM,
PERIOD_TO,
Comments_header
,PERFORMANCE_PERIOD_FROM
,PERFORMANCE_PERIOD_TO
FROM (
select DISTINCT
rsh.SHIPMENT_HEADER_ID,
poh.segment1 po_number,
rsh.CREATION_DATE,
rsh.EXPECTED_RECEIPT_DATE,
rsh.SHIPMENT_NUM WC_NUM,
null SHIPMENT_LINE_ID
,null VOUCHER_NUM --------added
,null invoice_NUM
,(select action_date from po_action_history where sequence_num = (SELECT MAX(sequence_num) FROM po_action_history WHERE OBJECT_ID=RSH.SHIPMENT_HEADER_ID)AND object_id=RSH.SHIPMENT_HEADER_ID)APPROVED_DATE
,rsh.receipt_num
,nvl(rsh.APPROVAL_STATUS,'PENDING')APPROVAL_STATUS,
(SELECT FULL_NAME
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = (SELECT EMPLOYEE_ID
FROM PO_ACTION_HISTORY
WHERE OBJECT_ID = RSH.SHIPMENT_HEADER_ID
AND ACTION_CODE IS NULL
AND ACTION_CODE IS NULL)
AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE) PENDING_WITH
, SHIPMENT_LINE_STATUS_CODE STATUS_CODE,
rsl.amount_received,
rsl.item_description Rec_line_desc,
(select vendor_name from po_vendors where vendor_id = rsh.vendor_id) Vendor_Name,
rsh.performance_period_from PERIOD_FROM,
rsh.performance_period_to PERIOD_TO,
rsh.COMMENTS Comments_header
,rsh.PERFORMANCE_PERIOD_FROM
,rsh.PERFORMANCE_PERIOD_TO
from rcv_shipment_headers rsh
,rcv_shipment_lines rsl
, po_headers_all poh
-- ,ap_invoice_lines_all ail -----------added
where rsh.SHIPMENT_HEADER_ID=rsl.SHIPMENT_HEADER_ID
and rsl.po_header_id=poh.po_header_id
--and ail.RCV_TRANSACTION_ID = rsh.SHIPMENT_HEADER_ID ----added
and NVL(rsl.SHIPMENT_LINE_STATUS_CODE,'A')<>'CANCELLED'
and ASN_TYPE = 'WC'
--and poh.segment1='500360'
order by po_number)
WHERE APPROVAL_STATUS = DECODE(:P_STATUS,'Pending','PENDING','Approved','APPROVED','Rejected','REJECTED','All',APPROVAL_STATUS)
AND CREATION_DATE BETWEEN NVL(:P_FROM_DATE,CREATION_DATE) AND NVL(:P_TO_DATE,CREATION_DATE)
AND VENDOR_NAME = nvl(:P_VENDOR_NAME,VENDOR_NAME)
and PERFORMANCE_PERIOD_TO = nvl(:P_PER_PERIOD_TO,PERFORMANCE_PERIOD_TO)
and PERFORMANCE_PERIOD_FROM = nvl(:P_PER_PERIOD_FROM,PERFORMANCE_PERIOD_FROM);