AP Inactive Supplier Report List

 

Supplier Ledger (FOR RECONC ONLY) - NG - Cust Rep

Supplier Balance Report-Statement Format - NG - Customized Report

Supplier Balances Group Wise (PMNT) - NG-ICT Customized Report

Supplier Ledger At A Glance - NG - Customized Report

Supplier Balances Group Wise Monthly Report - NG-ICT Customized Report

Supplier Balances Group Wise With Payment Term - NG-ICT Customized Report

Supplier Balances Group Wise(Latest) - NG-ICT Customized Report

Supplier Balances Group Wise (All Unit) - NG-ICT Customized Report

Supplier Balances Unit Wise Monthly Report - NG-ICT Customized Report

Supplier Balances Group Wise - NG Customized Report

How To Create View

 

CREATE or REPLACE VIEW NG_CLUSTER_UNIT_V AS

  SELECT HOU.ORGANIZATION_ID, HOU.NAME, DECODE(HOU.ORGANIZATION_ID, 101, 'Talha Group', 100, 'Zaber Group', '99', 'Zaber Group', '') AS GROUP_NAME

  FROM HR_OPERATING_UNITS HOU

  

  

SELECT * from NG_CLUSTER_UNIT_V

Group Wise Unit

 select * from hr_operating_units hou    where  hou.ORGANIZATION_ID  in (

84,

86,

89,

91,

96,

102

)

Noman: 

org_id in (83

90,

99,

1696,

2265,

2405

)

select * from hr_operating_units hou    where  hou.ORGANIZATION_ID  in (83,90,99,1696,2265,2405)

Talha:

org_id in (

94,

103,

106,

107,

108,

109,

110,

111,

2485

)

select * from hr_operating_units hou    where  hou.ORGANIZATION_ID  in (94,103,106,107,108,109,110,111,2485)

Zaber:

org_id in (

85,

87,

92,

95,

100,

101,

105,

1994

)

select * from hr_operating_units hou    where  hou.ORGANIZATION_ID  in (85,87,92,95,100,101,105,1994)

Zubair:

org_id in (

84,

86,

89,

91,

96,

102

)

select * from hr_operating_units hou    where  hou.organization_id  in (84,86,89,91,96,102)

select * from ap_invoices_all aia where aia.org_id

PO (Purchase Order) Data

 -------------------------------------Purchase Order Data---------------------------------------------------------------

SELECT

        DISTINCT

        PHA.PO_HEADER_ID

--       ,RT.SHIPMENT_HEADER_ID    

       ,PHA.SEGMENT1 PO_NO

--       ,PHA.RATE    CURRENCY_RATE

       ,HOU.NAME OPERATING_UNIT

       ,(SELECT HOUA.NAME FROM HR_ORGANIZATION_UNITS HOUA    WHERE HOUA.ORGANIZATION_ID = RSH.SHIP_TO_ORG_ID ) SHIP_ORG

       ,PHA.SEGMENT1 || ' - ' || PLA.LINE_NUM PO_LINE_NUMBER       

       ,PLA.QUANTITY PO_QUANTITY

       ,PLA.UNIT_PRICE PO_RATE

       ,PLA.QUANTITY * PLA.UNIT_PRICE PO_VALUE

       ,PHA.CREATION_DATE

       ,PLA.ATTRIBUTE1 BRAND

       ,PLA.ATTRIBUTE2 ORIGIN

       ,PV.VENDOR_NAME SUPPLIER_NAME

       ,PVS.VENDOR_SITE_CODE

       ,(SELECT FULL_NAME

                  FROM PER_ALL_PEOPLE_F

                 WHERE PERSON_ID = PHA.AGENT_ID

                   AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)

        BUYER_NAME

       ,PLA.ITEM_DESCRIPTION ITEM_NAME                                                                        

       ,PHA.ORG_ID

       ,PHA.CURRENCY_CODE

       ,DECODE(PHA.CURRENCY_CODE, 'BDT', 1, PHA.RATE) CURRENCY_RATE

       ,PLL.QUANTITY

       ,PLL.QUANTITY_RECEIVED

       ,PLL.QUANTITY_ACCEPTED

       ,(PLL.QUANTITY - PLL.QUANTITY_RECEIVED) DUE

       ,PLL.QUANTITY_REJECTED

       ,PLL.QUANTITY_BILLED

       ,PLL.QUANTITY_CANCELLED

    --   ,RSH.RECEIPT_NUM MRR_NO

       ,LISTAGG(RSH.RECEIPT_NUM, ',') WITHIN GROUP (ORDER BY RSH.RECEIPT_NUM) "MRR_LIST"          

       ,LISTAGG(RSH.CREATION_DATE, ',') WITHIN GROUP (ORDER BY RSH.CREATION_DATE) "MRR_LIST"           

