Oracle ASL Extraction Script R12

-- Item ASL

SELECT
  DECODE(NVL(MTL.SEGMENT1,'COMMODITY'),'COMMODITY','COMMODITY','ITEM') TYPE,
  MC.CONCATENATED_SEGMENTS COMMODITY,
  MTL.SEGMENT1 ITEM,
  MTL.DESCRIPTION DESCRIPTION,
  PASL.VENDOR_BUSINESS_TYPE,
  PV.VENDOR_NAME,
  PVS.VENDOR_SITE_CODE,
  OU.NAME OPERATING_UNIT,
  DECODE(PASL.USING_ORGANIZATION_ID, -1, 'Y','N') GLOBAL,
  HR.organization_code owning_org,
  PASL.PRIMARY_VENDOR_ITEM Supplier_Item,
  PASL.COMMENTS,
  PAA.PURCHASING_UNIT_OF_MEASURE,
  PAD.DOCUMENT_TYPE_CODE,
  POH.SEGMENT1 DOCUMENT_NUM,
  --POH.authorization_status,
  POL.LINE_NUM DOCUMENT_LINE_NUM,
  PAA.CONSIGNED_FROM_SUPPLIER_FLAG,
  PAA.CONSUME_ON_AGING_FLAG,
  PAA.AGING_PERIOD
FROM
APPS.PO_APPROVED_SUPPLIER_LIST PASL,
APPS.MTL_SYSTEM_ITEMS_B MTL,
APPS.MTL_CATEGORIES_KFV MC,
APPS.PO_VENDORS PV ,
APPS.PO_VENDOR_SITES_ALL PVS ,
APPS.PO_ASL_STATUSES PAST ,
APPS.PO_ASL_ATTRIBUTES PAA ,
APPS.PO_ASL_DOCUMENTS PAD,
APPS.HR_ORGANIZATION_UNITS OU,
APPS.ORG_ORGANIZATION_DEFINITIONS HR,
APPS.PO_HEADERS_ALL POH,
APPS.PO_LINES_ALL POL
WHERE
 MTL.INVENTORY_ITEM_ID                                       = PASL.ITEM_ID
AND MTL.ORGANIZATION_ID                                      = PASL.OWNING_ORGANIZATION_ID
AND MC.CATEGORY_ID (+)                                       = PASL.CATEGORY_ID
AND HR.OPERATING_UNIT                                        = OU.ORGANIZATION_ID
AND HR.ORGANIZATION_ID                                       = PASL.OWNING_ORGANIZATION_ID
AND PVS.VENDOR_SITE_ID(+)                                    = PASL.VENDOR_SITE_ID                                    
/*AND ((PASL.VENDOR_SITE_ID                                   IS NOT NULL
AND PVS.VENDOR_SITE_CODE                                    IS NOT NULL)
OR (PASL.VENDOR_SITE_ID                                     IS NULL
AND PVS.VENDOR_SITE_CODE                                    IS NULL))*/
AND PV.VENDOR_ID(+)                                          = PASL.VENDOR_ID                                          
AND PASL.USING_ORGANIZATION_ID                               = PAA.USING_ORGANIZATION_ID
--AND NVL(PAA.CONSIGNED_FROM_SUPPLIER_FLAG,'N')                ='N'
--AND PASL.ASL_STATUS_ID=2
AND PASL.ASL_STATUS_ID                                       = PAST.STATUS_ID
AND PASL.ASL_ID                                              = PAA.ASL_ID
AND PASL.USING_ORGANIZATION_ID                               = PAA.USING_ORGANIZATION_ID
--AND NVL(PASL.DISABLE_FLAG,'N')<>'Y'
AND PAD.ASL_ID(+) = PASL.ASL_ID
AND POH.PO_HEADER_ID(+)                                      = PAD.DOCUMENT_HEADER_ID
AND POL.PO_HEADER_ID(+)                                      = PAD.DOCUMENT_HEADER_ID
AND POL.PO_LINE_ID(+)                                        = PAD.DOCUMENT_LINE_ID
AND( EXISTS (SELECT ASL_ID FROM  
            APPS.PO_ASL_DOCUMENTS PAD,          
            APPS.PO_HEADERS_ALL PH
            WHERE PAD.ASL_ID = PASL.ASL_ID
              --AND PAD.OWNING_ORGANIZATION_ID=PASL.OWNING_ORGANIZATION_ID            
             -- AND PH.AUTHORIZATION_STATUS in ('CLOSED','CANCELLED')
              AND PH.PO_HEADER_ID=PAD.DOCUMENT_HEADER_ID            
              ) OR
   (NOT EXISTS(SELECT ASL_ID FROM   APPS.PO_ASL_DOCUMENTS PAD
            WHERE ASL_ID = PASL.ASL_ID          
            )));

/

-- Commodity ASL

SELECT
  DECODE(NVL(NULL,'COMMODITY'),'COMMODITY','COMMODITY','ITEM') TYPE,
  MC.CONCATENATED_SEGMENTS COMMODITY,
  PASL.VENDOR_BUSINESS_TYPE,
  PV.VENDOR_NAME,
  PVS.VENDOR_SITE_CODE,
  OU.NAME OPERATING_UNIT,
  DECODE(PASL.USING_ORGANIZATION_ID, -1, 'Y','N') GLOBAL,
  HR.organization_code owning_org,
  PASL.PRIMARY_VENDOR_ITEM Supplier_Item,
  PASL.COMMENTS,
  PAA.PURCHASING_UNIT_OF_MEASURE,
 PAA.CONSIGNED_FROM_SUPPLIER_FLAG,
  PAA.CONSUME_ON_AGING_FLAG,
  PAA.AGING_PERIOD
FROM
APPS.PO_APPROVED_SUPPLIER_LIST PASL,
APPS.MTL_CATEGORIES_KFV MC,
APPS.PO_VENDORS PV ,
APPS.PO_VENDOR_SITES_ALL PVS ,
APPS.PO_ASL_STATUSES PAST ,
APPS.PO_ASL_ATTRIBUTES PAA ,
APPS.HR_ORGANIZATION_UNITS OU,
APPS.ORG_ORGANIZATION_DEFINITIONS HR
WHERE 1=1
AND MC.CATEGORY_ID (+)                                       = PASL.CATEGORY_ID
AND HR.OPERATING_UNIT                                        = OU.ORGANIZATION_ID
AND HR.ORGANIZATION_ID                                       = PASL.OWNING_ORGANIZATION_ID
AND PVS.VENDOR_SITE_ID(+)                                    = PASL.VENDOR_SITE_ID                                    
AND PV.VENDOR_ID(+)                                          = PASL.VENDOR_ID                                          
AND PASL.USING_ORGANIZATION_ID                               = PAA.USING_ORGANIZATION_ID
AND PASL.ASL_STATUS_ID=2
AND PASL.ASL_STATUS_ID                                       = PAST.STATUS_ID
AND PASL.ASL_ID                                              = PAA.ASL_ID
AND PASL.USING_ORGANIZATION_ID                               = PAA.USING_ORGANIZATION_ID
AND PASL.ITEM_ID is  null