VIEW: APPS.PJM_PROJECT_BLANKET_PO_V

Source

View Text - Preformatted

SELECT DISTINCT DIST.PROJECT_ID , DIST.TASK_ID , POR.PO_HEADER_ID , POH.SEGMENT1 , pjm_inquiry.br_type(por.org_id, POR.RELEASE_TYPE) , POR.PO_RELEASE_ID , POR.RELEASE_NUM , POL.LINE_NUM , POLL.SHIPMENT_NUM , DIST.DISTRIBUTION_NUM , POR.REVISION_NUM , POR.REVISED_DATE , POR.RELEASE_DATE , POR.PRINTED_DATE , pjm_inquiry.get_lookup(NVL(POR.AUTHORIZATION_STATUS, 'INCOMPLETE'), 'AUTHORIZATION STATUS') STATUS , pjm_inquiry.get_lookup(NVL(POR.CLOSED_CODE, 'OPEN'), 'DOCUMENT STATE') CLOSURE_STATUS , POH.VENDOR_ID , pjm_inquiry.vendor_name(poh.vendor_id) , POH.VENDOR_CONTACT_ID , pjm_inquiry.vendor_name(POH.VENDOR_CONTACT_ID) , POH.VENDOR_SITE_ID , pjm_inquiry.vendor_site(poh.vendor_id, poh.vendor_site_id) , POH.SHIP_TO_LOCATION_ID , pjm_inquiry.location_code(POH.SHIP_TO_LOCATION_ID) , POH.SHIP_VIA_LOOKUP_CODE , pjm_inquiry.get_lookup(POH.FREIGHT_TERMS_LOOKUP_CODE, 'FREIGHT TERMS') , pjm_inquiry.get_lookup(POH.FOB_LOOKUP_CODE, 'FOB') , POR.AGENT_ID , PO_INQ_SV.GET_PERSON_NAME(POR.AGENT_ID) , POH.BILL_TO_LOCATION_ID , pjm_inquiry.location_code(POH.BILL_TO_LOCATION_ID) , POH.TERMS_ID , pjm_inquiry.payment_term(POH.TERMS_ID) , POH.CURRENCY_CODE , TO_CHAR( DECODE(POL.MATCHING_BASIS, 'QUANTITY', DIST.QUANTITY_ORDERED * POLL.PRICE_OVERRIDE, 'AMOUNT', DIST.AMOUNT_ORDERED), FND_CURRENCY_CACHE.GET_FORMAT_MASK( POH.CURRENCY_CODE , 40 ) ) AMOUNT , pjm_inquiry.item_number(POL.ITEM_ID,DIST.DESTINATION_ORGANIZATION_ID) , POL.ITEM_DESCRIPTION , DIST.END_ITEM_UNIT_NUMBER , POH.ORG_ID FROM PO_RELEASES_ALL POR , PO_HEADERS_ALL POH , PO_LINES_ALL POL , PO_LINE_LOCATIONS_ALL POLL , PO_DISTRIBUTIONS_ALL DIST WHERE POH.PO_HEADER_ID = POR.PO_HEADER_ID AND POH.TYPE_LOOKUP_CODE = 'BLANKET' AND POR.APPROVED_FLAG IN ( 'Y' , 'R' ) AND DIST.PO_HEADER_ID = POH.PO_HEADER_ID AND DIST.PO_RELEASE_ID = POR.PO_RELEASE_ID AND DIST.PO_LINE_ID = POL.PO_LINE_ID AND POH.PO_HEADER_ID = POL.PO_HEADER_ID AND DIST.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND POL.PO_LINE_ID = POLL.PO_LINE_ID AND DIST.ORG_ID = POL.ORG_ID AND POL.ITEM_ID IS NOT NULL

View Text - HTML Formatted

SELECT DISTINCT DIST.PROJECT_ID
, DIST.TASK_ID
, POR.PO_HEADER_ID
, POH.SEGMENT1
, PJM_INQUIRY.BR_TYPE(POR.ORG_ID
, POR.RELEASE_TYPE)
, POR.PO_RELEASE_ID
, POR.RELEASE_NUM
, POL.LINE_NUM
, POLL.SHIPMENT_NUM
, DIST.DISTRIBUTION_NUM
, POR.REVISION_NUM
, POR.REVISED_DATE
, POR.RELEASE_DATE
, POR.PRINTED_DATE
, PJM_INQUIRY.GET_LOOKUP(NVL(POR.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, 'AUTHORIZATION STATUS') STATUS
, PJM_INQUIRY.GET_LOOKUP(NVL(POR.CLOSED_CODE
, 'OPEN')
, 'DOCUMENT STATE') CLOSURE_STATUS
, POH.VENDOR_ID
, PJM_INQUIRY.VENDOR_NAME(POH.VENDOR_ID)
, POH.VENDOR_CONTACT_ID
, PJM_INQUIRY.VENDOR_NAME(POH.VENDOR_CONTACT_ID)
, POH.VENDOR_SITE_ID
, PJM_INQUIRY.VENDOR_SITE(POH.VENDOR_ID
, POH.VENDOR_SITE_ID)
, POH.SHIP_TO_LOCATION_ID
, PJM_INQUIRY.LOCATION_CODE(POH.SHIP_TO_LOCATION_ID)
, POH.SHIP_VIA_LOOKUP_CODE
, PJM_INQUIRY.GET_LOOKUP(POH.FREIGHT_TERMS_LOOKUP_CODE
, 'FREIGHT TERMS')
, PJM_INQUIRY.GET_LOOKUP(POH.FOB_LOOKUP_CODE
, 'FOB')
, POR.AGENT_ID
, PO_INQ_SV.GET_PERSON_NAME(POR.AGENT_ID)
, POH.BILL_TO_LOCATION_ID
, PJM_INQUIRY.LOCATION_CODE(POH.BILL_TO_LOCATION_ID)
, POH.TERMS_ID
, PJM_INQUIRY.PAYMENT_TERM(POH.TERMS_ID)
, POH.CURRENCY_CODE
, TO_CHAR( DECODE(POL.MATCHING_BASIS
, 'QUANTITY'
, DIST.QUANTITY_ORDERED * POLL.PRICE_OVERRIDE
, 'AMOUNT'
, DIST.AMOUNT_ORDERED)
, FND_CURRENCY_CACHE.GET_FORMAT_MASK( POH.CURRENCY_CODE
, 40 ) ) AMOUNT
, PJM_INQUIRY.ITEM_NUMBER(POL.ITEM_ID
, DIST.DESTINATION_ORGANIZATION_ID)
, POL.ITEM_DESCRIPTION
, DIST.END_ITEM_UNIT_NUMBER
, POH.ORG_ID
FROM PO_RELEASES_ALL POR
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL POLL
, PO_DISTRIBUTIONS_ALL DIST
WHERE POH.PO_HEADER_ID = POR.PO_HEADER_ID
AND POH.TYPE_LOOKUP_CODE = 'BLANKET'
AND POR.APPROVED_FLAG IN ( 'Y'
, 'R' )
AND DIST.PO_HEADER_ID = POH.PO_HEADER_ID
AND DIST.PO_RELEASE_ID = POR.PO_RELEASE_ID
AND DIST.PO_LINE_ID = POL.PO_LINE_ID
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND DIST.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND POL.PO_LINE_ID = POLL.PO_LINE_ID
AND DIST.ORG_ID = POL.ORG_ID
AND POL.ITEM_ID IS NOT NULL