--       ,RSH.CREATION_DATE MRR_DATE

FROM 

        PO_HEADERS_ALL    PHA,

        PO_LINES_ALL      PLA,

        PO_VENDORS        PV,

        HR_OPERATING_UNITS HOU,

        PO_VENDOR_SITES_ALL PVS,

        PO_LINE_LOCATIONS_ALL   PLL,

        RCV_TRANSACTIONS      RT,

        RCV_SHIPMENT_HEADERS    RSH

WHERE 

    PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

AND PHA.VENDOR_ID = PV.VENDOR_ID   

AND PHA.ORG_ID = HOU.ORGANIZATION_ID 

AND PHA.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID

AND PHA.PO_HEADER_ID = PLL.PO_HEADER_ID

AND PLA.PO_LINE_ID = PLL.PO_LINE_ID

AND PHA.PO_HEADER_ID = RT.PO_HEADER_ID(+)

AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID(+)

--AND PHA.PO_HEADER_ID = 86686    

AND PHA.CREATION_DATE BETWEEN '30-apr-2016' AND '30-nov-2020' 

--AND RSH.RECEIPT_NUM = 1862

AND RT.TRANSACTION_TYPE(+) = 'RECEIVE'

AND RT.PO_LINE_ID(+) = PLA.PO_LINE_ID

--AND PVS.VENDOR_SITE_CODE = 'PARTY SUPPLY' 

--AND PHA.ORG_ID IN (85)

--AND PHA.SEGMENT1 = 17

GROUP BY

        PHA.PO_HEADER_ID

       ,PHA.SEGMENT1

       ,HOU.NAME

       ,PLA.LINE_NUM

       ,PLA.QUANTITY

       ,PLA.UNIT_PRICE

       ,PHA.CREATION_DATE

       ,PLA.ATTRIBUTE1 

       ,PLA.ATTRIBUTE2 

       ,PV.VENDOR_NAME 

       ,PVS.VENDOR_SITE_CODE

       ,PLA.ITEM_DESCRIPTION                                                                  

       ,PHA.ORG_ID

       ,PLL.QUANTITY

       ,PLL.QUANTITY_RECEIVED

       ,PLL.QUANTITY_ACCEPTED

       ,PLL.QUANTITY_REJECTED

       ,PLL.QUANTITY_BILLED

       ,PLL.QUANTITY_CANCELLED

       ,RSH.SHIP_TO_ORG_ID 

       ,PHA.AGENT_ID  

       ,PHA.CURRENCY_CODE

       ,PHA.RATE

--    ,RSH.CREATION_DATE    

--       ,RSH.RECEIPT_NUM              

ORDER BY  PO_LINE_NUMBER

--ORDER BY PHA.ORG_ID, PLL.LINE_LOCATION_ID

------------------------------------------------------------------------------------------

Payment Data

  -----------------------------------------Payment Data---------------------------------------------------------------

