VIEW: APPS.PA_RCV_EXCEPTIONS_SUM_V

Source

View Text - Preformatted

SELECT PODIST.project_id , PODIST.task_id , rcv.transaction_id , rcv.shipment_header_id , rcv.vendor_id , rcv.CURRENCY_CODE , RCV_SUB.ENTERED_DR , rcv_sub.functional_currency_code , RCV_SUB.ACCOUNTED_DR , rcv.CURRENCY_CONVERSION_DATE , rcv.CURRENCY_CONVERSION_TYPE , rcv.CURRENCY_CONVERSION_RATE , rcv_sub.PA_ADDITION_FLAG EXCEPTION_CODE , PODIST.EXPENDITURE_ORGANIZATION_ID , PODIST.expenditure_type , PODIST.EXPENDITURE_ITEM_DATE , RCV.TRANSACTION_DATE GL_DATE , pa_utils.get_pa_date(PODIST.expenditure_item_date, RCV.TRANSACTION_DATE) PA_DATE , pa_utils.get_pa_period_name(PODIST.expenditure_item_date, RCV.TRANSACTION_DATE) PERIOD_NAME , RCV_SUB.PERIOD_NAME GL_PERIOD_NAME , PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('RECEIPT_EXCP', rcv_sub.PA_ADDITION_FLAG,NULL,'R') EXCEPTION_REASON , PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('RECEIPT_EXCP', rcv_sub.PA_ADDITION_FLAG,NULL,'A') CORRECTIVE_ACTION , PODIST.ORG_ID , IMP.SAME_PA_GL_PERIOD , PA_EXPENDITURES_UTILS.getorgtlname(PODIST.ORG_ID) , imp.set_of_books_id , rcv.po_header_id , rcv.po_line_id , rcv.po_line_location_id , podist.po_distribution_id , podist.distribution_num FROM PO_DISTRIBUTIONS_ALL PODIST, RCV_TRANSACTIONS RCV, PA_IMPLEMENTATIONS_ALL IMP, RCV_RECEIVING_SUB_LEDGER rcv_sub, RCV_SHIPMENT_HEADERS rcv_hdr WHERE rcv_sub.pa_addition_flag in ('N', 'I') AND ((rcv.destination_type_code='EXPENSE' AND rcv.transaction_type <> 'RETURN TO RECEIVING' AND rcv_sub.entered_dr is NOT NULL) OR (rcv.destination_type_code = 'RECEIVING' and rcv.transaction_type in ('RETURN TO RECEIVING' , 'RETURN TO VENDOR') AND rcv_sub.entered_cr is NOT NULL) ) AND PODIST.project_ID IS NOT NULL AND PODIST.destination_type_code ='EXPENSE' AND rcv_sub.ACTUAL_FLAG = 'A' AND PODIST.accrue_on_receipt_flag= 'Y' AND rcv.transaction_id=rcv_sub.rcv_transaction_id AND rcv_sub.code_combination_id = PODIST.code_combination_id AND NVL(PODIST.ORG_ID,-99) = IMP.ORG_ID AND rcv.PO_DISTRIBUTION_ID=PODIST.PO_DISTribution_id AND RCV.shipment_header_id = rcv_hdr.shipment_header_id

View Text - HTML Formatted

SELECT PODIST.PROJECT_ID
, PODIST.TASK_ID
, RCV.TRANSACTION_ID
, RCV.SHIPMENT_HEADER_ID
, RCV.VENDOR_ID
, RCV.CURRENCY_CODE
, RCV_SUB.ENTERED_DR
, RCV_SUB.FUNCTIONAL_CURRENCY_CODE
, RCV_SUB.ACCOUNTED_DR
, RCV.CURRENCY_CONVERSION_DATE
, RCV.CURRENCY_CONVERSION_TYPE
, RCV.CURRENCY_CONVERSION_RATE
, RCV_SUB.PA_ADDITION_FLAG EXCEPTION_CODE
, PODIST.EXPENDITURE_ORGANIZATION_ID
, PODIST.EXPENDITURE_TYPE
, PODIST.EXPENDITURE_ITEM_DATE
, RCV.TRANSACTION_DATE GL_DATE
, PA_UTILS.GET_PA_DATE(PODIST.EXPENDITURE_ITEM_DATE
, RCV.TRANSACTION_DATE) PA_DATE
, PA_UTILS.GET_PA_PERIOD_NAME(PODIST.EXPENDITURE_ITEM_DATE
, RCV.TRANSACTION_DATE) PERIOD_NAME
, RCV_SUB.PERIOD_NAME GL_PERIOD_NAME
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('RECEIPT_EXCP'
, RCV_SUB.PA_ADDITION_FLAG
, NULL
, 'R') EXCEPTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('RECEIPT_EXCP'
, RCV_SUB.PA_ADDITION_FLAG
, NULL
, 'A') CORRECTIVE_ACTION
, PODIST.ORG_ID
, IMP.SAME_PA_GL_PERIOD
, PA_EXPENDITURES_UTILS.GETORGTLNAME(PODIST.ORG_ID)
, IMP.SET_OF_BOOKS_ID
, RCV.PO_HEADER_ID
, RCV.PO_LINE_ID
, RCV.PO_LINE_LOCATION_ID
, PODIST.PO_DISTRIBUTION_ID
, PODIST.DISTRIBUTION_NUM
FROM PO_DISTRIBUTIONS_ALL PODIST
, RCV_TRANSACTIONS RCV
, PA_IMPLEMENTATIONS_ALL IMP
, RCV_RECEIVING_SUB_LEDGER RCV_SUB
, RCV_SHIPMENT_HEADERS RCV_HDR
WHERE RCV_SUB.PA_ADDITION_FLAG IN ('N'
, 'I')
AND ((RCV.DESTINATION_TYPE_CODE='EXPENSE'
AND RCV.TRANSACTION_TYPE <> 'RETURN TO RECEIVING'
AND RCV_SUB.ENTERED_DR IS NOT NULL) OR (RCV.DESTINATION_TYPE_CODE = 'RECEIVING'
AND RCV.TRANSACTION_TYPE IN ('RETURN TO RECEIVING'
, 'RETURN TO VENDOR')
AND RCV_SUB.ENTERED_CR IS NOT NULL) )
AND PODIST.PROJECT_ID IS NOT NULL
AND PODIST.DESTINATION_TYPE_CODE ='EXPENSE'
AND RCV_SUB.ACTUAL_FLAG = 'A'
AND PODIST.ACCRUE_ON_RECEIPT_FLAG= 'Y'
AND RCV.TRANSACTION_ID=RCV_SUB.RCV_TRANSACTION_ID
AND RCV_SUB.CODE_COMBINATION_ID = PODIST.CODE_COMBINATION_ID
AND NVL(PODIST.ORG_ID
, -99) = IMP.ORG_ID
AND RCV.PO_DISTRIBUTION_ID=PODIST.PO_DISTRIBUTION_ID
AND RCV.SHIPMENT_HEADER_ID = RCV_HDR.SHIPMENT_HEADER_ID