<수정필요>
PR PO 통합 정보
— PR Infomation — by oracle — View Name : CCKBC_POPRINFO_V select PRH.segment1 PR_Number, PRH.REQUISITION_HEADER_ID PR_Header_ID , PRH.PREPARER_ID, PRH.TYPE_LOOKUP_CODE PR_TYPE , PRH.LAST_UPDATE_DATE LAST_DATE , PRH.ATTRIBUTE_CATEGORY CES_Category, PRH.ATTRIBUTE1 CES_Number , PRL.REQUISITION_LINE_ID , PRL.LINE_NUM, PRL.LINE_TYPE_ID , PRL.TO_PERSON_ID Requester, PRL.ITEM_DESCRIPTION , PRL.QUANTITY, PRL.UNIT_MEAS_LOOKUP_CODE, PRL.UNIT_PRICE , PRD.DISTRIBUTION_ID , PRD.REQ_LINE_QUANTITY, PRD.DISTRIBUTION_NUM from po_requisition_headers_all PRH , po_requisition_lines_all PRL , po_req_distributions_all PRD where PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID — PRH and PRL Join and PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID — PRL and PRD Join
— PO Information — by oracle — View Name : CCKBC_POPOINFO_V select POH.SEGMENT1 PO_Number, POH.PO_HEADER_ID , POH.AGENT_ID Buyer, POH.TYPE_LOOKUP_CODE PO_TYPE , POH.LAST_UPDATE_DATE LAST_DATE , POH.VENDOR_ID, POH.VENDOR_SITE_ID , POH.CURRENCY_CODE, POH.REVISION_NUM , POL.PO_LINE_ID , POL.LINE_NUM, POL.LINE_TYPE_ID , POL.ITEM_ID, POL.ITEM_DESCRIPTION , POL.QUANTITY, POL.UNIT_MEAS_LOOKUP_CODE , POL.LIST_PRICE_PER_UNIT, POL.UNIT_PRICE , POD.PO_DISTRIBUTION_ID, POD.PO_RELEASE_ID , POD.QUANTITY_ORDERED, POD.QUANTITY_DELIVERED , POD.REQ_DISTRIBUTION_ID — PR Join Column from po_headers_all POH , po_Lines_all POL , po_distributions_all POD where POH.PO_HEADER_ID = POL.PO_HEADER_ID and POL.PO_LINE_ID = POD.PO_LINE_ID
— RCV Information — by oracle — View Name : CCKBC_PORCVINFO_V — select * from CCKBC_PORCVINFO_V select Headers.receipt_num RCV_NUM , Lines.quantity_received RCV_QUANTITY, Lines.LAST_UPDATE_DATE RCV_DATE , Lines.SHIPMENT_LINE_STATUS_CODE RVC_STATUS , Lines.PO_HEADER_ID, Lines.PO_LINE_ID, Lines.PO_LINE_LOCATION_ID , Lines.PO_DISTRIBUTION_ID from rcv_shipment_lines Lines, rcv_shipment_headers Headers where Lines.shipment_header_id = Headers.shipment_header_id ORDER BY RECEIPT_NUM
select Headers.receipt_num, Lines.PO_DISTRIBUTION_ID , Lines.* from rcv_shipment_lines Lines, rcv_shipment_headers Headers where Lines.shipment_header_id = Headers.shipment_header_id –ORDER BY RECEIPT_NUM ORDER BY Lines.PO_DISTRIBUTION_ID
select * from rcv_shipment_headers Headers
— PR and PO Join select * from CCKBC_POPRINFO_V order by pr_number — 65 Count select * from CCKBC_POPOINFO_V order by po_number — 52 Count select * from PO_releases_all — 3 count select * from rcv_shipment_lines — 33 Count select * from rcv_shipment_headers select * from Hr_Employees select * from rcv_shipment_lines where po_distribution_id = ’26’
select PR.*, PO.* , PO_REL.PO_RELEASE_ID, PO_REL.release_num , PO_REL.AUTHORIZATION_STATUS, PO_REL.RELEASE_TYPE , rcv.* , VEND.vendor_name , HR_P.full_name Preparer , HR_R.full_name Requester , HR_P.full_name Buyer from CCKBC_POPRINFO_V PR , CCKBC_POPOINFO_V PO , PO_releases_all PO_REL , CCKBC_PORCVINFO_V RCV , PO_VENDORS VEND , Hr_Employees HR_P , Hr_Employees HR_R , Hr_Employees HR_B where PO.REQ_DISTRIBUTION_ID = PR.DISTRIBUTION_ID(+) and PO.PO_RELEASE_ID = PO_REL.PO_RELEASE_ID(+) and PO.po_distribution_id = RCV.po_distribution_id(+) and PO.VENDOR_ID = VEND.VENDOR_ID(+) and PR.PREPARER_ID = HR_P.employee_id(+) and PR.Requester_ID = HR_R.employee_id(+) and PO.BUYER_ID = HR_B.employee_id(+) ORDER BY po_number
— Report — ROWNUM — View name : CCKBC_POTOTAL_V select — PR Information PR.PR_NUMBER PR_NUM, PR.LINE_NUM PR_LINE_NUM , PR.PR_TYPE PR_TYPE, PR.LAST_DATE PR_DATE , PO.Item_id ITEM_CODE, PO.Item_Description Description , PR.AUTHORIZATION_STATUS PR_STATUS, PR.REQ_LINE_QUANTITY PR_QUANTITY , PR.UNIT_MEAS_LOOKUP_CODE UOM , HR_P.full_name Preparer , HR_R.full_name Requester — PO Information , PO.PO_NUMBER PO_NUM, PO.LINE_NUM PO_LINE_NUM , PO.PO_TYPE PO_TYPE, PO.LAST_DATE PO_DATE , PO.AUTHORIZATION_STATUS PO_STATUS, HR_P.full_name Buyer , PO.QUANTITY_ORDERED PO_QUANTITY_ORDERED, PO.QUANTITY_DELIVERED PO_QUANTITY_DELIVERED , PO.CLOSED_CODE CLOSED_STATUS
— PO Release Information , PO_REL.release_num PO_release_num, PO_REL.RELEASE_TYPE , PO_REL.AUTHORIZATION_STATUS PO_RELEASE_STATUS
— Supplier Information , VEND.vendor_name Supplier — Amount Information , PR.UNIT_PRICE PR_UNIT_PRICE , PO.LIST_PRICE_PER_UNIT, PO.UNIT_PRICE PO_UNIT_PRICE , PO.CURRENCY_CODE Currency
— Receipt Information , RCV.RCV_NUM, RCV.RCV_DATE , RCV.RCV_QUANTITY RCV_STATUS — CES Information , PR.CES_NUMBER CES_NUM , PR.CES_CATEGORY CES_CATEGORY
— Check , PR.pr_header_id, PR.pr_LINE_ID , PR.DISTRIBUTION_ID , PO.PO_HEADER_ID, PO.PO_LINE_ID , PO.PO_DISTRIBUTION_ID, PO.req_distribution_id from CCKBC_POPRINFO_V PR , CCKBC_POPOINFO_V PO , PO_releases_all PO_REL , CCKBC_PORCVINFO_V RCV , PO_VENDORS VEND , Hr_Employees HR_P , Hr_Employees HR_R , Hr_Employees HR_B where PO.REQ_DISTRIBUTION_ID(+) = PR.DISTRIBUTION_ID and PO.PO_RELEASE_ID = PO_REL.PO_RELEASE_ID(+) and PO.po_distribution_id = RCV.po_distribution_id(+) and PO.VENDOR_ID = VEND.VENDOR_ID(+) and PR.PREPARER_ID = HR_P.employee_id(+) and PR.Requester_ID = HR_R.employee_id(+) and PO.BUYER_ID = HR_B.employee_id(+)
ORDER BY PR_NUM, PR_LINE_NUM –ORDER BY PO_NUM, PO_LINE_NUM
Comments