SELECT

        AIA.INVOICE_TYPE_LOOKUP_CODE,

        ASA.VENDOR_NAME,

        ASA.SEGMENT1                                            VENDOR_NUMBER,

        ASSA.VENDOR_SITE_CODE,

        HOU.NAME                                                OU,      

        ACA.CHECK_ID,

        ACA.CHECK_NUMBER,

        AIA.INVOICE_ID,

        AIA.INVOICE_NUM,

        AILA.LINE_NUMBER,

        AIA.PAY_GROUP_LOOKUP_CODE,

        AILA.ATTRIBUTE6,

        AILA.ATTRIBUTE7,

        AILA.ATTRIBUTE4 LC_NUMBER_DFF,

        AILA.ATTRIBUTE5 COST_FACTOR_DFF,

        (select ffv.description from apps.fnd_flex_values_vl ffv  

        where   ffv.FLEX_VALUE_SET_ID = 1017300

        and ffv.flex_value = aila.ATTRIBUTE5) cost_factor_dff_name,

        --(select ppet.name from  pon_price_element_types_vl ppet

        --where  aila.cost_factor_id = ppet.price_element_type_id) cost_factor_dff,

        (select distinct apv.COST_FACTOR_NAME from  qa_results_v v, ap_invoice_lines_v apv 

        where   v.plan_id = 108

        and v.PO_HEADER_ID = aila.PO_HEADER_ID

        and apv.INVOICE_ID = aila.invoice_id

        and apv.line_number = aila.line_number) cost_factor_line,

        AILA.attribute_category,

        (select distinct vl.DESCRIPTIVE_FLEX_CONTEXT_NAME from  FND_DESCR_FLEX_CONTEXTS_VL vl

        where   vl.DESCRIPTIVE_FLEX_CONTEXT_CODE = aila.attribute_category) attribute_category_name, 

        AILA.attribute4 LC_NO,

        (select distinct apv.PO_NUMBER from  qa_results_v v, ap_invoice_lines_v apv 

        where   v.plan_id = 108

        and v.PO_HEADER_ID = aila.PO_HEADER_ID

        and apv.INVOICE_ID = aila.invoice_id

        and apv.line_number = aila.line_number) PO_NUM,

        AILA.attribute_category,

        AILA.attribute4 LC_NO,

        (select distinct v.CHARACTER2 from  qa_results_v v 

        where   v.plan_id = 108

        and v.PO_HEADER_ID = aila.PO_HEADER_ID) LC_num_NEW,

        (select distinct apv.COST_FACTOR_NAME from  qa_results_v v, ap_invoice_lines_v apv 

        where   v.plan_id = 108

        and v.PO_HEADER_ID = aila.PO_HEADER_ID

        and apv.INVOICE_ID = aila.invoice_id

        and apv.line_number = aila.line_number) cost_factor,       

        ACA.BANK_ACCOUNT_NAME,

        ACA.STATUS_LOOKUP_CODE,

        TO_CHAR(ACA.CHECK_DATE)                                 PAYMENT_DATE,

        NG_NATURAL_ACCOUNT_CODE(AIDA.DIST_CODE_COMBINATION_ID)  NATURAL_ACCOUNT_CODE,

        NG_NATURAL_ACCOUNT_DESC(AIDA.DIST_CODE_COMBINATION_ID)  NATURAL_ACCOUNT_DESC,

        SUM(AIPA.AMOUNT)                                        PAYMENT_AMOUNT,

        AIPA.EXCHANGE_RATE                                      PAYMENT_EXCH_RATE,

        --SUM(AIPA.AMOUNT * NVL(AIPA.EXCHANGE_RATE,1))            PAYMENT_AMOUNT_FUNC_CURR,

        SUM((AIDA.AMOUNT * NVL(AIDA.EXCHANGE_RATE,1))) Line_amount,

        SUM(((AIDA.AMOUNT * NVL(AIDA.EXCHANGE_RATE,1)) / DECODE(NG_AP_GET_INVOICE_DIST_AMOUNT(AIA.INVOICE_ID),0,1,NG_AP_GET_INVOICE_DIST_AMOUNT(AIA.INVOICE_ID)))*(AIPA.AMOUNT * NVL(AIPA.EXCHANGE_RATE,1))) Amount

FROM

        AP_INVOICE_PAYMENTS_ALL         AIPA,

        AP_INVOICES_ALL                 AIA,

        AP_INVOICE_LINES_ALL            AILA,

        AP_INVOICE_DISTRIBUTIONS_ALL    AIDA,

        AP_CHECKS_ALL                   ACA,

        AP_SUPPLIERS                    ASA,

        AP_SUPPLIER_SITES_ALL           ASSA,

        HR_OPERATING_UNITS              HOU       

WHERE

        AIPA.INVOICE_ID(+)                  =       AIA.INVOICE_ID

        AND AIPA.ORG_ID(+)                  =       AIA.ORG_ID

        AND AIA.VENDOR_ID                   =       ASA.VENDOR_ID

        AND AIA.VENDOR_SITE_ID              =       ASSA.VENDOR_SITE_ID

        AND AIA.ORG_ID                      =       HOU.ORGANIZATION_ID

        AND AIA.INVOICE_ID                  =       AILA.INVOICE_ID

        AND AIA.ORG_ID                      =       AILA.ORG_ID

        AND AILA.INVOICE_ID                 =       AIDA.INVOICE_ID

        AND AILA.LINE_NUMBER                =       AIDA.INVOICE_LINE_NUMBER

        AND AILA.ORG_ID                     =       AIDA.ORG_ID

        AND AIPA.CHECK_ID                   =       ACA.CHECK_ID

        AND AIPA.ORG_ID                     =       ACA.ORG_ID

        --AND (AILA.ATTRIBUTE6                <>      'B2B' OR AILA.ATTRIBUTE6 IS NULL)

        --AND AIA.PAY_GROUP_LOOKUP_CODE       =       'IMPORT COTTON'

        --AND AIA.INVOICE_TYPE_LOOKUP_CODE    <>      'PREPAYMENT'

        --AND ACA.CHECK_NUMBER                =       '1935366'

        --AND AIA.INVOICE_ID                  =       408551

        --AND AILA.LINE_NUMBER                =       '32'

        AND AIDA.AMOUNT                     <>      0

        AND AIDA.PARENT_REVERSAL_ID         IS      NULL

        AND NVL(AILA.DISCARDED_FLAG,'N')    =       'N'

        AND ACA.STATUS_LOOKUP_CODE          <>      'VOIDED'

        AND AILA.LINE_TYPE_LOOKUP_CODE      NOT IN  ('AWT','PREPAY')

        AND TRUNC(AIPA.ACCOUNTING_DATE)     BETWEEN '01-JAN-2016'       AND      '31-OCT-2020'

--        AND AILA.ATTRIBUTE_CATEGORY = 'Inventory Information_B2B'

--        AND AIA.INVOICE_ID= 423366

GROUP BY

        ASA.VENDOR_NAME,

        ASA.SEGMENT1,

        ASSA.VENDOR_SITE_CODE,

        HOU.NAME,

        ACA.CHECK_ID,

        ACA.CHECK_NUMBER,

        AIA.INVOICE_ID,

        AIA.INVOICE_NUM,

        AILA.LINE_NUMBER,

        AIA.PAY_GROUP_LOOKUP_CODE,

        AILA.ATTRIBUTE6,

        AILA.ATTRIBUTE7,

        AILA.ATTRIBUTE5,

        AILA.ATTRIBUTE_CATEGORY,

        AILA.ATTRIBUTE4,

        AILA.PO_HEADER_ID,

        AILA.INVOICE_ID,

        AILA.COST_FACTOR_ID,

        AIPA.EXCHANGE_RATE,

        ACA.BANK_ACCOUNT_NAME,

        ACA.STATUS_LOOKUP_CODE,

        AIA.INVOICE_TYPE_LOOKUP_CODE,

        AIDA.DIST_CODE_COMBINATION_ID,

        TO_CHAR(ACA.CHECK_DATE)

----------------------------------------------------------------------------------------------------------------------

AP Supplier Data

  -----------------------------------------Supplier Data-------------------------------------------------------------------

select distinct * from 

(

select distinct aps.segment1 Supplier, aps.VENDOR_NAME

--,apsa.VENDOR_SITE_ID

,apsa.VENDOR_SITE_CODE

,apsa.ADDRESS_LINE1 || '-' || apsa.ADDRESS_LINE2 || '-' || apsa.ADDRESS_LINE3||'-' ||apsa.city Address,apsa.phone

from ap_suppliers aps,ap_supplier_sites_all apsa

where apsa.VENDOR_ID=aps.vendor_id

--and aps.segment1 = 1030

order by aps.segment1

)tab1,

(

SELECT  distinct asu.segment1 Supp_Num

,hpcp.email_address

FROM

 hz_relationships hr

,ap_suppliers asu

,ap_supplier_sites_all assa

,hz_org_contacts hoc

,hz_parties hpr

,hz_contact_points hpcp

WHERE 

1 = 1

AND hr.subject_id = asu.party_id

AND hr.relationship_code = 'CONTACT'

AND hr.object_table_name = 'HZ_PARTIES'

AND asu.vendor_id = assa.vendor_id

AND hr.party_id = hpr.party_id

AND hpr.party_type='PARTY_RELATIONSHIP'

AND hpcp.contact_point_type='EMAIL'

--AND assa.org_id = xxxx

AND hpr.party_id = hpcp.owner_table_id

AND hpcp.owner_table_name = 'HZ_PARTIES'

)tab2

where tab1.Supplier = tab2.Supp_Num(+)

--and tab1.Supplier = 4897

----------------------------------------------------------------------------------------------------------